Re: optimizing db for small table with tons of updates - Mailing list pgsql-performance

From Tom Lane
Subject Re: optimizing db for small table with tons of updates
Date
Msg-id 15521.1144089550@sss.pgh.pa.us
Whole thread Raw
In response to optimizing db for small table with tons of updates  (Kenji Morishige <kenjim@juniper.net>)
Responses Re: optimizing db for small table with tons of updates
List pgsql-performance
Kenji Morishige <kenjim@juniper.net> writes:
> Various users run a tool that updates this table to determine if the particular
> resource is available or not.  Within a course of a few days, this table can
> be updated up to 200,000 times.  There are only about 3500 records in this
> table, but the update and select queries against this table start to slow
> down considerablly after a few days.  Ideally, this table doesn't even need
> to be stored and written to the filesystem.  After I run a vacuum against this
> table, the overall database performance seems to rise again.

You should never have let such a table go that long without vacuuming.

You might consider using autovac to take care of it for you.  If you
don't want to use autovac, set up a cron job that will vacuum the table
at least once per every few thousand updates.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Rajesh Kumar Mallah"
Date:
Subject: Re: optimizing db for small table with tons of updates
Next
From: Kenji Morishige
Date:
Subject: Re: optimizing db for small table with tons of updates