Skip to content

13.4-configuration-autovacuum.md

Latest commit

 

History

History
1295 lines (997 loc) · 38.6 KB

13.4-configuration-autovacuum.md

File metadata and controls

1295 lines (997 loc) · 38.6 KB

🔝 Retour au Sommaire

16.13.4. Configuration Autovacuum

Introduction

L'autovacuum est l'un des mécanismes les plus critiques de PostgreSQL, mais aussi l'un des plus mal compris. Une mauvaise configuration de l'autovacuum peut causer des problèmes de performance catastrophiques : tables gonflées (bloat), requêtes lentes, et dans les cas extrêmes, un arrêt complet de la base de données (transaction ID wraparound).

Dans cette section, nous allons comprendre :

  • Pourquoi PostgreSQL a besoin de VACUUM
  • Comment fonctionne l'autovacuum
  • Les paramètres de configuration essentiels
  • Comment surveiller et diagnostiquer les problèmes
  • Les nouveautés de PostgreSQL 18

⚠️ Avertissement : L'autovacuum n'est pas optionnel. C'est un mécanisme de maintenance vital pour la santé de votre base de données. Ne jamais le désactiver en production !

Comprendre le Problème : Pourquoi VACUUM existe

Le Modèle MVCC de PostgreSQL

PostgreSQL utilise un système appelé MVCC (Multi-Version Concurrency Control) qui est au cœur de sa gestion de la concurrence. Pour comprendre l'autovacuum, il faut d'abord comprendre MVCC.

Principe de MVCC

Contrairement à d'autres systèmes de bases de données, PostgreSQL ne modifie jamais une ligne existante lors d'un UPDATE ou DELETE. À la place :

  • UPDATE : PostgreSQL crée une nouvelle version de la ligne
  • DELETE : PostgreSQL marque l'ancienne ligne comme "morte" mais ne la supprime pas physiquement

💡 Analogie : Imaginez un document Word. Quand vous modifiez un paragraphe, au lieu de l'effacer et de le réécrire, vous créez une nouvelle version du document complet. L'ancienne version reste disponible pour les lecteurs qui l'avaient ouverte. C'est ainsi que PostgreSQL gère les transactions concurrentes !

Exemple Concret

Soit une table simple :

CREATE TABLE users (id INT, name TEXT, email TEXT);  
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');

État initial :

Version 1: (1, 'Alice', 'alice@example.com') ← VISIBLE

Après un UPDATE :

UPDATE users SET email = 'alice.new@example.com' WHERE id = 1;

État sur disque :

Version 1: (1, 'Alice', 'alice@example.com') ← MORTE (dead tuple)  
Version 2: (1, 'Alice', 'alice.new@example.com') ← VISIBLE

PostgreSQL conserve les deux versions ! La version 1 est marquée comme "morte" mais occupe toujours de l'espace.

Le Problème : Les Tuples Morts (Dead Tuples)

Au fil du temps, avec des milliers d'UPDATE et DELETE, votre base accumule des tuples morts :

Table "users" sur disque:
[Tuple vivant] [Tuple mort] [Tuple mort] [Tuple vivant] [Tuple mort]
[Tuple mort] [Tuple mort] [Tuple vivant] [Tuple mort] [Tuple mort]
[Tuple mort] [Tuple mort] [Tuple mort] [Tuple vivant] [Tuple mort]

Conséquences :

  • 📈 Bloat : La table gonfle artificiellement (peut atteindre 10× la taille réelle)
  • 🐌 Performance : Les scans séquentiels lisent les tuples morts (inutilement)
  • 💾 Espace disque : Gaspillage d'espace
  • 📊 Index : Les index pointent aussi vers des tuples morts (gonflent également)

💡 Analogie : C'est comme une bibliothèque où on ne jette jamais les anciens livres. On empile les nouvelles éditions par-dessus les anciennes. Au bout d'un moment, la bibliothèque est remplie à 80% de vieux livres inutiles et on ne trouve plus rien !

La Solution : VACUUM et Autovacuum

Qu'est-ce que VACUUM ?

VACUUM est l'opération de nettoyage qui :

  1. Identifie les tuples morts
  2. Marque l'espace occupé comme réutilisable
  3. Met à jour les statistiques de la table
  4. Prévient le transaction ID wraparound (cas extrême)

Important : VACUUM ne retourne PAS immédiatement l'espace au système d'exploitation (sauf VACUUM FULL). Il marque juste l'espace comme réutilisable par PostgreSQL.

Types de VACUUM

Type Commande Action Verrouillage
Standard VACUUM Nettoie et marque espace réutilisable ✅ Aucun (concurrentiel)
Analyze VACUUM ANALYZE Nettoie + met à jour statistiques ✅ Aucun
Full VACUUM FULL Réécrit la table complètement ❌ EXCLUSIVE (bloquant)
Freeze VACUUM FREEZE Gèle les XIDs anciens ✅ Aucun

L'Autovacuum : Le Nettoyeur Automatique

Gérer manuellement VACUUM sur toutes les tables serait impossible. C'est pourquoi PostgreSQL a l'autovacuum : un système automatique qui lance des VACUUM en arrière-plan.

Fonctionnement :

