SQL Academy · Leçon

MVCC et causes du gonflement

Comprenez le contrôle de la concurrence multiversion, pourquoi les tuples morts s’accumulent et comment les transactions longues provoquent le gonflement.

MVCC et causes du gonflement est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que MVCC ?

Contrôle de la concurrence multiversion. Au lieu de verrouiller les données, PostgreSQL conserve plusieurs versions d'une ligne. Les lecteurs voient un instantané cohérent ; les rédacteurs créent de nouvelles versions sans bloquer les lecteurs.

Fonctionnement de UPDATE

UPDATE ne modifie pas la ligne sur place :

Marquer l'ancienne version de la ligne comme « morte » dans la transaction T
Écrire une nouvelle version
Les autres transactions voient la version autorisée par leur instantané

Pourquoi le gonflement apparaît

Les versions mortes s'accumulent. La table grossit même si le nombre de lignes reste stable. Sans nettoyage, les requêtes parcourent progressivement davantage de lignes mortes.

Quand VACUUM récupère de l'espace

VACUUM marque les lignes mortes comme réutilisables (dans le fichier de la table). Il ne réduit PAS les fichiers, sauf s'ils sont entièrement vides à la fin. VACUUM FULL réécrit la table — verrou exclusif, opération lente.

Autovacuum

PostgreSQL exécute autovacuum en arrière-plan. Il se déclenche lorsque le nombre de lignes mortes dépasse un seuil :

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
-- vacuum when dead_rows > 50 + 0.2 * total_rows

Charges de travail qui causent du gonflement

Nombreuses opérations UPDATE sur de petites tables / tables HOT
Gros lots de DELETE (un vacuum est nécessaire pour libérer l'espace)
Les transactions de longue durée bloquent le vacuum (elles conservent des instantanés)
Les sessions inactives dans une transaction accumulent des lignes mortes dans les tables très sollicitées

Diagnostiquer le gonflement

L'extension pgstattuple fournit des chiffres exacts :

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len, tuple_count, dead_tuple_count, free_space, etc.

SELECT * FROM pgstatindex('orders_user_id_idx');

Les transactions longues bloquent le vacuum

VACUUM ne peut nettoyer que les lignes plus anciennes que la transaction active la plus ancienne. Une session inactive dans une transaction pendant 4 heures implique 4 heures de lignes mortes non récupérées.

SELECT pid, state, xact_start, NOW() - xact_start AS duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY duration DESC NULLS LAST;

Protection contre le dépassement cyclique

Les identifiants de transaction sont codés sur 32 bits. Si autovacuum ne suit pas le rythme, le cluster risque un « dépassement cyclique » et passe en mode de sécurité (VACUUM forcé). Surveillez :

SELECT datname, age(datfrozenxid) FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Suppression logique ≠ suppression physique

DELETE marque les lignes comme mortes ; l'espace ne peut être récupéré que par VACUUM. Les DELETE en masse suivis de l'absence de vacuum laissent d'importants volumes de lignes mortes.

Mises à jour HOT

Si vous ne mettez à jour que des colonnes non indexées et qu'un emplacement libre existe sur la même page, PostgreSQL effectue une mise à jour HOT (Heap-Only Tuple) — aucune modification d'index et moins de gonflement.

Réduire le gonflement

Gardez les transactions courtes
Évitez les UPDATE étendus sur les colonnes indexées (HOT ne peut pas s'activer)
Paramétrez agressivement le nettoyage automatique sur les tables très sollicitées
Utilisez pg_repack pour réécrire les tables sans verrous prolongés

Récapitulatif

MVCC permet la concurrence au prix d'une accumulation de lignes mortes.

VACUUM nettoie les lignes mortes
Le nettoyage automatique est essentiel — ne le désactivez pas
Les transactions longues bloquent le nettoyage
Diagnostiquez le problème avec pgstattuple

Vérification rapide

Pourquoi un UPDATE ne réduit-il pas la taille de la table, même lorsqu'une seule colonne est modifiée ?

Questions Fréquemment Posées

La leçon « MVCC et causes du gonflement » est-elle gratuite ?

Oui — le texte complet de « MVCC et causes du gonflement » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « MVCC et causes du gonflement » ?

Comprenez le contrôle de la concurrence multiversion, pourquoi les tuples morts s’accumulent et comment les transactions longues provoquent le gonflement. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « MVCC et causes du gonflement » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

MVCC et causes du gonflement
VACUUM, autovacuum, vacuum_cost_delay
ANALYZE et pg_statistic
Parcours d’index uniquement et carte de visibilité

← Retour à SQL Academy