Message-ID: <035d01c23b2f$c5d49420$6b97f181@speed>
From: "Kristian Eide" <kreide@online.no>
To: <pgsql-sql@postgresql.org>
Subject: VACUUM not doing its job?
Date: Sat, 3 Aug 2002 22:53:08 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi, I have a PostgreSQL 7.2.1 database which normally (just after a
pg_restore) takes about 700-800MB of disk space. Now, the problem is that
the database grows quite quickly when in use, although we don't put very
much data in. Granted, there is quite a few records deleted and inserted,
but the total data volume grows only slowly.

Three week ago we did a pg_dump/pg_restore, and after pgsql/data/ took about
750MB. Now it uses 2,4GB of disk space! If we do a new complete dump and
restore the volume will decrease to about 800MB.

We of course do a 'vacuumdb -a -z' every day, but this does not seem to help
much unfortunately. The database is in use 24/7 so a full vacuum is not an
option. What we do now is simply a full dump/restore about once a month,
because the database slows to a crawl as the data volume grows too large
(this seems to be because it loads large amouts of data from disk for each
query, probably because the data postgre use no longer fit in the disk
cache).

Anyway, what could be causing this problem and what can we do about it? The
dump/restore option is not attractive in the long run for obvious reasons.


Regards,

Kristian