PostgreSQL implémente le Contrôle de Concurrence Multiversion (MVCC) en créant de nouvelles versions de lignes pour chaque Mise à jour et Suppression plutôt qu'en remplaçant les données sur place. Ce design élimine les verrouillages de lecture mais laisse des "tuples morts" dans le tas qui doivent être récupérés par le processus VACUUM. Lorsqu'une table subit des charges de travail à forte rotation—comme des mises à jour de statut fréquentes sur des enregistrements en attente ou la bascule de suppression douce—l'accumulation de tuples morts dépasse le processus de vacuum, entraînant un gonflement de la table, un gonflement des index et une dégradation des performances des requêtes.
Le problème principal survient parce que VACUUM doit scanner toute la table pour identifier les tuples morts, et dans des tables monolithiques de plusieurs téraoctets, cette opération devient limitée par l'I/O et peut échouer à se terminer avant le débordement de l'ID de transaction. Sans intervention, la table croît indéfiniment bien que le nombre de lignes réel reste stable, consommant un espace de stockage excessif et ralentissant les analyses d'index.
La solution implémente la Partitionnement Déclaratif en utilisant la stratégie RANGE sur une clé temporelle ou logique, comme la date de création. En divisant la table en plus petites partitions physiques (par exemple, des segments mensuels), les opérations de vacuum agissent sur des partitions individuelles plutôt que sur l'ensemble de la table. De plus, le PRUNING DE PARTITION garantit que les requêtes n'accèdent qu'aux segments pertinents, et les partitions âgées peuvent être détachées et archivées, réclamant instantanément de l'espace sans surcharge de vacuum.
-- Table parente avec partitionnement par plage CREATE TABLE iot_sensor_data ( sensor_id INT NOT NULL, temperature NUMERIC, recorded_at TIMESTAMP NOT NULL, status VARCHAR(20) ) PARTITION BY RANGE (recorded_at); -- Partition mensuelle pour les données à forte rotation récentes CREATE TABLE iot_sensor_data_2024_06 PARTITION OF iot_sensor_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); -- Index sur la clé de partition CREATE INDEX idx_sensor_date ON iot_sensor_data (recorded_at);
Un client de fabrication intelligente exploitait une base de données PostgreSQL 14 ingérant des télémetries provenant de 50 000 capteurs IoT, générant 10 millions de lectures par jour avec 30 % nécessitant des mises à jour de statut dans les 24 heures. La table sensor_logs a gonflé à 2 To en six mois parce que AUTOVACUUM ne pouvait pas traiter la table assez rapidement pour récupérer les tuples morts des mises à jour constantes des indicateurs de statut. La latence des requêtes a grimpé à 30 secondes, et pg_class a révélé que la table était 400 % gonflée par rapport à sa taille logique.
Trois solutions architecturales ont été évaluées. La première approche impliquait un réglage agressif de VACUUM, réduisant autovacuum_vacuum_scale_factor à 0,02 et augmentant maintenance_work_mem à 2 Go. Bien que cela ait aidé marginalement, le processus nécessitait de scanner en continu toute la table 2 To, provoquant une contentieux d'I/O sévère avec les requêtes de production et échouant à se terminer dans les fenêtres de maintenance nocturnes. La deuxième stratégie proposait des INDEX PARTIELS excluant les anciennes données, ce qui réduisait le gonflement des index mais ne traitait pas le gonflement sous-jacent du tas ou l'accumulation de tuples morts dans la table elle-même.
La troisième solution a mis en œuvre le PARTITIONNEMENT DÉCLARATIF par RANGE sur le timestamp recorded_at, créant des partitions mensuelles d'environ 300 Go chacune. Cette approche a été sélectionnée car elle a localisé l'activité à forte rotation dans la partition du mois en cours, permettant à VACUUM de traiter 300 Go au lieu de 2 To à chaque exécution. De plus, les partitions âgées de plus d'un an ont été DÉTACHÉES et déplacées vers un stockage froid, réclamant de l'espace sans opérations de SUPPRESSION coûteuses. Après la mise en œuvre, la durée du vacuum est passée de 8 heures à 45 minutes, les risques de débordement de l'ID de transaction ont disparu, et les performances des requêtes se sont améliorées de dix fois grâce à l'élagage des partitions.
Comment le mécanisme HOT (Heap-Only Tuple) de PostgreSQL réduit-il le gonflement des index, et dans quelle condition spécifique échoue-t-il à s'appliquer ?
Les mises à jour HOT se produisent lorsqu'une ligne est mise à jour mais aucunes colonnes indexées ne sont modifiées, et qu'un espace libre suffisant existe dans la même page de données de 8 Ko. Dans ce scénario, PostgreSQL enchaîne le nouveau tuple à l'ancien dans le tas sans créer de nouvelles entrées d'index, réduisant considérablement la charge d'entretien des index. Cependant, HOT échoue immédiatement si la mise à jour modifie une colonne présente dans un index quelconque, ou si la page manque d'espace libre en dessous du seuil de fillfactor, forçant PostgreSQL à écrire de nouveaux pointeurs d'index et à créer du gonflement. Les candidats supposent souvent que toutes les opérations de mise à jour impactent les index de manière égale, sans réaliser que la mise à jour des colonnes non indexées est significativement moins coûteuse lorsque HOT réussit.
Quelle est la différence précise entre VACUUM, VACUUM FULL et CLUSTER en termes de comportement de verrouillage et de disponibilité des tables ?
VACUUM s'exécute en même temps que toutes les opérations, marquant les tuples morts comme espace réutilisable sans retourner de stockage au système d'exploitation ; il n'exerce aucun verrou sur les données utilisateur. VACUUM FULL réécrit l'ensemble du fichier de la table pour éliminer complètement le gonflement, mais acquiert un verrou ACCESS EXCLUSIVE qui bloque toutes les lectures et écritures pendant la durée, pouvant durer des heures sur de grandes tables. CLUSTER réorganise physiquement la table pour correspondre à une séquence d'index, nécessitant également un verrou ACCESS EXCLUSIVE, et est généralement plus lent que VACUUM FULL pour la récupération d'espace pur mais conserve l'ordre trié pour de futures analyses d'index en cluster. Les candidats recommandent souvent de manière dangereuse VACUUM FULL pour la maintenance routinière, sans comprendre qu'il entraîne une indisponibilité complète de la table.
Comment la carte de visibilité impacte-t-elle les performances des scans uniquement d'index, et pourquoi la fréquence de vacuum importe-t-elle pour cette méthode d'accès ?
La carte de visibilité est un bitmap binaire stocké aux côtés de la table qui suit quelles pages de tas contiennent uniquement des tuples visibles pour toutes les transactions actuelles et futures. Un SCAN UNIQUEMENT D'INDEX peut satisfaire les requêtes en utilisant uniquement l'index et la carte de visibilité sans aller chercher les tuples du tas, mais seulement si la carte confirme que toutes les lignes sur cette page sont visibles. Si une page contient des tuples morts ou des transactions non validées, le bit de visibilité est désactivé, forçant la base de données à vérifier individuellement les tuples du tas. Un VACUUM fréquent met à jour les bits de la carte de visibilité, permettant un accès réel uniquement via l'index ; sans cela, même les requêtes utilisant des index couvrants encourent un I/O aléatoire pour vérifier la visibilité des tuples, contrecarrant le but de l'optimisation.