PostgreSQL et updates heap-only-tuples - partie 2
Voici une série d’articles qui va porter sur une nouveauté de la version 11.
Durant le développement de cette version, une fonctionnalité a attiré mon attention. On peut la retrouver dans les 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)
J’avoue que ce n’est pas très explicite et cette fonctionnalité nécessite quelques connaissances sur le fonctionnement du moteur que je vais essayer d’expliquer à travers plusieurs articles :
- Fonctionnement du MVCC et update heap-only-tuples
- Quand le moteur ne fait pas d’update heap-only-tuple et présentation de la nouveauté de la version 11
- Impact sur les performances
Cette fonctionnalité a été désactivée en 11.1 car elle pouvait conduire à des crash d’instance1. J’ai tout de même choisi de publier ces articles car ils permettent de comprendre le mécanisme des updates HOT et le gain que pourrait apporter cette fonctionnalité.
Je remercie au passage Guillaume Lelarge pour la relecture de cet article ;).
L’article précédent présentait le fonctionnement des UDATES heap-only-tuple. Dans cet article nous allons voir le cas où Postgres ne fait pas d’UPDATE heap-ony-tuple. Ce qui nous permettra d’aborder la fonctionnalité qui aurait du arriver dans la version 11.
Cas avec un index sur une colonne mise à jour
Reprenons l’exemple précédent et rajoutons une colonne indexée :
1ALTER TABLE t3 ADD COLUMN c3 int;
2CREATE INDEX ON t3(c3);
Les UPDATE précédents portaient sur une colonne non-indexée. Que se passe-t-il si l’UPDATE porte sur c3 ?
État de la table et des index avant 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)
Pas de changement sur la table car la colonne c3 ne contient que des nulls, on
peut le constater en observant l’index t3_c3_idx où nulls est à true sur
chaque ligne.
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)
On remarque bien la nouvelle entrée dans l’index portant sur c3. La table contient
bien un nouvel enregistrement. En revanche, l’index t3_c1_idx a également été
mis à jour. Entraînant l’ajout d’une troisième entrée, même si la valeur de la
colonne c1 n’a pas changée.
Après un 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)
Le moteur a nettoyé les index et la table. La première ligne de la table n’a plus
le flag REDIRECT.
Nouveauté de la version : 11 heap-only-tuple (HOT) avec index fonctionnels
Lorsqu’un index fonctionnel porte sur la colonne modifiée, il peut arriver que le résultat de l’expression reste inchangé malgré la mise à jour de la colonne. La clé dans l’index serait donc inchangée.
Prenons un exemple : un index fonctionnel sur une clé spécifique d’un objet JSON.
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-- changement qui ne porte pas sur prenom, on change que la ville
8UPDATE t4 SET c1 = '{"ville": "valence (#soleil)", "prenom": "guillaume"}' WHERE c2=2;
9SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
10 pg_stat_get_xact_tuples_hot_updated
11-------------------------------------
12 0
13(1 row)
14
15UPDATE t4 SET c1 = '{"ville": "nantes", "prenom": "guillaume"}' WHERE c2=2;
16SELECT pg_stat_get_xact_tuples_hot_updated('t4'::regclass);
17 pg_stat_get_xact_tuples_hot_updated
18-------------------------------------
19 0
20(1 row)
La fonction pg_stat_get_xact_tuples_hot_updated indique le nombre de lignes mises
à jour par le mécanisme HOT.
Les deux UPDATE n’ont fait que modifier la clé “ville” et pas la clé “prenom”. Ce qui n’entraîne pas de modification de l’index car il n’indexe que la clé “prenom”.
Le moteur n’a pas pu faire d’HOT. En effet, pour lui, l’UPDATE a porté sur la colonne et l’index doit être mis à jour.
Avec la version 11, le moteur est capable de constater que le résultat de l’expression ne change pas. Effectuons le même test sur la 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-- changement qui ne porte pas sur prenom
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 1
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 2
21(1 row)
Cette fois, le moteur a bien utilisé le mécanisme HOT. On peut le vérifier en regardant le contenu physique de l’index avec 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)
Ce comportement peut se contrôler grâce à une nouvelle option lors de la création
de l’index : recheck_on_update.
Par défaut à on, le moteur effectue la vérification du résultat de l’expression
pour faire un UPDATE HOT. On peut le paramétrer à off s’il y a de fortes chances
pour que le résultat de l’expression change lors d’un UPDATE. Cela permet d’éviter
d’exécuter l’expression inutilement.
A noter également que le moteur évite l’évaluation de l’expression si son coût est supérieur à 1000.
Dans le troisième et dernier article, nous verrons un cas un peu plus concret pour voir l’impact en terme de performance et de volumétrie.
J’ai occupé pendant 7 ans divers postes d’ingénieur système et réseau. Et c’est à partir de 2013 que j’ai commencé à mettre les doigts dans PostgreSQL. J’ai occupé des postes de consultant, formateur, mais également DBA de production (Doctolib, Peopledoc).
Ce blog me permet de partager mes connaissances et trouvailles, ainsi que mes interventions et conférences.