Re: High inserts, bulk deletes - autovacuum vs scheduled vacuum - Mailing list pgsql-performance

From Florian Weimer
Subject Re: High inserts, bulk deletes - autovacuum vs scheduled vacuum
Date
Msg-id 82vejfi5tq.fsf@mid.bfk.de
Whole thread Raw
In response to Re: High inserts, bulk deletes - autovacuum vs scheduled  ("Jeremy Haile" <jhaile@fastmail.fm>)
List pgsql-performance
* Jeremy Haile:

> 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?

Locality of access decreases, of course, and depending on your data
size, you hit something like to 2 or 4 additional disk seeks per
partition for index-based accesses.  Sequential scans are not
impacted.

> 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?

Sorry, I haven't got much experience with autovacuum, since most of
other databases are INSERT-only (or get VACUUMed automatically after
major updates).

--
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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Horribly slow query/ sequential scan
Next
From: Tom Lane
Date:
Subject: Re: Horribly slow query/ sequential scan