PostgreSQL 18 — ingénierie des données

Comprendre le mécanisme

Une clé primaire identifie une ligne et interdit null ; une clé étrangère exprime une relation vers une clé admissible. UNIQUE protège une unicité, tandis que CHECK contrôle une expression de ligne. Attention : une expression CHECK qui vaut inconnu à cause de null ne rejette pas nécessairement la ligne ; NOT NULL exprime explicitement l’obligation de valeur. Une règle multi-lignes demande un mécanisme approprié plutôt qu’un CHECK supposant un état global stable.

Raisonner et vérifier

Choisissez les types selon le domaine : numeric pour une précision décimale définie, entiers pour des quantités discrètes, timestamptz pour représenter un instant avec conversion d’affichage. Les noms et contraintes documentent le contrat partagé par toutes les applications. Testez aussi les écritures SQL directes : une validation dans une seule API ne protège pas tous les producteurs de données.

Identifier une ligne sans lui donner des droits

Une clé primaire impose une identité de ligne selon son contrat de non-nullité et d’unicité. Elle fournit une référence utile aux relations et aux recherches, mais ne définit pas qui peut consulter cette ligne. Un identifiant reçu doit encore être validé et résolu dans le périmètre métier autorisé. La présence d’une clé et une authentification réussie ne suffisent pas à protéger un objet tiers.

Déclarer la relation entre tables

Une clé étrangère protège une relation, par exemple une réservation qui doit viser un produit existant. Sans cette contrainte, un contrôle dans un formulaire peut être contourné par un autre client SQL ou devenir incorrect sous concurrence. Choisissez les colonnes et la politique de modification de la référence. Le nom product_id ne crée aucune garantie à lui seul : c’est la contrainte déclarée qui participe au contrat de stockage.

Présence et prédicat sont deux exigences

CHECK (quantity > 0) ne remplace pas NOT NULL. Une comparaison avec une valeur nulle peut produire un résultat inconnu, que ce CHECK ne rejette pas comme faux. Si la quantité est obligatoire et positive, exprimez les deux conditions. Les tests doivent inclure null, zéro, un négatif et un positif ; chacun vérifie une distinction du contrat plutôt qu’une simple variante du cas nominal.

L’unicité doit survivre à la concurrence

Une lecture préalable qui constate l’absence d’une référence ne réserve pas cette absence pour les autres transactions. Deux acteurs peuvent donc essayer la même insertion. Une contrainte UNIQUE donne au moteur une règle à appliquer au moment pertinent. Définissez ensuite la traduction de son refus en résultat métier. Un test utile synchronise deux connexions pour observer cette course au lieu d’enchaîner deux demandes séquentielles.

Choisir le sens des valeurs nulles

Une règle d’unicité doit préciser le traitement des valeurs nulles. Ne supposez pas que toutes les bases et toutes les options les considèrent toujours comme une seule valeur ordinaire. Vérifiez la sémantique choisie par PostgreSQL et les possibilités de la version ciblée. Si le domaine interdit l’absence, ajoutez sa contrainte explicite plutôt que tenter de déduire cette obligation de l’unicité seule.

La suppression référentielle est une décision métier

Une action CASCADE peut propager la suppression vers des lignes dépendantes. Cette commodité technique doit correspondre au cycle de vie voulu : supprimer un produit ne signifie pas nécessairement effacer un historique de facturation. Dessinez les dépendances avant de choisir l’action. Testez la suppression du parent, les références encore actives et la politique de conservation ; le moteur applique la règle déclarée, pas une intention implicite.

Vérifier la règle au niveau du stockage

Une contrainte de base protège les écritures de tous les clients soumis à cette règle. Un contrôle d’interface seul ne fournit pas cette propriété. Dans le laboratoire, tentez une écriture directe invalide avec le rôle approprié et observez le refus. Pour JPA, une écriture peut être différée : un test transactionnel doit provoquer le flush nécessaire afin que la base vérifie effectivement la contrainte avant le rollback de test.

Choisir une représentation fidèle au domaine

Une colonne text ne valide pas automatiquement une date ou un montant. Pour une valeur décimale exacte, un numeric avec précision et échelle adaptées peut être pertinent ; ses bornes et arrondis doivent correspondre au produit. L’objectif n’est pas de choisir le type le plus permissif, mais de faire participer le stockage au contrat. Définissez aussi les conversions entre API, application et base.

Migrer des données déjà présentes

Ajouter une contrainte à une base existante peut échouer si des lignes violent la nouvelle règle. Inventoriez ces états avant la migration et définissez leur traitement. Le déploiement doit considérer les applications encore actives qui écrivent selon l’ancien contrat. Une migration réussie sur une base vide ne prouve pas son comportement sur les données réelles ; préparez des fixtures représentatives et une procédure de reprise.

Normaliser selon les invariants utiles

Dupliquer une valeur dans plusieurs tables peut améliorer certains usages, mais crée un besoin de cohérence entre copies. Une normalisation adaptée explicite les responsabilités et relations importantes. Si une copie historique est voulue, documentez qu’elle représente une valeur à un instant et non une référence toujours synchronisée. Le schéma doit aider à lire le contrat métier, sans transformer une préférence de structure en garantie universelle de performance.

Atelier guidé : lire, expliquer et vérifier le code

CREATE TABLE lab_payments (
    id bigint PRIMARY KEY,
    amount integer CHECK (amount > 0)
);
INSERT INTO lab_payments(id, amount) VALUES (1, NULL);

La table de l’exercice possède un CHECK qui demande un montant positif. Pour la ligne dont amount vaut NULL, le prédicat n’est pas vrai ou faux comme pour un entier ordinaire : il produit une valeur inconnue. Ce seul CHECK ne fournit donc pas la présence obligatoire. La clé primaire protège id, pas amount. Il faut ajouter NOT NULL si l’absence doit être refusée.

Dans une base de laboratoire, testez séparément NULL, zéro, une valeur négative et une valeur positive. Notez quelle règle refuse chaque entrée. Rejouez l’écriture depuis un client SQL direct pour vérifier que la garantie ne dépend pas seulement du formulaire. Si vous utilisez un ORM qui diffère l’écriture, déclenchez le flush au point prévu afin que le test observe bien la décision du moteur.

JPA : persist, flush et commit ne sont pas équivalents

Un ORM peut différer l’exécution SQL. persist enregistre une intention dans le contexte de persistance sans que toutes les contraintes de la base aient nécessairement été évaluées immédiatement. Un test transactionnel annulé automatiquement peut donc réussir avant que l’écriture fautive soit réellement envoyée.

Pour une contrainte UNIQUE immédiate du schéma réel, déclenchez flush dans le test et vérifiez l’exception. Ce contrôle force la synchronisation SQL pertinente. Il ne démontre pas qu’un commit a réussi : contraintes différées, événements après commit et visibilité inter-transaction demandent des cas séparés.

Utilisez une base isolée avec le schéma et les contraintes attendus, plutôt qu’un mock qui accepte toute ligne. Le test doit choisir la frontière qu’il veut établir et éviter de confondre rollback du test avec rollback du service. Documentez les contraintes différables et vérifiez au commit celles dont le calendrier l’exige.

Points de vigilance : Un mock ne prouve pas le comportement de la contrainte de la base. Supprimer la contrainte retire précisément le comportement à vérifier. L’écoulement du temps ne constitue pas un mécanisme de flush fiable. Le flush force l’exécution SQL et expose la contrainte. Un flush réussi ne prouve toutefois pas qu’un commit a eu lieu.

Comprendre le mécanisme

Une jointure relie des lignes ; sa cardinalité peut multiplier un résultat avant agrégation. WHERE filtre les lignes avant GROUP BY, HAVING filtre les groupes après agrégation. Une fonction fenêtre calcule sur un ensemble lié tout en conservant les lignes d’origine. PARTITION BY organise les groupes de fenêtre et ORDER BY leur ordre logique ; il faut aussi expliciter les égalités lorsque le résultat dépend d’un classement déterministe.

Raisonner et vérifier

Une CTE structure une requête mais n’est pas automatiquement une optimisation magique. Définissez d’abord la granularité de sortie : une ligne par réservation, produit ou tenant. Testez avec doublons, groupes vides et valeurs null. NOT IN peut devenir inconnu en présence de null dans la sous-requête ; un NOT EXISTS correctement corrélé exprime souvent plus clairement l’absence recherchée.

Filtrer des lignes ou des groupes

La logique SQL distingue le filtre de lignes effectué par WHERE et les conditions appliquées aux groupes avec HAVING. Commencez par déterminer quelles lignes entrent dans l’agrégation, puis quelle propriété du groupe doit être vérifiée. Déplacer une condition sans analyser son niveau peut changer le résultat. Un jeu d’essai contenant plusieurs groupes, dont un vide de valeurs utiles, rend cette différence visible.

Une fenêtre conserve le contexte des lignes

Une fonction de fenêtre calcule une information sur un ensemble lié à chaque ligne sans nécessairement réduire ce résultat à une ligne par groupe. C’est utile pour afficher une réservation et son rang ou un cumul. Comparez le nombre et l’identité des lignes avant et après le calcul. Une fonction agrégée avec GROUP BY répond à un autre besoin et ne peut pas être substituée sans revoir le contrat.

L’ordre appartient à chaque calcul concerné

L’ordre utilisé dans une fenêtre définit le sens d’un rang ou d’un cumul. Un ORDER BY final organise l’affichage du résultat, mais ne remplace pas automatiquement cet ordre de calcul. Si plusieurs événements ont la même date, ajoutez un départage pertinent comme un identifiant stable. Sans règle explicite sur les égalités, une sortie observée sur quelques lignes ne démontre pas une sélection reproductible.

Lire le cadre de fenêtre

La frame détermine quelles lignes participent à certains calculs de fenêtre autour de la ligne courante. Un cumul et une valeur de fin de partition ne demandent pas forcément le même cadre. Lisez la définition complète avant de conclure depuis le nom de la fonction. Testez notamment des valeurs de tri égales : elles peuvent révéler une différence que des dates toutes distinctes ne montrent pas.

Préserver une absence avec LEFT JOIN

Une jointure externe peut conserver une ligne de gauche sans correspondance à droite. Ajouter ensuite dans WHERE une condition qui rejette la valeur nulle de droite peut supprimer cette ligne conservée. Déterminez si le filtre appartient à la condition de jointure ou au résultat final selon le besoin. Vérifiez les cas sans correspondance, avec une correspondance et avec plusieurs correspondances avant de valider le total obtenu.

La logique de NULL change les exclusions

Une comparaison ordinaire avec NULL peut produire un résultat inconnu, que WHERE ne conserve pas comme vrai. Cela explique le piège de NOT IN lorsqu’une sous-requête contient NULL : l’exclusion peut éliminer des lignes attendues. Étudiez les valeurs une par une et choisissez une formulation conforme au contrat, souvent une recherche d’absence avec NOT EXISTS. Filtrer les nulls est une autre décision qui doit être explicitement justifiée.

Stabiliser la sélection avec LIMIT

LIMIT borne le nombre de lignes renvoyées, sans créer leur ordre. Un tri par date seule peut encore laisser une ambiguïté lorsque des dates sont égales. Définissez un ordre total pertinent et testez ces égalités. Pour une pagination concurrente, documentez aussi l’effet des insertions entre appels : une sélection stable à un instant ne promet pas automatiquement un parcours identique sur une base qui évolue.

La cardinalité des jointures influence les agrégats

Une ligne de gauche peut correspondre à plusieurs lignes de droite. Si vous additionnez ensuite une valeur de gauche, elle peut être comptée plusieurs fois. Dessinez la cardinalité attendue et vérifiez le nombre de lignes après la jointure avant d’interpréter un total. Un résultat numérique plausible n’est pas une preuve de justesse ; préparez un cas où une seule référence possède plusieurs dépendances.

Structurer avec un CTE sans promettre la vitesse

Un CTE peut rendre une requête plus lisible en nommant une étape. Il ne garantit pas une amélioration de performance universelle. La version du moteur, la forme de la requête et les options influencent le plan. Validez d’abord le résultat, puis mesurez le comportement sur des données représentatives. Une refactorisation de lisibilité doit conserver les prédicats, cardinalités et conventions de NULL attendus.

Un jeu d’essai qui discrimine réellement

Un petit jeu utile doit inclure les cas qui distinguent deux interprétations possibles : doublons, égalités, lignes sans correspondance, valeurs nulles et limites de sélection. Écrivez à la main le résultat attendu avant d’exécuter la requête. Si deux requêtes incorrectement supposées équivalentes donnent le même résultat sur votre fixture, ajoutez un cas qui exerce la différence ; multiplier des lignes identiques n’améliore pas cette preuve.

Atelier guidé : lire, expliquer et vérifier le code

WITH lab(v) AS (VALUES (1), (NULL), (3))
SELECT count(*) AS row_count, count(v) AS value_count, sum(v) AS total
FROM lab;

La fixture de l’atelier comporte trois lignes, dont une valeur nulle. count(*) compte les lignes et donne trois. count(v) compte ici les valeurs non nulles et donne deux. sum(v) additionne les valeurs présentes, un et trois, ce qui donne quatre. Les trois colonnes répondent donc à des questions différentes sur le même ensemble.

Remplacez ensuite toutes les valeurs par NULL et étudiez le contrat de chaque agrégat ; ne généralisez pas le résultat du premier jeu. Ajoutez un groupe supplémentaire et comparez WHERE avec HAVING. Cet exercice construit une méthode de lecture : déterminer les lignes conservées, comprendre la règle de NULL, puis calculer chaque agrégat selon sa définition. Le bon résultat ne doit pas être deviné depuis la taille de la table seule.

SQL et NULL : pourquoi NOT IN peut ne rien retourner

SELECT id FROM invoices
WHERE id NOT IN (SELECT invoice_id FROM blocked);

SQL utilise une logique à trois valeurs : TRUE, FALSE et UNKNOWN. WHERE conserve seulement TRUE. Avec l’ensemble interdit {2,NULL}, l’expression 2 NOT IN retourne FALSE ; pour 1 ou 3, l’absence de comparaison déterminée avec NULL empêche d’établir TRUE et le résultat est UNKNOWN.

