🐘 3) Paramétrage PostgreSQ

Notation

• RAM = mémoire totale serveur
• RAM_PG = budget mémoire pour PostgreSQL (en général 60–75% de RAM si dédié)
• Ces valeurs sont des points de départ → valider après observation (pg_stat_statements, logs temp, I/O)

shared_buffers

effective_cache_size

≈ RAM_PG × 0,75–0,8 (suppose que le FS + shared_buffers tiennent pas mal de pages).

work_mem (par opération)

# approximation
work_mem = (RAM_PG × 0.05) / max_active_ops

# où
# max_active_ops ≈ connexions simultanées × 1,5  (OLTP)
# max_active_ops ≈ nb. workers OLAP            (OLAP)

Ex : RAM_PG = 32 Go, 100 opérations actives → (32 × 0,05) / 150 ≈ 10 MB.

Ajuster ensuite en surveillant les temporary files dans les logs.

maintenance_work_mem

• 512 MB → 2 GB selon RAM
• réservé aux opérations : VACUUM, REINDEX, CREATE INDEX
• ne pas le mettre trop haut sur des serveurs multi-connexions

wal_buffers

wal_buffers = -1 (auto-tune) ou 64–256 MB si write-heavy constant.

WAL & durabilité

Taille WAL

• min_wal_size = 1–4 GB
• max_wal_size = 8–32 GB (NVMe → plutôt haut)
• but : espacer les checkpoints → moins de pics I/O

Checkpoints

• checkpoint_timeout = 15–30min (write-heavy → 15–20 min)
• checkpoint_completion_target = 0.9 (étaler)

-- Surveiller les checkpoints
SELECT * FROM pg_stat_bgwriter;

-- Surveiller la taille WAL
SELECT pg_size_pretty(pg_xlog_location_diff(pg_current_wal_lsn(), '0/0'));

Si tu vois trop de checkpoints dans les stats → augmenter max_wal_size et/ou checkpoint_timeout.

Principes

• autovacuum = on (toujours)
• autovacuum_max_workers = 3–10 selon RAM et I/O
• autovacuum_naptime = 10–30s

Facteurs

• autovacuum_vacuum_scale_factor = 0.05 (plus bas sur grosses tables)
• autovacuum_analyze_scale_factor = 0.02–0.05
• gros volumes → aller à 0.01–0.02 par table

Par table

ALTER TABLE big_table
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_analyze_scale_factor = 0.01,
       autovacuum_vacuum_cost_limit = 2000);

Freeze

Surveiller age(datfrozenxid). Planifier VACUUM (FREEZE) hors-pics si proche du wraparound.

SELECT relname, age(relfrozenxid)
FROM pg_class
WHERE relkind = 'r'
ORDER BY 2 DESC
LIMIT 20;

Coûts d’accès

Parallélisme (>= 9.6)

• max_parallel_workers_per_gather = 2–4 (OLAP) ; OLTP → 1–2
• max_parallel_workers = nb_cœurs / 2 (plafonner)
• parallel_leader_participation = on

JIT

• OLTP : souvent off (latence)
• OLAP : on mais avec jit_above_cost élevé

Stockage temp

• Mettre temp_tablespaces sur volume rapide
• Surveiller l’espace libre si beaucoup de requêtes de tri

Surveiller les fichiers temporaires

-- En pré-prod
ALTER SYSTEM SET log_temp_files = 0;

-- En prod
ALTER SYSTEM SET log_temp_files = '10MB';

Puis ajuster work_mem si beaucoup de “could not write temp file” apparaissent.

Timeouts généraux

• idle_in_transaction_session_timeout = 60s → 300s
• statement_timeout par rôle/app (OLTP: 1–5 s)
• lock_timeout pour éviter les blocages longs

Locks

• max_locks_per_transaction → augmenter si migrations massives
• surveiller pg_locks et pg_stat_activity

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;