postgres-expert

PostgreSQL Expert

Workflow

1. Recueillir le contexte obligatoire

Avant toute recommandation, collecter :

SELECT version();                         -- version exacte (ex: 16.3)
SHOW shared_buffers;                      -- mémoire allouée
SHOW work_mem;
SELECT pg_size_pretty(pg_database_size(current_database()));

• Workload : OLTP (latence < 5 ms), OLAP (scan massif), mixte ?
• RAM totale du serveur, SSD ou HDD, réplication active ?

2. Diagnostiquer les performances

-- Top requêtes lentes (nécessite pg_stat_statements activé)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 20;

-- Tables avec beaucoup de dead tuples (candidat VACUUM)
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup,0)*100,1) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Requête lente : toujours EXPLAIN complet
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <votre requête>;

Critères d'action :

Signal	Seuil	Action
mean_exec_time	> 100 ms	Analyser plan + index
bloat_pct	> 20 %	VACUUM FULL ou pg_repack
cache_hit_ratio	< 99 %	Augmenter shared_buffers
seq_scan élevé	> 1 k/min sur grande table	Créer un index

-- Ratio de cache hits (doit être > 99 %)
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read),0) AS cache_hit_ratio
FROM pg_statio_user_tables;

3. Indexation — choisir le bon type

Type	Cas d'usage
B-tree (défaut)	=, <, >, BETWEEN, LIKE 'abc%'
GIN	JSONB, tableaux, full-text (tsvector)
GiST	Géométrie, plages (tsrange)
BRIN	Colonnes ordonnées naturellement (logs, séries temporelles) — très compact
Hash	= uniquement, rarement utile vs B-tree

-- Index couvrant (évite un heap fetch)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status) INCLUDE (created_at, total);

-- Index partiel (réduit taille, cible les lignes actives)
CREATE INDEX CONCURRENTLY idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Index GIN sur JSONB
CREATE INDEX CONCURRENTLY idx_events_payload ON events USING GIN (payload jsonb_path_ops);

Toujours utiliser CONCURRENTLY en production (pas de verrou table).