Dans l’exercice, aucune des factures 1, 2 et 3 n’est donc retenue. La présence d’une seule valeur NULL dans la sous-requête change le raisonnement habituel d’une liste de valeurs toutes connues. Il ne s’agit ni d’un hasard du plan ni d’un problème de casse.

Pour une exclusion par relation, un NOT EXISTS corrélé exprime souvent plus clairement l’absence d’une ligne correspondante. Une autre solution peut filtrer les NULL si cela respecte le contrat. Testez explicitement sous-requête vide, valeur correspondante et présence de NULL, puis choisissez la forme dont la sémantique correspond à vos données.

Points de vigilance : Pour 1 et 3, la présence de NULL rend le prédicat inconnu, donc rejeté par WHERE. L’identifiant 2 est exclu par sa correspondance ; les autres ont un résultat inconnu. 2 produit FALSE ; 1 et 3 produisent UNKNOWN. Aucun résultat ne vaut TRUE. NULL est autorisé ici ; il change la logique à trois valeurs sans provoquer d’erreur.

Comprendre le mécanisme

PostgreSQL emploie MVCC pour exposer des versions cohérentes des données. En READ COMMITTED, des instructions successives peuvent observer des instantanés différents. Une transaction ne garantit donc pas que toute décision lecture-puis-écriture conserve un invariant partagé. Un verrou de ligne ou une écriture conditionnelle peut protéger un cas précis ; une contrainte doit compléter la protection lorsque la règle peut y être exprimée.

Raisonner et vérifier

SERIALIZABLE peut annuler une transaction incompatible avec une exécution sérielle ; la reprise doit rejouer la transaction complète et recalculer la décision. Les deadlocks exigent aussi une politique de reprise et un ordre de prise de verrous cohérent. Dans vos tests, utilisez deux connexions réelles et des points de synchronisation afin de rendre l’entrelacement observable.

Un snapshot par instruction en Read Committed

Dans PostgreSQL, un SELECT ordinaire en Read Committed voit normalement un état validé au début de l’instruction. Un second SELECT de la même transaction peut donc voir une validation concurrente plus récente. BEGIN ne suffit pas à promettre une photographie unique pour toute la transaction. Représentez les lectures et commits sur une chronologie pour distinguer l’instant du snapshot de celui de la réponse réseau.

Read Uncommitted dans ce moteur

Le nom d’un niveau d’isolation doit être interprété avec les garanties réelles du moteur ciblé. PostgreSQL traite Read Uncommitted comme Read Committed plutôt que fournir des lectures sales par cette option. Ne recopiez pas aveuglément un tableau générique SQL dans une analyse de production. Vérifiez la version et les opérations étudiées, puis testez ce qui peut effectivement devenir visible entre deux sessions.

Les limites de Repeatable Read

Le modèle PostgreSQL Repeatable Read fournit une stabilité plus forte et évite notamment les lectures fantômes dans son implémentation. Il ne rend pas tout invariant métier sérialisable. Deux transactions peuvent lire un état commun puis modifier des lignes différentes de façon incompatible avec une règle globale. L’exemple des deux médecins de garde montre ce write skew : chaque écriture individuelle semble valide alors que leur combinaison viole l’invariant.

Reprendre une transaction sérialisable

Serializable vise un résultat compatible avec un ordre séquentiel, mais peut refuser une transaction lorsque l’exécution ne peut pas préserver cette propriété. Après un échec de sérialisation, il faut reprendre la transaction complète avec ses lectures et décisions, pas seulement la dernière écriture. Borner les reprises et traiter les effets externes évite qu’une garantie de base crée des doublons ou une boucle de charge illimitée.

Maintenir le verrou pendant la décision

Un verrou utile doit couvrir la lecture, la décision et l’écriture qui partagent l’invariant. Le prendre après le calcul ou le libérer avant la transition peut laisser une fenêtre de concurrence. Choisissez une ressource de coordination commune à tous les chemins concernés. Une stratégie suivie seulement par la moitié des transactions ne démontre pas la règle globale ; les participants doivent respecter le même protocole.

Ordre des verrous et attente cyclique

Deux transactions peuvent attendre chacune une ressource détenue par l’autre. Un ordre cohérent d’acquisition réduit certaines possibilités de deadlock sans prétendre éliminer tous les échecs de concurrence. Définissez cet ordre dans les opérations multi-objets et testez les chemins opposés. Le traitement d’erreur doit rester prévu : un blocage détecté n’est pas une autorisation de valider un état partiellement calculé.

Effets distants et reprise idempotente

Un rollback SQL n’annule pas un email déjà envoyé ni un appel PSP validé. Organisez les effets externes selon une frontière durable, par exemple avec une référence d’opération et une publication coordonnée. Une reprise de transaction doit pouvoir distinguer une nouvelle intention d’une répétition. Le test important simule la perte de réponse après l’effet, car l’absence de réponse ne démontre pas l’absence de mutation distante.

Une séquence n’est pas un compteur sans trous

Une valeur allouée par une séquence peut rester consommée après l’annulation d’une transaction. Des écarts ne prouvent donc pas automatiquement une suppression frauduleuse de ligne. Si le domaine exige une numérotation particulière, concevez cette règle explicitement au lieu de la déduire d’un identifiant technique. Les tests doivent séparer unicité, ordre apparent et absence de trous : ce sont des propriétés différentes.

Construire un test à deux connexions

Pour démontrer un invariant concurrent, synchronisez deux sessions aux points pertinents : lecture commune, décision, écriture puis validation. Un appel après l’autre ne reproduit pas la course. Vérifiez l’état final et le résultat de chaque demande. Pour deux réservations de remboursement de 7 000 sur une capacité de 10 000, il faut observer qu’une stratégie choisie empêche les deux succès, pas seulement qu’un calcul isolé est correct.

Limiter la durée des transactions

Une transaction conservée ouverte peut retenir verrous, snapshots et ressources, même lorsqu’un client ne travaille plus. Gardez les opérations nécessaires dans la frontière et déplacez les attentes externes lorsqu’un contrat cohérent le permet. Définissez délais et annulation selon le produit. Une durée courte n’est pas une preuve de correction métier, mais une transaction indéfinie augmente les coûts et rend les incidents de concurrence plus difficiles à diagnostiquer.

Atelier guidé : lire, expliquer et vérifier le code

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT amount FROM lab_accounts WHERE id = 1;
-- Une autre session valide amount = 900 ici.
SELECT amount FROM lab_accounts WHERE id = 1;
COMMIT;

Dans la chronologie de l’atelier, une autre session valide une modification entre deux SELECT. Avec Read Committed, le second SELECT peut observer ce nouvel état. Les lectures appartiennent à la même transaction explicite, mais ne promettent pas le même snapshot pour toute sa durée. L’exemple ne montre pas non plus un verrou exclusif global empêchant l’autre session de travailler.

Reproduisez la chronologie avec deux connexions de laboratoire et notez les commits. Comparez ensuite une stratégie d’isolation plus forte ou de verrouillage adaptée à un invariant précis. Le choix doit être évalué avec ses refus et reprises possibles. Une amélioration de stabilité des lectures ne doit pas être présentée comme une atomicité distribuée couvrant automatiquement les effets d’un service externe.

