Skip to content

04.3-transaction-wraparound.md

Latest commit

 

History

History
1043 lines (768 loc) · 28.4 KB

04.3-transaction-wraparound.md

File metadata and controls

1043 lines (768 loc) · 28.4 KB

🔝 Retour au Sommaire

19.4.3. Transaction Wraparound (XID exhaustion) et prévention

Introduction : Un problème silencieux mais catastrophique

Le Transaction Wraparound (ou épuisement des XID) est l'un des problèmes les plus graves que PostgreSQL puisse rencontrer. C'est comme une bombe à retardement qui, si elle n'est pas désamorcée, peut arrêter complètement votre base de données.

La bonne nouvelle ? Ce problème est :

  • ✅ Totalement prévisible : PostgreSQL vous avertit bien à l'avance
  • ✅ Facilement évitable : Avec une maintenance appropriée
  • ✅ Automatiquement géré : Dans la plupart des configurations modernes

Ce chapitre va vous expliquer ce qu'est le wraparound, pourquoi il se produit, comment le détecter avant qu'il soit trop tard, et comment le prévenir définitivement.

Comprendre les bases : Qu'est-ce qu'un Transaction ID (XID) ?

L'identifiant de chaque transaction

Dans PostgreSQL, chaque transaction reçoit un identifiant unique appelé XID (Transaction ID). C'est un simple nombre entier.

-- Voir votre XID actuel
SELECT txid_current();
-- Résultat possible : 1234567

Pourquoi PostgreSQL utilise des XID ?

Les XID sont au cœur du système MVCC (Multiversion Concurrency Control) de PostgreSQL. Ils permettent de savoir :

  • Quelles versions de données sont visibles pour quelle transaction
  • Quelle transaction a créé ou modifié une ligne
  • Si une transaction était en cours avant ou après une autre

Analogie : Imaginez une bibliothèque où chaque visiteur reçoit un numéro d'entrée. Ce numéro détermine quels livres (versions de données) il peut voir.

XID dans chaque ligne de table

Chaque ligne de vos tables stocke deux XID invisibles :

-- Voir les colonnes système cachées d'une ligne
SELECT
    xmin,        -- XID de la transaction qui a créé la ligne
    xmax,        -- XID de la transaction qui l'a supprimée (0 si toujours visible)
    ctid,        -- Position physique
    *
FROM users  
WHERE id = 1;

Exemple de résultat :

 xmin    | xmax | ctid  | id | username | email
---------+------+-------+----+----------+------------------
 1002345 |    0 | (0,1) |  1 | alice    | alice@example.com

Interprétation :

  • xmin = 1002345 : Ligne créée par la transaction 1002345
  • xmax = 0 : Ligne toujours vivante (pas supprimée)

Le problème : Les limites d'un nombre entier

XID est un entier 32 bits

PostgreSQL utilise un entier non signé de 32 bits pour les XID. Cela signifie :

Valeurs possibles : 0 à 4 294 967 295 (environ 4,3 milliards)

Que se passe-t-il après 4 milliards de transactions ?

Après avoir utilisé tous les XID disponibles, PostgreSQL recommence à zéro. C'est le wraparound (retour à zéro).

Transaction 4 294 967 295  (dernier XID)  
Transaction          0     (retour au début - wraparound!)  
Transaction          1  
Transaction          2  
...

Le danger : La confusion temporelle

Sans précaution, le wraparound créerait une catastrophe logique :

Scénario catastrophe :

  1. Transaction XID = 100 crée une ligne (xmin = 100)
  2. Wraparound se produit
  3. Transaction XID = 100 (nouveau tour) tente de lire les données
  4. PostgreSQL pense que cette ligne a été créée dans le futur !
  5. La ligne devient invisible alors qu'elle devrait être visible

Résultat : Vos données disparaissent mystérieusement ! 😱

La solution de PostgreSQL : Les Frozen XIDs

Le concept de "gel" (freezing)

Pour éviter le problème du wraparound, PostgreSQL "gèle" (freeze) les anciennes transactions. Cela signifie :

Avant le gel :

Ligne : xmin = 1002345 (créée par transaction 1002345)

Après le gel :

Ligne : xmin = 2 (valeur spéciale "FrozenTransactionId")

La valeur spéciale 2 (ou FrozenTransactionId) signifie : "Cette ligne est visible pour toutes les transactions, peu importe le wraparound".

