PostgreSQL : optimisation des performances avec exemples pratiques

Introduction

PostgreSQL peut délivrer d'excellentes performances lorsque la charge, le schéma, les requêtes et les paramètres sont cohérents. Ce guide pratique explique comment détecter les goulots d'étranglement, dimensionner correctement les ressources, réduire la latence, augmenter le débit et déployer les changements en toute sécurité. Vous trouverez des commandes concrètes, du SQL et des patterns immédiatement applicables. Dans l'écosystème francophone, on rencontre aussi les termes anglais suivants, que nous mentionnons pour clarté : PostgreSQL performance, PostgreSQL tuning, PostgreSQL optimization, PostgreSQL latency, PostgreSQL bottlenecks.

Vue d'ensemble du workflow

Utilisez un processus reproductible pour éviter les suppositions :

1. Baseline

• Capturez les faits de charge : connexions, QPS, latence p95, CPU, mémoire, I/O et plus grosses tables.
• Consignez la configuration actuelle et le matériel.

1. Triage des goulots d'étranglement

• Déterminez si la limite vient du CPU, de la mémoire, de l'I/O, des verrous ou de la conception des requêtes.

1. Formulation d'une hypothèse

• Choisissez une cause racine à traiter (ex. : index manquant, bloat sur une table chaude, work_mem mal dimensionné).

1. Changement minimal

• Privilégiez des tests au niveau session. Pour le DDL, utilisez CONCURRENTLY lorsque possible.

1. Vérification par la mesure

• Appuyez-vous sur EXPLAIN (ANALYZE, BUFFERS), les deltas de pg_stat_statements et les métriques système.

1. Déploiement et observation

• Appliquez pendant un créneau de maintenance si le risque est élevé. Surveillez des indicateurs avancés et retardés.

1. Itération

• Passez au goulot suivant uniquement après avoir confirmé les gains.

Contrôle de santé rapide

Exécutez ces vérifications pour comprendre l'état actuel.

Connexions et charge :

SELECT numbackends, xact_commit, xact_rollback, blks_read, blks_hit
FROM pg_stat_database WHERE datname = current_database();

Top requêtes par temps moyen et temps total (nécessite pg_stat_statements) :

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Efficacité du cache (plus c'est élevé, mieux c'est) :

SELECT sum(blks_hit) / NULLIF(sum(blks_hit + blks_read),0)::numeric AS cache_hit_ratio
FROM pg_stat_database;

Pression autovacuum et tuples morts :

SELECT relname, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Attentes de verrou (bloqueurs actifs en premier) :

SELECT a.pid, a.usename, a.state, l.locktype, l.mode,
       l.relation::regclass AS relation, now() - a.query_start AS age,
       a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
ORDER BY age DESC
LIMIT 20;

Dimensionnement des ressources et configuration

Adaptez les réglages au matériel et à la charge. Les valeurs ci-dessous sont des points de départ, pas des absolus.

Principes clés

• Conservez fsync=on pour la durabilité ; ne le désactivez jamais en production.
• Utilisez un pool de connexions afin que max_connections reste modeste (par exemple 100-300). Moins de backends = moins de commutations de contexte et de pression mémoire.
• Réglez la mémoire avec la concurrence en tête. work_mem est par opération de tri/hash, pas par session.

Mémoire

Exemple : 64 Go RAM, shared_buffers 16 Go, marge 8 Go → 40 Go restants. Avec ~200 requêtes concurrentes et 2 tris chacune → 40 Go / 400 = 100 Mo. Fixez work_mem ~64-96 Mo globalement et augmentez au besoin par session :

• shared_buffers : 15-25 % de la RAM sur un hôte dédié. Trop haut peut concurrencer le cache de l'OS.
• effective_cache_size : 50-75 % de la RAM pour refléter le cache OS ; cela guide le planificateur.
• work_mem : budget = (RAM − shared_buffers − marge OS) / (concurrent_sorts).

SET work_mem = '512MB';  -- pour une seule session

• maintenance_work_mem : utilisez des valeurs plus élevées pour VACUUM, CREATE INDEX et REINDEX.

I/O et WAL

• max_wal_size : augmentez pour réduire la fréquence des checkpoints sur les systèmes très écrits (par ex. 8-32 Go+ selon le volume).
• checkpoint_timeout : 10-15 min est courant ; gardez des checkpoints prévisibles.
• checkpoint_completion_target : 0.8-0.95 pour lisser l'I/O.
• wal_compression=on peut réduire l'I/O d'écriture pour des charges riches en updates.
• synchronous_commit : on pour la sécurité ; envisagez off pour des insertions non critiques à haut débit après test.
• effective_io_concurrency : plus élevé (par ex. 100-300) aide sur du stockage rapide avec parallélisme.

Planificateur

• random_page_cost : sur SSD, visez ~1.1-1.5 (par défaut plus élevé), seq_page_cost ~1.0.
• default_statistics_target : augmentez (par ex. 200-500) pour des colonnes asymétriques afin d'améliorer les estimations.

Parallélisme

• max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather : activez 1-4 workers par gather pour des requêtes analytiques après validation des gains.

Vérifications de latence

Trouvez les chemins lents, expliquez-les et confirmez le correctif.

Étape 1 : identifier les valeurs aberrantes

SELECT queryid, query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Étape 2 : expliquer une requête lente

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.customer_id, sum(o.total_cents) AS revenue
FROM orders o
WHERE o.status = 'PAID'
  AND o.created_at >= now() - interval '30 days'
GROUP BY o.customer_id
ORDER BY revenue DESC
LIMIT 20;

Si vous voyez un Seq Scan sur orders avec beaucoup de lignes filtrées, ajoutez un index ciblé.

Correctif : index partiel + couvrant pour les commandes payées récentes

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
  ON orders (created_at DESC)
  WHERE status = 'PAID';

Recontrôlez le plan ; il devrait basculer en Index Only Scan si la table est bien vacuumée :

• Index Only Scan utilisant idx_orders_paid_recent
• Buffers : moins de lectures, moins de hits partagés/locaux
• Temps d'exécution en baisse ; validez que les lignes retournées sont correctes

Étape 3 : valider les améliorations

• Comparez mean_exec_time et calls dans pg_stat_statements avant/après.
• Vérifiez les buffers dans EXPLAIN pour confirmer la baisse des lectures.
• Surveillez la latence p95/p99 sous une charge identique ou supérieure.

Optimisation du débit

Augmentez le QPS en supprimant les points chauds et le travail inutile.

Index

• L'ordre des colonnes d'un index composite compte : placez d'abord les colonnes les plus sélectives ou utilisées en jointure/filtre.
• Utilisez INCLUDE pour créer des index couvrants sans impacter l'ordonnancement.
• Employez des index partiels pour des prédicats fréquents (status='PAID', tenant_id=..., soft-deleted=false) afin de limiter la taille.

Exemple : accélérer les recherches et tris

-- Avant : lent à cause du filtre + tri sur une grande table
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50;

-- Après : l'index composite supporte filtre + ordre
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

Forme de requête

• Sélectionnez uniquement les colonnes nécessaires ; évitez SELECT * sur les chemins chauds.
• Préférez EXISTS à IN pour des semi-jointures lorsque pertinent.
• Évitez les fonctions sur les colonnes indexées dans WHERE (transformez plutôt la constante).
• Regroupez les écritures pour réduire le coût des commits ; utilisez COPY pour les chargements de masse.

Gestion des connexions

• Gardez max_connections modeste et utilisez un pooler. Trop de connexions nuisent au débit via la commutation de contexte et la fragmentation mémoire.

Checkpoints et WAL

• Si des pics de latence apparaissent toutes les quelques minutes, augmentez max_wal_size et checkpoint_completion_target pour lisser l'I/O.

Requêtes parallèles (analytique)

• Autorisez 1-4 workers par gather pour de grands scans et agrégations ; validez que CPU et I/O suivent.

Vacuum et contrôle du bloat

Le MVCC crée des tuples morts lors des updates et deletes. Sans contrôle, cela engendre bloat, mauvaise utilisation du cache et requêtes lentes.

Réglages autovacuum

