🐘 Cas d’usage – Tuning PostgreSQL

Scénario typique

• Batch UPDATE/DELETE sans limite sur une grosse table en pleine journée.
• Transactions ouvertes longtemps (idle in transaction) qui gardent des verrous sur des lignes/index.
• Deux modules accèdent aux mêmes tables dans un ordre différent → deadlocks fréquents.

Symptômes visibles

• Requêtes clients qui restent bloquées sur wait_event = lock.
• Messages « could not obtain lock on relation » ou « deadlock detected » dans les logs.
• Charge CPU faible mais temps de réponse qui explose.

Pièges qui font tomber le serveur

• Lancer un ALTER TABLE verrouillant en plein pic trafic.
• Trigger applicatif qui effectue des accès croisés à plusieurs tables dans un ordre non maîtrisé.
• Absence de timeout : pas de lock_timeout / statement_timeout.

Diagnostic rapide

SELECT now(), pid, usename, state, wait_event_type, wait_event,
       locktype, relation::regclass, mode
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT granted
ORDER BY now();
          

-- Sessions tenant des verrous lourds
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
          

Actions correctives

• Terminer les transactions zombies : SELECT pg_terminate_backend(pid) sur les plus anciennes.
• Mettre en place des timeouts (session/app) : lock_timeout, statement_timeout.
• Revoir le code pour imposer un ordre d’accès constant aux tables (A → B → C partout).
• Remplacer les gros UPDATE/DELETE par des traitements par chunks avec commit régulier.

Scénario typique

• Table de logs, commandes ou événements avec fort débit d’INSERT/UPDATE.
• Index unique central (ex : numéro de commande séquentiel) très sollicité.
• Beaucoup de sessions qui insèrent sur la même page de fin d’index → hot page.

Indicateurs clés

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC
LIMIT 10;
          

• Temps CPU élevé côté Postgres, file d’attente I/O faible → contention logique, pas matérielle.
• Spikes sur LWLock / buffer_content dans les vues de waits (pg_stat_statements, pg_wait_sampling…).

Pièges classiques

• Un seul index multi-usage pour tout (recherche + unicité).
• Partitionnement prévu mais jamais activé en prod, ou plages trop grosses.
• Pas de pool de connexions : chaque thread appli ouvre sa propre connexion.

Design recommandé

• Partitionnement par date / tenant / clé fonctionnelle : PARTITION BY RANGE (created_at) ou HASH (tenant_id).
• Index locaux sur chaque partition pour limiter la contention.
• Usage d’une séquence différente par partition / tenant si possible.

Actions concrètes

-- Exemple de refonte en partitionnée par mois
CREATE TABLE events_p (
  id           bigserial PRIMARY KEY,
  ts           timestamptz NOT NULL,
  payload      jsonb
) PARTITION BY RANGE (ts);

CREATE TABLE events_2025_01 PARTITION OF events_p
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
          

• Limiter la taille de chaque partition (facilite VACUUM, backup, purge).
• Activer/optimiser un pool de connexions (pgbouncer) pour réduire la pression.
• Pour certaines écritures, utiliser UNLOGGED ou partition de staging + merge.

Motivations légitimes

• Base > plusieurs To, fenêtres de sauvegarde/restore incompatibles avec le RTO.
• Certains tenants/clients consomment >80 % de la charge.
• Limites matérielles atteintes (RAM max, IOPS, stockage local) malgré tuning et partitionnement.

Modèles de sharding

Attention

• Les jointures cross-shards sont coûteuses et souvent impossibles sans middleware.
• La complexité applicative augmente (routing des requêtes, migrations de shard…).

Checklist décisionnelle

• As-tu déjà :
  • Optimisé le schéma (index pertinents, partitionnement) ?
  • Mis en place la réplication & la lecture sur secondaires ?
  • Activé un cache côté appli/CDN pour les lectures répétées ?
• Le RTO/RPO reste-t-il atteignable avec un backup/restore complet ?

Approche pragmatique

• Commencer par un sharding fonctionnel (micro-services DB) : plus simple à mettre en place.
• Limiter le nombre de shards au départ (2–4) et automatiser :
  • Provisioning & monitoring par shard.
  • Réplication / sauvegarde uniformisées.
