Hi!
I've looked around, but could only find very old answers to this question, and maybe it changed since then...
I'm struggling to identify the cause of the difference in size between the sum of all relations (via
pg_total_relation_size)and pg_database_size:
SELECT sum(pg_total_relation_size(relid)),
pg_size_pretty(sum(pg_total_relation_size(relid)))
FROM pg_catalog.pg_stat_all_tables
sum |pg_size_pretty|
------------+--------------+
518549716992|483 GB |
SELECT pg_database_size('mydb'),
pg_size_pretty(pg_database_size('mydb'))
pg_database_size|pg_size_pretty|
----------------+--------------+
869150909087|809 GB |
There are three databases in the cluster, apart from 'mydb' (one of them is the 'postgres' database). These other two
sizeabout 8MB each.
We run pg_repack weekly and recently had crashes related to disk running out of space, so my guess is something got
'lost'during repack.
What can I do to recover the wasted space?
Thanks!
Luis R. Weck