• autovacuum_vacuum_scale_factor : réduisez sous les valeurs par défaut sur les tables chaudes (par ex. 0.05 ou moins) pour lancer plus tôt le vacuum.
• autovacuum_analyze_scale_factor : abaissez pour garder des stats fraîches sur les tables changeant vite.
• autovacuum_vacuum_threshold et analyze_threshold : fixez un plancher absolu pour les petites tables.
• autovacuum_naptime : réduisez pour des charges à mises à jour rapides.
• autovacuum_work_mem : augmentez si la mémoire le permet pour accélérer le traitement.

À surveiller

SELECT relname, n_dead_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Réindexation

• Si le bloat d'index est élevé (taille bien au‑delà de l'attendu), utilisez REINDEX CONCURRENTLY sur des systèmes occupés.
• Évitez VACUUM FULL aux heures de pointe ; c'est bloquant et réécrit la table.

Mises à jour HOT

• Évitez de mettre dans des index « chauds » des colonnes fréquemment mises à jour pour favoriser les HOT updates.

Monitoring et alertes

Bâtissez une visibilité légère et actionnable. Fonctionne bien sur Linux, y compris dans des environnements Docker, avec des sondes SQL simples.

Sondes SQL de base

• Top requêtes :

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

• Ratio de cache :

SELECT sum(blks_hit) / NULLIF(sum(blks_hit + blks_read),0)::numeric AS cache_hit_ratio
FROM pg_stat_database;

• Checkpoints et background writer :

SELECT * FROM pg_stat_bgwriter;

• Attentes et bloqueurs de verrou : utilisez pg_locks joint à pg_stat_activity (voir plus haut).

Alertes opérationnelles (seuils à adapter)

• Latence p95 au‑dessus du SLO pendant N minutes.
• Retard de réplication au‑delà du seuil de bascule.
• Chute inattendue du ratio de cache.
• Distance checkpoint proche de max_wal_size et pics de checkpoints temporisés.
• Retard autovacuum avec beaucoup de tuples morts sur des tables chaudes.
• Taux de croissance disque au‑delà du budget (surveillez les plus grosses relations).

Plan pilote local

Menez un petit pilote mesurable avant un déploiement large.

Objectif

• Réduire de 30 %+ la latence p95 d'une requête lente connue, sans régression.

Périmètre

• Une table (orders) et un point d'entrée unique exécutant la requête cible.

Étapes (1-2 jours)

1. Baseline

• Activez pg_stat_statements si besoin.
• Relevez : calls, mean_exec_time, rows et p95 pendant 1-4 h en charge normale.

1. Hypothèse

• Exemple : ajouter un index partiel couvrant pour status='PAID' et created_at desc.

1. Sécurité

• Utilisez CREATE INDEX CONCURRENTLY pour éviter les longs verrous.
• Testez avec EXPLAIN (ANALYZE, BUFFERS) sur une pré‑production ou en heures creuses.
• Préparez un rollback : DROP INDEX CONCURRENTLY idx_orders_paid_recent si nécessaire.

1. Implémentation

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at DESC)
WHERE status='PAID';

1. Vérification

• Rejouez EXPLAIN (ANALYZE, BUFFERS) et comparez.
• Comparez les deltas pg_stat_statements (mean_exec_time, total time).
• Surveillez CPU, I/O et attentes de verrous pendant 1-2 h.

1. Décision

• Si les gains sont constants et sans régression, conservez le changement et documentez.
• Sinon, revenez en arrière et tentez l'hypothèse suivante (par ex. ajuster work_mem pour cette requête via SET LOCAL).

Conclusion

L'optimisation PostgreSQL efficace est itérative : mesure, hypothèse, changement, vérification. Démarrez avec un workflow clair, dimensionnez correctement la mémoire et le WAL, ciblez les requêtes les plus impactantes avec des index précis et maintenez un vacuum sain pour éviter le bloat. Exécutez d'abord un pilote restreint, confirmez les gains avec EXPLAIN et pg_stat_statements, puis généralisez l'approche. Révisez régulièrement vos réglages et vos index à mesure que les données et le trafic évoluent.