Comment le gel fonctionne

Le gel est effectué par le processus VACUUM :

-- VACUUM gèle les anciennes lignes
VACUUM users;

-- VACUUM FREEZE force le gel immédiat
VACUUM FREEZE users;

Ce que VACUUM fait :

  1. Parcourt toutes les lignes de la table
  2. Pour chaque ligne "ancienne" (xmin ancien)
  3. Remplace xmin par FrozenTransactionId (2)
  4. Marque la ligne comme "gelée" pour toujours

Les paramètres critiques du wraparound

PostgreSQL a plusieurs paramètres qui contrôlent quand et comment le gel se produit.

1. vacuum_freeze_min_age

Définition : Âge minimum (en transactions) avant qu'une ligne puisse être gelée.

SHOW vacuum_freeze_min_age;
-- Valeur par défaut : 50000000 (50 millions de transactions)

Signification : VACUUM ne gèlera une ligne que si son xmin a au moins 50 millions de transactions d'écart avec le XID actuel.

Pourquoi pas geler immédiatement ?

  • Geler coûte en I/O (écriture sur disque)
  • Les lignes récentes peuvent encore être modifiées
  • On attend que les lignes soient "stables"

2. vacuum_freeze_table_age

Définition : Âge d'une table (en transactions) qui déclenche un VACUUM complet pour geler.

SHOW vacuum_freeze_table_age;
-- Valeur par défaut : 150000000 (150 millions)

Signification : Quand la plus ancienne transaction non gelée d'une table atteint 150 millions de transactions d'âge, VACUUM parcourt toute la table pour geler agressivement.

3. autovacuum_freeze_max_age

Définition : Âge maximum absolu avant qu'un VACUUM d'urgence soit forcé.

SHOW autovacuum_freeze_max_age;
-- Valeur par défaut : 200000000 (200 millions)

Signification : À 200 millions de transactions d'âge, PostgreSQL force un VACUUM automatique même si autovacuum est désactivé. C'est la dernière ligne de défense.

⚠️ CRITIQUE : Si une table atteint autovacuum_freeze_max_age, PostgreSQL lancera un VACUUM prioritaire qui peut impacter les performances.

4. autovacuum_multixact_freeze_max_age

Définition : Similaire pour les MultiXacts (transactions concurrentes sur la même ligne).

SHOW autovacuum_multixact_freeze_max_age;
-- Valeur par défaut : 400000000 (400 millions)

Détection : Surveiller l'âge des transactions

Méthode 1 : Vérifier l'âge global de la base de données

-- Âge de la base de données (en transactions)
SELECT
    datname,
    age(datfrozenxid) AS age_in_transactions,
    datfrozenxid
FROM pg_database  
ORDER BY age(datfrozenxid) DESC;

Exemple de résultat :

 datname  | age_in_transactions | datfrozenxid
----------+---------------------+--------------
 mydb     |          95000000   |      1005000
 postgres |           2000000   |      1098000
 template1|           1500000   |      1098500

Interprétation :

  • age_in_transactions : Nombre de transactions depuis le dernier gel complet
  • Plus l'âge est élevé, plus on se rapproche du danger
  • ALERTE si > 1 milliard (25% de la limite de 4 milliards)

Méthode 2 : Vérifier l'âge par table

SELECT
    schemaname,
    tablename,
    age(relfrozenxid) AS age_in_transactions,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_catalog.pg_tables t  
JOIN pg_catalog.pg_class c ON c.relname = t.tablename  
ORDER BY age(relfrozenxid) DESC  
LIMIT 20;

Exemple de résultat :

 schemaname | tablename | age_in_transactions |  size
------------+-----------+---------------------+---------
 public     | logs      |          180000000  | 250 GB
 public     | events    |          170000000  | 180 GB
 public     | users     |           50000000  | 15 GB

Interprétation :

  • La table logs approche dangereusement de autovacuum_freeze_max_age (200M)
  • Elle a besoin d'un VACUUM urgent

Méthode 3 : Pourcentage avant le wraparound

SELECT
    datname,
    age(datfrozenxid) AS age_in_transactions,
    round(100.0 * age(datfrozenxid) / 2000000000, 2) AS percent_towards_wraparound
FROM pg_database  
ORDER BY age(datfrozenxid) DESC;

Exemple de résultat :

 datname  | age_in_transactions | percent_towards_wraparound
