Guide de survie Postgres pour les startups

 (hatchet.run)
3 points par GN⁺ 4 시간 전 | 1 commentaires | Partager sur WhatsApp
  • À partir des problèmes rencontrés par Hatchet en production pendant 2 ans, ce guide synthétise des principes opérationnels étape par étape, de la conception initiale du schéma et des requêtes jusqu’aux écritures massives et aux migrations de tables
  • Pour des lectures rapides, il faut aligner les index avec ORDER BY, mais comme le query planner peut choisir un scan séquentiel selon les statistiques et les coûts, il faut comparer les estimations et l’exécution réelle avec EXPLAIN ANALYZE
  • Les performances et la stabilité en écriture dépendent de transactions courtes, du verrouillage des seules lignes nécessaires, de CREATE INDEX CONCURRENTLY et du pooling de connexions ; dans les mesures de Hatchet, le traitement par lots a augmenté le débit d’environ 10×
  • Dans les environnements à forte fréquence d’écriture, la configuration par défaut d’autovacuum peut ne pas récupérer à temps les dead tuples et les transaction IDs ; atteindre le transaction ID wraparound entraîne une forte indisponibilité
  • Quand l’échelle augmente, il faut exploiter les files de tâches basées sur FOR UPDATE SKIP LOCKED, le partitionnement, les triggers et les backfills par lots, tout en étant capable de contrôler directement le SQL en dehors de l’abstraction de l’ORM

Public visé et limites des ORM

  • Ce guide est conçu pour aider les développeurs qui connaissent les notions de base de SQL, des lignes, des tables et des index à répondre aux problèmes Postgres en production
  • Le manuel Postgres est complet, mais difficile à consulter rapidement en situation d’incident ; Hatchet en propose donc une synthèse centrée sur 2 ans d’expérience opérationnelle
  • Même avec un ORM, les principes restent applicables, mais à mesure que l’échelle augmente, de nombreuses optimisations exigent de sortir de la couche d’abstraction pour écrire directement du SQL
    • Des fonctionnalités comme Prisma TypedSQL permettent d’utiliser ensemble ORM et SQL direct
    • Hatchet, basé sur Go, utilise sqlc, qui offre un comportement similaire
    • Pour un environnement où Claude écrit les requêtes, supabase/agent-skills est recommandé

Une conception de schéma difficile à modifier

  • Après le déploiement, les changements de schéma sont ce qu’il y a de plus difficile ; il faut donc créer une première version des tables et des clés primaires, puis concevoir de façon itérative en écrivant les requêtes nécessaires à l’application
  • Pendant la conception, les questions suivantes permettent de vérifier comment les tables seront utilisées
    • Qu’est-ce qui est le plus fréquent : les lectures ou les écritures ?
    • Quels sont les filtres les plus utilisés en lecture ?
    • Quelles colonnes sont mises à jour le plus souvent ?
  • On peut appliquer les formes 1NF, 2NF et 3NF de la normalisation des bases de données, mais les formes normales entrent parfois en conflit avec l’efficacité des requêtes ou la facilité d’usage nécessaire au développement rapide
    • Dans certains cas, il est plus simple de placer les données dans une colonne jsonb
  • Les règles empiriques appliquées à la conception du schéma sont les suivantes
    • Pour les clés primaires, utiliser une colonne identity avec entier auto-incrémenté ou un UUID intégré à Postgres
    • Les colonnes identity sont légèrement plus rapides que bigserial
    • Toujours utiliser timestamptz pour les dates et heures
    • Mettre une clé primaire dans chaque table
    • Utiliser des clés étrangères avec cascade delete pour les tables à faible volume où la cohérence et l’exactitude sont importantes, mais rester prudent dans les environnements à fort volume

Requêtes de lecture et index

  • Un modèle simple pour comprendre les SELECT rapides consiste à voir Postgres comme trouvant rapidement une ligne via un index, ou lisant toutes les lignes d’une table via un scan séquentiel (seq scan)
  • Pour trouver rapidement une seule ligne, on utilise les structures suivantes
    • Un index explicite
    • Une unique constraint, forme particulière d’index
    • Une clé primaire, que Postgres indexe automatiquement
  • Les index par défaut utilisent btree, que l’on peut comprendre comme une table séparée stockant les données sous une forme optimisée pour la recherche
    • Le temps de recherche d’une ligne est approximativement log(n), où n est le nombre de lignes de la table
  • Si aucun index ne peut être utilisé, un scan séquentiel est exécuté, mais les bases de données modernes chargent rapidement les lignes en mémoire ; sur des tables de moins de 20 000 lignes, cela peut donc se terminer presque instantanément

Jointures et index composites

  • Les jointures internes devraient généralement cibler des clés primaires ; sinon, cela peut indiquer un problème de conception du schéma ou de normalisation
  • La clause ON doit être traitée comme une clause WHERE, avec des index adaptés aux conditions de jointure
  • Les requêtes de liste sur de grandes tables deviennent souvent les premières requêtes à ralentir dans une application
    • Si l’on filtre et trie à la fois par organisation et par date de création, on peut utiliser un index composite
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • Pour les requêtes complexes, la règle empirique consiste à placer la colonne ORDER BY en dernier dans l’index et à faire correspondre le sens du tri
    • Postgres peut scanner un btree dans les deux sens, donc DESC peut être inutile sur une seule colonne, mais dans un index composite il vaut mieux l’aligner
    • Le fonctionnement détaillé des index descendants est présenté dans ces ressources associées

Écritures, verrous et migrations

  • La première condition d’une écriture réussie est de garder les transactions courtes
    • Sauf raison particulière, ne pas interroger de service externe pendant une transaction
  • La deuxième condition est de ne verrouiller que les lignes nécessaires
    • Lorsqu’une ligne est mise à jour, elle reste verrouillée jusqu’au commit de la transaction
    • Plus la charge du système augmente, plus l’impact des verrous devient visible
  • Exécuter un CREATE INDEX classique sur une grande table existante verrouille la table et bloque les insertions et mises à jour ; il faut donc toujours utiliser CREATE INDEX CONCURRENTLY
  • De bonnes capacités de migration de schéma accélèrent le développement itératif et améliorent la disponibilité
    • Éviter autant que possible de supprimer ou retirer des colonnes, et privilégier les changements par ajout
    • Si possible, exécuter dans une transaction pour gérer les rollbacks et les applications partielles
    • Une approche plus avancée consiste à utiliser les migrations expand and contract
  • Pour une migration, il faut d’abord déterminer si elle bloque toutes les écritures
    • La création d’un index sans CONCURRENTLY peut bloquer toutes les écritures et provoquer une indisponibilité
    • Les opérations ALTER TABLE doivent être réexaminées, et l’ajout d’une check constraint sur une grande table peut aussi bloquer les écritures
    • Ajouter une check constraint avec NOT VALID permet d’éviter ce blocage

Gestion des connexions

  • Toutes les requêtes et transactions utilisent des connexions à la base de données ; comme les connexions coûtent cher en CPU et en mémoire, il faut les conserver longtemps
  • Créer et supprimer fréquemment des connexions gaspille des ressources
    • Une connection storm, où de nombreuses nouvelles connexions apparaissent simultanément, peut provoquer des problèmes difficiles à déboguer liés aux verrous internes de Postgres
  • En priorité, envisager un pooler de connexions externe comme pgbouncer ; si ce n’est pas possible, utiliser un pool de connexions en mémoire comme alternative
    • Hatchet ne pouvant pas supposer que les bases de données de ses utilisateurs utilisent un pooler externe, il utilise pgxpool pour Go

Query planner et statistiques

  • Les requêtes complexes avec de nombreuses jointures ou un mélange de plusieurs types de jointures ne se règlent pas toujours simplement en ajoutant des index
    • Les index ont eux-mêmes un overhead ; il ne faut donc pas en ajouter sans limite
  • Le query planner transforme le SQL en opérations internes de base de données et décide notamment d’utiliser ou non les index, mais il peut choisir un plan non optimal à cause d’informations limitées
  • Les informations utilisées par le planner sont les statistiques de table, consultables dans pg_stats
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • Les statistiques sont collectées lors de ANALYZE et mises à jour aussi lors de l’exécution d’autovacuum
    • Augmenter la fréquence d’autovacuum maintient aussi les statistiques de requête à jour
    • L’une des causes fréquentes de mauvais comportement des requêtes est une fréquence d’analyse insuffisante
  • Juger simplement une requête selon la présence ou non d’un scan séquentiel permet de réduire les micro-optimisations qui augmentent l’imprévisibilité du planner
    • Rechercher principalement via clés primaires et index facilite le choix d’un plan par le planner

