Le coût caché du JSONB dans une base PostgreSQL

Le premier article posait la règle : « stocker du JSON dans PostgreSQL » veut dire jsonb, on indexe l’expression plutôt que le document, et on contraint ce qui doit l’être. De quoi travailler.

Celui-ci est la facture, avec les chiffres.

Pourquoi un GIN par défaut pèse la moitié de sa table. Pourquoi le planificateur annonce 50 lignes quand il y en a 500, et ce que ça casse le jour où ce prédicat est le côté d’une jointure. Ce que coûte réellement la modification d’une clé dans un document de 20 Ko. Tout est mesuré sur un PostgreSQL 18.4 en conteneur, et le protocole est en tête d’article pour que vous puissiez refaire les mesures plutôt que de me croire.

De quoi décider en connaissance de cause plutôt qu’au jugé : quel index poser et lequel éviter, quand une clé mérite des statistiques, et à partir de quelle taille un document devient un problème d’écriture.

1. Le protocole

500 000 documents d’environ 300 octets.
On simule l’enregistrement d’événements applicatifs avec :

• un type,
• une date,
• une chaîne représentant une session,
• un objet user imbriqué,
• un objet props,

Ces documents sont stockés dans trois tables identiques sur PostgreSQL 18.4 (conteneur Docker, paramètres par défaut).

docker container run --rm -d --name pgjson -e POSTGRES_PASSWORD=postgres postgres:18.4
docker container exec -it pgjson psql -U postgres

CREATE TABLE e_json  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, doc json  NOT NULL);
CREATE TABLE e_jsonb (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, doc jsonb NOT NULL);
CREATE TABLE e_text  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, doc text  NOT NULL);

CREATE VIEW gen AS
SELECT json_build_object(
  'event_type', (ARRAY['page_view','add_to_cart','checkout','signup'])[1 + g % 4],
  'occurred_at', (TIMESTAMP '2026-01-01 00:00:00' + (g % 200000) * INTERVAL '1 minute'),
  'user', json_build_object(
      'id', g % 50000,
      'plan', (ARRAY['free','pro','enterprise'])[1 + g % 3],
      'country', (ARRAY['FR','DE','US','ES'])[1 + g % 4]),
  'session', md5(g::text),
  'props', json_build_object(
      'page', '/product/' || (g % 1000),
      'referrer', 'https://example.com/landing/' || (g % 50),
      'ab_test', (ARRAY['A','B'])[1 + g % 2],
      'amount', round((g % 997)::numeric / 7, 2))
) AS doc
FROM generate_series(1, 500000) g;

INSERT INTO e_json  (doc) SELECT doc        FROM gen;
INSERT INTO e_jsonb (doc) SELECT doc::jsonb FROM gen;
INSERT INTO e_text  (doc) SELECT doc::text  FROM gen;

Les comparaisons de temps sont faites sur cache chaud, parallélisme désactivé. Le reste du jeu de données, les quatre tables annexes qu’utilisent les sections suivantes, est en fin d’article.

Le résultat de base, celui qui justifie de ne plus jamais écrire json ni text :

jsonb écrit 38 % plus lentement, occupe 9 % de plus, et lit 9,5 fois plus vite. Tout le reste de cet article part de la table e_jsonb : 500 000 documents, 178 Mo.

Ces 9 % de surcoût ne sont pas la seule chose que le document paie en octets. Les noms de clés sont répétés à chaque ligne, un octet par caractère, et la compression TOAST n’intervient qu’au-delà de 2 Ko par valeur, donc jamais sur ce corpus. Deux tables de 500 000 lignes portant exactement les mêmes valeurs, l’une avec les clés event_type / session_identifier, l’autre avec e / s : 61 Mo contre 45 Mo.

2. Le coût des index

Trois stratégies d’indexation existent sur du jsonb. Elles ne couvrent pas les mêmes requêtes et ne coûtent pas du tout la même chose.

