From: | Jeff Frost <jeff(at)frostconsultingllc(dot)com> |
---|---|
To: | Carol Walter <walterc(at)indiana(dot)edu> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: How much space do database objects take up in data files |
Date: | 2008-02-13 20:46:23 |
Message-ID: | Pine.LNX.4.64.0802131222500.5521@discord.home.frostconsultingllc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Wed, 13 Feb 2008, Carol Walter wrote:
> I've struggled with this one too. You can get the size of databases with
> SELECT pg_database_size('database name'); You can get the size of tables
> with SELECT pg_relation_size('table name');
>
> Carol
> On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:
>
>> Hi,
>>
>> How can I find out how much space is taken up by database objects in data
>> files?
Give this query a try. It's a variation of one posted on this list some time
ago. Unfortunately, I'm not sure who to credit for the original post. This
one takes into account index and toast size and sorts descending by totalsize.
SELECT nspname, relname,
pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
FROM
(SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size(reltoastrelid)
END AS toastsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
WHERE ct.oid = cl.reltoastrelid))
END AS toastindexsize
FROM pg_class cl, pg_namespace ns
WHERE cl.relnamespace = ns.oid
AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relname IN
(SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE')) ss
ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
From | Date | Subject | |
---|---|---|---|
Next Message | Leticia Larrosa | 2008-02-13 20:48:56 | terminating connection because of crash of another server process |
Previous Message | Tom Lane | 2008-02-13 20:18:01 | Re: CONTEXT messages for raise EXCEPTION |