We sometimes see customers asking questions related to a discrepancy between the server storage usage and their expectations on the actual data usage. In this blog we will go through how to investigate storage consumption for Azure Database for PostgreSQL.
In this section, I am listing down some thoughtful insights and recommendations to breakdown the storage usage to some extent.
It worth to mention that you can set the retention period for this short-term log storage using the log_retention_period parameter. The default value is 3 days; the maximum value is 7 days. The short-term storage location can hold up to 1 GB of log files. However, after 1 GB, the oldest files, regardless of retention period, will be deleted to make room for new logs.
For longer term retention and larger storage, consider using Azure diagnostic settings. see the Azure PostgresSQL logging documentation for more information.
4) Logical Decoding
Unnecessary PostgreSQL Logical decoding replication slots can have high impact on the server availability and storage consumption, replication slots hold on to Postgres WAL logs and relevant system catalogs until changes have been read by a consumer. in case this is failing to do so, the unconsumed logs will pile up and fill the server storage.
Therefore, it is critical that logical replication slots are consumed continuously. If a logical replication slot is no longer used, you need to drop it immediately.
You can check replication slots on server by running:
select * from pg_replication_slots;
In case the slot is no longer needed, you can simply delete it using the command:
Note:- VACUUM FULL, which can reclaim more space, but takes much longer and exclusively locks the table. Plus an extra disk space will be needed, since it writes a new copy of the table and doesn't release the old copy until the operation is complete.
6) Enable Storage Auto-grow and set up an alert
Last but not least, we always recommend that you enable storage auto-growor set up an alert to notify you when your server storage is approaching the threshold so you can avoid getting into the read-only state. For more information, see the documentation on how to set up an alert.
Note:- Keep in mind that storage can only be scaled up, not down.
I hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.