Write skew : préserver au moins un médecin de garde

Sous REPEATABLE READ PostgreSQL, chaque transaction peut observer un instantané cohérent sans que toutes les décisions concurrentes préservent un invariant portant sur plusieurs lignes. Deux médecins de garde sont visibles ; chaque transaction retire un médecin différent et pense laisser l’autre présent. Les écritures ne visent pas la même ligne, ce qui rend insuffisant le seul raisonnement sur conflits d’écriture directs.

Si toutes ces opérations suivent SERIALIZABLE, PostgreSQL peut refuser une exécution incompatible avec un ordre sériel. L’application doit alors reprendre toute la transaction : nouvelle lecture, nouveau calcul et nouvelle décision. Rejouer seulement le dernier UPDATE conserverait une décision prise sur un état périmé.

Bornez les tentatives et gérez séparément les effets externes qui ne peuvent pas être rejoués naïvement. Dans un test concurrent synchronisé, vérifiez l’invariant final et le traitement de l’erreur de sérialisation, pas seulement le succès apparent d’une connexion isolée.

Points de vigilance : Une contrainte sur une valeur de ligne ne démontre pas un invariant portant sur plusieurs médecins. Ce niveau ne supprime pas l’anomalie lecture-décision-écriture décrite. Rejouer seulement UPDATE conserve une décision prise sur une lecture devenue invalide. PostgreSQL peut annuler une transaction incompatible avec une exécution sérielle ; la reprise doit relire et recalculer.

Remboursements concurrents : réserver sans dépasser le capturé

L’invariant porte sur le cumul réservé, pas seulement sur chaque demande isolée. Avec 10000 capturés, deux lectures séparées de zéro peuvent chacune autoriser 7000 puis produire un total incompatible. Un contrôle applicatif avant écriture laisse donc une fenêtre de concurrence.

Dans le scénario READ COMMITTED posé, utilisez une mise à jour atomique de la même ligne : incrémenter reserved uniquement lorsque reserved + montant reste inférieur ou égal à captured. Vérifiez qu’une ligne a été modifiée. L’écriture concurrente attend puis réévalue le prédicat sur la version actualisée ; la seconde réservation de 7000 ne peut alors pas réussir.

Testez deux connexions synchronisées et inspectez résultat de chaque UPDATE et cumul final. Traitez aussi l’identité des demandes pour les doublons et la frontière PSP : réserver localement n’est pas encore un remboursement externe confirmé. Les montants et le capturé immuable sont des hypothèses explicites de cet exercice.

Points de vigilance : Deux lectures peuvent voir le même disponible et approuver chacune 7 000. Des verrous locaux à des processus différents ne protègent pas une ressource partagée. L’écriture concurrente attend puis réévalue la condition sur la ligne actualisée. Une seule réservation peut passer ici. L’unicité des demandes ne protège pas une limite globale de montant.

Comprendre le mécanisme

Un index accélère certains accès au prix d’espace et de maintenance lors des écritures. L’ordre des colonnes d’un index composite compte selon filtres et tri. Le planificateur utilise des statistiques ; une estimation erronée de cardinalité peut conduire à un choix inadapté. Un Sequential Scan n’est pas automatiquement mauvais, notamment lorsque la requête lit une grande part d’une petite table.

Raisonner et vérifier

EXPLAIN décrit le plan estimé. EXPLAIN ANALYZE exécute réellement l’instruction et mesure les étapes : faites attention aux mutations et aux effets déclenchés, même dans un laboratoire. Comparez lignes estimées et réelles, boucles, tri et lectures de buffers. Une optimisation doit être évaluée sur des données représentatives et sur la charge d’écriture, pas sur une seule mesure après création d’index.

Lire une estimation avant une mesure

EXPLAIN décrit le plan que le moteur envisage, avec ses estimations. Ce document aide à raisonner sur les accès et cardinalités sans constituer une mesure universelle de performance réelle. Repérez les nœuds, les lignes estimées et les prédicats utilisés. Un coût estimé n’est pas une durée directement interchangeable avec une mesure d’horloge ; il faut garder le contexte du planificateur et des données.

ANALYZE implique une exécution

EXPLAIN ANALYZE mesure une exécution réelle de l’instruction. Cette différence est essentielle pour un UPDATE ou un DELETE : le diagnostic peut produire les effets de l’opération. Utilisez un laboratoire et une procédure qui maîtrise explicitement les données. Lire le mot EXPLAIN ne suffit pas à conclure que l’action est inoffensive ; les options choisies font partie du contrat de l’outil.

Comparer estimations et observations

Le planificateur utilise des statistiques pour estimer les cardinalités. Une divergence importante entre lignes estimées et observées peut expliquer un choix mal adapté. Recherchez distribution de données, corrélations et fraîcheur des informations avant de forcer un plan au hasard. La mesure doit conserver la version et la fixture utilisées ; une amélioration sur un autre jeu ne corrige pas nécessairement le problème initial.

Le coût d’écriture d’un index

Un index offre un accès supplémentaire mais occupe du stockage et doit être maintenu lors des écritures pertinentes. Ajouter tous les index imaginables peut dégrader d’autres parcours. Évaluez le gain de lecture avec le coût de mise à jour et d’exploitation. Le choix doit correspondre à des requêtes observées et à leur fréquence, sans transformer une accélération locale en promesse de performance globale.

Un parcours séquentiel peut être rationnel

Lorsque la table est petite ou qu’une grande part des lignes est utile, lire séquentiellement peut être un choix adapté. Le seul nom Seq Scan ne démontre donc pas un défaut. Examinez volumes, sélectivité et coût observé du parcours. Une optimisation doit conserver le bon résultat et viser le goulot réel, plutôt que remplacer systématiquement toute lecture séquentielle par un accès indexé.

L’ordre des colonnes répond à une requête

Un B-tree composite doit être étudié avec ses prédicats et son ordre de tri. Pour une sélection par tenant puis un tri chronologique limité, la position des colonnes et les directions pertinentes peuvent permettre un parcours utile. Le choix d’un index ne promet pas que le planificateur le prendra toujours. Comparez les plans et mesures sur une distribution représentative, avec des égalités et bornes proches de la production.

Le prédicat d’un index partiel

Un index partiel ne contient que les lignes satisfaisant sa condition. Son bénéfice dépend donc du rapport entre ce prédicat et les requêtes à accélérer. Il ne constitue pas un index universel de la table. Définissez le sous-ensemble utile et les parcours qui doivent encore fonctionner autrement. Les tests doivent inclure des lignes dedans et dehors pour vérifier coût et résultat.

Mesurer sur des données représentatives

Trois lignes uniformes ne reproduisent pas une table volumineuse avec quelques tenants dominants. La distribution, les doublons et la sélectivité peuvent influencer le plan. Préparez une fixture adaptée au problème et consignez sa composition. Une mesure de laboratoire n’est utile que si ses conditions expliquent les conclusions ; elle ne doit pas être extrapolée sans limite à toute taille et tout trafic.

Comparer à contexte constant

Caches, concurrence et état du stockage modifient les temps observés. Un résultat chaud isolé ne démontre pas la latence à froid ou sous charge. Définissez le scénario de mesure et répétez seulement ce qui permet d’évaluer sa variabilité utile. Conservez le plan et les données avec le résultat afin que le reviewer sache quelles conditions ont changé entre deux versions.

La vitesse ne corrige pas la sémantique

Un index peut accélérer une requête qui calcule un mauvais total ou divulgue des lignes d’un autre tenant. Validez d’abord les prédicats, jointures et permissions. Les assertions de résultat doivent rester présentes pendant l’optimisation. Séparez la preuve de justesse de la mesure de coût : un temps plus court ne rend pas conforme une sélection qui ne respectait pas le contrat métier.

Atelier guidé : lire, expliquer et vérifier le code

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM lab_events WHERE created_at < DATE '2020-01-01';

L’instruction de l’atelier est un DELETE accompagné de l’option ANALYZE. Le moteur doit donc exécuter la requête pour produire les observations, ce qui la distingue d’un simple plan estimé. BUFFERS ajoute des informations de diagnostic ; cette option ne transforme pas la suppression en lecture et ne garantit aucune annulation automatique.

Étudiez ce cas uniquement sur des données de laboratoire prévues pour être modifiées. Comparez ensuite un EXPLAIN sans ANALYZE et relevez la différence entre estimation et exécution observée. Pour une requête de lecture, confrontez les lignes estimées et réelles avant de proposer un index. Le diagnostic doit rester une expérience contrôlée dont le périmètre, les effets et les conditions de restauration sont connus.

Index composite : égalité puis ordre et limite

SELECT id, created_at FROM orders
WHERE tenant_id = :tenant
ORDER BY created_at DESC, id DESC
LIMIT 50;

La consultation fixe un tenant puis demande les lignes les plus récentes, avec id comme départage déterministe. Un B-tree commençant par tenant_id puis created_at DESC et id DESC peut isoler le préfixe d’égalité et parcourir directement le suffixe dans l’ordre demandé.

L’ordre des colonnes n’est pas décoratif. Un index qui commence uniquement par la date peut devoir examiner des lignes de nombreux tenants ; un index sur tenant_id seul ne fournit pas automatiquement le tri demandé. L’index retenu doit correspondre aux prédicats et à l’ordre complet, y compris le départage des dates identiques.

Cette correspondance rend un chemin possible, sans imposer le choix du planificateur. Mesurez avec EXPLAIN et, sur un environnement approprié, EXPLAIN ANALYZE en tenant compte de l’exécution réelle. Comparez volumes, statistiques et coût d’écriture de l’index. Un test fonctionnel vérifie également que deux lignes de même date restent ordonnées de façon stable.

Points de vigilance : Cet index ne regroupe pas les lignes du tenant ni l’ordre principal sur created_at. Le préfixe d’égalité isole le tenant et le suffixe suit les deux clés de tri. Mesurer ensuite avec EXPLAIN. Intercaler tenant_id après created_at ne fournit pas un intervalle d’index exclusivement dédié à ce tenant. Après le tenant, id est prioritaire : cet ordre ne correspond pas au tri demandé.

Comprendre le mécanisme

Les droits d’un rôle définissent les opérations possibles sur les objets. Un rôle applicatif ne devrait pas hériter de privilèges d’administration inutiles. Les schémas organisent les noms ; search_path participe à la résolution et doit être maîtrisé dans les contextes sensibles. RLS ajoute des politiques par ligne, mais les rôles superuser ou BYPASSRLS et, normalement, le propriétaire de la table constituent des cas particuliers.

Raisonner et vérifier

Un test réalisé avec le propriétaire peut masquer une erreur de politique. Utilisez le rôle runtime non propriétaire et définissez le contexte de tenant depuis une identité fiable, pas un champ client arbitraire. FORCE ROW LEVEL SECURITY soumet le propriétaire aux politiques dans son périmètre, sans neutraliser tous les privilèges spéciaux. Vérifiez lecture et écriture ainsi que les requêtes de maintenance.

Définir le moindre privilège utile

Le rôle runtime doit posséder les opérations nécessaires au service, pas automatiquement les pouvoirs d’un administrateur SQL. Un privilège excessif élargit les conséquences d’une erreur ou d’une compromission. Inventoriez connexions, lectures, écritures et opérations de schéma. Les tests doivent démontrer un usage nominal et un refus pertinent ; une authentification à la base ne donne pas à elle seule un droit sur chaque table.

Propriété SQL et rôle métier

Le propriétaire d’une table est un concept du moteur distinct d’un utilisateur métier nommé administrateur. Ne confondez pas ces deux modèles lors de la conception des accès. Si le compte applicatif possède les objets, certaines protections peuvent se comporter autrement que pour un compte runtime limité. Une revue doit donc noter le rôle SQL réel et ses attributs, au lieu de conclure à partir du seul nom visible dans l’interface.

Comprendre les exceptions au modèle RLS

Une politique RLS doit être évaluée avec les rôles et paramètres effectifs. Le propriétaire est normalement exempté de certaines restrictions, sauf configuration adaptée ; superutilisateurs et capacités de contournement demandent aussi une revue. FORCE ROW LEVEL SECURITY n’est pas une formule remplaçant tout modèle de privilège. Décrivez qui crée, possède et interroge les tables, puis vérifiez ce que chaque contexte peut réellement voir.

Démontrer l’isolation avec deux tenants

Un test avec un seul tenant ne peut pas montrer une fuite inter-tenant. Préparez deux ensembles reconnaissables et consultez chacun avec son contexte fiable. Vérifiez aussi les écritures et les références croisées pertinentes. Le résultat attendu doit inclure l’absence des données tierces, pas seulement la présence des bonnes lignes. Une interface filtrée peut masquer une requête serveur trop large si le test ne regarde pas la réponse brute.

Privilèges de connexion, schéma et objet

Un droit de connexion n’équivaut pas à SELECT sur toutes les tables, et un droit sur une table ne décrit pas nécessairement la possibilité de résoudre le schéma concerné. Lisez les portées des privilèges avant de modifier une configuration. Un refus ne doit pas être corrigé en accordant tous les droits sans analyse. Cherchez l’opération requise et le périmètre minimal qui permet ce parcours.

Le contexte d’une connexion réutilisée

Un pool réutilise des connexions dont certains états peuvent survivre à une requête. Si un paramètre de session désigne un tenant, il doit être établi depuis une identité fiable et correctement nettoyé selon le contrat de réutilisation. Un champ client libre ne doit pas choisir le tenant de confiance. Testez deux requêtes successives sur la même connexion avec des tenants différents pour révéler une contamination d’état.

Protéger les credentials de base

Le DSN et les paramètres de connexion peuvent contenir des secrets donnant accès à un large ensemble de données. Leur injection par environnement ne rend pas leur affichage public acceptable. Limitez les logs, les accès de diagnostic et les copies d’artefact. En cas d’exposition, retirez la capacité compromise selon une procédure de rotation ; effacer seulement une ligne d’un fichier courant ne supprime pas les anciennes copies.

Revoir SECURITY DEFINER et la résolution des noms

Une fonction SECURITY DEFINER s’exécute dans un contexte de privilèges qui demande une analyse précise. Les noms résolus, le chemin de recherche et les objets accessibles peuvent influencer ce qu’elle appelle réellement. Ne supposez pas que l’option garantit à elle seule une isolation sûre. Limitez les capacités exposées et testez les entrées ainsi que les contextes où un utilisateur moins privilégié peut l’invoquer.

Séparer migration et exploitation courante

Créer ou modifier le schéma demande parfois des privilèges inutiles à une API après son déploiement. Utiliser le même compte pour tout rend la frontière moins précise. Définissez un rôle de migration et un rôle runtime selon le besoin, avec distribution et rotation de secrets adaptées. Vérifiez le service une fois les capacités temporaires retirées ; un démarrage réussi avec des droits administrateur ne suffit pas.

Tester le rôle effectivement déployé

Une requête réussie en superutilisateur ne prouve pas le comportement du compte applicatif. Les protections peuvent être contournées ou les permissions beaucoup plus larges. Conservez dans la preuve le rôle, ses attributs et la configuration testée. Une suite d’isolation doit être exécutée avec le contexte runtime pertinent et montrer les refus attendus, tout en gardant un parcours nominal qui confirme que le service reste utilisable.

Atelier guidé : lire, expliquer et vérifier le code

-- Connexion réutilisée par un pool :
SET app.tenant_id = 'tenant-a';
SELECT current_setting('app.tenant_id');
-- La même connexion sert ensuite une demande tenant-b.

La configuration de l’atelier place un tenant dans un paramètre de session, puis indique que la connexion est réutilisée. La valeur ne connaît pas automatiquement l’identité du prochain appelant. Si aucune remise en contexte correcte n’est faite, une requête suivante peut hériter d’un état qui ne lui appartient pas. Lire ce paramètre avec current_setting ne valide pas sa provenance.

Construisez un essai à deux clients sur un pool de laboratoire. Le premier établit son contexte, puis le second effectue une lecture sur la connexion rendue disponible. Vérifiez que les lignes du premier restent absentes du résultat du second. Répétez l’exercice avec le rôle runtime réel et documentez comment la valeur de tenant est dérivée de l’identité, installée puis nettoyée.

RLS : le rôle qui exécute la requête compte

Une politique RLS correcte ne suffit pas si le rôle runtime bénéficie d’une exemption. Le propriétaire de table contourne normalement RLS ; dans le cas posé, il peut donc voir tous les tenants même sans superuser ni attribut BYPASSRLS.

Utilisez de préférence un rôle d’exécution non propriétaire, avec permissions minimales, et un rôle séparé pour les migrations. FORCE ROW LEVEL SECURITY peut soumettre le propriétaire aux politiques dans le cas concerné, mais ne transforme pas les superusers ou rôles BYPASSRLS en rôles ordinaires. Documentez ces distinctions au lieu de parler simplement de RLS activé.

Testez avec les credentials réellement utilisés par l’application, puis changez de tenant. Un test effectué avec le rôle de migration ne démontre pas l’isolation du runtime et peut même masquer sa configuration. Vérifiez également la provenance du contexte tenant utilisé par la politique : un paramètre libre envoyé par le client ne constitue pas un contexte autorisé.

Points de vigilance : Le propriétaire bénéficie normalement d’une exemption. FORCE le soumet aux politiques ; superuser et BYPASSRLS restent des cas distincts. RLS peut limiter les lignes visibles par SELECT. Une politique peut filtrer les lignes sans prédicat explicite dans chaque SELECT ; son contexte doit néanmoins être fiable. Un index ne désactive pas le contrôle d’accès par ligne.

Comprendre le mécanisme

Une sauvegarde logique exporte des objets et données avec un outil comme pg_dump ; une sauvegarde physique et l’archivage WAL répondent à d’autres scénarios de récupération. Choisissez la méthode selon le point de reprise et le délai de reprise nécessaires. La réplication améliore certaines disponibilités, mais peut aussi répliquer une suppression erronée : elle ne constitue pas à elle seule une sauvegarde indépendante.

Raisonner et vérifier

Un fichier de dump présent ne prouve pas qu’il se restaure ni qu’il contient le périmètre attendu. Restaurez dans une base distincte, vérifiez schéma, données, droits et fonctions indispensables. Définissez la rétention, le chiffrement et l’accès aux copies. Avant une migration risquée, identifiez la sauvegarde restaurable et la compatibilité du code précédent avec le schéma futur.

Transformer une copie en preuve de reprise

Un fichier de sauvegarde présent démontre une production de données selon un outil, pas encore une restauration utile. Restaurez dans un environnement contrôlé et vérifiez que l’application peut retrouver ses invariants. Conservez versions, préconditions et résultat. Ce contrôle doit être régulier selon les évolutions du système ; une réussite ancienne ne couvre pas automatiquement un format ou un volume devenu différent.

Comprendre la perte admissible avec RPO

Le RPO décrit un objectif de perte de données admissible exprimé dans le temps. Il relie la fréquence et la disponibilité des copies à ce que le métier accepte de perdre lors d’un incident. Il ne définit pas le temps de redémarrage. Demandez quels événements doivent être récupérables et vérifiez la chronologie des données effectivement présentes après restauration, plutôt que déduire la perte du seul horaire du job.

Mesurer le délai de reprise avec RTO

Le RTO concerne le délai visé pour rétablir un service selon un périmètre défini. Il inclut davantage que la lecture d’une archive : provisionnement, restauration, vérifications et remise à disposition peuvent compter. Un objectif écrit n’est pas une mesure. Exécutez un exercice représentatif et notez le temps de chaque étape afin de savoir quelle hypothèse empêche de respecter le délai annoncé.

Le rôle des WAL dans une reprise ponctuelle

Une reprise vers un point choisi peut dépendre de la sauvegarde de base et des journaux WAL nécessaires à la chronologie visée. Conserver seulement une base ancienne ne permet pas de reconstruire librement tout état ultérieur. Vérifiez continuité, accès et procédure de récupération des journaux selon le modèle utilisé. Un exercice doit provoquer une absence contrôlée pour valider aussi le diagnostic et les limites de reprise.

Formats logiques et physiques

Une sauvegarde logique et une sauvegarde physique n’ont pas les mêmes contraintes de version, de restauration et de dépendances. Choisissez le format selon le besoin et l’environnement cible, puis testez les outils correspondants. Ne renommez pas simplement un fichier en supposant tous les formats interchangeables. La procédure doit préciser ce qu’elle conserve, ce qu’elle reconstruit et les éléments externes nécessaires au redémarrage utile du service.

Ne pas restaurer un secret retiré

Une sauvegarde peut contenir des credentials ou références qui étaient légitimes à sa date mais ont été retirés ensuite. Une restauration aveugle peut réintroduire cette capacité. Ajoutez une revue de configuration sensible et de cycle de clés à la procédure de reprise. Le succès technique de la base ne prouve pas la conformité de ses accès actuels ni des connexions qu’elle va tenter de réutiliser.

Restreindre les copies et leur lecture

