Re: database speed

"Chris Stokes" <ChrisS(at)BassSoftware(dot)com> writes:

> >The REINDEX is needed because VACUUM doesn't free up index space in
> >some circumstances. 7.4 (currently in late beta) will fix this.
>
> Sorry Doug,
>
> Yes I am doing a vacuum regularly - in fact, when the speed of the
> DB becomes slower, the vacuum takes longer and longer too.
>
> I have never used the REINDEX
>
> I wondered if it might be a row chaining issue of some sort ?!?

The index bloat problem can occur when you have a an indexed SERIAL
column whose value always increases, and you delete older rows on a
regular basis. VACUUM recycles the row storage, but the index never
shrinks.

You can check: next time you get the speed/data growth problem, find
which files in the database directory are growing, and use 'oid2name'
(in the source tree under 'contrib') to find out what they are. Odds
are they are indexes and REINDEXing their table will fix the problem.

If you are running a recent version of PG (7.3 and maybe 7.2 as well)
and have a very active database, you may also need to increase your
free space map (FSM) size in postgresql.conf, and possibly run VACUUM
FULL once a week or so.

-Doug