storage is growing Azure postgre database

Question

storage is growing Azure postgre database

Molagara, Bhanu P 20

DB sizes are very less in single digits GB and no replication slots are lagging and no sizes. But storage is growing. How to find which is consuming the storage size.

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2023-04-05T22:16:12.3533333+00:00

@Molagara, Bhanu P Thanks for posting your question in the Microsoft Q&A forum. Multiple reasons why the storage of your Postgres database is growing.

One possible reason is that the database is being used to store more data than before. Another reason could be that the database is not being properly vacuumed, which can lead to dead tuples taking up space_._ Additionally, if the storage usage reaches 95% or if the available capacity is less than 5 GiB, the server is automatically switched to read-only mode to avoid errors associated with disk-full situations.

You can monitor the usage and increase the storage to a higher size. When the storage usage reaches 95% or if the available capacity is less than 5 GiB, the server is automatically switched to read-only mode to avoid errors associated with disk-full situations.

We recommend setting alert rules for storage used or storage percent when they exceed certain thresholds so that you can proactively take action such as increasing the storage size

Regards

Geetha
GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2023-04-10T14:13:19.89+00:00

@Molagara, Bhanu P We haven't heard back from you. Just wanted to check if you had the chance to check my response. Regards Geetha
Rakesh Sharma 80 Reputation points Microsoft Employee

2023-04-11T12:21:23.62+00:00
@Molagara, Bhanu P

Too many inserts and deletions in the database could lead to a situation of too many dead tuples and they remain in database until a VACUUM is done.

Check if there is replica configured and there is a lag between primary and read replica under this situation there are chances of having too many WAL files.

https://learn.microsoft.com/en-us/azure/postgresql/single-server/how-to-optimize-autovacuum

1 answer

Your answer

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2023-04-05T22:16:12.3533333+00:00

@Molagara, Bhanu P Thanks for posting your question in the Microsoft Q&A forum. Multiple reasons why the storage of your Postgres database is growing.

One possible reason is that the database is being used to store more data than before. Another reason could be that the database is not being properly vacuumed, which can lead to dead tuples taking up space_._ Additionally, if the storage usage reaches 95% or if the available capacity is less than 5 GiB, the server is automatically switched to read-only mode to avoid errors associated with disk-full situations.

You can monitor the usage and increase the storage to a higher size. When the storage usage reaches 95% or if the available capacity is less than 5 GiB, the server is automatically switched to read-only mode to avoid errors associated with disk-full situations.

We recommend setting alert rules for storage used or storage percent when they exceed certain thresholds so that you can proactively take action such as increasing the storage size

Regards

Geetha
GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2023-04-10T14:13:19.89+00:00

@Molagara, Bhanu P We haven't heard back from you. Just wanted to check if you had the chance to check my response. Regards Geetha
Rakesh Sharma 80 Reputation points Microsoft Employee

2023-04-11T12:21:23.62+00:00

@Molagara, Bhanu P

Too many inserts and deletions in the database could lead to a situation of too many dead tuples and they remain in database until a VACUUM is done.

Check if there is replica configured and there is a lag between primary and read replica under this situation there are chances of having too many WAL files.

https://learn.microsoft.com/en-us/azure/postgresql/single-server/how-to-optimize-autovacuum

Answer 1

Hi,

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;

You can get each database temporary files and size using view pg_stat_database

SELECT * FROM pg_stat_database;

At times, replication slots can significantly affect server availability and storage usage. Replication slots retain Postgres WAL logs and relevant system catalogs until they are read by a consumer. If this process fails, unprocessed logs can accumulate and occupy server storage.

You can check replication slots on server by running:

select * from pg_replication_slots;

Analyze the subsequent server parameters that could potentially contribute to the increase in storage usage.

log_retention_period

It's worth noting that you have the option to adjust the retention period for this short-term log storage by using the log_retention_period parameter. The default value is 3 days, and the maximum value allowed is 7 days.

PostgreSQL Logging parameters,

The common recommendation is to exercise caution when using verbose logging. More information can be found in my colleague blog How to configure Postgres log settings - Microsoft Tech Community

I hope this is helpful.

Thank you!

Share via

storage is growing Azure postgre database

1 answer

Your answer