Re: Difference in the size of database size and relations

2013/12/10 Luis <luisico@gmail.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