Optimisation des requêtes PostgreSQL : de lent à sub-milliseconde

Le mois dernier, j'ai reçu un message Slack à 2 h du matin : « Le dashboard ne répond plus. » Je me suis connecté en SSH sur le serveur de production, j'ai ouvert pg_stat_activity et j'ai trouvé une seule requête qui scannait 14 millions de lignes pour retourner 12 résultats. La solution ? Un index partiel qui a pris 30 secondes à créer. La requête est passée de 4,2 secondes à 0,3 milliseconde.

C'est ça le truc avec les performances PostgreSQL. Les problèmes ne sont presque jamais exotiques. Ce sont des index manquants, des statistiques obsolètes, des requêtes qui avaient du sens quand la table avait 10 000 lignes mais qui en a maintenant 10 millions. Les solutions sont généralement simples — une fois qu'on sait où chercher.

Cet article rassemble tout ce que j'ai appris sur l'optimisation des requêtes PostgreSQL en gérant des bases de données de production. Pas de théorie sans pratique. Chaque technique présentée ici m'a fait gagner un temps réel sur des systèmes réels.

L'état d'esprit du débugage : ne devine pas, mesure#

La plus grosse erreur que je vois chez les développeurs face aux requêtes lentes, c'est de deviner. « Peut-être qu'on a besoin d'un index sur cette colonne. » « Peut-être que le JOIN est lent. » « Peut-être qu'il faut ajouter de la RAM. »

Arrête de deviner. PostgreSQL possède l'un des meilleurs analyseurs de requêtes de tous les SGBD. Utilise-le.

EXPLAIN — Le plan de vol#

Un simple EXPLAIN te montre ce que PostgreSQL prévoit de faire, sans exécuter réellement la requête :

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

Cela te donne le plan — un index scan — et le coût estimé. Mais c'est une prédiction. PostgreSQL peut se tromper complètement sur le nombre de lignes. J'ai vu des estimations de « 1 ligne » quand le résultat réel était de 50 000 lignes. Ce genre de mauvaise estimation entraîne des choix de plan catastrophiques.

Utilise le simple EXPLAIN quand tu veux un coup d'œil rapide au plan sans exécuter la requête. C'est important quand tu analyses un DELETE qui modifierait des données, ou une requête qui met 30 minutes à tourner.

EXPLAIN ANALYZE — La vérité#

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

Maintenant tu vois le temps d'exécution réel et le nombre de lignes réel. C'est là que le vrai débugage commence. Compare rows=1 (estimé) avec rows=1 (réel) — ils correspondent, donc le planificateur a pris une bonne décision. Quand ils ne correspondent pas, c'est ton premier indice.

Attention : EXPLAIN ANALYZE exécute réellement la requête. Si tu analyses un UPDATE ou un DELETE, enveloppe-le dans une transaction :

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Le tableau complet#

Voici ce que j'utilise réellement 90 % du temps :

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

La sortie BUFFERS est cruciale. shared hit=312 signifie que 312 pages provenaient du cache en mémoire (RAM). read=45 signifie que 45 pages ont dû être lues depuis le disque. Si tu vois beaucoup de read par rapport à hit, ton shared_buffers est peut-être trop petit, ou la table est trop volumineuse pour rester en cache.

J'utilise aussi FORMAT JSON quand j'ai besoin de coller le plan dans explain.dalibo.com pour le visualiser. La vue en arbre rend les plans complexes beaucoup plus faciles à lire :

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Les options d'EXPLAIN que j'utilise vraiment#

Voici la requête de diagnostic complète que je lance en premier pour toute investigation de requête lente :

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

• TIMING — chronométrage par nœud (activé par défaut avec ANALYZE, mais je préfère être explicite)
• VERBOSE — affiche les listes de colonnes en sortie et les noms de tables qualifiés par le schéma

Lire les plans de requête comme un détective#

Chaque plan de requête est un arbre. PostgreSQL le lit de bas en haut : les nœuds les plus profonds s'exécutent en premier et transmettent leurs résultats vers le haut. Comprendre les types de scan est la base de tout le reste.

Seq Scan — Le scan complet de table#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Le scan séquentiel lit chaque ligne de la table. Pour une table d'un million de lignes, ça signifie chaque page sur le disque.