• Documenter un plan de rebalancing (migrer un client d’un shard à un autre).

Scénario typique

• Workload très écrivant (UPDATE/DELETE fréquents), autovacuum laissé par défaut.
• Tables multi-To, pas de VACUUM FULL / REINDEX planifiés.
• Log d’alerte : database is not accepting commands to avoid wraparound data loss.

Mesures à suivre

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       autovacuum_count, vacuum_count,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
          

• Bloat index/table (via pgstattuple ou extensions équivalentes).
• Âge des transactions (datfrozenxid) pour anticiper les freezes.

Pièges

• Désactiver autovacuum « pour les perfs » sans job de VACUUM dédié.
• Laisser les tables critiques approcher la limite de wraparound.

Actions & tuning

• Augmenter la fréquence/autonomie d’autovacuum sur les tables chaudes via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold).
• Planifier des VACUUM (FULL) et REINDEX sur fenêtres de maintenance.
• Pour les tables d’events, prévoir partitionnement + retention policy plutôt que purge en ligne.

-- Exemple de tuning spécifique sur une table très écrite
ALTER TABLE orders
  SET (autovacuum_vacuum_scale_factor = 0.05,
       autovacuum_analyze_scale_factor = 0.02,
       autovacuum_vacuum_threshold = 5000);
          

• Monitorer les temps d’exécution autovacuum et les collisions avec la charge métier.

Scénario typique

• Un job cron lance un gros INSERT INTO ... SELECT ou un refresh de vue matérialisée.
• Le batch tourne plus longtemps que prévu et empiète sur les heures de pointe.
• Explosions de temp_files dans les logs, disque temp saturé.

Signaux à surveiller

-- Temp files créés par session
SELECT pid, datname, usename,
       temp_files, temp_bytes,
       query
FROM pg_stat_activity
JOIN pg_stat_database USING (datid)
WHERE temp_files > 0;
          

• I/O en crête, mais pas forcément plus de connexions.
• Augmentation concomitante des temps de réponse applicatifs.

Pièges

• Lancer plusieurs batchs lourds en parallèle (ETL + reporting + maintenance).
• Augmenter work_mem globalement plutôt que par session/batch.

Stratégie de maîtrise

• Exécuter les batchs sur un replica read-only quand c’est possible.
• Limiter la concurrence :
  • job unique via ordonnanceur (Airflow, Rundeck…),
  • paramètre max_parallel_workers_per_gather adapté.
• Ajuster work_mem pour le batch uniquement (connexion dédiée).

-- Exemple d’override pour un job
SET work_mem = '512MB';
SET maintenance_work_mem = '2GB';
-- lancer ensuite le batch / refresh materialized view
          

• Documenter une fenêtre horaire « interdite » pour les batchs critiques (contrat avec les équipes métiers).

Étapes en temps réel

1. Vérifier nombre de connexions & états :

   SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
                 

2. Regarder les requêtes les plus consommatrices (si pg_stat_statements actif).
3. Identifier les wait events dominants (lock, I/O, CPU…).
4. Si surcharge évidente liée à 1–2 requêtes, réduire leur impact :
   • limiter la fréquence côté appli,
   • tuer temporairement les sessions les plus lourdes,
   • désactiver une fonctionnalité non critique.

Après l’incident (post-mortem)

• Exporter les stats (pg_stat_statements, vues pg_stat_*, métriques monitoring) avant reset.
• Documenter :
  • heure de début/fin,
  • requêtes / jobs en cause,
  • limites atteintes (max_conn, IOPS, CPU…).
• Créer un plan d’action :
  • tuning paramètre (connexion, mémoire, autovacuum…),
  • refonte éventuelle de schéma (index/partition),
  • ajout d’alertes ciblées pour détecter le pattern plus tôt.

Mini-checklist « prêt pour la prod »

• pgbouncer en place, politique de connexions claire.
• Autovacuum & sauvegardes vérifiés sur plusieurs jours de charge réelle.
• Runbooks documentés pour :
  • blocage de verrous,
  • bloat extrême,
  • batch ETL incontrôlé.