PostgreSQL s’appuie sur un modèle de gestion de transactions appelé MVCC. Nous allons expliquer cet acronyme, puis étudier en profondeur son implémentation dans le moteur.

Cette technologie a en effet un impact sur le fonctionnement et l’administration de PostgreSQL.

MVCC est un sigle signifiant MultiVersion Concurrency Control, ou « contrôle de concurrence multi-version ».

Le principe est de faciliter l’accès concurrent de plusieurs utilisateurs (sessions) à la base en disposant en permanence de plusieurs versions différentes d’un même enregistrement. Chaque session peut travailler simultanément sur la version qui s’applique à son contexte (on parle d’« instantané » ou de snapshot).

Par exemple, une transaction modifiant un enregistrement va créer une nouvelle version de cet enregistrement. Mais celui-ci ne devra pas être visible des autres transactions tant que le travail de modification n’est pas validé en base. Les autres transactions verront donc une ancienne version de cet enregistrement. La dénomination technique est « lecture cohérente » (consistent read en anglais).

Précisons que la granularité des modifications est bien l’enregistrement (ou ligne) d’une table. Modifier un champ (colonne) revient à modifier la ligne. Deux transactions ne peuvent pas modifier deux champs différents d’un même enregistrement sans entrer en conflit, et les verrous portent toujours sur des lignes entières.

Avant d’expliquer en détail MVCC, voyons l’autre solution de gestion de la concurrence qui s’offre à nous, afin de comprendre le problème que MVCC essaye de résoudre.

Une table contient une liste d’enregistrements.

• Une transaction voulant consulter un enregistrement doit le verrouiller (pour s’assurer qu’il n’est pas modifié) de façon partagée, le consulter, puis le déverrouiller.
• Une transaction voulant modifier un enregistrement doit le verrouiller de façon exclusive (personne d’autre ne doit pouvoir le modifier ou le consulter), le modifier, puis le déverrouiller.

Cette solution a l’avantage de la simplicité : il suffit d’un gestionnaire de verrous pour gérer l’accès concurrent aux données. Elle a aussi l’avantage de la performance, dans le cas où les attentes de verrous sont peu nombreuses, la pénalité de verrouillage à payer étant peu coûteuse.

Elle a par contre des inconvénients :

• Les verrous sont en mémoire. Leur nombre est donc probablement limité. Que se passe-t-il si une transaction doit verrouiller 10 millions d’enregistrements ? Des mécanismes de promotion de verrou sont implémentés. Les verrous lignes deviennent des verrous bloc, puis des verrous table. Le nombre de verrous est limité, et une promotion de verrou peut avoir des conséquences dramatiques ;
• Un processus devant lire un enregistrement devra attendre la fin de la modification de celui-ci. Ceci entraîne rapidement de gros problèmes de contention. Les écrivains bloquent les lecteurs, et les lecteurs bloquent les écrivains. Évidemment, les écrivains se bloquent entre eux, mais cela est normal (il n’est pas possible que deux transactions modifient le même enregistrement simultanément, chacune sans conscience de ce qu’a effectué l’autre) ;
• Un ordre SQL (surtout s’il dure longtemps) n’a aucune garantie de voir des données cohérentes du début à la fin de son exécution : si, par exemple, durant un SELECT long, un écrivain modifie à la fois des données déjà lues par le SELECT, et des données qu’il va lire, le SELECT n’aura pas une vue cohérente de la table. Il pourrait y avoir un total faux sur une table comptable par exemple, le SELECT ayant vu seulement une partie des données validées par une nouvelle transaction ;
• Comment annuler une transaction ? Il faut un moyen de défaire ce qu’une transaction a effectué, au cas où elle ne se terminerait pas par une validation mais par une annulation.

C’est l’implémentation d’Oracle, par exemple. Un enregistrement, quand il doit être modifié, est recopié précédemment dans le tablespace d’UNDO. La nouvelle version de l’enregistrement est ensuite écrite par-dessus. Ceci implémente le MVCC (les anciennes versions de l’enregistrement sont toujours disponibles), et présente plusieurs avantages :

• Les enregistrements ne sont pas dupliqués dans la table. Celle-ci ne grandit donc pas suite à une mise à jour (si la nouvelle version n’est pas plus grande que la version précédente) ;
• Les enregistrements gardent la même adresse physique dans la table. Les index correspondant à des données non modifiées de l’enregistrement n’ont donc pas à être modifiés eux-mêmes, les index permettant justement de trouver l’adresse physique d’un enregistrement par rapport à une valeur.

Elle a aussi des défauts :

• La gestion de l’UNDO est très complexe : comment décider ce qui peut être purgé ? Il arrive que la purge soit trop agressive, et que des transactions n’aient plus accès aux vieux enregistrements (erreur SNAPSHOT TOO OLD sous Oracle, par exemple) ;
• La lecture cohérente est complexe à mettre en œuvre : il faut, pour tout enregistrement modifié, disposer des informations permettant de retrouver l’image avant modification de l’enregistrement (et la bonne image, il pourrait y en avoir plusieurs). Il faut ensuite pouvoir le reconstituer en mémoire ;
• Il est difficile de dimensionner correctement le fichier d’UNDO. Il arrive d’ailleurs qu’il soit trop petit, déclenchant l’annulation d’une grosse transaction. Il est aussi potentiellement une source de contention entre les sessions ;
• L’annulation (ROLLBACK) est très lente : il faut, pour toutes les modifications d’une transaction, défaire le travail, donc restaurer les images contenues dans l’UNDO, les réappliquer aux tables (ce qui génère de nouvelles écritures). Le temps d’annulation peut être supérieur au temps de traitement initial devant être annulé.

Dans une table PostgreSQL, un enregistrement peut être stocké dans plusieurs versions. Une modification d’un enregistrement entraîne l’écriture d’une nouvelle version de celui-ci. Une ancienne version ne peut être recyclée que lorsqu’aucune transaction ne peut plus en avoir besoin, c’est-à-dire qu’aucune transaction n’a un instantané de la base plus ancien que l’opération de modification de cet enregistrement, et que cette version est donc invisible pour tout le monde. Chaque version d’enregistrement contient bien sûr des informations permettant de déterminer s’il est visible ou non dans un contexte donné.

