MySQL vs PostgreSQL: charges JSON — raccourci rapide ou douleur à long terme

La décision en une page : quoi choisir et quand

Utilisez PostgreSQL quand…

• Vous avez besoin de requêtes riches (contenance, existence, filtres imbriqués) et voulez que l’optimiseur ait des options. JSONB + GIN de PostgreSQL est l’outil mature.
• Vous voulez des contraintes autour de données semi-structurées : CHECK, index d’expression, colonnes générées et index fonctionnels sont des citoyens de première classe.
• Vous vous attendez à ce que le JSON reste plus d’un trimestre. PostgreSQL a tendance à mieux vieillir quand JSON devient « schéma central ».
• Vous savez gérer vacuum correctement. PostgreSQL vous récompensera, mais seulement si vous respectez l’entretien MVCC.

Utilisez MySQL quand…

• Votre usage de JSON est principalement stockage de document + récupération, pas des filtrages analytiques lourds. Si les requêtes sont « récupérer par id, retourner le blob », MySQL peut très bien faire l’affaire.
• Vous comptez sur les colonnes générées pour projeter des chemins JSON chauds en scalaires indexés. C’est la voie pratique de MySQL vers la prévisibilité.
• Vous êtes déjà standardisé sur MySQL en exploitation et JSON représente un petit coin de la charge. Des opérations cohérentes valent mieux que l’élégance théorique.

Ce que je dirais à une équipe de production

Si vos colonnes JSON sont un hack transitoire (ingérer vite, normaliser plus tard), choisissez la base que votre équipe sait déjà
exploiter. Mais si JSON est le contrat d’interface (événements, configurations, feature flags, attributs utilisateur) et que vous prévoyez d’interroger
son contenu à l’échelle, PostgreSQL est généralement le pari à long terme le plus sûr.

MySQL peut bien performer avec JSON, mais il exige souvent que vous « déclariez les éléments importants » via des colonnes générées et des index ciblés.
Sinon, vous expliquerez à la direction comment votre schéma flexible est devenu une latence inflexible.

Une citation qui devrait figurer dans chaque runbook d’astreinte : « L’espoir n’est pas une stratégie. » — maxime opérationnelle largement répétée (idée paraphrasée).
Avec JSON, espérer que la base de données « trouve une solution » est la façon la plus sûre de s’offrir un incident le week-end.

Faits et historique : comment en sommes-nous arrivés là

JSON en base de données semble moderne, mais l’industrie tourne autour de cette idée depuis des décennies : « stocker des données flexibles près des données structurées,
et les interroger sans renoncer à la sécurité transactionnelle. » Les détails diffèrent, et ce sont ces détails qui expliquent pourquoi vous lisez ceci au lieu de dormir.

8 faits à garder en tête

1. PostgreSQL a ajouté JSON en 9.2 (2012), puis a introduit JSONB en 9.4 (2014) pour un stockage binaire et une meilleure indexation.
2. MySQL a introduit un type JSON natif en 5.7 (2015) ; avant cela, c’était du TEXT avec une prière et une regex.
3. JSONB normalise l’ordre des clés et supprime les clés dupliquées (la dernière clé l’emporte). C’est excellent pour l’indexation, surprenant si vous voulez « stocker exactement ce que j’ai envoyé ».
4. MySQL stocke aussi JSON dans un format binaire et valide le JSON à l’insertion, évitant certains cauchemars de « blob invalide ».
5. Les index GIN de PostgreSQL ont été initialement conçus pour la recherche textuelle, puis sont devenus l’outil de travail pour la contenance JSONB.
6. Les colonnes générées de MySQL existent depuis 5.7, et elles expliquent pourquoi de nombreux déploiements MySQL JSON tiennent la route.
7. MVCC dans PostgreSQL signifie que les mises à jour créent de nouvelles versions de ligne ; les mises à jour volumineuses de JSON peuvent amplifier le bloat à moins que vacuum suive.
8. Les formats de réplication comptent : le binlog row-based de MySQL et le décodage logique de PostgreSQL se comportent différemment sous des mises à jour JSON fréquentes et des lignes chaudes.

Sémantique JSON : ce que les moteurs stockent réellement

MySQL : JSON est un type, mais traitez-le comme un document sauf si vous projetez des champs

Le type JSON de MySQL n’est pas un « TEXT avec une étiquette ». Il est validé, stocké dans une représentation binaire, et manipulé avec des fonctions JSON.
C’est la bonne nouvelle. La nouvelle opérationnelle est que vous n’obtiendrez rarement des performances soutenues sauf si vous faites l’une des deux choses suivantes :
(1) garder le JSON majoritairement écrit une fois/lu par clé primaire, ou (2) extraire les chemins souvent interrogés dans des colonnes générées et les indexer.

MySQL vous laissera volontiers écrire une requête qui semble sélective mais n’est pas indexable. L’optimiseur fera ce qu’il peut, puis il balayera.
Vous pouvez parfois le sauver avec des index fonctionnels (selon la version) ou des colonnes générées, mais il faut être intentionnel.

PostgreSQL : JSONB pour interroger ; JSON (texte) pour préserver l’entrée exacte

