Azure flexible Postgres storage capacity usage unknown

Question

Azure flexible Postgres storage capacity usage unknown

Safi 10

Hello,

We are facing a problem which is baffling us, we have some databases on "Azure Database for PostgreSQL - Flexible Server" we summed the size of disk usage by these databases and included the managed dbs and came to a total of 121GB of total capacity purchased 256GB User's image But when we check used storage in Azure UI we find that 174GB us used..

User's image which means there is a lost 54GB!!

And we are paying for it....

can you please help troubleshoot this issue and find what is taking all this space, we tried restarting the instance but no change.

Vijayalaxmi Kattimani 1,720 Reputation points Microsoft External Staff

2025-01-30T05:59:46.14+00:00
Hi @Safi,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We apologize for the inconvenience caused.

We would like to inform you that, it's important to note that Azure Database for PostgreSQL - Flexible Server has several factors that can contribute to additional storage usage beyond the actual data stored in your databases. Here are some potential reasons for the discrepancy.

Automatic Backups: Azure Database for PostgreSQL flexible server automatically creates server backups and stores them on the region's zone redundant storage (ZRS). These backups can consume significant storage space, especially if the retention period is set to the maximum of 35 days.

Temporary Files: PostgreSQL can generate temporary files for sorting operations, hash joins, and other operations that require disk space. These temporary files can accumulate and contribute to the overall storage usage.

WAL (Write-Ahead Logging) Files: PostgreSQL uses WAL files to ensure data integrity and support recovery operations. These files can also consume a considerable amount of storage, especially if there are frequent write operations.

Bloat: Over time, PostgreSQL tables and indexes can become bloated due to updates and deletes. This bloat can lead to increased storage usage. Regular vacuuming and analyzing can help mitigate this issue.

Connection Resources: Each connection in Azure Database for PostgreSQL flexible server, whether idle or active, consumes resources from your database. This consumption can lead to performance issues beyond high CPU utilization, such as disk and lock contention.

To address these issues, you can follow these steps:

Check Backup Retention Settings: Review your backup retention settings and adjust them if necessary. You can also manually delete old backups if they are no longer needed.

Monitor Temporary Files: Use PostgreSQL's built-in tools to monitor and clean up temporary files. You can also configure settings to limit the size and duration of temporary files.

Manage WAL Files: Ensure that WAL files are being archived and cleaned up regularly. You can configure settings to control the size and retention of WAL files.

Perform Regular Maintenance: Schedule regular vacuuming and analyzing of your databases to reduce bloat and optimize storage usage.

Review Connection Usage: Monitor and manage the number of active connections to your database to avoid unnecessary resource consumption.

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/overview

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-troubleshooting-guides

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-limits

I hope this information helps. Please do let us know if you have any further
Safi 10 Reputation points

2025-01-30T10:30:58.31+00:00

Hello @Vijayalaxmi Kattimani

Thank you for your reply,

I understand, but In regards to disabling backup I didn't find such an option also regarding deletion of backups manually, its not possible in UI, but thru AzCLI I issue the command and it completes successfully but without effect, as after I issue the command and delete all backups I check again and all backups are still there even when I list them in cli

Also considering that this is a dev database server, it doesn't actually have any real load, so losing 54GB is a lot, we checked all temp files and deleted, we checked WAL ~10GB and it does auto clean, so we are unable to understand why we lost that much GBs from db server capacity constantly.

We even dropped databased and recreated, and still lost 54GB...
Vijayalaxmi Kattimani 1,720 Reputation points Microsoft External Staff

2025-01-30T13:28:48.43+00:00

Hi @Safi,

As you stated, you have already tried deleting backups via AzCLI without success, it might be worth checking if there are any known issues or limitations with the backup deletion process. According to the troubleshooting guide for Azure Database for PostgreSQL flexible server, there are several common errors and recommended actions for backup and restore operations. For instance, if the resource isn't in a valid state to perform the backup operation, it might cause issues with backup deletion. https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex-troubleshoot

Additionally, it's possible that the backup vault or the backup policy configuration might be contributing to the storage usage. Ensure that the backup vault has the required permissions and that the backup policy is correctly configured. https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex

Please make sure that the backup vault and policy are correctly configured and have the necessary permissions and refer to the troubleshooting guide for any known issues or limitations with backup deletion and follow the recommended actions.

I hope this information helps. Please do let us know if you have any further queries.
PratikLad 475 Reputation points Microsoft External Staff

2025-02-03T07:17:05.0133333+00:00
Hi @Safi,

Storage Used Metrics consist of:

Database Files – Actual data stored in tables and indexes.

Transaction Logs – Used for durability and recovery.

Server Logs – System and query logs maintained for auditing and debugging.

Disk Usage by Databases measured by:

pg_database_size() – Returns the size of a specific database, including only tables and indexes.

It does not include: Transactional logs, Temporary files

I hope this information helps. Please do let us know if you have any further queries.

2 answers

Your answer

Safi 10 Reputation points

2025-01-30T10:30:58.31+00:00

Hello @Vijayalaxmi Kattimani

Thank you for your reply,

I understand, but In regards to disabling backup I didn't find such an option also regarding deletion of backups manually, its not possible in UI, but thru AzCLI I issue the command and it completes successfully but without effect, as after I issue the command and delete all backups I check again and all backups are still there even when I list them in cli

Also considering that this is a dev database server, it doesn't actually have any real load, so losing 54GB is a lot, we checked all temp files and deleted, we checked WAL ~10GB and it does auto clean, so we are unable to understand why we lost that much GBs from db server capacity constantly.

We even dropped databased and recreated, and still lost 54GB...
Vijayalaxmi Kattimani 1,720 Reputation points Microsoft External Staff

2025-01-30T13:28:48.43+00:00

Hi @Safi,

As you stated, you have already tried deleting backups via AzCLI without success, it might be worth checking if there are any known issues or limitations with the backup deletion process. According to the troubleshooting guide for Azure Database for PostgreSQL flexible server, there are several common errors and recommended actions for backup and restore operations. For instance, if the resource isn't in a valid state to perform the backup operation, it might cause issues with backup deletion. https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex-troubleshoot

Additionally, it's possible that the backup vault or the backup policy configuration might be contributing to the storage usage. Ensure that the backup vault has the required permissions and that the backup policy is correctly configured. https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex

Please make sure that the backup vault and policy are correctly configured and have the necessary permissions and refer to the troubleshooting guide for any known issues or limitations with backup deletion and follow the recommended actions.

I hope this information helps. Please do let us know if you have any further queries.
PratikLad 475 Reputation points Microsoft External Staff

2025-02-03T07:17:05.0133333+00:00

Hi @Safi,

Storage Used Metrics consist of:

Database Files – Actual data stored in tables and indexes.

Transaction Logs – Used for durability and recovery.

Server Logs – System and query logs maintained for auditing and debugging.

Disk Usage by Databases measured by:

pg_database_size() – Returns the size of a specific database, including only tables and indexes.

It does not include: Transactional logs, Temporary files

I hope this information helps. Please do let us know if you have any further queries.

Answer 1

Safi 10

Hello @Vijayalaxmi Kattimani

Thank you for your reply,

We don't have any (vaults, policies..) or anything like that, we simply created a basic flexible postgresql server, the backup function is the built in one which we can't disable.

as for the resource I can confirm that its available, I just still don't know how I can get billed for 54GB which I don't see, there should be some explanation or purpose where these are being used and it should be plainly visible to the customer..

Answer 2

Hi @Safi,

Thank you for reaching out to MS Q&A.

In Azure UI Storage Used metrics Shows the storage that's used by the service can include the database files, transaction logs, and the server logs and when you are checking size of disk usage by these databases it shows only data files and do not include transection logs. Server logs in Azure Postgres server are initially disabled by default.

As I See for my Azure Postgres DB where I haven't created anything, I See the Db size as per Azure UI Storage used metrics initially 2.3 GB and now it's around 2.7 GB as per below image.

enter image description here

So, the difference is around 400 MB.

In Azure UI there is another metrics called Transaction Log Storage Used which shows the Amount of storage space that's used by the transaction logs.

it is initially 112.7 MB and now it is 486.5 MB as you can see below:

enter image description here

from above we can see the difference is around 380 MB

As per above the difference between Storage Used and Transaction Log Storage Used is around same. so, in your case the difference you see in your size of disk usage by these databases and Storage Used in Azure UI is the space user by Transactional log and Server logs (if it is enabled).

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-monitoring

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.

PratikLad 475 Reputation points Microsoft External Staff

2025-02-06T04:48:44.3866667+00:00

Hi @Safi,

Can you please confirm if above answer is helpful, please do let us know if you have any further.

Share via

Azure flexible Postgres storage capacity usage unknown

2 answers

Your answer