┌─────────────────────────────────────────┐
│   PostgreSQL en fonctionnement          │
│                                         │
│  [Processus Autovacuum Launcher]        │
│           ↓                             │
│  Détecte tables ayant besoin VACUUM     │
│           ↓                             │
│  Lance Autovacuum Workers (max 3)       │
│           ↓                             │
│  [Worker 1] → Table A                   │
│  [Worker 2] → Table B                   │
│  [Worker 3] → Table C                   │
└─────────────────────────────────────────┘

💡 Analogie : L'autovacuum, c'est comme un robot-aspirateur qui se déclenche automatiquement quand il détecte trop de poussière. Vous n'avez pas besoin d'y penser, il s'active tout seul en arrière-plan.

Les Déclencheurs d'Autovacuum

Quand l'Autovacuum se Déclenche-t-il ?

L'autovacuum évalue chaque table et décide de lancer un VACUUM selon cette formule :

Seuil de déclenchement = autovacuum_vacuum_threshold +
                         (autovacuum_vacuum_scale_factor × nombre_tuples_table)

Autovacuum se déclenche quand :

nombre_tuples_morts > Seuil de déclenchement

Les Paramètres de Déclenchement

autovacuum_vacuum_threshold

autovacuum_vacuum_threshold = 50  # Défaut

Signification : Nombre minimum de tuples morts avant de considérer un VACUUM.

Exemple :

  • Petite table de 100 lignes avec 40 tuples morts → Pas de VACUUM (< 50)
  • Petite table de 100 lignes avec 60 tuples morts → VACUUM déclenché (> 50)

autovacuum_vacuum_scale_factor

autovacuum_vacuum_scale_factor = 0.2  # Défaut = 20%

Signification : Fraction de la table devant être "morte" pour déclencher VACUUM.

Exemple avec une table de 10,000 lignes :

Seuil = 50 + (0.2 × 10,000) = 50 + 2,000 = 2,050 tuples morts

Donc autovacuum se déclenche quand il y a 2,050+ tuples morts.

Visualisation : Tables de Différentes Tailles

Taille Table Seuil (threshold=50, scale=0.2) % de la table
100 lignes 50 + 20 = 70 70%
1,000 lignes 50 + 200 = 250 25%
10,000 lignes 50 + 2,000 = 2,050 20.5%
100,000 lignes 50 + 20,000 = 20,050 20%
1,000,000 lignes 50 + 200,000 = 200,050 20%
10,000,000 lignes 50 + 2,000,000 = 2,000,050 20%

Observation : Pour les grandes tables, il faut que 20% de la table soit morte avant un VACUUM. C'est beaucoup !

Le Problème des Grosses Tables

Sur de très grosses tables (100+ millions de lignes), attendre 20% de tuples morts peut causer :

  • 📈 Bloat massif (tables gonflées de 30-50%)
  • 🐌 Dégradation progressive des performances
  • ⏱️ VACUUM très long quand il se déclenche enfin

Solution : Ajuster les paramètres pour les grosses tables.

Configuration des Paramètres Autovacuum

Paramètres Globaux (postgresql.conf)

1. autovacuum (Activation Globale)

autovacuum = on  # ⚠️ JAMAIS désactiver en production !

Valeurs :

  • on : Autovacuum actif ✅ (défaut)
  • off : Autovacuum désactivé ❌ (DANGER)

⚠️ DANGER CRITIQUE : Désactiver l'autovacuum peut causer un transaction ID wraparound au bout de quelques jours/semaines, entraînant un arrêt complet de la base. Ne JAMAIS désactiver en production.

2. autovacuum_max_workers

autovacuum_max_workers = 3  # Défaut

Signification : Nombre de processus autovacuum pouvant tourner simultanément.

Impact :

  • Trop peu de workers → Tables ne sont pas nettoyées assez vite
  • Trop de workers → Consommation I/O et CPU excessive

Recommandations :

Contexte Recommandation Explication
Petit serveur (< 1000 tables) 3 Défaut suffisant
Serveur moyen (1000-5000 tables) 5-8 Plus de tables = plus de workers
Gros serveur (5000+ tables) 10-15 Beaucoup de tables à gérer
Charge d'écriture intense 8-12 Beaucoup de tuples morts générés

🆕 PostgreSQL 18 : Nouveau paramètre complémentaire autovacuum_worker_slots (voir section nouveautés).

3. autovacuum_naptime

autovacuum_naptime = 1min  # Défaut

Signification : Intervalle entre deux cycles de détection par le launcher.

Impact :

  • Valeur basse (10s) → Détection rapide mais plus de CPU
  • Valeur haute (5min) → Moins de CPU mais réactivité moindre

Recommandations :

  • OLTP intensif : 30 secondes (détection rapide)
  • Standard : 1 minute (défaut)
  • Charge légère : 2-3 minutes

4. autovacuum_vacuum_threshold

autovacuum_vacuum_threshold = 50  # Défaut

Recommandations :

  • Tables petites/moyennes : 50 (défaut)
  • Tables très actives : 10-20 (plus agressif)
  • Tables stables : 100-200 (moins agressif)

5. autovacuum_vacuum_scale_factor

autovacuum_vacuum_scale_factor = 0.2  # Défaut = 20%

⚠️ Paramètre CRITIQUE : C'est souvent celui-ci qu'il faut ajuster !

Recommandations :

