PostgreSQL Database size is not equal to sum of size of all tables

user_7409834 0
Viewed 643

I am using an AWS RDS PostgreSQL instance. I am using below query to get size of all databases.

SELECT datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc

One database's size is 23 GB and when I ran below query to get sum of size of all individual tables in this particular database, it was around 8 GB.

select pg_size_pretty(sum(pg_total_relation_size(table_schema || '.' || table_name)))
from information_schema.tables

As it is an AWS RDS instance, I don't have rights on pg_toast schema. How can I find out which database object is consuming size?

Thanks in advance.

postgresql-9.6 postgresql
1 Answers

The documentation says:

pg_total_relation_size ( regclass ) → bigint

Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.

So TOAST tables are covered, and so are indexes.

One simple explanation could be that you are connected to a different database than the one that is shown to be 23GB in size.

Another likely explanation would be materialized views, which consume space, but do not show up in information_schema.tables.

Yet another explanation could be that there have been crashes that left some garbage files behind, for example after an out-of-space condition during the rewrite of a table or index.

This is of course harder to debug on a hosted platform, where you don't have shell access...

user_64643080
Related