Re: Estimating Database Disk Space

Richard A Lough <ralough.ced@dnet.co.uk> writes:
>> A "du" on your $PGDATA directory would give you some facts instead of
>> guesses.
>>
> It seems to have grown a little, du says:
> 250540     base
> 32812     pg_xlog
> 472    global
> 4    postmaster.pid
> 4    postmaster.opts
> 4    PG_VERSION
> 0    postgresql.conf

Okay, so your space usage is indeed mostly in the databases, and not the
overhead like pg_xlog.

> Databases, dumped in text format are 3.3MB, 3.3MB and 1.6MB,
> though there are be a couple of empty temporary databases

250MB of disk space does seem a tad high for that much data.  Have you
vacuumed lately?  If so, you could drill down to the next level of
detail by looking at the relpages column of pg_class in each database
to see which tables or indexes are using the most space.  (relpages is
measured in units of 8K disk blocks, btw, and it's only approximate
because it's generally only updated by vacuum.)  Something like this
would give you the top ten offenders:

select relname, relpages from pg_class order by relpages desc limit 10;

            regards, tom lane