@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:
- Check Autovacuum Settings: Ensure that autovacuum is running frequently enough. You can adjust the settings in your
postgresql.conf
file. - Monitor WAL Files: Check the size and number of WAL files. Ensure that they are being archived and cleaned up regularly.
- Run VACUUM and REINDEX: Regularly run
VACUUM
andREINDEX
to clean up dead tuples and reduce bloat. - 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.