Ce que nous détestons le plus dans PostgreSQL (2023)

 (cs.cmu.edu)
4 points par GN⁺ 2024-10-21 | 1 commentaires | Partager sur WhatsApp
  • PostgreSQL s’est récemment imposé comme le choix par défaut sur Internet, mais d’après l’expérience de Carnegie Mellon et d’OtterTune, son implémentation de MVCC entraîne une charge opérationnelle plus élevée que MySQL, Oracle ou Microsoft SQL Server
  • Lorsqu’une ligne est mise à jour, PostgreSQL ne remplace pas la ligne existante : il crée une nouvelle version physique. Ce stockage append-only et la chaîne O2N, qui suit les anciennes versions vers les nouvelles, sont au cœur de sa conception
  • Cette structure entraîne la copie complète des tuples, l’accumulation de dead tuples, la mise à jour de tous les index et une dépendance à autovacuum ; seuls certains coûts peuvent être évités lorsque les mises à jour HOT sont possibles
  • Dans l’analyse des bases PostgreSQL clientes d’OtterTune, environ 46 % des mises à jour en moyenne utilisaient l’optimisation HOT, et les paramètres par défaut d’autovacuum peuvent retarder longtemps le nettoyage sur les grandes tables
  • PostgreSQL reste un DBMS attrayant, mais pour les charges d’écriture importantes et les grandes tables, il faut gérer directement l’espace de stockage, les I/O, la mémoire, la maintenance des index et l’exploitation de vacuum

Pourquoi le MVCC de PostgreSQL pose problème

  • Les choix de bases de données sont nombreux — 897 étaient recensés dans DBDB en avril 2023 — mais selon les périodes, il y a eu des choix de fait par défaut
    • Dans les années 2000, MySQL, utilisé par Google et Facebook, était le choix habituel
    • Dans les années 2010, MongoDB a attiré l’attention avec son image « webscale »
    • Ces cinq dernières années, PostgreSQL a été largement privilégié pour sa stabilité, la richesse de ses fonctionnalités, son extensibilité et son adéquation aux charges de production
  • L’article se concentre sur l’implémentation du contrôle de concurrence multiversion (MVCC) dans PostgreSQL
    • Selon les travaux de Carnegie Mellon et l’expérience d’optimisation d’Amazon RDS PostgreSQL, l’implémentation MVCC de PostgreSQL est jugée moins bonne que celles de MySQL, Oracle et Microsoft SQL Server
    • Amazon Aurora PostgreSQL présente les mêmes problèmes structurels

Objectif du MVCC et choix de conception de PostgreSQL

  • Le MVCC est une approche visant à permettre à plusieurs requêtes de lire et d’écrire simultanément dans la base de données, si possible sans se bloquer mutuellement
    • Le DBMS ne remplace pas les lignes existantes, mais conserve plusieurs versions physiques pour chaque ligne logique
    • Une requête lit la version qui lui convient selon l’ordre des versions, par exemple leur heure de création
    • Cela permet l’isolation par instantané, où une transaction voit l’état de la base de données au moment où elle a démarré
  • Les lectures peuvent ainsi être moins souvent bloquées par des verrous explicites sur des enregistrements mis à jour par des écritures portant sur le même élément
  • Un DBMS MVCC doit principalement décider de trois choses
    • Comment stocker les mises à jour des lignes existantes
    • Comment trouver, au moment de l’exécution, la version de ligne adaptée à une requête
    • Comment supprimer les versions expirées qui ne sont plus visibles
  • À cause du premier choix fait dans les années 1980, PostgreSQL supporte encore aujourd’hui une charge dans les deux autres domaines