PostgreSQL vous propose deux philosophies différentes :
json stocke le texte original (y compris espaces et ordre), et
jsonb stocke un format binaire décomposé optimisé pour les opérateurs et l’indexation.
Si vous voulez des performances, vous voulez presque toujours JSONB.

Les opérateurs de PostgreSQL sont expressifs : contenance (@>), existence (?, ?|, ?&),
extraction par chemin (->, ->>, #>, #>>), et requêtes JSON path.
Cette expressivité peut être un piège : on écrit des filtres astucieux qui semblent peu coûteux et finissent CPU-bound sur la décompression ou coincés sur un index qui ne correspond pas au prédicat.

Blague 1/2 : JSON, c’est comme un tiroir à bric‑à‑brac — tout rentre jusqu’au moment où il faut trouver les ciseaux.

Indexer le JSON : où la performance se gagne ou se perd

Indexation MySQL : les colonnes générées sont le geste adulte

Dans MySQL, l’indexation d’expressions JSON arbitraires s’est améliorée au fil du temps, mais le schéma opérationnel le plus fiable reste :
définir des colonnes générées pour les quelques chemins JSON que vous interrogez tout le temps, les caster en types scalaires stables, et les indexer.
Cela fait trois choses :

• Donne à l’optimiseur un index B-tree normal qu’il comprend.
• Évite des extractions JSON répétées à l’exécution.
• Vous force à admettre quels champs font réellement partie du « vrai schéma ».

L’inconvénient : les changements de schéma deviennent plus lents et plus politiques, parce que le blob JSON a désormais des tentacules dans le DDL et les migrations.
Ce n’est pas un bug. C’est le prix à payer pour prétendre que des données semi-structurées ont une structure (parce qu’elles en ont une, une fois que vous en dépendez).

Quand l’indexation JSON MySQL échoue en pratique

• Prédicats trop dynamiques (chemins JSON différents selon l’entrée utilisateur) vous poussent vers des scans.
• Comparer des chaînes JSON à des nombres provoque des casts implicites et casse l’usage d’index.
• Utiliser des fonctions dans WHERE sans expression indexable fait hausser les épaules de l’optimiseur et exécuter le travail lentement.

Indexation PostgreSQL : GIN est puissant, mais il faut choisir la classe d’opérateur

L’histoire d’indexation JSONB de PostgreSQL est plus solide, mais ce n’est pas magique. Les index GIN peuvent accélérer la contenance et les requêtes d’existence,
mais ils ont différentes classes d’opérateurs :

• jsonb_ops : indexe plus de types d’opérations mais peut être plus volumineux.
• jsonb_path_ops : plus compact et plus rapide pour la contenance, mais prend en charge moins d’opérateurs.

Si votre charge est « trouver des lignes où le JSON contient ces paires », jsonb_path_ops est souvent le bon choix.
Si vous avez besoin d’existence flexible et d’un support d’opérateurs plus large, jsonb_ops.
Choisissez mal, et vous aurez un index qui existe uniquement pour rendre VACUUM triste.

Indexes d’expression : le pont pratique entre JSON et relationnel

Si vous filtrez fréquemment sur un champ extrait (par exemple, payload->>'customer_id'), un index d’expression peut battre un GIN large
en taille et en prévisibilité. Il est aussi plus simple de raisonner sur la sélectivité.

Blague 2/2 : un index GIN, c’est comme la caféine — incroyable quand il est ciblé, regret quand on en abuse.

Schémas de requêtes qui séparent « ok » de « en feu »

Schéma 1 : « Récupérer par id et renvoyer le JSON » (plutôt sûr)

MySQL et PostgreSQL gèrent bien cela. Le coût dominant est l’I/O et la taille de ligne, pas les fonctions JSON.
Là où les équipes se blessent, c’est la lente progression : le JSON grossit, la taille des lignes augmente, l’efficacité du cache baisse, et soudain « lectures simples » deviennent des lectures disque.

Schéma 2 : « Filtrer par clés JSON à haute cardinalité » (indexez ou mourrez)

Si vous filtrez par user_id, tenant_id, order_id à l’intérieur du JSON, vous filtrez en fait par une clé relationnelle.
Ne faites pas semblant que c’est flexible. Promouvez-la : colonne générée + index dans MySQL, index d’expression dans Postgres, ou mieux encore faites-en une vraie colonne.
Ce n’est pas de l’idéologie. C’est pour éviter des scans complets et des plans de requête instables.

Schéma 3 : « Analytique ad hoc sur JSON » (méfiez‑vous de la lente progression)

JSON attire pour l’analytique parce qu’il est auto-descriptif. Dans des bases OLTP en production, c’est un piège.
L’analytique ad hoc tend à :

• Utiliser des fonctions sur de nombreuses lignes, provoquant une consommation CPU.
• Forcer des scans séquentiels parce que les prédicats ne correspondent pas aux index.
• Sérialiser votre charge sur une grande table et un sous-système disque chaud.

Si le business veut de l’analytique, soit consacrez une réplique de reporting avec des garde‑fous plus stricts, soit streammez les événements ailleurs.
« Exécutez simplement sur prod » est une décision budgétaire déguisée en décision technique.

Schéma 4 : mises à jour partielles du JSON (lignes chaudes, journaux lourds)

Les deux bases peuvent mettre à jour des chemins à l’intérieur du JSON, mais les caractéristiques de performance diffèrent et l’impact opérationnel est similaire :
des mises à jour fréquentes sur de gros documents JSON signifient plus d’octets écrits, plus de churn d’index, plus de travail de réplication, et plus d’invalidation de cache.

La règle pratique : si un champ JSON est mis à jour fréquemment et lu fréquemment, il mérite une vraie colonne ou une table séparée.
JSON n’est pas un laissez‑passer gratuit à la normalisation ; c’est une facture différée.

Mises à jour, WAL/binlog et latence de réplication

MySQL : volume de binlog et réalités de la réplication basée sur les lignes

Dans MySQL, les grosses mises à jour JSON peuvent produire de grands événements binlog — surtout avec la réplication basée sur les lignes. Si vous mettez à jour de nombreuses lignes ou de gros
documents, vos réplicas en paieront le prix. La latence de réplication n’est rarement « un problème de réplica ». C’est un problème d’amplification d’écriture de l’application.

Surveillez aussi la taille des transactions et la fréquence des commits. Une charge qui met à jour le JSON par rafales peut créer des pics désagréables : pression fsync,
blocages de flush du binlog, et retard du thread SQL du réplica.

PostgreSQL : pression WAL + churn MVCC

PostgreSQL écrit du WAL pour les changements, et MVCC signifie que les mises à jour créent de nouvelles versions de lignes. Mettez à jour souvent un grand champ JSONB et vous obtiendrez :
plus de WAL, plus de tuples morts, plus de travail de vacuum, et potentiellement plus de bloat d’index.

La latence de réplication se manifeste par un backlog du WAL sender ou un délai de replay. L’important est de distinguer :
le réplica n’applique pas assez rapidement (CPU/I/O limités) vs
le primaire produit trop de WAL (amplification d’écriture).

Conseils opérationnels

• Mesurez les octets WAL/binlog par seconde pendant les pics. C’est la chose la plus proche d’une « vérité » sur l’amplification d’écriture.
• Partitionnez ou séparez les champs JSON chauds si les taux de mise à jour sont élevés.
• Sur PostgreSQL, ajustez autovacuum pour les tables avec de nombreuses mises à jour JSON, sinon la dette de vacuum se traduira par une dette de latence.

Réalité du stockage et des E/S : bloat, usure de pages et comportement du cache

Taille de ligne et cache : votre taxe invisible

Les colonnes JSON rendent les lignes plus volumineuses. Des lignes plus grandes signifient moins de lignes par page. Moins de lignes par page signifie plus de lectures de pages pour le même nombre de lignes logiques.
Cela se manifeste par :

• Une plus grande usure du buffer pool dans MySQL (InnoDB).
• Plus d’usure des shared_buffers dans PostgreSQL.
• Plus de pression sur le cache de pages du noyau.

La plupart des « régressions de performance mystérieuses » après l’ajout de JSON sont en fait « nous avons doublé la taille de ligne et personne n’a ajusté la mémoire ou les schémas d’accès. »

Bloat PostgreSQL : MVCC signifie que vous devez au collecteur vacuum

PostgreSQL n’écrase pas en place ; il crée de nouvelles versions de lignes. Si JSONB est volumineux et fréquemment mis à jour, les tuples morts s’accumulent et les index
churnent. Autovacuum peut gérer beaucoup de choses, mais il a besoin de seuils adaptés. Les réglages par défaut sont conçus pour être sûrs pour les débutants, pas optimaux pour votre désordre.

MySQL : index secondaires et pression undo/redo

InnoDB de MySQL a sa propre amplification d’écriture : redo logs, undo logs, doublewrite buffer, maintenance des index secondaires.
Les grosses mises à jour JSON augmentent les octets touchés et peuvent vous pousser vers des blocages de flush de logs. Vous le verrez comme des pics intermittents de latence,
des « commits soudainement lents », et des réplicas qui prennent du retard.

Tâches pratiques : 14 commandes que vous pouvez exécuter aujourd’hui

Ce sont le genre de commandes que j’exécute pendant un incident ou une revue de performance. Chaque tâche inclut :
la commande, ce que signifie la sortie, et la décision à prendre ensuite.
Les noms d’hôtes et chemins sont volontairement ennuyeux ; l’ennuyeux est reproductible.

Tâche 1 (MySQL) : confirmer l’utilisation de JSON et la pression de taille

cr0x@server:~$ mysql -e "SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE data_type='json' ORDER BY table_schema, table_name;"
+--------------+------------+-------------+-----------+
| table_schema | table_name | column_name | data_type |
+--------------+------------+-------------+-----------+
| app          | events     | payload     | json      |
| app          | users      | attrs       | json      |
+--------------+------------+-------------+-----------+

Signification : vous savez maintenant quelles tables sont candidates à des problèmes liés au JSON.
Décision : retenez les 1–3 tables principales par nombre de lignes et taux de mise à jour. Ce sont celles où les choix d’indexation et de schéma comptent.

Tâche 2 (MySQL) : vérifier la taille des tables et l’empreinte des index

cr0x@server:~$ mysql -e "SELECT table_name, table_rows, ROUND(data_length/1024/1024,1) AS data_mb, ROUND(index_length/1024/1024,1) AS index_mb FROM information_schema.tables WHERE table_schema='app' ORDER BY data_length DESC LIMIT 10;"
+------------+------------+---------+----------+
| table_name | table_rows | data_mb | index_mb |
+------------+------------+---------+----------+
| events     |    4821031 |  8120.4 |   2104.7 |
| users      |     820114 |  1190.8 |    412.2 |
+------------+------------+---------+----------+

Signification : les tables lourdes en JSON ont tendance à gonfler data_mb.
Décision : si data_mb croît plus vite que la croissance commerciale, vous devez plafonner la taille des payloads, compresser en amont, ou normaliser les champs chauds.

Tâche 3 (MySQL) : identifier les prédicats JSON lents dans le slow log

cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mysql-slow.log --limit 5
#  1.2s user time, 40ms system time, 27.31M rss, 190.55M vsz
# Query 1: 0.68 QPS, 0.31x concurrency, ID 0xA1B2C3D4 at byte 91234
# Time range: 2025-12-28T00:00:00 to 2025-12-28T01:00:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Exec time     62   180s    120ms     12s    540ms     3s   900ms   300ms
# Rows examine  90  1200M      10   2.5M   360k   1.1M   500k   200k
# Query: SELECT ... WHERE JSON_EXTRACT(payload,'$.customer.id') = ?

Signification : rows examined est votre « taxe de scan ». JSON_EXTRACT dans WHERE sans index est un coupable fréquent.
Décision : créez une colonne générée pour ce chemin (ou un index fonctionnel si approprié) et réécrivez la requête pour l’utiliser.

Tâche 4 (MySQL) : vérifier si une requête utilise un index

cr0x@server:~$ mysql -e "EXPLAIN SELECT id FROM app.events WHERE JSON_UNQUOTE(JSON_EXTRACT(payload,'$.customer.id'))='12345' LIMIT 10\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4821031
     filtered: 10.00
        Extra: Using where

Signification : type: ALL et pas de key signifie balayage complet de la table.
Décision : ne commencez pas par tuner les buffers. Corrigez le schéma/la requête : colonne générée + index, ou redesign.

Tâche 5 (MySQL) : ajouter une colonne générée pour un chemin JSON chaud

cr0x@server:~$ mysql -e "ALTER TABLE app.events ADD COLUMN customer_id VARCHAR(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.customer.id'))) STORED, ADD INDEX idx_events_customer_id (customer_id);"
Query OK, 0 rows affected (2 min 41 sec)
Records: 0  Duplicates: 0  Warnings: 0

Signification : une colonne générée STORED matérialise la valeur, l’index devient utilisable.
Décision : réécrivez les requêtes applicatives pour filtrer par customer_id au lieu de JSON_EXTRACT dans WHERE. Puis re‑vérifiez EXPLAIN.

Tâche 6 (MySQL) : valider que l’optimiseur utilise maintenant le nouvel index

cr0x@server:~$ mysql -e "EXPLAIN SELECT id FROM app.events WHERE customer_id='12345' LIMIT 10\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
         type: ref
possible_keys: idx_events_customer_id
          key: idx_events_customer_id
      key_len: 258
          ref: const
         rows: 120
        Extra: Using index

Signification : vous êtes passé de scanner des millions à toucher ~120 lignes.
Décision : déployez, puis surveillez la latence d’écriture : maintenir le nouvel index augmente le coût d’écriture.

Tâche 7 (MySQL) : vérifier la latence de réplication et la pression d’application

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G" | egrep "Seconds_Behind_Source|Replica_SQL_Running|Replica_IO_Running|Last_SQL_Error"
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 87
Last_SQL_Error:

Signification : un retard existe même si les threads tournent. En général, l’application ne peut pas appliquer aussi vite que les écritures arrivent.
Décision : mesurez le débit binlog et la taille des transactions ; réduisez le volume de mises à jour JSON ou changez le batching avant de blâmer le réplica.

Tâche 8 (PostgreSQL) : lister les colonnes JSON/JSONB et leurs tables

cr0x@server:~$ psql -d appdb -c "SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE data_type IN ('json','jsonb') ORDER BY 1,2,3;"
 table_schema | table_name | column_name | data_type
--------------+------------+-------------+-----------
 public       | events     | payload     | jsonb
 public       | users      | attrs       | jsonb
(2 rows)

Signification : périmètre. Comme MySQL : identifiez les quelques tables qui comptent le plus.
Décision : concentrez-vous d’abord sur les tables à taux de mise à jour élevé et requêtes orientées client.

Tâche 9 (PostgreSQL) : trouver les pires requêtes JSON par temps total

cr0x@server:~$ psql -d appdb -c "SELECT calls, total_exec_time::bigint AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, query FROM pg_stat_statements WHERE query ILIKE '%jsonb%' OR query ILIKE '%->%' OR query ILIKE '%@>%' ORDER BY total_exec_time DESC LIMIT 5;"
 calls | total_ms | mean_ms | rows |                   query
-------+----------+---------+------+-------------------------------------------
 18211 |   932144 |   51.20 |    0 | SELECT ... WHERE payload @> $1
  4102 |   512030 |  124.82 |    0 | SELECT ... WHERE (payload->>'customer')= $1
(2 rows)

Signification : vous avez des requêtes chaudes, pas des théories.
Décision : lancez EXPLAIN (ANALYZE, BUFFERS) sur les principaux coupables et construisez le bon index pour la forme du prédicat.

Tâche 10 (PostgreSQL) : inspecter un plan de requête JSONB avec buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM events WHERE payload @> '{\"customer\":{\"id\":\"12345\"}}'::jsonb LIMIT 10;"
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..8.44 rows=10 width=8) (actual time=0.088..0.146 rows=10 loops=1)
   Buffers: shared hit=42
   ->  Index Scan using idx_events_payload_gin on events  (cost=0.42..22134.77 rows=26235 width=8) (actual time=0.086..0.141 rows=10 loops=1)
         Index Cond: (payload @> '{"customer": {"id": "12345"}}'::jsonb)
         Buffers: shared hit=42
 Planning Time: 0.412 ms
 Execution Time: 0.182 ms
(7 rows)

Signification : index scan + majoritairement des hits en buffer = sain.
Décision : conservez cet index s’il supporte des chemins produits essentiels. S’il ne sert qu’à des requêtes ad hoc, ne payez pas le coût en écriture.

Tâche 11 (PostgreSQL) : créer un GIN ciblé (choisir la classe d’opérateur)

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_events_payload_pathops ON events USING gin (payload jsonb_path_ops);"
CREATE INDEX

Signification : la création concurrente évite de bloquer les écritures (au prix du temps et d’un travail supplémentaire).
Décision : utilisez jsonb_path_ops lorsque la contenance domine ; sinon considérez jsonb_ops ou des index d’expression.

Tâche 12 (PostgreSQL) : construire un index d’expression pour un seul chemin chaud

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_events_customer_id_expr ON events ((payload->'customer'->>'id'));"
CREATE INDEX

Signification : cela rend les prédicats d’égalité sur cette valeur extraite prévisibles et peu coûteux.
Décision : si ce chemin est stable et largement utilisé, envisagez de le promouvoir en vraie colonne pour réduire le traitement JSON.

Tâche 13 (PostgreSQL) : vérifier les signaux de bloat et l’efficacité d’autovacuum

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
 relname | n_live_tup | n_dead_tup |    last_autovacuum     |     last_vacuum
---------+------------+------------+------------------------+------------------------
 events  |    4809123 |     912044 | 2025-12-28 00:41:12+00 | 2025-12-22 03:11:02+00
 users   |     820104 |      12033 | 2025-12-28 00:38:01+00 | 2025-12-23 02:08:40+00
(2 rows)

Signification : les tuples morts sur events sont élevés ; autovacuum tourne, mais peut être sous-dimensionné pour ce churn de mises à jour.
Décision : ajustez les seuils autovacuum pour cette table, réduisez la fréquence des mises à jour sur de gros JSONB, ou séparez les champs mutables.

Tâche 14 (au niveau système) : identifier si vous êtes lié par I/O ou par CPU

cr0x@server:~$ iostat -x 1 5
Linux 6.1.0 (db01) 	12/29/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.11    0.00    6.34   18.90    0.00   52.65

Device            r/s     rkB/s   rrqm/s  %rrqm  r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         320.0  18240.0     0.0   0.00    4.20    57.00   410.0  24576.0    9.80    59.95   6.10   92.0

Signification : un %util élevé et un iowait significatif pointent vers une saturation du stockage. Les charges JSON gonflent souvent les E/S en raison de lignes plus grosses et du churn d’index.
Décision : corrigez d’abord les schémas de requête/index ; si la saturation persiste, augmentez les IOPS (disques plus performants) ou réduisez l’amplification d’écriture (changement de schéma/conception).

