Re: database speed

On Fri, Oct 31, 2003 at 11:54:55AM +1100, Chris Stokes wrote:
> Hi all,
>
> 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

The three important steps for maintainence of a postgresql database:

1. ANALYZE
2. VACUUM [FULL]
3. REINDEX

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato