Aperçu des patterns de production

Dans les vrais projets backend, la performance de PostgreSQL repose rarement sur une seule requête lente. C'est la combinaison de la façon dont votre application se connecte, dont elle interroge et dont votre schéma évolue au fil du temps. Cette leçon couvre quatre patterns de production qui ont un impact direct sur les performances de bout en bout : le connection pooling avec PgBouncer, les stratégies de pagination pour les grands ensembles de résultats, les workflows de migration de schéma avec des outils comme sqitch ou Flyway, et une approche systématique de l'optimisation des performances qui part de la requête et descend jusqu'à la configuration de la base de données.

L'objectif est de passer du « ça marche sur ma machine » à un comportement prévisible sous charge réelle.

Connection pooling avec PgBouncer

Chaque connexion à PostgreSQL fork un processus backend, ce qui coûte de la mémoire et du CPU. Un backend qui reçoit des centaines de requêtes simultanées va épuiser la limite de connexions rapidement. PgBouncer s'intercale entre votre application et PostgreSQL, en multiplexant de nombreuses connexions applicatives sur un pool plus restreint de connexions réelles à la base de données.

Le mode de production le plus courant est le transaction pooling, où une connexion n'est assignée à un client que pour la durée d'une transaction :

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Dans votre application, vous pointez l'hôte de la base de données vers le port 6432 au lieu de 5432. Une mise en garde importante avec le transaction pooling : les prepared statements, les commandes `SET`, et les fonctionnalités de niveau session comme `LISTEN/NOTIFY` ne fonctionnent pas de manière fiable car la connexion sous-jacente peut changer entre les transactions. N'utilisez les server-side prepared statements qu'en mode session pooling.

Stratégies de pagination

La pagination par offset avec `LIMIT ... OFFSET ...` est pratique mais offre de mauvaises performances sur de grandes tables, car PostgreSQL doit quand même parcourir et rejeter toutes les lignes sautées. Sur une table contenant des millions de lignes, la page 1000 est nettement plus lente que la page 1.

La pagination keyset (aussi appelée pagination par curseur) évite complètement l'offset en utilisant la dernière valeur vue sur une colonne indexée :

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

La seconde requête utilise un index composite sur `(created_at, id)` et peut sauter directement à la bonne position. Elle renvoie aussi des résultats stables même si de nouvelles lignes sont insérées entre deux chargements de page, ce qui est essentiel pour les feeds et le défilement infini.

Migrations de schéma

En production, les changements de schéma doivent être reproductibles, reviewables et réversibles. Des outils comme Flyway, Liquibase ou sqitch stockent les migrations sous forme de fichiers SQL versionnés qui s'exécutent dans l'ordre, avec une table de suivi enregistrant quelles migrations ont été appliquées.

Un fichier de migration classique ressemble à ceci :

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Trois règles comptent plus que tout. Premièrement, utilisez toujours `CREATE INDEX CONCURRENTLY` sur les tables existantes afin de ne pas verrouiller les écritures pendant la création de l'index. Deuxièmement, ne mélangez jamais des changements destructeurs (suppression d'une colonne) avec de nouvelles fonctionnalités dans la même migration, pour que le rollback soit possible. Troisièmement, testez les migrations contre une copie des données de production, car les statistiques du planner PostgreSQL peuvent révéler des problèmes qu'une petite base de développement ne montre jamais.

Optimisation des performances de bout en bout

L'optimisation suit une approche en couches. D'abord, identifiez la requête lente avec `pg_stat_statements` ou via les logs applicatifs. Ensuite, exécutez `EXPLAIN (ANALYZE, BUFFERS)` pour voir le plan réel, le nombre de lignes et les buffer hits versus les lectures disque. Troisièmement, corrigez la cause racine, qui est généralement un index manquant, un mauvais ordre de jointure, ou une requête qui récupère plus de données que nécessaire.

Ce n'est qu'une fois la couche requêtes nettoyée que vous devez toucher aux paramètres de la base. Les gains courants incluent l'augmentation de `shared_buffers` à environ 25 pour cent de la RAM, le réglage de `effective_cache_size` à environ 75 pour cent, et l'ajustement de `work_mem` par requête si de gros sorts ou hashes spillent sur disque. Mais gardez en tête : aucune configuration ne pourra sauver une requête qui parcourt une table entière à chaque requête.

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

En résumé

La performance de PostgreSQL en production est une propriété système, et pas un simple bouton. Pooler les connexions avec PgBouncer pour gérer la concurrence sans épuiser la base, remplacer l'offset pagination par la keyset pagination sur les grandes tables, gérer les changements de schéma via des outils de migration versionnés, et suivre un workflow d'optimisation query-first avant de toucher à la configuration serveur. Ensemble, ces patterns vous donnent une latence prévisible et une voie claire à suivre lorsque la charge augmente.