Playbook de diagnostic rapide

Quand les requêtes JSON deviennent lentes, les gens gaspillent des heures à discuter du « choix de la base » au lieu de trouver le véritable goulot d’étranglement.
Ce playbook est l’ordre dans lequel j’agirais lors d’un incident — parce qu’il converge rapidement.

Première étape : prouver s’il s’agit d’un scan, d’un miss d’index ou d’I/O brute

• MySQL : exécutez EXPLAIN sur la requête lente. Si type: ALL, arrêtez et corrigez le prédicat/index.
• PostgreSQL : exécutez EXPLAIN (ANALYZE, BUFFERS). Si vous voyez des scans séquentiels sur de grandes tables, il vous faut un index correspondant ou réécrire la requête.
• Système : vérifiez iostat -x. Si le stockage est saturé, les scans et le bloat seront vos principaux suspects.

Deuxième étape : quantifier l’amplification d’écriture et la pression de réplication

• MySQL : inspectez la latence de réplication et les patterns de croissance du binlog ; les grosses mises à jour JSON corrèlent souvent avec des pics de retard.
• PostgreSQL : vérifiez la génération de WAL et les tuples morts ; des mises à jour JSON lourdes peuvent transformer vacuum en crise de fond permanente.

Troisième étape : vérifier l’efficacité du cache et la dérive de taille des lignes

• Votre working set chaud est‑il toujours en mémoire, ou la croissance du JSON l’a‑t‑elle évincé ?
• Avez‑vous ajouté un large index GIN qui a doublé le coût des écritures ?
• Quelqu’un a‑t‑il commencé à faire des filtres ad hoc sur des clés JSON non indexées ?

Quatrième étape : corriger la plus petite chose qui change la courbe

• Promouvez les clés chaudes en vraies colonnes (meilleur) ou en colonnes générées/index d’expression (second meilleur).
• Ajoutez le bon index pour la forme du prédicat, puis validez avec EXPLAIN.
• Si les mises à jour posent problème, séparez les champs mutables du blob JSON.

Erreurs courantes : symptômes → cause racine → correction

Erreur 1 : « La requête semble sélective mais est lente »

Symptômes : la latence croit avec la taille de la table ; EXPLAIN montre un scan complet ; CPU en pic pendant les heures de pointe.

Cause racine : extraction JSON dans WHERE sans expression indexable (MySQL), ou inadéquation entre opérateur et index (PostgreSQL).

Correction : MySQL : colonne générée STORED + index B-tree ; PostgreSQL : index d’expression ou bonne classe d’opérateur GIN ; réécrire le prédicat pour correspondre à l’index.

Erreur 2 : « Nous avons ajouté un index GIN et les écritures sont devenues plus lentes »

Symptômes : latence insert/update augmente ; taux WAL/binlog en pic ; latence de réplication après création de l’index.

Cause racine : index GIN large sur un JSONB volumineux avec mises à jour fréquentes ; coût élevé de maintenance d’index.

Correction : remplacez par des index d’expression plus étroits ; utilisez jsonb_path_ops si seule la contenance importe ; séparez les champs mutables ; repensez si la requête doit être sur l’OLTP.

Erreur 3 : « Postgres ralentit dans le temps ; vacuum n’arrive pas à suivre »

Symptômes : la taille des tables et index augmente ; les requêtes ralentissent ; autovacuum tourne constamment ; tuples morts élevés.

Cause racine : mises à jour fréquentes de gros JSONB créant de nombreux tuples morts ; seuils autovacuum non adaptés au churn de la table.

Correction : ajustez autovacuum par table ; réduisez la fréquence/taille des mises à jour ; déplacez les données mutables dans une table séparée ; envisagez le partitionnement pour les tables d’événements.

Erreur 4 : « Latence de réplication MySQL après ajout de fonctionnalités JSON »

Symptômes : Seconds_Behind_Source augmente pendant les rafales ; les réplicas récupèrent lentement ; les commits sont en pics.

Cause racine : grands événements binlog basés sur les lignes causés par des mises à jour JSON ; transactions surdimensionnées ; trop d’index secondaires sur des projections JSON.

Correction : réduisez le volume de mises à jour JSON ; changez le batching ; limitez les projections indexées aux vraies chemins chauds ; vérifiez les réglages binlog/redo et les patterns de commit.

Erreur 5 : « Nous avons tout stocké en JSON et maintenant nous avons besoin de contraintes »

Symptômes : valeurs incohérentes dans le JSON ; validations côté appli qui divergent ; les requêtes doivent gérer des clés manquantes et des types erronés.

Cause racine : schéma externalisé dans le code applicatif ; pas de contraintes appliquées ; migrations évitées jusqu’à trop tard.

Correction : promouvez les clés critiques en colonnes ; ajoutez des CHECK (Postgres) ou imposez via des colonnes générées + NOT NULL (MySQL) ; introduisez des payloads versionnés.

Trois mini-histoires d’entreprise depuis les tranchées JSON

1) Incident causé par une mauvaise hypothèse : « JSON est pratiquement gratuit à requêter »

Une entreprise SaaS de taille moyenne a déployé un « flux d’activité » soutenu par une table d’événements. Chaque événement avait un payload JSON.
L’équipe produit voulait filtrer : « montrer seulement les événements où payload.actor.role = ‘admin’. » Facile, pensaient‑ils.
Le backend utilisait MySQL, et la première implémentation utilisait JSON_EXTRACT dans la clause WHERE.

En staging c’était correct. En production, ce fut un désastre en accéléré : la table events était grosse, et le filtre était populaire.
La requête paraissait sélective, mais elle faisait un scan complet, touchant des millions de lignes par requête pendant les pics.
CPU saturé, I/O saturé, et tout le cluster a développé le symptôme « tout est lent » qui fait que les exécutifs rejoignent le canal d’incident.

L’hypothèse erronée n’était pas « MySQL ne peut pas faire du JSON. » C’était : « si le prédicat est étroit, la base l’optimisera. »
Les bases optimisent ce que vous indexez, pas ce que vous espérez. L’extraction JSON sans expression indexable n’est pas étroite ; c’est un calcul coûteux répété sur de nombreuses lignes.

La correction fut douloureusement simple : ajouter une colonne générée STORED pour actor_role, l’indexer, et changer la requête.
Le postmortem a ajouté une règle : toute clé JSON utilisée dans un WHERE chaud doit être projetée et indexée, ou déplacée en vraie colonne.
Le schéma flexible est resté, mais seulement là où il n’était pas sur le chemin critique.

