## Introduction

PostgreSQL offre d'excellentes performances lorsque la charge de travail, le schéma, les requêtes et les paramètres sont alignés. Ce guide montre comment identifier les goulots d'étranglement, dimensionner les ressources, réduire la latence, augmenter le débit et déployer les changements en toute sécurité. Vous obtiendrez des commandes concrètes, du SQL et des motifs applicables immédiatement.

## Vue d'ensemble du flux de travail

Utilisez un processus répétable pour éviter les suppositions :

- Référence (baseline)

- Capturez les faits de charge : connexions, QPS (Queries Per Second) : requêtes par seconde, p95 (95e percentile) : 95e percentile de latence, CPU, mémoire, E/S (I/O) : entrées/sorties, et plus grosses tables.

- Enregistrez les paramètres actuels et le matériel.

- Triage des goulots d'étranglement

- Identifiez si vous êtes limité par le CPU, la mémoire, les E/S, les verrous ou la conception des requêtes.

- Formuler une hypothèse

- Choisissez une cause racine à traiter (par exemple, index manquant, gonflement d'une table chaude, work_mem mal dimensionné).

- Changer minimalement

- Préférez d'abord des tests au niveau session. Pour les DDL, utilisez CONCURRENTLY quand c'est possible.

- Vérifier par la mesure

- Utilisez EXPLAIN (ANALYZE, BUFFERS) , les deltas de pg_stat_statements et les métriques système.

- Déployer et surveiller

- Appliquez dans une fenêtre de maintenance si le risque est élevé. Surveillez les indicateurs avancés et retardés.

- Itérer

- Passez au goulot suivant seulement après avoir validé les gains.

## Vérification rapide de santé

Exécutez ces contrôles 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(); 

### Requêtes les plus lentes par temps moyen et 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 (taux de hit plus élevé est meilleur)

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

### Pression d'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 d'abord)

 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

 Ajustez les paramètres à votre matériel et charge. Les valeurs ci-dessous sont des points de départ, pas des absolus.

### Principes de base

- Maintenez fsync=on pour la durabilité ; ne le désactivez jamais en production.

- Utilisez un pooler de connexions pour que max_connections reste modeste (par exemple 100-300). Moins de backends réduisent le changement de contexte et la pression mémoire.

- Ajustez la mémoire en tenant compte de la concurrence. work_mem est par opération de tri/hachage, pas par session.

### Mémoire

Exemple : 64 Go de RAM, shared_buffers 16 Go, marge OS (Operating System) : système d'exploitation 8 Go laisse 40 Go. Avec ~200 requêtes concurrentes et 2 tris chacune, cela fait 400 opérations. 40 Go / 400 = 100 Mo par opération. Réglez work_mem ~64-96 Mo globalement, et augmentez par session pour les gros traitements :

- shared_buffers : 15-25 % de la RAM sur hôte dédié. Trop haut peut entrer en conflit avec le cache OS.

- effective_cache_size : 50-75 % de la RAM pour refléter le cache OS ; guide le planificateur.

- work_mem : budget = (RAM - shared_buffers - marge OS) / (tris_concurrents).

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

- maintenance_work_mem : valeurs plus élevées pour VACUUM , CREATE INDEX , REINDEX .

### E/S et WAL

- max_wal_size : augmentez pour réduire la fréquence des points de contrôle sur systèmes à forte écriture (par exemple 8-32 Go+ selon le volume).

- checkpoint_timeout : 10-15 min est courant ; gardez les points de contrôle prévisibles.

- checkpoint_completion_target : 0,8-0,95 pour étaler les E/S de point de contrôle.

- wal_compression = on peut réduire les E/S d'écriture pour charges à many updates.

- synchronous_commit : on pour la sécurité ; envisagez off pour insertions non critiques à haut débit après tests.

- effective_io_concurrency : plus élevé (par exemple 100-300) aide sur stockage rapide avec bon parallélisme.

### Planificateur

- random_page_cost : pour SSD (Solid State Drive) : disque à état solide réglez près de 1,1-1,5 (défaut plus haut), seq_page_cost ~1,0.

- default_statistics_target : augmentez (par exemple 200-500) sur colonnes avec biais pour améliorer les estimations.

### Parallélisme

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

## Contrôles de latence

 Trouvez les chemins lents, expliquez-les et confirmez la correction.

### É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é.

Correction : index partiel + covering pour commandes payées récentes

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at DESC)
WHERE status = 'PAID'; 
 Re-vérifiez le plan ; il devrait passer à Index Only Scan si la table est bien vacuumée :

- Index Only Scan utilisant idx_orders_paid_recent

- Buffers : moins de lectures, hits shared/local réduits

- Temps d'exécution chute ; confirmez que les lignes 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 moins de lectures.

- Surveillez la latence p95/p99 sous charge égale ou supérieure.

## Optimisation du débit

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

### Index

- L'ordre des colonnes dans un index composite compte : mettez les colonnes les plus sélectives ou de jointure/filtrage en premier.

- Utilisez INCLUDE pour créer des index covering sans affecter l'ordre.

- Utilisez des index partiels pour prédicats fréquents ( status='PAID' , tenant_id=... , soft_deleted=false ) pour garder les index petits.

Exemple : accélérer les recherches et le tri

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

-- Après : index composite supporte filtre + ordre
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC); 
 Exemple : index covering pour éviter les lectures de heap

CREATE INDEX CONCURRENTLY idx_orders_customer_created_cover
ON orders (customer_id, created_at DESC)
INCLUDE (total_cents, status); 

