public inbox for [email protected]help / color / mirror / Atom feed
documentation on HOT 19+ messages / 4 participants [nested] [flat]
* documentation on HOT @ 2022-02-07 01:07 Jonathan S. Katz <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Jonathan S. Katz @ 2022-02-07 01:07 UTC (permalink / raw) To: Pg Docs <[email protected]> Hi, While working on the 2020-02-10 release announcement, in order to better describe one of the fixes, I tried to find a reference in the docs that described what HOT is, how it works, etc. in a user-friendly manner. This lead me to the acronyms page[1], which lead me to a document in the repo[2]. The closest thing I found was a blog post that Laurenz Albe wrote on the topic[3]. Given the importance of HOT, it seems like this would be a good topic to document. I would suggest something higher-level for general users in the "Indexes"[4] section, and something lower-level in internals[5] (which could perhaps be derived from [2]). Thoughts on this? Thanks, Jonathan [1] https://www.postgresql.org/docs/current/acronyms.html [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=H... [3] https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ [4] https://www.postgresql.org/docs/current/indexes.html [5] https://www.postgresql.org/docs/current/internals.html Attachments: [application/pgp-signature] OpenPGP_signature (840B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-02-07 01:56 David G. Johnston <[email protected]> parent: Jonathan S. Katz <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: David G. Johnston @ 2022-02-07 01:56 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Pg Docs <[email protected]> On Sun, Feb 6, 2022 at 6:08 PM Jonathan S. Katz <[email protected]> wrote: > > Given the importance of HOT, it seems like this would be a good topic to > document. I would suggest something higher-level for general users in > the "Indexes"[4] section, and something lower-level in internals[5] > (which could perhaps be derived from [2]). > > Thoughts on this? > I'm doubting there is any disagreement that this is needed. Three of us said as much recently [1] while discussing a user question regarding our documentation for expression indexes. [1] https://www.postgresql.org/message-id/flat/CAKFQuwY2MAhXTpPNG9cBA135GAOPcZ0jK-QkqW8_VCGQDJXtjQ%40mai... I presently haven't felt moved to fill the need myself as so I was fine with at least providing a high-level summary in the glossary which we also the general user to understand the idea without having to dive into the README. The details for internals can continue to be handled there in the interest of at least getting something committed for the majority of users. David J. ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-02-07 02:29 Jonathan S. Katz <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Jonathan S. Katz @ 2022-02-07 02:29 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Pg Docs <[email protected]> On 2/6/22 8:56 PM, David G. Johnston wrote: > On Sun, Feb 6, 2022 at 6:08 PM Jonathan S. Katz <[email protected] > <mailto:[email protected]>> wrote: > > > Given the importance of HOT, it seems like this would be a good > topic to > document. I would suggest something higher-level for general users in > the "Indexes"[4] section, and something lower-level in internals[5] > (which could perhaps be derived from [2]). > > Thoughts on this? > > > I'm doubting there is any disagreement that this is needed. Three of us > said as much recently [1] while discussing a user question regarding our > documentation for expression indexes. I did try to search for previous discussion on this (not well enough apparently) and I was unable to find the thread. That is also part of the challenge with the term HOT. > I presently haven't felt moved to fill the need myself as so I was fine > with at least providing a high-level summary in the glossary which we > also the general user to understand the idea without having to dive into > the README. I don't agree that such an explanation belongs in the glossary. That feels like it would be too brief, and I think that does a disservice to the topic and our users. I agree with Bruce's point that we should have a new section (or subsection). As I mentioned in my previous post, given HOT involves indexing, I would suggest putting it there. I think that something that follows the general outline of Laurenz's post would satisfy the user requirements. It explains at a high level what HOT is, it's advantages, and how it works. > The details for internals can continue to be handled there > in the interest of at least getting something committed for the majority > of users. I do think there should be some reference of it to the docs. Even if we have a page in the "Internals" section that says "For more information on how HOT works, please see <link>". That said, is there any reasoning why the HOT README (or something similar to it) is not in the "Internals" section of the documentation, similar to other indexing topics? Thanks, Jonathan Attachments: [application/pgp-signature] OpenPGP_signature (840B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 02:02 Bruce Momjian <[email protected]> parent: Jonathan S. Katz <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 02:02 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Sun, Feb 6, 2022 at 09:29:56PM -0500, Jonathan Katz wrote: > I agree with Bruce's point that we should have a new section (or > subsection). As I mentioned in my previous post, given HOT involves > indexing, I would suggest putting it there. > > I think that something that follows the general outline of Laurenz's post > would satisfy the user requirements. It explains at a high level what HOT > is, it's advantages, and how it works. Attached is a patch that adds a new HOT section to the storage chapter. You can see the results here: https://momjian.us/tmp/pgsql/storage-hot.html -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (7.0K, 2-hot.diff) download | inline diff: 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/storage.sgml b/doc/src/sgml/storage.sgml index f4b9f66589..fec9e17985 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,65 @@ 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> + Fortunately, there is an automatic system called heap-only tuples + (<acronym>HOT</acronym>) which minimizes the downsides of + <productname>PostgreSQL</productname> updates. This optimization is + possible when: + + <itemizedlist> + <listitem> + <para> + The table's indexed columns are not modified by the update. + </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 the updated row. + </para> + </listitem> + <listitem> + <para> + Old versions of the updated row can be removed during normal + operation, including <command>SELECT</command>s, instead of requiring + periodic vacuum operations. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + In summary, to make heap-only tuple updates more + likely, indexing of frequently-updated columns + should be avoided, and the use of non-default table <link + linkend="sql-createtable"><literal>fillfactor</literal></link> settings + is recommended. + </para> +</sect1> + </chapter> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 12:51 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 12:51 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Thu, Jul 21, 2022 at 10:02:18PM -0400, Bruce Momjian wrote: > On Sun, Feb 6, 2022 at 09:29:56PM -0500, Jonathan Katz wrote: > > I agree with Bruce's point that we should have a new section (or > > subsection). As I mentioned in my previous post, given HOT involves > > indexing, I would suggest putting it there. > > > > I think that something that follows the general outline of Laurenz's post > > would satisfy the user requirements. It explains at a high level what HOT > > is, it's advantages, and how it works. > > Attached is a patch that adds a new HOT section to the storage chapter. I wasn't happy with the last paragraph so I added some more details. URL contents updated too: https://momjian.us/tmp/pgsql/storage-hot.html -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (7.2K, 2-hot.diff) download | inline diff: 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/storage.sgml b/doc/src/sgml/storage.sgml index f4b9f66589..1b947beafe 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,67 @@ 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> + Fortunately, there is an automatic system called heap-only tuples + (<acronym>HOT</acronym>) which minimizes the downsides of + <productname>PostgreSQL</productname> updates. This optimization is + possible when: + + <itemizedlist> + <listitem> + <para> + The table's indexed columns are not modified by the update. + </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 the updated row. + </para> + </listitem> + <listitem> + <para> + Old versions of the updated row can be removed during normal + operation, including <command>SELECT</command>s, instead of requiring + periodic vacuum operations. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + In summary, heap-only tuple updates can only happen if indexed + columns are not updated. You can increase the change of + <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. + </para> +</sect1> + </chapter> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 15:09 Jonathan S. Katz <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 3 replies; 19+ messages in thread From: Jonathan S. Katz @ 2022-07-22 15:09 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pg Docs <[email protected]> On 7/22/22 8:51 AM, Bruce Momjian wrote: > On Thu, Jul 21, 2022 at 10:02:18PM -0400, Bruce Momjian wrote: >> On Sun, Feb 6, 2022 at 09:29:56PM -0500, Jonathan Katz wrote: >>> I agree with Bruce's point that we should have a new section (or >>> subsection). As I mentioned in my previous post, given HOT involves >>> indexing, I would suggest putting it there. >>> >>> I think that something that follows the general outline of Laurenz's post >>> would satisfy the user requirements. It explains at a high level what HOT >>> is, it's advantages, and how it works. >> >> Attached is a patch that adds a new HOT section to the storage chapter. > > I wasn't happy with the last paragraph so I added some more details. > URL contents updated too: > > https://momjian.us/tmp/pgsql/storage-hot.html 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 :) A few suggestions: s/Fortunately, there is/To help reduce overhead, PostgreSQL has/ s/In summary, h/H/ > You can increase the change of HOT updates by using non-default table fillfactor settings. I think we should expand on this and explain how adjusting "fillfactor" will affect this. I think that may change the final sentence too. Thanks, Jonathan Attachments: [application/pgp-signature] OpenPGP_signature (840B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 16:25 David G. Johnston <[email protected]> parent: Jonathan S. Katz <[email protected]> 2 siblings, 1 reply; 19+ messages in thread From: David G. Johnston @ 2022-07-22 16:25 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <[email protected]> wrote: > On 7/22/22 8:51 AM, Bruce Momjian wrote: > > On Thu, Jul 21, 2022 at 10:02:18PM -0400, Bruce Momjian wrote: > >> On Sun, Feb 6, 2022 at 09:29:56PM -0500, Jonathan Katz wrote: > >>> I agree with Bruce's point that we should have a new section (or > >>> subsection). As I mentioned in my previous post, given HOT involves > >>> indexing, I would suggest putting it there. > >>> > >>> I think that something that follows the general outline of Laurenz's > post > >>> would satisfy the user requirements. It explains at a high level what > HOT > >>> is, it's advantages, and how it works. > >> > >> Attached is a patch that adds a new HOT section to the storage chapter. > > > > I wasn't happy with the last paragraph so I added some more details. > > URL contents updated too: > > > > https://momjian.us/tmp/pgsql/storage-hot.html > > 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 :) > > A few suggestions: > > s/Fortunately, there is/To help reduce overhead, PostgreSQL has/ > > s/In summary, h/H/ > > > You can increase the change of HOT updates by using non-default table > fillfactor settings. > > I think we should expand on this and explain how adjusting "fillfactor" > will affect this. I think that may change the final sentence too. > > I think we need to expose the information regarding columns used in predicates here. "(Here, "indexed column" means any column referenced at all in an index definition, including for example columns that are tested in a partial-index predicate but are not stored in the index.)" I get it is an implementation detail but explaining the name seems like a good thing to do as well: "Without HOT, every version of a row in an update chain has its own index entries, even if all indexed columns are the same. With HOT, a new tuple placed on the same page and with all indexed columns the same as its parent row version does not get new index entries. This means there is only one index entry for the entire update chain on the heap page. An index-entry-less tuple is marked with the HEAP_ONLY_TUPLE flag." Where the last sentence becomes: "Those index-entry-less tuples (yeah, still dislike triple-hypenation...) are thus named "Heap-Only Tuples". (I've actually incorporated this as I think it should be down below, as a lead-in to the listing of conditions for when the optimization can be used.) Then maybe "can be removed during select" should be reworded as: "No longer visible heap-only tuples can be removed during normal operation, including <command>SELECT</command>s, instead of requiring periodic vacuum operations." The original heap entry the index points to cannot be removed. "Old versions of heap-only tuples" vs. "No longer visible heap-only tuples" is probably a style choice. There are basically three different "versions" in context here though so avoiding "old versions" has some appeal to me. I'm not a fan of: "Fortunately, there is an automatic system..." I'd like to give credit to the fact we engineered a solution to the downsides, so change the lead-in paragraph to the conditions listing to be: "To mitigate these downsides PostgreSQL implements an optimization whereby sometimes only the heap tuple is created, not the index entry, when performing an update. In a case of giving things obvious and meaningful names, this is the Heap-Only Tuple (HOT) Optimization. This optimization is possible when:" David J. ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 17:05 Peter Geoghegan <[email protected]> parent: Jonathan S. Katz <[email protected]> 2 siblings, 1 reply; 19+ messages in thread From: Peter Geoghegan @ 2022-07-22 17:05 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <[email protected]> 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. 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. 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. Of course the indexes on the table are another matter -- each and every index will accumulate versions when there are many non-HOT updates, which can be a huge problem. My point is that *from the point of view of an affected heap page*, pruning (whether opportunistic or performed by VACUUM) can go ahead without HOT tuples/updates, and isn't really that different in terms of the amount of space freed each time. Bottom-up index deletion is effective is no small part because pruning was always pretty effective even with non-HOT updates, except within indexes -- it pretty much compensates for that remaining problem. The heuristics that it uses in the nbtree code work quite well because of the natural tendency of those 4 byte LP_DEAD item pointers to concentrate on individual heap pages. We can fit so many more 4 byte LP_DEAD items than full sized live heap tuples that it becomes pretty obvious (from the point of view of the nbtree code that starts out by looking at an index leaf page) which pointed-to heap blocks are likely to enable it to free space via index tuple deletes -- it often visits the heap pages with the most TIDs. Before bottom-up index deletion was added, I would notice that pgbench variant workloads with non-HOT updates would result in a pgbench_accounts table that is maybe 1%-2% larger after a few hours, while the indexes on the same table would grow by 100% - 200%. Opportunistic pruning would work pretty well for the heap, but wouldn't do anything at all to control bloat in indexes. So opportunistic pruning was always very effective with non-HOT updates in one important way, but totally ineffective in another way. 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. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 17:07 Bruce Momjian <[email protected]> parent: Jonathan S. Katz <[email protected]> 2 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 17:07 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 11:09:49AM -0400, Jonathan Katz wrote: > On 7/22/22 8:51 AM, Bruce Momjian wrote: > > URL contents updated too: > > > > https://momjian.us/tmp/pgsql/storage-hot.html > > 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 :) > > A few suggestions: > > s/Fortunately, there is/To help reduce overhead, PostgreSQL has/ So, I can't do that without making the later part of the sentence redundant. I adjusted it in the attached patch. > > s/In summary, h/H/ Uh, I don't want to remove that since I am restating what I said above --- do you have another approach? > > You can increase the change of HOT updates by using non-default table > fillfactor settings. > > I think we should expand on this and explain how adjusting "fillfactor" will > affect this. I think that may change the final sentence too. Okay, done in the attached patch. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (7.2K, 2-hot.diff) download | inline diff: 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/storage.sgml b/doc/src/sgml/storage.sgml index f4b9f66589..b7202e141e 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,67 @@ 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 table's indexed columns are not modified by the update. + </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 the updated row. + </para> + </listitem> + <listitem> + <para> + Old versions of the updated row can be removed during normal + operation, including <command>SELECT</command>s, instead of requiring + periodic vacuum operations. + </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. + </para> +</sect1> + </chapter> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 18:08 Bruce Momjian <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 18:08 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 09:25:43AM -0700, David G. Johnston wrote: > On Fri, Jul 22, 2022 at 8:09 AM Jonathan S. Katz <[email protected]> wrote: > I think we need to expose the information regarding columns used in predicates > here. > > "(Here, "indexed column" means any column referenced > at all in an index definition, including for example columns that are > tested in a partial-index predicate but are not stored in the index.)" Okay, I clarified this in the attached patch. > I get it is an implementation detail but explaining the name seems like a good > thing to do as well: > > "Without HOT, every version of a row in an update chain has its own index > entries, even if all indexed columns are the same. With HOT, a new tuple > placed on the same page and with all indexed columns the same as its > parent row version does not get new index entries. This means there is > only one index entry for the entire update chain on the heap page. > An index-entry-less tuple is marked with the HEAP_ONLY_TUPLE flag." I don't see how the chain is useful for people trying to understand how to benefit from this feature. > Where the last sentence becomes: "Those index-entry-less tuples (yeah, still > dislike triple-hypenation...) are thus named "Heap-Only Tuples". > > (I've actually incorporated this as I think it should be down below, as a > lead-in to the listing of conditions for when the optimization can be used.) > > Then maybe "can be removed during select" should be reworded as: > > "No longer visible heap-only tuples can be removed during normal > operation, including <command>SELECT</command>s, instead of requiring > periodic vacuum operations." I added a no-longer-visible qualifier to the patch. > The original heap entry the index points to cannot be removed. "Old versions of > heap-only tuples" vs. "No longer visible heap-only tuples" is probably a style > choice. There are basically three different "versions" in context here though > so avoiding "old versions" has some appeal to me. > > I'm not a fan of: > > "Fortunately, there is an automatic system..." > > I'd like to give credit to the fact we engineered a solution to the downsides, > so change the lead-in paragraph to the conditions listing to be: Yeah, good point. We didn't stumble upon this feature. I have adjusted that wording. > "To mitigate these downsides PostgreSQL implements an optimization whereby > sometimes only the heap tuple is created, not the index entry, when performing > an update. In a case of giving things obvious and meaningful names, this is > the Heap-Only Tuple (HOT) Optimization. This optimization is possible when:" Sorry, I don't like the above since it isn't precise and the "In a case of giving things obvious and meaningful names" seems odd. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (7.3K, 2-hot.diff) download | inline diff: 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/storage.sgml b/doc/src/sgml/storage.sgml index f4b9f66589..0c4c3ed7c2 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,68 @@ 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> + Old no-longer-visible versions of the updated rows can be removed + during normal operation, including <command>SELECT</command>s, + instead of requiring periodic vacuum operations. + </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. + </para> +</sect1> + </chapter> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 19:04 Bruce Momjian <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 19:04 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> 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 <[email protected]> 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 <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (8.4K, 2-hot.diff) download | inline diff: 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> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 21:11 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-22 21:11 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 01:07:41PM -0400, Bruce Momjian wrote: > > > You can increase the change of HOT updates by using non-default table > > fillfactor settings. > > > > I think we should expand on this and explain how adjusting "fillfactor" will > > affect this. I think that may change the final sentence too. > > Okay, done in the attached patch. I have improved the wording of the last paragraph in this patch. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (8.4K, 2-hot.diff) download | inline diff: 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..9736a51ffe 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 columns used by + indexes are not updated. You can increase the likelihood of sufficient + page space for <acronym>HOT</acronym> updates by decreasing a table's + <link linkend="sql-createtable"><literal>fillfactor</literal></link>. + 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> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-22 22:33 Peter Geoghegan <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Peter Geoghegan @ 2022-07-22 22:33 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 2:11 PM Bruce Momjian <[email protected]> wrote: > I have improved the wording of the last paragraph in this patch. I think that it would be worth prominently explaining where heap-only tuples get their name from: it comes from the fact there are (by definition) no entries for a heap-only tuple in any index, ever. Indexes are nevertheless capable of locating heap-only tuples during index scans, by dealing with a little additional indirection: they must traverse groups of related tuple versions, all for the same logical row that was HOT updated one or more times -- this group of related tuples is called a HOT chain. This seems like a useful thing to emphasize because it places the emphasis on what *doesn't* happen. Mostly what doesn't happen in indexes. New item identifiers actually *are* needed for heap-only tuples (perhaps we could get away with it, but we don't). However, that doesn't really matter too much in practice. Heap-only tuples can still have their line pointers set to LP_UNUSED directly during pruning, without having to be set to LP_DEAD for a time first (a situation which VACUUM alone can correct by setting the LP_DEAD items to LP_UNUSED during its second heap pass). So heap-only tuples "skip the step" where they have to become LP_DEAD stubs/tombstones. Which is possible precisely because indexes don't need to be considered (they're "heap-only"). I agree that pruning should be discussed here, though -- I wouldn't go as far as treating pruning as 100% unrelated to HOT. Perhaps something along the lines of this works: "It is possible for opportunistic pruning to completely remove all bloat caused by HOT updates (bloat from HOT chains), without leaving any residual garbage that only VACUUM is capable of cleaning up. Pruning a page affected by non-HOT updates or deletes is somewhat less effective, though, because small tombstone items (dead item identifiers) must remain until such time as VACUUM can verify that no remaining index tuples reference the items." Again, the emphasis is on what *doesn't* have to happen because indexes aren't making life hard for us. From the point of view of indexes, ignorance is bliss. The really nice important point about pruning and HOT is that it becomes possible (with care from the DBA and application) to practically eliminate the role of VACUUM. We may not even require a little help from VACUUM, under ideal conditions. -- Peter Geoghegan ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-23 15:51 Bruce Momjian <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-23 15:51 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Fri, Jul 22, 2022 at 03:33:20PM -0700, Peter Geoghegan wrote: > On Fri, Jul 22, 2022 at 2:11 PM Bruce Momjian <[email protected]> wrote: > > I have improved the wording of the last paragraph in this patch. > > I think that it would be worth prominently explaining where heap-only > tuples get their name from: it comes from the fact there are (by > definition) no entries for a heap-only tuple in any index, ever. > Indexes are nevertheless capable of locating heap-only tuples during > index scans, by dealing with a little additional indirection: they > must traverse groups of related tuple versions, all for the same > logical row that was HOT updated one or more times -- this group of > related tuples is called a HOT chain. > > This seems like a useful thing to emphasize because it places the > emphasis on what *doesn't* happen. Mostly what doesn't happen in > indexes. > > New item identifiers actually *are* needed for heap-only tuples > (perhaps we could get away with it, but we don't). However, that > doesn't really matter too much in practice. Heap-only tuples can still > have their line pointers set to LP_UNUSED directly during pruning, > without having to be set to LP_DEAD for a time first (a situation > which VACUUM alone can correct by setting the LP_DEAD items to > LP_UNUSED during its second heap pass). > > So heap-only tuples "skip the step" where they have to become LP_DEAD > stubs/tombstones. Which is possible precisely because indexes don't > need to be considered (they're "heap-only"). Good points. I have updated the attached patch and URL to mention that HOT rows are _completely_ removed, and why that is possible, and I clarified the page item identifier mention. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (8.6K, 2-hot.diff) download | inline diff: 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..c9636064f5 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,72 @@ 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> + Old versions of updated rows can be completely removed during normal + operation, including <command>SELECT</command>s, instead of requiring + periodic vacuum operations. This removal is possible because indexes + do not reference their <link linkend="storage-page-layout">page + item identifiers</link>. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + In summary, heap-only tuple updates can only happen if columns used by + indexes are not updated. You can increase the likelihood of sufficient + page space for <acronym>HOT</acronym> updates by decreasing a table's + <link linkend="sql-createtable"><literal>fillfactor</literal></link>. + 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> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-23 18:33 Peter Geoghegan <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 2 replies; 19+ messages in thread From: Peter Geoghegan @ 2022-07-23 18:33 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Sat, Jul 23, 2022 at 8:51 AM Bruce Momjian <[email protected]> wrote: > Good points. I have updated the attached patch and URL to mention that > HOT rows are _completely_ removed, and why that is possible, and I > clarified the page item identifier mention. I think that this version looks very good, but I do have some minor notes: * You wrote "Specifically, updates cause additional rows to be added to tables." Perhaps this could be rephrased: "Specifically, updates add new physical tuples to tables to represent each new version." I think that the term "row" should only refer to the simple/abstract idea of a row from a table, while the term tuple should be preferred when referring to a physical embodiment of a row, like one version of a row. Perhaps it's worth following that convention across the board here (not just in this sentence that I have highlighted). * You wrote "This can also require new index entries for each updated row, and removal of old versions of rows can be expensive" I believe that the operative word in this sentence (which appears in the first paragraph) is "can". I think that it would be good to go just a bit further with that. Maybe add another sentence immediately afterwards that conveys "and now we're going to discuss when and how new versions from updates can sometimes avoid the need for a new round of index entries". * You wrote "New index entries are not needed to represent updated rows" It seems to me that this undersells the key benefit. You could perhaps add another sentence. Something like: "This avoids the immediate cost of adding new successor versions to each and every index, and avoids the cost of removing the obsolete versions from each and every index later on." * You refer to opportunistic pruning as something that happens "during normal operation", but that doesn't seem to get the idea of "opportunistic" across. It seems like it would be worth writing a sentence or two more on this, just to get that aspect across. Opportunistic cleanup occurs when a query happens to notice that a heap page that it had to read as part of query processing needed to be cleaned up in passing. We do it there and then because it happens to be relatively cheap and convenient to do it that way. That sort of thing. Overall, I think that this is suitable to commit, and I don't want to make too much of a fuss. It's great that we're doing this. Thanks -- Peter Geoghegan ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-23 19:12 David G. Johnston <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 1 reply; 19+ messages in thread From: David G. Johnston @ 2022-07-23 19:12 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Bruce Momjian <[email protected]>; Jonathan S. Katz <[email protected]>; Pg Docs <[email protected]> On Sat, Jul 23, 2022 at 11:34 AM Peter Geoghegan <[email protected]> wrote: > On Sat, Jul 23, 2022 at 8:51 AM Bruce Momjian <[email protected]> wrote: > > Good points. I have updated the attached patch and URL to mention that > > HOT rows are _completely_ removed, and why that is possible, and I > > clarified the page item identifier mention. > > I think that this version looks very good, but I do have some minor notes: > > * You wrote "Specifically, updates cause additional rows to be added to > tables." > > Perhaps this could be rephrased: "Specifically, updates add new > physical tuples to tables to represent each new version." > > I think that the term "row" should only refer to the simple/abstract > idea of a row from a table, while the term tuple should be preferred > when referring to a physical embodiment of a row, like one version of > a row. Perhaps it's worth following that convention across the board > here (not just in this sentence that I have highlighted). > I concur, suggesting the following: "Specifically, updates result in multiple rows versions (tuples) existing on the table." "There is sufficient free space on the page containing the old tuple for the updated tuple." "Old tuples can be completely removed..." > Overall, I think that this is suitable to commit, and I don't want to > make too much of a fuss. It's great that we're doing this. > > Agreed. The other suggestion listed are not clear-cut winners in my mind. The following, though, seems to just come out of nowhere. It would be better setup as a "(See <link> for why this is possible.)" instead of dropping "page item identifiers" on the reader. + This removal is possible because indexes + do not reference their <link linkend="storage-page-layout">page + item identifiers</link>. As a related thought, this has done a great job of being usable for a DBA operating at a high-level of system knowledge and interaction. I don't think burying it in storage.sgml is desirable, Maybe "Performance Tips" under "Avoid Unnecessary Indexes" (yes, a bit of a stretch, but nothing else seems to fit better, except maybe in concurrency control since we are discussing overcoming the limitation of our concurrency control choice. Summary paragraph: "can only happen if" => "can only be created if" David J. ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-26 23:25 Bruce Momjian <[email protected]> parent: Peter Geoghegan <[email protected]> 1 sibling, 0 replies; 19+ messages in thread From: Bruce Momjian @ 2022-07-26 23:25 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David G. Johnston <[email protected]>; Pg Docs <[email protected]> On Sat, Jul 23, 2022 at 11:33:40AM -0700, Peter Geoghegan wrote: > On Sat, Jul 23, 2022 at 8:51 AM Bruce Momjian <[email protected]> wrote: > > Good points. I have updated the attached patch and URL to mention that > > HOT rows are _completely_ removed, and why that is possible, and I > > clarified the page item identifier mention. > > I think that this version looks very good, but I do have some minor notes: > > * You wrote "Specifically, updates cause additional rows to be added to tables." > > Perhaps this could be rephrased: "Specifically, updates add new > physical tuples to tables to represent each new version." Uh, that seems more confusing than what I have. I also considered "tuples", but if you are saying "old version of a row", you are taking about an old version of a logical row, not an old version of a physical tuple, really. > I think that the term "row" should only refer to the simple/abstract > idea of a row from a table, while the term tuple should be preferred > when referring to a physical embodiment of a row, like one version of > a row. Perhaps it's worth following that convention across the board > here (not just in this sentence that I have highlighted). Yes, if we were talking about tuples unrelated to the versions of the rows they represent, then yes, it would make sense. > * You wrote "This can also require new index entries for each updated > row, and removal of old versions of rows can be expensive" > > I believe that the operative word in this sentence (which appears in > the first paragraph) is "can". I think that it would be good to go > just a bit further with that. Maybe add another sentence immediately > afterwards that conveys "and now we're going to discuss when and how > new versions from updates can sometimes avoid the need for a new round > of index entries". I ended up adding index cleanup further up in the text --- please see my patch in the next email I send in this thread. > > * You wrote "New index entries are not needed to represent updated rows" > > It seems to me that this undersells the key benefit. You could perhaps > add another sentence. Something like: "This avoids the immediate cost > of adding new successor versions to each and every index, and avoids > the cost of removing the obsolete versions from each and every index > later on." Same. > > * You refer to opportunistic pruning as something that happens "during > normal operation", but that doesn't seem to get the idea of > "opportunistic" across. > > It seems like it would be worth writing a sentence or two more on > this, just to get that aspect across. Opportunistic cleanup occurs > when a query happens to notice that a heap page that it had to read as > part of query processing needed to be cleaned up in passing. We do it > there and then because it happens to be relatively cheap and > convenient to do it that way. That sort of thing. Yes, we need that, but not in this section --- I would like to it though. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-07-26 23:29 Bruce Momjian <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 19+ messages in thread From: Bruce Momjian @ 2022-07-26 23:29 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Jonathan S. Katz <[email protected]>; Pg Docs <[email protected]> On Sat, Jul 23, 2022 at 12:12:38PM -0700, David G. Johnston wrote: > I concur, suggesting the following: > > "Specifically, updates result in multiple rows versions (tuples) existing on > the table." See my reply to Peter G. about tuples. > "There is sufficient free space on the page containing the old tuple for the > updated tuple." Same. > "Old tuples can be completely removed..." It is really old row versions, not old tuples since tuples, in my mind, don't have a chain of versions. There are cannot-be-viewed-by-anyone tuples, but that is not the point here. > Overall, I think that this is suitable to commit, and I don't want to > make too much of a fuss. It's great that we're doing this. > > > > Agreed. The other suggestion listed are not clear-cut winners in my mind. > > The following, though, seems to just come out of nowhere. It would be better > setup as a "(See <link> for why this is possible.)" instead of dropping "page > item identifiers" on the reader. > > + This removal is possible because indexes > + do not reference their <link linkend="storage-page-layout">page > + item identifiers</link>. I added parentheses around that. > > As a related thought, this has done a great job of being usable for a DBA > operating at a high-level of system knowledge and interaction. I don't think > burying it in storage.sgml is desirable, Maybe "Performance Tips" under "Avoid > Unnecessary Indexes" (yes, a bit of a stretch, but nothing else seems to fit > better, except maybe in concurrency control since we are discussing overcoming > the limitation of our concurrency control choice. Uh, not sure. Anyone else have an opinion? > Summary paragraph: > "can only happen if" => "can only be created if" Yes, good point. Updated patch attached. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (8.6K, 2-hot.diff) download | inline diff: 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..e5b9f3f1ff 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1075,4 +1075,74 @@ 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 require new versions of rows to be added to + tables. This can also require new index entries for each updated row, + and removal of old versions of rows and their index entries 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> + Old versions of updated rows can be completely removed during normal + operation, including <command>SELECT</command>s, instead of requiring + periodic vacuum operations. (This is possible because indexes + do not reference their <link linkend="storage-page-layout">page + item identifiers</link>.) + </para> + </listitem> + </itemizedlist> + </para> + + <para> + In summary, heap-only tuple updates can only be created + if columns used by indexes are not updated. You can + increase the likelihood of sufficient page space for + <acronym>HOT</acronym> updates by decreasing a table's <link + linkend="sql-createtable"><literal>fillfactor</literal></link>. + 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> ^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: documentation on HOT @ 2022-08-12 19:07 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 19+ messages in thread From: Bruce Momjian @ 2022-08-12 19:07 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Peter Geoghegan <[email protected]>; Jonathan S. Katz <[email protected]>; Pg Docs <[email protected]> On Tue, Jul 26, 2022 at 07:29:10PM -0400, Bruce Momjian wrote: > > Summary paragraph: > > "can only happen if" => "can only be created if" > > Yes, good point. > > Updated patch attached. I applied this patch back to PG 11. (PG 10 had too many conflicts.) I also added a mention about how indexes can prevent HOT to the index introduction section; applied patch of that addition attached. -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson Attachments: [text/x-diff] hot.diff (649B, 2-hot.diff) download | inline diff: diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 023157d888..2a70e02f7c 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -103,7 +103,9 @@ CREATE INDEX test1_id_index ON test1 (id); <para> After an index is created, the system has to keep it synchronized with the - table. This adds overhead to data manipulation operations. + table. This adds overhead to data manipulation operations. Indexes can + also prevent the creation of <link linkend="storage-hot">heap-only + tuples</link>. Therefore indexes that are seldom or never used in queries should be removed. </para> ^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2022-08-12 19:07 UTC | newest] Thread overview: 19+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-02-07 01:07 documentation on HOT Jonathan S. Katz <[email protected]> 2022-02-07 01:56 ` David G. Johnston <[email protected]> 2022-02-07 02:29 ` Jonathan S. Katz <[email protected]> 2022-07-22 02:02 ` Bruce Momjian <[email protected]> 2022-07-22 12:51 ` Bruce Momjian <[email protected]> 2022-07-22 15:09 ` Jonathan S. Katz <[email protected]> 2022-07-22 16:25 ` David G. Johnston <[email protected]> 2022-07-22 18:08 ` Bruce Momjian <[email protected]> 2022-07-22 17:05 ` Peter Geoghegan <[email protected]> 2022-07-22 19:04 ` Bruce Momjian <[email protected]> 2022-07-22 17:07 ` Bruce Momjian <[email protected]> 2022-07-22 21:11 ` Bruce Momjian <[email protected]> 2022-07-22 22:33 ` Peter Geoghegan <[email protected]> 2022-07-23 15:51 ` Bruce Momjian <[email protected]> 2022-07-23 18:33 ` Peter Geoghegan <[email protected]> 2022-07-23 19:12 ` David G. Johnston <[email protected]> 2022-07-26 23:29 ` Bruce Momjian <[email protected]> 2022-08-12 19:07 ` Bruce Momjian <[email protected]> 2022-07-26 23:25 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox