Re: pre-algebra discussion - HOT?

On Thu, 2007-09-20 at 13:57 -0700, Selena Deckelmann wrote:
> HOT (Heap Overflow Tuple) is intended to improve update behavior on
> heavily updated tables.

At some point (exactly when, I'm not sure), they changed the name to
mean "Heap Only Tuple," which more accurately represents what's
currently happening. The original design of HOT used overflow segments
of the table to isolate the heavily-updated tuple versions (if I
remember correctly), and was designed to be a table option (like
fillfactor).

The newer design is enabled for all tables, and performs the HOT update
only when it can fit the newer version in the same page as the old
tuple. In practice, this results in "overflow pages" for tuples which
are heavily updated, because they will first be updated normally ("COLD"
update) and then the fresh page will have plenty of room for subsequent
HOT updates.

However, the design is not focused on the "overflow" aspect anymore,
it's more focused on avoiding index updates by allowing tuples that
exist only in the heap and are referenced by the index only indirectly
through the ctid chain; hence "heap only tuple". In 8.2 it's impossible
to have a tuple that exists in the heap without an index entry (assuming
there is an index on the table), but with HOT you can have more tuples
in the heap than index entries.

Regards,
Jeff Davis