Re: postgresql performance degradation over time....

On Fri, Aug 26, 2005 at 22:13:04 +0530,
sunil arora <arora(dot)sunil(at)gmail(dot)com> wrote:
> Hi folks,
>
> this is my first post to this emailing list. We are using postgres-7.4
> in a Server based application which requires frequent updates and
> inserts of the database. We have observed a substantial fall in the
> performance of database server over the time. It works fine for some
> initial days and then its performace falls... and goes on falling as
> the days passes by ( there is not much increase in the size of the
> database)
>
> I have heard that updates and deletes in any DB bring in some sort of
> fregmentation in the database, and we are using vaccum feature ot the
> postgres to defragment. We have also tweaked the performance
> parameters in postgresql.conf ... but its not helping.

Are you vacuuming the database?

If you haven't been, you will probably need to do a vacuum full now to get
things down to a reasonable size. You should have regularly scheduled
vacuum runs to allow for reuse of deleted tuples. In 8.0 there is a contrib
package that does automated vacuum scheduling. In the upcoming 8.1 release
(just in beta) that feature is part of the core distribution.

If you haven't already, you should read through the server administration
part of the documention.