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

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