Taille Table Recommandation Explication
< 10,000 lignes 0.2 (20%) Défaut OK
10,000 - 100,000 lignes 0.1 (10%) Plus agressif
100,000 - 1M lignes 0.05 (5%) Beaucoup plus agressif
> 1M lignes 0.01-0.02 (1-2%) Très agressif
Tables TRÈS actives 0.01 (1%) Maximum agressivité

Exemple : Table de 10 millions de lignes

Avec scale_factor = 0.2 (défaut) :

Seuil = 50 + (0.2 × 10,000,000) = 2,000,050 tuples morts
→ 20% de la table doit être morte !

Avec scale_factor = 0.02 (ajusté) :

Seuil = 50 + (0.02 × 10,000,000) = 200,050 tuples morts
→ 2% de la table morte (beaucoup mieux)

6. autovacuum_analyze_threshold et autovacuum_analyze_scale_factor

autovacuum_analyze_threshold = 50  
autovacuum_analyze_scale_factor = 0.1  # Défaut = 10%

Rôle : Déclenchent ANALYZE (mise à jour des statistiques du planificateur).

Formule :

Seuil ANALYZE = analyze_threshold + (analyze_scale_factor × nombre_tuples)

Pourquoi 10% et non 20% : Les statistiques doivent être mises à jour plus fréquemment que le nettoyage.

Recommandations : Généralement, garder les défauts. Si plans de requêtes instables, réduire analyze_scale_factor à 0.05.

7. autovacuum_vacuum_cost_delay et autovacuum_vacuum_cost_limit

autovacuum_vacuum_cost_delay = 2ms   # Défaut  
autovacuum_vacuum_cost_limit = 200   # Défaut

Rôle : Contrôlent la "vitesse" de l'autovacuum pour éviter de saturer les I/O.

Fonctionnement :

  1. Autovacuum accumule des "points de coût" pour chaque I/O
  2. Quand il atteint cost_limit, il s'endort pendant cost_delay
  3. C'est un mécanisme de throttling (limitation)

Impact :

  • cost_delay élevé → Autovacuum lent, moins d'impact sur performances
  • cost_delay faible → Autovacuum rapide, mais peut ralentir les requêtes

Recommandations :

Contexte cost_delay cost_limit Explication
SSD Rapide + Peu de charge 0-1 ms 400-1000 Autovacuum rapide, pas de throttling
SSD Standard 2 ms 200 Défaut OK
HDD ou Charge intensive 5-10 ms 200 Throttling agressif
Nuit (fenêtre maintenance) 0 ms 2000 Pas de throttling

Configuration dynamique par période :

-- En journée (ralentir autovacuum)
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10;

-- La nuit (accélérer autovacuum)
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 2;

8. autovacuum_work_mem

autovacuum_work_mem = -1  # Défaut = utilise maintenance_work_mem

Rôle : Mémoire allouée à chaque worker autovacuum pour ses opérations.

Recommandations :

  • -1 : Utilise maintenance_work_mem (souvent OK)
  • Si bloat important : Augmenter à 256 MB - 1 GB

Calcul de sécurité :

Total mémoire autovacuum = autovacuum_work_mem × autovacuum_max_workers

Avec 3 workers et 512 MB chacun = 1.5 GB RAM maximum.

Configuration Par Table (Granularité Fine)

Pourquoi Configurer Par Table ?

Toutes les tables ne se comportent pas de la même façon :

  • Table logs : 1 million d'INSERT par jour, aucun UPDATE
  • Table users : Quelques INSERT, beaucoup d'UPDATE
  • Table cache : UPDATE constant, besoin VACUUM fréquent

Solution : Override des paramètres autovacuum par table.

Syntaxe ALTER TABLE

-- Modifier les paramètres autovacuum d'une table spécifique
ALTER TABLE nom_table SET (
    autovacuum_vacuum_threshold = valeur,
    autovacuum_vacuum_scale_factor = valeur,
    autovacuum_vacuum_cost_delay = valeur
);

Exemples Concrets

Exemple 1 : Grande Table Très Active

-- Table "orders" : 50 millions de lignes, 10,000 UPDATE/sec
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% au lieu de 20%
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_cost_delay = 0        -- Pas de throttling
);

Effet :

  • Avant : VACUUM se déclenchait à 10 millions de tuples morts (20%)
  • Après : VACUUM se déclenche à 510,000 tuples morts (1%)
  • Résultat : Bloat réduit de 70%, performances stables

Exemple 2 : Table de Cache avec Turnover Élevé

-- Table "session_cache" : Tuples ont une durée de vie courte
ALTER TABLE session_cache SET (
    autovacuum_vacuum_scale_factor = 0.02,  -- 2%
    autovacuum_vacuum_threshold = 50,
    autovacuum_vacuum_cost_delay = 0,       -- Rapide
    autovacuum_naptime = '30s'              -- Non supporté (global seulement)
);

Exemple 3 : Table Historique Append-Only

-- Table "audit_log" : INSERT seulement, jamais d'UPDATE/DELETE
ALTER TABLE audit_log SET (
    autovacuum_vacuum_scale_factor = 0.5,   -- 50% (très tolérant)
    autovacuum_vacuum_threshold = 10000,
    autovacuum_enabled = true               -- S'assurer qu'il est actif
);

Logique : Cette table génère peu de tuples morts, inutile de VACUUM souvent.

Exemple 4 : Désactiver Autovacuum (Cas Rare)

-- Table temporaire gérée manuellement
ALTER TABLE temp_import SET (
    autovacuum_enabled = false  -- ⚠️ À utiliser avec EXTRÊME prudence
);

⚠️ ATTENTION : Ne désactivez l'autovacuum sur une table QUE si :

  1. Vous gérez manuellement les VACUUM
  2. La table a un cycle de vie court (DROP à la fin)
  3. Vous comprenez les risques (XID wraparound)

Voir la Configuration Par Table

-- Voir les paramètres autovacuum d'une table
SELECT
    schemaname,
    tablename,
    reloptions
FROM pg_tables  
WHERE tablename = 'nom_table';  
-- Exemple de résultat
 tablename |              reloptions
-----------+--------------------------------------
 orders    | {autovacuum_vacuum_scale_factor=0.01,
           |  autovacuum_vacuum_threshold=10000}

Réinitialiser la Configuration Par Table

-- Retour aux valeurs globales
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);
-- Ou tout réinitialiser :
ALTER TABLE orders RESET (autovacuum_enabled);

Nouveautés PostgreSQL 18

PostgreSQL 18 introduit plusieurs améliorations majeures pour l'autovacuum :

1. autovacuum_worker_slots (Nouveau Paramètre)

autovacuum_max_workers = 3            # Nombre de workers simultanés  
autovacuum_worker_slots = 8           # 🆕 Total de slots disponibles

Différence importante :

  • Avant PG 18 : max_workers était fixe, si 3 workers étaient occupés sur de longues tables, d'autres tables attendaient
  • PG 18 : worker_slots permet de pré-allouer plus de capacité

Analogie :

  • max_workers = Nombre de caissiers actifs dans un supermarché
  • worker_slots = Nombre total de caisses disponibles

Bénéfice : Permet de gérer des pics de charge autovacuum sans bloquer les petites tables.

Recommandation :

autovacuum_max_workers = 5  
autovacuum_worker_slots = 10  # 2× max_workers

2. autovacuum_vacuum_max_threshold (Nouveau Paramètre)

autovacuum_vacuum_max_threshold = -1  # 🆕 Défaut = illimité

Rôle : Plafonne le nombre de tuples morts avant de FORCER un autovacuum, même si scale_factor n'est pas atteint.

Problème résolu : Sur tables géantes (1+ milliard de lignes), avec scale_factor = 0.01, le seuil peut atteindre 10 millions de tuples morts. C'est parfois trop.

Exemple :

# Limiter à 5 millions de tuples morts maximum
autovacuum_vacuum_max_threshold = 5000000

Cas d'usage : Tables ultra-massives où même 1-2% représente des millions de tuples.

3. Statistiques Autovacuum Enrichies (pg_stat_all_tables)

PostgreSQL 18 ajoute des colonnes à pg_stat_all_tables :

SELECT
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_tup_ins,          -- Insertions
    n_tup_upd,          -- Updates
    n_tup_del,          -- Deletions
    n_tup_hot_upd,      -- HOT updates (efficaces)
    n_live_tup,         -- Tuples vivants estimés
    n_dead_tup,         -- 🆕 Tuples morts RÉELS (précision améliorée)
    n_mod_since_analyze, -- Modifications depuis dernier ANALYZE
    last_autoanalyze,
    autoanalyze_count,
    -- 🆕 Nouvelles colonnes PostgreSQL 18
    autovacuum_vacuum_cost_accumulated,  -- Coût total accumulé
    autovacuum_last_duration              -- Durée du dernier autovacuum
FROM pg_stat_all_tables  
WHERE schemaname = 'public';

Bénéfice : Meilleure observabilité pour diagnostiquer les problèmes d'autovacuum.

4. Ajustements Dynamiques Plus Intelligents

PostgreSQL 18 améliore l'algorithme d'autovacuum :

  • Détection plus précise du bloat
  • Ajustement dynamique du cost_delay selon la charge système
  • Priorisation des tables critiques

Monitoring et Diagnostics Autovacuum

1. Vue d'Ensemble : Tables à Problème

-- Identifier les tables avec beaucoup de tuples morts
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_all_tables  
WHERE n_dead_tup > 1000  
ORDER BY n_dead_tup DESC  
LIMIT 20;

Interprétation :

  • dead_pct > 20% : ⚠️ Table a besoin d'un VACUUM
  • dead_pct > 50% : 🔴 Problème sérieux, ajuster config
  • last_autovacuum ancien : 🔴 Autovacuum n'arrive pas à traiter la table

2. Tables Jamais Nettoyées

-- Tables qui n'ont JAMAIS eu d'autovacuum
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    last_autovacuum
FROM pg_stat_all_tables  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
  AND last_autovacuum IS NULL
  AND n_live_tup > 0
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Action : Ces tables ont peut-être besoin d'ajustement de seuils.

3. Autovacuum En Cours

-- Voir les autovacuum actifs en ce moment
SELECT
    pid,
    usename,
    datname,
    query,
    state,
    query_start,
    now() - query_start AS duration
FROM pg_stat_activity  
WHERE query LIKE '%autovacuum%'  
  AND pid != pg_backend_pid();

4. Bloat Estimation (Gonflement des Tables)

-- Estimer le bloat d'une table (approximation)
WITH table_stats AS (
    SELECT
        schemaname,
        tablename,
        n_live_tup,
        n_dead_tup
    FROM pg_stat_all_tables
    WHERE schemaname = 'public'
),
table_sizes AS (
    SELECT
        schemaname,
        tablename,
        pg_total_relation_size(schemaname||'.'||tablename) AS total_bytes,
        pg_relation_size(schemaname||'.'||tablename) AS table_bytes
    FROM pg_tables
    WHERE schemaname = 'public'
)
SELECT
    ts.schemaname,
    ts.tablename,
    pg_size_pretty(sz.table_bytes) AS table_size,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    CASE
        WHEN n_live_tup + n_dead_tup = 0 THEN 0
        ELSE ROUND(sz.table_bytes::numeric / NULLIF(n_live_tup + n_dead_tup, 0), 2)
    END AS bytes_per_tuple,
    CASE
        WHEN (n_live_tup + n_dead_tup) > 0 AND
             (sz.table_bytes::numeric / (n_live_tup + n_dead_tup)) > 300
        THEN '⚠️ Bloat probable'
        ELSE '✅ OK'
    END AS bloat_status
FROM table_stats ts  
JOIN table_sizes sz USING (schemaname, tablename)  
WHERE n_live_tup + n_dead_tup > 0  
ORDER BY dead_pct DESC;

Interprétation :

  • bytes_per_tuple normal : 100-200 bytes (dépend du schéma)
  • bytes_per_tuple > 300 : Probablement du bloat
  • bytes_per_tuple > 500 : Bloat important

5. Historique des Autovacuum

-- Voir l'historique des autovacuum (nécessite log_autovacuum_min_duration)
-- Dans postgresql.conf :
-- log_autovacuum_min_duration = 0

Dans les logs PostgreSQL :

automatic vacuum of table "mydb.public.orders":
    index scans: 1
    pages: 0 removed, 823421 remain, 12453 skipped due to pins
    tuples: 1234567 removed, 45678901 remain, 0 are dead but not yet removable
    buffer usage: 1567890 hits, 234567 misses, 123456 dirtied
    avg read rate: 12.345 MB/s, avg write rate: 5.678 MB/s
    system usage: CPU 12.34s/23.45s, elapsed 456.78s

6. Dashboard Complet

-- Vue complète pour monitoring quotidien
SELECT
    stat.schemaname,
    stat.tablename,
    pg_size_pretty(pg_total_relation_size(stat.schemaname||'.'||stat.tablename)) AS total_size,
    stat.n_live_tup AS live,
    stat.n_dead_tup AS dead,
    ROUND(100.0 * stat.n_dead_tup / NULLIF(stat.n_live_tup + stat.n_dead_tup, 0), 2) AS dead_pct,
    stat.last_autovacuum,
    stat.autovacuum_count AS av_count,
    COALESCE(
        EXTRACT(epoch FROM (now() - stat.last_autovacuum)) / 3600,
        999999
    ) AS hours_since_av,
    -- Calculer le seuil théorique
    (
        SELECT COALESCE(
            (regexp_match(reloptions::text, 'autovacuum_vacuum_threshold=([0-9]+)'))[1]::int,
            50
        ) +
        COALESCE(
            (regexp_match(reloptions::text, 'autovacuum_vacuum_scale_factor=([0-9.]+)'))[1]::numeric,
            0.2
        ) * stat.n_live_tup
        FROM pg_class c
        WHERE c.oid = (stat.schemaname||'.'||stat.tablename)::regclass
    ) AS threshold,
    CASE
        WHEN stat.n_dead_tup > (
            SELECT COALESCE(
                (regexp_match(reloptions::text, 'autovacuum_vacuum_threshold=([0-9]+)'))[1]::int, 50
            ) +
            COALESCE(
                (regexp_match(reloptions::text, 'autovacuum_vacuum_scale_factor=([0-9.]+)'))[1]::numeric, 0.2
            ) * stat.n_live_tup
            FROM pg_class c
            WHERE c.oid = (stat.schemaname||'.'||stat.tablename)::regclass
        )
        THEN '🟢 Devrait être vacuum bientôt'
        WHEN stat.n_dead_tup::float / NULLIF(stat.n_live_tup, 0) > 0.3
        THEN '🔴 Problème : Trop de tuples morts'
        ELSE '✅ OK'
    END AS status
FROM pg_stat_all_tables stat  
WHERE stat.schemaname NOT IN ('pg_catalog', 'information_schema')  
  AND stat.n_live_tup > 0
ORDER BY stat.n_dead_tup DESC  
LIMIT 30;

Troubleshooting : Problèmes Courants

Problème 1 : Autovacuum Ne Se Déclenche Jamais

Symptômes :

  • Tables gonflent continuellement
  • last_autovacuum est NULL ou très ancien
  • Requêtes de plus en plus lentes

Causes Possibles :

Cause 1.1 : Autovacuum Désactivé Globalement

SHOW autovacuum;
-- Si "off" → PROBLÈME CRITIQUE

Solution :

# Dans postgresql.conf
autovacuum = on  # TOUJOURS

sudo systemctl restart postgresql

Cause 1.2 : Autovacuum Désactivé Sur la Table

SELECT reloptions  
FROM pg_class  
WHERE relname = 'ma_table';  
-- Si contient "autovacuum_enabled=false" → Problème

Solution :

ALTER TABLE ma_table SET (autovacuum_enabled = true);

Cause 1.3 : Seuils Trop Élevés

Sur une table de 100 millions de lignes, avec défauts :

Seuil = 50 + (0.2 × 100,000,000) = 20,000,050

Il faut 20 millions de tuples morts ! C'est trop.

Solution :

ALTER TABLE ma_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1% au lieu de 20%
    autovacuum_vacuum_threshold = 10000
);

Cause 1.4 : Transactions Longues Bloquent Autovacuum

Une transaction ouverte depuis longtemps empêche VACUUM de nettoyer.

-- Identifier les transactions longues
SELECT
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start AS duration,
    query
FROM pg_stat_activity  
WHERE state IN ('idle in transaction', 'active')  
  AND (now() - query_start) > interval '10 minutes'
ORDER BY query_start;

Solution : Terminer les transactions longues :

-- Terminer gentiment
SELECT pg_cancel_backend(pid);

-- Si pas de réponse, terminer brutalement
SELECT pg_terminate_backend(pid);

Problème 2 : Autovacuum Trop Lent

Symptômes :

  • Autovacuum se déclenche mais met des heures
  • Tables restent gonflées pendant longtemps
  • Queues de tuples morts s'accumulent

Causes et Solutions :

Cause 2.1 : Throttling Trop Agressif

SHOW autovacuum_vacuum_cost_delay;
-- Si 10ms ou plus → très lent

Solution :

autovacuum_vacuum_cost_delay = 2ms    # Défaut  
autovacuum_vacuum_cost_limit = 400    # Double du défaut

Cause 2.2 : Pas Assez de Workers

SHOW autovacuum_max_workers;
-- Si 3 workers pour 5000 tables → insuffisant

Solution :

autovacuum_max_workers = 8  
autovacuum_worker_slots = 16  # PostgreSQL 18

Cause 2.3 : autovacuum_work_mem Trop Faible

SHOW autovacuum_work_mem;
-- Si -1, vérifie maintenance_work_mem
SHOW maintenance_work_mem;
-- Si < 256MB → peut-être insuffisant

Solution :

autovacuum_work_mem = 512MB  # Ou plus selon RAM disponible

Problème 3 : Transaction ID Wraparound Warning

Symptôme : Log alarmant :

WARNING: database "mydb" must be vacuumed within 1234567 transactions

Explication : PostgreSQL utilise des XIDs (Transaction IDs) sur 32 bits = 2 milliards de valeurs. Si on ne VACUUM pas, on risque le wraparound (réutilisation des IDs anciens = corruption).

Gravité : 🔴 CRITIQUE - Si non traité, la base s'arrête automatiquement !

Vérifier l'âge des bases :

SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2147483648 - age(datfrozenxid) AS transactions_until_wraparound,
    CASE
        WHEN age(datfrozenxid) > 2000000000 THEN '🔴 CRITIQUE'
        WHEN age(datfrozenxid) > 1500000000 THEN '🟠 URGENT'
        WHEN age(datfrozenxid) > 1000000000 THEN '🟡 Attention'
        ELSE '✅ OK'
    END AS status
FROM pg_database  
ORDER BY age(datfrozenxid) DESC;

Solution d'urgence :

-- VACUUM FREEZE sur toutes les tables (peut prendre des heures)
VACUUM FREEZE;

-- Ou table par table si besoin de prioriser
VACUUM FREEZE ma_table_critique;

Prévention :

# Forcer des autovacuum plus fréquents
autovacuum_freeze_max_age = 200000000  # Défaut 200M (OK)  
autovacuum_multixact_freeze_max_age = 400000000  # Défaut 400M

Problème 4 : Bloat Malgré Autovacuum

Symptôme : Autovacuum fonctionne mais les tables restent gonflées.

Cause : VACUUM marque l'espace comme réutilisable mais ne le retourne PAS à l'OS.

Vérification :

-- Comparer taille théorique vs réelle
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size_on_disk,
    n_live_tup,
    ROUND(pg_total_relation_size(schemaname||'.'||tablename)::numeric / NULLIF(n_live_tup, 0)) AS bytes_per_tuple
FROM pg_stat_all_tables  
WHERE schemaname = 'public'  
  AND n_live_tup > 0
ORDER BY bytes_per_tuple DESC;

Si bytes_per_tuple >> 200-300, il y a du bloat.

Solutions :

Option 1 : VACUUM FULL (Bloquant)

-- ⚠️ BLOQUE la table pendant toute l'opération
VACUUM FULL ma_table;

Inconvénient : Acquisition d'un verrou EXCLUSIVE (lecture/écriture bloquées).

Option 2 : pg_repack (Non Bloquant)

# Extension pg_repack (à installer)
sudo apt install postgresql-18-repack

# Utilisation
pg_repack -d mydb -t ma_table

Avantage : Réorganise la table sans verrous bloquants.

Option 3 : Prévention (Mieux)

Ajuster les seuils pour éviter l'accumulation :

ALTER TABLE ma_table SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- Plus agressif
    autovacuum_vacuum_cost_delay = 0        -- Plus rapide
);

