Juste pour vous rappeler, nous avons déjà discuté des problèmes liés à l' isolement , fait une digression concernant la structure des données de bas niveau , puis exploré les versions des lignes et observé comment les instantanés de données sont obtenus à partir des versions des lignes.

Nous allons maintenant passer à deux problèmes étroitement liés: le vide sur la page et les mises à jour HOT . Les deux techniques peuvent être appelées optimisations; ils sont importants, mais pratiquement pas couverts dans la documentation.

Vide sur la page lors des mises à jour régulières

Lorsque vous accédez à une page pour une mise à jour ou une lecture, si PostgreSQL comprend que la page manque d'espace, il peut faire un vide rapide dans la page. Cela se produit dans l'un ou l'autre des cas:

1. Une mise à jour précédente dans cette page n'a pas trouvé suffisamment d'espace pour allouer une nouvelle version de ligne dans la même page. Une telle situation est mémorisée dans l'en-tête de la page et la prochaine fois que la page est nettoyée.
2. La page est remplie à plus de fillfactor pour cent. Dans ce cas, le vide est effectué immédiatement sans remettre à plus tard.

fillfactor est un paramètre de stockage qui peut être défini pour une table (et pour un index). PostgresSQL insère une nouvelle ligne dans une page uniquement si la page est inférieure au pourcentage fillfactor . L'espace restant est réservé aux nouveaux tuples créés à la suite de mises à jour. La valeur par défaut pour les tables est 100, c'est-à-dire qu'aucun espace n'est réservé (et la valeur par défaut pour les index est 90).

Le vide sur la page supprime les tuples qui ne sont pas visibles dans aucun instantané (ceux qui sont au-delà de l'horizon de transaction de la base de données, qui a été discuté la dernière fois ), mais le fait strictement dans une page de table. Les pointeurs vers des tuples aspirés ne sont pas libérés car ils peuvent être référencés à partir d'index et un index se trouve dans une autre page. L'espace sur la page n'atteint jamais plus d'une page de table, mais fonctionne très rapidement.

Pour les mêmes raisons, la carte d'espace libre n'est pas mise à jour; cela réserve également l'espace supplémentaire pour les mises à jour plutôt que pour les insertions. La carte de visibilité n'est pas non plus mise à jour.

Le fait qu'une page puisse être nettoyée pendant les lectures signifie qu'une requête SELECT peut entraîner le changement de pages. Il s'agit d'un cas de plus comme celui-ci, en plus d'un changement différé de bits d'indication, discuté précédemment.

Prenons un exemple de son fonctionnement. Créons une table et des index sur les deux colonnes.

 => CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75); => CREATE INDEX hot_id ON hot(id); => CREATE INDEX hot_s ON hot(s); 

Si la colonne s ne stocke que des caractères latins, chaque version de ligne occupera 2004 octets plus 24 octets d'un en-tête. Nous avons défini le fillfactor stockage fillfactor à 75%, ce qui réserve juste assez d'espace pour trois lignes.

Pour examiner commodément le contenu de la page du tableau, recréons une fonction déjà familière en ajoutant deux champs supplémentaires à la sortie:

 => CREATE FUNCTION heap_page(relname text, pageno integer) RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid) AS $$ SELECT (pageno,lp)::text::tid 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(relname,pageno)) ORDER BY lp; $$ LANGUAGE SQL; 

Créons également une fonction pour examiner la page d'index:

 => CREATE FUNCTION index_page(relname text, pageno integer) RETURNS TABLE(itemoffset smallint, ctid tid) AS $$ SELECT itemoffset, ctid FROM bt_page_items(relname,pageno); $$ LANGUAGE SQL; 

Vérifions le fonctionnement du vide sur la page. Pour ce faire, nous insérons une ligne et la modifions plusieurs fois:

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

Il y a maintenant quatre tuples dans la page:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3979 (c) | 3980 (c) | | | (0,2) (0,2) | normal | 3980 (c) | 3981 (c) | | | (0,3) (0,3) | normal | 3981 (c) | 3982 | | | (0,4) (0,4) | normal | 3982 | 0 (a) | | | (0,4) (4 rows) 