2) Optimisation qui a échoué : « Ajoutons juste un gros index GIN »

Une autre entreprise utilisait PostgreSQL et avait une unique énorme table events avec des payloads JSONB.
Ils voulaient accélérer des recherches ad hoc pour le support client, alors quelqu’un a ajouté un large index GIN sur l’ensemble du payload en utilisant la classe d’opérateur par défaut.
La vitesse des requêtes s’est améliorée immédiatement. Tout le monde s’est félicité et est passé à autre chose.

Deux semaines plus tard, la latence d’écriture a commencé à augmenter. L’activité d’autovacuum est devenue constante. Un délai de réplication est apparu pendant les pics.
L’index GIN était coûteux à maintenir parce que les payloads étaient volumineux et mis à jour fréquemment avec des champs d’enrichissement.
L’index a aussi rapidement grossi, augmentant la pression sur checkpoints et les E/S. Le gain pour « recherche support » est devenu un problème « chaque endpoint API est plus lent ».

Le retour de bâton n’était pas que GIN soit mauvais. C’était qu’ils avaient indexé tout, pour une charge de requêtes qui n’était pas réellement centrale.
L’index a transformé la base en moteur de recherche. PostgreSQL peut le faire, mais vous payez en amplification d’écriture et bloat.

La correction finale : supprimer l’index large, ajouter deux index d’expression pour la poignée de clés utilisées dans les filtres du support,
et déplacer la recherche de type full-text hors du chemin OLTP. Le support a toujours sa capacité de recherche, mais la production n’a plus à payer la taxe sur chaque écriture.

3) Pratique ennuyeuse mais correcte qui a sauvé la mise : « Faire du JSON un contrat, le versionner et le tester »

Une équipe fintech stockait des métadonnées de vérification client en JSONB dans PostgreSQL. Cela incluait des champs imbriqués, des clés optionnelles et des blocs spécifiques à des fournisseurs.
Ils savaient que ces données allaient évoluer, et savaient aussi qu’ils auraient besoin d’interroger quelques champs de façon fiable pour des rapports de conformité.
Ils ont donc fait quelque chose d’ennuyeux : ils ont ajouté une colonne entière schema_version et écrit des migrations explicites pour les changements de forme du payload.

Ils ont aussi promu quelques champs critiques en vraies colonnes : customer_id, verification_status, et vendor_name.
Tout le reste vivait en JSONB. En plus, ils ont appliqué des CHECK garantissant que la colonne status correspondait à un ensemble connu,
et des tests applicatifs validant la compatibilité du schéma JSON par version.

Des mois plus tard, un fournisseur a changé subtilement son format de payload (un champ est devenu plus profond).
Les équipes qui conservent du JSON brut sans contrat découvrent généralement cela quand les rapports cassent à 2h du matin.
Cette équipe l’a découvert en CI, parce qu’un test de validation de schéma a échoué et que l’outil de migration a forcé une transformation explicite.

La pratique « ennuyeuse mais correcte » n’était pas un index sophistiqué. C’était traiter JSON comme un contrat versionné, pas comme un tiroir à bric‑à‑brac sans limite.
La production en a bénéficié : la performance des requêtes est restée stable, et la fréquence des incidents est restée faible — le type de victoire qui n’obtient jamais d’email de célébration.

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

Si vous lancez une nouvelle fonctionnalité lourde en JSON

1. Notez les 5 principaux schémas de requêtes que vous attendez dans les six prochains mois (pas seulement la semaine de lancement).
2. Classifiez les champs : immuables vs mutables ; fréquemment filtrés vs rarement filtrés ; haute cardinalité vs faible cardinalité.
3. Promouvez les champs « fréquemment filtrés, haute cardinalité » en vraies colonnes (préféré) ou en colonnes générées/index d’expression.
4. Choisissez une stratégie d’indexation spécifique à la base :
   • MySQL : colonnes générées STORED + index B-tree ; évitez JSON_EXTRACT dans les WHERE chauds.
   • PostgreSQL : index d’expression pour les chemins chauds ; GIN pour contenance/existence ; sélectionnez la classe d’opérateur intentionnellement.
5. Définissez des budgets de taille de payload (limites souples et strictes). La croissance du JSON est silencieuse jusqu’à ce qu’elle ne le soit plus.
6. Planifiez l’évolution : ajoutez schema_version, documentez les transformations, et rendez les migrations routinières.

Si vous avez déjà déployé et que c’est lent

1. Trouvez les 3 principales requêtes par temps total (slow log / pg_stat_statements).
2. Exécutez EXPLAIN avec la réalité (MySQL EXPLAIN, Postgres EXPLAIN ANALYZE BUFFERS). Ne supposez pas.
3. Ajoutez le plus petit index qui correspond au prédicat (index de colonne générée ou index d’expression) et vérifiez les changements de plan.
4. Mesurez le coût côté écriture après indexation (latence de commit, taux WAL/binlog, latence de réplication).
5. Si les mises à jour sont lourdes, séparez les champs mutables du JSON dans une table distincte avec une clé appropriée.
6. Mettez des garde‑fous sur les requêtes ad hoc (timeouts, réplicas de lecture, ou un chemin de reporting dédié).

