Re: Great change (size of data dir) upgrading postgresql

On Sun, 18 Jan 2004, Carlos Costa Portela wrote:

> On Sun, 18 Jan 2004, Nigel J. Andrews wrote:
> > On Sun, 18 Jan 2004, Carlos Costa Portela wrote:
> > > S.ficheros          Tamaño Usado  Disp Uso% Montado en
> > > /dev/hda1             1.9G  1.8G   64M  97% /usr/local/pgsql/data.old
> > > /dev/hdb2             4.5G  357M  3.9G   9% /usr/local/pgsql/data
> > >
> > Well, to be safe...back it up.
>
> I've backed up it to cd. Thanks.
>
> > However, I would suggest that you haven't 'vacuum'-ed you 7.3 database for some
> > time. To give some level of comfort before destroying the original data
> > directory you could fire up the 7.2 server and run vacuum full against it's
>
> I have a vacuum command each day on my crontab :-?

Is that a vacuum full? It may make a difference if the free space map hasn't
been able to track all the deletions so that new data could reuse that
space. The vacuum full does what could be described as data compaction. it
moves tuples aroud to fill holes in the tuple store.

The other thing is the reindex. Btree indexes were well known for increasing in
size as new records were added if those records were expanding the range of
keys covered by an index. For example indexing a serial where older values were
deleted and not reused didn't free the space in the index until 7.4. Check that
out by picking a much updated table with an index on a field with
values obtained from a sequence, drop that index then recreate it. Hopefully
you would see a noteworthy decrease in disk usage.

Of course you could look at the files within the data/base directory tree and
see which of those are consuming alot of disk space. These files are named
after the oid, in pg_class, of the entity they contain.


--
Nigel Andrews