Re: Estimating Database Disk Space

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