1. Accueil
  2. Blog
  3. PostgreSQL avancé : JSONB, index partiels, EXPLAIN ANALYZE et partitionnement

PostgreSQL avancé : JSONB, index partiels, EXPLAIN ANALYZE et partitionnement

22 mars 2026 • DevOps / Infrastructure • ~10 min de lecture
postgresql jsonb indexation performance sql

Une table trades à 50 millions de lignes. La requête « quels trades actifs pour cet utilisateur ce mois-ci » prend 4 secondes. Après les quatre techniques de cet article : 12 ms.

Ce n'est pas de la magie — c'est de la compréhension fine de ce que PostgreSQL fait vraiment sous le capot. J'ai déjà couvert le diagnostic de requêtes lentes avec EXPLAIN ANALYZE en mode basique. Ici on va plus loin : les quatre leviers qui font la différence sur des tables de production à fort volume.

JSONB : stocker du schéma flexible sans perdre les perfs

Chaque exchange crypto expose une API différente. Binance renvoie un clientOrderId, Kraken un userref, Coinbase un client_order_id dans un sous-objet. Si vous créez une colonne dédiée pour chaque champ de chaque exchange, votre table finit à 40 colonnes dont 35 sont NULL pour chaque ligne.

JSONB résout ce problème — à condition de comprendre quand l'utiliser.

json vs jsonb — toujours utiliser jsonb

json stocke le texte brut tel quel. jsonb le décompose en une représentation binaire au moment de l'insertion. Conséquence :

  • jsonb est indexable (GIN, expression indexes)
  • jsonb permet les opérateurs de containment (@>, ?)
  • jsonb est légèrement plus lent à l'écriture, mais largement plus rapide en lecture

Règle simple : utilisez toujours jsonb.

Opérateurs essentiels

-- Accès à une clé (retourne jsonb)
SELECT exchange_meta -> 'exchange' FROM trades;

-- Accès à une clé (retourne text)
SELECT exchange_meta ->> 'exchange' FROM trades;

-- Containment : le JSON contient-il ce sous-objet ?
SELECT * FROM trades WHERE exchange_meta @> '{"exchange": "binance"}';

-- Existence de clé
SELECT * FROM trades WHERE exchange_meta ? 'client_order_id';

Ajouter la colonne et l'index GIN

-- Stocker les métadonnées spécifiques à l'exchange sans colonnes supplémentaires
ALTER TABLE trades ADD COLUMN exchange_meta JSONB;

-- Index GIN pour les requêtes de containment (opérateur @>)
CREATE INDEX idx_trades_exchange_meta ON trades USING GIN (exchange_meta);

-- Requête : tous les trades avec un order_id exchange précis
SELECT * FROM trades WHERE exchange_meta @> '{"order_id": "12345"}';

-- Requête : tous les trades Binance au statut filled
SELECT * FROM trades WHERE exchange_meta @> '{"exchange": "binance", "status": "filled"}';

L'index GIN sur exchange_meta couvre automatiquement toutes les clés de vos documents. Pas besoin de prévoir à l'avance quelles clés vous allez interroger.

Quand ne pas utiliser JSONB

JSONB n'est pas une excuse pour éviter la modélisation. Si vous filtrez régulièrement sur un champ — user_id, status, created_at — ce champ doit être une vraie colonne avec un vrai index B-tree. Mettre ces champs dans du JSON vous fait perdre toutes les optimisations du planner.

Règle : JSONB pour les données variables et rarement filtrées directement. Colonnes typées pour tout ce qui est filtré, trié ou jointé fréquemment.

Index partiels : indexer seulement ce qui compte

C'est la fonctionnalité PostgreSQL la plus sous-utilisée. Un index partiel ne couvre que les lignes qui satisfont une clause WHERE. Sa taille peut être 100x inférieure à un index classique — et il tient en RAM.

Le problème des index classiques

-- Index classique sur status : indexe les 50 MILLIONS de lignes
-- dont les 49,9 millions de trades clôturés que personne ne consulte
CREATE INDEX idx_trades_status ON trades(status);

-- Taille approximative
SELECT pg_size_pretty(pg_relation_size('idx_trades_status'));
-- → 1 GB

La solution : index partiel sur les lignes actives

-- Index partiel : seulement les ~100 000 trades actifs
CREATE INDEX idx_trades_active ON trades(user_id, created_at)
WHERE status = 'active';

-- Taille réelle
SELECT pg_size_pretty(pg_relation_size('idx_trades_active'));
-- → 3 MB  ← tient entièrement en mémoire partagée

-- Cette requête utilise l'index partiel directement
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
ORDER BY created_at DESC;

PostgreSQL sait qu'une requête avec WHERE status = 'active' peut utiliser cet index — la condition de l'index est un sous-ensemble de la condition de la requête. Le planner choisit l'index partiel automatiquement.

Autres cas d'usage classiques

-- Emails non vérifiés (minorité des utilisateurs)
CREATE INDEX idx_users_unverified ON users(created_at)
WHERE verified = false;

-- Jobs en attente dans une queue
CREATE INDEX idx_jobs_pending ON jobs(priority, created_at)
WHERE status = 'pending';

-- Commandes non soldées
CREATE INDEX idx_orders_open ON orders(user_id, amount)
WHERE closed_at IS NULL;

Le principe est toujours le même : identifier le sous-ensemble de lignes que vos requêtes chaudes ciblent, et n'indexer que celui-là.

EXPLAIN ANALYZE : lire entre les lignes

« Seq Scan mauvais, Index Scan bon » — c'est la lecture naïve. La réalité est plus nuancée. Voici comment lire un plan d'exécution correctement.

La commande complète

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE exécute vraiment la requête et mesure les temps réels. BUFFERS indique l'activité cache/disque. Ne jamais lancer sur une requête DELETE ou UPDATE sans transaction.

Déchiffrer le plan

Limit  (cost=0.56..18.42 rows=20 width=112) (actual time=0.124..0.198 rows=20 loops=1)
  ->  Index Only Scan Backward using idx_trades_active on trades
        (cost=0.56..2891.33 rows=3201 width=112)
        (actual time=0.121..0.183 rows=20 loops=1)
      Index Cond: (user_id = 123)
      Heap Fetches: 0
      Buffers: shared hit=5 read=0
Planning Time: 0.312 ms
Execution Time: 0.221 ms

Les nœuds importants :

  • Index Only Scan — le meilleur cas possible. PostgreSQL lit l'index sans accéder au heap (la table physique). Heap Fetches: 0 confirme qu'aucun accès au tas n'a eu lieu. C'est possible ici car toutes les colonnes nécessaires sont dans l'index.
  • rows=3201 estimé vs rows=20 réel — un écart modéré, acceptable. Un écart de 100x indique des statistiques obsolètes ; lancez ANALYZE trades;.
  • Buffers: shared hit=5 read=0 — tout vient du cache mémoire, aucun accès disque. Si read est élevé, vos données ne tiennent pas en RAM.

Pattern à surveiller : le nœud Sort coûteux

Sort  (cost=15234.12..15489.23 rows=102044 width=112)
      (actual time=1823.44..2104.12 rows=102044 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8432kB

external merge Disk signifie que le tri a débordé sur disque — work_mem est insuffisant, ou l'index devrait inclure la colonne de tri. Solution : inclure created_at dans l'index pour un tri déjà ordonné. 

-- Avant : index sans ordre
CREATE INDEX idx_trades_user ON trades(user_id) WHERE status = 'active';

-- Après : index composé avec ordre de tri natif
CREATE INDEX idx_trades_user_date ON trades(user_id, created_at DESC)
WHERE status = 'active';
-- → Le nœud Sort disparaît du plan

Partitionnement par date : la solution aux tables géantes

Au-delà de 10 millions de lignes sur une table append-only (trades, logs, events), le partitionnement par plage de dates devient nécessaire. L'idée : découper physiquement la table en sous-tables par période. Une requête « ce mois-ci » ne scanne qu'une partition au lieu de l'intégralité.

Créer la table partitionnée

-- Table parente — définit la structure et la clé de partition
CREATE TABLE trades (
    id          BIGSERIAL,
    user_id     BIGINT       NOT NULL,
    pair        TEXT         NOT NULL,
    amount      NUMERIC(20, 8) NOT NULL,
    status      TEXT         NOT NULL DEFAULT 'active',
    exchange_meta JSONB,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Partitions mensuelles
CREATE TABLE trades_2026_01 PARTITION OF trades
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE trades_2026_02 PARTITION OF trades
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE trades_2026_03 PARTITION OF trades
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Partition pruning en action

EXPLAIN SELECT * FROM trades
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-04-01'
  AND user_id = 123;
Append  (cost=0.29..45.23 rows=18 width=156)
  Partitions: trades_2026_03
  ->  Index Scan using trades_2026_03_user_id_idx on trades_2026_03
        (cost=0.29..45.23 rows=18 width=156)
      Index Cond: (user_id = 123)

Partitions: trades_2026_03 — PostgreSQL a éliminé toutes les autres partitions au moment de la planification. Il n'accède physiquement qu'à janvier à mars 2026.

Les bénéfices opérationnels

  • Archivage instantané : DROP TABLE trades_2024_01 supprime un mois de données sans DELETE massif ni bloquer la table.
  • VACUUM ciblé : l'autovacuum s'exécute partition par partition. Les anciennes partitions figées ne sont plus jamais retraitées.
  • Index locaux : les index créés sur la table parente sont automatiquement créés sur chaque partition — et chacun est plus petit, plus rapide à construire, et tient plus facilement en mémoire. 
-- Créer un index s'applique à toutes les partitions existantes et futures
CREATE INDEX ON trades(user_id, created_at DESC) WHERE status = 'active';

-- Archiver janvier 2024 sans verrouiller la table
DROP TABLE trades_2024_01;

-- Ou détacher puis archiver
ALTER TABLE trades DETACH PARTITION trades_2024_01;

Combiner les quatre

Voici l'état final de la table après application des quatre techniques :

-- 1. Table partitionnée par mois
CREATE TABLE trades (
    id            BIGSERIAL,
    user_id       BIGINT         NOT NULL,
    pair          TEXT           NOT NULL,
    amount        NUMERIC(20, 8) NOT NULL,
    status        TEXT           NOT NULL DEFAULT 'active',
    exchange_meta JSONB,                          -- 2. JSONB pour métadonnées variables
    created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Partitions mensuelles (à automatiser avec pg_partman en production)
CREATE TABLE trades_2026_03 PARTITION OF trades
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 3. Index partiel : seulement les trades actifs, composé avec l'ordre de tri
CREATE INDEX idx_trades_active ON trades(user_id, created_at DESC)
WHERE status = 'active';

-- 4. Index GIN pour les requêtes containment sur exchange_meta
CREATE INDEX idx_trades_exchange_meta ON trades USING GIN (exchange_meta);

La requête qui prenait 4 secondes :

-- Avant : Seq Scan sur 50M lignes, 4 secondes
SELECT * FROM trades
WHERE user_id = 123
  AND status = 'active'
  AND created_at >= date_trunc('month', NOW())
ORDER BY created_at DESC
LIMIT 20;
-- Après : partition pruning + index partiel composé
-- Execution Time: 12 ms

Index Only Scan Backward using idx_trades_active on trades_2026_03
  Index Cond: (user_id = 123)
  Filter: (created_at >= date_trunc('month', now()))
  Heap Fetches: 0
  Buffers: shared hit=4 read=0

Le gain vient de l'effet cumulatif : le partitionnement réduit le périmètre à une partition mensuelle, l'index partiel ne couvre que les trades actifs dans cette partition, et l'ordre de l'index élimine le tri. PostgreSQL ne touche que les 4 pages mémoire strictement nécessaires.

Conclusion

L'ordre logique d'optimisation pour une table à fort volume :

  1. Index partiels en premier : gain immédiat, zéro changement de schéma, impact spectaculaire sur les requêtes chaudes qui ciblent un sous-ensemble des données.
  2. JSONB pour les données variables : quand les réponses d'API externes ont des champs imprévisibles, JSONB avec un index GIN évite la prolifération de colonnes NULL. Ne remplace pas des colonnes typées pour les champs fréquemment filtrés.
  3. Partitionnement quand la table est vraiment grande : à partir de 10M de lignes sur des données append-only. L'investissement initial est plus important (migration, automatisation des partitions, pg_partman), mais les bénéfices opérationnels à long terme sont considérables.

Et à chaque étape, EXPLAIN (ANALYZE, BUFFERS) pour vérifier que le planner fait bien ce qu'on attend de lui. Les estimations de rows et le ratio hit/read ne mentent pas.

Articles similaires

Commentaires (0)