Mais voici la nuance : le Seq Scan n'est pas toujours mauvais. Si tu sélectionnes 30 % ou plus de la table, un scan séquentiel est en fait plus rapide qu'un index scan parce que les E/S séquentielles sont beaucoup plus rapides que les E/S aléatoires. PostgreSQL le sait. S'il choisit un Seq Scan alors que tu as un index, vérifie à quel point ta clause WHERE est réellement sélective.

Quand c'est un problème : quand tu sélectionnes une infime fraction des lignes d'une grande table et qu'il n'y a pas d'index.

Index Scan — La recherche ciblée#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

Il va dans l'index B-tree, trouve les entrées correspondantes, puis récupère les lignes réelles depuis le heap de la table. Chaque récupération de ligne est une opération d'E/S aléatoire vers le heap. C'est excellent pour les requêtes très sélectives, mais chaque accès au heap a un coût.

Index Only Scan — Le Graal#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

C'est le type de scan le plus rapide. PostgreSQL obtient tout ce dont il a besoin uniquement à partir de l'index, sans jamais toucher le heap de la table. Tu vois cela quand toutes les colonnes que tu SELECT et sur lesquelles tu fais un WHERE sont dans l'index.

Le piège : Heap Fetches: 0 signifie que la visibility map est à jour. Si ta table a beaucoup de tuples morts (pas de vacuum récent), PostgreSQL doit quand même vérifier le heap pour valider la visibilité des lignes. C'est une des raisons pour lesquelles l'autovacuum est important pour les performances, pas seulement pour l'espace disque.

Bitmap Scan — Le juste milieu#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Les bitmap scans sont la réponse de PostgreSQL au problème « trop de lignes pour un index scan, trop peu pour un seq scan ». Il construit un bitmap des pages contenant les lignes correspondantes, les trie par emplacement physique, puis les récupère dans l'ordre. Cela transforme les E/S aléatoires en E/S séquentielles.

Tu verras souvent des bitmap scans quand deux index ou plus sont combinés :

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

Ce BitmapAnd combine deux index séparés. C'est PostgreSQL qui dit : « Je n'ai pas d'index composite, mais je peux combiner ces deux index mono-colonne. » Ça fonctionne, mais un vrai index composite serait plus rapide.

Repérer les mauvaises estimations#

La chose numéro un que je cherche dans un plan de requête, ce sont les estimations erronées :

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Le planificateur avait estimé 1 ligne. Le résultat réel était 48 753 lignes. C'est une erreur d'estimation de cinq ordres de grandeur. Le planificateur a choisi un Nested Loop parce qu'il pensait joindre contre 1 ligne. Avec 48 753 lignes, un Hash Join aurait été des ordres de grandeur plus rapide.

Causes courantes de mauvaises estimations :

