Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> I'm trying to understand why there are GB's difference when checking
> for db size using pg_size_pretty() and querying for tables + indexes
> size. .
You are not counting everything --- the total DB size is clearly 12GB,
so the question is where are the other 5.5GB? Your first query shows
that schema caom accounts for 6+GB, but the second one does not prove
that schema caom contains all the big hogs. My guesses are:
1. Toast tables for tables that aren't in caom --- you used
pg_relation_size not pg_total_relation_size, and excluded toast
tables, so you are missing those.
2. pg_largeobject ... got any large objects?
3. Bloat in other system catalogs. 5GB of catalog bloat would be
pretty awful, but maybe that's what it is.
Try that last query without the namespace restrictions.
regards, tom lane