Les avantages de cette implémentation stockant plusieurs versions dans la table principale sont multiples :

• La lecture cohérente est très simple à mettre en œuvre : à chaque session de lire la version qui l’intéresse. La visibilité d’une version d’enregistrement est simple à déterminer ;
• Il n’y a pas d’UNDO. C’est un aspect de moins à gérer dans l’administration de la base ;
• Il n’y a pas de contention possible sur l’UNDO ;
• Il n’y a pas de recopie dans l’UNDO avant la mise à jour d’un enregistrement. La mise à jour est donc moins coûteuse ;
• L’annulation d’une transaction est instantanée : les anciens enregistrements sont toujours disponibles.

Cette implémentation a quelques défauts :

• Il faut supprimer régulièrement les versions obsolètes des enregistrements ;
• Il y a davantage de maintenance d’index (mais moins de contentions sur leur mise à jour) ;
• Les enregistrements embarquent des informations de visibilité, qui les rendent plus volumineux.

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé est un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction). Quatre niveaux sont définis, ils ne sont pas tous implémentés par PostgreSQL.

Ce niveau d’isolation n’est disponible que pour les SGBD non-MVCC. Il est très dangereux : il est possible de lire des données invalides, ou temporaires, puisque tous les enregistrements de la table sont lus, quels que soient leurs états. Il est utilisé dans certains cas où les performances sont cruciales, au détriment de la justesse des données.

Sous PostgreSQL, ce mode n’est pas disponible. Une transaction qui demande le niveau d’isolation READ UNCOMMITTED s’exécute en fait en READ COMMITTED.

Ce mode est le mode par défaut, et est suffisant dans de nombreux contextes. PostgreSQL étant MVCC, les écrivains et les lecteurs ne se bloquent pas mutuellement, et chaque ordre s’exécute sur un instantané de la base (ce n’est pas un prérequis de READ COMMITTED dans la norme SQL). Il n’y a plus de lectures d’enregistrements non valides (dirty reads). Il est toutefois possible d’avoir deux problèmes majeurs d’isolation dans ce mode :

• Les lectures non-répétables (non-repeatable reads) : une transaction peut ne pas voir les mêmes enregistrements d’une requête sur l’autre, si d’autres transactions ont validé des modifications entre temps ;
• Les lectures fantômes (phantom reads) : des enregistrements peuvent ne plus satisfaire une clause WHERE entre deux requêtes d’une même transaction.

Ce mode, comme son nom l’indique, permet de ne plus avoir de lectures non-répétables. Deux ordres SQL consécutifs dans la même transaction retourneront les mêmes enregistrements, dans la même version. Ceci est possible car la transaction voit une image de la base figée. L’image est figée non au démarrage de la transaction, mais à la première commande non TCL (Transaction Control Language) de la transaction, donc généralement au premier SELECT ou à la première modification.

Cette image sera utilisée pendant toute la durée de la transaction. En lecture seule, ces transactions ne peuvent pas échouer. Elles sont entre autres utilisées pour réaliser des exports des données : c’est ce que fait pg_dump.

Dans le standard, ce niveau d’isolation souffre toujours des lectures fantômes, c’est-à-dire de lecture d’enregistrements différents pour une même clause WHERE entre deux exécutions de requêtes. Cependant, PostgreSQL est plus strict et ne permet pas ces lectures fantômes en REPEATABLE READ. Autrement dit, un même SELECT renverra toujours le même résultat.

En écriture, par contre (ou SELECT FOR UPDATE, FOR SHARE), si une autre transaction a modifié les enregistrements ciblés entre temps, une transaction en REPEATABLE READ va échouer avec l’erreur suivante :

ERROR: could not serialize access due to concurrent update

Il faut donc que l’application soit capable de la rejouer au besoin.

PostgreSQL fournit un mode d’isolation appelé SERIALIZABLE :

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
…
COMMIT / ROLLBACK ;

Dans ce mode, toutes les transactions déclarées comme telles s’exécutent comme si elles étaient seules sur la base, et comme si elles se déroulaient les unes à la suite des autres. Dès que cette garantie ne peut plus être apportée, PostgreSQL annule celle qui entraînera le moins de perte de données.

Le niveau SERIALIZABLE est utile quand le résultat d’une transaction peut être influencé par une transaction tournant en parallèle, par exemple quand des valeurs de lignes dépendent de valeurs d’autres lignes : mouvements de stocks, mouvements financiers… avec calculs de stocks. Autrement dit, si une transaction lit des lignes, elle a la garantie que leurs valeurs ne seront pas modifiées jusqu’à son COMMIT, y compris par les transactions qu’elle ne voit pas — ou bien elle tombera en erreur.

Au niveau SERIALIZABLE (comme en REPEATABLE READ), il est donc essentiel de pouvoir rejouer une transaction en cas d’échec. Par contre, nous simplifions énormément tous les autres points du développement. Il n’y a plus besoin de SELECT FOR UPDATE, solution courante mais très gênante pour les transactions concurrentes. Les triggers peuvent être utilisés sans soucis pour valider des opérations.

Ce mode doit être mis en place globalement, car toute transaction non sérialisable peut en théorie s’exécuter n’importe quand, ce qui rend inopérant le mode sérialisable sur les autres.

La sérialisation utilise le « verrouillage de prédicats ». Ces verrous sont visibles dans la vue pg_locks sous le nom SIReadLock, et ne gênent pas les opérations habituelles, du moins tant que la sérialisation est respectée. Un enregistrement qui « apparaît » ultérieurement suite à une mise à jour réalisée par une transaction concurrente déclenchera aussi une erreur de sérialisation.

Le wiki PostgreSQL, et la documentation officielle donnent des exemples, et ajoutent quelques conseils pour l’utilisation de transactions sérialisables. Afin de tenter de réduire les verrous et le nombre d’échecs :

• faire des transactions les plus courtes possibles (si possible uniquement ce qui a trait à l’intégrité) ;
• limiter le nombre de connexions actives ;
• utiliser les transactions en mode READ ONLY dès que possible, voire en SERIALIZABLE READ ONLY DEFERRABLE (au risque d’un délai au démarrage) ;
• augmenter certains paramètres liés aux verrous, c’est-à-dire augmenter la mémoire dédiée ; car si elle manque, des verrous de niveau ligne pourraient être regroupés en verrous plus larges et plus gênants ;
• éviter les parcours de tables (Seq Scan), et donc privilégier les accès par index.

