Re: database size much bigger than tablespaces on filesystem

Rob Audenaerde <Rob(dot)Audenaerde(at)Valuecare(dot)nl> writes:
> It gets stranger. I try this:

> select
> tablename
> , pg_relation_size(tablename)
> , pg_size_pretty(pg_relation_size(tablename) ) as relsize
> , pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
> , pg_total_relation_size(tablename)
> from pg_tables where schemaname <> 'information_schema'
> order by 2 desc

> And all the tables report a 'disksize' larger than the 'relsize',
> which seems natural. The biggest table (relsize) is around 5 GB.

pg_relation_size isn't going to count indexes nor toast tables, and
toast in particular could be where a lot of the space is. I'd suggest
sorting by pg_total_relation_size to see if anything jumps out at you.

Another problem with the above query is that it supposes that there are
no similarly-named tables in different schemas. If there are, you'll
get multiple reports of the size of the one that is visible in your
search path, and no reports for the others. If I were doing this,
I'd not rely on pg_tables but look at pg_class directly so I could use
the OID, something like

select relname, pg_total_relation_size(c.oid)
from pg_class c where relkind = 'r'
order by 2 desc

regards, tom lane