PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

27.4. Rapporter la progression

PostgreSQL™ a la possibilité de rapporter la progression de certaines commandes lors de leur exécution. Actuellement, les seules commandes supportant un rapport de progression sont CREATE INDEX, VACUUM et CLUSTER. Ceci pourrait être étendu dans le futur.

27.4.1. CREATE INDEX Progress Reporting

Whenever CREATE INDEX or REINDEX is running, the pg_stat_progress_create_index view will contain one row for each backend that is currently creating indexes. The tables below describe the information that will be reported and provide information about how to interpret it.

Tableau 27.22. pg_stat_progress_create_index View

Column Type Description
pid integer Process ID of backend.
datid oid OID of the database to which this backend is connected.
datname name Name of the database to which this backend is connected.
relid oid OID of the table on which the index is being created.
index_relid oid OID of the index being created or reindexed. During a non-concurrent CREATE INDEX, this is 0.
phase text Current processing phase of index creation. See Tableau 27.23, « CREATE INDEX phases ».
lockers_total bigint Total number of lockers to wait for, when applicable.
lockers_done bigint Number of lockers already waited for.
current_locked_pid bigint Process ID of the locker currently being waited for.
blocks_total bigint Total number of blocks to be processed in the current phase.
blocks_done bigint Number of blocks already processed in the current phase.
tuples_total bigint Total number of tuples to be processed in the current phase.
tuples_done bigint Number of tuples already processed in the current phase.
partitions_total bigint When creating an index on a partitioned table, this column is set to the total number of partitions on which the index is to be created.
partitions_done bigint When creating an index on a partitioned table, this column is set to the number of partitions on which the index has been completed.

Tableau 27.23. CREATE INDEX phases

Phase Description
initializing CREATE INDEX or REINDEX is preparing to create the index. This phase is expected to be very brief.
waiting for writers before build CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for transactions with write locks that can potentially see the table to finish. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.
building index The index is being built by the access method-specific code. In this phase, access methods that support progress reporting fill in their own progress data, and the subphase is indicated in this column. Typically, blocks_total and blocks_done will contain progress data, as well as potentially tuples_total and tuples_done.
waiting for writers before validation CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for transactions with write locks that can potentially write into the table to finish. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.
index validation: scanning index CREATE INDEX CONCURRENTLY is scanning the index searching for tuples that need to be validated. This phase is skipped when not in concurrent mode. Columns blocks_total (set to the total size of the index) and blocks_done contain the progress information for this phase.
index validation: sorting tuples CREATE INDEX CONCURRENTLY is sorting the output of the index scanning phase.
index validation: scanning table CREATE INDEX CONCURRENTLY is scanning the table to validate the index tuples collected in the previous two phases. This phase is skipped when not in concurrent mode. Columns blocks_total (set to the total size of the table) and blocks_done contain the progress information for this phase.
waiting for old snapshots CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for transactions that can potentially see the table to release their snapshots. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.
waiting for readers before marking dead REINDEX CONCURRENTLY is waiting for transactions with read locks on the table to finish, before marking the old index dead. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.
waiting for readers before dropping REINDEX CONCURRENTLY is waiting for transactions with read locks on the table to finish, before dropping the old index. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.

27.4.2. Rapporter la progression du VACUUM

La vue pg_stat_progress_vacuum contient une ligne pour chaque processus serveur (incluant les processus autovacuum worker) en train d'exécuter un VACUUM. Les tableaux ci-dessous décrivent les informations rapportées et fournissent des informations sur leur interprétation. Progress for VACUUM FULL commands is reported via pg_stat_progress_cluster because both VACUUM FULL and CLUSTER rewrite the table, while regular VACUUM only modifies it in place. See Section 27.4.3, « CLUSTER Progress Reporting ».

Tableau 27.24. Vue pg_stat_progress_vacuum

