PostgreSQL and heap-only-tuples updates - part 2
Here is a series of articles that will focus on a new feature in version 11.
During the development of this version, a feature caught my attention. It can be found in releases notes : https://www.postgresql.org/docs/11/static/release-11.html
Allow heap-only-tuple (HOT) updates for expression indexes when the values of the expressions are unchanged (Konstantin Knizhnik)
I admit that this is not very explicit and this feature requires some knowledge about how postgres works, that I will try to explain through several articles:
- How MVCC works and heap-only-tuples updates
- When postgres do not use heap-only-tuple updates and introduction to the new feature in v11
- Impact on performances
This feature was disabled in 11.1 because it could lead to instance crashes1. I chose to publish these articles because they help to understand the mechanism of HOT updates and the benefits that this feature could bring.
I thank Guillaume Lelarge for his review of this article ;).
The previous article showed how heap-only-tuple UPDATE works. In this one, we will see when Postgres does not perform heap-only-tuple UPDATE. This will allow us to approach the functionality that should have been available in version 11.
Cases with an index on an updated column
Let’s repeat the previous example and add an indexed column:
1ALTER TABLE t3 ADD COLUMN c3 int;
2CREATE INDEX ON t3(c3);
Previous UPDATE were on a non-indexed column. What happens if the update is on c3?
Content of the table and indexes before UPDATE:
1SELECT lp,lp_flags,t_data,t_ctid FROM heap_page_items(get_raw_page('t3',0));
2 lp | lp_flags | t_data | t_ctid
3----+----------+--------------------+--------
4 1 | 2 | |
5 2 | 1 | \x0200000002000000 | (0,2)
6 3 | 0 | |
7 4 | 0 | |
8 5 | 1 | \x0100000006000000 | (0,5)
9(5 rows)
10
11SELECT * FROM bt_page_items(get_raw_page('t3_c1_idx',1));
12 itemoffset | ctid | itemlen | nulls | vars | data
13------------+-------+---------+-------+------+-------------------------
14 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00
15 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00
16(2 rows)
17
18SELECT * FROM bt_page_items(get_raw_page('t3_c3_idx',1));
19 itemoffset | ctid | itemlen | nulls | vars | data
20------------+-------+---------+-------+------+------
21 1 | (0,1) | 16 | t | f |
22 2 | (0,2) | 16 | t | f |
23(2 rows)
No change on the table because the column c3 contains only nulls, we can see this
by looking at the index t3_c3_idx where nulls is true on each line.
1UPDATE t3 SET c3 = 7 WHERE c1=1;
2SELECT * FROM bt_page_items(get_raw_page('t3_c3_idx',1));
3 itemoffset | ctid | itemlen | nulls | vars | data
4------------+-------+---------+-------+------+-------------------------
5 1 | (0,3) | 16 | f | f | 07 00 00 00 00 00 00 00
6 2 | (0,1) | 16 | t | f |
7 3 | (0,2) | 16 | t | f |
8(3 rows)
9
10SELECT lp,lp_flags,t_data,t_ctid FROM heap_page_items(get_raw_page('t3',0));
11 lp | lp_flags | t_data | t_ctid
12----+----------+----------------------------+--------
13 1 | 2 | |
14 2 | 1 | \x0200000002000000 | (0,2)
15 3 | 1 | \x010000000600000007000000 | (0,3)
16 4 | 0 | |
17 5 | 1 | \x0100000006000000 | (0,3)
18(5 rows)
19
20SELECT * FROM bt_page_items(get_raw_page('t3_c1_idx',1));
21 itemoffset | ctid | itemlen | nulls | vars | data
22------------+-------+---------+-------+------+-------------------------
23 1 | (0,3) | 16 | f | f | 01 00 00 00 00 00 00 00
24 2 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00
25 3 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00
26(3 rows)
We notice the new entry in the index for c3. The table does contain a new record
On the other hand, the t3_c1_idx index has also been updated. This results in
the addition of a third entry, even if the value in column c1 has not changed.
After a vacuum:
1VACUUM t3;
2SELECT * FROM bt_page_items(get_raw_page('t3_c1_idx',1));
3 itemoffset | ctid | itemlen | nulls | vars | data
4------------+-------+---------+-------+------+-------------------------
5 1 | (0,3) | 16 | f | f | 01 00 00 00 00 00 00 00
6 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00
7(2 rows)
8
9SELECT lp,lp_flags,t_data,t_ctid FROM heap_page_items(get_raw_page('t3',0));
10 lp | lp_flags | t_data | t_ctid
11----+----------+----------------------------+--------
12 1 | 0 | |
13 2 | 1 | \x0200000002000000 | (0,2)
14 3 | 1 | \x010000000600000007000000 | (0,3)
15 4 | 0 | |
16 5 | 0 | |
17(5 rows)
18
19SELECT * FROM bt_page_items(get_raw_page('t3_c3_idx',1));
20 itemoffset | ctid | itemlen | nulls | vars | data
21------------+-------+---------+-------+------+-------------------------
22 1 | (0,3) | 16 | f | f | 07 00 00 00 00 00 00 00
23 2 | (0,2) | 16 | t | f |
24(2 rows)
Postgres cleaned the index and the table. The first line of the table no longer
has the flag REDIRECT.
New in version: 11 heap-only-tuple (HOT) with functional indexes
When a functional index is applied to the modified column, it may happen that the result of the expression remains unchanged despite the updating of the column. The key in the index would therefore remain unchanged.
Let’s take an example: a functional index on a specific key of a JSON object.
1CREATE TABLE t4 (c1 jsonb, c2 int,c3 int);
2CREATE INDEX ON t4 ((c1->>'prenom')) ;
3CREATE INDEX ON t4 (c2);
4INSERT INTO t4 VALUES ('{ "prenom":"adrien" , "ville" : "valence"}'::jsonb,1,1);
5INSERT INTO t4 VALUES ('{ "prenom":"guillaume" , "ville" : "lille"}'::jsonb,2,2);
6
7
8-- change that does not concern the first name, we change only the city
9UPDATE t4 SET c1 = '{"ville": "valence (#soleil)", "prenom": "guillaume"}' WHERE c2=2;
10SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
11 pg_stat_get_xact_tuples_hot_updated
12-------------------------------------
13 0
14(1 row)
15
16UPDATE t4 SET c1 = '{"ville": "nantes", "prenom": "guillaume"}' WHERE c2=2;
17SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
18 pg_stat_get_xact_tuples_hot_updated
19-------------------------------------
20 0
21(1 row)
The function pg_stat_get_get_xact_tuples_hot_updated indicates the number of
lines updated by the HOT mechanism.
The two UPDATE only modified the “city” key and not the “first name” key. This does not lead to a modification of the index because it only indexes the “first name” key.
Postgres could not make a HOT. Indeed, for him, the UPDATE is on the column and the index must be updated.
With version 11, postgres is able to see that the result of the expression does not change. Let’s do the same test on version 11 :
1CREATE TABLE t4 (c1 jsonb, c2 int,c3 int);
2-- CREATE INDEX ON t4 ((c1->>'prenom')) WITH (recheck_on_update='false');
3CREATE INDEX ON t4 ((c1->>'prenom')) ;
4CREATE INDEX ON t4 (c2);
5INSERT INTO t4 VALUES ('{ "prenom":"adrien" , "ville" : "valence"}'::jsonb,1,1);
6INSERT INTO t4 VALUES ('{ "prenom":"guillaume" , "ville" : "lille"}'::jsonb,2,2);
7
8
9-- changement qui ne porte pas sur prenom
10UPDATE t4 SET c1 = '{"ville": "valence (#soleil)", "prenom": "guillaume"}' WHERE c2=2;
11SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
12 pg_stat_get_xact_tuples_hot_updated
13-------------------------------------
14 1
15(1 row)
16
17UPDATE t4 SET c1 = '{"ville": "nantes", "prenom": "guillaume"}' WHERE c2=2;
18SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
19 pg_stat_get_xact_tuples_hot_updated
20-------------------------------------
21 2
22(1 row)
This time, Postgres used the HOT mechanism correctly. This can be verified by looking at the physical content of the index with pageinspect:
Version 10 :
1SELECT * FROM bt_page_items(get_raw_page('t4_expr_idx',1));
2itemoffset | ctid | itemlen | nulls | vars | data
3------------+-------+---------+-------+------+-------------------------------------------------
4 1 | (0,1) | 16 | f | t | 0f 61 64 72 69 65 6e 00
5 2 | (0,4) | 24 | f | t | 15 67 75 69 6c 6c 61 75 6d 65 00 00 00 00 00 00
6 3 | (0,3) | 24 | f | t | 15 67 75 69 6c 6c 61 75 6d 65 00 00 00 00 00 00
7 4 | (0,2) | 24 | f | t | 15 67 75 69 6c 6c 61 75 6d 65 00 00 00 00 00 00
8(4 rows)
Version 11 :
1SELECT * FROM bt_page_items(get_raw_page('t4_expr_idx',1));
2itemoffset | ctid | itemlen | nulls | vars | data
3------------+-------+---------+-------+------+-------------------------------------------------
4 1 | (0,1) | 16 | f | t | 0f 61 64 72 69 65 6e 00
5 2 | (0,2) | 24 | f | t | 15 67 75 69 6c 6c 61 75 6d 65 00 00 00 00 00 00
6(2 rows)
This behavior can be controlled by a new option when creating the index: recheck_on_update.
On by default, postgres checks the result of the expression to perform a HOT UPDATE.
It can be set to off if there is a good chance that the result of the expression
will change during an UPDATE. This avoids executing the expression unnecessarily.
Also notes that postgres avoids the evaluation of the expression if its cost is higher than 1000.
In the third and last article, we will see a more concrete case to measure impact in terms of performance and volumetry.
For seven years, I held various positions as a systems and network engineer. And it was in 2013 that I first started working with PostgreSQL. I held positions as a consultant, trainer, and also production DBA (Doctolib, Peopledoc).
This blog is a place where I share my findings, knowledge and talks.