Analyse des plans d’exécution et scans séquentiels

  • Certains fournisseurs, comme Google CloudSQL, échantillonnent les requêtes et enregistrent les requêtes lentes, mais tous les services ne le proposent pas
  • EXPLAIN ANALYZE exécute réellement la requête et compare le nombre estimé de lignes selon les statistiques de table avec le nombre de lignes effectivement scannées
    • En production, il faut être prudent, car la vraie requête est exécutée
    • Pour consulter seulement le plan sans exécution, utiliser EXPLAIN sans ANALYZE
  • On peut enregistrer le plan détaillé en JSON puis le visualiser sur explain.dalibo.com
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • Si un scan séquentiel est choisi alors que les statistiques et les index sont corrects, le planner a peut-être calculé que le coût du scan séquentiel est plus faible
    • Les index étant stockés séparément du heap qui contient les vraies données de la table, relire depuis le heap plusieurs lignes trouvées dans l’index a un coût
    • Si la requête ne peut pas être fortement restructurée, il faut accepter le scan séquentiel ou envisager le partitionnement

Écritures massives et traitement par lots

  • Chaque requête comporte des overheads : l’aller-retour avec la base de données, le temps d’obtention d’une connexion dans le pool de connexions applicatif, et le temps de traitement Postgres
    • Les verrous internes de Postgres peuvent aussi devenir un goulot d’étranglement dans les environnements à haut débit
  • Regrouper plusieurs lignes dans une seule requête réduit ces coûts
    • La méthode la plus simple consiste à envoyer plusieurs requêtes au serveur d’un coup, dans une transaction implicite
    • En Go, on peut utiliser SendBatch de pgx
  • Chez Hatchet, le traitement par lots a augmenté le débit d’environ 10×, et d’autres optimisations d’insertion sont présentées dans le guide des insertions Postgres rapides

Autovacuum et transaction ID wraparound

  • Autovacuum se charge du nettoyage des dead tuples et de la gestion des transaction IDs ; dans les environnements à forte fréquence d’écriture, il peut être nécessaire d’ajuster sa configuration
  • Un tuple est une version d’une ligne stockée dans le système de fichiers
    • Lorsqu’une ligne est mise à jour ou supprimée, l’ancienne version reste présente jusqu’à ce que toutes les transactions démarrées auparavant aient été validées ou annulées
    • Une version qui ne peut plus être lue par aucune transaction est un dead tuple
  • Si le rythme d’écriture est trop élevé, autovacuum peut ne pas suivre la vitesse de création des dead tuples, ce qui peut dégrader brutalement l’état de la base de données
  • Si, en consultant les processus actifs dans pg_stat_activity, une requête autovacuum s’exécute depuis environ 1 heure ou plus, il faut envisager de modifier la configuration
  • Si tous les transaction IDs sont épuisés avant qu’autovacuum ne les récupère, un transaction ID wraparound se produit et entraîne une forte indisponibilité

Bloat des tables et des index

  • Postgres stocke les lignes dans des pages de 8 Ko sur disque ; s’il ne peut pas placer une nouvelle ligne dans une page existante, il crée une nouvelle page
  • Lorsque des pages sont partiellement vides après récupération des dead tuples, un table bloat se produit, ce qui peut fortement augmenter l’utilisation disque
    • La meilleure prévention consiste à ajuster autovacuum avant que le bloat n’apparaisse
    • Pour les tables déjà bloatées, on peut utiliser une extension comme pg_repack
    • Le VACUUM FULL intégré est rarement un bon choix
    • Postgres 19 devrait ajouter REPACK...CONCURRENTLY pour le repacking concurrent des tables, mais Hatchet ne l’a pas encore testé
  • Le bloat des index est aussi une forme particulière de bloat de table, et des réglages appropriés d’autovacuum peuvent le réduire
    • Pour les index déjà bloatés, on peut utiliser la commande intégrée REINDEX INDEX CONCURRENTLY