CREATE INDEX idx_gin_ops   ON e_jsonb USING gin (doc);                          -- jsonb_ops (défaut)
CREATE INDEX idx_gin_path  ON e_jsonb USING gin (doc jsonb_path_ops);           -- jsonb_path_ops
CREATE INDEX idx_btree_exp ON e_jsonb ((doc -> 'user' ->> 'plan'));             -- B-tree sur expression

L’écart de taille est le chiffre le plus parlant de toute cette série. Un GIN par défaut sur une colonne jsonb, c’est plus de la moitié de la taille de la table. Sur une table de 200 Go, cela se remarque.

Ce 56 % n’est pas une constante universelle, il dépend de la forme du document. Le corpus ci-dessus contient une clé session en md5(), soit 500 000 valeurs uniques que jsonb_ops indexe une par une. Un document à faible cardinalité fait s’effondrer le ratio ; un document qui porte plusieurs champs libres le fait grimper. L’ordre de grandeur, lui, est robuste : un GIN par défaut se compte en dizaines de pourcents de la table, pas en pourcents.

Pourquoi cet écart

jsonb_ops, l’opérateur par défaut, indexe chaque clé et chaque valeur séparément, comme des entrées distinctes. Le document {"user": {"plan": "pro"}} produit des entrées pour user, pour plan et pour pro. D’où la taille, et d’où le coût à l’exécution : chercher {"user":{"plan":"pro"}} implique de croiser plusieurs listes d’identifiants puis de revérifier chaque ligne candidate.

jsonb_path_ops indexe une seule entrée par chemin complet comme un hash de user.plan = "pro".
Moitié moins d’entrées, listes bien plus courtes, croisement inutile. C’est ce qui explique les 0,16 ms contre 1,60 ms.

Le B-tree sur expression, lui, n’indexe qu’une clé. Il ne sait rien du reste du document.
C’est précisément pourquoi il fait 3,4 Mo et pourquoi il est imbattable sur la requête qu’il couvre.

Le prix de jsonb_path_ops

Il ne sait pas répondre à la question « cette clé existe-t-elle ? ». Les opérateurs ?, ?| et ?& ne sont supportés que par jsonb_ops.

Sur 200 documents portant une clé refund_reason parmi 500 200 :

SELECT count(*) FROM e_jsonb WHERE doc ? 'refund_reason';

162 fois plus rapide, et le rapport grandit avec la table puisque l’un est linéaire et l’autre pas.
Si votre application teste la présence de clés, cas classique des attributs optionnels par client, jsonb_path_ops ne vous servira à rien.

Le GIN sur sous-arbre, l’option qu’on oublie

Un GIN n’est pas obligé de porter sur toute la colonne. Il peut porter sur une expression qui en extrait un sous-arbre :

CREATE INDEX idx_gin_props ON e_jsonb USING gin ((doc -> 'props') jsonb_path_ops);

C’est l’esprit du B-tree sur expression appliqué au GIN : on garde les clés ouvertes du sous-document props, on cesse d’indexer tout le reste.

Un ordre de grandeur gagné, et l’index sert toujours les requêtes exploratoires sur le sous-arbre, à condition d’écrire le prédicat sous la même forme :

SELECT count(*) FROM e_jsonb WHERE doc -> 'props' @> '{"page":"/product/42"}';
-- Bitmap Index Scan on idx_gin_props → 0,8 ms

C’est la bonne réponse quand les clés inconnues sont regroupées sous un nœud dédié, ce qui est presque toujours le cas dès qu’on a un peu conçu le document (props, custom_fields, metadata). Pris à l’envers, ça donne une règle de modélisation :

ranger les champs libres sous une clé plutôt qu’à la racine, c’est ce qui rend l’index abordable deux ans plus tard.

3. Le planificateur est aveugle là où ça compte

C’est de loin le problème le plus coûteux, et le moins visible.

PostgreSQL collecte des statistiques par colonne :

• fréquence des valeurs les plus communes,
• nombre de valeurs distinctes,
• histogramme.

Sur une colonne jsonb, il collecte des statistiques sur le document entier vu comme une valeur opaque, pas sur ses clés.

