Introduction
In the previous post, we covered the concepts needed to understand HOT Update, such as page layout, HOT, fillfactor, and single-page vacuuming. In this post, we use these concepts to examine the various cases in which Update works.
Update in PostgreSQL
In PostgreSQL, Update implements multiversion concurrency control (MVCC) by keeping the previous (pre-change) version of the tuple. In other words, when an Update occurs, a new version of the tuple is created, and the previous version is logically deleted (the tuple is marked as no longer valid).
Classified by what they target, MVCC-based Updates fall into the following types.
Update Case
- Update on a table with no index
- Update on a column not included in an index
- Update on a column included in an index
These cases cover practically every possible Update scenario. By running the same steps for each, we look at how pages change during an Update and how the cases differ.
For this series of tests, we set the fillfactor and column size as shown below to induce HOT Updates. (*See the HOT Update constraints below.)
📢 HOT Update constraints
① The columns referenced by the table's indexes must not be updated.
② There must be enough space to store the new tuple version on the same page as the previous version, so that the tuples before and after the Update are on one table page.
Note: Script used to check tuple state (HEAP_PAGE & INDEX_PAGE FUNCTION)
Case 1. Update on a table with no index
First, let's look at how an Update on a table with no index works.
Under the MVCC approach described above, updating a tuple in a table with no index first checks whether the page that stores the tuple has enough free space for a new tuple version. If it does, the new tuple version is created on that page.
Case 1 meets the constraints for HOT Update, so the Update optimization should kick in. Let's see what actually changes inside the page over 7 steps.
1-1. Create the test table
1-2. Run Insert
Inserting 1 tuple into the new table t1 creates LP1(0,1) and the tuple on the page.
1-3. Run Update
Updating the tuple inserted in step 1 creates Tuple v2, a new version of Tuple v1. Another Update creates yet another version, Tuple v3.
If you query the LP and tuple state of the page with HEAP_PAGE ('T1', 0), you can see, as in the figure on the left, that each tuple stores the LP of the next version in t_ctid, forming a HOT chain. The hhu (HEAP_HOT_UPDATED) and hot (HEAP_ONLY_TUPLE) values also confirm that HOT Update took place.
📢 The hhu and hot columns, defined in the script and shown in the output above, are both HOT Update flags based on t_infomask2.
- hhu (HEAP_HOT_UPDATED) : Set on a tuple in a HOT chain created by HOT Update when the next tuple linked through the chain is a HOT (heap-only tuple).
- hot (HEAP_ONLY_TUPLE) : Set on a tuple created by HOT Update when no index points to that tuple. (These tuples can be accessed from an index only indirectly, through the head of the HOT chain.)
For a table with no index at all, as in Case 1, these flags are set on every tuple except the one the head of the HOT chain points to.
1-4. Run Select ( * triggers single-page vacuuming)
When a Select on table t1 reads the page and the page is over the fillfactor threshold (60%), single-page vacuuming is triggered. Single-page vacuuming removes the dead tuples Tuple v1 and Tuple v2 from the page. However, unlike the dead tuples (items), which are actually removed, LP1(0,1), the head of the HOT chain, gets a redirect to LP3 so it can still reach the valid tuple, Tuple v3, and LP2, which is no longer needed, is changed to the
unused state.📢 The State column, defined in the script and shown in the output above, is determined by lp_flags and can be normal, dead, redirect, unused, and so on.
- unused : lp_flags=0, not in use (always lp_len=0)
- normal : lp_flags=1, in use (always lp_len>0)
- redirect : lp_flags=2, HOT Redirection (lp_len=0)
- dead : lp_flags=3, the tuple it pointed to has been removed, but the LP is still referenced from outside and cannot be reused
Only LPs in the unused state can be reused immediately.
In addition, Tuple v3 moves to the end of the free space. Because its physical location changes, the offset of the tuple that LP3 points to changes too. Moving tuples like this prevents fragmentation of the free space.
📢 Tuples created by HOT Update form a HOT chain, and the first LP included in the chain becomes the head of the HOT chain. In Case 1 the table has no index, so the head is not referenced from an index, but it still keeps the property that its value does not change.
1-5. Run Update
Updating the same tuple again creates a new version, Tuple v4. No new LP is allocated for Tuple v4; LP2, which is in the
unused state, is reused. The t_ctid of the previous version, Tuple v3, stores LP2(0,2), which points to Tuple v4, linking the HOT chain.1-6. Run VACUUM on the whole table
When the user runs Vacuum on table t1, it removes dead tuples and cleans up the related LPs. After Vacuum, Tuple v3 is removed and LP3 becomes
unused, so it can be reused.The redirect is also reset from LP3 to LP2 so that the head of the HOT chain points to the valid tuple, and the offset of LP2 is updated to reflect the physical move of Tuple v4. (Works the same as single-page vacuuming in Case 1.4)
1-7. Run Update
When another HOT Update runs, a new version, Tuple v5, is created. If the page has a reusable LP, it is reused; otherwise a new LP must be created as well. In the figure above, you can see that LP3 was reused to point to Tuple v5.
Case 2. Update on a column not included in an index
Second, let's look at updating a column that is not included in an index.
You might think that an Update on a table with an index always adds an index entry. But if the updated column is not part of the index, there is no need to add or change index entries just to create an entry with the same key value.
Case 2 meets the HOT Update constraints and does not change index entries, so HOT Update should work normally. Let's run the same steps and compare with Case 1 to see whether the behavior differs.
2-1. Create the table & index
2-2. Run Insert
Inserting a new tuple creates the tuple in the free space of the table page and adds the key value for that tuple to the index page.
2-3. Run Update
After running 2 Updates in a row on the tuple inserted in Case 2-2, the HEAP_PAGE ('T2', 0) output shows through the hhu and hot values that a HOT chain was created and HOT Update took place.
The INDEX_PAGE ('T2_IDX', 1) output also shows that the Updates did not add or change any index entries, and that the index still references ctid(0,1), the head of the HOT chain.
📢 Apart from the index page referencing LP1, the head of the HOT chain, this case is the same as the Update in Case 1-3.
2-4. Run Select ( * triggers single-page vacuuming)
When a Select on table t2 reads the page and the page is over the fillfactor threshold, single-page vacuuming is triggered. It works the same as in Case 1-4 and cleans up dead tuples and LPs.
Note, however, that the head of the HOT chain stays the same, so the index entry that points to it does not need to change.
2-5. Run Update
When the same tuple is updated again, a new version, Tuple v4, is created. LP2 is available for reuse, so it is reused to point to Tuple v4, and the t_ctid of the previous version, Tuple v3, stores LP2(0,2), which points to Tuple v4.
2-6. Run VACUUM on the whole table
When the user runs Vacuum on table t2, it removes dead tuples and cleans up the related LPs. It also sets a redirect from the head to LP2 and updates the offset of LP2 to point to the moved Tuple v4. All of this is the same as in Case 1-6.
2-7. Run Update
When another HOT Update runs, a new version, Tuple v5, is created. If the page has a reusable LP, it is reused; otherwise a new LP must be created as well. In the figure above, you can see that LP3 was reused to point to Tuple v5. All of this is the same as in Case 1-7.
Case 3. Update on a column included in an index
Finally, in Case 3, unlike Case 2, we look at updating a column that is included in an index.
Except for Case 2, an Update on a table with indexes triggers an update of every index on that table. This is because index entries must be added to reference the newly created tuple version.
This case violates the HOT Update constraints, so it works as a regular Update. Unlike Cases 1 and 2, pay attention to how a regular Update works and how it differs from HOT Update.
3-1. Create the table & index
3-2. Run Insert
Inserting a new tuple creates the tuple in the free space of the table page and adds the key value for that tuple to the index page.
3-3. Run Update
Updating the tuple inserted in 3-1 creates Tuple v2 and Tuple v3, new versions of Tuple v1. Because a column included in the index was updated, index entries pointing to the newly created tuples are added to the index page.
As the output above shows, when the Update runs as a regular Update, hhu and hot are not set and no HOT chain is formed.
📢 t_ctid is used as an indirect address (pointer) to the tuple version created by the next Update of that tuple. In a HOT Update it is a key value that forms the HOT chain, but in a regular Update like the one above, each version is accessed through its own index entry, so t_ctid is used only to check whether a tuple is the latest version.
3-4. Run Select ( * triggers single-page vacuuming)
When the page is accessed to query table t3, it is over the configured fillfactor, so single-page vacuuming is triggered. It removes the dead tuples Tuple v1 and Tuple v2, and LP1 and LP2, which pointed to them, are changed to the
dead state.Single-page vacuuming works only on the table page, so LP1 and LP2, which are currently referenced by the index, are not removed right away, and the index is not updated either.
📢 When removing a dead tuple and cleaning up the LP that pointed to it, if no index references that LP, it is marked unused so it can be reused immediately. If an index does reference the LP, it cannot be released right away, so it is marked dead, and a dead LP cannot be reused. (Once the index references are cleaned up later, for example by Vacuum, it changes to unused and is reused.)
3-5. Run Update
Updating the same tuple again creates a new version, Tuple v4, and a matching index entry is added to the index page. Entries in the index page hold information (ctid) that identifies the tuple on the table page. As the INDEX_PAGE ('T3_IDX', 1) output shows, ctid information is added to the index page every time a tuple is created. During an index scan, the ctid is checked, and if the LP on the linked table page is
dead, the scan knows that tuple version no longer exists and moves on.As Updates repeat, dead tuples increase. These dead tuples can be removed by single-page vacuuming, but the LPs they used (LP1, LP2) are marked
dead, so they cannot be reused and remain on the page.3-6. Run VACUUM on the whole table
When Vacuum runs on the whole table, it removes dead tuples and unlinks the LPs that pointed to them, just like single-page vacuuming.
However, unlike single-page vacuuming, Vacuum also cleans up indexes, so it removes all index entries that pointed to dead tuples and dead LPs. The difference is that LPs no longer referenced by an index are no longer
dead and change to the reusable state (unused).📢 Update type and Vacuum type
Tuples created by HOT Update, as in Cases 1 and 2, are not referenced directly from outside the page, so single-page vacuuming alone can clean up dead tuples and LPs and make the page reusable. This means Vacuum and single-page vacuuming did the same job.
For a regular Update, as in Case 3, however, LPs referenced from the index page can be cleaned up only by Vacuum.
In short, to fully reorganize a table page, single-page vacuuming is enough for pages with HOT Updates, but pages with regular Updates need an additional Vacuum to change dead LPs to unused.
3-7. Run Update
Updating the same tuple once more creates a new version, Tuple v5. LP1, LP2, and LP3 are available for reuse, so no new LP is created and LP1 is reused first. You can see that the information for LP1(0,1), which points to Tuple v5, was added to the index page.
Related articles