----------+---------------------+---------------------------
 mydb     |          180000000  |                      9.00
 postgres |            2000000  |                      0.10

Seuils d'alerte :

  • < 5% : Tout va bien ✅
  • 5-10% : Surveiller 👀
  • 10-20% : Attention, planifier maintenance ⚠️
  • > 20% : URGENT, action immédiate requise 🚨

Méthode 4 : Vérifier les warnings PostgreSQL

PostgreSQL émet des warnings dans les logs :

WARNING: database "mydb" must be vacuumed within 11000000 transactions  
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

Vérifier les logs :

sudo tail -f /var/log/postgresql/postgresql-*.log | grep -i "wraparound\|must be vacuumed"

Les zones de danger : Comprendre les seuils

Voici les étapes progressives vers le wraparound :

         0 --------------------------- 200M ------------------------- 2B ---------------------------- 4B
         |                              |                            |                              |
         Transactions créées    autovacuum_freeze_max_age    Wraparound Warning          Shutdown!
                                   (VACUUM forcé)              (PostgreSQL alerte)      (Arrêt pour protection)

Zone verte (0 - 200M transactions)

✅ Tout va bien

  • Autovacuum fonctionne normalement
  • Le gel se fait automatiquement

Zone orange (200M - 2B transactions)

⚠️ Attention requise

  • Autovacuum se déclenche en mode agressif
  • Performance peut être affectée
  • Action : Investiguer pourquoi VACUUM n'a pas fonctionné

Zone rouge (2B - 2.1B transactions)

🚨 DANGER CRITIQUE

  • PostgreSQL émet des warnings constants
  • Base de données en mode "survie"
  • Action : VACUUM manuel d'urgence

Zone noire (> 2.1B transactions)

💀 CATASTROPHE

  • PostgreSQL refuse toutes les nouvelles transactions
  • Seules les opérations VACUUM sont autorisées
  • Database en lecture seule forcée

Message d'erreur :

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"  
HINT: Stop the postmaster and vacuum that database in single-user mode.

Prévention : Les bonnes pratiques

1. Vérifier qu'autovacuum est activé

-- Vérifier globalement
SHOW autovacuum;
-- Résultat attendu : on

-- Vérifier pour une table spécifique
SELECT
    schemaname,
    tablename,
    (reloptions::text LIKE '%autovacuum_enabled=off%') AS autovacuum_disabled
FROM pg_tables  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Si autovacuum est désactivé sur une table :

-- Réactiver autovacuum pour une table
ALTER TABLE my_table SET (autovacuum_enabled = true);

-- Ou supprimer le paramètre pour utiliser la config globale
ALTER TABLE my_table RESET (autovacuum_enabled);

⚠️ IMPORTANT : Ne JAMAIS désactiver autovacuum globalement en production !

2. Configurer autovacuum de manière appropriée

-- Dans postgresql.conf

# Activer autovacuum (OBLIGATOIRE)
autovacuum = on

# Nombre de workers (augmenter pour grandes bases)
autovacuum_max_workers = 6

# Fréquence de vérification (par défaut : 1 minute)
autovacuum_naptime = 1min

# Nouveauté PG 18 : Ajustement dynamique des workers
autovacuum_worker_slots = 10

# Seuils de déclenchement (par défaut souvent suffisant)
autovacuum_vacuum_threshold = 50  
autovacuum_vacuum_scale_factor = 0.2  

# Nouveauté PG 18 : Seuil max pour grandes tables
autovacuum_vacuum_max_threshold = 1000000

3. Monitoring et alertes proactives

Créez une vue pour monitorer facilement :

CREATE OR REPLACE VIEW v_wraparound_monitoring AS  
SELECT  
    datname,
    age(datfrozenxid) AS age_in_transactions,
    round(100.0 * age(datfrozenxid) / 2000000000, 2) AS percent_towards_wraparound,
    CASE
        WHEN age(datfrozenxid) < 100000000 THEN 'OK'
        WHEN age(datfrozenxid) < 200000000 THEN 'WATCH'
        WHEN age(datfrozenxid) < 1000000000 THEN 'WARNING'
        ELSE 'CRITICAL'
    END AS status,
    2000000000 - age(datfrozenxid) AS transactions_until_emergency
FROM pg_database  
ORDER BY age(datfrozenxid) DESC;  

-- Consulter facilement
SELECT * FROM v_wraparound_monitoring;

