postgres-expert

name	postgres-expert
description	Administration et optimisation PostgreSQL — diagnostic de performance, indexation, partitioning, tuning mémoire, VACUUM, backup/restore, JSONB, réplication. Se déclenche avec "PostgreSQL", "Postgres", "pg_stat", "JSONB", "partitioning", "VACUUM", "pg_dump"

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).

4. Partitioning

Disponible nativement depuis PG 10 (partitioning déclaratif).

-- Partition par range sur une date (OLAP, logs, IoT)
CREATE TABLE events (
    id BIGSERIAL,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2025 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Activer partition pruning (défaut ON depuis PG 11)
SET enable_partition_pruning = on;

Critères de choix :

• RANGE : dates, IDs croissants → purge par DROP TABLE partition O(1)
• LIST : régions, statuts finis (< 50 valeurs)
• HASH : distribution uniforme sans sémantique de plage

5. Tuning mémoire (postgresql.conf)

Formules de référence pour un serveur dédié PostgreSQL :

shared_buffers = 25% RAM          # ex: 8 GB sur 32 GB
effective_cache_size = 75% RAM    # indice pour le planner
work_mem = RAM / (max_connections * 4)  # max 256 MB ; attention aux sorts parallèles
maintenance_work_mem = 1–4 GB     # VACUUM, CREATE INDEX
wal_buffers = 64 MB               # ou -1 (auto)
max_wal_size = 2 GB               # réduit fréquence checkpoints
checkpoint_completion_target = 0.9

# Parallélisme (PG 9.6+)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Appliquer via ALTER SYSTEM SET param = val; + SELECT pg_reload_conf(); (sans redémarrage pour la plupart).

6. VACUUM et autovacuum

-- Forcer un VACUUM ANALYZE sur une table critique
VACUUM (ANALYZE, VERBOSE) ma_table;

-- VACUUM FULL (bloquant ! utiliser pg_repack en production)
-- pg_repack -t ma_table -d mabase

-- Ajuster autovacuum par table (évite de le désactiver globalement)
ALTER TABLE ma_table SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- déclencher à 1 % de dead tuples
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2         -- ms, réduire l'impact I/O
);

7. Backup et restore

# Dump logique (texte / format custom)
pg_dump -Fc -Z 5 -d mabase -f mabase_$(date +%Y%m%d).dump

# Restore
pg_restore -d mabase_restore --no-owner --no-acl mabase_20260624.dump

# Backup physique (streaming, recommandé production)
pg_basebackup -h localhost -U replicator -D /mnt/backup/base -Ft -z -Xs -P

# Point-in-time recovery : activer WAL archivage
archive_mode = on
archive_command = 'cp %p /mnt/wal_archive/%f'

Stratégie recommandée : pgBackRest pour la gestion des WAL, rétention et restore PITR automatisés.

8. JSONB — bonnes pratiques 2026

-- Extraire une valeur
SELECT payload->>'user_id', payload#>>'{address,city}' FROM events;

-- Filtrer avec index GIN (jsonb_path_ops plus compact)
SELECT * FROM events WHERE payload @> '{"status": "active"}';

-- JSON Path (PG 12+)
SELECT * FROM events WHERE payload @? '$.items[*] ? (@.price > 100)';

-- Agréger du JSONB
SELECT jsonb_agg(payload) FROM events WHERE occurred_at > now() - interval '1 day';

---

Garde-fous et anti-patterns

Anti-pattern	Risque	Alternative
SELECT * sur grande table	Scan complet + overhead réseau	Sélectionner les colonnes nécessaires
Index sur colonne de faible cardinalité (boolean)	Index inutilisé, overhead write	Index partiel ou pas d'index
VACUUM FULL en production sans pg_repack	Lock exclusif, downtime	pg_repack (online)
Désactiver autovacuum globalement	Transaction ID wraparound (DB forcée read-only)	Ajuster autovacuum_* par table
work_mem trop élevé × max_connections	OOM	Calculer par opération, pas par connexion
EXPLAIN sans ANALYZE	Plan estimé, pas réel	EXPLAIN (ANALYZE, BUFFERS) toujours
Connexions persistantes non poolées	Overhead process, verrous	PgBouncer en mode transaction pooling
Index sans CONCURRENTLY en production	Lock table bloquant les écritures	Toujours CREATE INDEX CONCURRENTLY
Ignorer pg_stat_replication en réplication	Lag non détecté	Monitorer write_lag, flush_lag, replay_lag

Checklist opérationnelle rapide

-- Taille des tables et indexes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table_only
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

-- Locks actifs suspects
SELECT pid, wait_event_type, wait_event, state, left(query,80)
FROM pg_stat_activity WHERE wait_event IS NOT NULL AND state != 'idle';

-- Vérifier le lag de réplication
SELECT client_addr, write_lag, flush_lag, replay_lag FROM pg_stat_replication;