Re: database speed

Quoting Doug McNaught <doug(at)mcnaught(dot)org>:

> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Speaking of recent versions... In 7.4, doesn't the cluster command address this
problem?

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com