Обсуждение: What's eating my space ?

Georgi Ivanov <georgi.r.ivanov@gmail.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°

Eric McKeeth wrote:
>>> 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)

[SELECT total size of all non-system tables]

>>> 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.
>>
> 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.

Maybe it's the system tables.

Try running the following query:

SELECT SUM(pg_total_relation_size(C.oid)) AS "total_size",
       (N.nspname IN ('pg_catalog', 'information_schema')) AS
"system_object"
  FROM pg_class C
  JOIN pg_namespace N ON (N.oid = C.relnamespace)
  LEFT OUTER JOIN pg_tablespace T ON (C.reltablespace = T.oid)
  WHERE C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND COALESCE(T.spcname, 'default') != 'pg_global'
  GROUP BY nspname IN ('pg_catalog', 'information_schema');

which will give you a sum of the sizes of all tables and their
appendixes,
grouped by system and non-system tables. I exclude global tables.

On my 8.4 test database I get:

 total_size | system_object
------------+---------------
     376832 | f
    5505024 | t
(2 rows)

For
SELECT pg_database_size(current_database())
I get:

 pg_database_size
------------------
          5972260
(1 row)

which comes pretty close.

Yours,
Laurenz Albe