• Statistiques périmées : lance ANALYZE sur la table
• Colonnes corrélées : le planificateur suppose que les valeurs des colonnes sont indépendantes. Si status = 'shipped' et created_at > '2026-01-01' sont corrélées (la plupart des commandes récentes sont expédiées), le planificateur sous-estime la sélectivité combinée
• Fonctions personnalisées dans le WHERE : le planificateur utilise une estimation de sélectivité par défaut (généralement 0,5 % pour l'égalité, 33 % pour les intervalles) quand il ne peut pas analyser une fonction
• Requêtes paramétrées avec des plans génériques : après 5 exécutions, PostgreSQL peut basculer vers un plan générique qui ne prend pas en compte la valeur réelle du paramètre

Quand tu constates de mauvaises estimations, la solution est généralement l'une des suivantes : lancer ANALYZE, créer des statistiques étendues, réécrire la requête, ou utiliser un CTE comme barrière d'optimisation.

-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Stratégie d'indexation : le bon index pour le bon usage#

Les index ne sont pas gratuits. Chaque index ralentit les écritures, consomme de l'espace disque et nécessite une maintenance. L'objectif n'est pas « tout indexer » — c'est « indexer exactement ce dont tu as besoin ».

B-tree — Le cheval de bataille par défaut#

B-tree est le type par défaut et couvre la grande majorité des cas. Il supporte les opérateurs d'égalité et de plage (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

CREATE INDEX idx_orders_created_at ON orders (created_at);

L'ordre des colonnes d'un index B-tree est crucial pour les index composites. La colonne la plus à gauche est le tri principal, puis la suivante, et ainsi de suite. Un index sur (a, b, c) peut répondre efficacement à :

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b > 5
• WHERE a = 1 ORDER BY b

Il ne peut pas répondre efficacement à :

• WHERE b = 2 (saute la première colonne)
• WHERE c = 3 (saute les deux premières colonnes)
• WHERE a = 1 AND c = 3 (trou au milieu — la condition sur a est utilisée, la condition sur c nécessite un filtre)

Pense à un annuaire trié par nom de famille, puis par prénom. Tu peux rapidement trouver toutes les entrées « Dupont », ou « Dupont, Jean » précisément. Mais tu ne peux pas rapidement trouver toutes les entrées « Jean » sans parcourir tout l'annuaire.

La règle : mets les conditions d'égalité en premier, puis les conditions de plage, puis les colonnes de tri.

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

Index couvrants avec INCLUDE#

PostgreSQL 11+ te permet d'ajouter des colonnes non-clé à un index avec INCLUDE. Ces colonnes sont stockées dans les pages feuilles mais ne font pas partie de la structure B-tree. Elles permettent des index-only scans sans alourdir l'arbre :

-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Maintenant PostgreSQL peut satisfaire la requête entière uniquement à partir de l'index. Sans INCLUDE, il aurait dû aller chercher total et status depuis le heap. La différence sur un cache froid peut être spectaculaire — j'ai vu des requêtes passer de 50 ms à 0,2 ms juste en ajoutant des colonnes INCLUDE.

Index partiels — N'indexe que ce qui compte#

C'est ma fonctionnalité PostgreSQL préférée et celle que la plupart des développeurs ne connaissent pas.

-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Cet index est minuscule comparé à un index complet sur created_at. Il ne contient que les lignes où status = 'pending'. Les requêtes qui correspondent à la clause WHERE utilisent ce petit index rapide. Les requêtes qui ne correspondent pas l'ignorent.

Exemple réel en production : j'avais une table sessions avec 50 millions de lignes. Seulement environ 200 000 étaient actives (non expirées). Un index complet sur user_id pesait 1,2 Go. Un index partiel WHERE expires_at > now() pesait 8 Mo. La requête est passée de 12 ms à 0,1 ms parce que l'index entier tenait en cache.

-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

Ce dernier est incroyablement utile. Il te permet d'avoir une contrainte d'unicité sur l'email des utilisateurs actifs tout en autorisant le même email dans les enregistrements supprimés.

Index GIN — Recherche full-text et JSONB#

GIN (Generalized Inverted Index) est la réponse quand tu as besoin de chercher à l'intérieur de valeurs — tableaux, documents JSONB, ou vecteurs de recherche full-text.

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

Les index GIN sont volumineux et lents à construire, mais extrêmement rapides à interroger. Ils valent le coup pour les charges de travail lourdes en lecture avec des requêtes de containment complexes.

Pour le JSONB, il existe aussi jsonb_path_ops qui est plus petit et plus rapide pour les requêtes de containment @>, mais ne supporte pas les opérateurs d'existence (?, ?|, ?&) :

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

Index GiST — Types géométriques et de plages#

GiST (Generalized Search Tree) gère les types de données qui se chevauchent : formes géométriques, plages, recherche full-text (alternative au GIN, plus petit mais plus lent).

-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

Index Hash — Niche mais utile#

Les index hash ne sont utiles que pour les comparaisons d'égalité. Depuis PostgreSQL 10, ils sont journalisés par le WAL et résistants aux crashs. Ils sont plus petits que les B-tree pour les colonnes larges et légèrement plus rapides pour l'égalité pure :

-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

En pratique, j'utilise rarement les index hash. Les B-tree gèrent très bien l'égalité, et l'impossibilité de faire des requêtes de plage ou de tri rend les index hash trop inflexibles pour la plupart des cas réels.

Quand les index font mal#

Chaque index a un coût :

• Amplification des écritures : chaque INSERT met à jour tous les index de la table. Une table avec 8 index signifie 8 écritures supplémentaires par INSERT
• Mises à jour HOT bloquées : les Heap-Only Tuple (HOT) updates sont une optimisation majeure où PostgreSQL peut mettre à jour une ligne sans mettre à jour les index, mais seulement si aucune colonne indexée n'a changé. Plus d'index = plus de chances de bloquer les mises à jour HOT
• Surcharge du vacuum : plus d'index signifie un vacuum plus long
• Surcharge du planificateur : plus d'index signifie plus d'options à évaluer pour le planificateur

J'audite régulièrement les index sur les tables de production :

-- Find unused indexes
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- exclude primary key and unique constraints
  )