Comme prévu, nous venons de dépasser le seuil fillfactor . Cela ressort clairement de la différence entre la pagesize la pagesize et upper valeurs upper : elle dépasse le seuil égal à 75% de la taille de la page, ce qui fait 6144 octets.

 => SELECT lower, upper, pagesize FROM page_header(get_raw_page('hot',0)); 

  lower | upper | pagesize -------+-------+---------- 40 | 64 | 8192 (1 row) 

Ainsi, lorsque la page sera consultée la prochaine fois, un vide sur la page devra se produire. Vérifions cela.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | dead | | | | | (0,2) | dead | | | | | (0,3) | dead | | | | | (0,4) | normal | 3982 (c) | 3983 | | | (0,5) (0,5) | normal | 3983 | 0 (a) | | | (0,5) (5 rows) 

Tous les tuples morts (0,1), (0,2) et (0,3) sont aspirés; après cela, un nouveau tuple (0,5) est ajouté dans l'espace libéré.

Les tuples qui ont survécu à l'aspirateur sont physiquement déplacés vers les adresses élevées de la page de sorte que tout l'espace libre est représenté par une zone continue. Les valeurs des pointeurs sont modifiées en conséquence. Grâce à cela, aucun problème ne se pose avec la fragmentation de l'espace libre dans une page.