Le bloc (ou page) est l’unité de base de transfert pour les I/O, le cache mémoire… Il fait généralement 8 ko (ce qui ne peut être modifié qu’en recompilant). Les lignes y sont stockées avec des informations d’administration telles que décrites dans le schéma ci-dessus. Une ligne ne fait jamais partie que d’un seul bloc (si cela ne suffit pas, un mécanisme que nous verrons plus tard, nommé TOAST, se déclenche).

Nous distinguons dans ce bloc :

• un entête de page avec diverses informations, notamment la somme de contrôle (si activée) ;
• des identificateurs de 4 octets, pointant vers les emplacements des lignes au sein du bloc ;
• les lignes, stockées à rebours depuis la fin du bloc ;
• un espace spécial, vide pour les tables ordinaires, mais utilisé par les blocs d’index.

Le ctid identifie une ligne, en combinant le numéro du bloc (à partir de 0) et l’identificateur dans le bloc (à partir de 1). Comme la plupart des champs administratifs liés à une ligne, il suffit de l’inclure dans un SELECT pour l’afficher. L’exemple suivant affiche les premiers et derniers éléments des deux blocs d’une table et vérifie qu’il n’y a pas de troisième bloc :

# CREATE TABLE deuxblocs AS SELECT i, i  AS j FROM generate_series(1, 452) i;
SELECT 452

# SELECT ctid, i,j FROM deuxblocs
WHERE ctid in ( '(1, 1)', '(0, 226)', '(1, 1)', '(1, 226)', '(1, 227)', '(2, 0)' );

  ctid   |  i  |  j
---------+-----+-----
 (0,1)   |   1 |   1
 (0,226) | 226 | 226
 (1,1)   | 227 | 227
 (1,226) | 452 | 452

La documentation officielle contient évidemment tous les détails.

PostgreSQL stocke des informations de visibilité dans chaque version d’enregistrement.

• xmin : l’identifiant de la transaction créant cette version.
• xmax : l’identifiant de la transaction invalidant cette version.

Ici, les deux enregistrements ont été créés par la transaction 100. Il s’agit peut-être, par exemple, de la transaction ayant importé tous les soldes à l’initialisation de la base.

Nous décidons d’enregistrer un virement de 200 € du compte de M. Durand vers celui de Mme Martin. Ceci doit être effectué dans une seule transaction : l’opération doit être atomique, sans quoi de l’argent pourrait apparaître ou disparaître de la table.

Nous allons donc tout d’abord démarrer une transaction (ordre SQL BEGIN). PostgreSQL fournit donc à notre session un nouveau numéro de transaction (150 dans notre exemple). Puis nous effectuerons :

UPDATE soldes SET solde = solde - 200 WHERE nom = 'M. Durand';

Puis nous effectuerons :

UPDATE soldes SET solde = solde + 200 WHERE nom = 'Mme Martin';

Nous avons maintenant deux versions de chaque enregistrement.

Notre session ne voit bien sûr plus que les nouvelles versions de ces enregistrements, sauf si elle décidait d’annuler la transaction, auquel cas elle reverrait les anciennes données.

Pour une autre session, la version visible de ces enregistrements dépend de plusieurs critères :

• La transaction 150 a-t-elle été validée ? Sinon elle est invisible ;
• La transaction 150 a-t-elle été validée après le démarrage de la transaction en cours, et sommes-nous dans un niveau d’isolation (repeatable read ou serializable) qui nous interdit de voir les modifications faites depuis le début de notre transaction ? ;
• La transaction 150 a-t-elle été validée après le démarrage de la requête en cours ? Une requête, sous PostgreSQL, voit un instantané cohérent de la base, ce qui implique que toute transaction validée après le démarrage de la requête doit être ignorée.

Dans le cas le plus simple, 150 ayant été validée, une transaction 160 ne verra pas les premières versions : xmax valant 150, ces enregistrements ne sont pas visibles. Elle verra les secondes versions, puisque xmin = 150, et pas de xmax.

• La suppression d’un enregistrement s’effectue simplement par l’écriture d’un xmax dans la version courante ;
• Il n’y a rien à écrire dans les tables pour annuler une transaction. Il suffit de marquer la transaction comme étant annulée dans la CLOG.

La CLOG est stockée dans une série de fichiers de 256 ko, stockés dans le répertoire pg_xact/ de PGDATA (répertoire racine de l’instance PostgreSQL).

Chaque transaction est créée dans ce fichier dès son démarrage et est encodée sur deux bits puisqu’une transaction peut avoir quatre états :

• TRANSACTION_STATUS_IN_PROGRESS signifie que la transaction en cours, c’est l’état initial ;
• TRANSACTION_STATUS_COMMITTED  signifie que la la transaction a été validée ;
• TRANSACTION_STATUS_ABORTED  signifie que la transaction a été annulée ;
• TRANSACTION_STATUS_SUB_COMMITTED signifie que la transaction comporte des sous-transactions, afin de valider l’ensemble des sous-transactions de façon atomique.

Nous avons donc un million d’états de transactions par fichier de 256 ko.

Annuler une transaction (ROLLBACK) est quasiment instantané sous PostgreSQL : il suffit d’écrire TRANSACTION_STATUS_ABORTED dans l’entrée de CLOG correspondant à la transaction.

Toute modification dans la CLOG, comme toute modification d’un fichier de données (table, index, séquence, vue matérialisée), est bien sûr enregistrée tout d’abord dans les journaux de transactions (dans le répertoire pg_wal/).

Reprenons les avantages du MVCC tel qu’implémenté par PostgreSQL :

• Les lecteurs ne bloquent pas les écrivains, ni les écrivains les lecteurs ;
• Le code gérant les instantanés est simple, ce qui est excellent pour la fiabilité, la maintenabilité et les performances ;
• Les différentes sessions ne se gênent pas pour l’accès à une ressource commune (l’UNDO) ;
• Un enregistrement est facilement identifiable comme étant verrouillé en écriture : il suffit qu’il ait une version ayant un xmax correspondant à une transaction en cours ;
• L’annulation est instantanée : il suffit d’écrire le nouvel état de la transaction annulée dans la CLOG. Pas besoin de restaurer les valeurs précédentes, elles sont toujours là ;
• Les anciennes versions restent en ligne aussi longtemps que nécessaire. Elles ne pourront être effacées de la base qu’une fois qu’aucune transaction ne les considérera comme visibles.

(Précisons toutefois que ceci est une vision un peu simplifiée pour les cas courants. La signification du xmax est parfois altérée par des bits positionnés dans des champs systèmes inaccessibles par l’utilisateur. Cela arrive, par exemple, quand des transactions insèrent des lignes portant une clé étrangère, pour verrouiller la ligne pointée par cette clé, laquelle ne doit pas disparaître pendant la durée de cette transaction.)

Comme toute solution complexe, l’implémentation MVCC de PostgreSQL est un compromis. Les avantages cités précédemment sont obtenus au prix de concessions.

VACUUM

Il faut nettoyer les tables de leurs enregistrements morts. C’est le travail de la commande VACUUM. Il a un avantage sur la technique de l’UNDO : le nettoyage n’est pas effectué par un client faisant des mises à jour (et créant donc des enregistrements morts), et le ressenti est donc meilleur.

VACUUM peut se lancer à la main, mais dans le cas général on s’en remet à l’autovacuum, un démon qui lance les VACUUM (et bien plus) en arrière-plan quand il le juge nécessaire. Tout cela sera traité en détail par la suite.

Bloat

Les tables sont forcément plus volumineuses que dans l’implémentation par UNDO, pour deux raisons :

• les informations de visibilité y sont stockées, il y a donc un surcoût d’une douzaine d’octets par enregistrement ;
• il y a toujours des enregistrements morts dans une table, une sorte de fond de roulement, qui se stabilise quand l’application est en régime stationnaire.

Ces enregistrements sont recyclés à chaque passage de VACUUM.

Visibilité

Les index n’ont pas d’information de visibilité. Il est donc nécessaire d’aller vérifier dans la table associée que l’enregistrement trouvé dans l’index est bien visible. Cela a un impact sur le temps d’exécution de requêtes comme SELECT count(*) sur une table : dans le cas le plus défavorable, il est nécessaire d’aller visiter tous les enregistrements pour s’assurer qu’ils sont bien visibles. La visibility map permet de limiter cette vérification aux données les plus récentes.

Colonnes supprimées

Un VACUUM ne s’occupe pas de l’espace libéré par des colonnes supprimées (fragmentation verticale). Un VACUUM FULL est nécessaire pour reconstruire la table.

Le numéro de transaction stocké dans les tables de PostgreSQL est sur 32 bits, même si PostgreSQL utilise en interne 64 bits. Il y aura donc dépassement de ce compteur au bout de 4 milliards de transactions. Sur les machines actuelles, cela peut être atteint relativement rapidement.

En fait, ce compteur est cyclique, et toute transaction considère que les 2 milliards de transactions supérieures à la sienne sont dans le futur, et les 2 milliards inférieures dans le passé. Le risque de bouclage est donc plus proche des 2 milliards. Si nous bouclions, de nombreux enregistrements deviendraient invisibles, car validés par des transactions futures. Heureusement PostgreSQL l’empêche. Au fil des versions, la protection est devenue plus efficace.

La parade consiste à « geler » les lignes avec des identifiants de transaction suffisamment anciens. C’est le rôle de l’opération appelée VACUUM FREEZE. Ce dernier peut être déclenché manuellement, mais il fait aussi partie des tâches de maintenance habituellement gérées par le démon autovacuum, en bonne partie en même temps que les VACUUM habituels. Un VACUUM FREEZE n’est pas bloquant, mais les verrous sont parfois plus gênants que lors d’un VACUUM simple.

Si cela ne suffit pas, le moteur déclenche automatiquement un VACUUM FREEZE quand les tables sont trop âgées, et ce, même si autovacuum est désactivé.

Quand le stock de transactions disponibles descend en dessous de 40 millions (10 millions avant la version 14), des messages d’avertissements apparaissent dans les traces.

Dans le pire des cas, après bien des messages d’avertissements, le moteur refuse toute nouvelle transaction dès que le stock de transactions disponibles se réduit à 3 millions (1 million avant la version 14 ; valeurs codées en dur).

Il faudra alors lancer un VACUUM FREEZE manuellement. Ceci ne peut plus arriver qu’exceptionnellement (par exemple si une transaction préparée a été oubliée depuis 2 milliards de transactions et qu’aucune supervision ne l’a détectée).

VACUUM FREEZE sera développé dans le module VACUUM et autovacuum. La documentation officielle contient aussi un paragraphe sur ce sujet.

Les améliorations suivantes ont été ajoutées au fil des versions :

• Heap-Only Tuples (HOT) s’agit de pouvoir stocker, sous condition, plusieurs versions du même enregistrement dans le même bloc. Ceci permet au fur et à mesure des mises à jour de supprimer automatiquement les anciennes versions, sans besoin de VACUUM. Cela permet aussi de ne pas toucher aux index, qui pointent donc grâce à cela sur plusieurs versions du même enregistrement. Les conditions sont les suivantes :

  • Le bloc contient assez de place pour la nouvelle version (les enregistrements ne sont pas chaînés entre plusieurs blocs). Afin que cette première condition ait plus de chance d’être vérifiée, il peut être utile de baisser la valeur du paramètre fillfactor pour une table donnée (cf documentation officielle) ;
  • Aucune colonne indexée n’a été modifiée par l’opération.

• Chaque table possède une Free Space Map avec une liste des espaces libres de chaque table. Elle est stockée dans les fichiers *_fsm associés à chaque table.

• La Visibility Map permet de savoir si l’ensemble des enregistrements d’un bloc est visible. En cas de doute, ou d’enregistrement non visible, le bloc n’est pas marqué comme totalement visible. Cela permet à la phase 1 du traitement de VACUUM de ne plus parcourir toute la table, mais uniquement les enregistrements pour lesquels la Visibility Map est à faux (des données sont potentiellement obsolètes dans le bloc). À l’inverse, les parcours d’index seuls utilisent cette Visibility Map pour savoir s’il faut aller voir les informations de visibilité dans la table. VACUUM repositionne la Visibility Map à vrai après nettoyage d’un bloc, si tous les enregistrements sont visibles pour toutes les sessions. Enfin, depuis la 9.6, elle repère aussi les bloc entièrement gelés pour accélérer les VACUUM FREEZE.

Toutes ces optimisations visent le même but : rendre VACUUM le moins pénalisant possible, et simplifier la maintenance.

Le gestionnaire de verrous de PostgreSQL est capable de gérer des verrous sur des tables, sur des enregistrements, sur des ressources virtuelles. De nombreux types de verrous sont disponibles, chacun entrant en conflit avec d’autres.

Chaque opération doit tout d’abord prendre un verrou sur les objets à manipuler. Si le verrou ne peut être obtenu immédiatement, par défaut PostgreSQL attendra indéfiniment qu’il soit libéré.

Ce verrou en attente peut lui-même imposer une attente à d’autres sessions qui s’intéresseront au même objet. Si ce verrou en attente est bloquant (cas extrême : un VACUUM FULL sans SKIP_LOCKED lui-même bloqué par une session qui tarde à faire un COMMIT), il est possible d’assister à un phénomène d’empilement de verrous en attente.

Le gestionnaire de verrous détecte tout verrou mortel (deadlock) entre deux sessions. Un deadlock est la suite de prise de verrous entraînant le blocage mutuel d’au moins deux sessions, chacune étant en attente d’un des verrous acquis par l’autre.

Il est possible d’accéder aux verrous actuellement utilisés sur une instance par la vue pg_locks.

Le gestionnaire de verrous fournit des verrous sur enregistrement. Ceux-ci sont utilisés pour verrouiller un enregistrement le temps d’y écrire un xmax, puis libérés immédiatement.

Le verrouillage réel est implémenté comme suit :

• D’abord, chaque transaction verrouille son objet « identifiant de transaction » de façon exclusive.

• Une transaction voulant mettre à jour un enregistrement consulte le xmax. Si ce xmax est celui d’une transaction en cours, elle demande un verrou exclusif sur l’objet « identifiant de transaction » de cette transaction, qui ne lui est naturellement pas accordé. La transaction est donc placée en attente.

• Enfin, quand l’autre transaction possédant le verrou se termine (COMMIT ou ROLLBACK), son verrou sur l’objet « identifiant de transaction » est libéré, débloquant ainsi l’autre transaction, qui peut reprendre son travail.

Ce mécanisme ne nécessite pas un nombre de verrous mémoire proportionnel au nombre d’enregistrements à verrouiller, et simplifie le travail du gestionnaire de verrous, celui-ci ayant un nombre bien plus faible de verrous à gérer.

Le mécanisme exposé ici est évidemment simplifié.

C’est une vue globale à l’instance.

# \d pg_locks

                       Vue « pg_catalog.pg_locks »
      Colonne       |   Type                   | Collationnement | NULL-able | Par défaut
--------------------+--------------------------+-----------------+-----------+------------
 locktype           | text                     |                 |           |
 database           | oid                      |                 |           |
 relation           | oid                      |                 |           |
 page               | integer                  |                 |           |
 tuple              | smallint                 |                 |           |
 virtualxid         | text                     |                 |           |
 transactionid      | xid                      |                 |           |
 classid            | oid                      |                 |           |
 objid              | oid                      |                 |           |
 objsubid           | smallint                 |                 |           |
 virtualtransaction | text                     |                 |           |
 pid                | integer                  |                 |           |
 mode               | text                     |                 |           |
 granted            | boolean                  |                 |           |
 fastpath           | boolean                  |                 |           |
 waitstart          | timestamp with time zone |                 |           |

• locktype est le type de verrou, les plus fréquents étant relation (table ou index), transactionid (transaction), virtualxid (transaction virtuelle, utilisée tant qu’une transaction n’a pas eu à modifier de données, donc à stocker des identifiants de transaction dans des enregistrements) ;
• database est la base dans laquelle ce verrou est pris ;
• relation est l’OID de la relation cible si locktype vaut relation (ou page ou tuple) ;
• page est le numéro de la page dans une relation (pour un verrou de type page ou tuple) cible ;
• tuple est le numéro de l’enregistrement cible (quand verrou de type tuple) ;
• virtualxid est le numéro de la transaction virtuelle cible (quand verrou de type virtualxid) ;
• transactionid est le numéro de la transaction cible ;
• classid est le numéro d’OID de la classe de l’objet verrouillé (autre que relation) dans pg_class. Indique le catalogue système, donc le type d’objet, concerné. Aussi utilisé pour les advisory locks ;
• objid est l’OID de l’objet dans le catalogue système pointé par classid ;
• objsubid correspond à l’ID de la colonne de l’objet objid concerné par le verrou ;
• virtualtransaction est le numéro de transaction virtuelle possédant le verrou (ou tentant de l’acquérir si granted vaut f) ;
• pid est le PID (l’identifiant de processus système) de la session possédant le verrou ;
• mode est le niveau de verrouillage demandé ;
• granted signifie si le verrou est acquis ou non (donc en attente) ;
• fastpath correspond à une information utilisée surtout pour le débogage (fastpath est le mécanisme d’acquisition des verrous les plus faibles) ;
• waitstart indique depuis quand le verrou est en attente.

La plupart des verrous sont de type relation, transactionid ou virtualxid. Une transaction qui démarre prend un verrou virtualxid sur son propre virtualxid. Elle acquiert des verrous faibles (ACCESS SHARE) sur tous les objets sur lesquels elle fait des SELECT, afin de garantir que leur structure n’est pas modifiée sur la durée de la transaction. Dès qu’une modification doit être faite, la transaction acquiert un verrou exclusif sur le numéro de transaction qui vient de lui être affecté. Tout objet modifié (table) sera verrouillé avec ROW EXCLUSIVE, afin d’éviter les CREATE INDEX non concurrents, et empêcher aussi les verrouillages manuels de la table en entier (SHARE ROW EXCLUSIVE).

Nombre de verrous :

max_locks_per_transaction sert à dimensionner un espace en mémoire partagée destinée aux verrous sur des objets (notamment les tables). Le nombre de verrous est :

max_locks_per_transaction × max_connections

ou plutôt, si les transactions préparées sont activées (et max_prepared_transactions monté au-delà de 0) :

max_locks_per_transaction × (max_connections + max_prepared_transactions)

La valeur par défaut de 64 est largement suffisante la plupart du temps. Il peut arriver qu’il faille le monter, par exemple si l’on utilise énormément de partitions, mais le message d’erreur est explicite.

Le nombre maximum de verrous d’une session n’est pas limité à max_locks_per_transaction. C’est une valeur moyenne. Une session peut acquérir autant de verrous qu’elle le souhaite pourvu qu’au total la table de hachage interne soit assez grande. Les verrous de lignes sont stockés sur les lignes et donc potentiellement en nombre infini.

Pour la sérialisation, les verrous de prédicat possèdent des paramètres spécifiques. Pour économiser la mémoire, les verrous peuvent être regroupés par bloc ou relation (voir pg_locks pour le niveau de verrouillage). Les paramètres respectifs sont :

• max_pred_locks_per_transaction (64 par défaut) ;
• max_pred_locks_per_page (par défaut 2, donc 2 lignes verrouillées entraînent le verrouillage de tout le bloc, du moins pour la sérialisation) ;
• max_pred_locks_per_relation (voir la documentation pour les détails).

Durées maximales de verrou :

Si une session attend un verrou depuis plus longtemps que lock_timeout, la requête est annulée. Il est courant de poser cela avant un ordre assez intrusif, même bref, sur une base utilisée. Par exemple, il faut éviter qu’un VACUUM FULL, s’il est bloqué par une transaction un peu longue, ne bloque lui-même toutes les transactions suivantes (phénomène d’empilement des verrous)  :

postgres=# SET lock_timeout TO '3s' ;
SET
postgres=# VACUUM FULL t_grosse_table ;
ERROR:  canceling statement due to lock timeout

Il faudra bien sûr retenter le VACUUM FULL plus tard, mais la production n’est pas bloquée plus de 3 secondes.

PostgreSQL recherche périodiquement les deadlocks entre transactions en cours. La périodicité par défaut est de 1 s (paramètre deadlock_timeout), ce qui est largement suffisant la plupart du temps : les deadlocks sont assez rares, alors que la vérification est quelque chose de coûteux. L’une des transactions est alors arrêtée et annulée, pour que les autres puissent continuer :

postgres=*# DELETE FROM t_centmille_int WHERE i < 50000;
ERROR:  deadlock detected
DÉTAIL : Process 453259 waits for ShareLock on transaction 3010357;
blocked by process 453125.
Process 453125 waits for ShareLock on transaction 3010360;
blocked by process 453259.
ASTUCE : See server log for query details.
CONTEXTE : while deleting tuple (0,1) in relation "t_centmille_int"

Trace des verrous :

Pour tracer les attentes de verrous un peu longue, il est fortement conseillé de passer log_lock_waits à on (le défaut est off).

Le seuil est également défini par deadlock_timeout (1 s par défaut) Ainsi, une session toujours en attente de verrou au-delà de cette durée apparaîtra dans les traces :

LOG:  process 457051 still waiting for ShareLock on transaction 35373775
      after 1000.121 ms
DETAIL:  Process holding the lock: 457061. Wait queue: 457051.
CONTEXT:  while deleting tuple (221,55) in relation "t_centmille_int"
STATEMENT:  DELETE FROM t_centmille_int ;

S’il ne s’agit pas d’un deadlock, la transaction continuera, et le moment où elle obtiendra son verrou sera également tracé :

LOG:  process 457051 acquired ShareLock on transaction 35373775 after
      18131.402 ms
CONTEXT:  while deleting tuple (221,55) in relation "t_centmille_int"
STATEMENT:  DELETE FROM t_centmille_int ;
LOG:  duration: 18203.059 ms  statement: DELETE FROM t_centmille_int ;

Principe du TOAST :

Une ligne ne peut déborder d’un bloc, et un bloc fait 8 ko (par défaut). Cela ne suffit pas pour certains champs beaucoup plus longs, comme certains textes, mais aussi des types composés (json, jsonb, hstore), ou binaires (bytea).

Le mécanisme TOAST consiste à déporter le contenu de certains champs d’un enregistrement vers une pseudo-table système associée à la table principale, de manière transparente pour l’utilisateur. Il permet d’éviter qu’un enregistrement ne dépasse la taille d’un bloc.

Le mécanisme TOAST a d’autres intérêts :

• la partie principale d’une table ayant des champs très longs est moins grosse, alors que les « gros champs » ont moins de chance d’être accédés systématiquement par le code applicatif ;
• ces champs peuvent être compressés de façon transparente, avec souvent de gros gains en place ;
• si un UPDATE ne modifie pas un de ces champs « toastés », la table TOAST n’est pas mise à jour : le pointeur vers l’enregistrement de cette table est juste « cloné » dans la nouvelle version de l’enregistrement.

Politiques de stockage :

Chaque champ possède une propriété de stockage :

CREATE TABLE unetable (i int, t text, b bytea, j jsonb);
# \d+ unetable

                    Table « public.unetable »
 Colonne |  Type   | Col... | NULL-able | Par défaut | Stockage | …
---------+---------+--------+-----------+------------+----------+--
 i       | integer |        |           |            | plain    |
 t       | text    |        |           |            | extended |
 b       | bytea   |        |           |            | extended |
 j2      | jsonb   |        |           |            | extended |
Méthode d’accès : heap

Les différentes politiques de stockage sont :

• PLAIN permettant de stocker uniquement dans la table, sans compression (champs numériques ou dates notamment) ;
• MAIN permettant de stocker dans la table tant que possible, éventuellement compressé (politique rarement utilisée) ;
• EXTERNAL permettant de stocker éventuellement dans la table TOAST, sans compression ;
• EXTENDED permettant de stocker éventuellement dans la table TOAST, éventuellement compressé (cas général des champs texte ou binaire).

Il est rare d’avoir à modifier ce paramétrage, mais cela arrive. Par exemple, certains longs champs (souvent binaires) se compressent si mal qu’il ne vaut pas la peine de gaspiller du CPU dans cette tâche. Dans le cas extrême où le champ compressé est plus grand que l’original, PostgreSQL revient à la valeur originale, mais là aussi il y a gaspillage. Il peut alors être intéressant de passer de EXTENDED à EXTERNAL, pour un gain de temps parfois non négligeable :

ALTER TABLE t1 ALTER COLUMN champ SET STORAGE EXTERNAL ;

Lors de ce changement, les données existantes ne sont pas affectées.

Les tables pg_toast_XXX :

Chaque table utilisateur est associée à une table TOAST à partir du moment où le mécanisme TOAST a eu besoin de se déclencher. Les enregistrements sont découpés en morceaux d’un peu moins de 2 ko. Tous les champs « toastés » d’une table se retrouvent dans la même table pg_toast_XXX, dans un espace de nommage séparé nommé pg_toast.

Pour l’utilisateur, les tables TOAST sont totalement transparentes. Un développeur doit juste savoir qu’il n’a pas besoin de déporter des champs texte (ou JSON, ou binaires…) imposants dans une table séparée pour des raisons de volumétrie de la table principale : PostgreSQL le fait déjà, et de manière efficace ! Il est également souvent inutile de se donner la peine de compresser les données au niveau applicatif juste pour réduire le stockage.

La présence de ces tables n’apparaît guère que dans pg_class, par exemple ainsi :

SELECT * FROM pg_class c
WHERE c.relname = 'longs_textes'
OR c.oid = (SELECT reltoastrelid FROM pg_class
          WHERE relname = 'longs_textes');

-[ RECORD 1 ]-------+---------------
oid                 | 16614
relname             | longs_textes
relnamespace        | 2200
reltype             | 16616
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 16614
reltablespace       | 0
relpages            | 35
reltuples           | 2421
relallvisible       | 35
reltoastrelid       | 16617
…
-[ RECORD 2 ]-------+---------------
oid                 | 16617
relname             | pg_toast_16614
relnamespace        | 99
reltype             | 16618
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 16617
reltablespace       | 0
relpages            | 73161
reltuples           | 293188
relallvisible       | 73161
reltoastrelid       | 0
…

La partie TOAST est une table à part entière, avec une clé primaire. On ne peut ni ne doit y toucher !

\d+ pg_toast.pg_toast_16614

Table TOAST « pg_toast.pg_toast_16614 »
  Colonne   |  Type   | Stockage
------------+---------+----------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain

Table propriétaire : « public.textes_comp »
Index :
    "pg_toast_16614_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Méthode d'accès : heap

La volumétrie des différents éléments (partie principale, TOAST, index éventuels) peut se calculer grâce à cette requête dérivée du wiki :

SELECT
    oid AS table_oid,
    c.relnamespace::regnamespace || '.' || relname AS TABLE,
    reltoastrelid,
    reltoastrelid::regclass::text AS table_toast,
    reltuples AS nb_lignes_estimees,
    pg_size_pretty(pg_table_size(c.oid)) AS " Table (dont TOAST)",
    pg_size_pretty(pg_relation_size(c.oid)) AS "  Heap",
    pg_size_pretty(pg_relation_size(reltoastrelid)) AS "  Toast",
    pg_size_pretty(pg_indexes_size(reltoastrelid)) AS  "  Toast (PK)",
    pg_size_pretty(pg_indexes_size(c.oid)) AS " Index",
    pg_size_pretty(pg_total_relation_size(c.oid)) AS "Total"
FROM  pg_class c
WHERE relkind = 'r'
AND   relname = 'longs_textes'
\gx

-[ RECORD 1 ]------+------------------------
table_oid           | 16614
table               | public.longs_textes
reltoastrelid       | 16617
table_toast         | pg_toast.pg_toast_16614
nb_lignes_estimees  | 2421
 Table (dont TOAST) | 578 MB
  Heap              | 280 kB
  Toast             | 572 MB
  Toast (PK)        | 6448 kB
 Index              | 560 kB
Total               | 579 MB

La taille des index sur les champs susceptibles d’être toastés est comptabilisée avec tous les index de la table (la clé primaire de la table TOAST est à part).

Les tables TOAST restent forcément dans le même tablespace que la table principale. Leur maintenance (notamment le nettoyage par autovacuum) s’effectue en même temps que la table principale, comme le montre un VACUUM VERBOSE.

Détails du mécanisme TOAST :

Les détails techniques du mécanisme TOAST sont dans la documentation officielle. En résumé, le mécanisme TOAST est déclenché sur un enregistrement quand la taille d’un enregistrement dépasse 2 ko. Les champs « toastables » peuvent alors être compressés pour que la taille de l’enregistrement redescende en-dessous de 2 ko. Si cela ne suffit pas, des champs sont alors découpés et déportés vers la table TOAST. Dans ces champs de la table principale, l’enregistrement ne contient plus qu’un pointeur vers la table TOAST associée.

Un champ MAIN peut tout de même être stocké dans la table TOAST, si l’enregistrement dépasse 2 ko : mieux vaut « toaster » que d’empêcher l’insertion.

Cette valeur de 2 ko convient généralement. Au besoin, on peut l’augmenter (à partir de la version 11) en utilisant le paramètre de stockage toast_tuple_target ainsi :

ALTER TABLE t1 SET (toast_tuple_target = 3000);

mais cela est rarement utile.

Compression pgls vs lz4 :

Depuis la version 14, il est possible de modifier l’algorithme de compression. Ceci est défini par le nouveau paramètre default_toast_compression dont la valeur par défaut est :

=# SHOW default_toast_compression ;

 default_toast_compression
---------------------------
 pglz

c’est-à-dire que PostgreSQL utilise la zlib, seule compression disponible jusqu’en version 13 incluse.

À partir de la version 14, il est souvent préférable d’utiliser lz4, un nouvel algorithme, si PostgreSQL a été compilé avec la bibliothèque du même nom (c’est le cas des paquets distribués par le PGDG).

L’activation demande soit de modifier la valeur par défaut dans postgresql.conf :

default_toast_compression = lz4

soit de déclarer la méthode de compression à la création de la table :

CREATE TABLE t1 (
  c1 bigint GENERATED ALWAYS AS identity,
  c2 text   COMPRESSION lz4
  ) ;