Stockage de versions append-only

  • PostgreSQL a été conçu dès le départ pour prendre en charge plusieurs versions ; lors d’une mise à jour, il ne remplace pas la ligne existante, mais en crée une copie puis applique les modifications à la nouvelle version
  • Cette approche peut être vue comme du stockage de versions append-only
    • Lorsqu’un tuple existant est mis à jour, le DBMS réserve dans la table un emplacement libre pour la nouvelle version de la ligne
    • Il copie le contenu de la ligne courante vers la nouvelle version
    • Il applique les modifications dans l’emplacement nouvellement alloué à cette version
  • L’exemple utilise une table movies contenant des informations sur des films
    • id est la clé primaire, avec les colonnes name, year et director
    • Elle possède l’index primaire movies_pkey ainsi que les index B+Tree secondaires idx_name et idx_director
  • Dans une mise à jour qui change l’année de sortie de "Shaolin and Wu Tang" de 1985 à 1983, PostgreSQL copie le tuple original puis applique l’année modifiée à la nouvelle version
    • S’il n’y a pas d’espace sur la page existante, la nouvelle version peut être créée sur une autre page de table

Chaînes de versions et approche O2N

  • Lorsque plusieurs versions physiques représentent la même ligne logique, le DBMS doit enregistrer la relation entre ces versions
    • Les DBMS MVCC créent une chaîne de versions sous forme de liste chaînée unidirectionnelle
    • La chaîne ne va que dans un seul sens afin de réduire le coût en stockage et en maintenance
  • Il existe deux ordres possibles pour les chaînes de versions
    • N2O : la version la plus récente pointe vers la version précédente, et la tête de chaîne est toujours la version la plus récente
    • O2N : chaque version pointe vers la nouvelle version, et la tête de chaîne est la version la plus ancienne
  • La plupart des DBMS, dont Oracle et MySQL, implémentent N2O
  • PostgreSQL utilise rarement O2N, à l’exception notable du moteur In-Memory OLTP de Microsoft SQL Server
    • O2N peut réduire le besoin de mettre à jour les index pour qu’ils pointent vers la nouvelle version à chaque modification d’un tuple
    • En contrepartie, il peut être nécessaire de parcourir une longue chaîne de versions pour trouver la version la plus récente
  • Le champ t_tcid de l’en-tête de ligne PostgreSQL contient l’ID de tuple de la version suivante, ou l’ID de tuple de lui-même s’il s’agit de la version la plus récente
    • Si un index pointe vers une ancienne version, PostgreSQL doit suivre la chaîne pour trouver la nouvelle version

Réduire le parcours des chaînes grâce aux index

  • Les développeurs de PostgreSQL connaissaient dès le début deux coûts
    • Créer une nouvelle copie complète du tuple à chaque mise à jour coûte cher
    • Parcourir toute la chaîne de versions pour trouver la version la plus récente, celle que veulent la plupart des requêtes, est du gaspillage
  • Pour éviter de longs parcours de chaînes, PostgreSQL ajoute une entrée d’index de table pour chaque version physique d’une ligne
    • Si une ligne logique possède 5 versions physiques, il peut y avoir jusqu’à 5 entrées d’index pour ce tuple
    • Si l’index idx_name pointe vers plusieurs versions de "Shaolin and Wu Tang", PostgreSQL peut accéder directement à la version la plus récente
  • L’accès à la version la plus récente peut être accéléré, mais les index grossissent et leur maintenance devient plus coûteuse

Optimisation des mises à jour HOT

  • PostgreSQL utilise les mises à jour HOT (heap-only tuple) pour réduire les cas où les versions liées sont dispersées sur plusieurs pages et où plusieurs entrées d’index sont créées
  • Deux conditions rendent une mise à jour HOT possible
    • La mise à jour ne modifie pas de colonne référencée par les index de la table
    • La nouvelle version peut être stockée sur la même page de données que la version précédente, avec suffisamment d’espace disponible
  • Lorsque HOT s’applique, l’index continue de pointer vers l’ancienne version, et la requête suit la chaîne de versions pour trouver la version la plus récente
  • En fonctionnement normal, PostgreSQL effectue aussi une optimisation qui élague les chaînes de versions en supprimant les anciennes versions

