From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kenji Morishige <kenjim@juniper.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimizing db for small table with tons of updates
Date: Mon, 03 Apr 2006 14:39:10 -0400
Message-ID: <15521.1144089550@sss.pgh.pa.us> (raw)
In-Reply-To: <20060403182403.GA12257@juniper.net>
References: <20060403182403.GA12257@juniper.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, kenjim@juniper.net
Subject: Re: optimizing db for small table with tons of updates
In-Reply-To: <15521.1144089550@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox