Message-Id: <1168373668.28926.1168412995@webmail.messagingengine.com>
From: "Jeremy Haile" <jhaile@fastmail.fm>
To: "Florian Weimer" <fweimer@bfk.de>
Cc: pgsql-performance@postgresql.org
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="ISO-8859-1"
MIME-Version: 1.0
References: <1168363601.5177.1168380907@webmail.messagingengine.com>
	<82d55okrim.fsf@mid.bfk.de>
Subject: Re: High inserts, bulk deletes - autovacuum vs scheduled
In-Reply-To: <82d55okrim.fsf@mid.bfk.de>
Date: Tue, 09 Jan 2007 15:14:28 -0500

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.=20=20

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:
>=20
> > 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!
>=20
> 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).
>=20
> --=20
> Florian Weimer                <fweimer@bfk.de>
> BFK edv-consulting GmbH       http://www.bfk.de/
> Kriegsstra=DFe 100              tel: +49-721-96201-1
> D-76133 Karlsruhe             fax: +49-721-96201-99