Difference in the size of database size and relations

From: Luis <luisico(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Difference in the size of database size and relations
Date: 2013-12-10 11:33:50
Message-ID: CAM-gcbSfTp6co+vo5s6UMy4b9oGf9yLFZX9WJKrBR1=P8BoXzw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I have noticed a weird thing in different databases.
When I run pg_database_size on some databases they report a pretty
high value and if I take a look to all relations the sum of all of
them is not even the half of the one reported by pg_database_size.

Although I've seen this in different databases, this is a specific example.

This are the sizes reported by this query:

SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

size
---------
99 MB
45 MB
10 MB
8528 kB
5280 kB
4352 kB
3184 kB
1304 kB
1152 kB
896 kB
544 kB
528 kB
336 kB
336 kB
336 kB
304 kB
296 kB
288 kB
272 kB
248 kB

And this is the database size reported by this query:
SELECT pg_size_pretty(pg_database_size('mydb'));

pg_size_pretty
----------------
3539 MB

I've read about LOBs but I don't seem to have any.

Any idea where the space is being used?

Thanks

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Ian Lawrence Barwick 2013-12-10 12:35:36 Re: Difference in the size of database size and relations
Previous Message Payal Singh 2013-12-07 06:33:42 Re: Cold backup with rsync -- WAL files?