Au cours du fonctionnement à long terme d'une base de données, le gonflement des tables et des indexes constitue l'une des menaces les plus insidieuses auxquelles les administrateurs de KingbaseES (KES) sont confrontés. Ce phénomène érode silencieusement l'espace disque, dégrade les performances des requêtes et, dans les cas graves, menace la stabilité du système. Cet article passe en revue systématiquement les mécanismes centraux et les pratiques opérationnelles pour la récupération des ressources dans KES, en abordant quatre dimensions clés : la reconstruction des index, le principe de la collecte des déchets, l'impact des transactions longues et l'ajustement fin d'autovacuum.

1. REINDEX CONCURRENTLY : Reconstruire un index gonflé sans interruption de service

Avec l'augmentation continue des opérations DML, les index, tout comme les tables, peuvent subir un gonflement. Un index gonflé gaspille non seulement de l'espace disque, mais dégrade également significativement les performances des requêtes — un index fraîchement construit offre souvent un meilleur temps d'accès qu'un ancien index soumis à de nombreuses mises à jour.

Pourquoi ne pas simplement utiliser REINDEX ?

La commande REINDEX standard requiert un verrou ACCESS EXCLUSIVE, le niveau de verrouillage le plus élevé, qui bloque toutes les opérations métier et est presque inacceptable en environnement de production.

La solution consiste à utiliser REINDEX ... CONCURRENTLY, qui abaisse le niveau de verrou à SHARE UPDATE EXCLUSIVE, n'interrompt pas les opérations DML et permet une reconstruction d'index transparente pour l'activité métier.

Les six phases d'exécution de REINDEX CONCURRENTLY

Identifier et nettoyer les index invalides résiduels

Si un REINDEX CONCURRENTLY échoue en cours de route, il peut laisser des index invalides cnotenant \_ccnew dans leur nom, nécessitant un nettoyage :

SELECT c.relname AS nom_index,
       pg_size_pretty(pg_relation_size(c.oid)) AS taille
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname LIKE '%_ccnew'   -- Index temporaires issus du processus
  AND NOT indisvalid              -- État INVALID
LIMIT 10;

2. Principe de la collecte des déchets : Pourquoi les tables gonflent-elles ?

KES est basé sur le mécanisme MVCC (Multi-Version Concurrency Control). Les opérations de mise à jour et de suppression ne suppriment pas physiquement immédiatement les anciennes versions des données ; elles les conservent comme des « tuples morts » (dead tuples). Ces tuples morts doivent être régulièrement récupérés par le mécanisme VACUUM, faute de quoi ils provoquent un gonflement de la table.

Les huit causes fondamentales du gonflement

1. Autovacuum désactivé : Cause la plus directe. Sans mécanisme automatique de récupération, les tuples morts s'accumulent indéfiniment.
2. Seuil de déclenchement d'autovacuum trop élevé : La condition de déclenchement par défaut est : seuil = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. Avec un scale\_factor par défaut de 0.2, la récupération n'est déclenchée que lorsque les tuples morts atteignent 20% du nombre total de lignes de la table — le gonflement est déjà un fait accompli.
3. Nombre insuffisant de workers autovacuum : La valeur par défaut de autovacuum\_max\_workers est 3. Lorsque le nombre de tables nécessitant un nettoyage dépasse 3, les autres doivent attendre en file d'attente.
4. Requêtes ou transactions longues maintenant un xmin actif : Cause la plus insidieuse et la plus dommageable en production, détaillée dans la section suivante.
5. Activation d'autovacuum_vacuum_cost_delay : Le mécanisme de limitation basé sur le coût ralentit considérablement la vitesse de récupération des déchets. Il est déconseillé sur les systèmes avec des E/S normales.
6. Valeur d'autovacuum_naptime trop longue : L'intervalle de réveil du processus launcher est trop long, laissant les déchets s'accumuler sans traitement.
7. Suppressions ou mises à jour massives : La suppression ou la mise à jour d'un grand volume de données dans une seule transaction. Avant le commit de cette transaction, ces versions obsolètes sont totalement inrécupérables.
8. Nombreuses mises à jour non-HOT entraînant un gonflement des index : Un page d'index B-Tree ne peut être récupérée que lorsqu'aucune référence ne pointe vers elle. Les mises à jour non-HOT peuvent rapidement augmenter la taille des index.

