Re: What's eating my space ?

[Eric McKeeth <eldin00@xxxxxxxxx>]

On Thu, May 19, 2011 at 1:05 AM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote:

Georgi Ivanov <georgi.r.ivanov@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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