MySQL vs PostgreSQL : pourquoi la même requête est rapide dans l’un et catastrophique dans l’autre

Mode opératoire de diagnostic rapide

Quand une requête est rapide dans MySQL et catastrophique dans PostgreSQL (ou l’inverse), résistez à la tentation de commencer à « régler » des boutons. Trouvez d’abord dans lequel des trois cas vous vous trouvez : plan mauvais, bloqué ou IO-bound. Ensuite vous pourrez corriger le vrai problème plutôt que de réarranger le feng shui du serveur.

Première étape : prouvez que c’est un problème de plan (ou non)

1. Capturez le plan :
   • Postgres : EXPLAIN (ANALYZE, BUFFERS)
   • MySQL : EXPLAIN ANALYZE (8.0+) et SHOW WARNINGS si nécessaire
2. Cherchez les mensonges de cardinalité : lignes estimées vs lignes réelles. Si l’écart est de 10x, vous êtes déjà en difficulté. Si c’est 100x, vous êtes en intervention d’incident.
3. Identifiez le nœud dominant : l’étape avec le temps réel le plus élevé (Postgres) ou le plus grand « actual time »/loops (MySQL 8.0 analyze). C’est là que vous vous concentrez.

Deuxième étape : éliminez le blocage et les transactions longues

1. Postgres : vérifiez pg_stat_activity pour wait_event et les transactions longues.
2. MySQL : vérifiez SHOW PROCESSLIST et les attentes de verrou InnoDB.
3. Dans les deux : si un écrivain détient des verrous ou qu’une transaction longue empêche le nettoyage, vous pouvez optimiser indéfiniment et perdre quand même.

Troisième étape : décidez si vous êtes IO-bound, CPU-bound ou memory-bound

1. Si le plan montre beaucoup de misses de buffers / lectures, vous êtes IO-bound.
2. Si le CPU est saturé et que le plan montre de gros tris/hashes, vous êtes CPU/mémoire-bound.
3. Si les pics de latence coïncident avec les checkpoints, flushs ou la pression d’fsync, vous êtes lié au stockage.

Paraphrasant l’idée de Werner Vogels : « Concevez pour l’échec comme condition normale. » Appliquez cela à la performance des requêtes aussi — concevez et exploitez en partant du principe que les plans se tromperont parfois.

Pourquoi « la même requête » n’est rarement la même

En production, « même requête » signifie généralement « même chaîne SQL ». Les bases tiennent compte du reste : détails du schéma, règles de collation, types de données, index, statistiques et configuration, ainsi que la concurrence. Une requête peut être logiquement équivalente tout en étant physiquement très différente.

Types de données et conversions implicites : les tueurs de plans silencieux

PostgreSQL est strict sur les types et introduira volontiers une conversion implicite qui empêche l’utilisation d’un index. MySQL est plus permissif et « aide » parfois en convertissant des valeurs ; parfois cette « aide » devient un scan complet avec le sourire.

Exemple classique : comparer une colonne uuid à un paramètre texte dans Postgres peut forcer un cast côté colonne, rendant l’index inutilisable. MySQL pourrait comparer des chaînes et utiliser un index si la collation et les règles de préfixe s’alignent. Ou pas. La cohérence est une caractéristique que vous devez mériter.

Collations et comparaisons de texte

Les collations de MySQL (surtout les défauts insensibles à la casse) peuvent modifier l’indexabilité et les comparaisons. Le comportement de collation de PostgreSQL dépend de libc/ICU et peut affecter l’ordre de tri, les classes d’opérateurs d’index et la performance. Si vous avez migré les données sans migrer les collations de manière intentionnelle, vous n’avez pas migré la charge — vous l’avez déplacée en espérant qu’elle se comporte de la même façon.

Paramétrage et cache des plans

PostgreSQL peut choisir un plan générique pour les statements préparés qui est « correct en moyenne » mais mauvais pour une valeur de paramètre courante. Le comportement de MySQL varie selon le connecteur et si vous utilisez des préparations côté serveur. Si votre appli a changé la façon dont elle lie les paramètres, votre plan peut changer drastiquement même si la chaîne SQL semble identique.

Blague #1 : Les optimiseurs de requêtes sont comme des tout-petits : ils peuvent être brillants, mais si vous les surprenez avec une nouvelle distribution de données, ils jettent le plan par terre.

Contexte historique : pourquoi ces différences existent

