13 - Performance : EXPLAIN ANALYZE, connection pooling et tuning

Ce que tu vas apprendre

• Lire et interpréter un plan EXPLAIN ANALYZE complet
• Connection pooling : pourquoi et comment (PgBouncer)
• pg_stat_statements pour trouver les requêtes lentes
• Les paramètres de configuration PostgreSQL a connaître
• VACUUM et autovacuum

Prerequisites

Avoir lu l'article 05 sur les index et l'article 06 sur les transactions.

EXPLAIN ANALYZE en détail

On a vu les bases dans l'article 05. Ici, on va plus loin.

sqlEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.id) AS nb_orders, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 10;

Limit  (cost=45.12..45.14 rows=10 width=48)
       (actual time=0.156..0.158 rows=2 loops=1)
  Buffers: shared hit=8
  -> Sort  (cost=45.12..45.62 rows=200 width=48)
           (actual time=0.155..0.156 rows=2 loops=1)
        Sort Key: (sum(o.total)) DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=8
        -> HashAggregate  (cost=35.50..38.00 rows=200 width=48)
                          (actual time=0.130..0.132 rows=2 loops=1)
              Group Key: c.name
              Buffers: shared hit=8
              -> Hash Join  (cost=14.00..32.50 rows=400 width=22)
                            (actual time=0.065..0.095 rows=4 loops=1)
                    Hash Cond: (o.customer_id = c.id)
                    Buffers: shared hit=8
                    -> Seq Scan on orders o  (cost=0.00..15.00 rows=400 width=18)
                                              (actual time=0.010..0.020 rows=5 loops=1)
                          Filter: (created_at > '2025-01-01')
                          Rows Removed by Filter: 0
                          Buffers: shared hit=4
                    -> Hash  (cost=10.00..10.00 rows=400 width=12)
                              (actual time=0.030..0.031 rows=4 loops=1)
                          Buffers: shared hit=4
                          -> Seq Scan on customers c  (cost=0.00..10.00 rows=400 width=12)
                                                       (actual time=0.005..0.008 rows=4 loops=1)
                                Buffers: shared hit=4
Planning Time: 0.250 ms
Execution Time: 0.200 ms

Les types de scan

• Seq Scan : lecture de toute la table. Normal pour les petites tables. Problématique sur des millions de lignes.
• Index Scan : utilise un index pour trouver les lignes, puis lit la table pour les colonnes non indexees.
• Index Only Scan : tout est dans l'index, pas besoin de lire la table. Le meilleur scénario.
• Bitmap Index Scan + Bitmap Heap Scan : utilise l'index pour construire une bitmap des pages a lire, puis lit ces pages. Bon compromis quand beaucoup de lignes matchent.

Les types de join

• Nested Loop : pour chaque ligne de la table externe, scan la table interne. Rapide si la table interne est petite et indexée. Desastreux si les deux sont grosses.
• Hash Join : construit une table de hash en mémoire sur la petite table, puis parcourt la grande. Bon pour les jointures d'egalite sur des tables de taille moyenne.
• Merge Join : trie les deux tables et fusionne. Bon si les donnees sont deja triees (par un index).

Quoi regarder

1. Rows estimated vs actual : si le planificateur estime 100 lignes et qu'il en trouve 100000, les statistiques sont perimees. Lance ANALYZE nom_table.
2. Seq Scan sur une grosse table : il manque un index. Ou le planificateur estime que l'index ne vaut pas le coup (trop de lignes a lire).
3. Sort Method: external merge Disk : le tri ne tient pas en mémoire. Augmente work_mem ou ajoute un index.
4. Buffers: shared hit vs read : hit = deja en cache. read = lecture disque. Un ratio hit eleve est bon.

EXPLAIN sans ANALYZE

EXPLAIN seul ne exécuté pas la requête, il estime juste le plan. Utile pour les requêtes qui modifient des donnees (UPDATE, DELETE) ou les requêtes tres longues. EXPLAIN ANALYZE exécuté vraiment la requête (mais dans une transaction annulee si tu ajoutes ROLLBACK).

pg_stat_statements : trouver les requêtes lentes

C'est l'extension la plus utile en production. Elle enregistre des statistiques sur chaque requête exécutée :

sqlCREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Les 10 requetes les plus couteuses en temps total
SELECT
  query,
  calls,
  total_exec_time::NUMERIC(10,2) AS total_ms,
  mean_exec_time::NUMERIC(10,2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

              query                | calls | total_ms | avg_ms | rows
-----------------------------------+-------+----------+--------+------
 SELECT * FROM orders WHERE ...    | 45000 | 12500.00 |   0.28 | 45000
 SELECT * FROM order_items WHERE.. | 45000 | 9800.00  |   0.22 | 180000
 UPDATE users SET last_seen = ...  |  8000 | 4500.00  |   0.56 | 8000

Les deux premières lignes sentent le N+1 (meme nombre d'appels, voir l'article 11). La troisieme pourrait beneficier d'un batch update.

Pour reset les stats : SELECT pg_stat_statements_reset();

Connection pooling

PostgreSQL fork un processus système par connexion. Chaque processus consomme environ 5-10 Mo de RAM. Avec 200 connexions, ca fait 1-2 Go rien que pour les processus. Et PostgreSQL ne gere pas bien au-delà de 200-300 connexions simultanees (le scheduler OS commence a souffrir).

Le problème : une application Node.js avec 10 instances sur Kubernetes, chacune avec un pool de 20 connexions = 200 connexions. Ajoute un second service et tu es a 400. C'est trop.

PgBouncer

PgBouncer est un proxy leger qui gere un pool de connexions PostgreSQL :

App instances (200 connexions) -> PgBouncer (20 connexions) -> PostgreSQL

Trois modes :

• Session pooling : une connexion PgBouncer = une connexion PostgreSQL pendant toute la session. Peu d'economie.
• Transaction pooling : la connexion PostgreSQL est libérée apres chaque transaction. C'est le mode qu'on utilise en production. Les SET, les PREPARE et les features session-specific ne fonctionnent plus.
• Statement pooling : libéré apres chaque requête. Pas de transaction multi-requêtes. Rarement utile.

Config PgBouncer basique (pgbouncer.ini) :

ini[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 400
default_pool_size = 20

Ton application se connecte a PgBouncer (port 6432) au lieu de PostgreSQL directement (port 5432). C'est transparent.

Prisma supporte aussi son propre connection pooler via Prisma Accelerate, mais PgBouncer reste la référencé quand tu heberges toi-meme.

Paramètres de configuration PostgreSQL

Les défauts de PostgreSQL sont conservateurs (il tourne sur un Raspberry Pi avec les config par défaut). Sur un serveur de production, il faut ajuster.

shared_buffers

Le cache en mémoire de PostgreSQL. Regle générale : 25% de la RAM.

# postgresql.conf
shared_buffers = 4GB  # sur un serveur avec 16GB de RAM

work_mem

Mémoire par opération de tri ou de hash (par requête, pas globale). Trop bas = tri sur disque (lent). Trop haut = risque d'OOM si beaucoup de requêtes en parallèle.

work_mem = 64MB  # bon pour la plupart des cas (defaut : 4MB, c'est trop peu)

effective_cache_size

Indice donne au planificateur sur la quantité de mémoire disponible pour le cache (OS + PostgreSQL). N'alloue rien, informe juste le planificateur. 75% de la RAM.

effective_cache_size = 12GB  # sur un serveur avec 16GB de RAM

maintenance_work_mem

Mémoire pour les opérations de maintenance (VACUUM, CREATE INDEX). Plus c'est haut, plus ces opérations sont rapides.

maintenance_work_mem = 1GB

Le site PGTune généré une config adaptee a ton serveur. Tu rentres le type de machine, la RAM, le nombre de CPUs, et il te donne les paramètres optimaux. C'est un bon point de depart.

VACUUM et autovacuum

PostgreSQL utilise MVCC (Multi-Version Concurrency Control). Quand tu fais un UPDATE, l'ancienne version de la ligne n'est pas supprimee immédiatement. Elle est marquee comme "morte" et reste sur disque. VACUUM nettoie ces lignes mortes.

sql-- VACUUM manuel
VACUUM orders;

-- VACUUM avec analyse des statistiques
VACUUM ANALYZE orders;

-- VACUUM FULL (reecrit la table, prend un lock exclusif, a utiliser rarement)
VACUUM FULL orders;

L'autovacuum tourne en permanence et nettoie les tables quand le nombre de lignes mortes dépassé un seuil. Par défaut, il se déclenché quand 20% des lignes d'une table sont mortes.

Pour vérifier l'état des tables :

sqlSELECT
  relname AS table,
  n_live_tup AS lignes_vivantes,
  n_dead_tup AS lignes_mortes,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Si tu vois une table avec 500000 lignes mortes et un last_autovacuum a NULL, c'est que l'autovacuum n'arrive pas a suivre. Soit la table a trop d'ecritures, soit la config autovacuum est trop conservatrice.

Les paramètres a ajuster si l'autovacuum ne suit pas :

autovacuum_vacuum_scale_factor = 0.05  # declencher a 5% de lignes mortes (defaut 20%)
autovacuum_max_workers = 5             # plus de workers en parallele (defaut 3)

Pour les grosses tables (dizaines de millions de lignes), tu peux configurer l'autovacuum par table :

sqlALTER TABLE huge_logs SET (autovacuum_vacuum_scale_factor = 0.01);

Sur paltemps.fr, les tables d'events et de logs ont un autovacuum plus agressif que les tables de référencé. Ca évité que le bloat s'accumule.

Résumé

• EXPLAIN ANALYZE avec BUFFERS pour diagnostiquer les requêtes lentes
• Verifier le ratio rows estimated vs actual (lancer ANALYZE si trop différent)
• PgBouncer en mode transaction pooling pour gerer les connexions
• shared_buffers = 25% RAM, work_mem = 64MB, effective_cache_size = 75% RAM
• L'autovacuum nettoie les lignes mortes, ajuster ses paramètres pour les tables a fort volume

Article précédent : 12 - Migrations Article suivant : 14 - Sécurité

Sources

• PostgreSQL Documentation - Using EXPLAIN
• PostgreSQL Documentation - pg_stat_statements
• PGTune - PostgreSQL Configuration Calculator
• pganalyze - VACUUM in PostgreSQL