Hi,
Le Tuesday 05 February 2008 21:28:31 Alvaro Herrera, vous avez écrit :
> Perhaps there is a user-level document somewhere.
I don't think such a document already exists, even rtfm_please doesn't know
about any as of now. So I'll try to begin something here, and depending on
the comments I'll publish a short user oriented HOT introduction article.
Here we go...
PostgreSQL implements HOT (Heap Only Tuples), a way for the server to limit
the work it has to make when updating tuples. That's what we call an
optimization :)
PostgreSQL MVCC implementation choice means that updating a tuple create a
entire new version of it and mark the old one as no longer valid (as of the
updating transaction id). Then VACUUM will have to clean out the old
reference as soon as possible.
Let's not forget that the indexes pointing the the old tuples need to point to
the new version of it as of transaction id. PostgreSQL currently does not
save visibility information into the index, though, reducing the janitoring
here. But still, for the index, the operation of updating a tuple is
equivalent to a delete and an insert.
That's before HOT.
Starting with PostgreSQL 8.3, when a tuple is updated and if the update only
concerns non-indexed columns, the RDBMS is smart enough for the existing
indexes not to need any update at all.
This is done by creating a new tuple if possible on the same page as the old
one, and maintaining a chain of updated tuples linking a new version to the
old one. An HOT tuple is in fact one that can't be reached from any index.
VACUUM will now only have to prune the tuple versions of the chain that are
no more visible, and as no index were updated (there was no need to), there's
no VACUUM work to get done on the indexes.
Of course, for HOT to work properly, PostgreSQL has now to follow each HOT
chain when SELECT'ing tuples and using an index, but the same amount of
tuples version was to be read before HOT too. The difference is that with HOT
the new versions of the HOT-updated tuples are no more reachable via the
index directly, so PostgreSQL has to follow the chain when reading the heap.
Please comment and correct me if my understanding is wrong (which wouldn't be
a surprise), if this article over simplified, or not really written in
English :)
I'd like to publish a correct version of this for us to point asking users to,
or maybe it could even end up as official documentation/FAQ material?
Regards,
--
dim