From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
Cc: Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: database size much bigger than tablespaces on filesystem
Date: Wed, 14 Sep 2011 17:05:55 -0400
Message-ID: <6114.1316034355@sss.pgh.pa.us> (raw)
In-Reply-To: <1D020918205F334CBDD5CE7DF2038A2E15A581245C@IE2RD2XVS271.red002.local>
References: <1D020918205F334CBDD5CE7DF2038A2E15A5812457@IE2RD2XVS271.red002.local>
<1316001615.2110.13.camel@localhost.localdomain>
<1D020918205F334CBDD5CE7DF2038A2E15A581245A@IE2RD2XVS271.red002.local>
<904.1316015234@sss.pgh.pa.us>
<1D020918205F334CBDD5CE7DF2038A2E15A581245C@IE2RD2XVS271.red002.local>
Rob Audenaerde <Rob.Audenaerde@Valuecare.nl> writes:
> It gets stranger. I try this:
> select
> tablename
> , pg_relation_size(tablename)
> , pg_size_pretty(pg_relation_size(tablename) ) as relsize
> , pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
> , pg_total_relation_size(tablename)
> from pg_tables where schemaname <> 'information_schema'
> order by 2 desc
> And all the tables report a 'disksize' larger than the 'relsize',
> which seems natural. The biggest table (relsize) is around 5 GB.
pg_relation_size isn't going to count indexes nor toast tables, and
toast in particular could be where a lot of the space is. I'd suggest
sorting by pg_total_relation_size to see if anything jumps out at you.
Another problem with the above query is that it supposes that there are
no similarly-named tables in different schemas. If there are, you'll
get multiple reports of the size of the one that is visible in your
search path, and no reports for the others. If I were doing this,
I'd not rely on pg_tables but look at pg_class directly so I could use
the OID, something like
select relname, pg_total_relation_size(c.oid)
from pg_class c where relkind = 'r'
order by 2 desc
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, Rob.Audenaerde@Valuecare.nl, guillaume@lelarge.info
Subject: Re: database size much bigger than tablespaces on filesystem
In-Reply-To: <6114.1316034355@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox