Plans d’exécution MariaDB vs PostgreSQL : identifier le véritable goulot d’étranglement, pas les symptômes

Un modèle mental pratique : les plans sont des hypothèses

Traitez un plan d’exécution comme vous traitez une chronologie d’incident. C’est une preuve, pas un verdict. Les optimiseurs MariaDB et PostgreSQL choisissent des plans basés sur un modèle de coût. Les modèles de coût dépendent des statistiques, des hypothèses de configuration et des détails d’implémentation. Ils ont souvent raison. Parfois ils ont tort de façon hilarante, et ils échouent de façons prévisibles : changement de distribution des données, modification des paramètres, ou changement de la concurrence.

Le flux de travail qui marche réellement en production comporte trois couches :

1. Confirmer le symptôme : la durée, la fréquence, la concurrence, et ce que « lent » signifie (p95 ? p99 ? temps mur ? temps CPU ?).
2. Trouver le goulot de ressources : CPU, I/O, mémoire, verrous, réseau, ou quelque chose hors de la base de données.
3. Utiliser le plan pour expliquer le goulot : pas l’inverse.

Si vous commencez par le plan et cherchez une ligne à « corriger », vous traiterez souvent les symptômes. Vous déploierez aussi une « amélioration de performance » qui augmente le risque : bloat d’index, amplification d’écriture pire, ou une régression de plan différente le lundi.

Idée paraphrasée (attribuée) : Werner Vogels a depuis longtemps soutenu l’idée que « tout échoue, concevez pour cela ». La même attitude s’applique aux plans de requête : supposez qu’ils auront parfois tort, et construisez des garde-fous.

Faits et histoire intéressants (parce que ça explique les bizarreries d’aujourd’hui)

Certains mystères de performance s’expliquent mieux quand on sait d’où viennent les moteurs. Voici des éléments concrets qui importent quand vous lisez des plans sous pression :

• L’optimiseur de PostgreSQL est fortement piloté par les statistiques, et il choisira volontiers un plan « correct » qui est pourtant inadapté à vos valeurs de paramètres à moins d’obtenir de meilleures estimations de sélectivité.
• MariaDB a hérité du vocabulaire EXPLAIN de MySQL (« Using temporary », « Using filesort »), qui décrit des tactiques d’implémentation plus que des causes racines. C’est utile, mais ce n’est pas un diagnostic complet.
• InnoDB est devenu le moteur par défaut de MySQL il y a des années, et MariaDB a gardé cette filiation. Beaucoup de « tuning de requêtes » est en réalité du comportement du buffer pool InnoDB déguisé.
• Postgres a le MVCC au cœur ; le « bloat » et le comportement du vacuum peuvent modifier drastiquement les schémas d’I/O sans que le texte de la requête change.
• Postgres supporte plusieurs types d’index (B-tree, GIN, GiST, BRIN, hash). Cela signifie que « ajouter un index » n’est pas une décision unique ; c’est un choix de conception.
• MariaDB propose trace de l’optimiseur et flags de commutation qui peuvent être activés par session, ce qui est excellent pour expérimenter et terrible pour la cohérence si on en abuse.
• Postgres a introduit la requête parallèle progressivement ; si votre plan affiche soudainement des workers, vous mesurez peut‑être l’ordonnancement CPU et la pression mémoire, pas la « vitesse SQL ».
• MySQL/MariaDB se reposaient historiquement sur les jointures en boucle imbriquée comme pain quotidien, tandis que Postgres a une gamme plus large de stratégies de jointure et les choisira plus volontiers quand les stats le suggèrent.
• Postgres peut mettre en cache un plan générique pour les prepared statements, ce qui est fantastique jusqu’à ce que la sélectivité des paramètres varie énormément.

Blague #1 : L’optimiseur est comme un collègue confiant : il a toujours un plan, et il est toujours sûr d’avoir raison.

Playbook de diagnostic rapide (premiers/second/troisièmes contrôles)

Quand la production chauffe et que le tableau de bord du CEO devient froid, vous n’avez pas le temps pour une analyse philosophique des plans. Vous avez besoin d’une séquence courte qui trouve vite le vrai goulot et empêche des « correctifs » qui aggravent la situation.

Premier : déterminer si la requête attend ou travaille

• En attente : attentes de verrou, attentes I/O, attentes réseau, file d’exécution CPU, blocages de checkpoint.
• En travail : consommation CPU, lecture de nombreuses pages, tri, hash, matérialisation de résultats intermédiaires.

Second : vérifier la concurrence et la contention

• Combien de sessions exécutent la même requête ?
• Y a‑t‑il un « thundering herd » après un déploiement ou une expiration de cache ?
• Les écritures bloquent‑elles les lectures (ou inversement) à cause de la portée des transactions ?

