Re: Estimating Database Disk Space

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: ralough(at)iee(dot)org
Cc: PostgreSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Estimating Database Disk Space
Date: 2002-03-18 19:54:46
Message-ID: 1067.1016481286@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Richard A Lough <ralough(dot)ced(at)dnet(dot)co(dot)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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Trevor Morrison 2002-03-19 05:09:44 Fwd: Query results
Previous Message Richard A Lough 2002-03-18 18:58:49 pg_restore has error message 7