SELECT tablename, attname, n_distinct,
       array_length(most_common_vals, 1) AS n_mcv,
       array_length(histogram_bounds, 1) AS n_hist
FROM pg_stats WHERE tablename IN ('e_jsonb', 'e_norm');

Chaque valeur commune de e_norm est une information exploitable : « plan = 'pro' représente 33 % des lignes ». Les 101 bornes de e_jsonb sont des documents complets, dont aucun ne renseigne sur la distribution d’une clé particulière.

Conséquence directe sur les estimations.
Même prédicat, même sélectivité réelle, deux modélisations :

D’où sort ce 50

L’opérateur @> a pour estimateur matchingsel, comme les six autres opérateurs jsonb (<@, ?, ?|, ?&, @@, @?) — le catalogue le confirme :

SELECT oprname, oprrest FROM pg_operator
WHERE oprname = '@>' AND oprleft = 'jsonb'::regtype;
--  @>  | matchingsel

Sa sélectivité par défaut est de 1 % : DEFAULT_MATCHING_SEL vaut 0.010 dans src/include/utils/selfuncs.h, soit le double de DEFAULT_EQ_SEL, ce que la documentation formule par « twice the default estimate used in eqsel ». Sur 500 000 lignes, ça donnerait 5 000. Pas 50.

Ce qui se passe est plus compliqué.

ANALYZE construit bien un histogramme sur la colonne doc avec 101 bornes, des documents entiers, puisque jsonb est un type ordonné donc échantillonnable. Le planificateur évalue le prédicat @> contre ces 101 documents, puis extrapole. Son estimation dépend donc entièrement de la sélectivité réelle :

Quand le prédicat retient un tiers de la table, plusieurs des 101 documents échantillonnés correspondent, et l’estimation est bonne. Le planificateur n’est donc pas aveugle au sens strict.

Il l’est là où ça compte. Dès que le prédicat devient sélectif, aucun des 101 documents ne correspond, la sélectivité calculée tombe à zéro, et PostgreSQL applique alors un plancher codé en dur. matchingsel délègue à generic_restriction_selectivity, dans src/backend/utils/adt/selfuncs.c, qui se termine par :

/* In any case, don't believe extremely small or large estimates. */
if (selec < 0.0001)
    selec = 0.0001;
else if (selec > 0.9999)
    selec = 0.9999;

D’où :

0,0001 × 500 000 = 50

Ce même plancher s’applique aux deux prédicats sélectifs, ce qui explique qu’ils soient estimés à l’identique alors que l’un ramène 500 lignes et l’autre zéro.

Sur un count(*) isolé, aucune importance : le plan est le même. Le jour où ce prédicat est le côté d’une jointure, c’est autre chose.

Estimer 50 lignes quand il y en a 500 000 fait choisir une Nested Loop là où il fallait un Hash Join, et la requête qui prenait 200 ms en prend 40 secondes.

👉 C’est la forme la plus courante de « ça marchait bien en recette ».

Ce qui répare le problème

Deux outils, et le moins connu est le moins cher.

L’index B-tree sur expression rend deux services pour le prix d’un. Il accélère la lecture, et ANALYZE collecte au passage de vraies statistiques sur l’expression indexée : le prédicat doc -> 'user' ->> 'plan' = 'pro' passe d’un facteur 3 d’erreur à 1,5 %. C’est la raison la plus solide de le préférer à un GIN quand les clés sont connues.

Les statistiques étendues sur expression, depuis PostgreSQL 14, donnent la seconde moitié sans payer la première :

CREATE STATISTICS events_plan_stats ON (payload -> 'user' ->> 'plan') FROM events;
ANALYZE events;

Aucun index, aucune écriture ralentie, quelques kilo-octets de catalogue.

Cela reformule la règle de la section 2. Le B-tree sur expression reste le bon choix quand on veut aussi l’accès indexé. Mais quand le prédicat n’est qu’un côté de jointure et que le scan restera séquentiel de toute façon, les statistiques étendues suffisent, et on s’épargne un index à maintenir.

Elles vont même plus loin. Déclarées sur plusieurs expressions, elles capturent les dépendances entre clés, par exemple le fait que plan = 'enterprise' implique presque toujours country = 'US'. Aucun index n’apporte cette information.

CREATE STATISTICS events_multi (dependencies, ndistinct)
  ON (payload -> 'user' ->> 'plan'), (payload -> 'user' ->> 'country')
  FROM events;

Sur le corpus de test, le prédicat combiné plan = 'pro' AND country = 'FR' est alors estimé à 41 872 lignes pour 41 667 réelles, soit 0,5 % d’erreur sur deux clés imbriquées d’un document, sans un seul index.

4. TOAST : le coût caché des gros documents

Quand une ligne dépasse environ 2 Ko, PostgreSQL la fait rentrer dans une page en deux temps, et l’ordre des deux compte :

1. il compresse d’abord les plus gros attributs, sur place ;
2. si le tuple dépasse toujours le seuil, il sort les plus gros de la ligne et les range dans une table annexe dite TOAST.

Un document de 3 Ko qui se comprime à 1,5 Ko reste donc dans la table, compressé, sans TOAST. C’est la taille après compression qui décide.
Quand le déplacement a bien lieu, le document n’est plus dans la ligne : il est référencé par elle.

C’est efficace pour le stockage mais c’est brutal en écriture, car il n’existe pas de mise à jour partielle.
Modifier une clé oblige à décompresser le document, le reconstruire entier, le recompresser et réécrire toute la chaîne TOAST.

La mesure, sur 2 000 documents d’environ 21 Ko (≈ 15,5 Ko une fois compressés) :

UPDATE big SET status = 'done';                                -- colonne texte à côté
UPDATE big SET doc = jsonb_set(doc, '{0,v}', '"patched"');      -- une clé dans le document

115 fois plus lent, 73 fois plus de WAL pour modifier une chaîne de sept caractères. Et ce WAL se paie une deuxième fois sur les réplicas et une troisième dans les sauvegardes.

Ces deux ratios ne sont que des grandeurs de mes tests, ils ne se valent donc pas partout.

Par ailleurs, on peut améliorer la vitesse de compression en fonction de l’algorithme choisis qui dépend de default_toast_compression.
pglz par défaut, lz4 disponible depuis PostgreSQL 14 est plus rapide.

Ce qui est sûre c’est que : modifier une clé d’un document TOASTé réécrit le document entier.

5. L’amplification en écriture du GIN

Le 56 % de la section 2 a un pendant en écriture, et c’est lui qui tue les tables jsonb chaudes.

Un GIN n’a pas de notion de mise à jour partielle lui non plus. Modifier une clé du document réinsère toutes les entrées d’index de la ligne, pas seulement celle qui a changé : du point de vue de l’index, la ligne est une nouvelle version, dont il faut réindexer chaque clé et chaque valeur.

Sur 20 000 lignes mises à jour dans une table de 200 000 documents, une seule clé imbriquée modifiée :

12 fois plus lent et 5 fois plus de WAL pour le même UPDATE, du seul fait de l’index.

fastupdate, actif par défaut, change l’endroit où les nouvelles entrées atterrissent. Au lieu de les insérer une par une dans la structure GIN, PostgreSQL les accumule dans une liste d’attente non triée, puis les y déplace en bloc, avec la même technique d’insertion massive que la création d’index.

Ce que le mécanisme achète, et ce qu’il coûte :

• les écritures sont nettement plus rapides, et une partie du travail peut être faite par un processus d’arrière-plan plutôt qu’en avant-plan. C’est l’écart entre les deux dernières lignes du tableau, 188 ms contre 515 ;
• les lectures doivent parcourir la liste d’attente en plus de l’index, donc une liste longue ralentit les recherches ;
• l’écriture qui fait déborder la limite déclenche le transfert et le paie elle-même, ce qui la rend bien plus lente que les autres.

La documentation en tire la règle :

si la régularité des temps de réponse compte plus que la vitesse d’écriture, désactivez fastupdate.

C’est le même raisonnement que pour la taille, vu du côté des écritures :

indexez le sous-arbre ou l’expression, pas le document.

Un B-tree sur expression ne réindexe qu’une clé, et ne bouge même pas quand cette clé n’a pas changé.

En résumé

• Un GIN jsonb_ops par défaut, c’est 56 % de la taille de la table. jsonb_path_ops divise par deux, le même posé sur un sous-arbre divise par dix, un B-tree sur expression par trente.
• En écriture, le même GIN coûte 12 fois plus lent et 5 fois plus de WAL sur un UPDATE qui ne touche qu’une clé, parce qu’il réindexe le document entier. fastupdate en absorbe une partie (188 ms au lieu de 515) en différant l’insertion dans une liste d’attente, que les lectures doivent ensuite parcourir en plus de l’index.
• jsonb_path_ops ne sait pas répondre à ?. Si vous testez la présence de clés, il ne vous sert à rien.
• Le vrai coût de jsonb n’est ni les octets ni le CPU : le planificateur voit correctement les prédicats peu sélectifs et pas du tout les autres. Sous un certain seuil il applique un plancher de sélectivité à 0.0001, et se trompe d’un ordre de grandeur au moment précis où l’estimation sert à choisir un plan.
• CREATE STATISTICS sur expression répare ça sans index 👉 0,6 % d’erreur au lieu de 67×, pour quelques kilo-octets de catalogue. Déclarée sur deux expressions, elle capture même la corrélation entre deux clés.
• Un document TOASTé (> 2 Ko après compression) mis à jour souvent est un piège : 73 fois plus de WAL pour modifier sept caractères. Sous le seuil, le surcoût disparaît entièrement.
• Aucune de ces trois douleurs n’est un défaut du moteur : ce sont des problèmes de modélisation, et elles se corrigent dans le schéma et dans les index.

Le document ne coûte pas cher à écrire.

Il coûte cher à interroger le jour où le planificateur doit deviner ce qu’il contient.

Reproduire les mesures

Toutes les mesures viennent d’un PostgreSQL 18.4 en conteneur, paramètres par défaut, cache chaud, parallélisme désactivé pour les comparaisons de temps. Les tables de la section 1 suffisent pour la majorité ; voici les autres, pour que le protocole soit complet.

-- e_norm : la même donnée, en colonnes typées (section 3)
CREATE TABLE e_norm AS
SELECT (doc ->> 'event_type')      AS event_type,
       (doc -> 'user'  ->> 'plan') AS plan,
       (doc -> 'props' ->> 'page') AS page
FROM e_jsonb;

-- les 200 lignes portant une clé optionnelle, soit 500 200 au total (section 2)
INSERT INTO e_jsonb (doc)
SELECT jsonb_build_object('event_type', 'refund', 'refund_reason', 'damaged')
FROM generate_series(1, 200);

-- loose : le même document, sans aucune contrainte (article précédent)
CREATE TABLE loose (doc jsonb NOT NULL);

-- big : 2 000 documents d'environ 21 Ko, donc au-delà du seuil TOAST (section 4)
CREATE TABLE big (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text  NOT NULL DEFAULT 'pending',
  doc    jsonb NOT NULL
);

INSERT INTO big (doc)
SELECT (SELECT jsonb_agg(jsonb_build_object('k', i, 'v', md5(i::text)))
        FROM generate_series(1, 420) i)
FROM generate_series(1, 2000);

VACUUM ANALYZE e_jsonb;
VACUUM ANALYZE e_norm;
VACUUM ANALYZE big;

Ressources

• JSON Types
• JSON Functions and Operators
• GIN Indexes
• GIN Fast Update Technique
• TOAST
• Extended Statistics
• Operator Optimization Information (la doc de matchingsel)
• selfuncs.c (le plancher de sélectivité)
• Using PostgreSQL for JSON Storage
• Indexing Strategies for JSONB Fields in PostgreSQL: GIN vs BTREE
• Mastering gin_pending_list_limit: How This parameter shapes GIN index insert performance