Azure PostgreSQL storage data incresing

sima savke 0 Reputation points
2024-10-28T16:59:23.1566667+00:00

I am using postgresql for development purpose. And its increasing its size automatically even without access or no activity. Every day it increases for about 700mb. When i go to metrics and select used storage it is 2.9gb. And when i select backup storage it is 5gb. So i dont know why it is increasing every day withoud inputs in database?

User's image

Azure Database for PostgreSQL
{count} votes

1 answer

Sort by: Most helpful
  1. NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor
    2024-10-29T06:18:28.7533333+00:00

    @sima savke
    Thank you for the question and for using Microsoft Q&A platform.
    The increasing size of your PostgreSQL database in Azure, even without active data inputs, could be due to a few common factors:

    Automatic Backups: Azure PostgreSQL services have automatic backup settings that store daily backups for recovery purposes. Backup storage increases over time due to incremental changes, and these backups are separate from your data storage metrics. In your case, backup storage growing to 5GB while used storage shows 2.9GB is consistent with incremental backups.

    Autovacuum Operations: PostgreSQL uses autovacuum to manage and clean up dead tuples (from deleted or updated rows). However, if autovacuum isn’t configured optimally, it may not reclaim storage space efficiently. To check or adjust, you could analyze your autovacuum settings and consider scheduling manual VACUUM operations to reclaim space.

    Transaction Logs (WAL files): Write-ahead logs (WAL) keep track of database changes and are essential for backup and recovery. If transactions are active, even with limited data modifications, these WAL logs might accumulate. Azure PostgreSQL retains WAL files for Point-In-Time Recovery (PITR), which could account for the steady storage increase.

    Index Bloat: If there are many indexes on tables, they may accumulate “bloat” over time, consuming additional storage without data growth. You can identify bloat with queries on index usage and consider rebuilding indexes periodically.

    To mitigate this:

    1. Check Autovacuum Settings: Ensure that autovacuum is running frequently enough. You can adjust the settings in your postgresql.conf file.
    2. Monitor WAL Files: Check the size and number of WAL files. Ensure that they are being archived and cleaned up regularly.
    3. Run VACUUM and REINDEX: Regularly run VACUUM and REINDEX to clean up dead tuples and reduce bloat.
    4. Review Backup Strategy: Consider using incremental backups instead of full backups to save space.

    For more information, please refer to these links:
    https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-autovacuum-tuning
    https://learn.microsoft.com/en-us/training/modules/optimize-vacuum-azure-database-for-postgresql/
    https://www.postgresql.org/docs/current/wal-intro.html
    https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-backup-restore
    https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-perform-fullvacuum-pg-repack
    https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-monitor-bloat-in-postgres-with-datadog-custom-metrics-on/ba-p/1542366
    https://www.timescale.com/blog/how-to-reduce-your-postgresql-database-size/

    Hope this helps. Please let us know if you have any further queries.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.