Re: Difference in the size of database size and relations

2013/12/10 Luis <luisico(at)gmail(dot)com>:
> Hi,
>
> I have noticed a weird thing in different databases.
> When I run pg_database_size on some databases they report a pretty
> high value and if I take a look to all relations the sum of all of
> them is not even the half of the one reported by pg_database_size.
>
> Although I've seen this in different databases, this is a specific example.
>
>
> This are the sizes reported by this query:
>
> SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
>
>
> size
> ---------
(…)

> And this is the database size reported by this query:
> SELECT pg_size_pretty(pg_database_size('mydb'));
>
> pg_size_pretty
> ----------------
> 3539 MB
>
> I've read about LOBs but I don't seem to have any.
>
> Any idea where the space is being used?

Indexes for a start. Try:

SELECT C.relname, pg_size_pretty(pg_relation_size(C.oid)) AS "size",
pg_size_pretty(pg_indexes_size(C.oid)) AS "idx_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE relkind='r'
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

Regards

Ian Barwick