Learning from Expertise #7: Where is my server storage taken - Azure PostgreSQL?

Published Dec 31 2021 08:58 AM 1,935 Views
Microsoft

Overview:

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.

 

Solution:

In this section, I am listing down some thoughtful insights and recommendations to breakdown the storage usage to some extent.

 

1) First and foremost, monitor the server storage usage using the available Azure PostgreSQL Metrics:

 

Storage percentage Percent The percentage of storage used out of the server's maximum.
Storage used Bytes The amount of storage in use. The storage used by the service may include the database files, transaction logs, and the server logs.
Server Log storage percent Percent The percentage of server log storage used out of the server's maximum server log storage.
Server Log storage used Bytes The amount of server log storage in use.
Server Log storage limit Bytes The maximum server log storage for this server.
Storage limit Bytes The maximum storage for this server.

 

On Azure Portal, you can use more than one metrics as shown in below figure:

Ahmed_S_Mahmoud_0-1640951654024.png

 

Note:- Azure PostgreSQL flexible server comes with "Transaction Log Storage Used" metric, which depicts the amount of storage space used by the transaction logs.

 

2) The following queries can help you to have insights upon the database storage usage:

  • run below query to know each database size
SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database where datname not like ('%azure%') ORDER by size_in_mb DESC;
  • Check out the tables' vacuum status, if dead rows are being vacuumed using pg_stat_user_tables
SELECT relname,n_dead_tup, n_tup_del,n_tup_upd, last_vacuum, last_autovacuum,last_analyze,last_autoanalyze FROM pg_stat_user_tables;
  • You can get each database temporary files and size using view pg_stat_database 
SELECT * FROM pg_stat_database;
  • A quick look at view pg_stat_archiver can be a good idea, the view is showing statistics about the WAL archiver process's activity.
select * from pg_stat_archiver;
  • You can get the difference b/w the current WAL and the last archived one, taking into account that each WAL file has size of 16MB.
select pg_walfile_name(pg_current_wal_lsn()),last_archived_wal,last_failed_wal,
('x'||substring(pg_walfile_name(pg_current_wal_lsn()),9,8))::bit(32)::int*256 +
('x'||substring(pg_walfile_name(pg_current_wal_lsn()),17))::bit(32)::int -
('x'||substring(last_archived_wal,9,8))::bit(32)::int*256 - ('x'||substring(last_archived_wal,17))::bit(32)::int as diff 
from pg_stat_archiver;

3) Examine the following server parameters which might contribute into the storage usage growth

The general advice that you should be careful with the verbose logging, More information can be found in my colleague blog How to configure Postgres log settings - Microsoft Tech Community

 

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:

SELECT pg_drop_replication_slot('<slot-name>');

For more information, see Azure PostgreSQL Logical Decoding.

 

5) Leverage PostgreSQL VACUUM and Autovacuum to reclaim the unused space.

The bloated data can be cleaned by using VACUUM to reclaim some unused space. Another thing you can optimize the autovacuum setting for better PostgreSQL performance. More information can be found at: Optimize autovacuum - Azure Database for PostgreSQL - Single Server | Microsoft Docs
 
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-grow  or 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.

 

Ahmed_S_Mahmoud_0-1640950365554.png

 

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.

 

Ahmed S. Mazrouh

Co-Authors
Version history
Last update:
‎Apr 01 2022 03:45 AM
Updated by: