🐘 Cas 2 – Concurrence OLTP

Métriques clés

• Nombre d’INSERT/UPDATE/DELETE par table.
• Ratio lectures/écritures, taille de la table et des index.
• Taux de cache (hit ratio) et temp files associés aux requêtes OLTP.

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

On repère ainsi les tables les plus écrites, candidates à un partitionnement ou à une séparation OLTP / historique.

Hot pages & I/O

SELECT relname,
       heap_blks_read, heap_blks_hit,
       idx_blks_read,  idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read + idx_blks_read DESC
LIMIT 10;
          

Profiler les requêtes

• Avec pg_stat_statements, isoler les requêtes les plus fréquentes sur les tables identifiées.
• Identifier si le problème vient d’un manque d’index, de jointures coûteuses ou d’une simple saturation d’INSERT.

SELECT queryid,
       left(query, 120) AS sample,
       calls,
       rows,
       mean_time,
       shared_blks_hit + shared_blks_read AS shared_blocks
FROM pg_stat_statements
WHERE query ILIKE '%orders%'     -- adapter à la table hot
ORDER BY calls DESC
LIMIT 20;
          

Checklist « hot table »

• La table est-elle utilisée comme queue de messages et historique ?
• Existe-t-il des index inutiles qui pénalisent les INSERT/UPDATE ?
• Les FK importantes sont-elles indexées ?
• La colonne de partitionnement potentielle (date, tenant, statut…) est-elle claire ?

Choisir la clé de partition

• RANGE (date/time) pour les journaux, commandes, événements temporels.
• LIST pour des catégories limitées (environnements, régions).
• HASH pour répartir la charge par tenant ou identifiant quand la distribution est uniforme.

CREATE TABLE events_p (
  id      bigserial PRIMARY KEY,
  ts      timestamptz NOT NULL,
  tenant  int          NOT NULL,
  payload jsonb
) PARTITION BY RANGE (ts);
          

Granularité des partitions

• Partition mensuelle ou quotidienne selon le volume.
• Objectif : taille par partition compatible avec VACUUM, REINDEX et backups rapides.
• Ne pas hésiter à fusionner ou splitter plus tard avec des scripts dédiés.

Routing applicatif & index

• Prévoir une fonction de routing côté appli (par date, tenant, shard) pour les cas très volumineux.
• Index locaux à chaque partition sur les colonnes filtrantes (date, status, tenant).
• Limiter le nombre d’index globaux ou d’index non alignés sur la clé de partition (risque de scans globaux).

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

CREATE INDEX ON events_2025_01 (tenant, ts);
          

Pièges

• Partitionnement mis en place mais clé de partition jamais utilisée dans les requêtes.
• Nombre excessif de partitions (10 000+) → overhead catalogue & planification.
• Oubli des index sur les partitions fraîchement créées.

Index uniques & pages de fin

• Les index basés sur des clés monotones (identifiant séquentiel, timestamp) concentrent les writes sur la même page de fin.
• Avec beaucoup de concurrence, cela crée un goulot sur le buffer et les verrous de page.

-- Vérifier les index les plus mis à jour
SELECT relname, indexrelname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC
LIMIT 10;
          

Stratégies

• Utiliser des clés pseudo-aléatoires (UUID v4, snowflake, hash) pour certains index secondaires.
• Réduire le nombre d’index sur les tables très écrites.

Séquences & multi-tenant

• Séquence globale saturée par des milliers de sessions.
• Pour des tenants isolés : séquence par tenant ou par partition.

-- Consommation d’une séquence
SELECT relname, seq_tup_read, seq_tup_write
FROM pg_stat_all_sequences
ORDER BY seq_tup_write DESC
LIMIT 10;
          

-- Exemple de séquence par tenant
CREATE SEQUENCE order_seq_tenant_42;
SELECT nextval('order_seq_tenant_42');
          

Autres leviers

• Regrouper certains writes en batch (COPY, INSERT multi-lignes) pour réduire le coût par transaction.
• Utiliser des tables UNLOGGED pour des buffers temporaires, puis intégrer en différé dans la table principale.

Séparer OLTP & historique

• Garder la table OLTP compacte (fenêtre temporelle courte).
• Déplacer régulièrement les données anciennes vers une table d’historique partitionnée ou un data warehouse.

-- Exemple simple : table OLTP + historique
CREATE TABLE orders_live (...);
CREATE TABLE orders_archive (LIKE orders_live) PARTITION BY RANGE (created_at);
          

Queues de travail efficaces

• Table dédiée pour les jobs, avec status & timestamps.
• Workers consommant via FOR UPDATE SKIP LOCKED pour éviter la contention.

CREATE TABLE job_queue (
  id       bigserial PRIMARY KEY,
  status   text        NOT NULL,
  payload  jsonb,
  created_at timestamptz DEFAULT now()
);

-- worker
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 50;
          

Étapes immédiates (0–5 min)

1. Identifier la table / index responsable via pg_stat_activity + pg_stat_user_tables.
2. Vérifier si la latence vient de verrous (cf. cas 1) ou de saturation CPU/I/O.
3. Limiter le trafic sur les fonctionnalités non critiques (feature flags, rate limiting côté appli).

Délestage rapide

• Basculer certaines lectures sur un replica en lecture seule.
• Augmenter temporairement les ressources (scaling vertical ou horizontal si possible).
• Désactiver ou ralentir les batchs secondaires (reporting, exports, ETL).

Après l’incident

• Analyser :
  • les requêtes les plus coûteuses sur la table hot,
  • la présence ou non de partitionnement,
  • le nombre et la nature des index.
• Définir un plan :
  • mise en place / amélioration du partitionnement,
  • réduction / refonte des index,
  • séparation OLTP / historique,
  • mise en cache côté appli ou via Redis/HTTP cache.
• Créer des alertes proactives sur :
  • TPS sur la table,
  • temps de réponse moyen et p95,
  • temp files et I/O sur le volume data.