• Fait 1 : PostgreSQL descend du projet POSTGRES à l’UC Berkeley (fin des années 1980), qui faisait du planning de requêtes et de l’extensibilité des objectifs de conception prioritaires.
• Fait 2 : MySQL a démarré au milieu des années 1990 avec un focus sur la rapidité et la simplicité pour les charges web ; les premières versions avaient un support limité des sous-requêtes et un optimiseur plus simple.
• Fait 3 : InnoDB est devenu le moteur par défaut de MySQL en 5.5 ; avant cela, beaucoup de déploiements utilisaient MyISAM avec des caractéristiques de verrouillage et de durabilité différentes.
• Fait 4 : Le MVCC de PostgreSQL a longtemps été central, mais il nécessite le vacuum pour récupérer l’espace — la performance peut se dégrader si vous ignorez ce cycle de vie.
• Fait 5 : MySQL 8.0 a introduit un dictionnaire de données plus capable et des améliorations d’optimiseur ; les conseils datant de l’ère 5.6 peuvent être activement nuisibles.
• Fait 6 : PostgreSQL 12+ a amélioré le partitionnement et le comportement du planificateur pour les partitions ; les versions plus anciennes pouvaient mal planner les requêtes partitionnées.
• Fait 7 : La réplication MySQL a historiquement été basée sur des déclarations ou sur des lignes avec des compromis différents ; les réplicas en lecture peuvent se comporter différemment sous charge à cause du lag d’application et du metadata locking.
• Fait 8 : Le JIT de PostgreSQL (LLVM) peut accélérer certaines requêtes mais ajoute aussi un overhead ; cela dépend de la charge et peut vous surprendre dans les systèmes sensibles à la latence.

Comportements de l’optimiseur qui changent tout

Estimation de cardinalité : la base devine, et elle devine différemment

La plupart des histoires « rapide dans DB A, lent dans DB B » se résument à une estimation du nombre de lignes. L’optimiseur choisit l’ordre de jointure et les algorithmes de jointure en fonction des lignes estimées. S’il se trompe, il choisit la mauvaise arme.

PostgreSQL : utilise les statistiques de ANALYZE : statistiques par colonne, valeurs les plus fréquentes, histogrammes, corrélation et (optionnellement) statistiques étendues entre colonnes. C’est performant, mais pas télépathique. Si vos prédicats impliquent plusieurs colonnes corrélées, les stats par défaut peuvent sous-estimer fortement.

MySQL : utilise les statistiques du moteur ; InnoDB peut avoir des stats persistantes ou recalculées ; des histogrammes existent et aident en 8.0. Sans histogrammes, InnoDB peut supposer une distribution uniforme et faire des choix tragiques quand les données sont biaisées.

Algorithmes de jointure et préférences des moteurs

PostgreSQL dispose d’un bel arsenal : nested loop, hash join, merge join. Il peut aussi faire des bitmap index scans pour combiner plusieurs index. MySQL s’est historiquement appuyé fortement sur les nested loops (y compris block nested loop) et s’est amélioré, mais son approche diffère.

Cela compte parce qu’une « grosse jointure » peut être rapide si le planner choisit hash join sur de grands ensembles, ou catastrophique s’il choisit nested loop avec un résultat interne mal estimé.

CTE, sous-requêtes et clôtures d’optimisation

Les anciennes versions de PostgreSQL traitaient les CTE (WITH) comme des clôtures d’optimisation : elles étaient matérialisées et empêchaient le pushdown de prédicats. Les versions plus récentes peuvent inline les CTE, mais pas toujours. MySQL traite les tables dérivées différemment et a ses propres règles de matérialisation.

Si votre « même SQL » utilise des CTE, l’interprétation du planner peut faire la différence entre millisecondes et « annulé par l’astreinte ».

Tri, hachage et paramètres mémoire

Postgres utilise work_mem par opération de tri/hash, par nœud de requête, par worker parallèle. Une requête peut lancer plusieurs tris ; le régler trop haut provoque des pics mémoire. Le régler trop bas et vous allez déborder sur disque et pleurer.

MySQL utilise d’autres structures mémoire (join buffer, sort buffer, tmp tables). Il créera des tables temporaires sur disque selon la taille et les types. Les deux moteurs peuvent sembler « lents CPU » alors qu’ils déversent sur disque lentement.

Indexes : même idée, bords tranchants différents

B-tree n’est pas une promesse, c’est un contrat avec des petits caractères

Les deux moteurs utilisent les index B-tree pour la plupart des usages. Mais la façon de les utiliser diffère : index-only scans, index couvrants, visibility maps, comportement proche du clustering, et si le moteur peut appliquer des conditions au niveau de l’index.

PostgreSQL : index-only scans dépendant de la visibilité

Postgres peut faire un index-only scan s’il peut prouver que les tuples sont visibles sans toucher le heap. Cela dépend de la visibility map, qui dépend du vacuum. Si votre table subit beaucoup de churn et que le vacuum ne suit pas, votre « index couvrant » ne couvre pas à l’exécution. Le plan peut toujours indiquer index-only, mais les fetchs vers le heap révéleront la vérité.

MySQL/InnoDB : la clé primaire est la table

InnoDB stocke les lignes clusterisées par clé primaire. Les index secondaires pointent vers la clé primaire, pas directement vers l’emplacement de la ligne. Cela signifie que les recherches via index secondaire peuvent nécessiter des sauts supplémentaires. Cela signifie aussi que le choix de la clé primaire affecte la localité, les splits de pages et le comportement du cache. Une clé primaire UUID peut convenir avec le bon format et la bonne stratégie d’insertion, mais ce n’est pas gratuit.

Index multi-colonnes : l’ordre compte plus qu’on ne le croit

MySQL et Postgres tiennent compte des colonnes de tête. Mais leurs optimiseurs diffèrent dans la façon de combiner les index (bitmap scans dans Postgres peuvent être très efficaces ; MySQL a index merge mais ce n’est pas un substitut universel). Si une requête est rapide dans Postgres parce qu’elle bitmap-AND deux index mono-colonne, le même schéma dans MySQL peut nécessiter un index composite pour éviter de scanner.

Index fonctionnels et support d’expressions

Postgres est à l’aise avec les index d’expression (comme lower(email)) et les index partiels (comme WHERE deleted_at IS NULL). MySQL a des index fonctionnels (via des colonnes générées) et peut faire des choses similaires, mais l’ergonomie diffère. Si votre requête repose sur le « patron naturel » d’un moteur, la porter peut être trompeusement lente jusqu’à ce que vous réexprimiez la stratégie d’index.

MVCC, vacuum et le coût caché des lignes mortes

MySQL (InnoDB) et PostgreSQL utilisent tous deux MVCC. Ils en paient le prix différemment.

Inflation de PostgreSQL : quand « supprimé » n’est pas parti

Dans Postgres, les mises à jour créent de nouvelles versions de lignes. Les suppressions marquent des lignes mortes. L’espace est récupéré plus tard. Si autovacuum ne suit pas — à cause de transactions longues, de mauvais réglages ou d’un churn trop important — votre table et vos index gonflent. Les plans qui étaient favorables aux index deviennent IO-bound. La dette de vacuum ressemble à une dette de carte de crédit : les paiements minimaux paraissent gérables jusqu’à ce que ce ne soit plus le cas.

Purge MySQL et longueur de la history list

InnoDB conserve aussi d’anciennes versions dans les undo logs et les purge quand c’est sûr. Les transactions longues peuvent faire croître la history list length, créant du travail supplémentaire pour la purge et augmentant la surcharge de lecture pour certains workloads. C’est une douleur de forme différente, mais toujours une douleur.

Visibilité et « pourquoi lit-il autant ? »

Si une requête Postgres commence à effectuer beaucoup de fetchs heap, vous avez souvent un problème de vacuum/visibilité. Si un workload InnoDB commence à ralentir sur la purge ou montre une pression croissante sur les undo, vous avez des problèmes de cycle de vie des transactions. Aucun des deux n’est résolu par l’ajout d’un autre index « au cas où ».

Verrous et isolation : quand la justesse coûte la latence

Différences par défaut, surprises différentes

Le niveau d’isolation par défaut de PostgreSQL est READ COMMITTED. Le défaut de MySQL (souvent) est REPEATABLE READ pour InnoDB. Cela peut changer ce que les lectures voient et la durée pendant laquelle certains verrous metadata et gap locks sont détenus. Le résultat : la même « requête de lecture » peut être bloquée différemment selon les écritures concurrentes et les frontières de transaction.

Verrous metadata et DDL

Le metadata locking de MySQL peut provoquer des blocages surprenants lors de l’exécution de DDL. PostgreSQL a ses propres comportements de verrouillage autour des changements de schéma. La règle opérationnelle est la même : traitez les DDL comme vous traitez les déploiements — planifiés, testés et chronométrés. Si vous lancez un « ALTER rapide » à midi, vous auditionnez pour la rotation de l’astreinte.

Blague #2 : Rien ne vous rajeunit plus vite qu’un « changement de schéma en ligne » qui se révèle « en ligne, comme : tout le monde est en ligne en train de le regarder bloquer ».

Cache et IO : votre stockage fait partie de la base

À l’échelle, le temps de requête est souvent du temps de stockage avec un meilleur marquage.

Buffer pools vs shared buffers : même maths, même physique

MySQL/InnoDB a le buffer pool, typiquement dimensionné grand (60–80 % de la RAM sur des machines dédiées, ajusté selon la charge et les autres besoins mémoire). PostgreSQL a shared_buffers plus le cache de page du noyau qui fait une grande partie du travail. Cela affecte le comportement chaud vs froid. Une requête « rapide » sur un buffer pool MySQL chaud peut être « brutale » sur une instance Postgres qui s’appuie davantage sur le cache OS et qui concurrence d’autres processus ou a un réglage mémoire différent.

Checkpoints, fsync et amplification d’écriture

Les deux moteurs sont durables par défaut, mais leurs schémas d’écriture diffèrent. Les checkpoints PostgreSQL peuvent produire des rafales d’IO sauf si bien réglés ; le redo log et le doublewrite buffer de MySQL ont leur propre empreinte IO. Si votre stockage a des IOPS limités ou une forte variance de latence, la base convertira fidèlement cela en misère côté utilisateur.