Configurations Recommandées

Configuration Petit Serveur (Dev/Test, 8 GB RAM)

# Autovacuum - Petit serveur
autovacuum = on  
autovacuum_max_workers = 3  
autovacuum_naptime = 1min  
autovacuum_vacuum_threshold = 50  
autovacuum_vacuum_scale_factor = 0.2  
autovacuum_analyze_threshold = 50  
autovacuum_analyze_scale_factor = 0.1  
autovacuum_vacuum_cost_delay = 2ms  
autovacuum_vacuum_cost_limit = 200  
autovacuum_work_mem = -1  # Utilise maintenance_work_mem

Configuration Serveur Production OLTP (32 GB RAM, SSD)

# Autovacuum - Production OLTP optimisé
autovacuum = on  
autovacuum_max_workers = 6  
autovacuum_naptime = 30s              # Plus réactif  
autovacuum_vacuum_threshold = 50  
autovacuum_vacuum_scale_factor = 0.05  # Plus agressif  
autovacuum_analyze_threshold = 50  
autovacuum_analyze_scale_factor = 0.05  
autovacuum_vacuum_cost_delay = 1ms     # Plus rapide (SSD)  
autovacuum_vacuum_cost_limit = 400     # Double du défaut  
autovacuum_work_mem = 512MB  

# PostgreSQL 18
autovacuum_worker_slots = 12           # Double des workers

Configuration Data Warehouse (64 GB RAM, RAID SSD)

# Autovacuum - Data Warehouse
autovacuum = on  
autovacuum_max_workers = 8  
autovacuum_naptime = 1min  
autovacuum_vacuum_threshold = 1000     # Tables massives  
autovacuum_vacuum_scale_factor = 0.02  # 2% seulement  
autovacuum_analyze_threshold = 1000  
autovacuum_analyze_scale_factor = 0.05  
autovacuum_vacuum_cost_delay = 0       # Pas de throttling (fenêtre nuit)  
autovacuum_vacuum_cost_limit = 1000  
autovacuum_work_mem = 2GB              # Beaucoup de mémoire  

# PostgreSQL 18
autovacuum_worker_slots = 16  
autovacuum_vacuum_max_threshold = 10000000  # Plafonner à 10M tuples morts

Configuration Haute Charge (Serveur E-commerce, 100+ GB RAM)

# Autovacuum - Très haute charge
autovacuum = on  
autovacuum_max_workers = 10  
autovacuum_naptime = 20s               # Très réactif  
autovacuum_vacuum_threshold = 100  
autovacuum_vacuum_scale_factor = 0.01  # 1% très agressif  
autovacuum_analyze_threshold = 50  
autovacuum_analyze_scale_factor = 0.02  
autovacuum_vacuum_cost_delay = 0       # Aucun throttling  
autovacuum_vacuum_cost_limit = 2000  
autovacuum_work_mem = 1GB  

# PostgreSQL 18
autovacuum_worker_slots = 20  
autovacuum_vacuum_max_threshold = 5000000

Stratégies Avancées

1. Autovacuum par Plage Horaire

Problème : Autovacuum consomme des ressources en pleine journée.

Solution : Script pour ajuster dynamiquement selon l'heure.

#!/bin/bash
# /etc/cron.d/autovacuum-tuning

# À 8h (début journée) : ralentir autovacuum
0 8 * * * postgres psql -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10; SELECT pg_reload_conf();"

# À 20h (fin journée) : accélérer autovacuum
0 20 * * * postgres psql -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 1; SELECT pg_reload_conf();"

# À 23h (nuit) : mode agressif
0 23 * * * postgres psql -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0; SELECT pg_reload_conf();"

2. VACUUM Manuel Complémentaire

Pour les tables critiques, combiner autovacuum + VACUUM manuel programmé :

#!/bin/bash
# VACUUM nocturne des tables critiques

# Chaque nuit à 2h
psql -d production -c "VACUUM ANALYZE orders;"  
psql -d production -c "VACUUM ANALYZE customers;"  
psql -d production -c "VACUUM ANALYZE transactions;"

Avantage : Garantir que les tables importantes sont toujours propres.

3. Monitoring et Alerting

Métriques à Surveiller :

  1. Tables avec > 20% tuples morts
-- Alerte si dead_pct > 20%
SELECT COUNT(*)  
FROM pg_stat_all_tables  
WHERE n_dead_tup::float / NULLIF(n_live_tup, 0) > 0.2;
  1. Tables jamais nettoyées depuis 24h
-- Alerte si last_autovacuum > 24h
SELECT COUNT(*)  
FROM pg_stat_all_tables  
WHERE last_autovacuum < now() - interval '24 hours'  
  AND n_live_tup > 1000;
  1. Transaction ID Age
-- Alerte si age > 1 milliard
SELECT COUNT(*)  
FROM pg_database  
WHERE age(datfrozenxid) > 1000000000;

Intégration Prometheus :

# prometheus.yml
- job_name: 'postgresql'
  static_configs:
    - targets: ['localhost:9187']
  metrics_path: /metrics

# Alertes
groups:
- name: postgresql_autovacuum
  rules:
  - alert: HighDeadTuples
    expr: pg_stat_user_tables_n_dead_tup / pg_stat_user_tables_n_live_tup > 0.2
    for: 1h
    annotations:
      summary: "Table {{ $labels.relname }} has high dead tuple ratio"