ORDER BY pg_relation_size(indexrelid) DESC;

J'ai trouvé des index de plusieurs gigaoctets qui avaient été créés pendant une migration ponctuelle et jamais utilisés depuis. Les supprimer a accéléré les écritures de manière notable.

-- Find duplicate indexes (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

Le problème des requêtes N+1#

Tous les développeurs utilisant un ORM finissent par y être confrontés. C'est le problème de performance le plus courant que je débugue.

À quoi ressemble le N+1#

# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # This triggers a separate query for EACH order
    print(f"Customer: {order.customer.name}")

Cela génère :

-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;

501 requêtes au lieu de 1 ou 2. Chaque requête est rapide individuellement, peut-être 0,5 ms. Mais 501 d'entre elles font 250 ms rien qu'en temps de base de données, plus la latence d'aller-retour réseau pour chacune.

Détecter le N+1 dans les logs#

Le moyen le plus rapide de repérer les requêtes N+1 est d'activer temporairement la journalisation des requêtes :

-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
 
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

Ensuite regarde les logs. Le N+1 est impossible à rater — tu verras des centaines de requêtes identiques avec des valeurs de paramètre différentes en succession rapide.

Une approche plus ciblée pour le développement :

-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Cela journalise la sortie complète d'EXPLAIN ANALYZE pour toute requête de plus de 100 ms, y compris les requêtes à l'intérieur de fonctions.

La solution : chargement anticipé ou JOINs#

Approche ORM — dis à l'ORM de charger les données liées d'emblée :

# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

Approche SQL brut — utilise simplement un JOIN :

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Une seule requête. C'est réglé.

JOINs vs requêtes multiples#

Le débat est légitime. Parfois deux requêtes valent mieux qu'un JOIN :

-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

L'approche JOIN duplique les données de commande pour chaque article. Si chaque commande a 20 articles et que la ligne de commande est large, c'est un transfert de données 20 fois plus important. L'approche à deux requêtes envoie chaque commande exactement une fois.

Ma règle : utilise les JOINs pour les relations one-to-one, envisage des requêtes séparées pour les one-to-many quand le côté « one » est large. Mais fais toujours un benchmark — l'aller-retour réseau d'une seconde requête coûte souvent plus que les données dupliquées.

Réécritures de requêtes courantes#

Certaines requêtes sont lentes non pas à cause d'un index manquant, mais à cause de leur écriture. Le planificateur de PostgreSQL est bon, mais ce n'est pas de la magie.

Sous-requête vs JOIN vs CTE#

Ces trois approches peuvent produire des plans très différents :

-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

En PostgreSQL 12+, les CTE sont généralement inlinés (le planificateur les traite comme des sous-requêtes), donc les performances sont identiques. Mais en PostgreSQL 11 et versions antérieures, les CTE sont des barrières d'optimisation — le planificateur les matérialise et ne peut pas pousser les prédicats à travers. Si tu es encore sur PG 11, évite les CTE pour les requêtes critiques en performance.

EXISTS vs IN vs JOIN#

C'est une question qui revient constamment :

-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

Pour les grandes tables, EXISTS l'emporte souvent parce qu'il court-circuite. La version IN doit construire la liste complète des order_ids retournés avant de filtrer. La version JOIN peut produire des doublons, nécessitant un DISTINCT qui ajoute une étape de tri ou de hashage.

Mon choix par défaut : utilise EXISTS quand tu vérifies l'existence de lignes liées. C'est le plus clair sémantiquement et généralement le plus rapide.

Mais il y a un contre-exemple. Si l'ensemble de résultats de la sous-requête est petit et que tu en as besoin pour plusieurs conditions :

-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Éviter le SELECT *#

Ce n'est pas qu'une préférence de style de code. Cela a de vraies implications sur les performances :

-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
 
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

Avec SELECT * :

• Impossible d'utiliser les index-only scans (toutes les colonnes devraient être dans l'index)
• Plus de données transférées sur le réseau
• Plus de mémoire utilisée pour le tri et le hashage
• Si quelqu'un ajoute une colonne BYTEA de 10 Mo plus tard, tes requêtes existantes deviennent silencieusement plus lentes

Fonctions de fenêtrage vs sous-requêtes#

Les fonctions de fenêtrage sont l'une des fonctionnalités les plus puissantes de PostgreSQL et surpassent presque toujours les sous-requêtes corrélées :

-- Slow: correlated subquery runs once per row
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions compute in a single pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

Un autre pattern courant — obtenir la ligne la plus récente par groupe :

-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

DISTINCT ON est mon choix par défaut pour ce pattern. C'est concis, lisible, et PostgreSQL l'optimise bien avec le bon index :

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

La pagination bien faite#

OFFSET est un piège pour les grands ensembles de données :

-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

La solution est la pagination par keyset (aussi appelée pagination par curseur) :

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

C'est toujours rapide parce qu'il utilise l'index pour sauter directement à la bonne position, quelle que soit la « page » où tu te trouves. Le compromis c'est que tu ne peux pas sauter à un numéro de page arbitraire, mais pour le défilement infini ou les interfaces « page suivante », la pagination par keyset est strictement supérieure.

Pour les ordres de tri complexes :

-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Cela utilise la comparaison row-value, que PostgreSQL gère efficacement avec un index composite sur (price, id).

Statistiques de table et vacuuming#

Le planificateur de requêtes de PostgreSQL prend ses décisions en se basant sur les statistiques de tes données. De mauvaises statistiques mènent à de mauvais plans. C'est aussi simple que ça.

ANALYZE : mettre à jour les statistiques#

-- Analyze a single table
ANALYZE orders;
 
-- Analyze the entire database
ANALYZE;
 
-- See current statistics for a column
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

La valeur de correlation est particulièrement intéressante. Elle va de -1 à 1 et mesure à quel point l'ordre physique des lignes correspond à l'ordre logique de la colonne. Une corrélation proche de 1 ou -1 signifie que les données sont physiquement triées sur cette colonne, rendant les scans de plage très efficaces (E/S séquentielles). Une corrélation proche de 0 signifie des E/S aléatoires pour les requêtes de plage.

-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

La cible de statistiques par défaut est 100, ce qui signifie que PostgreSQL échantillonne 300 * 100 = 30 000 lignes. Pour les colonnes avec beaucoup de valeurs distinctes ou des distributions biaisées, augmenter à 500 ou 1000 donne au planificateur de meilleures données au prix de temps d'ANALYZE légèrement plus longs.

Tuning de l'autovacuum#

L'autovacuum fait deux choses : récupérer l'espace des tuples morts (lignes supprimées ou mises à jour) et mettre à jour les statistiques. Sur les tables très sollicitées, les paramètres par défaut de l'autovacuum sont souvent trop conservateurs.

-- Check autovacuum status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Si tu vois des tables avec des millions de tuples morts et que le dernier vacuum date de plusieurs heures, ton autovacuum prend du retard.

Pour les tables à fort taux de modification (comme les sessions, les files d'attente de jobs ou les métriques), je définis des paramètres d'autovacuum par table :

-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% changes (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (default 2ms in newer PG)
);

Bloat de table#

Quand PostgreSQL met à jour une ligne, il ne la modifie pas sur place — il crée une nouvelle version et marque l'ancienne comme morte. Le vacuum récupère les lignes mortes, mais l'espace n'est réutilisé que par cette table. Le fichier de la table sur le disque ne rétrécit pas.

Avec le temps, une table fortement mise à jour peut avoir un bloat significatif — la table est beaucoup plus volumineuse sur le disque que ce que les données vivantes nécessitent. Cela signifie plus de pages à scanner, plus d'E/S, plus de pression sur le cache.

-- Estimate table bloat (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Pour les tables sévèrement bloatées, l'option nucléaire est VACUUM FULL — il réécrit la table entière. Mais cela prend un verrou exclusif, donc tu ne peux pas le faire sur une table de production en service sans temps d'arrêt. La meilleure approche est pg_repack, qui fait la même chose sans verrouillage :

pg_repack --table orders --no-kill-backend -d mydb

Connection pooling#

Voici quelque chose qui surprend beaucoup de développeurs : les connexions PostgreSQL sont coûteuses. Chaque connexion crée un nouveau processus (pas un thread), consomme environ 5 à 10 Mo de mémoire, et a un surcoût de fork non négligeable.

Le max_connections par défaut est 100. Si tu as un serveur applicatif avec 20 workers, chacun ouvrant 5 connexions, tu es déjà à ta limite. Ajoute un processeur de jobs en arrière-plan, un outil de monitoring et une migration qui tourne quelque part, et tu as un problème.

Pourquoi tu as besoin d'un connection pooler#

Sans pooler, si ton application doit gérer 500 requêtes concurrentes, tu as besoin de 500 connexions PostgreSQL. C'est 5 Go de mémoire rien que pour le surcoût de connexion, et les performances de PostgreSQL se dégradent significativement au-delà de quelques centaines de connexions à cause du surcoût de gestion des processus.

Avec PgBouncer devant PostgreSQL, ces 500 connexions applicatives sont mappées sur peut-être 20 connexions PostgreSQL réelles. Le pooler met les requêtes en file d'attente quand toutes les connexions à la base sont occupées.

Configuration de PgBouncer#

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

Mode transaction vs mode session#

• Mode transaction (pool_mode = transaction) : les connexions sont retournées au pool après chaque transaction. C'est ce que tu veux 95 % du temps. Réutilisation maximale des connexions.
• Mode session (pool_mode = session) : les connexions sont maintenues pendant toute la session client. Utilise ça si tu as besoin de prepared statements, de commandes SET, de LISTEN/NOTIFY, ou d'autres fonctionnalités au niveau session.
• Mode statement (pool_mode = statement) : les connexions sont retournées après chaque statement. Trop restrictif pour la plupart des applications — tu ne peux même pas utiliser de transactions explicites.

Le piège avec le mode transaction : tu ne peux pas utiliser les prepared statements (ce sont des états au niveau session), tu ne peux pas utiliser SET pour les variables de session, et LISTEN/NOTIFY ne fonctionnera pas comme prévu. La plupart des ORM ont un moyen de désactiver les prepared statements.

Pour Node.js avec le driver pg :

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,  // PgBouncer port, not PostgreSQL port
  database: 'mydb',
  max: 20,     // matches PgBouncer default_pool_size
  statement_timeout: 30000,
  // Disable prepared statements for PgBouncer transaction mode
  prepare: false,
});

Formule de dimensionnement du pool#

Il existe une formule courante pour le dimensionnement optimal du pool de connexions PostgreSQL :

optimal_connections = (core_count * 2) + effective_spindle_count

Pour un serveur moderne avec 4 coeurs et un SSD (1 spindle effectif) :

optimal = (4 * 2) + 1 = 9

