Tom Lane wrote:
> "Pavan Deolasee" <[EMAIL PROTECTED]> writes:
>> Please see the version 14 of HOT patch attached.
>
> I expected to find either a large new README, or some pretty substantial
> additions to existing README files, to document how this all works.
Here's an updated version of the README I posted earlier. It now
reflects the changes to how pruning works.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Use case
--------
The best use case for HOT is a table that's frequently UPDATEd, and is large
enough that VACUUM is painful. On small tables that fit in cache, running
VACUUM every few minutes isn't a problem.
Heap-only tuples
----------------
When a HOT update is performed, the new tuple is placed on the same page as the
old one, marked with the HEAP_ONLY_TUPLE flag. HEAP_ONLY_TUPLE means that
there's no index pointers to the tuple, which allows pruning the chain in the
future. The old tuple is marked with HEAP_HOT_UPDATE-flag, which means that the
tuple pointed to by t_ctid is a heap-only tuple. That needs to be taken into
account when vacuuming, so that we don't remove the root tuple in the update
chain, when there's no index pointers to the later tuples.
When doing an index scan, whenever we reach a non-visible tuple, we need to
check if the tuple has been HOT-updated (== HEAP_HOT_UPDATE flag is set). If
so, we need to follow the ctid pointer until we reach a visible one, or one
that hasn't been HOT-updated.
Sequential scans (and bitmap heap scans with a lossy bitmap) don't need to pay
attention to the flags.
The pre-requirements for doing a HOT update is that none of the indexed columns
are changed. That's checked at execution time, comparing the binary
representation of the old and new values. That means that dummy updates, like
"UPDATE foo SET col1 = ?", where ? is the same as the old value can be HOT.
In addition to the above, there needs to be room on the page for the new tuple.
If the page is full, we try to make room by pruning the page.
Pruning
-------
Pruning is a lightweight vacuum operation that can be run on a single page,
with no need to scan indexes, but it only removes dead HOT tuples. Other dead
tuples are truncated, leaving only a redirected dead line pointer. The removed
tuples are compacted away using PageRepairFragmentation, like in normal vacuum.
There's two reasons to prune a page: to make room on the page for future
updates, and to shorten HOT chains to make index lookups cheaper.
When accessing a page with HOT updated tuples on it, and less than a certain
threshold of free space, we try to prune it. To do that, we need to take a
vacuum strength lock on the buffer. If that fails, we don't prune; the theory
is that you usually do get the lock, and if you don't, you'll get to try again
next time. It would be more logical to do the pruning in heap_update when the
page is full, but by the time we get there we have already pinned the page and
have references to tuples on it, so we can't start moving tuples around it.
Also, that alone wouldn't address the desire to keep HOT chains short, to avoid
overhead of traversing long chains on index lookups.
To reclaim the index-visible (i.e. first) tuple in a HOT chain, the line
pointer is turned into a redirecting line pointer that points to the line
pointer of the next tuple in the chain.
When the last live tuple in an update chain becomes dead (after a DELETE or a
cold update), the redirecting line pointer is marked as redirected dead. That
allows us to immediately reuse the space, sans the line pointer itself. We've
effectively resurrected the "truncate dead tuples to just line pointer" idea
that has been proposed and rejected before because of fear of line pointer
bloat. To limit the damage in worst case, and to keep numerous arrays as well
as the bitmaps in bitmap scans reasonably sized, the maximum number of line
pointers (MaxHeapTuplesPerPage) is somewhat arbitrarily capped at 2 * what it
was before.
VACUUM FULL
-----------
To make vacuum full work, any DEAD tuples in the middle of an update chain
needs to be removed (see comments at the top of heap_prune_hotchain_hard for
details). Vacuum full performs a more aggressive pruning that not only removes
dead tuples at the beginning of an update chain, it scans the whole chain and
removes any intermediate dead tuples as well.
Vacuum
------
There's not much changes to regular vacuum. It removes dead HOT tuples, like
pruning, and cleans up any redirected dead line pointers.
In lazy vacuum, we must not freeze a tuple that's in the middle of an update
chain. That can happen when a tuple has xmin > xmax; it's the same scenario
that requires "hard pruning" in VACUUM FULL. Freezing such tuples will break
the check that xmin and xmax matches when following the chain. It's not a
problem without HOT, because the preceding tuple in the chain must be dead as
well so no-one will try to follow the chain, but with HOT the preceding tuple
would be DEAD_CHAIN, and someone might still need to follow the chain to find
the live tuple. We avoid that by just not freezing such tuples. They can be
frozen eventually, when the xmax of the preceding tuple is < OldestXmin as well.
Statistics
----------
XXX: How do HOT-updates affect statistics? How often do we need to run
autovacuum?
CREATE INDEX
CREATE INDEX CONCURRENTLY
-------------------------
I'm not very familiar with how these, so I'll just shut up..
Glossary
--------
Heap-only tuple
A heap tuple with no index pointers. Marked with HEAP_ONLY_TUPLE flag.
HOT-updated tuple
An updated tuple, so that the next tuple in the chain is a heap-only
tuple. Marked with HEAP_HOT_UPDATE flag.
Redirecting line pointer
A line pointer that points to another line pointer. lp_len is set to a
magic value (ITEMID_REDIRECTED), and lp_off is the OffsetNumber of the line
pointer it points to.
Redirected dead line pointer
A stub line pointer, that doesn't point to anything, but can't be
removed or reused yet because there is index pointers to it. Semantically same
as a dead tuple.
Root tuple
The first tuple in a HOT update chain, that indexes point to.
Update chain
A chain of updated tuples, so that each tuple's ctid points to the next
tuple in the chain. A HOT update chain is an update chain that consists of a
root tuple and one or more heap-only tuples. An update chain can contain both
HOT and non-HOT (cold) updated tuples.
Cold update
A normal, non-HOT update.
HOT update
An UPDATE, where the new tuple becomes a heap-only-tuple, and no index
entries are made.
DEAD_CHAIN (HEAPTUPLE_DEAD_CHAIN)
New return value for HeapTupleSatisfiesVacuum, which means that the
tuple is not visible to anyone, but it's been HOT updated so we can't remove it
yet because the following tuples in the chain would become inaccessible from
indexes.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