Test pratique du gonflement lors de mises à jour concurrentes

Le test suivant met à jour en continu 1 million de lignes à l'aide de 10 processus concurrents pour observer le processus de gonflement :

-- État initial
Taille de la table : 73 Mo    Taille de l'index : 21 Mo

-- Après des mises à jour concurrentes continues par 10 processus
Taille de la table : 335 Mo   Taille de l'index : 48 Mo

Le journal d'autovacuum montre une grande quantité de tuples morts non récupérables :

tuples: 0 removed, 2049809 remain, 999991 are dead but not yet removable
tuples: 501373 removed, 2176172 remain, 999991 are dead but not yet removable

Cause fondamentale : Les workers autovacuum opèrent au niveau de la table. Pour une même table, un seul worker travaille à un instant donné. Les verrous exclusifs détenus par les transactions de mise à jour concurrentes bloquent le processus de récupération, générant des tuples morts « not yet removable », ce qui finit par forcer la base de données à allouer de nouveaux blocs de données.

Méthode d'amélioration : Découper les mises à jour massives en plusieurs petites transactions, raccourcir la durée de détention d'une transaction et réduire la probabilité d'occurrence de « not yet removable ».

3. Transactions longues : L'obstacle invisible au VACUUM

Trois scénarios typiques de transactions longues

Dans KES, les trois situations suivantes maintiennent un backend\_xid actif, empêchant la récupération des versions obsolètes générées par la suite :

Scénario 1 : Curseur ouvert sans fermeture

BEGIN;
DECLARE c1 CURSOR FOR SELECT 1 FROM pg_class;
-- Le curseur n'est pas fermé, le backend_xid persiste.
-- Pendant ce temps, toutes les versions mortes générées ne peuvent être récupérées.
CLOSE c1;  -- Le xmin n'est libéré qu'à la fermeture.

Scénario 2 : Requête en cours d'exécution pendant une longue durée

BEGIN;
SELECT pg_sleep(1000);  -- Pendant l'exécution, le backend_xid est maintenu.
-- Le xmin n'est libéré qu'à l'annulation ou à la fin de la requête.

Scénario 3 : Transaction avec niveau d'isolation REPEATABLE READ / SERIALIZABLE

BEGIN WORK ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
-- Le backend_xid persiste jusqu'au COMMIT / ROLLBACK.
END;

Test pratique : Une transaction longue bloque VACUUM FREEZE

-- Lorsque la première transaction longue (xid: 1668525) n'est pas terminée :
VACUUM (FREEZE, VERBOSE) t2;
-- Résultat : 0 gelées, l'âge ne diminue pas.
-- "oldest xmin: 1668525"

-- Après la fin de la première transaction, la seconde transaction longue (xid: 1788896) est toujours active :
VACUUM (FREEZE, VERBOSE) t2;
-- Résultat : L'âge diminue légèrement, mais ne peut pas être réinitialisé à zéro.

-- Après la fin des deux transactions longues :
VACUUM (FREEZE, VERBOSE) t2;
-- Résultat : age = 0, gel complet réussi ✅

Conclusion clé : Non seulement une transaction longue sur la table cible bloque son propre VACUUM FREEZE, mais une transaction longue sur une autre table bloque également la progression du gel pour toutes les tables. C'est la cause la plus courante d'un âge de table élevé en production.

Requêtes SQL pratiques pour surveiller les transactions longues

-- Surveiller les sessions actives maintenant un xmin (durée > 30 minutes)
SELECT datname, usename, query, xact_start,
       now() - xact_start AS duree_transaction,
       state
FROM pg_stat_activity
WHERE state <> 'idle'
  AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
  AND now() - xact_start > INTERVAL '30 min'
ORDER BY xact_start;

-- Surveiller les transactions préparées (2PC) non commitées
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

-- Vérifier si un slot de réplication bloque la progression du xmin à cause d'une transaction longue sur le standby
SELECT * FROM pg_replication_slots
ORDER BY age(xmin) DESC;

4. Ajustement fin d'autovacuum : Configuration de paramètres au niveau de la table