Pièges de stockage que les SRE rencontrent réellement

• Stockage bloc cloud avec crédits burst : « rapide jusqu’à ce que ça ne le soit plus ».
• Volumes en réseau avec voisins bruyants : parfaits pour les sauvegardes, douteux pour les données chaudes.
• Mauvais alignement RAID / paramètres du système de fichiers : vous pouvez vous illusionner par benchmarking.
• Compression et chiffrement : excellentes fonctionnalités, pas gratuites.

Tâches pratiques : commandes, sorties et décisions (12+)

Ceci sont les actions que vous faites à 02:00 quand le tableau de bord est rouge et que la théorie fait une pause cigarette. Chaque tâche inclut une commande, un extrait réaliste de sortie, ce que cela signifie et la décision suivante.

Task 1: PostgreSQL — obtenir le vrai plan avec le timing et les buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > now()-interval '7 days';"
Hash Join  (cost=1250.11..9821.44 rows=42000 width=8) (actual time=38.221..412.903 rows=39812 loops=1)
  Output: o.id
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=10231 read=821
  ->  Seq Scan on public.orders o  (cost=0.00..7260.00 rows=500000 width=16) (actual time=0.020..220.111 rows=480321 loops=1)
        Output: o.id, o.customer_id
        Filter: (o.created_at > (now() - '7 days'::interval))
        Rows Removed by Filter: 120034
        Buffers: shared hit=8121 read=701
  ->  Hash  (cost=1025.00..1025.00 rows=18000 width=8) (actual time=37.902..37.904 rows=18210 loops=1)
        Buffers: shared hit=2110 read=120
Planning Time: 1.102 ms
Execution Time: 418.771 ms

Ce que cela signifie : Le Seq Scan sur orders domine et lit 701 buffers depuis le disque. Votre filtre sur created_at n’utilise pas d’index (ou le planificateur a décidé de ne pas l’utiliser).

Décision : Vérifiez si un index sur (created_at) ou (created_at, customer_id) existe et s’il est sélectif. Vérifiez aussi les statistiques et si les 7 derniers jours représentent une grande fraction de la table.

Task 2: MySQL — obtenir le plan et le timing réel

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY\G"
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=12453.20 rows=40000) (actual time=0.112..52.883 rows=39812 loops=1)
    -> Index lookup on c using idx_segment (segment='enterprise')  (cost=210.12 rows=18000) (actual time=0.041..6.233 rows=18210 loops=1)
    -> Index lookup on o using idx_customer_created (customer_id=c.id, created_at>(now() - interval 7 day))  (cost=0.62 rows=3) (actual time=0.002..0.002 rows=2 loops=18210)

Ce que cela signifie : MySQL part de customers en utilisant idx_segment puis interroge orders via un index composite. C’est pourquoi c’est rapide.

Décision : Si Postgres scanne orders, vous avez probablement besoin de l’index composite (ou de meilleures stats) pour encourager un plan basé sur l’index.

Task 3: PostgreSQL — vérifier les index existants sur une table

cr0x@server:~$ psql -d appdb -c "\d+ orders"
Table "public.orders"
Column       | Type                     | Collation | Nullable | Default | Storage | Stats target | Description
-------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
id           | bigint                   |           | not null |         | plain   |              |
customer_id  | bigint                   |           | not null |         | plain   |              |
created_at   | timestamp with time zone |           | not null | now()   | plain   |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "idx_orders_customer_id" btree (customer_id)

Ce que cela signifie : Il n’y a pas d’index sur created_at et pas de composite sur (customer_id, created_at).

Décision : Ajoutez un index composite qui corresponde à votre motif de jointure + filtre, ou un index partiel si vous interrogez seulement des données récentes.

Task 4: PostgreSQL — créer un index composite sans tout interrompre

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);"
CREATE INDEX

Ce que cela signifie : La création d’index s’est terminée sans bloquer les lectures/écritures (cela prend toujours du temps et de l’IO).

Décision : Relancez EXPLAIN (ANALYZE, BUFFERS). Si le plan change et que la latence s’améliore, déployez. Sinon, vous avez probablement un problème de statistiques/sélectivité ou un autre goulot.

Task 5: PostgreSQL — rafraîchir les statistiques maintenant (quand c’est sûr)

cr0x@server:~$ psql -d appdb -c "ANALYZE orders; ANALYZE customers;"
ANALYZE
ANALYZE

Ce que cela signifie : Les stats du planificateur ont été rafraîchies. Si vous avez chargé des données récemment ou que la distribution a changé, cela peut corriger de mauvais plans.

Décision : Si les estimations étaient très erronées, revérifiez le plan. Si des corrélations existent entre colonnes, envisagez des statistiques étendues.

