🐘Cas 5 – Batches nocturnes

Inventaire des jobs

• Source : cron, orchestrateur (Airflow, Control-M…), scripts maison.
• Pour chaque job :
  • horaire / fréquence,
  • tables touchées (lecture/écriture),
  • durée typique & durée max observée,
  • priorité métier (peut être décalé ?).

Fenêtres de maintenance & SLA

• Identifier les plages où la charge OLTP est minimale.
• Répartir les jobs lourds pour éviter les pics alignés.
• Prévoir des marges : pas de job qui déborde sur l’heure d’ouverture.

Cette cartographie est la base : sans elle, impossible de raisonner sur l’impact combiné des ETL, refresh et exports.

Observer les jobs en cours

SELECT pid, usename, application_name,
       now() - query_start AS age,
       wait_event_type, wait_event,
       state, query
FROM pg_stat_activity
WHERE query NOT ILIKE '%pg_stat_activity%'
  AND state <> 'idle'
ORDER BY age DESC;
          

Permet de repérer les gros SELECT/INSERT/CTAS lancés par les jobs ETL et de voir s’ils attendent sur I/O, locks, etc.

Temp files & tri / hash lourds

SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
ORDER BY temp_bytes DESC;
          

• Les jobs de reporting mal réglés peuvent générer des temp files énormes.
• À corriger via :
  • ajout d’index,
  • réécriture des requêtes,
  • augmentation raisonnable de work_mem côté job uniquement.

Design d’une vue matérialisée

CREATE MATERIALIZED VIEW mv_sales_daily AS
SELECT
  date_trunc('day', created_at) AS day,
  customer_id,
  sum(amount)                   AS total_amount,
  count(*)                      AS nb_orders
FROM orders
GROUP BY 1, 2;
          

• Limiter le nombre de jointures : vues simples, spécialisées.
• Index locaux sur les colonnes de filtrage (day, customer_id…).

Refresh & bascule

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_daily;
          

• CONCURRENTLY évite les verrous exclusifs sur la vue mais nécessite un index unique.
• Stratégie fréquente :
  • vue matérialisée de travail,
  • refresh dessus,
  • swap via renommage ou vue logique par-dessus.

Isoler le reporting

• Réplicas en lecture réservés aux jobs de reporting.
• Data warehouse dédié (Redshift, BigQuery, autre PG…) alimenté par ETL.
• Limiter les requêtes « full scan » sur le cluster OLTP.

Patterns utiles

• CDC / logical replication pour pousser les changements vers le DW.
• Exports incrémentaux plutôt que full dump chaque nuit.
• Pré-agrégations (vues matérialisées) pour les dashboards temps quasi-réel.

0–5 min : diagnostic rapide

1. Identifier le job en cause (application_name, user, query).
2. Mesurer l’impact : CPU, I/O, verrous, temp files.
3. Vérifier si le job est annulable sans risque fonctionnel.

5–15 min : actions

• Si possible, throttler le job (limitation du nombre de lignes traitées, pause entre lots).
• À défaut, annuler via pg_cancel_backend() puis replanifier dans une fenêtre adaptée.
• Informer les métiers : reporting incomplet vs prod indisponible, faire un choix.