Skip site navigation (1) Skip section navigation (2)

Re: Estimating Database Disk Space

From: Richard A Lough <ralough(dot)ced(at)dnet(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: ralough(at)iee(dot)org,PostgreSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Estimating Database Disk Space
Date: 2002-03-20 21:34:31
Message-ID: 3C990067.8EDC30EC@dnet.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Tom Lane wrote:
> 
SNIP
> > 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
> 
Hmmm.. \l tells me I have nine databases, including postgres, lo_demo, 
template0, and template1. Template0 refuses a connection, and three 
of the other databases are empty. Only one of my active databases 
shows significant figures (recently increased): 

relpages | relname 
17473	pg_attribute_relid_attnam_index
7267	pg_attribute_relid_attnum_index
3245	pg_attribute
1012	pg_statistic_relid_att_index
646	pg_class_oid_index
645	pg_type_oid_index
507	pg_class_relname_index
478	pg_type_typname_index
77	pg_class
76	pg_type

Of these pg_attribute and pg_statistic_relid_att_index have grown 
significantly (3245/909, and 1012/549 respectively). At most I have 
added an average of one tuple to the 3126 tables. I'm adding 
frequently, so size is a moving target. 

I had a look (I think) at the indexes for nasdaq01 using pgaccess.
No indexes were shown, I'm not sure I got that right. Size 
dumped with pg_dump is 2.6MB for the relpages given above.

Richard A Lough

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2002-03-20 21:43:21
Subject: Re: Estimating Database Disk Space
Previous:From: Masse JacquesDate: 2002-03-20 18:05:28
Subject: installing procedural language

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group