Colonne Type Description
pid integer Identifiant (PID) du processus serveur.
datid oid OID de la base de données où est connecté ce processus serveur.
datname name Nom de la base de données où est connecté ce processus serveur.
relid oid OID de la table nettoyée par le VACUUM.
phase text Phase actuelle du vacuum. Voir Tableau 27.25, « Phases du VACUUM ».
heap_blks_total bigint Nombre total de blocs de la table. Ce nombre est récupéré au début du parcours. Des blocs peuvent être ajoutés par la suie, mais ne seront pas (et n'ont pas besoin d'être) visités par ce VACUUM.
heap_blks_scanned bigint Nombre de blocs parcourus dans la table. Comme la carte de visibilité est utilisée pour optimiser les parcours, certains blocs seront ignorés sans inspection ; les blocs ignorés sont inclus dans ce total, pour que ce nombre puisse devenir égal à heap_blks_total quand le nettoyage se termine. Ce compteur avance seulement quand la phase est scanning heap.
heap_blks_vacuumed bigint Nombre de blocs nettoyés dans la table. Sauf si la table n'a pas d'index, ce compteur avance seulement quand la phase est vacuuming heap. Les blocs qui ne contiennent aucune ligne morte sont ignorés, donc le compteur pourrait parfois avancer par de larges incréments.
index_vacuum_count bigint Nombre de cycles de nettoyage d'index réalisés.
max_dead_tuples bigint Nombre de lignes mortes que nous pouvons stocker avant d'avoir besoin de réaliser un cycle de nettoyage d'index, basé sur maintenance_work_mem.
num_dead_tuples bigint Nombre de lignes mortes récupérées depuis le dernier cycle de nettoyage d'index.

Tableau 27.25. Phases du VACUUM

Phase Description
initializing VACUUM se prépare à commencer le parcours de la table. Cette phase est habituellement très rapide.
scanning heap VACUUM parcourt la table. Il va défragmenter chaque bloc si nécessaire et potentiellement réaliser un gel des lignes. La colonne heap_blks_scanned peut être utilisée pour surveiller la progression du parcours.
vacuuming indexes VACUUM est en train de nettoyer les index. Si une table a des index, ceci surviendra au moins une fois par vacuum, après le parcours complet de la table. Cela pourrait arriver plusieurs fois par vacuum si if maintenance_work_mem n'est pas suffisamment important pour y enregistrer le nombre de lignes mortes trouvées.
vacuuming heap VACUUM est en train de nettoyer la table. Nettoyer la table est différent du parcours de la table, et survient après chaque phase de nettoyage d'index. Si heap_blks_scanned est inférieur à heap_blks_total, le système retournera à parcourir la table après la fin de cette phase. Sinon, il commencera le nettoyage des index une fois cette phase terminée.
cleaning up indexes VACUUM est en train de nettoyer les index. Ceci survient que la table ait été entièrement parcourue et que le vacuum des index et de la table soit terminé.
truncating heap VACUUM est en cours de tronquage de la table pour pouvoir redonner au système d'exploitation les pages vides en fin de relation. Ceci survient après le nettoyage des index.
performing final cleanup VACUUM réalise le nettoyage final. Durant cette phase, VACUUM nettoiera la carte des espaces libres, mettra à jour les statistiques dans pg_class, et rapportera les statistiques au collecteur de statistiques. Une fois cette phase terminée, VACUUM se terminera.

27.4.3. CLUSTER Progress Reporting

Whenever CLUSTER or VACUUM FULL is running, the pg_stat_progress_cluster view will contain a row for each backend that is currently running either command. The tables below describe the information that will be reported and provide information about how to interpret it.

Tableau 27.26. pg_stat_progress_cluster View

Column Type Description
pid integer Process ID of backend.
datid oid OID of the database to which this backend is connected.
datname name Name of the database to which this backend is connected.
relid oid OID of the table being clustered.
command text The command that is running. Either CLUSTER or VACUUM FULL.
phase text Current processing phase. See Tableau 27.27, « CLUSTER and VACUUM FULL phases ».
cluster_index_relid oid If the table is being scanned using an index, this is the OID of the index being used; otherwise, it is zero.
heap_tuples_scanned bigint Number of heap tuples scanned. This counter only advances when the phase is seq scanning heap, index scanning heap or writing new heap.
heap_tuples_written bigint Number of heap tuples written. This counter only advances when the phase is seq scanning heap, index scanning heap or writing new heap.
heap_blks_total bigint Total number of heap blocks in the table. This number is reported as of the beginning of seq scanning heap.
heap_blks_scanned bigint Number of heap blocks scanned. This counter only advances when the phase is seq scanning heap.
index_rebuild_count bigint Number of indexes rebuilt. This counter only advances when the phase is rebuilding index.

Tableau 27.27. CLUSTER and VACUUM FULL phases

Phase Description
initializing The command is preparing to begin scanning the heap. This phase is expected to be very brief.
seq scanning heap The command is currently scanning the table using a sequential scan.
index scanning heap CLUSTER is currently scanning the table using an index scan.
sorting tuples CLUSTER is currently sorting tuples.
swapping relation files The command is currently swapping newly-built files into place.
rebuilding index The command is currently rebuilding an index.
performing final cleanup The command is performing final cleanup. When this phase is completed, CLUSTER or VACUUM FULL will end.