Traitement concurrent basé sur FOR UPDATE SKIP LOCKED

  • FOR UPDATE SKIP LOCKED réserve les lignes sélectionnées pour la transaction en cours sans gêner les autres requêtes
  • Hatchet l’utilise pour sa file de tâches ; une seule requête peut verrouiller les tâches en attente et passer leur statut à RUNNING
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • C’est également utile pour mettre à jour simultanément des lignes indépendantes ou quand plusieurs instances applicatives gèrent le lease d’objets
    • Hatchet l’utilise pour répartir les tenant leases entre plusieurs moteurs

Partitionnement

  • Le partitionnement intégré de Postgres divise les tables selon des valeurs de lignes comme un timestamp ou un hash
  • Pour les données temporelles et les anciennes données de tâches de Hatchet, il apporte les avantages suivants
    • Exécuter autovacuum indépendamment sur chaque partition, ce qui augmente la capacité de traitement d’autovacuum pour la table
    • Supprimer presque instantanément les anciennes données en détachant la table de partition, au lieu de supprimer ligne par ligne
  • Si Postgres ne parvient pas à éliminer les partitions inutiles à l’étape de planification, les requêtes de lecture peuvent subir un overhead

Déplacer des données entre grandes tables

  • Ici, la migration de grandes tables ne désigne pas un changement de schéma, mais le déplacement massif de données d’une table vers une autre
  • Copier une très grande table dans une seule transaction peut prendre des heures
    • Les transactions longues empêchent le fonctionnement normal d’autovacuum et provoquent du bloat lié aux dead tuples
    • Si des écritures continuent d’arriver sur l’ancienne table, ces données ne seront pas reflétées dans la nouvelle table
  • Hatchet exécute de gros backfills par lots hors transaction, et les nouvelles écritures après le début de la migration sont copiées vers la nouvelle table avec des triggers Postgres
    • La unique constraint de la clé primaire permet d’éviter les écritures en double

À lire aussi

