Skip to content

04 Lesson 04: MVCC / HOT Updates and Page Pruning

Native Markdown source page: MVCC / HOT Updates and Page Pruning
Note

This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. HTML source: dba2_04_mvcc_hot.html Archive: DBA2-handouts-16-en.zip PDF pages: 26

Page 1: MVCC / HOT Updates and Page Pruning

© Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)

Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.

Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru

Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

Page 2: Topics

  • Why Vacuuming Is Not Enough
  • HOT Updates
  • Table Pruning
  • Index Pruning

Page 3: Vacuuming Is Not Enough

Dead row versions

  • lead to table and index bloat
  • Index synchronization
    • any change in a table row requires updating all indexes
  • Splits of index pages
    • reduce performance
    • increase size

Speaker notes

The multiversion concurrency control generates outdated row versions. This leads to table and index bloating which in turn reduces query performance.

If a table has indexes, they need to be synchronized with table changes. This leads to a modification of a large number of pages and also reduces performance.

The problem is aggravated by the specific implementation of B-trees in PostgreSQL: if an index page does not have enough free space for a new record, the page is split into two, and the data is redistributed between them. Frequent splits reduce performance when inserting and updating rows.

Moreover, when records are deleted (or vacuumed), two index pages are not merged back into one. Because of this, the size of the index may not decrease even if a significant part of the data has been deleted.

Regular vacuuming solves the problem of table and index bloating, but it requires significant resources. Therefore, it is beneficial to remove outdated row versions and index entries even during normal page access that is not part of a vacuum process. Also, to prevent redundant index updates during table modifications, PostgreSQL combines row versions into HOT chains. We will study them in the next slides.

Page 4: Normal Update