Suppression des dead tuples et vacuum

  • Comme PostgreSQL crée une copie de ligne à chaque mise à jour, il doit supprimer les anciennes versions, les dead tuples
  • Les premières versions de PostgreSQL, dans les années 1980, ne supprimaient pas les dead tuples
    • L’idée était qu’en conservant les anciennes versions, on pouvait exécuter des requêtes de type time-travel pour voir l’état de la base de données à un instant donné
    • Mais sans suppression des dead tuples, la taille des tables ne diminue pas même après des suppressions, et les chaînes de versions des tuples fréquemment mis à jour s’allongent
  • PostgreSQL nettoie les dead tuples des tables via la procédure vacuum
    • Vacuum effectue un scan séquentiel des pages de table modifiées depuis sa dernière exécution et cherche les versions expirées
    • Une version est considérée comme expired si elle n’est visible par aucune transaction active
    • Comme la transaction courante n’accède pas à cette version et que les futures transactions utiliseront la dernière version live, il est sûr de réutiliser l’espace
  • Selon sa configuration, PostgreSQL exécute périodiquement autovacuum de manière automatique
    • Des paramètres globaux permettent d’ajuster la fréquence de vacuum pour l’ensemble des tables
    • Des paramètres autovacuum par table sont également possibles
    • L’utilisateur peut l’exécuter manuellement avec la commande SQL VACUUM

Problème 1 : copie complète des tuples

  • Dans un MVCC append-only, même si une seule colonne d’un tuple change, toutes les colonnes sont copiées dans une nouvelle version
  • Cette approche augmente fortement la duplication des données et les besoins en stockage
    • PostgreSQL peut nécessiter davantage de mémoire et de disque que d’autres DBMS pour stocker la même base de données
    • En conséquence, les requêtes peuvent ralentir et les coûts cloud augmenter
  • MySQL et Oracle stockent un delta compressé entre la nouvelle version et la version courante
    • Dans une table de 1000 colonnes, si une seule colonne change, seul un enregistrement delta pour cette colonne modifiée est stocké
    • PostgreSQL crée une nouvelle version contenant la colonne modifiée et les 999 colonnes inchangées
  • Les attributs TOAST de PostgreSQL étant traités différemment, ils sont exclus de cette comparaison
  • En 2013, EnterpriseDB a lancé le projet zheap pour remplacer le moteur de stockage append-only par une approche basée sur des versions delta
    • La dernière mise à jour officielle est un billet d’état de 2021
    • Depuis, aucune avancée nette n’est visible

Problème 2 : bloat des tables

  • Les versions expirées de PostgreSQL, c’est-à-dire les dead tuples, occupent plus d’espace que des versions delta
  • Dans les workloads write-heavy, les dead tuples peuvent s’accumuler plus vite qu’autovacuum ne parvient à suivre
    • Les tables peuvent continuer à grossir
    • Comme les dead tuples et les live tuples sont mélangés dans les pages, les dead tuples doivent aussi être chargés en mémoire lors de l’exécution des requêtes
    • Un bloat non contrôlé augmente la consommation d’IOPS et de mémoire lors des scans de table, ce qui dégrade les performances des requêtes
    • Si les dead tuples rendent les statistiques de l’optimiseur inexactes, cela peut conduire à de mauvais plans de requête
  • Par exemple, si la table movies contient 10 millions de live tuples et 40 millions de dead tuples, 80 % des données sont obsolètes
    • Avec une taille moyenne de tuple de 1 Ko, les live tuples occupent 10 Go et les dead tuples environ 40 Go
    • La taille totale de la table est de 50 Go
    • Lors d’un scan complet de la table, PostgreSQL doit lire 50 Go depuis le disque et les charger en mémoire, même si la majeure partie est constituée de données obsolètes
  • PostgreSQL dispose de mécanismes de protection pour éviter qu’un scan séquentiel ne pollue le cache du buffer pool, mais ils n’éliminent pas le coût d’I/O lui-même

Différence entre VACUUM et VACUUM FULL

  • Même si autovacuum s’exécute régulièrement et parvient à suivre la charge, un autovacuum normal ne peut pas restituer l’espace de stockage au système d’exploitation
  • Un VACUUM classique supprime les dead tuples et réorganise les live tuples à l’intérieur de chaque page, mais ne récupère pas les pages libres sur disque
  • Si PostgreSQL parvient à vider la dernière page, il peut la tronquer, mais les autres pages restent sur disque
    • Même après suppression de 40 Go de dead tuples dans une table de 50 Go, PostgreSQL peut conserver 50 Go d’espace alloué du point de vue du système d’exploitation ou de RDS
  • Pour restituer réellement l’espace inutilisé, il faut réécrire toute la table dans un nouvel espace avec VACUUM FULL ou pg_repack
    • Ces deux opérations sont gourmandes en ressources et longues
    • Sur une base de données de production, elles peuvent fortement affecter les performances des requêtes
  • VACUUM FULL supprime les dead tuples de chaque page, compacte les live tuples restants en les déplaçant vers de nouvelles pages, puis supprime les pages inutiles

