To: Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
cc: Guillaume Lelarge <guillaume@lelarge.info>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: database size much bigger than tablespaces on filesystem 
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>
Comments: In-reply-to Rob Audenaerde <Rob.Audenaerde@Valuecare.nl>
	message dated "Wed, 14 Sep 2011 09:04:29 -0700"
Date: Wed, 14 Sep 2011 17:05:55 -0400
Message-ID: <6114.1316034355@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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