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 :

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

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

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;