### Forme des requêtes

- Sélectionnez seulement les colonnes nécessaires ; évitez SELECT * dans les chemins chauds.

- Utilisez EXISTS au lieu de IN pour les semi-jointures quand approprié.

- Évitez les fonctions sur colonnes indexées dans WHERE (enveloppez la constante à la place).

- Groupez les écritures pour réduire la surcharge de commit ; privilégiez COPY pour les chargements en masse.

### Gestion des connexions

- Gardez max_connections modeste et utilisez un pooler. Des connexions sur-provisionnées tuent le débit via changement de contexte et fragmentation mémoire.

### Points de contrôle et WAL

- Si des pics de latence apparaissent toutes les quelques minutes, augmentez max_wal_size et checkpoint_completion_target pour lisser les E/S.

### Requête parallèle (analytics)

- Autorisez 1-4 workers parallèles par gather pour gros scans et agrégations ; vérifiez que CPU et E/S le supportent.

## Vacuum et contrôle du gonflement

 MVCC (Multi-Version Concurrency Control) : contrôle de concurrence multi-version crée des tuples morts sur mises à jour et suppressions. Laissé sans surveillance, ils causent gonflement tables/index, mauvaise utilisation du cache et requêtes lentes.

### Paramètres d'autovacuum

- autovacuum_vacuum_scale_factor : réduisez par rapport aux défauts sur tables chaudes (par exemple 0,05 ou moins) pour déclencher vacuum plus tôt.

- autovacuum_analyze_scale_factor : abaissez pour garder stats fraîches sur tables à changement rapide.

- autovacuum_vacuum_threshold et analyze_threshold : fixez un plancher absolu pour petites tables.

- autovacuum_naptime : raccourcissez pour charges à mises à jour rapides.

- autovacuum_work_mem : augmentez pour accélérer le traitement si la mémoire le permet.

### À surveiller

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

### Autovacuum par table (exemple pour table orders chaude)

 ALTER TABLE orders SET (
 autovacuum_vacuum_scale_factor = 0.02,
 autovacuum_analyze_scale_factor = 0.01
); 

### Réindexation

- Si le gonflement d'index est fort (taille bien supérieure à l'attendu), utilisez REINDEX CONCURRENTLY sur systèmes occupés.

- Évitez VACUUM FULL en heures de pointe ; il est bloquant et réécrit la table.

### Mises à jour HOT

- Gardez les colonnes fréquemment mises à jour hors des indexes chauds si possible pour favoriser les mises à jour HOT.

## Monitoring et alertes

 Construisez une visibilité légère et actionnable.

### Sondes SQL de base

Requêtes les plus lentes :

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20; 
 Taux de cache :

SELECT sum(blks_hit) / NULLIF(sum(blks_hit + blks_read),0)::numeric AS cache_hit_ratio
FROM pg_stat_database; 
 Points de contrôle et background writer :

SELECT * FROM pg_stat_bgwriter; 
 Attentes de verrou et bloqueurs : utilisez la jointure pg_locks / pg_stat_activity montrée plus haut.

### Alertes opérationnelles (exemples de seuils à adapter)

- Latence p95 au-dessus du SLO pendant 5 minutes.

- Lag de réplication au-dessus de 30 secondes (ou votre seuil de bascule).

- Taux de cache chute sous 99 % de façon inattendue.

- Distance de point de contrôle proche de max_wal_size et pics de checkpoints chronométrés.

- Retard d'autovacuum avec beaucoup de tuples morts sur tables chaudes (ex. n_dead_tup > 100k sur table fréquemment mise à jour).

- Croissance du disque au-dessus du budget (surveillez les plus grosses relations).

## Plan pilote local

Exécutez un petit pilote mesurable avant déploiement large.

### Objectif

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

### Périmètre

- Une table ( orders ) et un endpoint exécutant une requête cible.

### Étapes (1-2 jours)

- Référence

- Activez pg_stat_statements si absent.

- Enregistrez : appels, mean_exec_time , lignes, p95 pendant 1-4 h sous charge normale. Capturez le texte exact de la requête et son plan EXPLAIN .

- Hypothèse

- Exemple : ajouter index partiel covering pour status='PAID' et created_at DESC .

- Sécurité

- Utilisez CREATE INDEX CONCURRENTLY pour éviter verrous longs.

- Testez avec EXPLAIN (ANALYZE, BUFFERS) sur copie staging ou hors pointe.

- Gardez un rollback : DROP INDEX CONCURRENTLY idx_orders_paid_recent si nécessaire.

- Implémentation

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

- Vérification

- Relancez EXPLAIN (ANALYZE, BUFFERS) et comparez.

- Comparez les deltas pg_stat_statements pour mean_exec_time et temps total.

- Surveillez CPU, E/S, attentes de verrou pendant 1-2 h.

- Décision

- Si gains constants et sans régression, conservez le changement et documentez-le.

- Sinon, revenez et testez l'hypothèse suivante (par exemple work_mem pour cette requête via SET LOCAL ).

## Conclusion

 L'optimisation efficace de PostgreSQL est itérative : mesurer, hypothétiser, changer, vérifier. Commencez par un flux propre, dimensionnez mémoire et WAL (Write-Ahead Logging) : journalisation en écriture anticipée, ciblez les requêtes à fort impact avec des index précis, et maintenez vacuum sain pour éviter le gonflement. Lancez un pilote étroit, confirmez les gains réels avec EXPLAIN et pg_stat_statements , puis étendez l'approche. Revoyez paramètres et indexes à mesure que données et trafic croissent.