Task 6: PostgreSQL — trouver rapidement le décalage estimation vs réel

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE) SELECT * FROM orders WHERE created_at > now()-interval '7 days';"
Seq Scan on orders  (cost=0.00..7260.00 rows=5000 width=64) (actual time=0.012..189.442 rows=480321 loops=1)
Planning Time: 0.211 ms
Execution Time: 205.118 ms

Ce que cela signifie : L’estimation (5 000 lignes) est loin de la réalité (480 321 lignes), soit ~96x. C’est la preuve évidente.

Décision : Augmentez le stats target pour created_at, ajoutez des statistiques étendues ou repensez le prédicat/l’indexation (par ex. partitionnement par date).

Task 7: PostgreSQL — augmenter le stats target pour une colonne biaisée

cr0x@server:~$ psql -d appdb -c "ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000; ANALYZE orders;"
ALTER TABLE
ANALYZE

Ce que cela signifie : Postgres conservera des statistiques plus détaillées pour created_at. Le planning devient plus lent, l’exécution souvent plus rapide (valable pour les requêtes chaudes).

Décision : Si la requête est fréquente et sensible, c’est un compromis raisonnable. Si c’est de l’analytics ad hoc, peut-être pas.

Task 8: MySQL — confirmer l’usage d’index et le type d’accès

cr0x@server:~$ mysql -e "EXPLAIN SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY;"
id	select_type	table	type	possible_keys	key	key_len	rows	Extra
1	SIMPLE	c	ref	idx_segment	idx_segment	1022	18210	Using where
1	SIMPLE	o	range	idx_customer_created	idx_customer_created	16	39812	Using where; Using index

Ce que cela signifie : type=ref/range et une clé spécifique indiquent un accès par index. « Using index » suggère un index couvrant pour les colonnes sélectionnées.

Décision : Si vous voyez type=ALL de façon inattendue, vous scannez. Inspectez alors les définitions d’index et la sargabilité des prédicats.

Task 9: MySQL — vérifier le comportement du hit rate du buffer pool InnoDB

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"
Variable_name	Value
Innodb_buffer_pool_reads	1842231
Variable_name	Value
Innodb_buffer_pool_read_requests	987654321

Ce que cela signifie : reads sont des lectures physiques ; read_requests sont logiques. Si les lectures physiques augmentent fortement pendant la fenêtre lente, vous allez sur le disque.

Décision : Si le hit rate est mauvais, augmentez le buffer pool (si la RAM le permet), corrigez le working set (index, requête) ou traitez la latence IO.

Task 10: PostgreSQL — vérifier les blocages et les transactions longues

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-xact_start AS xact_age, query FROM pg_stat_activity WHERE datname='appdb' ORDER BY xact_age DESC LIMIT 5;"
 pid  | usename | state  | wait_event_type |  wait_event   | xact_age |                   query
------+--------+--------+-----------------+---------------+----------+--------------------------------------------
 9123 | app    | active | Lock            | transactionid | 00:41:22 | SELECT ... FROM orders ...
 8877 | app    | idle in transaction | Client | ClientRead | 02:13:10 | UPDATE customers SET ...

Ce que cela signifie : Une session « idle in transaction » est ouverte depuis des heures. Cela peut bloquer le vacuum et causer du bloat, et indirectement ruiner les performances de lecture.

Décision : Corrigez la gestion des transactions dans l’appli. À court terme, terminez la session si c’est sûr et que vous comprenez le rayon d’impact.

Task 11: MySQL — trouver les attentes de verrou et qui bloque

cr0x@server:~$ mysql -e "SHOW PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
221	app	10.0.1.10:42218	appdb	Query	28	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN foo INT
305	app	10.0.1.11:53122	appdb	Query	3	Sending data	SELECT ... FROM orders ...

Ce que cela signifie : Un DDL attend un metadata lock ; cela peut aussi faire s’accumuler d’autres sessions selon le timing et l’ordre des verrous.

Décision : Mettez en pause/tuez le DDL s’il impacte la production et re-planifiez. Pour l’avenir, utilisez des méthodes de changement de schéma en ligne adaptées à votre version.

Task 12: PostgreSQL — vérifier les indicateurs de bloat de table et d’index

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |     last_autovacuum
-------------+------------+------------+----------------------------
 orders      |    5200000 |    2100000 | 2025-12-29 01:12:03+00
 events      |   22000000 |    9800000 | 

Ce que cela signifie : Beaucoup de tuples morts, et une table n’a pas été autovacuumée récemment (ou jamais). Cela peut gonfler les scans et dégrader les index-only scans.

Décision : Examinez les réglages d’autovacuum et les transactions longues ; envisagez un VACUUM (ANALYZE) manuel pendant une fenêtre de maintenance et corrigez la cause racine.

Task 13: Linux — confirmer si vous êtes lié à la latence IO maintenant

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    5.11   24.33    0.00   52.34

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await  aqu-sz  %util
nvme0n1         820.0  65536.0     0.0   0.00   18.40    79.90   210.0  18432.0   25.12   12.33   98.70

