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