Problème 3 : maintenance des index secondaires

  • Lorsqu’un tuple est mis à jour, PostgreSQL doit mettre à jour tous les index de la table correspondante
  • C’est parce que les index primaires comme secondaires stockent tous la position physique exacte de la version
    • Cette opération est nécessaire à chaque mise à jour, sauf s’il s’agit d’une mise à jour HOT où la nouvelle version est stockée sur la même page que l’ancienne
  • Dans l’exemple de mise à jour, PostgreSQL crée la nouvelle version dans Table Page #2, puis insère dans movies_pkey, idx_director et idx_name des entrées pointant vers cette nouvelle version
  • Une architecture qui impose de modifier tous les index entraîne plusieurs coûts de performance
    • Les requêtes de mise à jour ralentissent
    • Des I/O supplémentaires sont nécessaires pour parcourir chaque index et insérer de nouvelles entrées
    • Il y a de la contention de locks/latches dans les structures internes, comme les index et les tables de pages du buffer pool
    • Des coûts de maintenance sont payés même pour les index que les requêtes réelles n’utilisent pas
    • Dans un DBMS facturé à l’IOPS, comme Amazon Aurora, les lectures et écritures supplémentaires deviennent problématiques
  • Dans l’analyse des bases PostgreSQL clientes d’OtterTune, environ 46 % des mises à jour en moyenne utilisaient l’optimisation HOT
    • Les plus de 50 % restants supportent le coût de maintenance des index
  • Le billet de 2016 d’Uber sur sa migration de Postgres vers MySQL est un exemple représentatif de ce problème
    • Un workload write-heavy sur des tables avec beaucoup d’index secondaires a rencontré d’importants problèmes de performance
  • Oracle et MySQL n’ont pas le même problème, car leurs index secondaires ne stockent pas l’adresse physique de la nouvelle version
    • Les index secondaires stockent un identifiant logique, comme un ID de tuple ou une clé primaire
    • Le DBMS utilise cet identifiant logique pour trouver l’adresse physique de la version courante
    • Les lectures via index secondaires peuvent être plus lentes, mais d’autres avantages de leur implémentation MVCC réduisent l’overhead

Problème 4 : difficulté opérationnelle d’autovacuum

  • Les performances de PostgreSQL dépendent fortement de l’efficacité d’autovacuum, qui supprime les données obsolètes et permet de réutiliser l’espace
  • RDS, Aurora et Aurora Serverless étant tous des variantes de PostgreSQL, ils présentent les mêmes problèmes d’autovacuum
  • Autovacuum est complexe, ce qui le rend difficile à faire fonctionner de manière optimale
    • Les paramètres par défaut ne conviennent pas à toutes les tables
    • Le problème s’aggrave particulièrement sur les grandes tables
  • La valeur par défaut de autovacuum_vacuum_scale_factor est 20 %
    • Sur une table de 100 millions de tuples, au moins 20 millions de tuples doivent être mis à jour pour déclencher autovacuum
    • De nombreux dead tuples peuvent donc rester longtemps dans la table, entraînant des coûts d’I/O et de mémoire
  • Les transactions longues peuvent bloquer autovacuum
    • Si le nettoyage des versions expirées prend du retard, les dead tuples et les statistiques obsolètes s’accumulent
    • Les problèmes de performance peuvent créer davantage de transactions longues, qui bloquent à leur tour autovacuum : un cercle vicieux peut apparaître
    • Dans ce cas, une personne peut devoir arrêter manuellement les transactions longues

