Re: HOT for PostgreSQL 8.3

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> The basic idea is that when a tuple is UPDATEd we can, in certain
> circumstances, avoid inserting index tuples for a tuple. Such tuples are
> marked HEAP_ONLY_TUPLE, but their storage is otherwise identical to
> other tuples.

What is VACUUM FULL going to do when it wants to move one of these things?

> CREATE INDEX requires some careful work to allow it to identify and
> correctly index HEAP_ONLY_TUPLEs that need to become ~HEAP_ONLY_TUPLE as
> a result of the new index.

I think you've glossed over the CREATE INDEX problem much too easily.
The difficulty with that is going to be that de-HOT-ifying a tuple
is going to require multiple updates that can't possibly be put into
a single WAL record, and I don't think that WAL replay can clean up
after an incomplete update (since it can't run user-defined functions
and hence cannot be expected to compute index entries for itself).
So I don't think you can do that while preserving crash safety.

> Removing the root tuple will require a VACUUM *FULL*.

That seems unacceptable ... it won't take too long for your table to
fill up with stubs, and we don't want to return to the bad old days
when periodic VACUUM FULL was unavoidable.

ISTM we could fix that by extending the index VACUUM interface to
include two concepts: aside from "remove these TIDs when you find them",
there could be "replace these TIDs with those TIDs when you find them".
This would allow pointer-swinging to one of the child tuples, after
which the old root could be removed. This has got the same atomicity
problem as for CREATE INDEX, because it's the same thing: you're
de-HOT-ifying the child. So if you can solve the former, I think you
can make this work too.

regards, tom lane