# CLAUDE.md — PostgreSQL Performance & Optimisation
> Contexte spécialisé pour Claude Code. Coller ce fichier à la racine du projet pour guider le debug et l'optimisation de requêtes PostgreSQL.
---
## Quand utiliser ce contexte
- ✅ Diagnostic de requêtes lentes identifiées via pg_stat_statements ou logs applicatifs
- ✅ Trafic élevé avec contention de locks, timeouts ou dégradation progressive
- ✅ Mise en place d'une stratégie d'indexation sur une table qui grossit
- ✅ Audit de performance avant un passage en production ou une mise à l'échelle
- ❌ MVP ou prototype : optimiser prématurément une BDD sans données réelles est du temps perdu
- ❌ Moins de 10k lignes et < 100 requêtes/min : les optimisations par défaut de PostgreSQL suffisent
---
## Section 1 : Principe fondamental — mesurer avant d'agir
Ne jamais optimiser à l'aveugle. Toute modification de requête, d'index ou de configuration doit être précédée d'une **mesure** et suivie d'une **vérification** de l'impact réel.
### Règles d'or
1. **EXPLAIN ANALYZE** avant et après chaque changement — jamais de "ça devrait être plus rapide"
2. **Changer une seule chose à la fois** — impossible d'identifier la cause si on modifie plusieurs variables simultanément
3. **Mesurer dans des conditions réelles** — données de production, cache chaud, charge représentative
4. **Conserver les baselines** — noter les métriques avant toute intervention
5. **pg_stat_statements** est votre premier outil, pas votre cerveau
### Workflow de debug recommandé
```
1. Identifier → pg_stat_statements (quelles requêtes coûtent cher ?)
2. Analyser → EXPLAIN (ANALYZE, BUFFERS) (pourquoi c'est lent ?)
3. Hypothèse → une seule piste à la fois
4. Tester → appliquer le changement en dev/staging
5. Mesurer → comparer avant/après avec EXPLAIN ANALYZE
6. Valider → vérifier l'impact en production avec pg_stat_statements
7. Documenter → noter ce qui a été fait et pourquoi
```
### Ce qu'il ne faut jamais faire
```sql
-- ❌ MAUVAIS: Ajouter un index "parce que la colonne est souvent utilisée"
CREATE INDEX idx_users_all ON users (id, email, status, created_at);
-- ❌ MAUVAIS: VACUUM FULL en production sans fenêtre de maintenance
VACUUM FULL orders; -- Pose un verrou exclusif !
-- ❌ MAUVAIS: Changer work_mem globalement sans mesure
SET work_mem = '1GB'; -- Peut tuer la RAM si 100 connexions
-- ✅ BON: Mesurer d'abord, cibler précisément
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
-- Lire le plan, identifier le problème, créer l'index ciblé
```
---
## Section 2 : Identifier les requêtes lentes (pg_stat_statements)
`pg_stat_statements` est l'extension indispensable pour le diagnostic de performance. Elle agrège les statistiques d'exécution de toutes les requêtes.
### Activation
```sql
-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all -- all, top, none
pg_stat_statements.max = 10000 -- Nombre de requêtes suivies
pg_stat_statements.track_utility = on -- Track COPY, CREATE TABLE, etc.
-- Après redémarrage PostgreSQL, créer l'extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```
### Requêtes de diagnostic essentielles
```sql
-- Top 20 requêtes par temps total d'exécution (le plus important)
SELECT
round(total_exec_time::numeric, 2) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
calls,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) AS percent_total,
left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE userid = (SELECT oid FROM pg_roles WHERE rolname = current_user)
ORDER BY total_exec_time DESC
LIMIT 20;
```
```sql
-- Top requêtes par temps moyen (latence élevée, requêtes ponctuelles lentes)
SELECT
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
round(total_exec_time::numeric, 2) AS total_exec_ms,
calls,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE calls > 10 -- Éviter les requêtes exécutées une seule fois
ORDER BY mean_exec_time DESC
LIMIT 20;
```
```sql
-- Requêtes avec le plus d'I/O disque (shared_blks_read élevé)
SELECT
round(total_exec_time::numeric, 2) AS total_exec_ms,
calls,
shared_blks_hit,
shared_blks_read,
round(100.0 * shared_blks_hit /
NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio,
left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;
```
```sql
-- Requêtes avec beaucoup d'écritures temporaires (work_mem insuffisant)
SELECT
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
calls,
temp_blks_read,
temp_blks_written,
left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;
```
```sql
-- Identifier les patterns N+1 (beaucoup d'appels, temps moyen faible)
SELECT
calls,
round(mean_exec_time::numeric, 4) AS mean_exec_ms,
round(total_exec_time::numeric, 2) AS total_exec_ms,
left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE calls > 10000
AND mean_exec_time < 5 -- Rapides individuellement mais appelées en masse
ORDER BY calls DESC
LIMIT 20;
```
```sql
-- Réinitialiser les statistiques (faire avant un test de performance)
SELECT pg_stat_statements_reset();
-- Ou réinitialiser pour une requête spécifique (PG14+)
SELECT pg_stat_statements_reset(0, 0, queryid) -- remplacer queryid
FROM pg_stat_statements
WHERE query LIKE '%orders%'
LIMIT 1;
```
### Slow query logging
```sql
-- postgresql.conf : logger les requêtes dépassant 1 seconde
log_min_duration_statement = 1000 -- ms, 0 = tout, -1 = désactivé
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_lock_waits = on
log_temp_files = 0 -- Logger tout fichier temporaire
```
### auto_explain — EXPLAIN automatique sur les requêtes lentes
```sql
-- postgresql.conf (ou via ALTER SYSTEM)
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000 -- ms
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_nested_statements = on -- Inclure les subplans
auto_explain.log_format = text
-- Activer dynamiquement pour une session (sans redémarrage)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 500;
SET auto_explain.log_analyze = true;
```
### Requêtes en cours d'exécution
```sql
-- Voir toutes les requêtes actives avec leur durée
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
state,
wait_event_type,
wait_event,
left(query, 200) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start IS NOT NULL
ORDER BY duration DESC;
```
```sql
-- Tuer une requête qui s'emballe (annulation propre)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345; -- Remplacer par le PID cible
-- Tuer si pg_cancel_backend échoue (force)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;
-- Tuer toutes les requêtes dépassant 30 minutes (à utiliser avec précaution)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
AND query_start < NOW() - INTERVAL '30 minutes'
AND pid != pg_backend_pid(); -- Ne pas se tuer soi-même
```
---
## Section 3 : EXPLAIN ANALYZE — lecture et interprétation
`EXPLAIN ANALYZE` est l'outil central du diagnostic de performance. Il exécute réellement la requête et affiche le plan d'exécution avec les coûts estimés ET réels.
### Syntaxe recommandée
```sql
-- Toujours utiliser ces options ensemble
EXPLAIN (
ANALYZE, -- Exécute la requête et mesure les temps réels
BUFFERS, -- Affiche les lectures/écritures de blocs cache/disque
VERBOSE, -- Détails supplémentaires (colonnes de sortie, etc.)
FORMAT TEXT -- TEXT pour la lecture humaine, JSON pour les outils
) SELECT ...;
```
### Protection des requêtes destructives
```sql
-- ⚠️ ANALYZE exécute vraiment la requête !
-- Protéger les INSERT/UPDATE/DELETE dans une transaction annulée
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE created_at < NOW() - INTERVAL '2 years';
ROLLBACK; -- La suppression n'est PAS appliquée
```
### Lire un nœud d'exécution
Un nœud ressemble à :
```
Seq Scan on orders (cost=0.00..45231.00 rows=1200000 width=89)
(actual time=0.023..412.456 rows=1198234 loops=1)
Filter: (status = 'pending')
Rows Removed by Filter: 4872
Buffers: shared hit=12432 read=20134
```
Décomposition :
- `cost=0.00..45231.00` : coût estimé (startup..total, unités arbitraires)
- `rows=1200000` : nombre de lignes **estimé** par le planner
- `actual time=0.023..412.456` : temps réel en millisecondes (startup..total)
- `rows=1198234` : nombre de lignes **réellement** retournées
- `loops=1` : nombre de fois que ce nœud a été exécuté
- `Buffers: shared hit=12432 read=20134` : blocs trouvés en cache vs lus sur disque
### Importance de `loops`
```
-- ⚠️ Attention ! Les temps sont par loop, pas en total
Nested Loop (cost=... actual time=0.1..0.5 rows=3 loops=50000)
-- Temps réel total = 0.5ms × 50000 = 25 secondes !
```
### Tableau des nœuds principaux
| Nœud | Description | Quand c'est un problème |
|------|-------------|------------------------|
| `Seq Scan` | Parcours séquentiel complet de la table | Sur grande table avec faible sélectivité |
| `Index Scan` | Parcours d'index + fetch des lignes | Rarement problématique |
| `Index Only Scan` | Index seul, sans accès table | Idéal — aucun problème |
| `Bitmap Index Scan` | Construit un bitmap d'OIDs | Normal pour sélectivité ~1-5% |
| `Bitmap Heap Scan` | Accède aux lignes via bitmap | Normal avec Bitmap Index Scan |
| `Nested Loop` | Pour chaque ligne outer, cherche inner | Problématique si loops très élevé |
| `Hash Join` | Construit une hash table puis sonde | `Hash Batches > 1` = spill disque |
| `Merge Join` | Fusionne deux entrées triées | Coûteux si Sort avant |
| `Sort` | Tri en mémoire ou disque | `external merge` = manque work_mem |
| `Hash Aggregate` | Agrégation via hash table | `Batches > 1` = spill disque |
| `Materialize` | Met en cache un sous-plan | Peut indiquer CTE forcé |
| `Gather` | Collecte les résultats des workers parallèles | Normal pour requêtes parallèles |
### Signaux d'alarme (red flags)
```sql
-- Red flag 1: Seq Scan sur grande table
Seq Scan on orders (... rows=5000000 ...)
-- → Envisager un index sur la colonne filtrée
-- Red flag 2: Estimation très éloignée de la réalité
Index Scan (... rows=100 ...) (actual ... rows=95000 ...)
-- rows estimé vs réel avec ratio > 10x → statistiques obsolètes
-- Corriger: ANALYZE orders;
-- Red flag 3: Sort externe sur disque
Sort (... Sort Method: external merge Disk: 42832kB)
-- → Augmenter work_mem pour cette requête
-- Red flag 4: Hash Join avec batches
Hash (... Batches: 8 Memory Usage: 4096kB)
-- Batches > 1 = hash table ne rentre pas en RAM
-- → Augmenter work_mem
-- Red flag 5: Beaucoup de blocs lus depuis le disque
Buffers: shared hit=100 read=500000
-- hit faible vs read élevé = données pas en cache
-- → Peut indiquer table trop grosse pour shared_buffers
-- Red flag 6: Rows Removed by Filter élevé
Filter: (status = 'active')
Rows Removed by Filter: 4999850
-- Scanne 5M lignes pour en garder 150 → index nécessaire
-- Red flag 7: Loops très élevé sur Nested Loop
Nested Loop (actual ... loops=50000)
-- → Vérifier que l'index est bien présent sur la table inner
```
### Forcer un plan pour le débogage
```sql
-- Forcer un Seq Scan pour comparer avec un index
SET enable_indexscan = off;
SET enable_indexonlyscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_indexscan;
RESET enable_indexonlyscan;
RESET enable_bitmapscan;
-- Forcer le Seq Scan (tester si l'index aide vraiment)
SET enable_seqscan = off; -- Désactiver pour forcer l'utilisation d'index
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SET enable_seqscan = on;
-- ⚠️ Ne jamais laisser ces settings en production !
```
### Outils de visualisation
- **explain.dalibo.com** — Visualisation graphique du plan (coller le JSON)
- **explain.depesz.com** — Analyse textuelle avec colorisation des nœuds lents
```sql
-- Générer un plan au format JSON pour les visualiseurs
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;
```
### Mise à jour des statistiques
```sql
-- Mettre à jour les statistiques d'une table spécifique
ANALYZE orders;
-- Mettre à jour toutes les statistiques
ANALYZE;
-- Vérifier quand une table a été analysée pour la dernière fois
SELECT
schemaname,
tablename,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_analyze ASC NULLS FIRST;
```
---
## Section 4 : Types d'index — quand utiliser lequel
PostgreSQL propose plusieurs types d'index, chacun adapté à des cas d'usage spécifiques. Choisir le mauvais type annule tout bénéfice.
### B-tree (défaut)
Le type d'index universel. Utilisé automatiquement avec `CREATE INDEX`.
```sql
-- Cas d'usage: égalité, inégalité, range, ORDER BY, LIKE 'préfixe%'
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_users_email ON users (email);
-- Supporte:
-- WHERE status = 'active'
-- WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
-- WHERE email LIKE 'john%' ← préfixe seulement
-- ORDER BY created_at DESC ← peut utiliser l'index
```
```sql
-- Index composite B-tree: ordre des colonnes CRUCIAL
-- Règle: colonne la plus sélective en premier (égalité avant range)
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);
-- Requête qui utilise l'index:
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
-- La règle "left-prefix": WHERE status = 'pending' utilise aussi l'index (colonne 1 seulement)
-- WHERE created_at > ... SANS status N'utilise PAS l'index composite
-- ❌ Mauvais ordre: range en premier
CREATE INDEX idx_bad ON orders (created_at, status); -- Moins efficace pour le filtre status
```
### Hash
Uniquement pour l'égalité stricte. Plus rapide que B-tree pour les grandes valeurs (UUID, texte long).
```sql
-- Cas d'usage: égalité uniquement sur colonnes de grande taille
CREATE INDEX idx_users_uuid ON users USING HASH (uuid);
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);
-- Supporte UNIQUEMENT:
-- WHERE uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
-- Ne supporte PAS: <, >, BETWEEN, ORDER BY
```
### GIN (Generalized Inverted Index)
Optimal pour les types "contenant plusieurs valeurs" : JSONB, tableaux, full-text search.
```sql
-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('french', title || ' ' || body);
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);
-- Requête full-text avec l'index
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('french', 'PostgreSQL & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;
```
```sql
-- JSONB: opérateurs @> (contient) et ? (clé existe)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
-- Requêtes supportées
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM products WHERE attributes ? 'warranty';
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];
```
```sql
-- Tableaux PostgreSQL
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
```
```sql
-- LIKE '%terme%' avec pg_trgm (trigrammes)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
-- Maintenant LIKE avec wildcard leading fonctionne via l'index
SELECT * FROM users WHERE name ILIKE '%dupont%';
SELECT * FROM users WHERE name ~ 'dup.*nt'; -- Regex aussi
```
### GiST (Generalized Search Tree)
Pour les types géométriques, les ranges, et les recherches de proximité.
```sql
-- Types géométriques (PostGIS)
CREATE INDEX idx_places_location ON places USING GIST (location);
SELECT * FROM places ORDER BY location <-> ST_MakePoint(2.3488, 48.8534) LIMIT 10;
-- Range types
CREATE TABLE reservations (
id serial PRIMARY KEY,
room_id int,
period tsrange
);
CREATE INDEX idx_reservations_period ON reservations USING GIST (period);
-- Rechercher les chevauchements
SELECT * FROM reservations WHERE period && tsrange('2024-06-01', '2024-06-07');
-- Exclusion constraint (empêcher les chevauchements)
ALTER TABLE reservations
ADD CONSTRAINT no_overlap EXCLUDE USING GIST (room_id WITH =, period WITH &&);
```
### BRIN (Block Range INdex)
Très petit index pour les très grandes tables dont les données sont physiquement corrélées avec leur ordre d'insertion.
```sql
-- Cas idéal: table de logs, événements, time-series (append-only, jamais mis à jour)
CREATE INDEX idx_events_created_at_brin ON events USING BRIN (created_at)
WITH (pages_per_range = 128); -- Défaut: 128 pages par range
-- Fonctionne parce que: les événements récents sont dans des blocs récents
-- L'index note juste: "blocs 1-128 contiennent dates du 2024-01-01 au 2024-01-05"
-- Taille: des centaines de fois plus petit qu'un B-tree
SELECT pg_size_pretty(pg_relation_size('idx_events_created_at_brin'));
-- ⚠️ Inutile si les données sont insérées dans un ordre aléatoire
-- ⚠️ Moins précis qu'un B-tree: peut retourner des faux positifs (rechecked après)
```
### Tableau récapitulatif
| Type | Opérateurs | Cas d'usage | Taille | Notes |
|------|-----------|-------------|--------|-------|
| B-tree | `=` `<` `>` `<=` `>=` `BETWEEN` `LIKE 'x%'` | Universel | Moyen | Défaut |
| Hash | `=` uniquement | UUID, tokens longs | Petit | PG10+ stable |
| GIN | `@>` `?` `@@` `&&` | JSONB, arrays, FTS | Grand | Mise à jour lente |
| GiST | `<<` `&&` `@>` `<->` | Géométrie, ranges | Moyen | Nearest-neighbor |
| SP-GiST | `=` `<` `<<` | IP ranges, téléphones | Variable | Données non-balancées |
| BRIN | `=` `<` `>` ranges | Très grandes tables ordonnées | Minuscule | Corrélation physique requise |
---
## Section 5 : Index avancés — partiels, covering, expression
### Index partiels
Un index partiel n'indexe qu'un sous-ensemble des lignes. Idéal quand seule une fraction des données est fréquemment accédée.
```sql
-- Exemple classique: ordres en attente (minorité des lignes)
-- Sans index partiel: index sur TOUTES les commandes (millions de lignes)
CREATE INDEX idx_orders_status_full ON orders (status); -- Index lourd
-- Avec index partiel: index uniquement sur 'pending' (quelques milliers)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- La requête suivante utilise l'index partiel (beaucoup plus petit et rapide)
SELECT * FROM orders
WHERE status = 'pending'
AND created_at > NOW() - INTERVAL '24 hours';
```
```sql
-- Soft-delete pattern: index uniquement sur les lignes non supprimées
CREATE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;
-- Utilisé par:
SELECT * FROM users WHERE email = 'john@example.com' AND deleted_at IS NULL;
-- Garantir l'unicité uniquement sur les utilisateurs actifs
CREATE UNIQUE INDEX idx_users_unique_email_active ON users (email)
WHERE deleted_at IS NULL;
```
```sql
-- Index partiel pour éviter les NULL dans l'index
CREATE INDEX idx_orders_completed_at ON orders (completed_at)
WHERE completed_at IS NOT NULL;
-- Beaucoup plus petit si 80% des commandes n'ont pas de completed_at
```
### Index couvrants (INCLUDE)
Permettent un `Index Only Scan` en incluant des colonnes supplémentaires dans l'index sans les indexer.
```sql
-- Sans INCLUDE: Index Scan nécessite un accès table pour récupérer name et email
CREATE INDEX idx_users_id ON users (id);
SELECT id, name, email FROM users WHERE id = 12345;
-- → Index Scan + Heap Fetch (accès table)
-- Avec INCLUDE: Index Only Scan, pas d'accès table
CREATE INDEX idx_users_id_covering ON users (id) INCLUDE (name, email, created_at);
SELECT id, name, email FROM users WHERE id = 12345;
-- → Index Only Scan (tout est dans l'index)
```
```sql
-- Cas pratique: pagination de liste avec tri
CREATE INDEX idx_orders_user_covering ON orders (user_id, created_at DESC)
INCLUDE (id, status, total_amount);
-- Requête de liste utilisateur entièrement servie par l'index
SELECT id, status, total_amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- → Index Only Scan, aucun accès table
```
```sql
-- Vérifier si un Index Only Scan est utilisé (vérifier le heap fetches)
SELECT
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch -- Si proche de 0 → Index Only Scan effectif
FROM pg_stat_user_indexes
WHERE relname = 'orders';
```
### Index sur expressions
Indexer le résultat d'une fonction ou expression.
```sql
-- Recherche case-insensitive sur email
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- La requête DOIT utiliser exactement la même expression
SELECT * FROM users WHERE lower(email) = lower('John@Example.com');
-- ✅ Utilise l'index
SELECT * FROM users WHERE email = 'john@example.com';
-- ❌ N'utilise PAS l'index (expression différente)
```
```sql
-- Extraire une partie de date pour regrouper par mois
CREATE INDEX idx_orders_year_month ON orders (date_trunc('month', created_at));
SELECT date_trunc('month', created_at), count(*)
FROM orders
GROUP BY date_trunc('month', created_at);
-- ✅ Utilise l'index sur expression
```
```sql
-- Indexer un champ JSONB spécifique (plus ciblé que GIN sur tout le champ)
CREATE INDEX idx_orders_metadata_source ON orders ((metadata->>'source'));
SELECT * FROM orders WHERE metadata->>'source' = 'mobile_app';
-- ✅ Utilise l'index expression (plus efficace que GIN sur tout le JSONB)
```
```sql
-- Computed column simulée avec index expression
CREATE INDEX idx_users_full_name ON users ((first_name || ' ' || last_name));
SELECT * FROM users WHERE first_name || ' ' || last_name = 'Jean Dupont';
```
### Trouver les index inutilisés
```sql
-- Index jamais utilisés depuis le dernier reset des stats
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;
```
```sql
-- Index peu utilisés mais volumineux (rapport taille/utilité faible)
SELECT
t.tablename,
i.indexname,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
CASE WHEN i.idx_scan = 0 THEN 'JAMAIS UTILISÉ'
WHEN i.idx_scan < 100 THEN 'PEU UTILISÉ'
ELSE 'OK'
END AS status
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables t ON i.relid = t.relid
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 30;
```
### Trouver les index manquants
```sql
-- Tables avec beaucoup de Seq Scans (souvent = index manquant)
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
n_live_tup,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 10000 -- Ignorer les petites tables
ORDER BY seq_tup_read DESC
LIMIT 20;
```
---
## Section 6 : Optimisation de requêtes — patterns SQL
### CTEs et matérialisation (PG12+)
```sql
-- Avant PG12: les CTEs étaient TOUJOURS matérialisées (fence d'optimisation)
-- PG12+: le planner peut "inliner" les CTEs par défaut
-- Forcer la matérialisation (comportement PG11)
WITH expensive_subquery AS MATERIALIZED (
SELECT user_id, sum(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY user_id
)
SELECT u.name, e.total
FROM users u
JOIN expensive_subquery e ON u.id = e.user_id;
-- Interdire la matérialisation (laisser le planner optimiser)
WITH user_ids AS NOT MATERIALIZED (
SELECT id FROM users WHERE country = 'FR'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM user_ids);
-- Peut être plus efficace: le planner traite comme un sous-select inline
```
### Optimisation des JOINs
```sql
-- Règle: indexer les colonnes de JOIN des deux côtés
-- La table "inner" dans un Nested Loop a BESOIN d'un index
-- ❌ Problème: JOIN sans index sur la table inner
SELECT u.name, count(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id -- orders.user_id sans index ?
GROUP BY u.name;
-- ✅ Solution: créer l'index sur la FK
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- Vérifier les FK sans index (PostgreSQL ne les crée PAS automatiquement)
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column,
'MISSING INDEX' AS status
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_index pi
JOIN pg_class pc ON pc.oid = pi.indrelid
JOIN pg_attribute pa ON pa.attrelid = pc.oid AND pa.attnum = ANY(pi.indkey)
WHERE pc.relname = tc.table_name
AND pa.attname = kcu.column_name
);
```
### LATERAL JOIN
```sql
-- LATERAL: sous-requête corrélée par ligne (comme un foreach)
-- Utile pour "les N derniers éléments par groupe"
-- Top 3 commandes par utilisateur
SELECT u.id, u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
SELECT id, total_amount, created_at
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) recent_orders
WHERE u.country = 'FR';
-- Plus efficace que:
-- SELECT ... FROM users u JOIN (SELECT ... RANK() OVER (...)) ...
-- car LATERAL peut utiliser l'index sur user_id pour chaque utilisateur
```
### Éviter les fonctions sur colonnes indexées
```sql
-- ❌ MAUVAIS: La fonction empêche l'utilisation de l'index
SELECT * FROM orders WHERE date_trunc('day', created_at) = '2024-01-15';
-- → Seq Scan: le planner ne peut pas utiliser l'index sur created_at
-- ✅ BON: Réécrire pour exposer la colonne nue
SELECT * FROM orders
WHERE created_at >= '2024-01-15'
AND created_at < '2024-01-16';
-- → Index Scan sur created_at ✓
-- ❌ MAUVAIS: cast implicite
SELECT * FROM users WHERE user_id = '12345'; -- TEXT vs INTEGER
-- → Seq Scan ou TypeCast, pas d'index
-- ✅ BON: type explicite
SELECT * FROM users WHERE user_id = 12345; -- INTEGER
-- ou
SELECT * FROM users WHERE user_id = '12345'::integer;
```
### SELECT * et les index-only scans
```sql
-- ❌ MAUVAIS: SELECT * empêche les Index Only Scans
SELECT * FROM users WHERE email = 'john@example.com';
-- → Index Scan + Heap Fetch (doit lire la table pour tous les champs)
-- ✅ BON: Sélectionner uniquement les colonnes nécessaires
SELECT id, name, email FROM users WHERE email = 'john@example.com';
-- → Index Only Scan si (email) INCLUDE (id, name) existe
-- ✅ Encore mieux avec un index couvrant
CREATE INDEX idx_users_email_cov ON users (email) INCLUDE (id, name);
SELECT id, name FROM users WHERE email = 'john@example.com';
-- → Index Only Scan pur
```
### Résoudre le problème N+1
```sql
-- ❌ Antipattern N+1 en application (ORM classique)
-- for user in users:
-- orders = SELECT * FROM orders WHERE user_id = $1 -- N requêtes!
-- ✅ Solution 1: JOIN avec agrégation
SELECT
u.id,
u.name,
count(o.id) AS order_count,
coalesce(sum(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- ✅ Solution 2: ANY avec tableau d'IDs (batch loading)
SELECT * FROM orders
WHERE user_id = ANY($1::int[]);
-- Appeler une seule fois avec tous les user_ids
-- En Go: db.SelectContext(ctx, &orders, query, pq.Array(userIDs))
-- ✅ Solution 3: JSON aggregation pour nested objects
SELECT
u.id,
u.name,
json_agg(
json_build_object(
'id', o.id,
'amount', o.total_amount,
'status', o.status
) ORDER BY o.created_at DESC
) FILTER (WHERE o.id IS NOT NULL) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
```
### Pagination efficace
```sql
-- ❌ MAUVAIS: OFFSET devient de plus en plus lent avec la pagination profonde
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
-- → PostgreSQL doit parcourir et ignorer 100000 lignes
-- ✅ BON: Keyset pagination (seek method)
-- Page suivante: passer le dernier created_at et id vu
SELECT * FROM orders
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Index pour keyset pagination
CREATE INDEX idx_orders_pagination ON orders (created_at DESC, id DESC);
```
### Vues matérialisées
```sql
-- Précalculer des agrégations coûteuses
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT
u.id AS user_id,
u.name,
count(o.id) AS total_orders,
coalesce(sum(o.total_amount), 0) AS total_spent,
max(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
GROUP BY u.id, u.name;
-- Index sur la vue matérialisée
CREATE INDEX idx_mv_user_stats_user_id ON mv_user_stats (user_id);
CREATE INDEX idx_mv_user_stats_total_spent ON mv_user_stats (total_spent DESC);
-- Rafraîchir (sans verrouillage pour PG9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;
-- Planifier avec pg_cron ou un job applicatif
-- Vérifier la fraîcheur
SELECT
matviewname,
hasindexes,
ispopulated
FROM pg_matviews
WHERE matviewname = 'mv_user_stats';
```
### Window functions
```sql
-- Classement sans sous-requête
SELECT
id,
user_id,
total_amount,
created_at,
rank() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS rank_for_user,
sum(total_amount) OVER (PARTITION BY user_id) AS user_total,
lag(total_amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_amount
FROM orders
WHERE status = 'completed';
-- Optimiser avec un index sur PARTITION BY + ORDER BY
CREATE INDEX idx_orders_window ON orders (user_id, created_at) WHERE status = 'completed';
```
---
## Section 7 : Anti-patterns SQL courants
### N+1 (déjà couvert Section 6)
Détecter avec pg_stat_statements: `calls` très élevé + `mean_exec_time` très faible.
```sql
-- Signal d'alarme dans pg_stat_statements
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
WHERE calls > 100000 AND mean_exec_time < 1.0
ORDER BY calls DESC;
```
### FK sans index
```sql
-- PostgreSQL NE crée PAS automatiquement d'index sur les clés étrangères
-- Chaque DELETE sur la table parente scanne toutes les lignes de la table enfant
-- ❌ Problème silencieux
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- DELETE FROM users WHERE id = 42; → Seq Scan sur orders !
-- ✅ Toujours créer l'index immédiatement après la FK
CREATE INDEX idx_orders_user_id ON orders (user_id);
```
### LIKE avec wildcard en début
```sql
-- ❌ MAUVAIS: leading wildcard = Seq Scan toujours
SELECT * FROM users WHERE name LIKE '%dupont%';
SELECT * FROM users WHERE name ILIKE '%dupont'; -- Pire: insensible à la casse
-- ✅ Solution 1: pg_trgm pour LIKE et regex
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
-- Maintenant LIKE '%dupont%' utilise l'index
-- ✅ Solution 2: Full-text search pour la recherche sémantique
ALTER TABLE users ADD COLUMN name_tsv tsvector
GENERATED ALWAYS AS (to_tsvector('french', name)) STORED;
CREATE INDEX idx_users_name_fts ON users USING GIN (name_tsv);
SELECT * FROM users WHERE name_tsv @@ plainto_tsquery('french', 'dupont');
```
### DISTINCT inutile
```sql
-- ❌ MAUVAIS: DISTINCT masque souvent un problème de JOIN
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id; -- Produit des doublons = mauvaise jointure
-- ✅ BON: Utiliser EXISTS quand on cherche "les utilisateurs qui ont des commandes"
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Plus lisible ET plus efficace (s'arrête au premier résultat)
-- ✅ Ou GROUP BY si on veut les agréger
SELECT u.id, u.name, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
```
### Sous-requêtes corrélées
```sql
-- ❌ MAUVAIS: Sous-requête corrélée exécutée pour chaque ligne
SELECT
id,
name,
(SELECT count(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;
-- → Exécute la sous-requête N fois (une par utilisateur)
-- ✅ BON: JOIN avec agrégation
SELECT
u.id,
u.name,
count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- → Un seul passage sur les deux tables
```
### Index B-tree sur colonne faible cardinalité
```sql
-- ❌ Inutile: index B-tree sur booléen ou enum avec peu de valeurs
CREATE INDEX idx_users_is_active ON users (is_active); -- Seulement true/false
-- → PostgreSQL préfèrera souvent un Seq Scan (50% des lignes = pas sélectif)
-- ✅ Index partiel si une valeur est minoritaire
CREATE INDEX idx_users_is_active_true ON users (id) WHERE is_active = true;
-- Ou
CREATE INDEX idx_users_inactive ON users (created_at) WHERE is_active = false;
-- Utilisé uniquement pour les cas rares
```
### Transactions longues et dead rows
```sql
-- ❌ Transaction longue bloque le vacuum → accumulation de dead tuples
BEGIN;
-- ... traitement de 2 heures ...
COMMIT;
-- Surveiller les transactions longues
SELECT
pid,
now() - xact_start AS duration,
state,
left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;
-- Configurer un timeout sur les transactions idle
-- postgresql.conf
idle_in_transaction_session_timeout = '10min'
```
---
## Section 8 : Vacuum et autovacuum
PostgreSQL utilise MVCC (Multi-Version Concurrency Control) : les UPDATE et DELETE ne suppriment pas physiquement les lignes, ils créent des "dead tuples". VACUUM les nettoie.
### Comprendre le bloat
```sql
-- Vérifier le bloat d'une table
SELECT
tablename,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_pct,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_ratio_pct DESC NULLS LAST
LIMIT 20;
```
### Configuration autovacuum
```sql
-- postgresql.conf: paramètres globaux
autovacuum = on
autovacuum_max_workers = 5 -- Augmenter pour systèmes chargés (défaut: 3)
autovacuum_naptime = 30s -- Vérification toutes les 30s (défaut: 1min)
autovacuum_vacuum_scale_factor = 0.02 -- Déclenche sur 2% de dead tuples (défaut: 0.2 = 20%)
autovacuum_analyze_scale_factor = 0.01 -- Analyse sur 1% de changements (défaut: 0.1)
autovacuum_vacuum_cost_limit = 400 -- Plus agressif (défaut: 200)
autovacuum_vacuum_cost_delay = 2ms -- Moins de pauses (défaut: 2ms)
-- Pour les très grandes tables: le scale factor pose problème
-- 20% de 100M lignes = 20M dead tuples avant vacuum !
-- Utiliser autovacuum_vacuum_threshold + scale_factor par table:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- 1%
autovacuum_analyze_scale_factor = 0.005, -- 0.5%
autovacuum_vacuum_cost_limit = 800 -- Plus de ressources
);
```
### Commandes VACUUM manuelles
```sql
-- VACUUM standard: nettoie les dead tuples, ne rend pas l'espace à l'OS
VACUUM orders;
-- VACUUM ANALYZE: vacuum + mise à jour des statistiques du planner
VACUUM ANALYZE orders;
-- VACUUM VERBOSE: affiche les détails de l'opération
VACUUM (VERBOSE, ANALYZE) orders;
-- ⚠️ VACUUM FULL: compacte la table, rend l'espace à l'OS
-- MAIS: pose un verrou ACCESS EXCLUSIVE (table inaccessible en lecture/écriture !)
-- Utiliser uniquement pendant une fenêtre de maintenance planifiée
VACUUM FULL orders;
-- Alternative sans verrou: pg_repack (extension)
-- pg_repack -t orders # Réécrit la table sans downtime
```
### Surveiller autovacuum en cours
```sql
-- Voir les autovacuum en cours d'exécution
SELECT
pid,
now() - xact_start AS duration,
left(query, 100) AS query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;
```
### Risque de wraparound XID
```sql
-- ⚠️ Alerte critique: si le XID wraparound se produit, PostgreSQL se met en read-only !
-- Surveiller les tables qui approchent de la limite (2 milliards de transactions)
SELECT
schemaname,
tablename,
age(relfrozenxid) AS xid_age,
2000000000 - age(relfrozenxid) AS transactions_remaining
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY xid_age DESC
LIMIT 20;
-- Alerte si < 200M transactions restantes
-- Forcer un VACUUM FREEZE si nécessaire
VACUUM FREEZE orders; -- Met à jour relfrozenxid
```
---
## Section 9 : Lock debugging
Les verrous (locks) sont une cause fréquente de dégradation soudaine des performances.
### Requête canonique : qui bloque qui ?
```sql
-- Vue complète des blocages (query classique, à bookmarker)
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
left(blocked_activity.query, 80) AS blocked_query,
now() - blocked_activity.xact_start AS blocked_duration,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
left(blocking_activity.query, 80) AS blocking_query,
now() - blocking_activity.xact_start AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
```
```sql
-- Vue simplifiée: verrous non accordés
SELECT
pid,
locktype,
relation::regclass AS table_name,
mode,
granted,
left(query, 100) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT granted
ORDER BY pid;
```
### Configuration du logging des locks
```sql
-- postgresql.conf
log_lock_waits = on -- Logger les attentes de verrous
deadlock_timeout = 1s -- Délai avant détection de deadlock (défaut: 1s)
lock_timeout = '30s' -- Lever une erreur si attente > 30s
statement_timeout = '60s' -- Annuler les requêtes > 60s
```
### Tableau des types de verrous PostgreSQL
| Mode | Compatible avec | Incompatible avec | Opérations |
|------|----------------|-------------------|------------|
| `ACCESS SHARE` | Presque tout | ACCESS EXCLUSIVE | SELECT |
| `ROW SHARE` | La plupart | EXCLUSIVE, ACCESS EXCLUSIVE | SELECT FOR UPDATE |
| `ROW EXCLUSIVE` | Partiel | SHARE, ACCESS EXCLUSIVE | INSERT, UPDATE, DELETE |
| `SHARE UPDATE EXCLUSIVE` | ACCESS SHARE, ROW SHARE | Partiel | VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY |
| `SHARE` | ACCESS SHARE, SHARE | ROW EXCLUSIVE+ | CREATE INDEX (sans CONCURRENTLY) |
| `SHARE ROW EXCLUSIVE` | ACCESS SHARE | SHARE+ | CREATE TRIGGER |
| `EXCLUSIVE` | ACCESS SHARE | ROW SHARE+ | Rare en usage direct |
| `ACCESS EXCLUSIVE` | Rien | Tout | ALTER TABLE, DROP TABLE, VACUUM FULL, TRUNCATE |
### DDL sans downtime
```sql
-- ❌ MAUVAIS: Pose un verrou ACCESS EXCLUSIVE pendant toute la construction
CREATE INDEX idx_orders_created ON orders (created_at);
-- → Bloque toutes les lectures et écritures !
-- ✅ BON: CONCURRENTLY ne bloque que brièvement pour les métadonnées
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);
-- → Les lectures/écritures continuent pendant la construction
-- ⚠️ Prend plus de temps, peut échouer (INVALID) si conflit → DROP et recommencer
-- ❌ MAUVAIS: ADD COLUMN NOT NULL avec DEFAULT recalcule toutes les lignes
ALTER TABLE orders ADD COLUMN processed boolean NOT NULL DEFAULT false;
-- PG10 et antérieur: réécrit toute la table!
-- ✅ BON pour PG11+: ADD COLUMN NOT NULL DEFAULT est instantané (métadonnée)
-- Pour PG10 et antérieur: séparer en étapes
ALTER TABLE orders ADD COLUMN processed boolean; -- Step 1: ajout nullable
ALTER TABLE orders ALTER COLUMN processed SET DEFAULT false; -- Step 2: défaut pour nouveaux
UPDATE orders SET processed = false WHERE processed IS NULL; -- Step 3: backfill par batch
ALTER TABLE orders ALTER COLUMN processed SET NOT NULL; -- Step 4: contrainte
```
### Timeout de sécurité pour les migrations
```sql
-- Utiliser lock_timeout pour éviter les migrations qui bloquent trop longtemps
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN processed boolean;
-- Si le verrou n'est pas obtenu en 5s → erreur, pas de blocage prolongé
RESET lock_timeout;
```
---
## Section 10 : Connection pooling (PgBouncer)
PostgreSQL consomme ~5-10MB RAM par connexion et un coût CPU non négligeable pour chaque établissement de connexion. PgBouncer mutualise les connexions.
### Modes de fonctionnement
| Mode | Comportement | Usage |
|------|-------------|-------|
| `session` | 1 connexion PgBouncer = 1 connexion PostgreSQL pour toute la session | Compatibilité maximale |
| `transaction` | La connexion PostgreSQL est libérée après chaque transaction | **Recommandé pour web apps** |
| `statement` | Libérée après chaque statement | Incompatible avec les transactions multi-statements |
### Configuration recommandée (pgbouncer.ini)
```ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
pool_mode = transaction ; Mode recommandé pour web
max_client_conn = 1000 ; Connexions clients acceptées
default_pool_size = 20 ; Connexions PostgreSQL par db/user
min_pool_size = 5 ; Maintenir au minimum
reserve_pool_size = 5 ; Pool de secours
reserve_pool_timeout = 3 ; Délai avant utiliser le pool de secours
server_idle_timeout = 600 ; Fermer connexion inactive > 10min
client_idle_timeout = 0 ; Ne pas fermer les clients inactifs
server_connect_timeout = 15 ; Timeout connexion à PostgreSQL
server_login_retry = 15 ; Délai en cas d'échec de login
; Authentification
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Logging
log_connections = 0 ; Trop verbeux en production
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60
```
### Dimensionnement
```
Règle de base: default_pool_size ≈ 1.5 × nombre de vCPU
Exemple: Serveur 8 vCPU
→ default_pool_size = 12
→ max_client_conn peut aller jusqu'à 500-1000
→ PostgreSQL reçoit max 12 connexions (workload efficace)
Formule plus précise:
default_pool_size = (nb_vCPU × temps_moyen_query_ms) / sla_latence_ms
```
### Surveiller PgBouncer
```bash
# Se connecter à la console d'administration PgBouncer
psql -h localhost -p 6432 -U pgbouncer pgbouncer
# Voir les pools
SHOW POOLS;
# Voir les connexions clients
SHOW CLIENTS;
# Voir les serveurs (connexions PostgreSQL)
SHOW SERVERS;
# Statistiques de performance
SHOW STATS;
# Voir la configuration
SHOW CONFIG;
```
### Caveats du mode transaction
```sql
-- ❌ NE FONCTIONNE PAS en mode transaction PgBouncer:
-- SET: la connexion peut changer entre les transactions
SET work_mem = '256MB'; -- Peut ne pas s'appliquer à la transaction suivante
-- → Utiliser SET LOCAL dans la transaction ou configurer dans postgresql.conf
-- LISTEN/NOTIFY: nécessite une connexion persistante
LISTEN events; -- Incompatible avec mode transaction
-- Prepared statements côté serveur
PREPARE my_query AS SELECT * FROM users WHERE id = $1;
-- → Utiliser prepared statements côté applicatif (driver), pas côté serveur
-- Transactions sans BEGIN explicite côté PgBouncer
-- → S'assurer que l'application utilise des transactions explicites
-- ✅ Solution: configurer server_reset_query pour nettoyer l'état
server_reset_query = DISCARD ALL
```
---
## Section 11 : Configuration tuning (mémoire, WAL, planner)
### Mémoire
```sql
-- shared_buffers: cache partagé entre toutes les connexions
-- Défaut: 128MB (catastrophiquement bas en production)
-- Recommandé: 25% de la RAM totale
shared_buffers = 4GB -- Sur un serveur 16GB RAM
-- effective_cache_size: estimation du cache OS + PostgreSQL (utilisée par le planner)
-- NE réserve pas de mémoire, seulement une indication au planner
-- Recommandé: 70-75% de la RAM
effective_cache_size = 11GB -- Sur un serveur 16GB RAM
-- work_mem: mémoire par opération de tri/hash PAR CONNEXION
-- ⚠️ Peut être multiplié par: nb_connexions × opérations_simultanées
-- Recommandé: 16-64MB pour OLTP, 256MB+ pour analytics
work_mem = 32MB
-- Augmenter pour une session spécifique (analytics ponctuelles)
SET work_mem = '256MB';
-- Toujours avec statement_timeout pour sécurité
SET statement_timeout = '5min';
-- maintenance_work_mem: pour VACUUM, CREATE INDEX, ALTER TABLE
-- Recommandé: 256MB-1GB
maintenance_work_mem = 512MB
-- Vérifier l'utilisation mémoire actuelle
SELECT
sum(blks_hit) AS cache_hits,
sum(blks_read) AS disk_reads,
round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;
```
### WAL (Write-Ahead Log)
```sql
-- wal_buffers: buffer pour l'écriture du WAL
-- Recommandé: 16MB (auto si shared_buffers >= 2GB: min(1/32 de shared_buffers, 64MB))
wal_buffers = 16MB
-- checkpoint_timeout: fréquence maximale des checkpoints
-- Recommandé: 10-30min (défaut: 5min trop agressif)
checkpoint_timeout = 15min
-- max_wal_size: taille maximale du WAL avant checkpoint forcé
-- Recommandé: 2-8GB selon la charge d'écriture
max_wal_size = 4GB
-- checkpoint_completion_target: étaler l'écriture du checkpoint
-- Recommandé: 0.9 (écrire pendant 90% de l'intervalle)
checkpoint_completion_target = 0.9
-- Surveiller les checkpoints
-- postgresql.conf: log_checkpoints = on
-- Dans les logs, si "checkpoint occurring too frequently" → augmenter max_wal_size
SELECT
checkpoints_timed,
checkpoints_req, -- Checkpoints forcés (mauvais si > timed)
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
buffers_backend -- Écriture directe par backends (mauvais signe)
FROM pg_stat_bgwriter;
```
### Planner
```sql
-- random_page_cost: coût estimé d'une lecture aléatoire
-- Défaut: 4.0 (pour HDD)
-- SSD: 1.1 (lecture aléatoire quasi aussi rapide que séquentielle)
random_page_cost = 1.1 -- ← CRITIQUE pour les serveurs SSD
-- seq_page_cost: coût d'une lecture séquentielle (base de référence)
seq_page_cost = 1.0 -- Ne pas modifier
-- effective_io_concurrency: parallélisme I/O pour les Bitmap Heap Scans
-- SSD: 200, NVMe: 500+
effective_io_concurrency = 200
-- Parallélisme
max_parallel_workers = 8 -- = nombre de vCPU
max_parallel_workers_per_gather = 4 -- Workers par requête parallèle
min_parallel_table_scan_size = 8MB -- Seuil pour activer le parallélisme
-- Statistiques planner: augmenter pour les colonnes corrélées
-- Défaut: 100 buckets d'histogramme
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;
```
### Timeouts de sécurité
```sql
-- postgresql.conf: timeouts de sécurité (défenses en profondeur)
statement_timeout = '60s' -- Annuler les requêtes > 60s
lock_timeout = '30s' -- Erreur si attente verrou > 30s
idle_in_transaction_session_timeout = '10min' -- Fermer les transactions idle
tcp_keepalives_idle = 60 -- Détecter les connexions mortes
tcp_keepalives_interval = 10
tcp_keepalives_count = 6
-- Overrider par rôle (pratique pour les migrations)
ALTER ROLE migration_user SET statement_timeout = '0'; -- Pas de timeout
ALTER ROLE app_user SET statement_timeout = '30s'; -- 30s pour l'app
ALTER ROLE read_only_user SET default_transaction_read_only = on;
```
### Logging utile pour le diagnostic
```sql
-- postgresql.conf: logging recommandé pour la production
log_min_duration_statement = 1000 -- Log toute requête > 1s
log_checkpoints = on -- Log les checkpoints
log_lock_waits = on -- Log les attentes de verrous
log_temp_files = 0 -- Log tous les fichiers temporaires
log_autovacuum_min_duration = 250ms -- Log autovacuum > 250ms
-- Format des logs
log_line_prefix = '%m [%p] %q%u@%d '
-- %m = timestamp, %p = PID, %q = quiet (vide si pas dans une requête), %u = user, %d = db
```
---
## Section 12 : Partitioning — stratégies et implémentation
Le partitionnement divise une grande table en plusieurs tables physiques (partitions) tout en les exposant comme une table logique unique.
### Quand partitionner ?
- Table > 100M lignes avec des requêtes ciblant toujours une plage temporelle
- Besoin de supprimer massivement des données historiques (DROP PARTITION instantané)
- Isolation des données par tenant, région, ou catégorie
### Partitionnement par range (time-series)
```sql
-- Créer la table partitionnée
CREATE TABLE events (
id bigserial,
user_id int NOT NULL,
event_type varchar(50) NOT NULL,
payload jsonb,
created_at timestamptz NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, created_at) -- created_at doit être dans la PK pour le routing
) PARTITION BY RANGE (created_at);
-- Créer les partitions mensuelles
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Partition par défaut (reçoit les lignes hors plage)
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Index sur chaque partition (automatiquement propagé si défini sur la table parente PG11+)
CREATE INDEX ON events (user_id, created_at DESC);
-- Automatiser la création des partitions (avec pg_partman ou manuellement)
```
```sql
-- Vérifier que le partition pruning fonctionne
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01';
-- → Doit montrer "Partition Filters" et ne scanne que events_2024_01
-- Supprimer les données historiques instantanément (vs DELETE qui prend des heures)
DROP TABLE events_2023_01; -- Instantané !
-- Ou détacher sans supprimer
ALTER TABLE events DETACH PARTITION events_2023_01;
```
### Partitionnement par hash (distribution uniforme)
```sql
-- Utile pour distribuer uniformément sans critère temporel
CREATE TABLE users (
id bigserial PRIMARY KEY,
email varchar(255) NOT NULL,
name varchar(100)
) PARTITION BY HASH (id);
-- Créer N partitions (choisir une puissance de 2 pour faciliter les ajouts futurs)
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);
```
### Partitionnement par list (catégories)
```sql
-- Partitionner par région géographique
CREATE TABLE orders (
id bigserial,
region varchar(10) NOT NULL,
amount numeric(10,2),
PRIMARY KEY (id, region)
) PARTITION BY LIST (region);
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('FR', 'DE', 'ES', 'IT', 'UK');
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('JP', 'KR', 'SG', 'AU');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;
```
### Gestion des partitions
```sql
-- Lister toutes les partitions d'une table
SELECT
parent.relname AS parent_table,
child.relname AS partition_name,
pg_get_expr(child.relpartbound, child.oid) AS partition_bound,
pg_size_pretty(pg_total_relation_size(child.oid)) AS size
FROM pg_inherits i
JOIN pg_class parent ON parent.oid = i.inhparent
JOIN pg_class child ON child.oid = i.inhrelid
WHERE parent.relname = 'events'
ORDER BY child.relname;
-- Attacher une table existante comme partition
ALTER TABLE events ATTACH PARTITION events_2024_03
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- Détacher une partition (devient une table normale)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY; -- PG14+
```
---
## Section 13 : Monitoring — requêtes de diagnostic
### Ratio de cache hit (objectif : > 95%)
```sql
-- Ratio global par base de données
SELECT
datname,
blks_hit,
blks_read,
round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
tup_returned,
tup_fetched,
conflicts
FROM pg_stat_database
WHERE datname = current_database();
-- Ratio par table
SELECT
schemaname,
tablename,
heap_blks_hit,
heap_blks_read,
round(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS table_cache_hit_pct,
idx_blks_hit,
idx_blks_read,
round(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2) AS index_cache_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 20;
```
### Taille des tables et indexes
```sql
-- Top 20 tables les plus volumineuses
SELECT
schemaname,
tablename,
pg_size_pretty(pg_table_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup AS live_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
```
### Bloat des tables et indexes
```sql
-- Estimation du bloat (approximation, pas exacte)
SELECT
schemaname,
tablename,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS bloat_pct,
pg_size_pretty(pg_table_size(relid)) AS table_size,
last_vacuum::date,
last_autovacuum::date
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY bloat_pct DESC NULLS LAST
LIMIT 20;
```
### Utilisation des connexions
```sql
-- État des connexions
SELECT
state,
wait_event_type,
wait_event,
count(*) AS connection_count
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY connection_count DESC;
-- Utilisation par rapport au maximum configuré
SELECT
max_conn,
used_conn,
round(100.0 * used_conn / max_conn, 1) AS usage_pct,
(max_conn - used_conn) AS available
FROM
(SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc,
(SELECT count(*) AS used_conn FROM pg_stat_activity WHERE state != 'idle') uc;
```
### Santé des checkpoints
```sql
SELECT
checkpoints_timed,
checkpoints_req AS forced_checkpoints,
round(100.0 * checkpoints_req /
NULLIF(checkpoints_timed + checkpoints_req, 0), 2) AS forced_pct,
round(checkpoint_write_time / 1000.0 / 60, 2) AS write_time_minutes,
round(checkpoint_sync_time / 1000.0 / 60, 2) AS sync_time_minutes,
buffers_checkpoint,
buffers_clean,
buffers_backend,
-- buffers_backend > 0 indique que les checkpoints arrivent trop tard
CASE WHEN buffers_backend > 0
THEN 'ATTENTION: Écriture directe backend détectée'
ELSE 'OK'
END AS backend_writes_status
FROM pg_stat_bgwriter;
```
### Statistiques d'index complètes
```sql
-- Vue complète des performances d'index
SELECT
t.schemaname,
t.tablename,
i.indexname,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
CASE
WHEN i.idx_scan = 0 THEN 'JAMAIS UTILISÉ - candidat à la suppression'
WHEN i.idx_scan < 100 THEN 'PEU UTILISÉ - surveiller'
ELSE 'ACTIF'
END AS status
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables t ON i.relid = t.relid
ORDER BY i.idx_scan ASC, pg_relation_size(i.indexrelid) DESC;
```
### Requêtes longues en cours
```sql
-- Toutes les requêtes actives depuis plus de 5 secondes
SELECT
pid,
usename,
application_name,
client_addr,
now() - query_start AS duration,
state,
wait_event_type,
wait_event,
left(query, 200) AS query
FROM pg_stat_activity
WHERE state = 'active'
AND query_start < NOW() - INTERVAL '5 seconds'
AND query NOT LIKE '%pg_stat_activity%' -- Exclure cette requête
ORDER BY duration DESC;
```
---
## Section 14 : Benchmarking et tests de performance
### Préparer un test propre
```sql
-- 1. S'assurer que le cache est chaud (représentatif de la production)
-- Exécuter la requête 2-3 fois avant de mesurer
SELECT * FROM orders WHERE user_id = 42; -- Run 1: froid
SELECT * FROM orders WHERE user_id = 42; -- Run 2: cache chaud
SELECT * FROM orders WHERE user_id = 42; -- Run 3: mesurer celui-ci
-- 2. Réinitialiser pg_stat_statements avant le test
SELECT pg_stat_statements_reset();
-- 3. Désactiver JIT pour des mesures plus stables (optionnel)
SET jit = off;
```
### Mesurer précisément avec EXPLAIN ANALYZE
```sql
-- Utiliser plusieurs runs et prendre la médiane
DO $$
DECLARE
i int;
start_time timestamptz;
durations numeric[] := '{}';
BEGIN
FOR i IN 1..10 LOOP
start_time := clock_timestamp();
PERFORM count(*) FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
durations := durations || EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time)::numeric;
END LOOP;
RAISE NOTICE 'Min: %ms, Max: %ms, Avg: %ms',
(SELECT min(d) FROM unnest(durations) d),
(SELECT max(d) FROM unnest(durations) d),
round((SELECT avg(d) FROM unnest(durations) d), 2);
END;
$$;
```
### pgbench pour les tests de charge
```bash
# Initialiser une base de test avec 10M lignes (scale factor 100)
pgbench -i -s 100 myapp_test
# Test de base: 50 clients, 4 threads, pendant 60 secondes
pgbench -c 50 -j 4 -T 60 myapp_test
# Test avec script custom
cat > custom_test.sql << 'EOF'
\set user_id random(1, 100000)
SELECT id, total_amount FROM orders WHERE user_id = :user_id ORDER BY created_at DESC LIMIT 10;
EOF
pgbench -c 50 -j 4 -T 60 -f custom_test.sql myapp_test
# Test en read-only (plus réaliste pour une app web)
pgbench -c 100 -j 8 -T 120 -S myapp_test
```
### Workflow de comparaison avant/après
```sql
-- ÉTAPE 1: Baseline - capturer les métriques initiales
SELECT pg_stat_statements_reset();
-- Exécuter le workload typique pendant 5-10 minutes
-- ÉTAPE 2: Capturer les résultats baseline
SELECT
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 2) AS total_ms,
calls,
left(query, 100) AS query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;
-- ÉTAPE 3: Appliquer le changement
CREATE INDEX CONCURRENTLY idx_orders_status_date ON orders (status, created_at DESC);
-- ÉTAPE 4: Reset et re-mesurer
SELECT pg_stat_statements_reset();
-- Exécuter le même workload
-- ÉTAPE 5: Comparer
SELECT
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 2) AS total_ms,
calls,
left(query, 100) AS query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;
```
### Tests de régression de performance
```sql
-- Créer une table de référence pour suivre les performances dans le temps
CREATE TABLE perf_baselines (
id serial PRIMARY KEY,
query_tag varchar(100) NOT NULL,
measured_at timestamptz DEFAULT NOW(),
mean_exec_ms numeric,
p95_exec_ms numeric,
calls_per_min numeric,
notes text
);
-- Insérer une mesure baseline
INSERT INTO perf_baselines (query_tag, mean_exec_ms, calls_per_min, notes)
SELECT
'get_user_orders',
round(mean_exec_time::numeric, 2),
round(calls / (EXTRACT(EPOCH FROM (NOW() - stats_since)) / 60), 2),
'Before index idx_orders_user_date'
FROM pg_stat_statements, pg_stat_database
WHERE pg_stat_statements.query LIKE '%orders WHERE user_id%'
AND pg_stat_database.datname = current_database()
LIMIT 1;
```
---
## Section 15 : Checklist de debug complète
### Triage immédiat (< 5 minutes)
```sql
-- 1. Identifier les requêtes qui consomment le plus de temps en ce moment
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
calls,
left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 2. Y a-t-il des blocages actifs ?
SELECT count(*) FROM pg_locks WHERE NOT granted;
-- 3. Y a-t-il des requêtes qui durent depuis longtemps ?
SELECT pid, now() - query_start AS duration, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC NULLS LAST
LIMIT 10;
-- 4. Quel est le ratio de cache hit ?
SELECT round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database WHERE datname = current_database();
-- 5. Y a-t-il des connexions qui saturent le max ?
SELECT count(*) AS current, setting::int AS max_connections,
round(100.0 * count(*) / setting::int, 1) AS usage_pct
FROM pg_stat_activity, pg_settings
WHERE pg_settings.name = 'max_connections'
GROUP BY setting;
```
### Investigation approfondie (5-30 minutes)
```sql
-- 6. Analyser la requête la plus lente avec EXPLAIN ANALYZE
-- (remplacer par la requête identifiée à l'étape 1)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...;
-- → Chercher: Seq Scan sur grande table, rows off par 10x, Sort external merge,
-- Hash Batches > 1, shared read élevé, Rows Removed by Filter élevé
-- 7. Vérifier les statistiques de la table concernée
SELECT
tablename,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'orders';
-- Si last_analyze NULL ou > 1 semaine: ANALYZE orders;
-- 8. Vérifier les index existants sur la table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- 9. Vérifier les FK sans index
SELECT
kcu.column_name,
ccu.table_name AS references_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'orders';
-- → Pour chaque FK, vérifier qu'un index existe sur orders.fk_column
-- 10. Vérifier le bloat de la table
SELECT
n_dead_tup,
n_live_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE tablename = 'orders';
-- Si dead_pct > 20%: VACUUM ANALYZE orders;
```
### Vérifications configuration
```sql
-- 11. random_page_cost correctement configuré pour SSD ?
SHOW random_page_cost;
-- SSD: doit être 1.1, pas 4.0 (défaut pour HDD)
-- 12. work_mem suffisant ?
SHOW work_mem;
-- Vérifier temp_blks_written dans pg_stat_statements: si > 0 → augmenter work_mem
-- 13. shared_buffers suffisant ?
SHOW shared_buffers;
-- Doit être ~25% de la RAM. Vérifier cache_hit_pct (Section 13)
-- 14. Autovacuum actif et bien configuré ?
SHOW autovacuum;
SHOW autovacuum_vacuum_scale_factor;
-- Si autovacuum_vacuum_scale_factor = 0.2 (20%) → probablement trop haut pour grandes tables
-- 15. Timeouts configurés ?
SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;
-- Doivent être > 0 en production pour éviter les requêtes runaway
```
### Avant chaque déploiement (pre-deploy checklist)
```sql
-- 16. Vérifier qu'aucun EXPLAIN ne montre un Seq Scan sur > 1M lignes
-- pour les requêtes de l'endpoint déployé
-- 17. Vérifier que les migrations utilisent CONCURRENTLY et lock_timeout
-- CREATE INDEX CONCURRENTLY ...
-- SET lock_timeout = '5s'; ALTER TABLE ...
-- 18. Vérifier les nouvelles FK ont un index associé
-- Pour chaque FOREIGN KEY dans la migration → CREATE INDEX CONCURRENTLY correspondant
-- 19. Vérifier que les nouvelles requêtes n'ont pas d'implicit type cast
-- WHERE user_id = $1 → $1 doit être integer, pas string
-- 20. Benchmarker en staging avec données proches de la production
-- pgbench ou test de charge applicatif
-- 21. Avoir un plan de rollback documenté
-- Indexes peuvent être droppés rapidement
-- DROP INDEX CONCURRENTLY idx_name; -- Si l'index cause des problèmes
```
### Actions correctives rapides
```sql
-- Problème: Seq Scan sur grande table → Créer l'index manquant
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status)
WHERE status IN ('pending', 'processing');
-- Problème: Statistiques obsolètes → Mettre à jour
ANALYZE orders;
-- Augmenter la résolution pour les colonnes à faible cardinalité
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders (status);
-- Problème: Bloat excessif → Nettoyer
VACUUM ANALYZE orders;
-- Si bloat > 50% et fenêtre de maintenance disponible:
VACUUM FULL orders; -- ⚠️ Verrou exclusif !
-- Problème: work_mem insuffisant pour les sorts → Augmenter par session
SET work_mem = '256MB';
-- Puis exécuter la requête concernée
-- Problème: Cache miss élevé → Vérifier shared_buffers et la taille des données
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
SHOW shared_buffers;
-- Si db_size >> shared_buffers × 8 → le cache ne peut pas tout contenir, c'est normal
-- Problème: Connexions saturées → Vérifier PgBouncer et les connexions idle
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- Tuer les connexions idle in transaction depuis longtemps
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start < NOW() - INTERVAL '10 minutes';
```
---
> Ce fichier couvre les patterns les plus fréquents de diagnostic et d'optimisation PostgreSQL. Pour les cas avancés (réplication, sharding, TimescaleDB), consulter la documentation officielle de PostgreSQL et les guides spécialisés.
---
*Last updated: 2025-03 — Revoir si : PostgreSQL 17+ (nouveaux paramètres ou stats views), ou changements majeurs du planner/optimiseur.*