Si vous décidez entre MySQL et PostgreSQL pour JSON aujourd’hui

• Choisissez PostgreSQL si l’interrogation JSON est une fonctionnalité produit, pas un détail d’implémentation.
• Choisissez MySQL si JSON est surtout du stockage et que vous êtes prêt à projeter les clés chaudes dans des colonnes générées indexées.
• Choisissez la base que votre équipe peut exploiter en conditions d’incident. Une fonctionnalité théoriquement supérieure ne réveillera pas votre personne d’astreinte à 3h du matin.

FAQ

1) PostgreSQL est-il toujours meilleur pour JSON que MySQL ?

Non. PostgreSQL est généralement meilleur pour les requêtes complexes et l’indexation flexible. MySQL peut être excellent si vous gardez l’usage JSON simple
ou si vous projetez les chemins chauds en colonnes générées indexées. « Toujours » est la façon dont commencent les incidents.

2) Dois‑je stocker JSON en TEXT/VARCHAR à la place ?

Généralement non. Vous perdez la validation et de nombreux opérateurs JSON. Si vous ne requêtez vraiment jamais à l’intérieur du JSON et que vous ne faites que stocker/retirer,
TEXT peut fonctionner — mais vous prenez un risque sur l’hygiène des données. Les types JSON natifs sont plus sûrs pour la correction.

3) Quand une clé JSON doit‑elle devenir une vraie colonne ?

Si elle est utilisée dans des jointures, dans des WHERE chauds, pour le tri, ou nécessaire pour des contraintes, c’est une colonne. Si elle est mise à jour fréquemment,
c’est probablement une colonne ou une table séparée. JSON sert à la variabilité, pas à l’identité centrale.

4) Les index GIN résolvent‑ils les performances JSONB dans PostgreSQL ?

Ils résolvent certains problèmes. Ils peuvent aussi en créer d’autres (coût en écriture, bloat, maintenance).
Utilisez GIN lorsque vos prédicats s’alignent sur la contenance/l’existence et que les données indexées sont suffisamment stables pour justifier le coût en écriture.

5) Quelle est l’équivalence MySQL d’un index GIN Postgres sur JSONB ?

Il n’y a pas d’équivalent direct. Dans MySQL, vous créez typiquement des colonnes générées qui extraient des valeurs scalaires et les indexent.
C’est une philosophie différente : vous décidez ce qui compte à l’avance.

6) Comment éviter « des clés aléatoires partout » dans le JSON ?

Traitez le JSON comme un contrat : versionnez‑le, validez‑le, et documentez les formes autorisées.
Faites respecter les invariants critiques avec des contraintes en base (Postgres) ou des colonnes générées + NOT NULL/casts (MySQL).

7) Pourquoi les mises à jour partielles de JSON restent-elles coûteuses ?

Parce qu’une « mise à jour partielle » au niveau SQL peut toujours signifier une réécriture substantielle et du churn d’index au niveau stockage,
plus du volume WAL/binlog. Les gros documents mis à jour fréquemment sont coûteux, quelle que soit la beauté du SQL.

8) Puis‑je utiliser JSON pour des données multi‑locataires et juste filtrer sur tenant_id dans le JSON ?

Vous pouvez, mais vous ne devriez pas. L’isolation des tenants appartient à une vraie colonne indexée.
Le mettre dans JSON facilite les scans accidentels entre tenants et rend plus difficile l’application des contraintes et des limites de performance.

9) Quel est le modèle hybride le plus sûr ?

Stockez les champs centraux en colonnes (ids, status, timestamps, clés étrangères), stockez les champs optionnels/spécifiques fournisseur en JSON/JSONB,
et indexez seulement le petit sous‑ensemble de chemins JSON que vous interrogez réellement. Le reste reste flexible sans entraîner le coût des requêtes centrales.

Conclusion : prochaines étapes qui ne vous feront pas honte plus tard

Le JSON dans MySQL et PostgreSQL n’est plus une nouveauté. C’est un outil de production — et comme tous les outils de production, il récompense la discipline.
MySQL tend à vouloir que vous projetiez la structure depuis le JSON et l’indexiez explicitement. PostgreSQL vous offre plus d’expressivité pour les requêtes et l’indexation,
mais il vous facturera en WAL, bloat et maintenance si vous indexez trop largement ou mettez à jour de gros JSONB trop souvent.

Prochaines étapes pratiques :

1. Identifiez les 3 principales requêtes JSON par temps total et exécutez EXPLAIN avec des stats réelles.
2. Promouvez les 3 principales clés JSON utilisées pour filtrer/jointer en colonnes ou en colonnes générées/index d’expression et indexez‑les.
3. Mesurez l’amplification d’écriture (taux WAL/binlog) avant et après indexation ; surveillez la latence de réplication.
4. Mettez en place un budget de taille de payload et faites‑le respecter à l’ingestion.
5. Versionnez vos payloads JSON. Votre futur vous remerciera, sinon vous passerez un week‑end à décoder « pourquoi cette clé existe parfois. »

Choisissez la base qui correspond aux forces opérationnelles de votre équipe, puis concevez l’usage de JSON comme si vous vous attendiez à ce qu’il devienne permanent — parce que c’est souvent le cas.