Troisième : confirmer que le plan correspond à la réalité

• Postgres : comparez EXPLAIN (ANALYZE, BUFFERS) estimations de lignes vs lignes réelles et lectures de buffers.
• MariaDB : utilisez EXPLAIN FORMAT=JSON, vérifiez les index choisis et validez les compteurs handler de lecture.

Quatrième : décider de votre levier

• Réécriture de requête (réduire les lignes tôt, éviter les casts implicites, éviter les fonctions sur colonnes indexées).
• Changement d’index (ordre de clés correct, index partiel/fonctionnel dans Postgres, index composite vs simple).
• Stats et maintenance (ANALYZE, histogrammes dans MariaDB, VACUUM dans Postgres).
• Limiteurs opérationnels (pool de connexions, work_mem/tmp_table_size, plafond IOPS, voisin bruyant).

MariaDB vs PostgreSQL : ce qu’un plan vous dit (et ne vous dit pas)

Plans PostgreSQL : explicites, mesurables, et parfois trompeurs

Postgres vous donne un arbre d’exécution avec coûts, estimations de lignes, et (avec ANALYZE) timings réels et décomptes par nœud. C’est de l’or. Cela incite aussi les gens à traiter le plan comme une vérité déterministe. Ce n’est pas le cas.

Dans Postgres, la comparaison la plus précieuse est estimations de lignes vs lignes réelles. Quand elles divergent fortement, l’optimiseur prend des décisions avec de mauvaises entrées : stats obsolètes, corrélations qu’il ne peut pas modéliser, sélectivité dépendant des paramètres, ou skew des données. C’est là que le plan devient une piste vers le vrai goulot.

Un autre super‑pouvoir de Postgres est BUFFERS. Il vous dit si la requête touche des pages en cache ou va chercher sur le stockage. « Requête lente » avec beaucoup de shared read buffers, c’est une histoire d’I/O. « Requête lente » avec peu de lectures mais beaucoup de CPU, c’est une histoire de calcul (ou une attente de verrou déguisée en calcul).

Plans MariaDB : pragmatiques, avec les internes de l’optimiseur accessibles si vous le demandez

La sortie EXPLAIN traditionnelle de MariaDB est compacte : ordre des jointures, index choisi, et flags comme « Using where », « Using temporary », « Using filesort ». Ces flags ne sont pas des jugements moraux. Ce sont des indices d’implémentation. « Using filesort » ne signifie pas « il trie sur disque ». Cela signifie qu’il utilise l’algorithme filesort, qui peut ou non déborder.

Pour un travail sérieux, préférez EXPLAIN FORMAT=JSON et — lorsque vous devez savoir pourquoi un plan a été choisi — OPTIMIZER_TRACE. MariaDB peut vous montrer les alternatives considérées et les décisions de coût. C’est ainsi que vous attrapez les cas où le moteur a choisi un plan parce qu’il croyait qu’un filtre était sélectif alors qu’il ne l’était pas.

Ce que partagent les deux moteurs : les suspects habituels

Dans MariaDB comme dans Postgres, la plupart des « problèmes de plan » tombent dans une courte liste :

• Mauvaises estimations de cardinalité : skew, corrélation, stats obsolètes, sélectivité dépendant des paramètres.
• Mauvais chemin d’accès : scan d’index quand il faudrait un scan séquentiel (oui, vraiment), ou inversement.
• Mauvais choix de stratégie de jointure : boucle imbriquée sur une grande table, hash join qui déborde, merge join qui trie trop.
• Débordement de tri/hash : paramètres mémoire trop faibles ou concurrence trop élevée.
• Verrouillage : « requête lente » qui n’est pas lente — juste bloquée.
• Latence de stockage : les plans semblent corrects ; le disque ne l’est pas.

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

Voici les manœuvres éprouvées sur le terrain. Chaque tâche inclut une commande exécutable, un extrait de sortie d’exemple, ce que cela signifie, et quelle décision prendre ensuite. Utilisez‑les comme une boîte à outils, pas comme une religion.

Task 1 (Postgres): find the sessions and whether they’re waiting

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS age, left(query,120) AS q FROM pg_stat_activity WHERE datname='appdb' AND state<>'idle' ORDER BY age DESC LIMIT 10;"
 pid  | usename | state  | wait_event_type | wait_event |   age    | q
------+--------+--------+-----------------+------------+----------+----------------------------------------------------------
 8421 | app    | active | Lock            | relation   | 00:01:12 | UPDATE orders SET status='paid' WHERE id=$1
 9110 | app    | active | IO              | DataFileRead | 00:00:43 | SELECT ... FROM order_items WHERE order_id=$1

Signification : Une requête est bloquée sur un verrou de relation, une autre attend une lecture.

Décision : Si c’est Lock, arrêtez de tuner la requête et trouvez le bloquant. Si c’est IO, validez la latence de stockage et les lectures de buffers avant d’ajouter un index.

Task 2 (Postgres): identify blockers and victims

cr0x@server:~$ psql -d appdb -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked_locks JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;"
 blocked_pid | blocking_pid |         blocked_query         |         blocking_query
------------+--------------+-------------------------------+----------------------------------
       8421 |         8333 | UPDATE orders SET status=...  | ALTER TABLE orders ADD COLUMN...

Signification : Du DDL bloque du DML. Ce n’est pas un problème de plan de requête.

Décision : Stoppez le DDL ou déplacez‑le dans une fenêtre plus sûre. Puis revenez à la requête originale si elle est toujours lente.

Task 3 (Postgres): get a measured plan with buffer activity

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM order_items WHERE order_id=12345;"
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using order_items_order_id_idx on public.order_items  (cost=0.43..42.10 rows=12 width=128) (actual time=0.085..12.611 rows=9800 loops=1)
   Output: id, order_id, sku, qty, price
   Index Cond: (order_items.order_id = 12345)
   Buffers: shared hit=12 read=8102
 Planning Time: 0.214 ms
 Execution Time: 13.004 ms

Signification : L’index est utilisé, mais il lit des milliers de pages depuis le disque. Le prédicat n’est pas très sélectif ou la table est bloatée/non clusterisée.

Décision : Envisagez un index couvrant (inclure colonnes), réduisez les colonnes sélectionnées, ou traitez le bloat de la table. Vérifiez aussi la latence I/O.

Task 4 (Postgres): compare estimated vs actual rows to spot stats problems

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE) SELECT * FROM users WHERE email LIKE '%@example.com';"
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18250.00 rows=100 width=256) (actual time=0.040..210.221 rows=48210 loops=1)
   Filter: (email ~~ '%@example.com'::text)
   Rows Removed by Filter: 151790
 Planning Time: 0.251 ms
 Execution Time: 213.904 ms

Signification : L’estimation (100) est complètement fausse (48k). De plus, un wildcard en début de chaîne désactive un index B-tree normal.

Décision : Si cette requête compte, repensez le prédicat (stocker le domaine séparément) ou utilisez un index trigramme (extension Postgres). Ne blâmez pas le « seq scan » ici ; blâmez le prédicat.

Task 5 (Postgres): check whether temp files (spills) are happening

cr0x@server:~$ psql -d appdb -c "SELECT datname, temp_files, temp_bytes FROM pg_stat_database WHERE datname='appdb';"
 datname | temp_files | temp_bytes
---------+------------+------------
 appdb   |       1821 |  987654321

Signification : La base crée des fichiers temporaires pour des tris/hashes, ou produit de grands résultats intermédiaires. Souvent une histoire de work_mem et de concurrence.

Décision : Trouvez les requêtes qui effectuent de gros tris/hashes ; ajustez la mémoire par session avec prudence, ou réécrivez pour réduire les lignes plus tôt. Ne montez pas aveuglément work_mem globalement en priant.

Task 6 (Postgres): see the biggest time consumers (requires pg_stat_statements)

cr0x@server:~$ psql -d appdb -c "SELECT left(query,100) AS q, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                         q                         | calls | total_exec_time | mean_exec_time |  rows
---------------------------------------------------+-------+-----------------+----------------+-------
 SELECT ... FROM order_items WHERE order_id=$1      | 92000 |       812345.12 |          8.83  | 901234
 SELECT ... FROM users WHERE email LIKE $1          |  5000 |       401122.88 |         80.22  |  48210

Signification : Toutes les requêtes lentes ne sont pas importantes. Le temps total montre ce qui brûle votre budget.

Décision : Commencez par le temps total le plus élevé (et p95/p99 depuis votre APM). Optimisez là où cela a un impact, pas là où ça fait honte.

Task 7 (MariaDB): find active threads and what they’re waiting on

cr0x@server:~$ mariadb -e "SHOW FULL PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
231	app	10.0.2.41:51012	appdb	Query	65	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN note TEXT
244	app	10.0.2.77:52910	appdb	Query	42	Statistics	SELECT ... FROM order_items WHERE order_id=12345

Signification : Un verrou de métadonnées bloque les autres. Notez aussi les états « Statistics » ou autres qui peuvent indiquer un travail de l’optimiseur ou un comportement du moteur de stockage.

Décision : Résolvez d’abord le verrou de métadonnées. Ensuite mesurez l’exécution de la requête ; des ajustements de plan ne régleront pas des threads bloqués.

Task 8 (MariaDB): show the plan in JSON (more detail than tabular EXPLAIN)

