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�

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

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°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.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

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