Source slide page 4: Normal Update{#source-figure-004 caption=“Source slide page 4: Normal Update”}

Speaker notes

Note that during a normal update, an index creates references to all tuples in the table pages.

(The exception is BRIN index, which does not contain references to individual table rows. The structure of different index types is covered in detail in QPT course.)

Why is this approach inefficient?

Any change to a row requires updating every index created on the table (even if the modified fields are not part of the index). Therefore, the more indexes a table has, the greater the performance overhead.

Page 5: HOT Update

Source slide page 5: HOT Update{#source-figure-005 caption=“Source slide page 5: HOT Update”}

Speaker notes

If an index is created on a field whose value has not changed as a result of updating the row, you can avoid creating an additional reference in the B-tree for the same key value. This is how optimization called Heap-Only Tuple Update works.

In this type of update, the index page contains only one reference to the first row version in the table page. A chain of row versions is then built within the table page:

● Rows that are modified and included in the chain are tagged with the Heap Hot Updated bit.

● Row versions that are not referenced from the index are tagged with the Heap Only Tuple bit.

● Row versions are chained together using the ctid field in the tuple header.

If an index scan accesses a heap page and finds a row version marked as Heap Hot Updated, it means that it should proceed further along the chain of row versions. (Of course, the visibility of all row versions obtained this way is checked before they are returned to the client.)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/acc ess/heap/README.HOT;hb=HEAD

Page 6: HOT Update

Values of the indexed columns must not change

  • otherwise you will have to add an index entry referencing the new row
  • version, and the version cannot be tagged as heap only
  • Update chain must remain within one page
    • no need to access other pages,
    • traversing the chain does not degrade performance
    • if the table page does not have enough space for the new version,
    • the chain is broken (as if the optimization does not work)
    • space on the page can be reserved by reducing
    • the fillfactor storage parameter of the table (100% → 10%)

Speaker notes

We emphasize that HOT updates work only if none of the values of the indexed columns are changed. Otherwise, a reference appears in an index directly to the new row version, which contradicts the idea of this optimization. Only the abovementioned BRIN index does not prevent HOT updates (it does not contain references to individual table rows).

HOT updates are also applied to tables that have no indexes at all. When any field in such a table is updated, a version chain is built.

This optimization is confined to one page. Therefore, traversing the chain does not require accessing other pages and does not degrade performance.

However, if the page does not have enough free space to place a new row version, the chain is broken. You have to make a reference from the index to the row version located on a different page.

Therefore, with frequent updates of non-indexed fields, it may be beneficial to reduce the fillfactor storage parameter. This parameter specifies the threshold percentage of space occupied on the page after which new rows cannot be inserted. The default value is 100%, it can be reduced to up to 10%. The remaining space is reserved for updates. In this case, a new row version can fit on the same page. (On the other hand, the higher the fillfactor, the more compact the records are and, accordingly, the table size is smaller.)

Page 7: HOT Update

Let’s create a table. For simplicity, we do not create an index: any update is a HOT update. Each table row consists of 2000 characters; if only Latin letters are used, then the row version will occupy 2000 bytes plus the header. We set the fillfactor parameter to 75% so that only three versions can be placed on the page and one is available for updating.

=> CREATE DATABASE mvcc_hot;
CREATE DATABASE
=> \c mvcc_hot
You are now connected to database "mvcc_hot" as user "student".
=> CREATE TABLE t(
  s char(2000)
)
WITH (fillfactor = 75, autovacuum_enabled = off);
CREATE TABLE

To examine the content of the page, we use the pageinspect extension.

=> CREATE EXTENSION pageinspect;
CREATE EXTENSION

For convenience, create a familiar view in a more compact form, supplemented with two fields:

=> CREATE VIEW t_v AS
SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256) > 0 THEN ' (c)'
         WHEN (t_infomask & 512) > 0 THEN ' (a)'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' (c)'
         WHEN (t_infomask & 2048) > 0 THEN ' (a)'
         ELSE ''
       END AS xmax,
       CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
       CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
       t_ctid
FROM heap_page_items(get_raw_page('t',0))
ORDER BY lp;
CREATE VIEW

Let’s insert a row and update it to create a new version:

=> INSERT INTO t(s) VALUES ('A');
INSERT 0 1
=> UPDATE t SET s = 'B';
UPDATE 1

Since the updated column is not included in any index (in our case, there are no indexes at all), a chain of row versions appears in the table page:

=> SELECT * FROM t_v;
 ctid  | state  |  xmin   | xmax  | hhu | hot | t_ctid
-------+--------+---------+-------+-----+-----+--------
 (0,1) | normal | 747 (c) | 748   | t   |     | (0,2)
 (0,2) | normal | 748     | 0 (a) |     | t   | (0,2)
(2 rows)

Page 8: Page 8

hhu (Heap Hot Updated) flag indicates that the ctid chain must be followed. hot (Heap Only Tuple) flag indicates that this tuple is not referenced from indexes. The chain will grow (within the page) with further changes:

=> UPDATE t SET s = 'C';
UPDATE 1
=> SELECT * FROM t_v;
 ctid  | state  |  xmin   |  xmax   | hhu | hot | t_ctid
-------+--------+---------+---------+-----+-----+--------
 (0,1) | normal | 747 (c) | 748 (c) | t   |     | (0,2)
 (0,2) | normal | 748 (c) | 749     | t   | t   | (0,3)
 (0,3) | normal | 749     | 0 (a)   |     | t   | (0,3)
(3 rows)

Page 9: Table Pruning

Performed whenever the page is accessed

  • if a previous update does not find space for a new row version
  • on the same page
  • if the page exceeds either the fillfactor or the 90% threshold
  • Operates within one table page
    • does not release item identifiers that can be referenced by indexes
    • does not update the free space map
    • does not update the visibility map

Speaker notes

When accessing a page, whether for an update or for a read, pruning can occur if PostgreSQL considers that the space on the page is running out. One of the following conditions must be met:

  1. A previous update on this page could not find enough space to place a new row version here. It sets a flag in the page’s header, indicating that pruning should be triggered the next time the page is accessed.

  2. If the page exceeds either the fillfactor or the 90% threshold, then pruning will trigger immediately.

Pruning removes row versions that are not visible in any snapshot (located beyond the database horizon), but it operates strictly within one table page. Item identifiers are not released, since they may be referenced by indexes which are stored in other pages that are not pruned.

The free space map is not updated for two reasons: to minimize overhead and because the freed space is prioritized for updates, not for inserts. The visibility map is also not updated.

The fact that a page can be pruned during reading operation means that a SELECT query can potentially change pages. This is another instance of such behavior, in addition to the updating of hint bits discussed in the Page Layout and Row Versions lesson.

Page 10: Before HOT Pruning

Source slide page 10: Before HOT Pruning{#source-figure-010 caption=“Source slide page 10: Before HOT Pruning”}

Speaker notes

HOT pruning is a specific but important case of pruning.

The figure shows the state before pruning. There are three versions of the same row in the table. The first version (0,1) is referenced by an index, while the other two, (0,2) and (0,3), are marked as heap-only tuples.

Row versions (0,1) and (0,2) are dead, not visible in any snapshot, and can be removed.

Page 11: After HOT Pruning

Source slide page 11: After HOT Pruning{#source-figure-011 caption=“Source slide page 11: After HOT Pruning”}

Speaker notes

Pruning removes outdated row versions.

In HOT updates, an index can contain only one reference to the head of the HOT chain, which is maintained within a table page. As row versions change, the item identifier must remain in place and reference the head of the chain.

Therefore, a double addressing is used. For the item identifier referenced by the index, in this case (0,1), the status “redirect” is used, redirecting to the item identifier of the proper row version.

The item identifier of the second version (0,2) is no longer needed. It gets the “unused” status and will be used when inserting some new version.

All remaining row versions are shifted together so that the free space on the page is represented by one fragment. The offsets in the item identifiers change accordingly. This prevents issues with free space fragmentation within the page.

Page 12: HOT Pruning

Let’s check how pruning works with HOT updates. Let’s update the row again:

=> UPDATE t SET s = 'D';
UPDATE 1

The page now has four row versions:

=> SELECT * FROM t_v;
 ctid  | state  |  xmin   |  xmax   | hhu | hot | t_ctid
-------+--------+---------+---------+-----+-----+--------
 (0,1) | normal | 747 (c) | 748 (c) | t   |     | (0,2)
 (0,2) | normal | 748 (c) | 749 (c) | t   | t   | (0,3)
 (0,3) | normal | 749 (c) | 750     | t   | t   | (0,4)
 (0,4) | normal | 750     | 0 (a)   |     | t   | (0,4)
(4 rows)

We’ve actually just exceeded the fillfactor threshold. 75% of the page size is 6144 bytes, and the difference between page size and upper is 8192-64=8128:

=> SELECT lower, upper, pagesize FROM page_header(get_raw_page('t',0));
 lower | upper | pagesize
-------+-------+----------
    40 |    64 |     8192
(1 row)

Check that the threshold is indeed exceeded. Update the row again:

=> UPDATE t SET s = 'E';
UPDATE 1

What changes occurred in the page?

=> SELECT * FROM t_v;
 ctid  |     state     |  xmin   | xmax  | hhu | hot | t_ctid
-------+---------------+---------+-------+-----+-----+--------
 (0,1) | redirect to 4 |         |       |     |     |
 (0,2) | normal        | 751     | 0 (a) |     | t   | (0,2)
 (0,3) | unused        |         |       |     |     |
 (0,4) | normal        | 750 (c) | 751   | t   | t   | (0,2)
(4 rows)

All outdated row versions (0,1), (0,2), and (0,3) were pruned. After that, the new row version was added into the freed space. Item identifiers of the pruned rows were freed (unused status). Meanwhile, the identifier of the first version remained in place but its status changed to redirect. Trace the references from this head version to the end of the HOT chain. Let’s perform the update a few more times:

=> UPDATE t SET s = 'F';
UPDATE 1
=> UPDATE t SET s = 'G';
UPDATE 1
=> SELECT * FROM t_v;
 ctid  |     state     |  xmin   |  xmax   | hhu | hot | t_ctid
-------+---------------+---------+---------+-----+-----+--------
 (0,1) | redirect to 4 |         |         |     |     |
 (0,2) | normal        | 751 (c) | 752 (c) | t   | t   | (0,3)
 (0,3) | normal        | 752 (c) | 753     | t   | t   | (0,5)
 (0,4) | normal        | 750 (c) | 751 (c) | t   | t   | (0,2)
 (0,5) | normal        | 753     | 0 (a)   |     | t   | (0,5)
(5 rows)

Page 13: Page 13

The following update triggers pruning again:

=> UPDATE t SET s = 'H';
UPDATE 1
=> SELECT * FROM t_v;
 ctid  |     state     |  xmin   | xmax  | hhu | hot | t_ctid
-------+---------------+---------+-------+-----+-----+--------
 (0,1) | redirect to 5 |         |       |     |     |
 (0,2) | normal        | 754     | 0 (a) |     | t   | (0,2)
 (0,3) | unused        |         |       |     |     |
 (0,4) | unused        |         |       |     |     |
 (0,5) | normal        | 753 (c) | 754   | t   | t   | (0,2)
(5 rows)

Create an index on column s and create an auxiliary view to look into it:

=> CREATE INDEX t_s ON t(s);
CREATE INDEX
=> CREATE VIEW t_s_v AS
SELECT itemoffset,
       ctid
FROM bt_page_items('t_s',1);
CREATE VIEW

When creating an index, data is not rebuilt in table pages, HOT chains remain in place:

=> SELECT * FROM t_v;
 ctid  |     state     |  xmin   |  xmax   | hhu | hot | t_ctid
-------+---------------+---------+---------+-----+-----+--------
 (0,1) | redirect to 5 |         |         |     |     |
 (0,2) | normal        | 754 (c) | 0 (a)   |     | t   | (0,2)
 (0,3) | unused        |         |         |     |     |
 (0,4) | unused        |         |         |     |     |
 (0,5) | normal        | 753 (c) | 754 (c) | t   | t   | (0,2)
(5 rows)

The index entry refers to the item identifier of the chain head:

=> SELECT * FROM t_s_v;
 itemoffset | ctid
------------+-------
          1 | (0,1)
(1 row)

Page 14: Before Pruning

Source slide page 14: Before Pruning{#source-figure-014 caption=“Source slide page 14: Before Pruning”}

Speaker notes

Of course, pruning also works for versions that appeared as a result of a regular (not HOT) update.

The figure shows the state before pruning. The table page has three versions of the same row. Two of them are dead, not visible in any snapshot and can be removed.

The index has three references to each of the three row versions.

Page 15: After Pruning

Source slide page 15: After Pruning{#source-figure-015 caption=“Source slide page 15: After Pruning”}

Speaker notes

If the condition for pruning is met, it can remove two outdated row versions (0,1) and (0,2). Then item identifiers of removed versions get the dead status, and the free space can be used to insert new row versions.

Unlike the scenario with HOT updates, the item identifiers of the removed row versions cannot be released here, since they are referenced from the index page. With index access, PostgreSQL can get (0,1) or (0,2) as a row version identifier, and then try to follow this identifier of the table page. However, by the status of the item identifier it will detect that this version no longer exists.

The fundamental point is that table pruning works only within one page and does not clean index pages.

Page 16: Index Pruning

Obsolete index entries

  • marked when executing queries
  • deleted if there is not enough space on the page
  • Bottom-up index deletion
    • if there is still not enough space, the row versions are checked

Speaker notes

If a table has several indexes and the column being updated is included in at least one of them, HOT update becomes impossible. In this case, the values of the columns included in another index may not change, but you still have to add a reference to the new row version (the index is changed physically, but not logically). To avoid frequent index pruning or rebuilding, PostgreSQL can identify obsolete index entries in advance and remove them when there is not enough space in the index page. PostgreSQL has two mechanisms to facilitate this.

If a query finds that an index entry references a row version that no longer exists or is dead, that entry is marked as dead. If a new index entry cannot be placed into a page, these marked entries are the first to be removed.

If the above method fails to prevent a page split, the bottom-up deletion, introduced in PostgreSQL 14, is activated. For index entries with the same key value, the corresponding row versions are checked. If a version is found to be missing or dead, its index entry is deleted. Checking requires accessing a table, but this is more efficient than unnecessary splitting of the index page.

Page 17: Index Pruning

Let’s reduce the table row length, truncate the table, insert seven rows, and update them:

=> ALTER TABLE t ALTER COLUMN s TYPE CHAR(500);
ALTER TABLE
=> TRUNCATE t;
TRUNCATE TABLE
=> INSERT INTO t SELECT random()::text FROM generate_series(1,7);
INSERT 0 7
=> UPDATE t SET s = random()::text;
UPDATE 7

Examine the page-by-page statistics of the index using the function from the pageinspect extension:

=> SELECT blkno, type, live_items, dead_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('t_s',1,-1) \gx
-[ RECORD 1 ]-+-----
blkno         | 1
type          | l
live_items    | 14
dead_items    | 0
avg_item_size | 512
page_size     | 8192
free_size     | 924

The index now has only one leaf page (it is also the root), which has about 900 bytes of free space left. It contains 14 index entries:

=> SELECT itemoffset, ctid, dead FROM bt_page_items('t_s',1);
 itemoffset |  ctid  | dead
------------+--------+------
          1 | (0,2)  | f
          2 | (0,6)  | f
          3 | (0,14) | f
          4 | (0,13) | f
          5 | (0,7)  | f
          6 | (0,1)  | f
          7 | (0,11) | f
          8 | (0,10) | f
          9 | (0,8)  | f
         10 | (0,3)  | f
         11 | (0,4)  | f
         12 | (0,5)  | f
         13 | (0,12) | f
         14 | (0,9)  | f
(14 rows)

These are the rows they reference:

=> SELECT * FROM t_v;

Page 18: Page 18

  ctid  | state  |  xmin   | xmax  | hhu | hot | t_ctid
--------+--------+---------+-------+-----+-----+--------
 (0,1)  | normal | 759 (c) | 760   |     |     | (0,8)
 (0,2)  | normal | 759 (c) | 760   |     |     | (0,9)
 (0,3)  | normal | 759 (c) | 760   |     |     | (0,10)
 (0,4)  | normal | 759 (c) | 760   |     |     | (0,11)
 (0,5)  | normal | 759 (c) | 760   |     |     | (0,12)
 (0,6)  | normal | 759 (c) | 760   |     |     | (0,13)
 (0,7)  | normal | 759 (c) | 760   |     |     | (0,14)
 (0,8)  | normal | 760     | 0 (a) |     |     | (0,8)
 (0,9)  | normal | 760     | 0 (a) |     |     | (0,9)
 (0,10) | normal | 760     | 0 (a) |     |     | (0,10)
 (0,11) | normal | 760     | 0 (a) |     |     | (0,11)
 (0,12) | normal | 760     | 0 (a) |     |     | (0,12)
 (0,13) | normal | 760     | 0 (a) |     |     | (0,13)
 (0,14) | normal | 760     | 0 (a) |     |     | (0,14)
(14 rows)

Seven entries refer to current table row versions, the other seven refer to dead ones, but this information cannot be obtained from the index. Therefore, the next update will split the index page. But if a query accesses the table via the index, it will have to follow the references and check the status of the table rows:

=> SET enable_seqscan = off; -- force table access via index
SET
=> SET enable_bitmapscan = off; -- directly, not via bitmap
SET
=> EXPLAIN (analyze) SELECT count(*) FROM t;
                                                     QUERY PLAN
------------------------------------------------------------------------------------------
--------------------------
 Aggregate  (cost=48.84..48.85 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=1)
   ->  Index Only Scan using t_s on t  (cost=0.14..48.74 rows=40 width=0) (actual
time=0.028..0.029 rows=7 loops=1)
         Heap Fetches: 14
 Planning Time: 0.046 ms
 Execution Time: 0.059 ms
(5 rows)

Such a query marks obsolete index entries as dead…

=> SELECT itemoffset, ctid, dead FROM bt_page_items('t_s',1);
 itemoffset |  ctid  | dead
------------+--------+------
          1 | (0,2)  | t
          2 | (0,6)  | t
          3 | (0,14) | f
          4 | (0,13) | f
          5 | (0,7)  | t
          6 | (0,1)  | t
          7 | (0,11) | f
          8 | (0,10) | f
          9 | (0,8)  | f
         10 | (0,3)  | t
         11 | (0,4)  | t
         12 | (0,5)  | t
         13 | (0,12) | f
         14 | (0,9)  | f
(14 rows)

… and if we perform an update after this,..

=> UPDATE t SET s = random()::text;
UPDATE 7

it doesn’t lead to page splitting because space can be freed up for new entries:

=> SELECT blkno, type, live_items, dead_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('t_s',1,-1) \gx

Page 19: Bottom-Up Deletion

-[ RECORD 1 ]-+-----
blkno         | 1
type          | l
live_items    | 14
dead_items    | 0
avg_item_size | 512
page_size     | 8192
free_size     | 924

Now let’s add a second text column to the table and create an index on it:

=> ALTER TABLE t ADD r CHAR(500);
ALTER TABLE
=> CREATE INDEX t_r ON t(r);
CREATE INDEX

Truncate the table again and insert rows:

=> TRUNCATE t;
TRUNCATE TABLE
=> INSERT INTO t SELECT random()::text, 1 FROM generate_series(1,12);
INSERT 0 12

Now, since both columns are indexed, HOT chains are not built during updates. If we change only the first column, the index on it grows:

=> UPDATE t SET s = random()::text;
UPDATE 12
=> SELECT blkno, type, live_items, dead_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('t_s',1,-1) \gx
-[ RECORD 1 ]-+-----
blkno         | 1
type          | l
live_items    | 11
dead_items    | 0
avg_item_size | 512
page_size     | 8192
free_size     | 2472
-[ RECORD 2 ]-+-----
blkno         | 2
type          | l
live_items    | 6
dead_items    | 0
avg_item_size | 512
page_size     | 8192
free_size     | 5052
-[ RECORD 3 ]-+-----
blkno         | 3
type          | r
live_items    | 3
dead_items    | 0
avg_item_size | 344
page_size     | 8192
free_size     | 7104
-[ RECORD 4 ]-+-----
blkno         | 4
type          | l
live_items    | 9
dead_items    | 0
avg_item_size | 512
page_size     | 8192
free_size     | 3504

However, the keys of the second index are not changed logically, so if there is not enough space, the bottom-up deletion is triggered and the page of the second index is not split:

=> SELECT blkno, type, live_items, dead_items, avg_item_size, page_size, free_size
FROM bt_multi_page_stats('t_r',1,-1) \gx

Page 20: Page 20

-[ RECORD 1 ]-+-----
blkno         | 1
type          | l
live_items    | 10
dead_items    | 0
avg_item_size | 521
page_size     | 8192
free_size     | 2892

Page 21: Takeaways

If the column being modified is not part of any index and there is free space on the page, HOT update is applied

  • If there is not enough space in the table or index page, pruning is performed automatically

Page 22: Practice

  1. Reproduce the pruning scenario without HOT updates. Check the contents of the table and index pages using the pageinspect extension.
    1. Reproduce the HOT update scenario on a table indexed by some fields.
    1. Reproduce a scenario of a HOT update where pruning does not free up enough space on the page and a new version is created on another page. How many records will be in the index in this case?

Speaker notes

  1. The query for the index page was shown in the demonstration for the Page Layout and Row Versions lesson of this module:
SELECT itemoffset, ctid
FROM bt_page_items ('index-name', 1);

Page 23: 1. Non-HOT Pruning

Let’s create a table with two fields. Set the fillfactor parameter to 75% as in the demonstration.

=> CREATE DATABASE mvcc_hot;
CREATE DATABASE
=> \c mvcc_hot
You are now connected to database "mvcc_hot" as user "student".
=> CREATE TABLE t(
  id integer GENERATED ALWAYS AS IDENTITY,
  s char(2000)
)
WITH (fillfactor = 75);
CREATE TABLE

Create an index on column s:

=> CREATE INDEX t_s ON t(s);
CREATE INDEX

As usual, we use the pageinspect extension.

=> CREATE EXTENSION pageinspect;
CREATE EXTENSION
=> CREATE VIEW t_v AS
SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256) > 0 THEN ' (c)'
         WHEN (t_infomask & 512) > 0 THEN ' (a)'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' (c)'
         WHEN (t_infomask & 2048) > 0 THEN ' (a)'
         ELSE ''
       END AS xmax,
       CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
       CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
       t_ctid
FROM heap_page_items(get_raw_page('t',0))
ORDER BY lp;
CREATE VIEW

Insert a row and update column s several times:

=> INSERT INTO t(s) VALUES ('A');
INSERT 0 1
=> UPDATE t SET s = 'B';
UPDATE 1
=> UPDATE t SET s = 'C';
UPDATE 1
=> UPDATE t SET s = 'D';
UPDATE 1

An entry has been created in the index for each of the versions:

Page 24: Page 24

=> SELECT itemoffset, ctid
FROM bt_page_items('t_s',1);
 itemoffset | ctid
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
          4 | (0,4)
(4 rows)

The presence of an index on the updated column prevented the use of HOT updates:

=> SELECT * FROM t_v;
 ctid  | state  |  xmin   |  xmax   | hhu | hot | t_ctid
-------+--------+---------+---------+-----+-----+--------
 (0,1) | normal | 748 (c) | 749 (c) |     |     | (0,2)
 (0,2) | normal | 749 (c) | 750 (c) |     |     | (0,3)
 (0,3) | normal | 750 (c) | 751     |     |     | (0,4)
 (0,4) | normal | 751     | 0 (a)   |     |     | (0,4)
(4 rows)

As in the demonstration, another row update triggers pruning:

=> UPDATE t SET s = 'E';
UPDATE 1
=> SELECT * FROM t_v;
 ctid  | state  |  xmin   | xmax  | hhu | hot | t_ctid
-------+--------+---------+-------+-----+-----+--------
 (0,1) | dead   |         |       |     |     |
 (0,2) | dead   |         |       |     |     |
 (0,3) | dead   |         |       |     |     |
 (0,4) | normal | 751 (c) | 752   |     |     | (0,5)
 (0,5) | normal | 752     | 0 (a) |     |     | (0,5)
(5 rows)

All obsolete row versions (0,1), (0,2) and (0,3) have been erased. After that, a new row version (0,5) is added to the freed space. Table pruning does not affect the index:

=> SELECT itemoffset, ctid
FROM bt_page_items('t_s',1);
 itemoffset | ctid
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
          4 | (0,4)
          5 | (0,5)
(5 rows)

For this reason, item identifiers of the erased tuples in the table are not freed but have a dead status.

2. HOT Update with Index

Remove the index on column s and add another one, on column id:

=> DROP INDEX t_s;
DROP INDEX
=> CREATE INDEX t_id ON t(id);
CREATE INDEX
=> CREATE VIEW t_id_v AS
SELECT itemoffset,
       ctid
FROM bt_page_items('t_id',1);
CREATE VIEW

Truncate the table and repeat the commands that lead to pruning:

Page 25: Page 25

=> TRUNCATE t;
TRUNCATE TABLE
=> INSERT INTO t(s) VALUES ('A');
INSERT 0 1
=> UPDATE t SET s = 'B';
UPDATE 1
=> UPDATE t SET s = 'C';
UPDATE 1
=> UPDATE t SET s = 'D';
UPDATE 1

The index page contains only one reference to the table:

=> SELECT * FROM t_id_v;
 itemoffset | ctid
------------+-------
          1 | (0,1)
(1 row)

The table page contains row versions combined in a list:

=> SELECT * FROM t_v;
 ctid  | state  |  xmin   |  xmax   | hhu | hot | t_ctid
-------+--------+---------+---------+-----+-----+--------
 (0,1) | normal | 757 (c) | 758 (c) | t   |     | (0,2)
 (0,2) | normal | 758 (c) | 759 (c) | t   | t   | (0,3)
 (0,3) | normal | 759 (c) | 760     | t   | t   | (0,4)
 (0,4) | normal | 760     | 0 (a)   |     | t   | (0,4)
(4 rows)

An index on a column does not interfere with a HOT update if that column is not updated.

3. HOT Chain Break

In a new session, begin a transaction and create a snapshot:

=> \c mvcc_hot
You are now connected to database "mvcc_hot" as user "student".
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
=> SELECT count(*) FROM t;
 count
-------
     1
(1 row)

Now perform updates in the first session:

=> UPDATE t SET s = 'I';
UPDATE 1
=> UPDATE t SET s = 'J';
UPDATE 1
=> UPDATE t SET s = 'K';
UPDATE 1
=> SELECT * FROM t_v;

Page 26: Page 26

 ctid  |     state     |  xmin   |  xmax   | hhu | hot | t_ctid
-------+---------------+---------+---------+-----+-----+--------
 (0,1) | redirect to 4 |         |         |     |     |
 (0,2) | normal        | 761 (c) | 762 (c) | t   | t   | (0,3)
 (0,3) | normal        | 762 (c) | 763     | t   | t   | (0,5)
 (0,4) | normal        | 760 (c) | 761 (c) | t   | t   | (0,2)
 (0,5) | normal        | 763     | 0 (a)   |     | t   | (0,5)
(5 rows)

The following update will no longer be able to free up space on the page:

=> UPDATE t SET s = 'L';
UPDATE 1
=> SELECT * FROM t_v;
 ctid  |     state     |  xmin   |  xmax   | hhu | hot | t_ctid
-------+---------------+---------+---------+-----+-----+--------
 (0,1) | redirect to 4 |         |         |     |     |
 (0,2) | normal        | 761 (c) | 762 (c) | t   | t   | (0,3)
 (0,3) | normal        | 762 (c) | 763 (c) | t   | t   | (0,5)
 (0,4) | normal        | 760 (c) | 761 (c) | t   | t   | (0,2)
 (0,5) | normal        | 763 (c) | 764     |     | t   | (1,1)
(5 rows)

We see a reference (1,1) to page 1. The pg_stat_all_tables view shows the number of updated rows (including those using HOT), as well as the number of updated rows that moved to a new table page. If this value is high enough during operations with a table, then consider reducing the fillfactor value.

=> SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables WHERE relname = 't';
 relname | n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd
---------+-----------+---------------+-------------------
 t       |        11 |             6 |                 1
(1 row)

Now there are two entries in the index, each pointing to the start of its own HOT chain:

=> SELECT * FROM t_id_v;
 itemoffset | ctid
------------+-------
          1 | (0,1)
          2 | (1,1)
(2 rows)
=> COMMIT;
COMMIT