Re: Visibility map, partial vacuums - Mailing list pgsql-hackers

From Gregory Stark
Subject Re: Visibility map, partial vacuums
Date
Msg-id 87bpw5rt2p.fsf@oxford.xeocode.com
Whole thread Raw
In response to Re: Visibility map, partial vacuums  (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>)
List pgsql-hackers
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

> Gregory Stark wrote:
>> However I'm a bit puzzled how you could possibly maintain this frozenxid. As
>> soon as you freeze an xid you'll have to visit all the other pages covered by
>> that visibility map page to see what the new value should be.
>
> Right, you could only advance it when you scan all the pages covered by the
> visibility map page. But that's better than having to scan the whole relation.

Is it? It seems like that would just move around the work. You'll still have
to visit every page once ever 2B transactions or so. You'll just do it 64MB at
a time. 

It's nice to smooth the work but it would be much nicer to detect that a
normal vacuum has already processed all of those pages since the last
insert/update/delete on those pages and so avoid the work entirely.

To avoid the work entirely you need some information about the oldest xid on
those pages seen by regular vacuums (and/or prunes). 

We would want to skip any page which:

a) Has been visited by vacuum freeze and not been updated since 

b) Has been visited by a regular vacuum and the oldest xid found was more  recent than freeze_threshold.

c) Has been updated frequently such that no old tuples remain

Ideally (b) should completely obviate the need for anti-wraparound freezes
entirely.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!


pgsql-hackers by date:

Previous
From: "Lawrence, Ramon"
Date:
Subject: Re: Proposed Patch to Improve Performance of Multi-Batch Hash Join for Skewed Data Sets
Next
From: Tom Lane
Date:
Subject: Re: Re: [COMMITTERS] pgsql: Explicitly bind gettext() to the UTF8 locale when in use.