Checklist Autovacuum

✅ Configuration Minimale (Obligatoire)

  • autovacuum = on (TOUJOURS)
  • log_autovacuum_min_duration = 0 (pour monitoring)
  • Ajuster autovacuum_vacuum_scale_factor pour grandes tables
  • Vérifier régulièrement pg_stat_all_tables

✅ Configuration Production Standard

  • autovacuum_max_workers ajusté selon nombre de tables
  • autovacuum_naptime réduit pour bases actives
  • autovacuum_vacuum_cost_delay optimisé pour SSD
  • Configuration par table pour tables critiques
  • Monitoring avec alertes

✅ Configuration Avancée

  • PostgreSQL 18 : autovacuum_worker_slots configuré
  • PostgreSQL 18 : autovacuum_vacuum_max_threshold pour très grandes tables
  • autovacuum_work_mem augmenté si RAM disponible
  • Scripts de tuning par plage horaire
  • pg_repack installé pour maintenance d'urgence
  • Dashboard monitoring temps réel

Résumé et Points Clés

🎯 L'Essentiel à Retenir

  1. Autovacuum est OBLIGATOIRE

    • Ne JAMAIS désactiver autovacuum = off en production
    • Risque de bloat extrême et de transaction ID wraparound

  2. Le Paramètre Critique : autovacuum_vacuum_scale_factor

    • Défaut 0.2 (20%) est trop élevé pour grandes tables
    • Réduire à 0.01-0.05 (1-5%) pour tables > 1M lignes

  3. Configuration Par Table

    • Une taille unique ne convient pas à toutes les tables
    • Utiliser ALTER TABLE ... SET (autovacuum_...) pour tables spécifiques

  4. Nouveautés PostgreSQL 18

    • autovacuum_worker_slots : Meilleure gestion des pics
    • autovacuum_vacuum_max_threshold : Plafonner les seuils extrêmes
    • Statistiques enrichies pour meilleur monitoring

  5. Monitoring Continu

    • Surveiller n_dead_tup et dead_pct
    • Vérifier last_autovacuum régulièrement
    • Alertes sur XID age > 1 milliard

  6. En Cas de Problème

    • Bloat : VACUUM FULL (bloquant) ou pg_repack (non bloquant)
    • Transactions longues : Identifier et terminer
    • XID wraparound : VACUUM FREEZE d'urgence

📊 Formule de Déclenchement (Rappel)

Autovacuum se déclenche quand :  
n_dead_tup > (threshold + scale_factor × n_live_tup)  

Défaut : 50 + 0.2 × n_live_tup  
Recommandé grandes tables : 10000 + 0.01 × n_live_tup

🔍 Requête de Sanity Check Quotidien

SELECT
    schemaname || '.' || tablename AS table,
    n_live_tup AS live,
    n_dead_tup AS dead,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum,
    CASE
        WHEN n_dead_tup::float / NULLIF(n_live_tup, 0) > 0.2 THEN '🔴 URGENT'
        WHEN n_dead_tup::float / NULLIF(n_live_tup, 0) > 0.1 THEN '🟠 Attention'
        ELSE '✅ OK'
    END AS status
FROM pg_stat_all_tables  
WHERE schemaname = 'public' AND n_live_tup > 0  
ORDER BY dead_pct DESC NULLS LAST  
LIMIT 20;

Pour Aller Plus Loin

Documentation Officielle

Articles Techniques

  • "Understanding Autovacuum" - Percona Blog
  • "Tuning Autovacuum for High-Throughput Workloads" - 2ndQuadrant
  • "PostgreSQL Bloat and VACUUM Deep Dive" - CrunchyData

Outils

  • pg_stat_all_tables : Vue système principale
  • pgstattuple : Extension pour analyse précise du bloat
  • pg_repack : Réorganisation non bloquante
  • pgBadger : Analyse logs autovacuum
  • check_postgres : Monitoring et alerting

Extensions Utiles

-- Analyse précise du bloat
CREATE EXTENSION pgstattuple;  
SELECT * FROM pgstattuple('ma_table');  

-- Estimation rapide du bloat
CREATE EXTENSION pg_freespacemap;

Conclusion

L'autovacuum est le système immunitaire de PostgreSQL. Sans lui, votre base de données se détériore progressivement jusqu'à devenir inutilisable. Mais mal configuré, il peut être inefficace ou consommer trop de ressources.

Les clés du succès :

  1. ✅ Ne jamais désactiver l'autovacuum
  2. 🎯 Ajuster scale_factor selon la taille des tables
  3. 👀 Monitorer régulièrement avec pg_stat_all_tables
  4. ⚡ Profiter des nouveautés PostgreSQL 18 pour plus de flexibilité
  5. 🔧 Configuration par table pour les cas spécifiques

Avec une configuration appropriée et un monitoring attentif, l'autovacuum maintient votre base de données en excellente santé, sans intervention manuelle constante.

Prochaine étape : Dans la section suivante, nous explorerons les stratégies de sauvegarde et restauration, compléments essentiels de l'autovacuum pour garantir la durabilité de vos données !

⏭️ PGTune et outils d'aide à la configuration