cr0x@server:~$ mariadb -e "EXPLAIN FORMAT=JSON SELECT * FROM order_items WHERE order_id=12345\G"
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "order_items",
      "access_type": "ref",
      "possible_keys": ["order_id_idx"],
      "key": "order_id_idx",
      "key_length": "8",
      "rows": 12000,
      "filtered": 100,
      "attached_condition": "(`order_items`.`order_id` = 12345)"
    }
  }
}

Signification : MariaDB s’attend à ~12k lignes. Si les lignes réelles sont 9,8k, c’est correct. Si les lignes réelles sont 900k, les stats mentent ou le prédicat n’est pas ce que vous pensez.

Décision : Validez les décomptes de lignes avec une requête ciblée et rafraîchissez les stats si nécessaire.

Task 9 (MariaDB): enable optimizer trace to see why the plan was chosen

cr0x@server:~$ mariadb -e "SET optimizer_trace='enabled=on'; SELECT * FROM order_items WHERE order_id=12345; SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G"
TRACE
{
  "steps": [
    {
      "join_optimization": {
        "table_dependencies": [
          {"table": "order_items", "row_may_be_null": false}
        ],
        "rows_estimation": [
          {"table": "order_items", "range_analysis": {"chosen_range_access_summary": "ref on order_id_idx"}}
        ]
      }
    }
  ]
}

Signification : Vous pouvez voir les alternatives considérées. C’est ainsi que vous prouvez « l’optimiseur croyait X ».

Décision : Si le choix de l’optimiseur est guidé par une estimation erronée, corrigez les stats/distribution (histogrammes) ou réécrivez. Ne commencez pas par forcer l’index comme premier réflexe.

Task 10 (MariaDB): check handler reads to spot table scans and random I/O

cr0x@server:~$ mariadb -e "SHOW SESSION STATUS LIKE 'Handler_read%';"
Variable_name	Value
Handler_read_first	0
Handler_read_key	12045
Handler_read_next	980000
Handler_read_rnd_next	4500000

Signification : Beaucoup de Handler_read_rnd_next implique souvent des scans de table complets. Beaucoup de read_next peut indiquer des scans de plage parcourant de nombreuses lignes.

Décision : Si les scans sont larges, réduisez les lignes plus tôt (meilleurs prédicats, meilleur index), ou acceptez le scan s’il est moins coûteux que des I/O aléatoires sur un mauvais chemin d’index.

Task 11 (System): confirm whether the host is I/O-bound

cr0x@server:~$ iostat -x 1 3
Linux 6.1.0 (db01) 	12/30/2025 	_x86_64_	(8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.10    0.00    6.20   31.50    0.00   50.20

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         820.0   240.0 65440.0 18200.0  18.2   0.8   98.0

Signification : %iowait élevé, await élevé et %util proche de 100 % : le stockage est saturé. Votre plan peut être « correct » et pourtant lent.

Décision : Réduisez les lectures (indexes/indexs couvrants, moins de colonnes, meilleurs filtres), corrigez le bloat, ou ajoutez des IOPS. Si vous continuez à tuner le SQL alors que le disque est saturé, vous réarrangez des meubles pendant un exercice d’incendie.

Task 12 (System): check CPU run queue and saturation

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 8  0      0 512000  12000 980000    0    0   200   500 1200 2400 55 10 30  5  0
 9  0      0 508000  12000 978000    0    0   180   420 1180 2500 58 12 25  5  0

Signification : Une file d’exécution r proche ou supérieure au nombre de CPU et un us élevé signifient saturation CPU. Un wa faible indique que ce n’est pas principalement de l’attente I/O.

Décision : Cherchez des fonctions coûteuses, trop de workers parallèles (Postgres), des boucles chaudes, ou des index manquants provoquant un filtrage lourd en CPU. Vérifiez aussi la compression/crypto et les pics de connexions.

Task 13 (Postgres): check autovacuum/vacuum health and bloat signals

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
  relname    | n_dead_tup | n_live_tup | last_vacuum | last_autovacuum     | last_analyze        | last_autoanalyze
------------+------------+------------+-------------+----------------------+---------------------+------------------------
 order_items |    812345  |   9023410  |             | 2025-12-30 09:12:01  | 2025-12-30 08:05:10 | 2025-12-30 09:12:10

Signification : Beaucoup de tuples morts : risque de bloat, plus de pages à lire, comportement de cache dégradé, scans d’index plus lents.

Décision : Ajustez autovacuum pour les tables chaudes, envisagez VACUUM (ou une réécriture) si vous êtes à la traîne. Les plans de requête ne vous sauveront pas d’une table effectivement deux fois plus grande que nécessaire.

Task 14 (MariaDB/InnoDB): check buffer pool pressure and reads

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';"
Variable_name	Value
Innodb_buffer_pool_read_requests	987654321
Innodb_buffer_pool_reads	12345678
Variable_name	Value
Innodb_buffer_pool_pages_total	1048576
Innodb_buffer_pool_pages_free	1024

Signification : Très peu de pages libres et des lectures physiques non négligeables peuvent signifier que le working set ne tient pas. Les lectures toucheront le disque sous charge.

Décision : Réduisez le working set (supprimez les index inutiles, rapetissez les sélections), augmentez le buffer pool si c’est sûr, ou corrigez la requête pour qu’elle n’accède pas autant de pages.

Task 15 (Postgres): test whether prepared statement plan caching is hurting you

cr0x@server:~$ psql -d appdb -c "SHOW plan_cache_mode;"
 plan_cache_mode
-----------------
 auto

Signification : En auto, Postgres peut choisir un plan générique pour les prepared statements quand il estime que c’est globalement moins coûteux.

Décision : Si vous suspectez une sensibilité aux paramètres, testez avec SET plan_cache_mode = force_custom_plan; dans une session et comparez les performances. Si cela aide, corrigez au niveau de l’app/driver ou de la structure de la requête.

Trois mini-récits d’entreprise venant des tranchées

1) Incident causé par une mauvaise supposition : « Seq Scan est toujours mauvais »

Une entreprise de taille moyenne gérait une page d’analytics sur Postgres. L’on‑call a reçu une alerte : le p95 de l’endpoint est passé de « acceptable » à « problématique », et le CPU de la base a grimpé. Le développeur a posté un EXPLAIN montrant un scan séquentiel sur une table de taille modérée et a décrété : « On a besoin d’un index. »

Ils ont ajouté un index composite correspondant au WHERE et à l’ORDER BY. Les écritures sont devenues plus lentes immédiatement. La requête était plus rapide en staging au calme. En production, le p95 s’est à peine amélioré, et le CPU est resté élevé. Pendant ce temps, la construction de l’index a concurrencé les I/O et rendu l’incident plus laid.

Le vrai problème n’était pas le scan. C’était la concurrence : après un déploiement, l’endpoint s’est mis à tirer deux fois par vue de page à cause d’un régressif front-end. La requête faisait un scan séquentiel raisonnable parce que la table était assez petite, le prédicat peu sélectif, et les pages en cache rendaient le scan bon marché — jusqu’à ce que le cache soit moins chaud sous la charge doublée.

Corriger le bug front‑end a coupé le QPS de moitié. L’index « correctif » est devenu une dette technique : il a augmenté l’amplification d’écriture et le travail d’autovacuum, et il n’était pas nécessaire. Ils l’ont finalement supprimé une fois le postmortem apaisé et la fixation émotionnelle au seq scan dissipée.

2) Optimisation qui s’est retournée contre eux : forcer le comportement du plan

Une autre équipe utilisait MariaDB pour une API client. Une requête devenait parfois pathologique après de gros imports. Quelqu’un a utilisé FORCE INDEX pour contraindre l’optimiseur à utiliser ce qui semblait être le « bon » index dans EXPLAIN. Les benchmarks se sont améliorés. Tout le monde était rassuré.

Deux mois plus tard, une nouvelle fonctionnalité a ajouté un prédicat, et l’index forcé est devenu le mauvais index. La requête a maintenant effectué un énorme scan de plage, puis a filtré la plupart des lignes. L’optimiseur voulait passer à une autre clé, mais ne pouvait pas. La latence a monté en flèche, et l’API a commencé à expirer sous pic de trafic.

L’incident fut d’autant plus compliqué que le plan paraissait « stable ». La stabilité peut être un piège : vous avez figé une décision qui n’était correcte que pour l’ancienne distribution et l’ancienne forme de requête. En postmortem, l’équipe a retiré le hint d’index forcé et a plutôt corrigé les stats et la conception d’index. Ils ont aussi ajouté un test de régression sur le temps des requêtes avec des données représentatives, car « ça marche sur les données du mois dernier » n’est pas une stratégie.

3) Pratique ennuyeuse mais correcte qui a sauvé la mise : rollouts mesurés + baselines

Une grande plateforme interne avait des clusters Postgres et MariaDB. Brûlés par des régressions de plan auparavant, ils ont fait quelque chose d’agressivement peu glamour : pour chaque requête sensible à la performance, ils conservaient une baseline de (a) texte de requête normalisé, (b) décomptes de lignes attendus, (c) forme du plan, et (d) latence sur un snapshot de données fixe.

Lors d’une mise à jour de Postgres de routine, une requête est passée d’une boucle imbriquée indexée à un hash join. La latence a doublé en staging. Personne n’a paniqué, parce qu’ils avaient un diff : le nouveau plan débordait sur disque à cause d’un comportement mémoire changé sous un parallélisme plus élevé.

Ils ont ajusté work_mem pour ce rôle, limité les workers parallèles pour la session, et validé avec EXPLAIN (ANALYZE, BUFFERS) que les débordements avaient disparu. La mise à jour s’est déroulée sans drame. Le meilleur : personne n’a eu à argumenter sur des impressions. La régression de plan a été détectée avant que les utilisateurs ne la voient.

Blague #2 : Rien ne dit « entreprise » comme passer une semaine pour éviter de passer un week‑end.

Schémas de goulots : comment repérer la vraie limitation

1) Attentes de verrou déguisées en requêtes lentes

Si une requête est lente parce qu’elle est bloquée, votre plan est pour la plupart hors sujet. Dans Postgres, regardez wait_event_type. Dans MariaDB, vérifiez les états de processlist comme les verrous de métadonnées. Le piège classique est d’optimiser un UPDATE qui est « lent » parce qu’il attend derrière une longue transaction, un DDL, ou un job batch qui a oublié de commit.

La cause racine est souvent la portée des transactions. La correction n’est généralement pas un index. C’est des transactions plus petites, un comportement de verrou différent, ou planifier le DDL de manière sûre.

2) Exécution liée à l’I/O : le plan est correct, le stockage ne l’est pas

Vous pouvez avoir un scan d’index parfait qui lit pourtant des milliers de pages parce que les données sont éparpillées et hors cache. Les plans montrent ce qui devrait arriver ; BUFFERS et les métriques système montrent ce qui s’est passé.

Schémas I/O-bound :

• Le plan Postgres montre beaucoup de buffers shared read, temps CPU modestes.
• L’hôte montre iowait élevé, await élevé, util. appareil élevée.
• La latence s’aggrave avec la concurrence (mise en file d’attente sur le device).

Les corrections incluent des index couvrants, réduire les colonnes sélectionnées, partitionnement pour localité, vacuum pour bloat, ou admettre qu’il faut plus d’IOPS. Parfois la meilleure optimisation consiste à acheter le stockage que vous pensiez déjà avoir.

3) Exécution liée au CPU : prédicats coûteux et traitement ligne à ligne

Les requêtes liées au CPU impliquent souvent :

• Des fonctions sur colonnes dans le WHERE (surtout non immuables dans Postgres ; ou empêchant l’utilisation d’un index).
• Parsing JSON, regex, LIKE avec wildcards, comparaisons lourdes de collation.
• Explosion de jointures due à un mauvais ordre ou une cardinalité sous‑estimée.

Les plans vous aident à identifier où les lignes explosent. Mais ne manquez pas les consommateurs CPU non évidents : règles de collation, décompression, ou casts par ligne dus à des types non assortis.

4) Pression mémoire : tris/hashes qui débordent, puis tout devient « lent au hasard »

Dans Postgres, les débordements apparaissent comme des fichiers temporaires et dans les détails par nœud pour les tris/hashes dans ANALYZE. Dans MariaDB, « Using temporary » et « Using filesort » sont des indices, mais vous devez toujours confirmer s’il y a eu débordement. Les paramètres mémoire sont par thread/par requête ; la concurrence les multiplie. C’est là que le tuning naïf provoque des outages.

5) Mensonges de cardinalité : quand les estimations sont fictionnelles

Si les lignes estimées diffèrent des réelles par 10× ou 100×, considérez‑le comme un diagnostic primaire. Cela conduit à de mauvais choix de jointure, de mauvais chemins d’accès, et des performances imprévisibles.

Causes communes :

• Stats obsolètes (chargements massifs, churn, skew).
• Corrélation entre colonnes (l’optimiseur suppose l’indépendance).
• Sélectivité dépendant des paramètres (prepared statements, ORM).
• Prédicats non sargables (fonctions, casts).

Corriger les stats n’est pas glamour, mais c’est souvent la première bonne action car cela améliore chaque requête qui en dépend.

Erreurs courantes (symptôme → cause racine → correction)

« On voit Seq Scan, donc c’est forcément le problème »

Symptôme : Postgres affiche Seq Scan ; quelqu’un veut un index.

Cause racine : Le prédicat n’est pas sélectif, ou l’index causerait des I/O aléatoires pires que le scan, ou la requête retourne une large portion de la table.

Correction : Mesurez avec EXPLAIN (ANALYZE, BUFFERS). Si la plupart des pages sont touchées de toute façon, acceptez le seq scan ou redesign de la requête. Indexer n’est pas une vertu en soi.

« Using filesort signifie qu’il trie sur disque »