Les pointeurs vers des tuples aspirés ne peuvent pas être libérés car ils sont référencés à partir de la page d'index. Regardons la première page de l'index hot_s (car la page zéro est occupée par la métainformation):

 => SELECT * FROM index_page('hot_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) 4 | (0,4) 5 | (0,5) (5 rows) 

Nous voyons également la même image dans l'autre index:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,5) 2 | (0,4) 3 | (0,3) 4 | (0,2) 5 | (0,1) (5 rows) 

Vous remarquerez peut-être que les pointeurs vers les lignes du tableau suivent ici dans un ordre inverse, mais cela ne fait aucune différence puisque tous les tuples ont la même valeur: id = 1. Mais dans l'index précédent, les pointeurs sont classés par les valeurs de s , ce qui est essentiel.

Avec l'accès à l'index, PostgreSQL peut obtenir (0,1), (0,2) ou (0,3) comme identificateurs de tuple. Il essaiera alors d'obtenir la version de ligne appropriée à partir de la page du tableau, mais en raison de l'état "mort" du pointeur, PostgreSQL découvrira qu'une telle version n'existe plus et l'ignorera. (En fait, après avoir découvert une fois que la version d'une ligne de table n'est pas disponible, PostgreSQL changera l'état du pointeur dans la page d'index afin de ne plus accéder à la page de table.)

Il est essentiel que le vide dans la page ne fonctionne que dans une seule page de table et ne vide pas les pages d'index.

Mises à jour CHAUDES

Pourquoi est-il inutile de stocker des références à toutes les versions de lignes dans l'index?

Tout d'abord, pour tout changement de ligne, tous les index créés pour la table doivent être mis à jour: une fois qu'une nouvelle version a été créée, elle doit être référencée. Et nous devons le faire dans tous les cas, même si les champs modifiés ne sont pas indexés. Ce n'est évidemment pas très efficace.

Deuxièmement, les index accumulent des références à des tuples historiques, qui doivent ensuite être aspirés avec les tuples eux-mêmes (nous discuterons un peu plus tard comment cela est fait).

De plus, B-tree dans PostgreSQL a les spécificités de l'implémentation. Si une page d'index n'a pas suffisamment d'espace pour insérer une nouvelle ligne, la page est divisée en deux et toutes les données sont réparties entre elles. C'est ce qu'on appelle un fractionnement d'une page. Cependant, lorsque des lignes sont supprimées, les deux pages d'index ne sont pas fusionnées en une seule. Pour cette raison, la taille de l'index peut ne pas réduire même si une partie importante des données est supprimée.

Naturellement, plus les index sont créés sur une table, plus les complexités sont rencontrées.

Cependant, si une valeur est modifiée dans une colonne qui n'est pas du tout indexée, il n'est pas logique de créer une ligne d'arbre B supplémentaire qui contient la même valeur de la clé. C'est exactement ainsi que fonctionne l'optimisation appelée HOT update (Heap-Only Tuple update).

Lors de cette mise à jour, la page d'index ne contient qu'une seule ligne, qui fait référence à la toute première version de la ligne dans la page de table. Et c'est déjà à l'intérieur de la page du tableau, qu'une chaîne de tuples est organisée:

• Les lignes mises à jour qui sont dans la chaîne sont étiquetées avec le bit Heap Hot Updated.
• Les lignes qui ne sont pas référencées à partir de l'index sont étiquetées avec le bit de segment de mémoire uniquement.
• Comme d'habitude, les versions de ligne sont liées via le champ ctid .

Si pendant l'analyse d'index, PostgreSQL accède à une page de table et trouve un tuple appelé Heap Hot Updated, il comprend qu'il ne doit pas s'arrêter, mais doit suivre la chaîne HOT, en tenant compte de chaque tuple. Certes, pour tous les tuples ainsi obtenus, la visibilité est vérifiée avant de les renvoyer au client.

Pour observer le fonctionnement d'une mise à jour HOT, supprimons un index et effaçons la table.

 => DROP INDEX hot_s; => TRUNCATE TABLE hot; 

Maintenant, nous refaisons l'insertion et la mise à jour d'une ligne.

 => INSERT INTO hot VALUES (1, 'A'); => UPDATE hot SET s = 'B'; 

Et voici ce que nous voyons dans la page du tableau:

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 | t | | (0,2) (0,2) | normal | 3987 | 0 (a) | | t | (0,2) (2 rows) 

Il y a une chaîne de changements dans la page:

• L'indicateur Heap Hot Updated indique que la chaîne ctid doit être suivie.
• L'indicateur Heap Only Tuple indique que ce tuple n'est pas référencé à partir des index.

La chaîne se développera (dans la page) avec d'autres modifications:

 => UPDATE hot SET s = 'C'; => UPDATE hot SET s = 'D'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 3986 (c) | 3987 (c) | t | | (0,2) (0,2) | normal | 3987 (c) | 3988 (c) | t | t | (0,3) (0,3) | normal | 3988 (c) | 3989 | t | t | (0,4) (0,4) | normal | 3989 | 0 (a) | | t | (0,4) (4 rows) 

Mais il n'y a qu'une seule référence à la tête de chaîne dans l'index:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) (1 row) 

Pour souligner, les mises à jour HOT fonctionnent dans le cas où les champs à mettre à jour ne sont pas du tout indexés. Sinon, certains index contiendraient une référence directement à une nouvelle version de ligne, ce qui est incompatible avec le concept de cette optimisation.

L'optimisation ne fonctionne que sur une seule page et, par conséquent, une marche supplémentaire dans la chaîne n'a pas besoin d'accéder à d'autres pages et n'affecte pas les performances.

Vide sur la page pendant les mises à jour HOT

Passer l'aspirateur pendant les mises à jour HOT est un cas spécial mais important de vide sur la page.

Comme précédemment, nous avons déjà dépassé le seuil fillfactor , donc la prochaine mise à jour doit provoquer un vide dans la page. Mais cette fois, il y a une chaîne de mises à jour dans la page. La tête de cette chaîne HOT doit toujours rester où elle est puisqu'elle est référencée par l'index, tandis que les autres pointeurs peuvent être libérés: ils sont connus pour n'avoir aucune référence de l'extérieur.

Afin de ne pas toucher le pointeur de tête, un adressage indirect est utilisé: le pointeur référencé par l'index - (0,1) dans ce cas - acquiert le statut de "redirection", qui redirige vers le tuple approprié.

 => UPDATE hot SET s = 'E'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | normal | 3989 (c) | 3990 | t | t | (0,2) (4 rows) 

Notez que:

• Les tuples (0,1), (0,2) et (0,3) ont été aspirés.
• Le pointeur de tête (0,1) reste, mais il a acquis le statut de "redirection".
• La nouvelle version de ligne a écrasé (0,2) car il n'y avait aucune référence à ce tuple à coup sûr, et le pointeur a été libéré (état "inutilisé").

Faisons une mise à jour plusieurs fois de plus:

 => UPDATE hot SET s = 'F'; => UPDATE hot SET s = 'G'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 4 | | | | | (0,2) | normal | 3990 (c) | 3991 (c) | t | t | (0,3) (0,3) | normal | 3991 (c) | 3992 | t | t | (0,5) (0,4) | normal | 3989 (c) | 3990 (c) | t | t | (0,2) (0,5) | normal | 3992 | 0 (a) | | t | (0,5) (5 rows) 

La prochaine mise à jour provoque à nouveau le nettoyage de la page:

 => UPDATE hot SET s = 'H'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+-------+-----+-----+-------- (0,1) | redirect to 5 | | | | | (0,2) | normal | 3993 | 0 (a) | | t | (0,2) (0,3) | unused | | | | | (0,4) | unused | | | | | (0,5) | normal | 3992 (c) | 3993 | t | t | (0,2) (5 rows) 

Encore une fois, certains des tuples sont aspirés et le pointeur vers la tête de la chaîne est déplacé en conséquence.

Conclusion: si les colonnes qui ne sont pas indexées sont fréquemment mises à jour, il peut être judicieux de réduire le paramètre fillfactor afin de réserver un espace de page pour les mises à jour. Nous devons toutefois tenir compte du fait que moins il y a de fillfactor , plus il y a d'espace libre dans une page, donc la taille physique de la table augmente.

Rupture d'une chaîne HOT

Si la page manque d'espace libre pour allouer un nouveau tuple, la chaîne se cassera. Et nous devrons faire une référence distincte de l'index à la version de ligne située dans une page différente.

Pour reproduire cette situation, commençons une transaction simultanée et construisons l'instantané de données dedans.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT count(*) FROM hot; 

 | count | ------- | 1 | (1 row) 

L'instantané ne permettra pas de passer l'aspirateur sur les tuples de la page. Faisons maintenant une mise à jour dans la première session:

 => UPDATE hot SET s = 'I'; => UPDATE hot SET s = 'J'; => UPDATE hot SET s = 'K'; => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 | t | t | (0,5) (0,5) | normal | 3996 | 0 (a) | | t | (0,5) (5 rows) 

Lors de la prochaine mise à jour, la page n'aura pas assez d'espace, mais le vide dans la page ne pourra rien nettoyer:

 => UPDATE hot SET s = 'L'; 

 | => COMMIT; -- snapshot no longer needed 

 => SELECT * FROM heap_page('hot',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+---------------+----------+----------+-----+-----+-------- (0,1) | redirect to 2 | | | | | (0,2) | normal | 3993 (c) | 3994 (c) | t | t | (0,3) (0,3) | normal | 3994 (c) | 3995 (c) | t | t | (0,4) (0,4) | normal | 3995 (c) | 3996 (c) | t | t | (0,5) (0,5) | normal | 3996 (c) | 3997 | | t | (1,1) (5 rows) 

Dans le tuple (0,5), il y a une référence à (1,1), qui se trouve à la page 1.

 => SELECT * FROM heap_page('hot',1); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+------+-------+-----+-----+-------- (1,1) | normal | 3997 | 0 (a) | | | (1,1) (1 row) 

Il y a maintenant deux lignes dans l'index, chacune pointant vers le début de sa chaîne HOT:

 => SELECT * FROM index_page('hot_id',1); 

  itemoffset | ctid ------------+------- 1 | (1,1) 2 | (0,1) (2 rows) 

Malheureusement, la documentation manque pratiquement d'informations sur le vide sur la page et les mises à jour HOT, et vous devez rechercher des réponses dans le code source. Je vous conseille de commencer par README.HOT .

Continuez à lire .