Re: Minimally avoiding Transaction Wraparound in VLDBs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Minimally avoiding Transaction Wraparound in VLDBs
Date: 2005-09-01 02:21:36
Message-ID: 7684.1125541296@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote:
>> If you don't remove any tuples,
>> you don't scan the indexes anyway IIRC.

> No. Even if you remove *zero* tuples, an index is still scanned twice.
> Once to not delete the rows and once to not delete the pages.

Yeah? Well, that could probably be improved with a less intrusive fix,
that is, one that does it automatically instead of involving the user.

I really really do not like proposals to introduce still another kind
of VACUUM. We have too many already; any casual glance through the
archives will show that most PG users don't have a grip on when to use
VACUUM FULL vs VACUUM. Throwing in some more types will make that
problem exponentially worse.

> autovacuum will respond only to UPDATEs and DELETEs. In the scenario I
> outline, these will *never* occur on the largest tables. A VACUUM would
> still eventually be required to freeze long lived tuples and this would
> not be performed by autovacuum.

Yes, it will, at least as of 8.1.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-09-01 02:24:47 Re: TODO item: set proper permissions on non-system schemas
Previous Message Tom Lane 2005-09-01 02:08:48 Re: Indexing dead tuples