Ce que cela signifie : %util élevé et r_await/w_await élevés avec un %iowait notable. Le stockage est saturé ou lent.

Décision : Arrêtez de blâmer l’optimiseur pour la physique. Réduisez les lectures (meilleurs index, éviter les scans), ajoutez du cache ou améliorez la classe de stockage / les IOPS provisionnés.

Task 14: PostgreSQL — voir si les tris/hashes déversent sur disque

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000;"
Sort  (cost=980000.00..1030000.00 rows=5200000 width=64) (actual time=2100.551..2320.112 rows=100000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 512000kB
  Buffers: shared hit=12000 read=88000
Execution Time: 2355.991 ms

Ce que cela signifie : « external merge Disk » signifie qu’il y a eu débordement sur disque. C’est souvent la raison pour laquelle une requête est brutale sur un moteur : le réglage mémoire et le working set ne correspondent pas.

Décision : Corrigez en ajoutant un index correspondant à l’ordre ((created_at DESC)), en réduisant l’ensemble de résultats, ou en augmentant soigneusement work_mem pour cette charge.

Trois mini-récits industriels

1) Incident causé par une fausse hypothèse : « Un index est un index »

L’entreprise voulait migrer un service lecture-intensive de MySQL vers PostgreSQL pour simplifier l’exploitation. La couche SQL était « portable ». Le schéma était « le même ». Le plan de déploiement était d’écrire en double et de basculer les lectures.

En staging, tout semblait correct. En production, un seul endpoint a commencé à timeout-er. Même requête. Même clause WHERE. Réalité différente. PostgreSQL a choisi un sequential scan et un hash join qui semblaient raisonnables sur le papier. Les lignes réelles n’étaient nulle part proches de l’estimation.

La cause racine était banale : dans MySQL, l’équipe disposait d’un index composite qui correspondait à la forme de la requête. Dans PostgreSQL, la migration avait créé des index mono-colonne mais manquait le composite. L’index merge et le pattern nested-loop de MySQL masquaient l’erreur ; PostgreSQL demandait un index qui supporte à la fois la clé de jointure et le filtre temporel. Le planificateur Postgres sous-estimait aussi la sélectivité du prédicat temporel parce que la distribution avait un « cliff » (un job de rétention avait récemment backfillé une semaine de données).

La correction n’était pas un réglage héroïque. C’était construire le bon index composite en concurrent, exécuter ANALYZE et ajouter une garde : toute requête joignant et filtrant doit avoir une revue d’index. Ils ont aussi ajouté des statistiques étendues pour colonnes corrélées. Après cela, les plans se sont stabilisés et l’endpoint a cessé de jouer les vecteurs de déni de service.

2) Optimisation qui a rebondi : « Forçons le planner »

Une autre équipe avait une requête analytique lente dans PostgreSQL. Quelqu’un a découvert que désactiver les nested loops rendait la requête plus rapide sur son laptop. Ils ont mis SET enable_nestloop=off dans la configuration de session de l’application. C’était astucieux. C’était aussi un piège.

Une semaine, ça a semblé être une victoire. Puis le trafic a changé : le dataset d’un client était beaucoup plus petit que la moyenne, et les nested loops étaient en réalité le bon choix. Avec les nested loops désactivés, Postgres a choisi des hash joins et de gros tris, l’utilisation mémoire a grimpé. La base a commencé à déverser sur disque, la latence IO a augmenté et des requêtes non liées ont ralenti. La « correction » avait transformé la préférence de performance d’une requête en une politique globale pour ce pool de connexions.

Ils ont annulé le réglage et fait le travail ennuyeux : améliorer les stats, ajouter un index et réécrire la requête pour être plus sélective tôt. La leçon est restée : les hints de plan (ou pseudo-hints via paramètres de session) sont le dernier recours. Ils périment vite. Les données changent. Les charges changent. Votre astuce ne survivra pas au trimestre suivant.

3) Pratique ennuyeuse mais correcte qui a sauvé la mise : « Mesurer, puis changer une seule chose »

Un système de paiements utilisait MySQL et PostgreSQL pour différents services. Après un déploiement, la latence MySQL a augmenté pour un ensemble de requêtes normalement sous 50 ms. La réaction immédiate fut d’ajuster le buffer pool et de blâmer le réseau.

La personne d’astreinte a suivi un runbook : confirmer le blocage, confirmer la saturation IO, capturer les plans, comparer avant/après. Ils ont extrait le slow query log et trouvé la même requête faisant désormais « Using temporary; Using filesort ». Puis ils ont vérifié le diff du schéma : une migration avait supprimé un index composite et l’avait remplacé par deux index mono-colonne, croyant que « l’optimiseur peut les combiner ». Parfois il peut. Cette fois non.