1 commentaires

 
GN⁺ 4 시간 전
Avis sur Hacker News

  • Pour une base de données de production, il me semble qu’il faut commencer avant tout par établir un plan de sauvegarde et de restauration. La haute disponibilité peut être optionnelle au début, mais il est étonnant qu’un guide de survie n’inclue pas les sauvegardes et la restauration.
    Je me demande si, pour les sauvegardes PostgreSQL, on utilise encore beaucoup Barman (https://pgbarman.org/) aujourd’hui.

    • Si vous n’êtes pas spécialiste de PostgreSQL, mieux vaut ne pas l’exploiter vous-même et utiliser plutôt une base de données managée comme RDS. Les économies réalisées en l’auto-hébergeant sont minimes par rapport au coût d’une haute disponibilité éprouvée, des sauvegardes/restaurations, de la restauration à un point dans le temps et des réplicas de lecture.
    • J’utilise pgBackRest. Il offre une restauration à un point dans le temps meilleure que notre ancienne solution maison de sauvegardes nocturnes ; je l’ai configuré assez facilement pour sauvegarder vers Backblaze B2, et je n’ai pas eu de problème particulier.
    • Pour la plupart des cas, exécuter pg_dump_all via cron, compresser avec zstd, puis copier vers S3, FTP, etc., suffit largement. Quand les données grossissent, le temps et le coût des sauvegardes complètes deviennent pénalisants, mais cette approche simple permet de tenir assez longtemps.
    • Si la base de données garantit la durabilité même en cas de coupure de courant, on peut la sauvegarder avec des snapshots atomiques de volume. Pour réduire le temps de restauration, il faut d’abord créer un checkpoint ; et pour éviter la corruption des données, l’atomicité du snapshot doit impérativement être garantie.
      Sur AWS, nous avons sauvegardé un MongoDB de plusieurs To avec des snapshots EBS afin de mettre en place des sauvegardes incrémentales et des restaurations rapides. Il n’y a pas de restauration à un point précis, mais comme on peut les prendre fréquemment, à l’échelle de l’heure, c’est une bonne stratégie complémentaire à utiliser avec des outils dédiés à PostgreSQL.
    • Si vous exploitez déjà Kubernetes, vous pouvez utiliser CloudNativePG.

  • Il y a quelques compléments à apporter. Utiliser UUIDv7 plutôt que l’UUIDv4 classique, et, pour éviter les interblocages, uniformiser de façon déterministe l’ordre des verrous dans toutes les requêtes, par exemple id ASC, pas seulement le nombre de lignes verrouillées.
    Avec EXPLAIN (GENERIC_PLAN), on peut copier une requête en conservant les placeholders de paramètres, et voir aussi le plan d’optimisation que PostgreSQL choisit quand il ne connaît pas les valeurs réelles. Sur des tables vides ou petites, SET enable_seqscan = off permet de vérifier si un index peut être utilisé.
    Les index B-tree, utilisés par défaut par tout le monde, sont lourds et ont tendance à gonfler ; si l’on fait seulement des recherches simples, sans tri ni recherche par plage, les index hash peuvent aussi être envisagés. On ne peut pas créer d’index hash unique, mais une contrainte d’exclusion hash permet d’obtenir un effet similaire ; les index uniques multicolonnes ne sont pas pris en charge.
    Il est aussi utile de se familiariser avec les index GIN et GiST. Cela peut surprendre les utilisateurs de MySQL, mais on peut accélérer des requêtes ordinaires LIKE '%foo%' sans passer à la recherche plein texte.

    • Les interblocages ne se produisent pas seulement quand l’ensemble de lignes à verrouiller n’a pas de ORDER BY cohérent, mais aussi quand l’ordre de verrouillage des tables diffère. Si une transaction verrouille table_a puis table_b, et qu’une autre verrouille dans l’ordre inverse, on obtient un interblocage même en utilisant ORDER BY et FOR UPDATE à l’intérieur de chaque table.
      En théorie, c’est évident, mais en pratique le débogage est bien plus difficile, car il faut comprendre globalement quelles tables sont touchées par toutes les écritures ; cela m’est déjà arrivé avec une extension donnée. Je teste GIN pour des recherches clé-valeur JSONB, et le gain de performances est très important ; la différence de performance entre AND et OR l’était aussi.
    • Utiliser n’importe quel UUID comme clé primaire entraîne des jointures fréquentes sur la clé primaire, avec un coût élevé et généralement peu de bénéfices. Par défaut, il est plus sûr d’utiliser une clé primaire séquentielle auto-incrémentée, puis d’ajouter une colonne UUIDv4 avec un index secondaire si elle doit être exposée publiquement. Je me demande si UUIDv7 offre réellement de meilleures performances B-tree qu’UUIDv4.
    • Si l’on désactive les scans séquentiels, j’ai l’impression que PostgreSQL forcera l’utilisation d’un index dès qu’il en existe au moins un. Donc cela ne dira probablement pas s’il s’agit du bon index.
    • Les outils de conversion UUIDv7 et UUIDv4 https://github.com/ali-master/uuidv47 et https://github.com/stateless-me/uuidv47 ont été mentionnés plusieurs fois.

  • Ce conseil est pertinent, mais les startups avec lesquelles j’ai travaillé se heurtaient d’abord à des problèmes organisationnels, bien en amont des questions de scalabilité. Mieux vaut ne pas utiliser d’ORM, employer des clés primaires auto-incrémentées plutôt que des champs porteurs de sens, et limiter l’usage de JSONB aux cas où c’est vraiment nécessaire.
    Les données sources doivent être append-only : on insère, mais on ne modifie ni ne supprime. Les tables auxiliaires dénormalisées, destinées aux performances et au confort, peuvent être modifiées, mais ne doivent pas devenir la source de vérité.
    Utilisez un pool de connexions, mais surveillez le nombre de connexions ; s’il n’y a pas de problème, PgBouncer n’est pas forcément nécessaire. Sans raison claire, évitez les transactions explicites, ne lancez pas d’opérations longues comme des RPC pendant qu’une transaction est ouverte, et mieux vaut aussi éviter presque toujours SERIALIZABLE.
    Si vous avez besoin de verrous explicites comme SELECT FOR UPDATE, il est possible que la conception soit mauvaise. Ne réinventez pas un système de types en donnant plusieurs significations aux lignes d’une même table selon une valeur type int, et n’imitez pas une base de données graphe avec des tables node et edge qui se référencent elles-mêmes. Dans la plupart des cas, des tables relationnelles normalisées classiques suffisent.

    • Dans le backend PHP sur lequel je travaille, il faut instancier des objets pour les contrôles de permissions, entre autres, donc un ORM est très utile. Sans ORM, l’implémentation semble demander beaucoup plus de travail ; je me demande pourquoi ce serait un mauvais choix.
    • Si les salaires des développeurs sont le principal coût, le principe n’utilisez pas d’ORM est discutable. Sous les contraintes des besoins métier des tables, de la pression des clients et d’un budget serré, les coûts continuent de courir pendant qu’on discute longuement avec un DBA de la bonne conception ; éviter les colonnes de type ou les structures façon graphe n’est pas aussi simple qu’il y paraît.
    • Pour une startup qui doit lancer rapidement son produit, un ORM est un choix tout à fait acceptable. Si l’on comprend les pièges comme les requêtes N+1 et le chargement paresseux, c’est un meilleur compromis que de reconstruire soi-même la gestion des requêtes et la paramétrisation.
      Au début d’un projet, je préfère consacrer du temps au développement du produit plutôt que de trop réfléchir au schéma de base de données et d’optimiser prématurément.
    • J’ai utilisé SELECT FOR UPDATE utilement à plusieurs endroits, et je me demande quel est le problème. J’aimerais aussi savoir si une source de vérité append-only rend ce type de verrou inutile.
    • Les données sources append-only sont séduisantes, mais dans plusieurs systèmes sur lesquels j’ai travaillé, elles auraient fait exploser le stockage d’un grand nombre de tables pour des bénéfices douteux. C’est une technique utile, mais je me demande si c’est vraiment un principe à imposer partout.
      À l’inverse, que penser d’une approche où des tables relationnelles modifiables classiques restent la source de vérité, avec des triggers qui enregistrent un journal des changements ?

  • Je n’aime pas les suppressions en cascade. La plupart des développeurs vivent davantage dans la couche applicative — Python, Node, Go — que dans la base de données ; du coup, supprimer une ligne de la table A et voir disparaître aussi des données de la table B peut facilement ressembler à de la magie. Si c’est mal configuré, c’est encore plus dangereux ; pour la maintenabilité à long terme, des instructions de suppression explicites sont préférables, et des clés étrangères bien utilisées suffisent à préserver la cohérence.
    Les pièges des migrations de grandes tables et leurs contournements sont bien réels, mais des outils comme pg-osc existent déjà. Cela devrait être aussi simple que de lancer une commande, puis de surveiller nerveusement pendant les 24 heures où les données sont copiées.
    Il faut séparer tôt les déploiements de l’application et de la base de données. Comme il est impossible de déployer des changements de schéma et d’application de manière parfaitement simultanée et transactionnelle, une fois en production il faut prendre l’habitude de ne faire que des changements de schéma rétrocompatibles : nouvelles colonnes nullable ou avec valeur par défaut, pas de renommage de tables ou de colonnes, etc.
    Il faut aussi choisir tôt une stratégie de gestion de schéma. Évitez les procédures de déploiement où un développeur senior exécute manuellement du DDL sur la base de production depuis son ordinateur ; on peut utiliser des outils familiers comme Liquibase ou Flyway.

    • J’ai créé pgschema, un outil déclaratif de gestion de schéma.

  • Le planificateur de requêtes optimise le cas moyen, mais pour une application il est parfois plus utile d’optimiser le pire cas. Pour l’utilisateur moyen, qui avait peu de lignes, un certain index renvoyait les résultats en moins de 10 ms ; pour les gros utilisateurs, la même requête pouvait prendre plus d’une seconde selon les paramètres.
    En forçant un autre chemin d’index avec une requête plus complexe, les performances moyennes ont légèrement baissé, mais le pire cas est passé sous les 100 ms. Pour l’entreprise, éviter les timeouts était bien plus important que d’économiser 10 ms en moyenne.

  • SKIP LOCKED est utile pour les files de tâches fondées sur des transactions interactives, où l’application garde une transaction ouverte et verrouille des lignes pendant qu’elle travaille. Dans une application haute performance, on évite ce type de transaction et on met immédiatement la ligne à jour en pending, ce qui rend SKIP LOCKED inutile.
    Plus l’échelle augmente, plus il faut réduire l’état maintenu en mémoire par la base de données, et les transactions interactives font partie de cet état. Dans un environnement scalable, l’idempotence est préférable à l’atomicité.

  • Les transactions longues peuvent dégrader l’état de la base de données ; il ne faut les utiliser qu’avec une justification solide. Avec idle_in_transaction_session_timeout, on empêche les transactions inactives de conserver trop longtemps des verrous ou des tuples ; pour les migrations, il faut définir lock_timeout afin qu’un seul DDL ne bloque pas tout le système.
    Il faut aussi définir statement_timeout pour éviter qu’une requête coûteuse ne paralyse le système.

  • Pour avoir exploité PostgreSQL au début d’une startup, je trouve que cet article n’insiste pas assez sur la surveillance et les alertes. PostgreSQL a quelques modes de panne critiques qu’il faut absolument éviter, et des alertes permettent de détecter le danger tôt.
    Même si AWS envoie un e-mail indiquant que l’on approche du bouclage des identifiants de transaction, dans une startup cela peut facilement passer inaperçu, surtout un jour comme le Boxing Day. Les signaux surveillés par AWS doivent être reliés à un pager, pas à de simples e-mails.

  • Il existe de grandes différences peu connues entre les implémentations de pools de connexions. La plupart des pools applicatifs utilisent le premier entré, premier sorti (FIFO) pour optimiser la faible latence et la disponibilité des connexions, mais cela maintient constamment les connexions au chaud, ce qui rend difficile la réduction des connexions inutiles.
    PgBouncer et certains poolers externes utilisent le dernier entré, premier sorti (LIFO) pour optimiser le nombre de connexions qui atteignent PostgreSQL et le débit. En réutilisant d’abord la connexion la plus récente, les connexions excédentaires refroidissent naturellement puis sont fermées.
    Pour une nouvelle application, FIFO suffit, mais à mesure que l’échelle augmente, il vaut mieux utiliser un outil comme PgBouncer pour réduire de l’ordre de 90 % des centaines de connexions. L’architecture de PostgreSQL, qui crée un processus par connexion, fonctionne d’autant mieux que le nombre de connexions est faible.

  • Dans des cas très spécifiques, faire des jointures en mémoire applicative a donné de bons résultats. Il arrive qu’en cherchant à réduire les allers-retours avec la base de données, on finisse par produire une requête unique où s’entremêlent des JOIN, UNION et CASE complexes.
    À la place, exécuter indépendamment plusieurs requêtes simples, puis parcourir les résultats pour relier les lignes associées via une map, peut rendre le plan de requête plus prévisible et donc s’avérer avantageux, même avec des coûts supplémentaires d’allers-retours et d’itérations. À utiliser seulement de façon limitée ; le fait que certains ORM fonctionnent ainsi en interne ne signifie pas que cette approche soit recommandable systématiquement.

    • L’efficacité de cette approche dépend fortement du contexte. Si une jointure produit un produit cartésien beaucoup plus grand que les données d’origine, récupérer uniquement les ensembles sources et les combiner localement peut réduire la charge sur la base de données et le trafic réseau.
      En revanche, une jointure interne sélective produit un résultat bien plus petit que les données d’origine ; récupérer tous les enregistrements pour faire localement l’intersection et le filtrage est donc beaucoup plus coûteux. Avec une jointure sur index, le planificateur de requêtes peut aussi exploiter les index pour éviter un scan complet de table, un tri et un filtrage brutaux.
    • Je crois savoir qu’une autre approche consiste aussi à créer deux vues puis à les joindre, au lieu d’écrire une seule requête complexe.