Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEx4h-0006yg-PJ for pgsql-docs@arkaria.postgresql.org; Fri, 22 Jul 2022 18:08:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oEx4g-00085Q-3a for pgsql-docs@arkaria.postgresql.org; Fri, 22 Jul 2022 18:08:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEx4f-00084f-Qs for pgsql-docs@lists.postgresql.org; Fri, 22 Jul 2022 18:08:29 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oEx4c-0003jE-N9; Fri, 22 Jul 2022 18:08:29 +0000 Received: from bruce by momjian.us with local (Exim 4.94.2) (envelope-from ) id 1oEx4b-009Crk-0Q; Fri, 22 Jul 2022 14:08:25 -0400 Date: Fri, 22 Jul 2022 14:08:24 -0400 From: Bruce Momjian To: "David G. Johnston" Cc: "Jonathan S. Katz" , Pg Docs Subject: Re: documentation on HOT Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Xb+7Jcw9oO4WVjcm" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Xb+7Jcw9oO4WVjcm Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit 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 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 SELECTs, 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 https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson --Xb+7Jcw9oO4WVjcm Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="hot.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 @@ HOT - Heap-Only - Tuples + Heap-Only Tuples 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(relopts local_relopts *) returns accumulate and adversely affect query latency and throughput. This typically occurs with UPDATE-heavy workloads where most individual updates cannot apply the - HOT optimization. Changing the value of only + HOT optimization. + Changing the value of only one column covered by one index during an UPDATE always necessitates a new set of index tuples — one for each and every 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$<iteration count>:&l If true, queries must not use the index until the xmin of this pg_index row is below their TransactionXmin - event horizon, because the table may contain broken HOT chains with + event horizon, because the table may contain broken HOT chains with incompatible rows that they can see 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 num_sync ( HOT updates + 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. (HOT + tuples 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)); 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 non-HOT update. However, the index expressions are not recomputed during an indexed search, since they are already stored in the index. In both examples above, the system sees the query as just WHERE indexedcolumn = 'constant' 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 n_tup_upd bigint - Number of rows updated (includes HOT updated rows) + Number of rows updated (includes HOT updated rows) 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. + + + Heap-Only Tuples (<acronym>HOT</acronym>) + + + To allow for high concurrency, PostgreSQL + uses multiversion concurrency + control (MVCC) to store rows. However, + MVCC 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. + + + + To help reduce the overhead of updates, + PostgreSQL has an optimization called + heap-only tuples (HOT). This optimization is + possible when: + + + + + The update does not modify any columns referenced by the table's + indexes, including expression and partial indexes. + + + + + There is sufficient free space on the page containing the old row + for the updated row. + + + + + In such cases, heap-only tuples provide two optimizations: + + + + + New index entries are not needed to represent updated rows. + + + + + Old no-longer-visible versions of the updated rows can be removed + during normal operation, including SELECTs, + instead of requiring periodic vacuum operations. + + + + + + + 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 HOT updates by using non-default table fillfactor settings. + If you don't, HOT updates will still happen because + new rows will naturally migrate to new pages and existing pages with + sufficient free space for new row versions. + + + --Xb+7Jcw9oO4WVjcm--