Question

Managed Postgres instance reporting 10x disk space used vs data stored

I have a single 10GB Postgres cluster, without replication. Reporting on table sizes gives me a total data used of 113 MB but the Insights panel is reporting almost 10% of 10GB disk space used (i.e. 1GB).

How can there be 900MB of overhead, for just over 100MB of data? I realise logs, indexes etc are in there somewhere, but this seems really excessive.

How can I optimise this, or at least find out what is using all the space?


Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev
Site Moderator
Site Moderator badge
March 22, 2024

Hey!

This could be due to the fact that PostgreSQL maintains various logs, such as transaction logs, error logs, and slow query logs and etc. also, indexes can consume disk space as well. The space used by indexes is not included in the table size reports but can be significant, especially with complex indexing strategies.

I could also suggest using this query to get a summary of the total size of all tables:

SELECT 
  relname AS "Table",
  pg_size_pretty(pg_total_relation_size(relid)) AS "Size",
  pg_size_pretty(pg_relation_size(relid)) AS "Table Size",
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS "Index Size"
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

There was a similar quesiton regarding this in a while back:

https://www.digitalocean.com/community/questions/any-way-to-reduce-disk-space-usage-with-managed-postgres

I could suggest trying to narrow down which database and tables are using most of the disk space. You could use the following queries:

  • Check the size of a specific database:
SELECT pg_size_pretty(pg_database_size('defaultdb'));
  • Check the size of a specific table:
SELECT pg_size_pretty( pg_total_relation_size('table_name_here') );

And the user reported that they were able to reduce the disk space using vacuum full on the large tables.

Let me know how it goes!

Best,

Bobby

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel