On Fri, Jul 22, 2022 at 10:05:04AM -0700, Peter Geoghegan wrote:
> On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <jk...@postgresql.org> wrote:
> > Thanks! This is great. Probably the most concise and clear explanation I
> > have seen for HOT, which is exactly what we need for the docs :)
>
> I'm delighted that this is finally happening!
>
> > I think we should expand on this and explain how adjusting "fillfactor"
> > will affect this. I think that may change the final sentence too.
>
> Definitely -- this is by far the most important reason to tune heap
> fill factor, which can make a big difference. There should be a link
> from the fill factor docs to the new HOT section, at a minimum.
Done in the attached patch, and updated URL:
https://momjian.us/tmp/pgsql/storage-hot.html
> Another thing: would be good to point out how to monitor the number of
> HOT updates vs non-HOT updates using views like pg_stat_user_tables.
I added a sentence about that, good idea.
> One minor correction: Opportunistic pruning isn't limited to heap-only
> tuples -- "HOT pruning" is actually a bit of a misnomer, that somehow
> caught on in the Postgres community. Opportunistic pruning can and
> will happen even with non-HOT updates -- the only difference on the
> heap page level is that 4 byte LP_DEAD line pointers will accumulate
> over time, which only VACUUM is able to clean up (we need LP_DEAD
> items to stick around until then as tombstones, so that index scans
> don't ever get confused). But pruning will still be able to free most
> of the space on the heap page, in almost the same way.
I see your point. I changed the benefits from pruning to no new page
item identifiers.
> There is a section about bottom-up index deletion in the docs (in the
> B-Tree internals chapter) that already references HOT. It should link
> to this new chapter now, I think.
Already done in the attached patch, I think.
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml
index 9ed148ab84..2df6559acc 100644
--- a/doc/src/sgml/acronyms.sgml
+++ b/doc/src/sgml/acronyms.sgml
@@ -299,9 +299,7 @@
<term><acronym>HOT</acronym></term>
<listitem>
<para>
- <ulink
- url="https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=HEAD";>Heap-Only
- Tuples</ulink>
+ <link linkend="storage-hot">Heap-Only Tuples</link>
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/btree.sgml b/doc/src/sgml/btree.sgml
index a9200ee52e..6f608a14bf 100644
--- a/doc/src/sgml/btree.sgml
+++ b/doc/src/sgml/btree.sgml
@@ -639,7 +639,8 @@ options(<replaceable>relopts</replaceable> <type>local_relopts *</type>) returns
accumulate and adversely affect query latency and throughput. This
typically occurs with <command>UPDATE</command>-heavy workloads
where most individual updates cannot apply the
- <acronym>HOT</acronym> optimization. Changing the value of only
+ <link linkend="storage-hot"><acronym>HOT</acronym> optimization.</link>
+ Changing the value of only
one column covered by one index during an <command>UPDATE</command>
<emphasis>always</emphasis> necessitates a new set of index tuples
— one for <emphasis>each and every</emphasis> index on the
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a186e35f00..248dbc0e26 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4381,7 +4381,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<para>
If true, queries must not use the index until the <structfield>xmin</structfield>
of this <structname>pg_index</structname> row is below their <symbol>TransactionXmin</symbol>
- event horizon, because the table may contain broken HOT chains with
+ event horizon, because the table may contain broken <link linkend="storage-hot">HOT chains</link> with
incompatible rows that they can see
</para></entry>
</row>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e2d728e0c4..e5a84ed76d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4482,7 +4482,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<listitem>
<para>
Specifies the number of transactions by which <command>VACUUM</command> and
- <acronym>HOT</acronym> updates will defer cleanup of dead row versions. The
+ <link linkend="storage-hot"><acronym>HOT</acronym> updates</link>
+ will defer cleanup of dead row versions. The
default is zero transactions, meaning that dead row versions can be
removed as soon as possible, that is, as soon as they are no longer
visible to any open transaction. You may wish to set this to a
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index cf359fa9ff..4f83970c85 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -45,7 +45,8 @@
extant versions of the same logical row; to an index, each tuple is
an independent object that needs its own index entry. Thus, an
update of a row always creates all-new index entries for the row, even if
- the key values did not change. (HOT tuples are an exception to this
+ the key values did not change. (<link linkend="storage-hot">HOT
+ tuples</link> are an exception to this
statement; but indexes do not deal with those, either.) Index entries for
dead tuples are reclaimed (by vacuuming) when the dead tuples themselves
are reclaimed.
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 023157d888..42e1e86c8a 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -749,7 +749,7 @@ CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
<para>
Index expressions are relatively expensive to maintain, because the
derived expression(s) must be computed for each row insertion
- and non-HOT update. However, the index expressions are
+ and <link linkend="storage-hot">non-HOT update.</link> However, the index expressions are
<emphasis>not</emphasis> recomputed during an indexed search, since they are
already stored in the index. In both examples above, the system
sees the query as just <literal>WHERE indexedcolumn = 'constant'</literal>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 7dbbab6f5c..6408d28c5d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4426,7 +4426,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<structfield>n_tup_upd</structfield> <type>bigint</type>
</para>
<para>
- Number of rows updated (includes HOT updated rows)
+ Number of rows updated (includes <link linkend="storage-hot">HOT updated rows</link>)
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 6bbf15ed1a..c14b2010d8 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1435,7 +1435,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
to the indicated percentage; the remaining space on each page is
reserved for updating rows on that page. This gives <command>UPDATE</command>
a chance to place the updated copy of a row on the same page as the
- original, which is more efficient than placing it on a different page.
+ original, which is more efficient than placing it on a different
+ page, and makes <link linkend="storage-hot">heap-only tuple
+ updates</link> more likely.
For a table whose entries are never updated, complete packing is the
best choice, but in heavily updated tables smaller fillfactors are
appropriate. This parameter cannot be set for TOAST tables.
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index f4b9f66589..2e1aa58e4f 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1075,4 +1075,70 @@ data. Empty in ordinary tables.</entry>
</sect2>
</sect1>
+<sect1 id="storage-hot">
+
+ <title>Heap-Only Tuples (<acronym>HOT</acronym>)</title>
+
+ <para>
+ To allow for high concurrency, <productname>PostgreSQL</productname>
+ uses <link linkend="mvcc-intro">multiversion concurrency
+ control</link> (<acronym>MVCC</acronym>) to store rows. However,
+ <acronym>MVCC</acronym> has some downsides for update queries.
+ Specifically, updates cause additional rows to be added to tables.
+ This can also require new index entries for each updated row, and
+ removal of old versions of rows can be expensive.
+ </para>
+
+ <para>
+ To help reduce the overhead of updates,
+ <productname>PostgreSQL</productname> has an optimization called
+ heap-only tuples (<acronym>HOT</acronym>). This optimization is
+ possible when:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The update does not modify any columns referenced by the table's
+ indexes, including expression and partial indexes.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ There is sufficient free space on the page containing the old row
+ for the updated row.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ In such cases, heap-only tuples provide two optimizations:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ New index entries are not needed to represent updated rows.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <link linkend="storage-page-layout">Page item identifiers</link> are
+ not needed for new rows because they are chained to older versions
+ of rows on the same page.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ In summary, heap-only tuple updates can only happen if indexed columns
+ are not updated. You can increase the chance of sufficient page space
+ for <acronym>HOT</acronym> updates by using non-default table <link
+ linkend="sql-createtable"><literal>fillfactor</literal></link> settings.
+ If you don't, <acronym>HOT</acronym> updates will still happen because
+ new rows will naturally migrate to new pages and existing pages with
+ sufficient free space for new row versions. The system view <link
+ linkend="monitoring-pg-stat-all-tables-view">pg_stat_all_tables</link>
+ allows monitoring of the occurrence of HOT and non-HOT updates.
+ </para>
+</sect1>
+
</chapter>
