From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: VACUUM and read-mostly tables
Date: Tue, 05 Apr 2005 11:34:40 -0400
Message-ID: <13828.1112715280@sss.pgh.pa.us> (raw)
In-Reply-To: <1112713990.8115.83.camel@spectre.intellivid.com>
References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net>
<10364.1112683320@sss.pgh.pa.us>
<1112713990.8115.83.camel@spectre.intellivid.com>
Ian Westmacott <ianw@intellivid.com> writes:
> But the question is whether vacuum freezing tables will
> help me reduce the frequency of a full vacuum, or reduce
> its cost when we do it? That is, if more transactions
> are frozen, will a full vacuum be more efficient
> (primarily in the I/O)?
I'm not sure if you are actually confused, or are just using confusing
terminology ... but there's a significant difference between VACUUM FULL
(ie, vacuum and try to reclaim space) and a database-wide vacuum. I
*think* you are using "full vacuum" to mean "database-wide vacuum" but
it's not entirely clear.
Anyway, the frequency with which you have to do database-wide vacuums to
avoid XID wraparound is determined entirely by the rate at which you use
up XIDs. Doing piecemeal VACUUM FREEZEs would reduce the amount of work
that needs to be done in the eventual database-wide vacuum, but it's not
clear that it'd be a net win given the added work of the extra VACUUM
scans.
Have you looked at whether you can slow down the rate of XID consumption
(ie, by bundling operations into larger transactions)? That might be a
more useful route to limiting the costs involved.
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ianw@intellivid.com
Subject: Re: VACUUM and read-mostly tables
In-Reply-To: <13828.1112715280@sss.pgh.pa.us>
References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> <10364.1112683320@sss.pgh.pa.us> <1112713990.8115.83.camel@spectre.intellivid.com> <13828.1112715280@sss.pgh.pa.us>
* 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