Comment ralentir Postgres

• Présentation d’une approche expérimentale sur des combinaisons de paramètres capables de dégrader drastiquement les performances de Postgres
• À rebours des bonnes pratiques, l’auteur « optimise » volontairement à l’envers le cache, les index, le WAL, les E/S et d’autres éléments
• En manipulant de façon extrême shared_buffers, autovacuum et les options liées au WAL, il obtient une chute de TPS de 42 000x
• L’expérience exploite aussi des fonctions récentes de Postgres 18/19 comme io_method et io_workers pour imposer une limite à un seul thread d’E/S
• Les résultats montrent qu’il est possible de provoquer une dégradation extrême des performances uniquement via le fichier de configuration de Postgres

---

Vue d’ensemble

Cet article présente une expérience qui prend le contre-pied du tuning Postgres habituellement orienté vers la vitesse : ici, l’objectif est uniquement de le rendre lent, en modifiant uniquement différents paramètres de configuration de PostgreSQL afin de pousser les performances à leur dégradation maximale.
Les tests reposent sur la charge TPC-C de BenchBase (128 entrepôts, 100 connexions, tentative de jusqu’à 10 000 transactions/seconde chacune, Postgres 19devel récent, Ryzen 7950x, 32 Go de RAM, SSD de 2 To, exécution pendant 120 secondes).
Avec les valeurs par défaut, les performances étaient de 7 082 TPS, puis l’auteur observe étape par étape à quel point chaque manipulation de paramètre ralentit le système.

Réduire massivement le cache

• Postgres s’appuie sur un cache puissant (shared_buffers) pour réduire les E/S disque
• En passant de la valeur de base (10 Go) à shared_buffers = 8MB, le TPS tombe à environ 1/7 (1 052 TPS)
• Le taux de cache hit chute de 99,90 % à 70,52 %, et l’on observe une hausse de plus de 300x des appels système de lecture
• Une tentative de réduction jusqu’à 128kB a été faite, mais Postgres n’autorise qu’un minimum d’environ 2MB, ce qui fait encore tomber le TPS à 485

Augmenter les tâches d’arrière-plan (tuning autovacuum)

• Tous les seuils liés à autovacuum sont abaissés au minimum, de sorte qu’un vacuum se déclenche quasiment à chaque opération
  • combinaison incluant autovacuum_vacuum_insert_threshold=1, autovacuum_naptime=1, etc.
  • même lorsqu’il n’y a pratiquement rien à faire, le vacuum se relance presque toutes les secondes
• Pendant cette phase, maintenance_work_mem est réduit à 128kB et toute la journalisation d’autovacuum est activée
• Résultat : le TPS descend à 293 (moins de 1/20 du niveau initial)
• Les logs en temps réel liés à autovacuum confirment que cette activité d’arrière-plan très fréquente est bien la cause de la dégradation

Rendre les écritures WAL (Write-Ahead Log) aussi mauvaises que possible

• Tous les paramètres liés au WAL sont réglés de la pire manière possible
  • wal_writer_flush_after=0, wal_writer_delay=1, wal_sync_method=open_datasync, etc.
  • checkpoint forcé toutes les 30 secondes, avec min_wal_size=32MB et max_wal_size=32MB pour rester au minimum
  • wal_level=logical, wal_log_hints=on pour journaliser même des informations inutiles dans le WAL
  • des surcharges supplémentaires comme track_wal_io_timing et summarize_wal sont aussi activées
• Le TPS tombe alors à 98 (moins de 1/70 du niveau de départ)
• Les logs montrent des comportements anormaux, avec des checkpoints qui se répètent toutes les quelques centaines de ms

Supprimer l’effet des index

• L’usage des index est neutralisé en définissant des valeurs où leur coût est calculé comme maximal (random_page_cost=1e300, cpu_index_tuple_cost=1e300)
• shared_buffers est remonté à 8MB (pour des raisons de stabilité), et le TPS chute à 0,87, soit 7 000x plus lent

Imposer un seul thread d’E/S

• Utilisation d’une fonctionnalité récente de Postgres 18+
• Avec io_method=worker et io_workers=1, toutes les E/S sont forcées à passer par un seul thread worker
• Le TPS baisse encore jusqu’à 0,016, soit 42 000x plus lent
• Dans une expérience avec 100 connexions sur 120 secondes, seules 11 transactions aboutissent

Conclusion et indications de reproduction

• L’auteur montre qu’avec seulement 32 manipulations de paramètres, il est possible de mettre en pratique une base de production dans un état proche de la « paralysie »
• Il est possible de maximiser cette dégradation en ne touchant qu’à postgresql.conf
• Pour reproduire l’expérience, il faut se référer à BenchBase Postgres, à l’environnement TPC-C décrit plus haut, ainsi qu’à la liste complète des réglages
• Certains paramètres additionnels ou tentatives supplémentaires pour ralentir encore davantage n’ont pas été inclus

