Subject: Re: VACUUM not doing its job?
From: Ken Corey <ken@kencorey.com>
To: Kristian Eide <kreide@online.no>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <035d01c23b2f$c5d49420$6b97f181@speed>
References: <035d01c23b2f$c5d49420$6b97f181@speed>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 03 Aug 2002 23:14:43 +0100
Message-Id: <1028412884.28635.22.camel@kenlinux.bithub.org>
Mime-Version: 1.0

I've run into this myself.  Tom lane helped me out.

In my case, it was the fact that indexes don't release the space of
indexes of deleted rows.  So, if you have a table that has a lot of
inserts/deletes, your indexes will grow incredibly fast.

The way to see what your biggest items are: 

select * from pg_class order by relpages desc;

If your tables are active with lots of inserts/deletes, the biggest
things will likely be indexes.

The only way that I know to recover this space is to drop the indexes
and recreate them.  Vacuum didn't touch them for me.

-Ken

On Sat, 2002-08-03 at 21:53, Kristian Eide wrote:
> 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.