Ils ont restauré l’index composite, confirmé via EXPLAIN que le plan était revenu, et observé la latence se rétablir. Pas de héros dramatique. Pas de roulette de paramètres. Juste des preuves, un changement et un plan de rollback. Ce n’est pas glamour, mais ce n’est pas non plus rater la paie parce que votre base trie.

Erreurs courantes : symptôme → cause → correction

• Symptôme : Postgres utilise Seq Scan, MySQL utilise un index et est rapide.
  Cause : Index composite manquant dans Postgres, ou prédicat non sargable à cause de casts/fonctions.
  Correction : Créer l’index composite correspondant à la jointure + filtre ; s’assurer que les prédicats comparent les mêmes types ; envisager un index d’expression si nécessaire.
• Symptôme : Postgres estime 5k lignes, lit en fait 500k ; plan catastrophique.
  Cause : Statistiques obsolètes/insuffisantes ; colonnes corrélées ; distribution biaisée.
  Correction : ANALYZE ; augmenter le stats target ; ajouter des statistiques étendues ; envisager le partitionnement par temps ou par tenant.
• Symptôme : « Index-only scan » mais toujours lent ; beaucoup de fetchs heap.
  Cause : Visibility map non mise à jour à cause d’un vacuum en retard ; mises à jour/suppressions fréquentes.
  Correction : Corriger autovacuum et la durée des transactions ; lancer un vacuum de maintenance ; réduire le churn ou repenser les mises à jour.
• Symptôme : Plan MySQL bascule aléatoirement entre bon et mauvais.
  Cause : Différences de recalcul/persistance des stats InnoDB ; sensibilité aux paramètres ; histogramme manquant.
  Correction : Activer les stats persistantes ; ajouter des histogrammes pour colonnes biaisées ; stabiliser avec les bons index composites.
• Symptôme : Requête lente seulement parfois ; sinon correcte.
  Cause : Rafales IO pendant checkpoints/flushs ; éviction de cache ; latence stockage due à un voisin bruyant.
  Correction : Lisser l’IO d’écriture (réglage checkpoints, taille redo), améliorer la classe de stockage, réduire le working set, ajouter du cache.
• Symptôme : Lectures bloquées ou expirant pendant un déploiement.
  Cause : Verrous DDL / metadata locks ; transactions longues retenant des verrous.
  Correction : Planifier les fenêtres DDL ; utiliser des techniques de schéma en ligne ; définir des timeouts ; corriger les transactions de l’appli.
• Symptôme : Postgres ralentit sur des semaines sans changement de code.
  Cause : Bloat table/index et dette de vacuum ; transactions longue durée empêchant le cleanup.
  Correction : Auditer autovacuum, gestion des transactions ; reindex périodique si besoin ; supprimer les « idle in transaction ».
• Symptôme : MySQL monte en CPU avec « Sending data », faible activité disque.
  Cause : Usage élevé de join buffer, ordre de jointure inefficace, index couvrant manquant, ou lookups ligne‑à‑ligne.
  Correction : Ajouter des index couvrants/composites ; réécrire la requête ; vérifier l’ordre de jointure avec EXPLAIN ANALYZE ; éviter les fonctions sur colonnes indexées.

Listes de contrôle / plan pas à pas

Quand une requête est rapide dans MySQL et lente dans PostgreSQL

1. Obtenez le plan avec l’exécution réelle : Postgres EXPLAIN (ANALYZE, BUFFERS). Identifiez le nœud dominant.
2. Comparez estimations vs réel : si l’écart > 10x, corrigez les stats avant toute autre chose.
3. Vérifiez les index et leur forme : assurez-vous que les index composites existent pour jointure + filtre. Postgres veut souvent le composite là où MySQL s’en sort avec index merge.
4. Confirmez la sargabilité des prédicats : pas de casts sur colonnes indexées, pas de WHERE date(created_at)=... sans index d’expression.
5. Investiguer vacuum/visibilité : tuples morts, fetchs heap, transactions longues.
6. Vérifier les déversements mémoire : tris/hashes déversant sur disque ; ajustez l’indexation ou la conception de la requête avant d’augmenter la mémoire.
7. Puis seulement régler les paramètres : work_mem, parallélisme, seuils autovacuum — sur la base de goulots mesurés.

Quand une requête est rapide dans PostgreSQL et lente dans MySQL

1. EXPLAIN dans MySQL : cherchez type=ALL, « Using temporary », « Using filesort ».
2. Confirmez les index composites : les bitmap scans de Postgres peuvent combiner des index efficacement ; MySQL a souvent besoin d’un index composite unique pour éviter les tables temporaires.
3. Vérifiez collations et types : comparaisons de chaînes et conversions implicites peuvent désactiver les index aussi dans MySQL.
4. Inspectez les stats InnoDB et les histogrammes : les colonnes biaisées sans histogrammes peuvent tromper l’optimiseur.
5. Validez l’état du buffer pool : si les lectures du buffer pool augmentent pendant la lenteur, c’est IO-bound, pas « SQL mauvais ».
6. Vérifiez les metadata locks : DDL ou transactions longues peuvent bloquer des requêtes apparemment innocentes.

