Skip to content

07.5-pg-repack.md

Latest commit

 

History

History
1300 lines (954 loc) · 36.7 KB

07.5-pg-repack.md

File metadata and controls

1300 lines (954 loc) · 36.7 KB

🔝 Retour au Sommaire

18.7.5. pg_repack : Réorganisation Sans Verrous dans PostgreSQL

Introduction

pg_repack est une extension PostgreSQL qui permet de réorganiser et compacter des tables et index en ligne, c'est-à-dire sans bloquer les opérations de lecture et d'écriture pendant le processus. C'est une alternative moderne et sûre à VACUUM FULL et CLUSTER.

Le Problème : Le Bloat (Gonflement) des Tables

Au fil du temps, les tables PostgreSQL peuvent devenir gonflées (bloated) à cause du fonctionnement de MVCC (Multiversion Concurrency Control).

Qu'est-ce que le Bloat ?

Le bloat est l'accumulation d'espace mort (dead space) dans une table ou un index :

Table au départ (10 000 lignes, 1 Mo)
├─ Données utiles : 100%
└─ Espace mort : 0%

Après 1 an d'UPDATE/DELETE (toujours 10 000 lignes visibles)
├─ Données utiles : 40%  ← Seulement 400 Ko de données utiles
└─ Espace mort : 60%     ← 600 Ko d'anciennes versions et trous
                           Taille totale : 2.5 Mo au lieu de 1 Mo

Pourquoi le bloat se produit-il ?

PostgreSQL utilise MVCC (voir tutoriel sur les transactions). Quand vous faites un UPDATE ou DELETE :

  1. UPDATE : L'ancienne ligne n'est pas supprimée immédiatement, une nouvelle version est créée
  2. DELETE : La ligne est marquée comme supprimée mais reste physiquement présente
  3. VACUUM : Nettoie l'espace mort, mais ne le rend pas au système d'exploitation

Conséquence : La table occupe plus d'espace que nécessaire, ce qui ralentit :

  • Les scans séquentiels (plus de pages à lire)
  • Les sauvegardes (plus de données à copier)
  • La réplication (plus de WAL à transférer)

Analogie : Imaginez une bibliothèque où vous ne jetez jamais les vieux livres, vous les marquez juste comme "obsolète" et ajoutez les nouvelles versions sur de nouvelles étagères. Avec le temps, la bibliothèque devient énorme et il faut marcher longtemps pour trouver les livres actuels.

Les Solutions Traditionnelles (Imparfaites)

1. VACUUM (normal)

VACUUM customers;

Ce que ça fait :

  • Nettoie l'espace mort
  • Marque l'espace comme réutilisable
  • Ne rend PAS l'espace au système d'exploitation
  • N'optimise pas l'ordre des données

Avantage : Pas de verrous exclusifs, fonctionne en ligne
Inconvénient : Ne réduit pas la taille du fichier de la table

2. VACUUM FULL

VACUUM FULL customers;

Ce que ça fait :

  • Reconstruit complètement la table
  • Élimine tout le bloat
  • Rend l'espace au système d'exploitation

Avantage : Compactage maximal
Inconvénients :

  • ❌ Verrou exclusif (ACCESS EXCLUSIVE) : Bloque TOUTES les opérations (SELECT, INSERT, UPDATE, DELETE)
  • ❌ Très lent sur grandes tables (peut prendre des heures)
  • ❌ Nécessite 2× l'espace disque temporairement
  • ❌ Inutilisable en production pour des tables actives

3. CLUSTER

CLUSTER customers USING idx_customers_pk;

Ce que ça fait :

  • Réorganise physiquement la table selon un index
  • Élimine le bloat
  • Optimise l'ordre des données (améliore les scans séquentiels)

Avantages : Compactage + optimisation de l'ordre
Inconvénients :

  • ❌ Verrou exclusif : Même problème que VACUUM FULL
  • ❌ Très lent
  • ❌ Inutilisable en production

La Solution : pg_repack

pg_repack fait la même chose que VACUUM FULL ou CLUSTER, mais sans bloquer l'application :

  • ✅ Pas de verrou exclusif : Les SELECT, INSERT, UPDATE, DELETE continuent pendant le repack
  • ✅ En ligne : Transparence totale pour l'application
  • ✅ Sûr : Mécanisme transactionnel, rollback en cas d'erreur
  • ✅ Efficace : Compactage complet et réorganisation
  • ✅ Flexible : Peut réorganiser tables, index, ou les deux

Limitation : Nécessite un verrou exclusif très bref (quelques millisecondes) au début et à la fin.

Architecture et Fonctionnement

Comment pg_repack Fonctionne-t-il ?

pg_repack utilise une approche en plusieurs phases pour réorganiser une table tout en maintenant la disponibilité.

Phase 1 : Préparation (Verrou Bref)

┌─────────────────────────────────┐
│  Table Originale: customers     │
│  (10 000 lignes, 2.5 Mo bloat)  │
└─────────────────────────────────┘
         ↓
[Verrou bref ~1-2 ms]
         ↓
┌─────────────────────────────────┐
│  Création de triggers           │
│  - INSERT trigger               │
│  - UPDATE trigger               │
│  - DELETE trigger               │
└─────────────────────────────────┘

Objectif : Installer des triggers pour capturer toutes les modifications pendant la copie.

Phase 2 : Copie des Données (Sans Verrou)

┌─────────────────────────────────┐
│  Table Originale: customers     │
│  (L'application continue à      │
│   lire/écrire normalement)      │
└─────────────────────────────────┘
         ↓ [Copie progressive]
┌─────────────────────────────────┐
│  Table Temporaire: repack_xxx   │
│  (Données compactées)           │
└─────────────────────────────────┘
         +
┌─────────────────────────────────┐
│  Table de Log: repack_log_xxx   │
│  (Capture toutes les modifs     │
│   pendant la copie via triggers)│
└─────────────────────────────────┘

Pendant cette phase :

  • pg_repack copie toutes les données de la table originale vers une table temporaire
  • Les triggers capturent toutes les modifications (INSERT/UPDATE/DELETE) dans une table de log
  • Aucun verrou exclusif : L'application fonctionne normalement

Phase 3 : Application des Modifications (Sans Verrou)

┌──────────────────────────────────┐
│  Table Temporaire: repack_xxx    │
│  (Données compactées + initiales)│
└──────────────────────────────────┘
         ↓ [Application du log]
┌─────────────────────────────────┐
│  Table Temporaire: repack_xxx   │
│  (Données à jour et compactées) │
└─────────────────────────────────┘

Objectif : Rejouer toutes les modifications capturées dans le log pour que la table temporaire soit à jour.

Phase 4 : Swap Final (Verrou Bref)

[Verrou exclusif bref ~10-100 ms]
         ↓
┌─────────────────────────────────┐
│  Swap des tables                │
│  - Table temporaire → customers │
│  - customers → old_customers    │
└─────────────────────────────────┘
         ↓
┌──────────────────────────────────┐
│  Nouvelle Table: customers       │
│  (10 000 lignes, 1 Mo, compactée)│
└──────────────────────────────────┘
         ↓
[Suppression de l'ancienne table]

Objectif : Échanger atomiquement les tables. L'ancienne table devient la nouvelle.

Verrou exclusif : Nécessaire pour garantir la cohérence, mais très bref (quelques dizaines de ms).

Schéma Récapitulatif

Timeline:
│
├─ T0: Début pg_repack
│  └─ Verrou exclusif: 1-2 ms (création triggers)
│
├─ T0+1s à T0+1h: Copie des données
│  └─ Aucun verrou, application fonctionne normalement
│  └─ Les modifications sont loguées via triggers
│
├─ T0+1h: Application des logs
│  └─ Aucun verrou, rattrapage des modifications
│
├─ T0+1h+5min: Swap final
│  └─ Verrou exclusif: 10-100 ms (échange des tables)
│
└─ T0+1h+6min: Fin, nettoyage

Détails Techniques

Triggers créés par pg_repack :

-- Trigger pour capturer les INSERT
CREATE TRIGGER repack_trigger_insert  
AFTER INSERT ON customers  
FOR EACH ROW EXECUTE FUNCTION repack_log_insert();  

-- Trigger pour capturer les UPDATE
CREATE TRIGGER repack_trigger_update  
AFTER UPDATE ON customers  
FOR EACH ROW EXECUTE FUNCTION repack_log_update();  

-- Trigger pour capturer les DELETE
CREATE TRIGGER repack_trigger_delete  
AFTER DELETE ON customers  
FOR EACH ROW EXECUTE FUNCTION repack_log_delete();

Ces triggers capturent toutes les modifications dans une table de log qui sera rejouée sur la table temporaire.

Installation et Configuration

Prérequis

  • PostgreSQL 9.4+ (idéalement PostgreSQL 18)
  • Droits superutilisateur : Pour l'installation de l'extension
  • Espace disque : Environ 1.5× la taille de la table à réorganiser
  • Extension disponible : Selon votre distribution PostgreSQL

Processus d'Installation (Théorique)

Étape 1 : Installer le binaire pg_repack

pg_repack est un outil en ligne de commande, pas juste une extension SQL.

Sur Linux (Debian/Ubuntu) :

sudo apt-get install postgresql-18-repack

Sur Linux (RHEL/CentOS) :

sudo yum install pg_repack18

Compilation depuis les sources :

git clone https://github.com/reorg/pg_repack.git  
cd pg_repack  
make  
sudo make install

Étape 2 : Créer l'extension dans la base de données

-- Se connecter à la base de données cible
CREATE EXTENSION pg_repack;

Vérification :

-- Vérifier que l'extension est active
SELECT * FROM pg_extension WHERE extname = 'pg_repack';

-- Vérifier que le binaire est accessible
-- (depuis le shell)
pg_repack --version

Étape 3 : Permissions

L'utilisateur qui exécute pg_repack doit avoir :

-- Option 1 : Superutilisateur (recommandé pour simplicité)
ALTER USER repack_user WITH SUPERUSER;

-- Option 2 : Permissions minimales (plus sécurisé)
GRANT USAGE ON SCHEMA public TO repack_user;  
GRANT ALL ON ALL TABLES IN SCHEMA public TO repack_user;  
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO repack_user;  
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO repack_user;

Note : pg_repack nécessite des permissions élevées car il crée des objets temporaires et modifie des métadonnées.

Utilisation de Base

Syntaxe Générale

pg_repack est un outil en ligne de commande, pas une commande SQL.

Format :

pg_repack [OPTIONS] [dbname]

Réorganiser une Table Spécifique

# Syntaxe de base
pg_repack -d mydatabase -t customers

# Avec utilisateur et hôte
pg_repack -h localhost -p 5432 -U postgres -d mydatabase -t customers

# Avec schéma explicite
pg_repack -d mydatabase -t public.customers

Options principales :

Option Description
-d, --dbname Nom de la base de données
-t, --table Nom de la table à réorganiser
-h, --host Hôte PostgreSQL (défaut: localhost)
-p, --port Port PostgreSQL (défaut: 5432)
-U, --username Utilisateur PostgreSQL
-n, --schema Schéma spécifique
-k, --no-superuser-check Ignorer la vérification superutilisateur

Réorganiser Plusieurs Tables

# Tables spécifiques (séparées par des virgules)
pg_repack -d mydatabase -t customers,orders,products

# Toutes les tables d'un schéma
pg_repack -d mydatabase -n public

# Toutes les tables de la base (attention : long !)
pg_repack -d mydatabase --all

Réorganiser Uniquement les Index

Par défaut, pg_repack réorganise la table ET ses index. Pour réorganiser uniquement les index :

# Réorganiser uniquement les index d'une table
pg_repack -d mydatabase -t customers --only-indexes

# Réorganiser un index spécifique
pg_repack -d mydatabase -i idx_customers_email

Réorganiser avec CLUSTER (Ordre Physique)

Pour réorganiser une table selon l'ordre d'un index (équivalent de CLUSTER) :

# Réorganiser selon un index
pg_repack -d mydatabase -t orders -o order_date

# Équivalent de : CLUSTER orders USING idx_orders_date;
# Mais sans verrou exclusif !

Avantage : Améliore les performances des scans séquentiels et des requêtes range-based.

Options Avancées

# Mode verbeux (affiche les détails)
pg_repack -d mydatabase -t customers --verbose

# Exécution à vide (dry-run, pour tester)
pg_repack -d mydatabase -t customers --dry-run

# Spécifier un tablespace pour les tables temporaires
pg_repack -d mydatabase -t customers --tablespace fast_ssd

# Limiter le nombre de connexions
pg_repack -d mydatabase -t customers --jobs 2

# Attendre la fin des transactions en cours (au lieu d'échouer)
pg_repack -d mydatabase -t customers --wait-timeout 600  # 10 minutes

Cas d'Usage Détaillés

1. Réduire le Bloat d'une Table Active

Scénario : Table orders avec 50% de bloat, utilisée 24/7.

Diagnostic :

-- Vérifier le bloat (requête simplifiée)
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    ROUND(100 * (pg_relation_size(schemaname||'.'||tablename)::numeric /
          NULLIF(pg_total_relation_size(schemaname||'.'||tablename), 0)), 2) as table_percent
FROM pg_tables  
WHERE tablename = 'orders';  

-- Estimer le bloat (extension pgstattuple recommandée)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    schemaname,
    tablename,
    pg_size_pretty(table_len) as table_size,
    ROUND(dead_tuple_percent, 2) as dead_tuple_percent,
    pg_size_pretty(dead_tuple_len) as wasted_space
FROM pgstattuple('orders')  
JOIN pg_tables ON pg_tables.schemaname = 'public' AND pg_tables.tablename = 'orders';

Résultat hypothétique :

table_size: 5 GB  
dead_tuple_percent: 48%  
wasted_space: 2.4 GB

Action :

# Réorganiser la table avec pg_repack
pg_repack -h localhost -U postgres -d production -t orders --verbose

# Résultat attendu après repack :
# table_size: 2.6 GB (réduction de ~50%)
# dead_tuple_percent: 0%

Vérification post-repack :

-- Vérifier la taille après
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- Vérifier qu'il n'y a plus de bloat
SELECT * FROM pgstattuple('orders');

2. Réorganiser pour Améliorer les Performances (CLUSTER-like)

Scénario : Table events avec des requêtes fréquentes par plage de dates.

-- Requête typique
SELECT * FROM events  
WHERE event_date >= '2025-01-01'  
  AND event_date < '2025-02-01';

Problème : Les données ne sont pas ordonnées physiquement par event_date, causant des scans inefficaces.

Solution : Réorganiser la table selon l'ordre de event_date.

# Créer d'abord un index si nécessaire
psql -d production -c "CREATE INDEX IF NOT EXISTS idx_events_date ON events(event_date);"

# Réorganiser selon cet index
pg_repack -d production -t events -o event_date --verbose

Impact :

  • Avant : Scan séquentiel avec beaucoup d'I/O random
  • Après : Scan séquentiel optimisé, I/O séquentiel (plus rapide)

Vérification :

EXPLAIN (ANALYZE, BUFFERS)  
SELECT * FROM events  
WHERE event_date >= '2025-01-01'  
  AND event_date < '2025-02-01';

-- Vérifier le nombre de buffers lus
-- Avant: shared buffers read: 50000
-- Après: shared buffers read: 15000 (amélioration ~70%)

3. Maintenance Régulière Automatisée

Scénario : Maintenance hebdomadaire de tables critiques.

#!/bin/bash
# Script: weekly_repack.sh

DBNAME="production"  
TABLES=("orders" "customers" "products")  

for table in "${TABLES[@]}"; do
    echo "Repacking $table..."
    pg_repack -h localhost -U postgres -d $DBNAME -t $table --verbose

    if [ $? -eq 0 ]; then
        echo "$table: SUCCESS"
    else
        echo "$table: FAILED"
        # Envoyer une alerte
        send_alert "pg_repack failed for $table"
    fi
done

Planification avec cron :

# Exécuter tous les dimanches à 2h du matin
0 2 * * 0 /scripts/weekly_repack.sh >> /var/log/pg_repack.log 2>&1

Ou avec pg_cron (depuis PostgreSQL) :

-- Job pg_cron pour exécuter pg_repack
SELECT cron.schedule(
    'weekly_repack_orders',
    '0 2 * * 0',  -- Dimanches à 2h
    $$
    -- Nécessite une fonction wrapper pour appeler pg_repack depuis SQL
    SELECT run_pg_repack_for_table('orders');
    $$
);

4. Réorganiser Après une Migration Massive

Scénario : Après un import massif ou une migration, les tables ont un mauvais facteur de remplissage.

# Après un gros import
psql -d newdb -c "COPY orders FROM '/data/orders.csv' WITH CSV HEADER;"

# Statistiques obsolètes et bloat potentiel
psql -d newdb -c "ANALYZE orders;"

# Réorganiser pour optimiser
pg_repack -d newdb -t orders --verbose

5. Réorganiser une Table Partitionnée

Important : pg_repack peut réorganiser chaque partition individuellement.

-- Lister les partitions
SELECT
    schemaname,
    tablename
FROM pg_tables  
WHERE tablename LIKE 'orders_p%'  
ORDER BY tablename;  
# Réorganiser chaque partition
pg_repack -d production -t orders_p2024_01 --verbose  
pg_repack -d production -t orders_p2024_02 --verbose  
pg_repack -d production -t orders_p2024_03 --verbose  

# Ou via un script
for partition in $(psql -d production -t -c "SELECT tablename FROM pg_tables WHERE tablename LIKE 'orders_p%'"); do
    pg_repack -d production -t $partition --verbose
done

Note : Ne pas réorganiser la table parent, seulement les partitions.

Surveillance et Monitoring

Suivre la Progression de pg_repack

Pendant l'exécution, pg_repack affiche sa progression (si --verbose) :

INFO: repacking table "public.customers"  
INFO: Setting up workers...  
INFO: [1/6] creating table to copy data  
INFO: [2/6] copying rows  
INFO: 500000 rows copied (5% complete)  
INFO: 1000000 rows copied (10% complete)  
...
INFO: [3/6] creating indexes  
INFO: [4/6] applying log  
INFO: [5/6] swapping relations  
INFO: [6/6] dropping old tables  
INFO: repack complete

Monitorer Depuis PostgreSQL

Pendant le repack, observer l'activité :

-- Voir les processus pg_repack actifs
SELECT
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    NOW() - query_start as duration
FROM pg_stat_activity  
WHERE application_name LIKE '%repack%'  
   OR query LIKE '%repack%';

-- Voir les verrous en attente
SELECT
    locktype,
    mode,
    granted,
    pid
FROM pg_locks  
WHERE NOT granted;  

-- Voir la taille des tables temporaires créées par pg_repack
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables  
WHERE tablename LIKE 'repack%'  
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Métriques à Surveiller

Métrique Commande Alerte si
Durée d'exécution Via --verbose > 4h (selon table)
Espace disque df -h < 20% libre
Connexions actives SELECT COUNT(*) FROM pg_stat_activity Proche de max_connections
Verrous bloquants SELECT * FROM pg_locks WHERE NOT granted > 0 pendant longtemps
I/O iostat -x 1 Saturation disque

Bonnes Pratiques

1. Planifier les Repack Pendant les Heures Creuses

✅ Pourquoi : Bien que pg_repack soit "online", il consomme des ressources (CPU, I/O, mémoire).

✅ Quand : Pendant les heures de faible activité (nuit, week-end).

Exception : Si le bloat est critique et affecte les performances, réorganiser immédiatement.

2. Vérifier l'Espace Disque Avant

✅ Règle : Avoir au moins 1.5× la taille de la table en espace libre.

# Vérifier l'espace avant
df -h /var/lib/postgresql

# Vérifier la taille de la table
psql -d production -c "SELECT pg_size_pretty(pg_total_relation_size('orders'));"

Exemple :

  • Table orders : 100 GB
  • Espace libre requis : ≥ 150 GB

Si insuffisant : Réorganiser les tables les plus petites d'abord, ou libérer de l'espace.

3. Tester sur un Clone Avant la Production

✅ Workflow recommandé :

1. Cloner la base de production (pg_dump/pg_restore)
2. Tester pg_repack sur le clone
3. Mesurer la durée et l'impact
4. Valider que tout fonctionne après
5. Planifier l'exécution en production

4. Utiliser --dry-run pour Valider

# Test à blanc (ne fait rien, juste vérifie la faisabilité)
pg_repack -d production -t orders --dry-run

# Si succès : Exécuter réellement
pg_repack -d production -t orders --verbose

5. Surveiller les Connexions Actives

pg_repack crée plusieurs connexions. Vérifier max_connections :

SHOW max_connections;
-- Si proche de la limite, augmenter ou limiter --jobs

-- Voir les connexions utilisées
SELECT COUNT(*) FROM pg_stat_activity;

Ajuster --jobs :

# Par défaut : 1 job (1 connexion principale + quelques auxiliaires)
pg_repack -d production -t orders --jobs 1

# Pour aller plus vite (si ressources disponibles)
pg_repack -d production -t orders --jobs 4

6. Réorganiser Régulièrement, Pas Ponctuellement

✅ Stratégie préventive : Réorganiser avant que le bloat ne devienne critique.

Exemple de calendrier :

Type de Table Fréquence Méthode
Très actives (OLTP) Mensuelle pg_repack
Modérément actives Trimestrielle pg_repack
Peu actives Annuelle VACUUM FULL acceptable

7. Combiner avec ANALYZE

Après un repack, mettre à jour les statistiques :

# Repack puis ANALYZE
pg_repack -d production -t orders --verbose

psql -d production -c "ANALYZE orders;"

Ou :

# pg_repack analyse automatiquement à la fin (depuis v1.4.6)
# Pas besoin d'ANALYZE manuel supplémentaire

8. Documenter les Maintenances

-- Créer une table de logs
CREATE TABLE monitoring.repack_log (
    log_id SERIAL PRIMARY KEY,
    table_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,
    size_before BIGINT,
    size_after BIGINT,
    duration INTERVAL,
    success BOOLEAN,
    error_message TEXT
);

-- Logger manuellement ou via script wrapper
INSERT INTO monitoring.repack_log
    (table_name, start_time, end_time, size_before, size_after, duration, success)
VALUES
    ('orders',
     '2025-11-23 02:00:00',
     '2025-11-23 03:45:00',
     107374182400,  -- 100 GB
     53687091200,   -- 50 GB
     '1 hour 45 minutes',
     TRUE);

Limitations et Considérations

Limitations de pg_repack

  1. Verrous exclusifs brefs

pg_repack nécessite deux verrous exclusifs très brefs :

  • Au début : ~1-10 ms (création des triggers)
  • À la fin : ~10-100 ms (swap des tables)

Impact : Pendant ces quelques ms, toutes les requêtes sont bloquées.

Mitigation : Utiliser --wait-timeout pour attendre la fin des transactions longues.

  1. Nécessite de l'espace disque

pg_repack crée une copie complète de la table (1.5× la taille).

Solution : Vérifier l'espace avant, ou utiliser un tablespace alternatif (--tablespace).

  1. Ne fonctionne pas avec toutes les tables

pg_repack ne peut pas réorganiser :

  • Tables sans clé primaire ou UNIQUE
  • Tables temporaires (TEMP)
  • Tables non-loggées (UNLOGGED) avant PostgreSQL 9.5
  • Tables système (pg_catalog)

Vérification :

-- Identifier les tables sans clé primaire
SELECT
    schemaname,
    tablename
FROM pg_tables t  
WHERE NOT EXISTS (  
    SELECT 1
    FROM pg_constraint c
    WHERE c.conrelid = (t.schemaname||'.'||t.tablename)::regclass
      AND c.contype IN ('p', 'u')  -- PRIMARY KEY ou UNIQUE
)
AND schemaname NOT IN ('pg_catalog', 'information_schema');

Solution : Ajouter une clé primaire avant de repack.

-- Ajouter une clé primaire
ALTER TABLE my_table ADD PRIMARY KEY (id);

-- Puis réorganiser
pg_repack -d production -t my_table
  1. Impact sur les Replicas

Le repack génère beaucoup de WAL (Write-Ahead Log), ce qui peut :

  • Saturer la bande passante réseau (réplication streaming)
  • Créer un retard (lag) sur les replicas

Mitigation :

  • Exécuter pendant les heures creuses
  • Augmenter max_wal_size temporairement
  • Surveiller le lag de réplication : SELECT * FROM pg_stat_replication;
  1. Tables avec beaucoup d'écritures

Si la table est très active en écriture pendant le repack, la table de log peut devenir énorme.

Symptôme : Le repack ne finit jamais (il n'arrive pas à rattraper les modifications).

Solution : Réduire le trafic en écriture temporairement, ou planifier pendant une fenêtre plus calme.

  1. Pas de rollback automatique

Si pg_repack échoue, il ne rollback pas automatiquement. Les objets temporaires peuvent rester.

Nettoyage manuel :

-- Identifier les objets résiduels
SELECT
    schemaname,
    tablename
FROM pg_tables  
WHERE tablename LIKE 'repack%';  

-- Supprimer manuellement
DROP TABLE IF EXISTS repack_123456;

Dépannage (Troubleshooting)

Problème 1 : ERROR: pg_repack failed with error: relation has no primary key

Cause : La table n'a pas de clé primaire ou d'index UNIQUE.

Solution :

-- Option 1 : Ajouter une clé primaire
ALTER TABLE my_table ADD PRIMARY KEY (id);

-- Option 2 : Ajouter un index UNIQUE
CREATE UNIQUE INDEX idx_my_table_unique ON my_table(id);

-- Puis réessayer pg_repack

Problème 2 : ERROR: pg_repack: query failed: ERROR: could not create unique index

Cause : La colonne choisie pour clé primaire contient des doublons ou des NULL.

Diagnostic :

-- Vérifier les doublons
SELECT id, COUNT(*)  
FROM my_table  
GROUP BY id  
HAVING COUNT(*) > 1;  

-- Vérifier les NULL
SELECT COUNT(*)  
FROM my_table  
WHERE id IS NULL;

Solution :

-- Nettoyer les doublons et NULL
-- Puis ajouter la clé primaire

Problème 3 : ERROR: pg_repack: query failed: ERROR: out of shared memory

Cause : Pas assez de mémoire partagée (shared_buffers ou max_locks_per_transaction).

Solution :

-- Augmenter temporairement
ALTER SYSTEM SET max_locks_per_transaction = 256;  -- Défaut: 64  
SELECT pg_reload_conf();  

-- Ou dans postgresql.conf
max_locks_per_transaction = 256
# Puis redémarrer PostgreSQL

Problème 4 : pg_repack prend trop de temps

Causes possibles :

  1. Table très volumineuse
  2. Beaucoup d'écritures concurrentes
  3. I/O lent

Diagnostics :

-- Vérifier l'activité I/O
SELECT * FROM pg_stat_database WHERE datname = 'production';

-- Vérifier les écritures sur la table
SELECT
    schemaname,
    tablename,
    n_tup_ins + n_tup_upd + n_tup_del as write_activity
FROM pg_stat_user_tables  
WHERE tablename = 'orders';

Solutions :

  • Planifier pendant une période plus calme
  • Augmenter --jobs pour paralléliser (si ressources disponibles)
  • Utiliser un tablespace sur SSD rapide

Problème 5 : Verrou en attente prolongé

Symptôme : pg_repack bloqué en attente d'un verrou.

WARNING: Waiting for a transaction to complete...

Cause : Une transaction longue empêche pg_repack d'acquérir le verrou exclusif final.

Diagnostic :

-- Identifier la transaction bloquante
SELECT
    pid,
    usename,
    state,
    NOW() - xact_start as xact_duration,
    query
FROM pg_stat_activity  
WHERE state != 'idle'  
  AND xact_start IS NOT NULL
ORDER BY xact_start;

Solutions :

# Option 1 : Attendre avec un timeout
pg_repack -d production -t orders --wait-timeout 600  # 10 minutes

# Option 2 : Terminer la transaction bloquante (attention !)
psql -d production -c "SELECT pg_terminate_backend(12345);"  -- PID de la transaction

Problème 6 : Espace disque insuffisant

Symptôme : ERROR: could not extend file

Cause : Plus d'espace disque pendant la copie.

Solution :

# Libérer de l'espace
# Ou utiliser un tablespace alternatif
pg_repack -d production -t orders --tablespace fast_storage

Comparaison avec Alternatives

pg_repack vs VACUUM FULL

Critère pg_repack VACUUM FULL
Verrou exclusif Très bref (~100 ms) Complet (heures)
Disponibilité Application continue Hors ligne
Vitesse Rapide Lent
Espace disque 1.5× 2× (temporairement)
Sécurité Haute (rollback possible) Moyenne
Complexité Extension externe Commande native

Verdict : pg_repack pour production, VACUUM FULL uniquement si repack impossible.

pg_repack vs CLUSTER

Critère pg_repack CLUSTER
Verrou exclusif Très bref Complet
Disponibilité Online Offline
Ordre physique Oui (avec -o) Oui
Optimisation Complète Complète

Verdict : pg_repack avec -o remplace CLUSTER pour la production.

pg_repack vs pg_squeeze

pg_squeeze : Alternative plus récente, similaire à pg_repack.

Critère pg_repack pg_squeeze
Maturité Très mature Plus récent
Adoption Large Émergente
Fonctionnalités Complètes Similaires
Maintenance Active Active

Verdict : pg_repack est plus éprouvé et recommandé pour la production critique.

Intégration avec l'Écosystème

pg_repack + pg_cron (Automatisation)

-- Créer une fonction wrapper pour pg_repack
CREATE OR REPLACE FUNCTION run_pg_repack(table_name TEXT)  
RETURNS BOOLEAN AS $$  
DECLARE  
    cmd TEXT;
BEGIN
    -- Construire la commande shell
    cmd := format('pg_repack -h localhost -U postgres -d production -t %s --verbose', table_name);

    -- Exécuter via COPY PROGRAM (nécessite superutilisateur)
    EXECUTE format('COPY (SELECT 1) TO PROGRAM %L', cmd);

    RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'pg_repack failed for %: %', table_name, SQLERRM;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Planifier avec pg_cron
SELECT cron.schedule(
    'monthly_repack_orders',
    '0 2 1 * *',  -- Le 1er de chaque mois à 2h
    'SELECT run_pg_repack(''orders'');'
);

pg_repack + Monitoring (Prometheus/Grafana)

Exporter des métriques de bloat et déclencher des alertes :

-- Vue pour métriques de bloat
CREATE OR REPLACE VIEW monitoring.table_bloat AS  
SELECT  
    schemaname,
    tablename,
    pg_relation_size(schemaname||'.'||tablename) as table_bytes,
    (SELECT dead_tuple_percent FROM pgstattuple(schemaname||'.'||tablename)) as bloat_percent
FROM pg_tables  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');  

-- Alerte si bloat > 30%

pg_repack + Patroni (High Availability)

Dans une configuration Patroni, exécuter pg_repack uniquement sur le primaire :

#!/bin/bash
# Script: repack_if_primary.sh

# Vérifier si ce nœud est le primaire
ROLE=$(patronictl list -f json | jq -r '.[] | select(.Member == "'$HOSTNAME'") | .Role')

if [ "$ROLE" == "Leader" ]; then
    echo "This node is primary, running pg_repack..."
    pg_repack -d production -t orders --verbose
else
    echo "This node is standby, skipping pg_repack"
fi

Cas d'Usage Avancés

1. Réorganiser avec Tablespace Alternatif

Pour éviter de saturer le disque principal :

-- Créer un tablespace sur un disque rapide
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pg_tablespace';
# Utiliser ce tablespace pour les tables temporaires
pg_repack -d production -t orders --tablespace fast_ssd

2. Réorganiser avec Parallélisation

Pour des tables très volumineuses (>1 TB) :

# Utiliser plusieurs jobs (si assez de CPU/I/O)
pg_repack -d production -t huge_table --jobs 8 --verbose

Attention : Augmente la consommation de ressources (CPU, mémoire, connexions).

3. Réorganiser Toutes les Tables avec Bloat >30%

#!/bin/bash
# Script: repack_bloated_tables.sh

DBNAME="production"  
BLOAT_THRESHOLD=30  

# Requête pour identifier les tables avec bloat > 30%
TABLES=$(psql -d $DBNAME -t -c "
    SELECT tablename
    FROM pgstattuple_approx('public.' || tablename)
    JOIN pg_tables ON pg_tables.tablename = pgstattuple_approx.tablename
    WHERE approx_dead_tuple_percent > $BLOAT_THRESHOLD
      AND schemaname = 'public';
")

for table in $TABLES; do
    echo "Repacking $table (bloat > ${BLOAT_THRESHOLD}%)..."
    pg_repack -d $DBNAME -t $table --verbose
done

Pour Aller Plus Loin

Ressources Officielles

Outils Complémentaires

  • pgstattuple : Extension pour mesurer le bloat
  • check_postgres : Script Nagios pour surveiller le bloat
  • pg_squeeze : Alternative à pg_repack

Lectures Recommandées

  • PostgreSQL: Up and Running (Regina Obe, Leo Hsu) - Chapitre sur la maintenance
  • PostgreSQL Administration Cookbook - Recettes de maintenance

Communauté et Support

  • GitHub Issues : Signaler des bugs ou demander des fonctionnalités
  • PostgreSQL Mailing Lists : pgsql-admin@postgresql.org
  • Reddit : r/PostgreSQL

Résumé

pg_repack est un outil essentiel pour maintenir les performances PostgreSQL en production en permettant de réorganiser les tables sans interruption de service.

Points Clés

  • ✅ Réorganisation online : Pas de downtime, application continue
  • ✅ Réduction du bloat : Récupère l'espace perdu par MVCC
  • ✅ Optimisation physique : Réorganise selon un ordre optimal (CLUSTER-like)
  • ✅ Sûr et transactionnel : Rollback possible en cas d'erreur
  • ✅ Production-ready : Utilisé par des milliers d'entreprises

Quand Utiliser pg_repack ?

  • Bloat élevé : Table avec >30% de dead tuples
  • Performances dégradées : Scans séquentiels lents
  • Après migrations : Optimiser après imports massifs
  • Maintenance régulière : Prévenir l'accumulation de bloat
  • Alternative à VACUUM FULL : Réorganiser sans verrou exclusif

Workflow Recommandé

1. Détecter le bloat (pgstattuple, pg_stat_user_tables)
2. Évaluer l'impact (taille, activité, disponibilité)
3. Tester sur un clone (dry-run, mesurer durée)
4. Vérifier l'espace disque (1.5× taille table)
5. Planifier pendant heures creuses
6. Exécuter pg_repack avec monitoring
7. Valider post-repack (taille, performances)
8. Documenter l'opération

Critères de Décision

Situation Outil Recommandé
Production 24/7 pg_repack
Table < 1 GB, downtime acceptable VACUUM FULL
Besoin d'ordre physique pg_repack -o
Table sans PK Ajouter PK, puis pg_repack
Bloat critique (>80%) pg_repack immédiatement

Métriques de Succès

Une opération pg_repack réussie devrait :

  • ✅ Réduire la taille : Table compactée (50-80% de réduction typique)
  • ✅ Améliorer les performances : Scans plus rapides
  • ✅ Pas de downtime : Application ininterrompue
  • ✅ Pas d'erreurs : Aucune transaction perdue

Prochaines Étapes

  1. Installer pg_repack sur votre environnement de test
  2. Mesurer le bloat actuel de vos tables (pgstattuple)
  3. Tester pg_repack sur une petite table
  4. Observer le processus et les verrous
  5. Planifier la maintenance régulière
  6. Automatiser avec des scripts

En Conclusion : pg_repack est un outil indispensable pour maintenir des bases de données PostgreSQL performantes et saines en production. Il résout le problème du bloat sans les contraintes des outils natifs (VACUUM FULL, CLUSTER), permettant une maintenance transparente et sûre. Pour tout DBA, DevOps ou développeur gérant PostgreSQL en production, maîtriser pg_repack est essentiel pour garantir des performances optimales dans le temps.

Principe d'or : "Maintenez régulièrement, prévenez les problèmes" - pg_repack transforme la maintenance d'une opération critique et risquée en un processus routinier et sûr.

⏭️ PostgreSQL en Production