Les paramètres globaux d'autovacuum sont appliqués de manière uniforme. Pour les tables critiques soumises à de fréquentes mises à jour, la configuration de paramètres au niveau de la table constitue une solution plus précise.

Exemple de configuration de paramètres au niveau de la table

-- Activer autovacuum pour une table et définir un seuil précis
ALTER TABLE t2 SET (autovacuum_enabled = true);
ALTER TABLE t2 SET (autovacuum_vacuum_threshold = 1);
ALTER TABLE t2 SET (autovacuum_vacuum_scale_factor = 0);  -- Essentiel : doit être défini à 0 en même temps

-- Configurer de manière analogue les paramètres liés à analyze
ALTER TABLE t2 SET (autovacuum_analyze_threshold = 1);
ALTER TABLE t2 SET (autovacuum_analyze_scale_factor = 0);

⚠️ Note importante : Si seul autovacuum\_vacuum\_threshold = 1 est défini sans autovacuum\_vacuum\_scale\_factor = 0, la condition de déclenchement ajoutera toujours le scale\_factor global (par défaut 0.2), ce qui aboutit à un seuil bien plus élevé que prévu. Les deux paramètres doivent être utilisés conjointement.

Logique de calcul du seuil de déclenchement

Liste de recommandations pour l'optimisation globale

Pour différents scénarios, voici des recommandations d'optimisation validées par des tests pratiques :

1. Autovacuum doit être activé — C'est un impératif. Il ne doit être désactivé dans aucune circonstance.
2. Améliorer les capacités E/S du stockage — Des SSD performants constituent la base physique pour un VACUUM efficace.
3. Réduire le scale_factor — Pour les grandes tables, définir autovacuum\_vacuum\_scale\_factor = 0.001 pour éviter d'attendre qu'elles atteignent 20% de tuples morts.
4. Augmenter le nombre de workers — Pour les scénarios avec de nombreuses et grandes tables, autovacuum\_max\_workers peut être ajusté au nombre de cœurs CPU, tout en définissant autovacuum\_work\_mem à 2 Go.
5. Éviter les transactions longues — Cela inclut les requêtes longues, les curseurs non fermés, l'utilisation inutile du niveau d'isolation REPEATABLE READ, et les transactions longues implicites lors de sauvegardes logiques avec pg\_dump.
6. Désactiver la limitation par cost_delay — Le paramètre autovacuum\_vacuum\_cost\_delay n'est pas nécessaire sur les systèmes avec des E/S normales.
7. Découper les opérations massives — Fractionner les grandes transactions en plusieurs petites pour réduire la probabilité de « not yet removable ».
8. Solutions de réparation après gonflement : Un VACUUM standard ne peut pas réduire une table déjà gonflée. Il est nécessaire d'utiliser VACUUM FULL ou CLUSTER (requiert un verrou exclusif, à exécuter de préférence en période de faible activité), ou d'utliiser le plugin sys_squeeze (qui dépend de la décodification logique et nécessite de définir wal\_level = logical) pour une réduction en ligne.

5. Synthèse : Construire une défense complète pour la récupération des ressources dans KES

Le système de récupération des ressources de KES est un mécanisme de précision multi-niveaux et interconnecté. Une négligence dans n'importe lequel de ces maillons peut déclencher une réaction en chaîne de gonflement.

Gonflement d'index  →  REINDEX CONCURRENTLY  →  Reconstruction sans interruption
Gonflement de table →  Ajustement fin d'autovacuum →  Récupération rapide des tuples morts
Transaction longue  →  Surveillance active + Arrêt rapide →  Suppression du blocage xmin
Accumulation d'âge  →  VACUUM FREEZE         →  Prévention de l'épuisement des ID de transaction

En tant qu'administrateur, une surveillance proactive l'emporte sur une réponse réactive. Mettre en place des alertes pour les transactions longues, vérifier régulièrement les taux de gonflement et configurer de manière adéquate les paramètres d'autovacuum sont des pratiques opérationnelles essentielles pour garantir une santé durable de la base de données KES. En environnement de production, communiquer pleinement avec les équipes métier sur les risques potentiels des transactions longues et éviter les problèmes au niveau de la conception applicative constitue la solution la plus fondamentale.