From: Pierre C <lists@peufeu.com>
To: gnanam@zoniac.com
To: pgsql-performance@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
Date: Sat, 27 Mar 2010 14:35:15 +0100
Message-ID: <op.u98ao1lxeorkce@immo.peufeu.com> (raw)
In-Reply-To: <004701cacdad$7013e9f0$503bbdd0$@com>
References: <004701cacdad$7013e9f0$503bbdd0$@com>
> 1. VACUUM FULL ANALYZE once in a week during low-usage time and
VACUUM FULL compacts tables, but tends to bloat indexes. Running it weekly
is NOT RECOMMENDED.
A correctly configured autovacuum (or manual vacuum in some circumstances)
should maintain your DB healthy and you shouldn't need VACUUM FULL.
If you realize you got a bloat problem, for instance due to a
misconfigured vacuum, use CLUSTER, which re-generates table AND index
data, and besides, having your table clustered on an index of your choice
can boost performance quite a lot in some circumstances.
8.2 is so old I don't remember if autovacuum is even included. Please try
upgrading to the latest version...
Since your database probably fits in RAM, CLUSTER will be pretty fast.
You can schedule it weekly, if you need clustering. If you don't,
autovacuum will suffice.
Hint : add a "SELECT count(*) FROM yourtable;" before "CLUSTER yourtable;"
so that the table is pulled in the OS disk cache, it'll make CLUSTER
faster.
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: lists@peufeu.com, gnanam@zoniac.com
Subject: Re: Database size growing over time and leads to performance impact
In-Reply-To: <op.u98ao1lxeorkce@immo.peufeu.com>
* 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