Re: HOT documentation README

On 9/4/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>
> I have taken this, and Pavan's documentation about CREATE INDEX, and
> worked up an updated README. Comments? Corrections?

Thanks Bruce, Heikki, Greg for helping me with the documentation.

> The requirements for doing a HOT update is that none of the indexed
> columns are changed. That is checked at execution time, comparing the
> binary representation of the old and new values.

It would be worth mentioning that columns appearing in predicates
of partial indexes and expressions of expression indexes are also
checked. If any of these columns are changed, HOT update is not done.

>
> 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 heap space (but not the
> line pointer itself).

A lazy vacuum is required to reclaim redirect-dead line pointers.

To limit the damage in the 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
> arbitrarily capped at twice its previous maximum.

With the latest patch, we have reverted it back to the original value.

VACUUM FULL
> -----------

It might be worth mentioning that vacuum full also removes
redirected line pointers by making them directly point to
the first tuple in the HOT chain. We can do so, because vacuum
full works with an exclusive lock on the relation.

VACUUM
> ------
>
> There is little change to regular vacuum. It removes dead HOT tuples,
> like pruning does, and cleans up any redirected dead line pointers.

One change that is worth mentioning is that with HOT it needs vacuum
strength
lock in the first phase (currently it works with SHARE lock if no tuples
need
freezing or EXCLUSIVE lock otherwise). We can improve it a bit by first
checking if there is really a need for pruning and then only go for cleanup
lock. But thats probably not worth the efforts (atleast for large tables
where
we should usually get cleanup lock rather easily).

Statistics
> ----------
>
> XXX: How do HOT-updates affect statistics? How often do we need to run
> autovacuum?

As the latest patch stands, we track dead-space in the relation and trigger
autovacuuum based on the percentage of dead space in the table. We
don't have any mechanism to account for index bloat yet. Autoanalyze
does not change.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com