soit après coup :

ALTER TABLE t1 ALTER c2 SET COMPRESSION lz4 ;

De manière générale, l’algorithme lz4 ne compresse pas mieux les données courantes, mais cela dépend des usages. Surtout, lz4 est beaucoup plus rapide à compresser, et parfois à décompresser.

Par exemple, il peut accélérer une restauration logique avec beaucoup de données toastées et compressées. Si lz4 n’a pas été activé par défaut, il peut être utilisé dès le chargement :

$ PGOPTIONS='-c default_toast_compression=lz4'  pg_restore …

Une table TOAST peut contenir un mélange de lignes compressées de manière différentes. En effet, l’utilisation SET COMPRESSION sur une colonne préexistante ne recompresse pas les données de la table TOAST. De plus, pendant une requête, des données toastées lues par une requête, puis réinsérées sans être modifiées, sont recopiées vers les champs cibles telles quelles, sans étapes de décompression/recompression, et ce même si la compression de la cible est différente. Il existe une fonction pg_column_compression (nom_colonne) pour consulter la compression d’un champ sur la ligne concernée.

Pour forcer la recompression de toutes les données d’une colonne, il faut modifier leur contenu, ce qui n’est pas forcément intéressant.

Niveaux d’isolation READ COMMITTED et REPEATABLE READ

Créer une nouvelle base de données nommée b2.

Se connecter à la base de données b2. Créer une table t1 avec deux colonnes c1 de type integer et c2 de type text.

Insérer 5 lignes dans table t1 avec des valeurs de (1, 'un') à (5, 'cinq').

Ouvrir une transaction.

Lire les données de la table t1.

Depuis une autre session, mettre en majuscules le texte de la troisième ligne de la table t1.

Revenir à la première session et lire de nouveau toute la table t1.

Fermer la transaction et ouvrir une nouvelle transaction, cette fois-ci en REPEATABLE READ.

Lire les données de la table t1.

Depuis une autre session, mettre en majuscules le texte de la quatrième ligne de la table t1.

Revenir à la première session et lire de nouveau les données de la table t1. Que s’est-il passé ?

Niveau d’isolation SERIALIZABLE (Optionnel)

Une table de comptes bancaires contient 1000 clients, chacun avec 3 lignes de crédit et 600 € au total :

CREATE TABLE mouvements_comptes
(client int,
 mouvement numeric NOT NULL DEFAULT 0
 );
CREATE INDEX on mouvements_comptes (client) ;

-- 3 clients, 3 lignes de +100, +200, +300 €
INSERT INTO mouvements_comptes (client, mouvement)
SELECT i, j * 100
FROM generate_series(1, 1000) i
CROSS JOIN generate_series(1, 3) j ;

Chaque mouvement donne lieu à une ligne de crédit ou de débit. Une ligne de crédit correspondra à l’insertion d’une ligne avec une valeur mouvement positive. Une ligne de débit correspondra à l’insertion d’une ligne avec une valeur mouvement négative. Nous exigeons que le client ait toujours un solde positif. Chaque opération bancaire se déroulera donc dans une transaction, qui se terminera par l’appel à cette procédure de test :

CREATE PROCEDURE verifie_solde_positif (p_client int)
LANGUAGE plpgsql
AS $$
DECLARE
    solde   numeric ;
BEGIN
    SELECT  round(sum (mouvement), 0)
    INTO    solde
    FROM    mouvements_comptes
    WHERE   client = p_client ;
    IF solde < 0 THEN
        -- Erreur fatale
        RAISE EXCEPTION 'Client % - Solde négatif : % !', p_client, solde ;
    ELSE
        -- Simple message
        RAISE NOTICE 'Client % - Solde positif : %', p_client, solde ;
    END IF ;
END ;
$$ ;

Au sein de trois transactions successives, Insérer successivement 3 mouvements de débit de 300 € pour le client 1.
Chaque transaction doit finir par CALL verifie_solde_positif (1); avant le COMMIT.
La sécurité fonctionne-t-elle ?

Dans deux sessions parallèles, pour le client 2, procéder à deux retraits de 500 €. Appeler CALL verifie_solde_positif (2); dans les deux transactions, puis valider les deux. La règle du solde positif est-elle respectée ?

Reproduire avec le client 3 le même scénario de deux débits parallèles de 500 €, mais avec des transactions sérialisables (BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE).
Avant chaque COMMIT, consulter la vue pg_locks pour la table mouvements_comptes :
SELECT locktype, mode, pid, granted FROM pg_locks
WHERE relation = (SELECT oid FROM pg_class WHERE relname = 'mouvements_comptes') ;

Effets de MVCC

Créer une nouvelle table t2 avec les mêmes colonnes que la table t1.

Insérer 5 lignes dans la table t2 de (1, 'un') à (5, 'cinq').

Lire les données de la table t2.

Commencer une transaction et mettre en majuscules le texte de la troisième ligne de la table t2.

Lire les données de la table t2. Que faut-il remarquer ?

Ouvrir une autre session et lire les données de la table t2. Que faut-il observer ?

Récupérer quelques informations systèmes (xmin et xmax) pour les deux sessions lors de la lecture des données de la table t2.

Récupérer maintenant en plus le ctid lors de la lecture des données de la table t2.

Valider la transaction.

Installer l’extension pageinspect.

À l’aide de la documentation de l’extension sur https://docs.postgresql.fr/current/pageinspect.html, et des fonctions get_raw_page et heap_page_items, décoder le bloc 0 de la table t2. Que faut-il remarquer ?

Verrous

Ouvrir une transaction et lire les données de la table t1. Ne pas terminer la transaction.

Ouvrir une autre transaction, et tenter de supprimer la table t1.

Lister les processus du serveur PostgreSQL. Que faut-il remarquer ?

Depuis une troisième session, récupérer la liste des sessions en attente avec la vue pg_stat_activity.

Récupérer la liste des verrous en attente pour la requête bloquée.

Récupérer le nom de l’objet dont le verrou n’est pas récupéré.

Récupérer la liste des verrous sur cet objet. Quel processus a verrouillé la table t1 ?

Retrouver les informations sur la session bloquante.

Retrouver cette information avec la fonction