Ça paraît contre-intuitivement bas. Mais PostgreSQL est limité par le CPU sur la plupart du matériel moderne (les SSD sont assez rapides pour que les E/S soient rarement un goulot d'étranglement). Plus de connexions que ça entraîne un surcoût de changement de contexte qui ralentit en fait les choses.

En pratique, je mets généralement default_pool_size à 2-3 fois ce nombre pour gérer les pics de trafic, en sachant qu'en charge maximale, certaines requêtes attendront dans la file de PgBouncer plutôt que de frapper PostgreSQL simultanément.

Checklist pratique : les étapes exactes pour chaque requête lente#

Voici mon processus réel quand je reçois un rapport « cette requête est lente ». Je suis ces étapes dans cet ordre, à chaque fois.

Étape 1 : obtenir la requête réelle#

Pas « l'endpoint est lent » — le SQL réel. Si tu utilises un ORM, active la journalisation des requêtes :

-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Ou consulte pg_stat_statements pour les requêtes principales par temps total :

-- Top 10 queries by total execution time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

pg_stat_statements est l'extension la plus précieuse pour les performances PostgreSQL. Si tu ne l'utilises pas, installe-la maintenant :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Étape 2 : lancer EXPLAIN (ANALYZE, BUFFERS)#

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;

Cherche :

1. Mauvaises estimations de lignes — le nombre de lignes réel très différent du nombre estimé
2. Seq Scans sur de grandes tables — index manquant potentiel
3. Nested Loops avec beaucoup de lignes — devrait être un Hash Join ou un Merge Join
4. Lectures de buffers élevées — cache froid ou table trop volumineuse
5. Opérations de tri qui débordent sur le disque — augmente work_mem ou ajoute un index pour le tri

Étape 3 : vérifier les statistiques de la table#

-- When were stats last updated?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Si last_analyze est ancien ou si n_dead_tup est élevé par rapport à n_live_tup, lance :

ANALYZE orders;

Puis relance EXPLAIN ANALYZE. Si le plan change, les statistiques périmées étaient le problème.

Étape 4 : vérifier les index existants#

-- What indexes exist on this table?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

Peut-être que l'index existe mais n'est pas utilisé. Peut-être que l'ordre des colonnes est mauvais pour ta requête.

Étape 5 : créer ou modifier les index#

En fonction du plan de requête, crée l'index approprié. Teste avec EXPLAIN ANALYZE avant et après.

-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;

Utilise toujours CONCURRENTLY sur les tables de production. Un CREATE INDEX classique prend un verrou complet sur la table qui bloque toutes les écritures.

Étape 6 : envisager des réécritures de requête#

Si l'index existe et que les statistiques sont fraîches mais que la requête est toujours lente, examine la requête elle-même :

• Une sous-requête peut-elle être réécrite en JOIN ?
• Est-ce que OFFSET pose problème ? Passe à la pagination par keyset
• Sélectionnes-tu plus de colonnes que nécessaire ?
• Une sous-requête corrélée peut-elle devenir une fonction de fenêtrage ?
• Un CTE empêche-t-il le planificateur d'optimiser ?

Étape 7 : vérifier les paramètres du serveur#

Pour des patterns de requêtes spécifiques, les paramètres du serveur comptent :

-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB';  -- try this and re-run EXPLAIN ANALYZE
 
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
 
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;

Si changer work_mem résout un problème de tri sur disque, envisage de l'augmenter globalement. Mais fais attention — c'est par opération, pas par connexion. Une requête complexe avec 10 opérations de tri et work_mem = 256MB pourrait utiliser 2,5 Go pour une seule requête.

Étape 8 : surveiller après la correction#

Ne te contente pas de corriger et d'oublier. Vérifie que la correction tient dans la durée :

-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
 
-- Check back in an hour/day
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

Bonus : les quick wins que j'applique à chaque nouvelle base de données#

Ce sont les paramètres et pratiques que j'applique à chaque base de données PostgreSQL que je mets en place, avant tout problème de performance :

-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restart

Et une requête de monitoring que je lance chaque semaine :

-- Tables that need attention
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Conclusion#

L'optimisation des requêtes PostgreSQL n'est pas un art obscur. C'est un processus systématique :

1. Mesure — ne devine pas. EXPLAIN (ANALYZE, BUFFERS) est ton meilleur allié.
2. Comprends le plan — apprends à lire les types de scan, les types de jointure et les estimations de lignes.
3. Indexe stratégiquement — le bon index sur les bonnes colonnes, dans le bon ordre. Les index partiels et les index couvrants sont des super-pouvoirs sous-utilisés.
4. Écris de meilleures requêtes — EXISTS plutôt qu'IN pour les vérifications d'existence, pagination par keyset plutôt qu'OFFSET, fonctions de fenêtrage plutôt que sous-requêtes corrélées.
5. Maintiens la base de données — tuning de l'autovacuum, mises à jour des statistiques, connection pooling.
6. Surveille en continu — pg_stat_statements te dit où ta base passe son temps. Consulte-le régulièrement.

La différence entre une requête qui prend 4 secondes et une qui prend 0,3 milliseconde est rarement le matériel. C'est presque toujours la connaissance — savoir où chercher et quoi changer. Et maintenant tu sais où chercher.