Une sauvegarde peut représenter une copie très large des données sensibles. Elle demande donc des droits, une conservation et un transport adaptés à sa capacité réelle de divulgation. Un répertoire de diagnostic public n’est pas un stockage approprié simplement parce que le fichier porte un nom de secours. Les preuves d’exercice doivent rester expurgées et permettre l’analyse sans donner accès aux données réelles.

Reproduire le volume utile

Restaurer quelques lignes n’établit pas le délai d’une base volumineuse avec index et contraintes. Les ressources du stockage et de l’environnement de reprise comptent. Préparez un volume représentatif et mesurez les étapes coûteuses. Si l’exercice utilise une réduction volontaire du périmètre, annoncez-la dans la conclusion : elle sert à tester le mécanisme, pas à promettre un délai réel qui n’a jamais été observé.

Vérifier le sens des données retrouvées

Un processus de restauration terminé sans erreur ne prouve pas chaque invariant métier. Définissez les contrôles utiles, par exemple cohérence des relations, comptages attendus et possibilité de lire un parcours clé. Les tests doivent distinguer disponibilité technique et résultat fonctionnel. Un fichier reconstruit peut encore manquer une dépendance, un rôle ou un élément de configuration indispensable à l’application.

Retour de code, réplication et sauvegarde

La réplication peut reproduire une suppression logique accidentelle ; elle n’est donc pas une garantie indépendante contre toutes les erreurs métier. De même, revenir à un ancien binaire ne rembobine pas automatiquement la base. Définissez les scénarios couverts par chaque mécanisme et leurs limites. Une procédure de restauration doit être validée dans un environnement séparé, sans détruire les données qui pourraient encore servir à l’investigation.

Atelier guidé : lire, expliquer et vérifier le code

pg_dump --format=custom --file=lab-backup.dump lab_database
# Le processus retourne 0 ; aucun test de restauration n’a été fait.

La commande de l’atelier réalise un export logique et retourne un succès selon l’outil. Cela ne mesure pas encore un RTO et ne prouve pas la présence de toutes les conditions d’une restauration future. Le commentaire précise justement qu’aucune reprise n’a été exécutée. La conclusion doit rester limitée à l’export observé.

Préparez une base distincte de laboratoire, restaurez le fichier avec l’outil compatible puis lancez des vérifications choisies avant l’exercice. Mesurez le délai complet et contrôlez un petit parcours applicatif. Documentez les rôles et éléments de configuration nécessaires. Cet essai transforme une hypothèse de récupérabilité en preuve limitée et reproductible, tout en laissant visibles les volumes ou scénarios qui n’ont pas été couverts.

Comprendre le mécanisme

JSON conserve une représentation textuelle validée ; JSONB organise les données dans une représentation exploitable pour des recherches et index spécifiques. Les deux choix ont des comportements différents, notamment sur la représentation du document. Un document JSON valide ne garantit pas le schéma métier. Les relations, clés et valeurs fréquemment interrogées peuvent mériter des colonnes relationnelles explicites plutôt qu’un document opaque.

Raisonner et vérifier

Le partitionnement organise physiquement de grands ensembles selon une clé et des règles ; il ne remplace pas la conception des accès ni tous les index. Pour faire évoluer un schéma utilisé par plusieurs versions d’application, séparez ajout compatible, transition du code et retrait ultérieur. Mesurez les verrous et le coût d’une migration sur un jeu représentatif, puis définissez la reprise si elle échoue.

Une représentation JSONB n’est pas le texte initial

JSONB organise un document selon une représentation propre au moteur. Ne supposez pas qu’il conserve tous les espaces ou détails textuels du message reçu. Si vous devez authentifier des octets bruts, cette exigence doit être traitée avant une transformation qui change leur représentation. Distinguez identité de contenu métier et identité d’octets ; les deux propriétés peuvent servir à des contrôles différents.

La syntaxe ne définit pas le schéma métier

Un document valide peut manquer amount, porter une chaîne au lieu d’un entier ou contenir des combinaisons interdites. Après parsing, appliquez les règles de présence, de type et de relation prévues par le domaine. Les tests doivent viser ces distinctions. Une base capable de stocker le document n’a pas automatiquement décidé qu’il représente une commande acceptable pour l’application.

Absence et valeur null

Une clé présente avec JSON null peut signifier autre chose qu’une clé absente. Pour une mise à jour partielle, l’une pourrait demander un effacement tandis que l’autre signifie conserver la valeur, selon votre contrat. Vérifiez les opérateurs choisis et l’interprétation applicative. Une comparaison visuelle des documents ne suffit pas : écrivez le résultat attendu pour chaque état et testez leur lecture et transformation.

Indexer selon les opérateurs utiles

Un index JSON doit correspondre aux opérations et expressions des requêtes à accélérer. Le simple fait qu’un index concerne un document ne garantit pas l’accès efficace à toute extraction possible. Définissez les recherches importantes et mesurez leur plan avec des données représentatives. Conservez d’abord les assertions de résultat, car une optimisation de structure ne doit pas modifier la sémantique de présence, de type ou de filtrage.

Combiner données relationnelles et documents

Un identifiant métier utilisé pour des relations ou une contrainte importante peut bénéficier d’une colonne relationnelle explicite. Le format JSON ne dispense pas de garantir l’unicité et les références utiles au domaine. Choisissez quelles informations ont une responsabilité stable dans le schéma et quelles parties restent documentaires. Une séparation cohérente aide les consommateurs à comprendre les garanties, plutôt que cacher tout invariant dans une chaîne générale.

Lire les anciennes versions

Ajouter un champ attendu par une nouvelle application ne le crée pas dans les documents historiques. Définissez comment les consommateurs lisent un ancien format et comment les données sont migrées. Un défaut de lecture choisi doit avoir un sens métier clair ; une valeur arbitraire peut masquer un cas impossible. Préparez des fixtures d’avant et d’après l’évolution afin de vérifier réellement cette compatibilité.

Élargir, migrer, retirer

Quand deux versions d’application peuvent être actives, une modification brutale de format peut casser l’une d’elles. Une stratégie progressive ajoute d’abord une capacité compatible, migre les usages et données, puis retire l’ancien contrat une fois le périmètre maîtrisé. Cette séquence demande des critères observables, pas seulement trois noms d’étapes. Testez les deux consommateurs pendant la transition et la reprise après un incident intermédiaire.

Convertir une extraction selon son contrat

Une extraction textuelle peut contenir autre chose qu’un nombre valide. Avant un cast, définissez la grammaire et les bornes admises, puis traitez le refus. Le stockage JSON ne fournit pas automatiquement la bonne conversion pour chaque domaine. Un texte numérique très grand peut aussi dépasser une représentation choisie par l’application. Si seuls ses chiffres importent, le contrat peut demander de le conserver comme texte.

Les droits ne changent pas avec le format

Une donnée personnelle reste sensible lorsqu’elle est stockée dans JSONB. Les règles d’accès doivent porter sur le document et les opérations permises, avec le contexte de tenant approprié. Un champ imbriqué ne devient pas public parce qu’il échappe à une colonne relationnelle dédiée. Testez les réponses effectivement renvoyées et la minimisation des extractions, notamment sur les endpoints de recherche et les exports.

Tester les cas qui changent d’interprétation

Une évolution de document doit être vérifiée sur clés absentes, nulls, mauvais types et valeurs limites, ainsi que sur le nominal. Écrivez les résultats attendus avant la conversion. Ces catégories révèlent souvent une différence entre ancienne et nouvelle version qui reste invisible sur un seul document complet. Le test doit aussi vérifier l’absence d’effet sur une entrée refusée, pas seulement une exception affichée.

Atelier guidé : lire, expliquer et vérifier le code

SELECT
    '{"amount":null}'::jsonb ? 'amount' AS present,
    '{}'::jsonb ? 'amount' AS absent;

L’opérateur montré teste la présence d’une clé. Dans le premier document, amount existe même si sa valeur est JSON null ; le résultat est vrai. Dans le second, la clé est absente et le résultat est faux. La fixture démontre donc une différence de structure, pas une décision automatique sur la validité métier d’un montant.

Définissez ensuite un contrat de modification partielle et attribuez un sens à chacune de ces situations. Ajoutez un document où amount porte une chaîne invalide. Vérifiez la lecture, la conversion éventuelle et le refus avant écriture. Une bonne migration doit conserver cette distinction tant que les deux formats existent ; traiter toutes les absences comme zéro pourrait modifier silencieusement le sens de données historiques.

Comprendre le mécanisme

MVCC laisse des versions de lignes devenues inutiles après les changements. VACUUM et autovacuum participent à leur gestion et à la prévention du wraparound des identifiants de transaction. ANALYZE met à jour les statistiques utilisées par le planificateur. Une transaction ouverte trop longtemps peut retenir des versions et gêner le nettoyage. Les symptômes doivent être corrélés à l’activité réelle et à la politique de maintenance.

Raisonner et vérifier

Surveillez requêtes lentes, attentes de verrous, durée des transactions, connexions et croissance des objets. Un pool limite et réutilise des connexions, sans rendre les requêtes plus correctes. Définissez des délais adaptés et une procédure d’annulation contrôlée. Une amélioration doit être mesurée avant/après avec des métriques comparables ; évitez une opération lourde choisie uniquement pour faire disparaître un compteur.

Statistiques et choix de plan

Le planificateur estime les lignes utiles à partir d’informations sur les données. ANALYZE met à jour des statistiques qui participent à ces estimations. Une modification importante de distribution peut rendre un diagnostic ancien moins représentatif. Comparez estimations et observations avant de conclure à un problème d’index. Un temps isolé ne dit pas si la cause est une cardinalité mal évaluée ou une attente extérieure au parcours choisi.

Le rôle de VACUUM

Le modèle de versions du moteur peut laisser des tuples devenus inutiles qui demandent une gestion de maintenance. VACUUM participe à ce cycle selon son contrat ; il ne constitue pas une sauvegarde et ne démontre pas une restauration. Gardez ces responsabilités séparées dans les procédures d’exploitation. Une opération de maintenance réussie ne remplace pas un exercice de reprise ni une vérification des données métier.

Transactions longues et rétention de versions

Une transaction ancienne conservant un snapshot peut gêner la récupération de versions et retenir des ressources. L’absence de requête active au moment d’une observation ne signifie pas forcément absence de coût. Relevez durée, état et propriétaire du parcours, puis définissez la bonne action selon le produit. Les tests de reprise doivent aussi vérifier que les connexions reviennent dans un état propre.

Localiser les attentes de verrou

Une latence SQL peut venir d’un verrou détenu ailleurs, pas seulement d’un plan lent. Identifiez la transaction qui attend et celle qui bloque, puis reliez-les aux opérations applicatives. Modifier un index sans cette observation peut laisser la cause intacte. Les preuves doivent préciser la chronologie et les ressources concernées, avec une reproduction de laboratoire lorsque c’est possible.

Observer sans créer une fuite

Les paramètres SQL et traces d’authentification peuvent contenir des informations personnelles ou des secrets. Définissez ce qui doit être collecté pour le diagnostic, les accès et la conservation. Une corrélation et une catégorie d’opération peuvent aider sans recopier toute valeur. Vérifiez les chemins d’erreur et les outils de support : ils ne doivent pas devenir un stockage sensible plus largement accessible que la base.

Relier l’alerte à une action

Une métrique seule n’assure aucune intervention. Une alerte utile possède un seuil justifié, une fenêtre, un responsable et une procédure de vérification. Le traitement doit distinguer symptôme et cause : une file de requêtes peut refléter une dépendance ralentie. Testez le déclenchement puis la disparition de l’alerte dans un environnement maîtrisé, en notant les faux positifs acceptés et les limites de détection.

Mesurer dans des conditions comparables

Les volumes, distributions, caches et charges concurrentes influencent les résultats. Pour comparer deux versions, conservez un scénario qui explique ce qui change et ce qui reste comparable. Une seule observation favorable ne démontre pas toute tendance. La conclusion doit annoncer son périmètre et sa variabilité utile, plutôt qu’un pourcentage de gain extrapolé à toutes les requêtes du service.

Le disque appartient au budget du système

Données, index et journaux consomment un stockage fini. La croissance et les marges de reprise doivent être surveillées avant la saturation. Un service peut continuer de répondre sur un petit contrôle alors qu’une opération utile ne peut plus écrire. Définissez les ressources couvertes par le contrôle de santé et les actions prévues ; l’existence du processus ne prouve pas sa capacité future.

Planifier les opérations et les connexions

Les commandes de maintenance ont des coûts et des contraintes propres. Leur planification doit respecter le trafic et les garanties du service. Un pool peut aider à gérer les connexions, mais ne rend pas automatiquement correctes les transactions ou invariants métier. Revoir la capacité du pool n’est donc pas une correction d’une réservation concurrente ; mesurez les ressources tout en conservant les tests de cohérence applicative.

Compléter la surveillance par une reprise testée

Un tableau de bord vert n’établit pas qu’une sauvegarde sera restaurable après un incident. Les données et procédures de secours doivent être exercées dans un périmètre distinct, avec des vérifications fonctionnelles. Conservez la version, les étapes et les limites de cet essai. La surveillance décrit un état observé du service ; la reprise démontre une capacité différente qui demande ses propres preuves.

Atelier guidé : lire, expliquer et vérifier le code

BEGIN;
SELECT * FROM lab_orders WHERE id = 1;
-- Le client cesse son travail sans COMMIT ni ROLLBACK.

L’extrait ouvre explicitement une transaction, effectue une lecture, puis laisse le client sans COMMIT ni ROLLBACK. Le SELECT ne ferme pas ce cycle à lui seul. La session peut donc conserver un état transactionnel dont la durée devient une information utile au diagnostic, même sans travail applicatif courant.

Dans le laboratoire, observez une session de ce type puis terminez-la proprement selon la procédure choisie. Comparez les ressources et états avant et après. Reproduisez ensuite un retour de connexion au pool pour vérifier que le prochain utilisateur ne récupère pas un état oublié. Le but est de relier la métrique de transaction ancienne à une action contrôlée, et non d’arrêter arbitrairement toutes les connexions dès qu’une latence apparaît.