Liste récapitulative des paramètres

• shared_buffers = 8MB
• seuils / scale_factor liés à autovacuum : abaissés au minimum entre 0 et 1
• coûts, mémoire et logs liés au vacuum : minimisés et maximisés selon le cas
• sync / flush / logs / niveau liés au WAL : configurés pour rester lents
• random_page_cost et cpu_index_tuple_cost liés aux index : définis à 1e300
• io_method = worker, io_workers = 1
• pour les autres valeurs détaillées, voir la liste dans le corps de l’article

Mot de la fin

• Le simple fichier postgresql.conf peut suffire à provoquer une dégradation de performances extrême
• En pratique, cette combinaison peut être utile comme contre-exemple pour améliorer efficacement les performances
• L’article se termine par la mention d’une interruption due à un mal de dos de l’auteur pendant l’expérience

[GN⁺]

Commentaires sur Hacker News

• Présentation d’une approche qui oublie complètement les index, les tables multiples, les transactions, les relations entre entités et l’intégrité référentielle, et utilise toutes les données dans une seule table, à la manière des premières versions de TRIRIGA, comme un KVS NoSQL SQL
• Cette approche est tellement amusante qu’il faudrait en faire une série continue, voire un livre sur « comment encore plus tout gâcher » ; on pourrait ainsi apprendre en trouvant par contraste de meilleures méthodes ; avec une couverture façon O’Reilly illustrée d’un animal fantastique mal dessiné (par ex. une licorne à deux têtes passant un appel sur des AirPods, donnant de l’argent à un escroc, créant un PowerPoint, se goinfrant et prenant des drogues)
  • Il existe un précédent réel de cette stratégie pendant la Seconde Guerre mondiale : pour améliorer les chances de survie des pilotes, des météorologues ont d’abord cherché les conditions causant le plus de pertes humaines, puis ont conçu les missions pour éviter ces conditions ; lien de référence : Suppose I wanted to kill a lot of pilots
  • Dans un cours d’écriture créative, il y avait aussi des exercices consistant à lire et analyser des textes ratés, ou à réécrire volontairement de bons textes en mauvais ; c’était l’exercice d’écriture le plus utile
  • Voir aussi le site parodique ORLYBooks
• Ce serait formidable d’avoir un sandbox en environnement de production pour expérimenter des outils d’observabilité : un système de type SaaS de taille raisonnable, avec simulation d’usage, et une configuration postgres/rabbit simplement correcte pour tester ses outils ou stratégies de débogage
• Cette idée est vraiment géniale ; si l’on veut bien optimiser, il est utile de commencer, ou d’avoir comme groupe témoin, une version volontairement sabotée à l’extrême ; cela pousse à se demander si l’on traite vraiment les bases de données ou les systèmes de manière scientifique, ou si l’on ne fait que suivre des habitudes vagues
  • C’est toujours ce que je fais quand j’utilise un nouveau service cloud : je commence par consommer le plus vite possible le palier Series A, puis seulement après je passe à l’optimisation des coûts cloud
• Le livre The Defence of Duffer's Drift est un exemple ancien de ce genre : dans la première histoire, la tactique d’escouade est si mauvaise que la plupart des hommes sont perdus, puis les récits suivants modifient progressivement les conditions tactiques et les résultats s’améliorent ; des ouvrages tactiques plus récents comme Musicians of Mars 2 suivent la même approche ; la première histoire de Team Badger ressemble beaucoup à Duffer's Drift, mais adaptée selon le lieu, l’équipement et la technologie ; PDF disponibles ici : The Defence of Duffer's Drift et Musicians of Mars 2 ; le fait que le commandant de Team Badger revienne sur les raisons d’une défaite aussi écrasante, malgré sa confiance et sa préparation, est particulièrement marquant
  • Dans un registre similaire au cinéma, il y a Groundhog Day et surtout Edge of Tomorrow, ainsi qu’un hommage moderne publié récemment sur un blog militaire britannique : Defence Baltic Bridge Dreams
• La mention des deadlocks était intéressante ; je me demande si cela abordera aussi le réglage des niveaux d’isolation des transactions
• Heureux de voir une mention de B Sanderson
• On dirait un mélange de Hyperbole and a Half et d’un admin DB ; cela m’a mis de bonne humeur à la lecture, et j’y ai aussi appris quelques choses
• Le style d’écriture et la manière de dérouler la pensée étaient vraiment excellents ; c’était une lecture très agréable

[sonic0987]

Excellent. J’aime beaucoup ce genre d’approche.