# 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.*