Configurer des alertes :

-- Créer une fonction d'alerte
CREATE OR REPLACE FUNCTION check_wraparound_danger()  
RETURNS TABLE(datname name, age bigint, percent numeric, status text) AS $$  
BEGIN  
    RETURN QUERY
    SELECT
        d.datname,
        age(d.datfrozenxid)::bigint AS age,
        round(100.0 * age(d.datfrozenxid) / 2000000000, 2) AS percent,
        CASE
            WHEN age(d.datfrozenxid) > 1000000000 THEN 'CRITICAL'
            WHEN age(d.datfrozenxid) > 200000000 THEN 'WARNING'
            ELSE 'OK'
        END AS status
    FROM pg_database d
    WHERE age(d.datfrozenxid) > 200000000;
END;
$$ LANGUAGE plpgsql;

-- Planifier avec pg_cron
SELECT cron.schedule(
    'check-wraparound',
    '0 */4 * * *',  -- Toutes les 4 heures
    'SELECT * FROM check_wraparound_danger()'
);

4. VACUUM manuel préventif pour tables critiques

Pour les très grandes tables :

-- Identifier les tables problématiques
SELECT
    schemaname || '.' || tablename AS full_table_name,
    age(relfrozenxid) AS age_in_transactions,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    last_vacuum,
    last_autovacuum
FROM pg_catalog.pg_tables t  
JOIN pg_catalog.pg_class c ON c.relname = t.tablename  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
  AND age(relfrozenxid) > 150000000
ORDER BY age(relfrozenxid) DESC;

-- VACUUM manuel avec monitoring
\timing on
VACUUM (VERBOSE, ANALYZE) large_table;

Option pour tables très volumineuses :

-- VACUUM par morceaux (attention : technique avancée)
-- Augmenter temporairement maintenance_work_mem
SET maintenance_work_mem = '4GB';  
VACUUM (VERBOSE, ANALYZE) huge_table;  
RESET maintenance_work_mem;

5. Planification de VACUUM pendant heures creuses

Si VACUUM impacte vos performances :

-- Utiliser pg_cron pour planifier la nuit
SELECT cron.schedule(
    'vacuum-large-tables',
    '0 2 * * *',  -- 2h du matin tous les jours
    $$VACUUM (VERBOSE, ANALYZE) logs$$
);

SELECT cron.schedule(
    'vacuum-events',
    '0 3 * * *',
    $$VACUUM (VERBOSE, ANALYZE) events$$
);

Alternative avec crontab système :

# Éditer crontab
crontab -e

# Ajouter
0 2 * * * /usr/bin/psql -U postgres -d mydb -c "VACUUM (VERBOSE) logs;" >> /var/log/vacuum.log 2>&1

6. Ajuster les coûts de VACUUM

Si VACUUM est trop agressif et impacte les performances :

-- Dans postgresql.conf

# Limiter l'impact I/O de VACUUM (valeurs plus basses = moins agressif)
vacuum_cost_delay = 10ms          # Pause entre opérations (0 = aucune pause)  
vacuum_cost_page_hit = 1          # Coût page en cache  
vacuum_cost_page_miss = 10        # Coût page lue depuis disque  
vacuum_cost_page_dirty = 20       # Coût page écrite  
vacuum_cost_limit = 200           # Limite avant pause  

# Pour autovacuum spécifiquement
autovacuum_vacuum_cost_delay = 2ms  
autovacuum_vacuum_cost_limit = 200

Augmenter la vitesse pour maintenance urgente :

-- Augmenter temporairement pour VACUUM manuel rapide
SET vacuum_cost_delay = 0;  -- Aucune pause, pleine vitesse  
VACUUM logs;  
RESET vacuum_cost_delay;

Résolution d'urgence : Table en danger critique

Scénario : Table approche autovacuum_freeze_max_age

-- Identifier la table en danger
SELECT
    schemaname || '.' || tablename AS table_name,
    age(relfrozenxid) AS age,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_catalog.pg_tables t  
JOIN pg_catalog.pg_class c ON c.relname = t.tablename  
WHERE age(relfrozenxid) > 180000000  
ORDER BY age(relfrozenxid) DESC  
LIMIT 5;

Solution 1 : VACUUM manuel immédiat

-- Option 1 : VACUUM standard (recommandé)
VACUUM (VERBOSE, ANALYZE) problematic_table;

-- Option 2 : VACUUM FREEZE (plus agressif, gèle tout)
VACUUM (FREEZE, VERBOSE, ANALYZE) problematic_table;

Surveiller la progression :

-- Dans une autre session, monitorer
SELECT
    pid,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) AS percent_complete
FROM pg_stat_progress_vacuum  
WHERE relid = 'problematic_table'::regclass;

Solution 2 : Pour table vraiment énorme (> 1TB)

Si VACUUM complet prend trop de temps, considérez le partitionnement :

-- Nouvelle table partitionnée
CREATE TABLE logs_new (
    id BIGSERIAL,
    message TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Créer des partitions
CREATE TABLE logs_2024_01 PARTITION OF logs_new
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs_new
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ...

-- Migrer progressivement avec INSERT ... SELECT
-- En plusieurs transactions pour ne pas bloquer
BEGIN;  
INSERT INTO logs_new  
SELECT * FROM logs  
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';  
COMMIT;  
-- Répéter par jour ou semaine

Cas d'urgence extrême : Base de données en lecture seule

Si PostgreSQL refuse les nouvelles transactions :

ERROR: database is not accepting commands to avoid wraparound data loss

Étape 1 : Ne pas paniquer 😅

C'est une protection, pas une destruction. PostgreSQL protège vos données.

Étape 2 : Mode single-user

# 1. Arrêter PostgreSQL
sudo systemctl stop postgresql

# 2. Démarrer en mode single-user
sudo -u postgres postgres --single -D /var/lib/postgresql/data mydb

# 3. Dans le mode single-user, exécuter VACUUM
VACUUM FREEZE;

# 4. Quitter (Ctrl+D)

# 5. Redémarrer normalement
sudo systemctl start postgresql

Étape 3 : VACUUM agressif de toutes les bases

-- Une fois la base redémarrée
VACUUM (FREEZE, VERBOSE) ANALYZE;

-- Pour chaque base de données
\c mydb
VACUUM (FREEZE, VERBOSE) ANALYZE;

\c otherdb
VACUUM (FREEZE, VERBOSE) ANALYZE;

Étape 4 : Vérifier que tout est revenu à la normale

SELECT
    datname,
    age(datfrozenxid) AS age,
    round(100.0 * age(datfrozenxid) / 2000000000, 2) AS percent
FROM pg_database  
ORDER BY age DESC;

L'âge devrait être redescendu à quelques millions de transactions.

Nouveautés PostgreSQL 18 concernant VACUUM

PostgreSQL 18 apporte plusieurs améliorations pour VACUUM :

1. Statistiques VACUUM enrichies

-- Nouveauté PG 18 : Statistiques détaillées dans pg_stat_all_tables
SELECT
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_tup_ins,          -- Lignes insérées
    n_tup_upd,          -- Lignes mises à jour
    n_tup_del,          -- Lignes supprimées
    n_live_tup,         -- Lignes vivantes
    n_dead_tup,         -- Lignes mortes (à nettoyer)
    last_analyze,
    last_autoanalyze
FROM pg_stat_all_tables  
WHERE schemaname = 'public'  
ORDER BY n_dead_tup DESC;

2. Autovacuum plus intelligent

-- Nouveauté PG 18 : Paramètres dynamiques
-- Dans postgresql.conf

# Workers ajustables dynamiquement
autovacuum_worker_slots = 10

# Seuil maximum pour éviter autovacuum trop fréquent sur énormes tables
autovacuum_vacuum_max_threshold = 1000000

3. Statistiques I/O et WAL par backend

-- Suivre le WAL généré (indication de l'activité)
SELECT
    datname,
    sum(wal_bytes) AS total_wal_bytes,
    pg_size_pretty(sum(wal_bytes)) AS total_wal_size
FROM pg_stat_database  
GROUP BY datname  
ORDER BY sum(wal_bytes) DESC;

Monitoring automatisé avec scripts

Script bash de monitoring quotidien

#!/bin/bash
# wraparound_check.sh

PGUSER="postgres"  
PGDATABASE="postgres"  
THRESHOLD_WARNING=200000000  
THRESHOLD_CRITICAL=1000000000  

# Vérifier l'âge des bases
RESULT=$(psql -U $PGUSER -d $PGDATABASE -t -c "  
SELECT  
    datname || ': ' || age(datfrozenxid)
FROM pg_database  
WHERE age(datfrozenxid) > $THRESHOLD_WARNING  
ORDER BY age(datfrozenxid) DESC;  
")

if [ -n "$RESULT" ]; then
    echo "⚠️ WRAPAROUND WARNING ⚠️"
    echo "$RESULT"

    # Vérifier si critique
    CRITICAL=$(psql -U $PGUSER -d $PGDATABASE -t -c "
    SELECT COUNT(*)
    FROM pg_database
    WHERE age(datfrozenxid) > $THRESHOLD_CRITICAL;
    ")

    if [ "$CRITICAL" -gt 0 ]; then
        echo "🚨 CRITICAL: Immediate action required!"
        # Envoyer alerte (email, Slack, etc.)
    fi
else
    echo "✅ All databases are safe from wraparound"
fi

Planifier avec cron :

# Tous les jours à 8h
0 8 * * * /usr/local/bin/wraparound_check.sh >> /var/log/wraparound_check.log 2>&1

Intégration avec Prometheus

# prometheus.yml - Alertes wraparound

groups:
  - name: postgresql_wraparound
    rules:
      - alert: PostgreSQLWraparoundWarning
        expr: pg_database_age > 200000000
        for: 1h
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL database {{ $labels.datname }} approaching wraparound"
          description: "Age: {{ $value }} transactions"

      - alert: PostgreSQLWraparoundCritical
        expr: pg_database_age > 1000000000
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL database {{ $labels.datname }} in CRITICAL wraparound danger!"
          description: "Age: {{ $value }} transactions - IMMEDIATE ACTION REQUIRED"

FAQ : Questions fréquentes

Q1 : Combien de temps prend un VACUUM FREEZE ?

Réponse : Dépend de la taille de la table et du disque.

Estimation approximative :

  • Table 10 GB sur SSD : ~5-15 minutes
  • Table 100 GB sur SSD : ~30-90 minutes
  • Table 1 TB sur SSD : ~5-15 heures

Facteurs d'accélération :

  • Augmenter maintenance_work_mem
  • Utiliser SSD NVMe
  • Mettre vacuum_cost_delay = 0 temporairement

Q2 : VACUUM bloque-t-il les lectures/écritures ?

Réponse :

  • ❌ VACUUM normal ne bloque pas les lectures ni les écritures
  • ⚠️ VACUUM FULL bloque tout (exclusif), évitez-le en production
  • ✅ VACUUM FREEZE se comporte comme VACUUM normal (non bloquant)

Q3 : Puis-je annuler un VACUUM en cours ?

Réponse : Oui, avec pg_cancel_backend(pid) ou pg_terminate_backend(pid).

Conséquence : Le travail déjà fait est conservé, mais la table n'est pas complètement gelée.

Q4 : Que faire si autovacuum ne s'exécute jamais ?

Causes possibles :

  1. Autovacuum désactivé (vérifier avec SHOW autovacuum;)
  2. Pas assez de workers (autovacuum_max_workers trop faible)
  3. Transactions longues bloquent VACUUM
  4. vacuum_cost_delay trop élevé (VACUUM trop lent)

Solution :

-- Vérifier les verrous
SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '1 hour';

-- Augmenter les workers
ALTER SYSTEM SET autovacuum_max_workers = 6;  
SELECT pg_reload_conf();

Q5 : Les réplicas ont-ils aussi besoin de VACUUM ?

Réponse :

  • Réplication physique : Les réplicas reçoivent les modifications VACUUM automatiquement
  • Réplication logique : Oui, chaque instance doit faire son propre VACUUM

Checklist de prévention du wraparound

Quotidien

  • Vérifier que autovacuum est actif
  • Consulter les logs pour warnings
  • Vérifier via monitoring (Grafana, Prometheus)

Hebdomadaire

  • Exécuter la requête de monitoring : SELECT * FROM v_wraparound_monitoring;
  • Vérifier les tables avec age(relfrozenxid) > 100M
  • Analyser les statistiques de VACUUM : SELECT * FROM pg_stat_user_tables;

Mensuel

  • VACUUM manuel des tables les plus volumineuses
  • Vérifier la configuration autovacuum
  • Analyser l'efficacité de VACUUM (logs)
  • Vérifier disk space (VACUUM nécessite espace temporaire)

Trimestriel

  • Revoir les paramètres autovacuum_freeze_max_age, vacuum_freeze_min_age
  • Tester un VACUUM FREEZE complet en maintenance
  • Auditer les tables avec autovacuum désactivé

Configuration recommandée

Pour base de données standard (< 500 GB)

# postgresql.conf

# Autovacuum
autovacuum = on  
autovacuum_max_workers = 3  
autovacuum_naptime = 1min  
autovacuum_vacuum_threshold = 50  
autovacuum_vacuum_scale_factor = 0.2  

# Freeze
vacuum_freeze_min_age = 50000000  
vacuum_freeze_table_age = 150000000  
autovacuum_freeze_max_age = 200000000  

# Performance VACUUM
maintenance_work_mem = 1GB  
autovacuum_work_mem = -1  # Utilise maintenance_work_mem  
autovacuum_vacuum_cost_delay = 2ms

Pour très grande base (> 1 TB)

# postgresql.conf

# Plus de workers pour paralléliser
autovacuum_max_workers = 8  
autovacuum_worker_slots = 12  # PG 18  

# Seuils ajustés pour grandes tables
autovacuum_vacuum_scale_factor = 0.05  # 5% au lieu de 20%  
autovacuum_vacuum_max_threshold = 5000000  # PG 18  

# Plus de mémoire
maintenance_work_mem = 4GB

# Freeze plus agressif pour éviter accumulation
vacuum_freeze_table_age = 100000000  
autovacuum_freeze_max_age = 150000000

Outils de diagnostic avancés

Extension pg_visibility

-- Installer l'extension
CREATE EXTENSION pg_visibility;

-- Vérifier les pages gelées d'une table
SELECT
    all_visible,
    all_frozen,
    count(*)
FROM pg_visibility_map('large_table')  
GROUP BY all_visible, all_frozen;

Interprétation :

  • all_frozen = true : Pages complètement gelées (bon)
  • all_frozen = false : Pages nécessitant un gel

Extension pgstattuple

-- Installer
CREATE EXTENSION pgstattuple;

-- Analyser une table
SELECT
    table_len,
    tuple_count,
    tuple_len,
    tuple_percent,
    dead_tuple_count,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('large_table');

Utilisation : Identifier les tables avec beaucoup de lignes mortes nécessitant VACUUM.

Ressources complémentaires

  • Documentation officielle : Routine Database Maintenance
  • Wiki PostgreSQL : Vacuum
  • Blog 2ndQuadrant : "Understanding Transaction ID Wraparound"
  • Percona Blog : "PostgreSQL Vacuum and Wraparound"

Résumé des points clés

✅ XID (Transaction ID) : Identifiant unique de chaque transaction, limité à 4 milliards

✅ Wraparound : Retour à zéro des XID après 4 milliards, causant confusion temporelle

✅ Freezing : PostgreSQL "gèle" les anciennes transactions pour éviter le wraparound

✅ VACUUM : Processus qui gèle les lignes et prévient le wraparound

✅ autovacuum_freeze_max_age : 200M par défaut, limite critique à 2 milliards

✅ Monitoring : Vérifier age(datfrozenxid) régulièrement

✅ Alertes : Mettre en place monitoring proactif (< 5% = OK, > 20% = URGENT)

✅ Prévention : Autovacuum activé + configuration appropriée + monitoring

✅ Urgence : VACUUM FREEZE manuel si table approche limite

✅ Protection finale : PostgreSQL refuse les transactions pour protéger les données

Conclusion

Le Transaction Wraparound peut sembler effrayant, mais c'est un problème totalement maîtrisable avec :

  1. Autovacuum activé (ne JAMAIS désactiver)
  2. Monitoring régulier de age(datfrozenxid)
  3. Alertes proactives avant d'atteindre les seuils critiques
  4. Maintenance planifiée pour les très grandes tables

PostgreSQL vous protège avec plusieurs niveaux de défense :

  • Autovacuum automatique
  • VACUUM forcé à 200M transactions
  • Warnings dans les logs
  • Mode lecture seule en dernier recours

Avec une configuration appropriée et un monitoring minimal, vous ne devriez jamais rencontrer ce problème en production. Le wraparound n'est un danger que pour les bases de données mal maintenues ou avec autovacuum désactivé.

Votre meilleure défense : Laissez autovacuum faire son travail, et surveillez occasionnellement que tout va bien !

Prochaine étape : 19.4.4 - Corruption de données et Checksums

⏭️ Corruption de données et Checksums