Re: Visibility map, partial vacuums

Gregory Stark wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Gregory Stark wrote:
>>> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>>>> Hmm. It just occurred to me that I think this circumvented the anti-wraparound
>>>> vacuuming: a normal vacuum doesn't advance relfrozenxid anymore. We'll need to
>>>> disable the skipping when autovacuum is triggered to prevent wraparound. VACUUM
>>>> FREEZE does that already, but it's unnecessarily aggressive in freezing.
>> FWIW, it seems the omission is actually the other way 'round. Autovacuum always
>> forces a full-scanning vacuum, making the visibility map useless for
>> autovacuum. This obviously needs to be fixed.
>
> How does it do that? Is there some option in the VacStmt to control this? Do
> we just need a syntax to set that option?

The way it works now is that if VacuumStmt->freeze_min_age is not -1
(which means "use the default"), the visibility map is not used and the
whole table is scanned. Autovacuum always sets freeze_min_age, so it's
never using the visibility map. Attached is a patch I'm considering to
fix that.

> How easy is it to tell what percentage of the table needs to be vacuumed? If
> it's > 50% perhaps it would make sense to scan the whole table? (Hm. Not
> really if it's a contiguous 50% though...)

Hmm. You could scan the visibility map to see how much you could skip by
using it. You could account for contiguity.

> Another idea: Perhaps each page of the visibility map should have a frozenxid
> (or multiple frozenxids?). Then if an individual page of the visibility map is
> old we could force scanning all the heap pages covered by that map page and
> update it. I'm not sure we can do that safely though without locking issues --
> or is it ok because it's vacuum doing the updating?

We discussed that a while ago:

http://archives.postgresql.org/message-id/492A6032.6080000@enterprisedb.com

Tom was concerned about making the visibility map not just a hint but
critical data. Rightly so. This is certainly 8.5 stuff; perhaps it would
be more palatable after we get the index-only-scans working using the
visibility map, since the map would be critical data anyway.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com