Re: What's eating my space ?

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