Cas clients d’OtterTune

  • Dans une base PostgreSQL Amazon RDS, le nombre de dead tuples a évolué en dents de scie pendant deux semaines
    • Autovacuum effectuait un gros nettoyage environ une fois par jour
    • Le 14 février, le DBMS a nettoyé 3,2 millions de dead tuples
    • Sur l’ensemble du graphe, le nombre de dead tuples était en tendance haussière, un état anormal où autovacuum ne parvenait pas à suivre
  • Sur une instance PostgreSQL RDS d’un client OtterTune, une bulk insertion a entraîné une requête longue à cause de statistiques obsolètes
    • Cette requête a empêché autovacuum de mettre à jour les statistiques
    • En conséquence, d’autres requêtes longues sont apparues
    • Le health check automatique d’OtterTune a identifié le problème, mais l’administrateur a dû arrêter manuellement la requête et exécuter ANALYZE après la bulk insertion
    • Le temps d’exécution de cette requête longue est passé de 52 minutes à 34 secondes

Conclusion pratique

  • La conception d’un DBMS implique toujours des choix difficiles, et ces choix influencent les performances selon les workloads
  • Dans le workload spécifique write-intensive d’Uber, l’amplification des écritures d’index due au MVCC de PostgreSQL a été une raison du passage à MySQL
  • L’implémentation MVCC de PostgreSQL est considérée comme une approche à ne pas suivre si l’on créait aujourd’hui un nouveau DBMS MVCC
    • La combinaison du stockage append-only et d’autovacuum est le problème central
    • Cette conception est un héritage des années 1980, antérieur à la diffusion massive des patterns de systèmes log-structured à partir des années 1990
  • PostgreSQL reste un DBMS apprécié, mais il faut l’exploiter en acceptant les faiblesses de son MVCC
  • Le contournement consiste à consacrer soi-même beaucoup de temps et d’efforts au tuning

À lire aussi

