Re: Latest plans for Utilities with HOT

On Mon, 2007-03-05 at 21:39 +0530, Pavan Deolasee wrote:
> Simon Riggs wrote:
> >
> > - VACUUM FULL - The best solution, for now, is to make VACUUM FULL
> > perform a reindex on all indexes on the table. Chilling may require us
> > to modify considerably more index entries than previously. UPDATE & WAIT
> > would be very good, but probably should wait for the next release now,
> > since we now have changes to make to 4 utilities.
>
> On my way back home, I was thinking about VACUUM FULL. Is there really
> a problem with VACUUM FULL and HOT ? VF moves tuple chains in the
> table and does that while holding AccessExclusive lock on the table.

No problem, just additional work for little benefit.

> If we prune the HOT-update chains (which we anyways do for lazy vacuum),
> we can guarantee that the entire HOT-update chain will be moved, if
> any of the tuples in the chain is moved.

Currently each tuple is moved individually. You'd need to inspect the
whole HOT chain on a page, calculate space for that and then try to move
them all in one go. I was originally thinking that would be a problem,
but its not so bad - but it may cause us to end repair_frag() earlier
than we otherwise would depending upon the game of Tetris plays out.

Thats harder than it sounds. My concern is that VACUUM FULL code is
fairly ugly and doing this might introduce a bug into a rarely used
piece of code that we don't pick up. I would not choose that path
myself, but we can go there if the consensus is that doing a reindex
would be the wrong way to go. Reindex will be fast to code and much more
likely to be bug-free.

> Also, when VF moves a tuple
> chain appropriate index entries are inserted into all the indexes.
> If we don't carry the HEAP_HOT_UPDATED or HEAP_ONLY_TUPLE flags
> to the moved location, the HOT-update chain will be broken, but
> that should not have any correctness problems. If VACUUM FULL
> crashes before completeing its operation, the original HOT-update
> chain would still remain valid and the new tuples will be discarded.

You mean all moves will be cold?

So VACUUM FULL will actually bloat the indexes? I hope I've
misunderstood you.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com