From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: pgsql-performance@postgresql.org
Subject: Re: When to vacuum a table?
Date: Mon, 27 Nov 2006 12:59:48 -0600
Message-ID: <456AE144.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1164540257.7902.0.camel@panoramix>
References: <1164540257.7902.0.camel@panoramix>
>>> On Sun, Nov 26, 2006 at 5:24 AM, in message
<camel@panoramix/">1164540257.7902.0.camel@panoramix>, Joost Kraaijeveld
<J.Kraaijeveld@Askesis.nl> wrote:
>
> Are there guidelines (or any empirical data) available how to
determine
> how often a table should be vacuumed for optimum performance or is
this
> an experience / trial- and- error thing?
For most of our databases we use a daily "VACUUM ANALYZE VERBOSE;". We
grep the results to show large numbers of removable or dead rows and to
show the fsm numbers at the end. There are a few small tables with high
update rates which this doesn't cover. To handle that we set the
autovacuum to 0.2/0.1 and 1/1 on a ten second interval and do a daily
CLUSTER of these tables. We really don't have many tables which can hit
these autovacuum thresholds in one day.
We have databases which are 400 GB with the vast majority of that being
in tables which are insert-only except for a weekly purge of data over a
year old. We do nightly vacuums on the few tables with update/delete
activity, and a weekly vacuum of the whole database -- right after the
delete of old rows from the big tables.
-Kevin
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: Kevin.Grittner@wicourts.gov, J.Kraaijeveld@Askesis.nl
Subject: Re: When to vacuum a table?
In-Reply-To: <456AE144.EE98.0025.0@wicourts.gov>
* 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