From: Jeremy Haile <jhaile@fastmail.fm>
To: Florian Weimer <fweimer@bfk.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: High inserts, bulk deletes - autovacuum vs scheduled
Date: Tue, 09 Jan 2007 15:14:28 -0500
Message-ID: <1168373668.28926.1168412995@webmail.messagingengine.com> (raw)
In-Reply-To: <82d55okrim.fsf@mid.bfk.de>
References: <1168363601.5177.1168380907@webmail.messagingengine.com>
<82d55okrim.fsf@mid.bfk.de>
Good advice on the partitioning idea. I may have to restructure some of
my queries, since some of them query across the whole range - but it may
be a much more performant solution. How is the performance when
querying across a set of partitioned tables vs. querying on a single
table with all rows? This may be a long term idea I could tackle, but
is probably not feasible for my current time-frame.
Does my current approach of disabling autovacuum and manually vacuuming
once-an-hour sound like a good idea, or would I likely have better
results by auto-vacuuming and turning row-level stats back on?
On Tue, 09 Jan 2007 19:02:25 +0100, "Florian Weimer" <fweimer@bfk.de>
said:
> * Jeremy Haile:
>
> > I'd like any performance advice, but my main concern is the amount of
> > time vacuum/analyze runs and its possible impact on the overall DB
> > performance. Thanks!
>
> You could partition your data tables by date and discard old data
> simply by dropping the tables. This is far more effective than
> vacuuming, but obviously, this approach cannot be used in all cases
> (e.g. if you need more dynamic expiry rules).
>
> --
> Florian Weimer <fweimer@bfk.de>
> BFK edv-consulting GmbH http://www.bfk.de/
> Kriegsstraße 100 tel: +49-721-96201-1
> D-76133 Karlsruhe fax: +49-721-96201-99
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: jhaile@fastmail.fm, fweimer@bfk.de
Subject: Re: High inserts, bulk deletes - autovacuum vs scheduled
In-Reply-To: <1168373668.28926.1168412995@webmail.messagingengine.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