1 commentaires

 
GN⁺ 2024-10-21
Avis de Hacker News

  • Je pensais assez bien connaître les entrailles de Postgres, mais cet article est excellent et m’a beaucoup appris.
    L’une des faiblesses fondamentales semble être que Postgres a choisi, pour le suivi des versions de lignes, une approche O2N plutôt que N2O. Passer à N2O ne résoudrait pas tous les problèmes — il resterait par exemple celui du stockage de copies complètes des lignes —, mais dans une logique 80/20, cela réduirait sans doute la plupart des inconvénients de l’implémentation actuelle.
    Comme la plupart des transactions veulent la version la plus récente d’une ligne, avec un ordre N2O, il ne faudrait suivre la liste chaînée que lorsqu’on a besoin d’une ancienne version, et il serait probablement inutile de stocker chaque version de ligne dans les index.

    • La conférence YouTube d’Andy, History of Databases (CMU Databases / Spring 2020), vaut le détour.
      Il donne tout le premier cours dans les rues d’Amsterdam parce qu’il n’a pas pu entrer dans son hôtel ; le personnage est intéressant et sa capacité à expliquer les mécanismes internes est impressionnante.

  • Le grand avantage, c’est que lorsque la charge de travail consiste surtout en des INSERT puis en la suppression de tables, aucun espace supplémentaire n’est nécessaire.
    En général, il n’est même pas nécessaire de découper les transactions d’insertion. Il n’y a en effet pas de limite pratique à la taille des données créées ni au nombre total de lignes modifiées. Il existe bien une limite au nombre d’instructions dans une transaction, mais si l’on ne modifie pas trop souvent les tables, on peut la contourner avec COPY FROM.
    Du point de vue d’un DBA, il n’est pas nécessaire de gérer un espace de rollback/undo séparément du stockage des tables. Cela dépend des applications, mais la conception de PostgreSQL n’est pas perdante sur tous les plans. Autrement dit, ce n’est pas comme le tri à bulles.

    • En développant des jeux au début des années 2000, j’ai appris que le tri à bulles non plus n’est pas perdant sur tous les plans.
      Il est performant quand la liste est presque triée, ce qui arrive en rendu 3D quand on trie les objets selon leur distance à la caméra. Si l’on déplace ou fait pivoter légèrement la caméra, le tri à bulles fonctionne très bien pour retrier à partir de l’ordre de l’image précédente.
      Pour éviter le pire des cas, il suffit de compter le nombre de comparaisons échouées lors de la dernière passe et le nombre de passes effectuées jusque-là, puis de basculer vers un autre algorithme de tri si un seuil est dépassé.
    • Le tri à bulles est excellent en matériel ou sur des ensembles presque triés.
    • L’article dit littéralement que la conception MVCC de pg est typique des années 90 et que plus personne ne fait ça aujourd’hui.
      C’est une technologie vieille de plus de 30 ans ; même si elle n’est pas perdante sur tous les plans, elle l’est selon moi sur les aspects les plus importants.

  • Je pense que cet article se trompe surtout sur le passage suivant :
    « Dans les années 2000, la sagesse populaire choisissait MySQL parce que des étoiles montantes de la tech comme Google et Facebook l’utilisaient. Dans les années 2010, c’était MongoDB, devenu “webscale” grâce aux écritures non durables. Au cours des cinq dernières années, PostgreSQL est devenu le SGBD préféré d’Internet. Et pour de bonnes raisons ! Il est fiable, riche en fonctionnalités, extensible et adapté à la plupart des charges de travail opérationnelles. »
    Si des ingénieurs compétents choisissaient Postgres, ce n’était pas à cause d’un sophisme d’appel à la popularité, mais pour la sécurité des données, ACID, sa proximité avec Oracle, MVCC, le respect du standard SQL, l’équipe Postgres, une communauté excellente et serviable, les types de données, les hautes performances et la flexibilité de la licence BSD.
    C’est aussi pour ces raisons qu’ATT a choisi Postgres au début des années 2000, et les DBA Oracle ont très facilement accepté la transition. Pendant que MySQL traversait une transition difficile, PG a continué à se renforcer et à s’améliorer. Je pense que Bruce Momjian a joué un grand rôle dans cette réussite, et la communauté est vraiment remarquable.

    • De même, en 2005, ma préférence est passée de MySQL à PostgreSQL.
      Je voulais utiliser des vues de base de données pour créer une couche de compatibilité « live » entre un ancien schéma de base de données AS400 et une application Rails moderne.
      Par la suite, ma préférence n’a fait que grandir grâce à la sécurité des données, au DDL dans les transactions, et à d’autres aspects du même genre.

  • « MySQL et Oracle stockent un delta compressé entre la nouvelle version et la version actuelle (pensez à un git diff). »
    Git est justement connu pour ne pas stocker des diffs, mais plutôt, comme Postgres ici, le nouvel objet et l’ancien objet en entier, non ?

    • La phrase citée ne dit pas que Git stocke des diffs ; elle dit que ce que stockent MySQL et Oracle est similaire à un git diff.
    • Exact. Chaque version d’un fichier est un blob distinct.
      Il y a bien une compression par packing pour accélérer les clones, mais la forme brute que manipule Git, ce sont ces blobs.
    • La comparaison ne porte pas sur le mode de stockage de Git, mais sur le fait que le mode de stockage de MySQL et Oracle ressemble au format git diff.
      Les dépôts Git effectuent aussi de la compression, et cette compression est en quelque sorte basée sur des diffs, mais pas sur l’historique des commits comme on pourrait naïvement s’y attendre.
    • D’autres ont bien parlé de « git diffs », mais Git utilise effectivement des deltas comme optimisation de bas niveau dans les pack files, ce qui ressemble à la comparaison avec MySQL.
      Dans les requêtes SQL non plus, le diff n’est pas renvoyé tel quel.
    • Un git diff est généré à la volée, mais un diff reste un diff.

  • « Le fait que PostgreSQL doive modifier tous les index d’une table à chaque mise à jour a plusieurs conséquences sur les performances. Évidemment, comme le système doit faire davantage de travail, les requêtes update sont plus lentes. »
    Je m’interrogeais sur cette amplification d’écriture. Il est vrai que MySQL n’a pas besoin de mettre à jour les index de cette façon, mais la réplication MySQL repose sur le binlog, et chaque modification doit être enregistrée en plus de l’enregistrement par la base elle-même (redo log InnoDB, etc.).
    Donc MySQL utilisé en cluster semble avoir un autre type d’amplification d’écriture. PostgreSQL réutilise le WAL pour la réplication, il n’a donc pas cette amplification.
    De plus, côté récepteur, MySQL écrit d’abord le binlog entrant dans un relay log, puis le thread applier consomme ce relay log, générant des écritures InnoDB supplémentaires et, avec la configuration par défaut, un binlog supplémentaire.

  • Ce sujet ne peut pas être discuté sans parler du disque
    Un SSD écrit des pages de 4 Ko à la fois. Autrement dit, même si l’on ne met à jour qu’un seul bit, le disque lit 4 Ko, modifie le bit, puis réécrit une page de 4 Ko dans un nouvel emplacement. La pénalité de copie dépend donc du type de disque

    • Intéressant. Je me demande comment cela se reflète dans la facturation AWS
      AWS facture un forfait sur les Mo/s d’IO, mais je ne sais pas s’il existe une règle d’arrondi aux 4 Ko les plus proches, ou s’ils suivent le volume d’écriture du disque lui-même plutôt que le volume de requêtes, afin de facturer la quantité réelle d’IO au niveau de l’implémentation du stockage
    • Les pages Postgres font 8 Ko, donc cet argument n’a pas vraiment de sens

  • « Oracle et MySQL n’ont pas ce problème dans leur implémentation de MVCC. C’est parce que les index secondaires ne stockent pas l’adresse physique de la nouvelle version. Ils stockent à la place un identifiant logique (par exemple un ID de tuple ou une clé primaire), que le SGBD utilise pour trouver l’adresse physique de la version courante. Cela peut ralentir les lectures via les index secondaires, mais ces SGBD ont d’autres avantages dans leur implémentation de MVCC qui réduisent l’overhead. »
    J’ai observé un comportement intéressant dans MySQL. C’était une base d’environ 500 Go, avec un schéma plus proche d’une approche orientée document que relationnelle, et SELECT id WHERE something; UPDATE what WHERE id=id était plusieurs ordres de grandeur plus rapide que UPDATE what WHERE something
    Je pense que la raison de ce comportement est probablement là. Cela dit, les charges de travail ordinaires ne procèdent pas ainsi, et ce n’est lent que pour des DML temporaires destinés à corriger des incohérences

    • SELECT est une opération en lecture seule, donc elle peut être exécutée en parallèle
      En revanche, UPDATE effectue réellement une écriture et peut aussi verrouiller la table. UPDATE id=id permet un verrouillage au niveau ligne. Il existe toutefois un risque de manquer des enregistrements nouvellement insérés entre le SELECT et l’UPDATE
    • J’exploite plusieurs instances Postgres de plus de 2 To, principalement orientées lecture, et elles aussi sont assez proches d’une approche orientée document
      Je suis d’accord pour dire que les mises à jour massives peuvent être très lentes. Au final, on finit souvent par traiter les mises à jour progressivement par lots, voire par utiliser COPY

  • « Dans les années 2010, c’était MongoDB. Parce que ses écritures non durables l’avaient rendu “webscale”. »
    C’est hors sujet, mais du début à la fin, c’était du marketing : https://news.ycombinator.com/item?id=15124306

    • MongoDB a été conçu par d’anciens ingénieurs de DoubleClick comme une base DIY créée au passage, faute de base de données répondant aux exigences dont ils avaient besoin pour un autre service
      La version 4.2.8 (2020) était devenue assez solide, et elle est réputée ne pas faire d’écritures sales : https://en.wikipedia.org/wiki/MongoDB#Technical_criticisms

  • Ce que je déteste le plus, c’est qu’en 2024 il faille encore mettre un pooler de connexions en amont (par exemple pgbouncer) pour que ce soit utilisable

  • OrioleDB a essayé de résoudre ce problème avec un nouveau moteur de stockage : https://github.com/orioledb/orioledb

    • Il est chez l’équipe Supabase
      Oriole a désormais rejoint Supabase, et Alexander et son équipe y travaillent à plein temps. Le patchset est ici : https://www.orioledb.com/docs#patch-set
      Il devrait aussi être possible de l’essayer sur la plateforme Supabase d’ici la fin de l’année