Re: database speed

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

> WE currently use PG for the local database on our POS workstations
> in shops. Data is then extracted and sent back to head office. Also
> product updates etc are regularly sent to the shops. I have noticed
> over time that the shop PG databases get slower and slower. If I do
> a pg_dump, recreate the database and then restore the data - all
> speed problems are gone and the database folder shrinks in size
> substantially.
>
> >From this information could anyone tell me what are the likely
> >causes and what we may or may not be doing in our extractions and
> >updates that are not good PG insert, update, delete procedures

You should periodically be doing:

a) VACUUM (at least once a day, probably) and
b) REINDEX

Are you doing both of these? See the docs if you're not familiar with
them.

The REINDEX is needed because VACUUM doesn't free up index space in
some circumstances. 7.4 (currently in late beta) will fix this.

-Doug