Re: What's eating my space ?

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°