Symptôme : EXPLAIN MariaDB affiche Using filesort, la requête est lente.

Cause racine : Il utilise l’algorithme filesort ; cela peut être en mémoire, ou cela peut déborder parce que la mémoire est insuffisante ou que le jeu de résultats est volumineux.

Correction : Confirmez avec les status serveur et métriques tmp table, réduisez le jeu de résultats, ajoutez un index composite correspondant à ORDER BY + WHERE si approprié, et évitez de trier d’énormes résultats intermédiaires.

« On a ajouté l’index et c’était pire »

Symptôme : Les lectures se sont légèrement améliorées, les écritures et la réplication se sont détériorées.

Cause racine : L’amplification d’écriture et la maintenance supplémentaire (vacuum/autovacuum, splits de pages, redo/undo) ont nui plus que le gain en lecture ; possible régression de plan utilisant mal le nouvel index.

Correction : Validez avec des tests représentatifs de la charge. Préférez des index plus étroits, couvrants ; supprimez les index inutilisés ; envisagez des index partiels (Postgres) pour des sous‑ensembles chauds.

« Le plan n’a pas changé, mais c’est plus lent aujourd’hui »

Symptôme : Même forme de plan, latence plus élevée.

Cause racine : La latence de stockage a changé, le churn du cache a augmenté, le bloat a augmenté, la concurrence a augmenté, ou la pression de checkpoint.

Correction : Vérifiez iostat/vmstat, lectures de buffers vs hits, autovacuum et tuples morts, et la concurrence. Traitez‑le d’abord comme un problème système.

« C’est rapide en staging mais lent en prod »

Symptôme : Les benchmarks sont bons ; la production crie.

Cause racine : Distribution de données différente, skew manquant, chaleur de cache différente, concurrence différente, paramètres différents (work_mem/tmp_table_size), stockage différent.

Correction : Utilisez des snapshots de données représentatifs, exécutez avec concurrence, capturez des plans réels avec ANALYZE/JSON, et comparez lectures de buffers et usage de temporaires.

« On force l’optimiseur et maintenant c’est stable »

Symptôme : Hint d’index / switch d’optimiseur « corrige » les performances.

Cause racine : Vous avez figé un choix qui dépend de la distribution des données ; les changements futurs le rendront mauvais.

Correction : Corrigez les stats et la conception du schéma ; gardez les hints comme dernier recours avec surveillance et plan de retrait.

« Ajouter de la RAM va régler ça »

Symptôme : Latence élevée, quelqu’un blâme le cache.

Cause racine : Parfois vrai ; souvent ce sont de mauvaises requêtes faisant du travail inutile, ou de la contention de verrous, ou la saturation IOPS.

Correction : Confirmez avec les métriques de buffers et I/O wait. Ajoutez de la RAM seulement quand le working set est prouvé ne pas tenir et que le pattern d’accès en bénéficiera.

Listes de contrôle / plan étape par étape

Triage étape par étape (15–30 minutes)

1. Choisir la bonne cible : identifiez les requêtes au temps total le plus élevé ou au p95/p99 le plus haut (pg_stat_statements / slow log).
2. Vérifier attente vs travail : Postgres pg_stat_activity ; MariaDB SHOW FULL PROCESSLIST.
3. Vérifier la saturation système : iostat -x, vmstat.
4. Capturer un plan mesuré : Postgres EXPLAIN (ANALYZE, BUFFERS) ; MariaDB EXPLAIN FORMAT=JSON plus handler reads et trace si besoin.
5. Comparer les estimations : si les estimations sont fausses, corrigez d’abord les stats (ANALYZE, histogrammes).
6. Vérifier les débordements : fichiers temporaires Postgres ; tmp tables et comportement de tri MariaDB via les compteurs de status.
7. Décider du levier : réécriture de requête, conception d’index, stats/maintenance, ou infrastructure/concurrence.
8. Valider avec un plan de rollback : mesurez avant/après, confirmez qu’il n’y a pas de régression sur les écritures, la réplication et le stockage.

Checklist décisionnelle pour les index (ne pas deviner)

• Le prédicat réduit‑il significativement les lignes ? Sinon, l’index peut nuire plus qu’il n’aide.
• La requête a‑t‑elle besoin d’un order/limit ? Les index composites peuvent transformer des tris en scans d’index ordonnés.
• Sélectionnez‑vous beaucoup de colonnes ? Envisagez des index couvrants (Postgres INCLUDE ; MariaDB index secondaire inclut la PK en InnoDB, mais pas toutes les colonnes).
• La requête est‑elle sensible aux paramètres ? Évitez les suppositions basées sur une seule valeur « typique ».
• Quel est le coût en écriture ? Considérez le taux d’insert/update, pas seulement la latence de lecture.

