🐘 7) Recettes par profil
Pour chaque recette : onglet 1 = paramètres clés, onglet 2 = snippet postgresql.conf, onglet 3 = opérations/notes. Comme ça la modale reste lisible.
OLTP (latence & concurrence)
Pooler obligatoire, work_mem modéré, checkpoints 15–20 min, JIT off.
OLAP (débit & scans)
Peu de connexions mais très lourdes, parallélisme 2–4, work_mem haut.
Mixte
Deux pools PgBouncer, buffers moyens, WAL moyen, JIT ciblé.
Profils spécialisés
Temps réel, ingestion IoT, ETL batch, réplication/HA.
Objectif : tenir sous 1–3 s même avec bcp de connexions. On protège le serveur en limitant max_connections et en déportant sur PgBouncer.
| Paramètre | Valeur OLTP |
|---|---|
| max_connections | 150–200 (le reste dans PgBouncer) |
| shared_buffers | 25% RAM PG (plafond 8–16 Go) |
| effective_cache_size | 75–80% RAM PG |
| work_mem | 4–16 MB |
| maintenance_work_mem | 512 MB – 1 GB |
| synchronous_commit | off (ou local) si SLA OK |
| max_wal_size | 8–16 GB |
| checkpoint_timeout | 15–20 min |
| checkpoint_completion_target | 0.9 |
| random_page_cost | 1.1 (SSD) |
| effective_io_concurrency | 64–128 |
| jit | off |
| statement_timeout | 1000–3000 ms |
# --- OLTP preset --- max_connections = 200 shared_buffers = 8GB effective_cache_size = 24GB work_mem = 8MB maintenance_work_mem = 1GB synchronous_commit = off max_wal_size = 16GB checkpoint_timeout = 20min checkpoint_completion_target = 0.9 random_page_cost = 1.1 effective_io_concurrency = 64 jit = off statement_timeout = 2000
À adapter selon RAM réelle. Conserver la logique : peu de work_mem mais très réactif.
Autovacuum agressif
ALTER TABLE public.orders SET ( autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000 );
Checklist OLTP
- ☐ PgBouncer en mode transaction
- ☐ log_min_duration_statement = 200–500 ms
- ☐ pg_stat_statements activé
- ☐ Taux de HIT ≥ 99%
- ☐ Moniteur sur nb de connexions actives
Profil analytique : peu de sessions, chaque requête consomme beaucoup. Il faut autoriser de gros work_mem et activer le parallélisme.
| Paramètre | Valeur OLAP |
|---|---|
| max_connections | 20–50 |
| shared_buffers | 15–20% RAM_PG |
| effective_cache_size | 80% RAM |
| work_mem | 64–512 MB (par session) |
| maintenance_work_mem | 1–2 GB |
| synchronous_commit | on |
| wal_level | replica |
| max_wal_size | 16–64 GB |
| max_parallel_workers_per_gather | 2–4 |
| max_parallel_workers | 8–16 |
| jit | on (seuils hauts) |
| random_page_cost | 1.1 |
| effective_io_concurrency | 128 |
# --- OLAP preset --- max_connections = 40 shared_buffers = 6GB effective_cache_size = 24GB work_mem = 256MB maintenance_work_mem = 2GB synchronous_commit = on wal_level = replica max_wal_size = 32GB max_parallel_workers_per_gather = 3 max_parallel_workers = 12 jit = on jit_above_cost = 100000 jit_inline_above_cost = 200000 jit_optimize_above_cost = 500000 random_page_cost = 1.1 effective_io_concurrency = 128
Tips OLAP
- Limiter le nombre de sessions BI en même temps.
- Rejouer EXPLAIN (ANALYZE, BUFFERS) sur les requêtes > 2–3 s.
- Surveiller temp files ; si trop gros → augmenter work_mem.
- Partitionner les tables fact et utiliser BRIN.
On garde des valeurs médianes pour ne pas léser l’un ou l’autre workload. La vraie séparation se fait dans PgBouncer et dans les rôles.
| Paramètre | Valeur Mixte |
|---|---|
| shared_buffers | 20–25 % RAM |
| effective_cache_size | 70–80 % RAM |
| work_mem | 16–64 MB |
| maintenance_work_mem | 1 GB |
| max_wal_size | 16–32 GB |
| checkpoint_timeout | 20–30 min |
| checkpoint_completion_target | 0.9 |
| max_parallel_workers_per_gather | 1–2 |
| jit | on (seuil ↑) |
# --- Mixte preset --- shared_buffers = 8GB effective_cache_size = 24GB work_mem = 32MB maintenance_work_mem = 1GB max_wal_size = 24GB checkpoint_timeout = 25min checkpoint_completion_target = 0.9 max_parallel_workers_per_gather = 2 max_parallel_workers = 8 jit = on jit_above_cost = 200000
Bonnes pratiques
- Deux pools PgBouncer : pool_oltp (timeouts courts), pool_olap (connexions peu nombreuses).
- Planifier les jobs ETL et les gros VACUUM en heures creuses.
- Surveiller
pg_locksetpg_stat_statementsrégulièrement. - Mettre des
statement_timeoutplus faibles sur les rôles applicatifs.
A. Temps réel / faible latence
Priorité : latence basse, on accepte de perdre un peu de durabilité.
shared_buffers = 20–25% work_mem = 4–8MB synchronous_commit = off wal_compression = on max_wal_size = 8–16GB jit = off statement_timeout = 1500
B. Ingestion / IoT / séries temporelles
Beaucoup d’INSERT, on veut amortir les WAL et faire du partitionné.
max_wal_size = 16–32GB checkpoint_timeout = 30min wal_writer_delay = 20ms autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.02
Utiliser COPY + partitions par jour / semaine.
C. ETL / Batch lourd
Lancé de nuit, on veut de la RAM dispo pour les tri/hash.
work_mem = 128–512MB maintenance_work_mem = 2GB temp_buffers = 128MB max_parallel_workers = 12
À ne pas laisser activé en permanence si d’autres applis tournent.
D. Réplication / HA
wal_level = replica archive_mode = on max_wal_senders = 10 hot_standby = on
Surveiller pg_stat_wal_receiver et le lag côté standby.