Re: What's eating my space ?

On Thu, May 19, 2011 at 1:05 AM, Andreas Kretschmer <
akretschmer(at)spamfence(dot)net> wrote:

> Georgi Ivanov <georgi(dot)r(dot)ivanov(at)gmail(dot)com> wrote:
>
> > Hi,
> > I wander what is taking up my space on disk ...
> >
> > btv=# SELECT pg_size_pretty(pg_database_size('btv_good'));
> > pg_size_pretty
> > ----------------
> > 10 GB
> > (1 row)
> >
> >
> > btv=# SELECT nspname || '.' || relname AS "relation",
> > pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
> > FROM pg_class C
> > LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> > WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> > AND C.relkind <> 'i'
> > AND nspname !~ '^pg_toast'
> > ORDER BY pg_total_relation_size(C.oid) DESC
> > LIMIT 15;
> > relation | total_size
> > --------------------------------------+------------
> > users.users | 703 MB
> > btv.material | 557 MB
> > btv_admin.material | 269 MB
> > btv_admin.block | 24 MB
> > btv.block | 20 MB
> > btv_admin.block_list | 9136 kB
> > btv.block_list | 9112 kB
> > multimedia.rel_image_collection2size | 2984 kB
> > multimedia.rel_image_collection2tag | 1024 kB
> > btv_admin.block_common | 976 kB
> > multimedia.image_collection | 936 kB
> > btv.block_common | 832 kB
> > users_admin.invalidate_notify | 752 kB
> > btv_admin.tv_program | 656 kB
> > btv.rel_material2tag | 592 kB
> > (15 rows)
> >
> > The sum of biggest tables is not even close to the total db size .
> > Some index going wild ?
>
> Your sum doesn't contains indexes and toast-tables.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Since he used pg_total_relation_size(), according to the manual (
http://www.postgresql.org/docs/current/interactive/functions-admin.html)
indexes and toast should be included in the numbers reported for the tables.
Unfortunately, I don't have any insight as to why pg_database_size() is
returning a number roughly 5x larger than the sum of
pg_total_relation_size() here.

-Eric