Checklist stats et maintenance

• Postgres : autovacuum et analyze suivent‑ils les tables les plus chaudes ?
• Postgres : avez‑vous beaucoup de tuples morts et une dernière_autovacuum ancienne ?
• MariaDB : avez‑vous besoin d’histogrammes pour les colonnes skewées ?
• Les deux : la distribution des données a‑t‑elle changé (imports, backfills, croissance de locataires) ?

FAQ

1) Lequel est plus facile à lire : EXPLAIN MariaDB ou EXPLAIN Postgres ?

Postgres, parce qu’il vous donne le timing réel et les décomptes de lignes par nœud avec ANALYZE. MariaDB est aussi lisible, mais il faut souvent le format JSON et des compteurs supplémentaires pour confirmer la réalité.

2) Dois‑je toujours exécuter EXPLAIN ANALYZE en production ?

Non. Cela exécute la requête. Pour du debugging sûr en production, utilisez‑le sur une réplique ou un échantillon contrôlé. Quand vous devez l’exécuter, encadrez‑le par des limites et des prédicats stricts, et faites‑le en heures creuses.

3) Pourquoi Postgres choisit‑il un seq scan alors qu’il y a un index ?

Parce que scanner peut être moins coûteux que des I/O aléatoires quand une grande fraction de la table correspond, ou quand la table est petite, ou quand les pages en cache rendent le scan rapide. L’optimiseur a souvent raison.

4) Dans MariaDB, que signifie réellement « Using temporary » ?

Cela signifie qu’une table temporaire interne est utilisée pour des agrégations, tris, ou résultats intermédiaires. Elle peut être en mémoire ou sur disque selon la taille et les paramètres. Considérez‑le comme « il y a un résultat intermédiaire » et mesurez s’il y a débordement.

5) Comment détecter une régression de plan après un déploiement ?

Comparez les empreintes normalisées des requêtes et les plans mesurés avant/après. Dans Postgres, capturez EXPLAIN (ANALYZE, BUFFERS) et vérifiez la précision des estimations. Dans MariaDB, comparez le plan JSON plus les handler reads et le temps d’exécution sous charge représentative.

6) Quand « ajouter un index » est‑il la bonne réponse ?

Quand le prédicat est sélectif, la requête est suffisamment fréquente pour justifier le coût en écriture, et l’index supporte un mode d’accès qu’on ne peut obtenir autrement (filtrage, tri, clé de jointure). Si c’est une requête batch qui s’exécute une fois par jour, votre « correctif index » peut devenir une taxe pour toujours.

7) Pourquoi mon optimisation s’est‑elle retournée contre moi en concurrence ?

Parce que la mémoire par requête se multiplie entre les sessions, parce que le cache se chauffe différemment, et parce que la mise en file d’attente I/O est non linéaire. Un plan qui semble rapide en concurrence 1 peut s’effondrer à concurrence 200.

8) Quelle est la façon la plus rapide de savoir si c’est le stockage ?

Regardez les métriques hôte : iostat -x await/util, plus les preuves au niveau base : lectures de buffers Postgres et wait events ; lectures physiques InnoDB et pression du buffer pool. Si le device est saturé, le tuning de requête reste limité tant qu’on ne réduit pas les lectures.

9) MariaDB ou Postgres : quel optimiseur est « meilleur » ?

Aucun n’est magiquement meilleur ; les deux sont solides et ont des angles morts. Postgres est plus facile à valider car il expose plus de vérité runtime dans le plan. MariaDB vous donne une trace d’optimiseur qui peut être très révélatrice quand vous avez besoin du « pourquoi ».

Conclusion : prochaines étapes pratiques

Si vous ne retenez qu’une habitude opérationnelle : cessez de traiter les plans comme une liste de tâches. Commencez à les considérer comme une hypothèse à confirmer avec les attentes, les buffers et la saturation système. La façon la plus rapide de corriger une performance est de corriger la bonne chose.

Prochaines étapes que vous pouvez faire cette semaine :

1. Activez et utilisez la mise en baseline au niveau requête (pg_stat_statements pour Postgres ; slow log + digest pour MariaDB) afin d’optimiser ce qui vous coûte le plus.
2. Adoptez le playbook de diagnostic rapide : attente vs travail, puis contention, puis plans mesurés.
3. Ajoutez une politique « pas d’index non revu » : chaque index nécessite un gain mesuré et une vérification du coût en écriture.
4. Pour vos 5 meilleures requêtes, capturez un plan connu‑bon et un profil d’exécution mesuré (buffers/usage temporaires). Re‑vérifiez après modifications de schéma et mises à jour.

Votre futur vous, en astreinte à 02:00, vous en sera reconnaissant. Pas émotionnellement. Pragmatiquement.