Changer un plan en toute sécurité

1. Changez une seule chose à la fois (index, stats, réécriture de requête, ou config).
2. Mesurez avant/après avec les mêmes paramètres et des données représentatives.
3. Ayez un rollback : supprimer un index de façon concurrente (Postgres) ou supprimer l’index (MySQL) après avoir vérifié les alternatives.
4. Documentez la raison dans la migration. Le vous du futur mérite des preuves, pas des impressions.

FAQ

1) PostgreSQL est‑il plus lent que MySQL ?

Non. Les deux peuvent être extrêmement rapides. PostgreSQL gagne souvent sur les requêtes complexes et les options d’indexation riches ; MySQL brille souvent sur des schémas OLTP simples. Le facteur décisif est que le planificateur ait les bons index et les bonnes statistiques pour votre distribution de données.

2) Pourquoi PostgreSQL fait‑il un sequential scan alors qu’un index existe ?

Parce qu’il estime que le scan est moins cher. Raisons courantes : le prédicat n’est pas sélectif, l’index n’est pas aligné sur le filtre, les statistiques sont fausses, ou la requête applique une fonction/cast qui empêche l’utilisation de l’index.

3) Pourquoi MySQL choisit‑il un plan terrible même avec des index ?

Les statistiques InnoDB peuvent tromper sur des données biaisées sans histogrammes ; certaines formes de requêtes poussent MySQL vers des tables temporaires et des filesorts ; parfois le meilleur index est composite et non deux index mono-colonne.

4) Dois‑je utiliser des hints pour forcer des plans ?

Évitez‑le sauf si vous êtes acculé. Les plans forcés périment vite quand les données changent. Corrigez d’abord les index, les stats et la forme de la requête. Si vous devez forcer, isolez la manoeuvre, documentez‑la et surveillez‑la pour détecter quand elle devient obsolète.

5) PostgreSQL nécessite‑t‑il plus de maintenance que MySQL ?

Maintenance différente. PostgreSQL exige une discipline de vacuum et une attention aux transactions longues. MySQL demande de l’attention sur le comportement purge/undo d’InnoDB, le dimensionnement du buffer pool et les pratiques opérationnelles autour des DDL et metadata locks. Aucun n’est « installer et oublier » en production.

6) Pourquoi les statements préparés changent‑ils la performance dans PostgreSQL ?

Postgres peut utiliser un plan générique pour les statements préparés qui ne s’adapte pas aux valeurs de paramètres. Si une valeur de paramètre est fréquente et très sélective (ou non), un plan générique peut être systématiquement faux. Parfois utiliser des plans custom (ou éviter des préparations prématurées) compte.

7) Pourquoi la même requête se comporte différemment sur un replica ?

Le décalage de réplication, la chaleur du cache différente, la contention IO différente et l’overhead d’application/replay peuvent tous changer la latence. De plus, les réplicas ont souvent des réglages différents (intentionnellement ou accidentellement), surtout autour de la durabilité et de la mémoire.

8) Dois‑je partitionner les tables pour corriger des requêtes lentes ?

Le partitionnement aide si vous filtrez par la clé de partition et que le moteur peut élaguer les partitions efficacement. Il ajoute aussi de la complexité opérationnelle. Si votre problème est un index manquant ou des stats mauvaises, le partitionnement est une manière sophistiquée d’éviter de faire les bases.

9) Quelle base est la plus prévisible pour la performance des requêtes ?

La prévisibilité vient de la discipline : schémas stables, index corrects, statistiques rafraîchies, durées de transaction contrôlées et configuration cohérente. N’importe quel moteur peut être prévisible si vous l’exploitez sérieusement.

Conclusion : que faire la semaine prochaine

Si vous faites face à un choc de performance entre moteurs, faites trois choses avec intention :

1. Rendez les plans visibles : capturez EXPLAIN ANALYZE (et BUFFERS en Postgres) pour les cas lents, pas pour le chemin heureux.
2. Corrigez la forme, pas la superstition : ajoutez l’index composite qui correspond à la jointure + filtre ; retirez les casts implicites ; rafraîchissez les stats ; traitez la dette vacuum/transaction.
3. Exploitez la base comme un système : vérifiez la latence IO, la santé du cache, les blocages et la maintenance. L’optimiseur n’est pas votre ennemi ; c’est votre employé. Donnez‑lui des données précises et des outils appropriés.

Puis faites la chose ennuyeuse qui maintient les systèmes en vie : écrivez ce qui s’est passé, pourquoi c’est arrivé et quelles garde‑fous l’empêchent de se reproduire. La production ne récompense pas la mémoire. Elle récompense la répétabilité.