|
- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>27.4. Progress Reporting</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="monitoring-locks.html" title="27.3. Viewing Locks" /><link rel="next" href="dynamic-trace.html" title="27.5. Dynamic Tracing" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">27.4. Progress Reporting</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="monitoring-locks.html" title="27.3. Viewing Locks">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="monitoring.html" title="Chapter 27. Monitoring Database Activity">Up</a></td><th width="60%" align="center">Chapter 27. Monitoring Database Activity</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="dynamic-trace.html" title="27.5. Dynamic Tracing">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="PROGRESS-REPORTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">27.4. Progress Reporting</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING">27.4.1. CREATE INDEX Progress Reporting</a></span></dt><dt><span class="sect2"><a href="progress-reporting.html#VACUUM-PROGRESS-REPORTING">27.4.2. VACUUM Progress Reporting</a></span></dt><dt><span class="sect2"><a href="progress-reporting.html#CLUSTER-PROGRESS-REPORTING">27.4.3. CLUSTER Progress Reporting</a></span></dt></dl></div><p>
- <span class="productname">PostgreSQL</span> has the ability to report the progress of
- certain commands during command execution. Currently, the only commands
- which support progress reporting are <code class="command">CREATE INDEX</code>,
- <code class="command">VACUUM</code> and
- <code class="command">CLUSTER</code>. This may be expanded in the future.
- </p><div class="sect2" id="CREATE-INDEX-PROGRESS-REPORTING"><div class="titlepage"><div><div><h3 class="title">27.4.1. CREATE INDEX Progress Reporting</h3></div></div></div><p>
- Whenever <code class="command">CREATE INDEX</code> or <code class="command">REINDEX</code> is running, the
- <code class="structname">pg_stat_progress_create_index</code> 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.
- </p><div class="table" id="PG-STAT-PROGRESS-CREATE-INDEX-VIEW"><p class="title"><strong>Table 27.22. <code class="structname">pg_stat_progress_create_index</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_progress_create_index View" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Column</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="structfield">pid</code></td><td><code class="type">integer</code></td><td>Process ID of backend.</td></tr><tr><td><code class="structfield">datid</code></td><td><code class="type">oid</code></td><td>OID of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">datname</code></td><td><code class="type">name</code></td><td>Name of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">relid</code></td><td><code class="type">oid</code></td><td>OID of the table on which the index is being created.</td></tr><tr><td><code class="structfield">index_relid</code></td><td><code class="type">oid</code></td><td>OID of the index being created or reindexed. During a
- non-concurrent <code class="command">CREATE INDEX</code>, this is 0.</td></tr><tr><td><code class="structfield">command</code></td><td><code class="type">text</code></td><td>
- The command that is running: <code class="literal">CREATE INDEX</code>,
- <code class="literal">CREATE INDEX CONCURRENTLY</code>,
- <code class="literal">REINDEX</code>, or <code class="literal">REINDEX CONCURRENTLY</code>.
- </td></tr><tr><td><code class="structfield">phase</code></td><td><code class="type">text</code></td><td>
- Current processing phase of index creation. See <a class="xref" href="progress-reporting.html#CREATE-INDEX-PHASES" title="Table 27.23. CREATE INDEX Phases">Table 27.23</a>.
- </td></tr><tr><td><code class="structfield">lockers_total</code></td><td><code class="type">bigint</code></td><td>
- Total number of lockers to wait for, when applicable.
- </td></tr><tr><td><code class="structfield">lockers_done</code></td><td><code class="type">bigint</code></td><td>
- Number of lockers already waited for.
- </td></tr><tr><td><code class="structfield">current_locker_pid</code></td><td><code class="type">bigint</code></td><td>
- Process ID of the locker currently being waited for.
- </td></tr><tr><td><code class="structfield">blocks_total</code></td><td><code class="type">bigint</code></td><td>
- Total number of blocks to be processed in the current phase.
- </td></tr><tr><td><code class="structfield">blocks_done</code></td><td><code class="type">bigint</code></td><td>
- Number of blocks already processed in the current phase.
- </td></tr><tr><td><code class="structfield">tuples_total</code></td><td><code class="type">bigint</code></td><td>
- Total number of tuples to be processed in the current phase.
- </td></tr><tr><td><code class="structfield">tuples_done</code></td><td><code class="type">bigint</code></td><td>
- Number of tuples already processed in the current phase.
- </td></tr><tr><td><code class="structfield">partitions_total</code></td><td><code class="type">bigint</code></td><td>
- 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.
- </td></tr><tr><td><code class="structfield">partitions_done</code></td><td><code class="type">bigint</code></td><td>
- When creating an index on a partitioned table, this column is set to
- the number of partitions on which the index has been completed.
- </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="CREATE-INDEX-PHASES"><p class="title"><strong>Table 27.23. CREATE INDEX Phases</strong></p><div class="table-contents"><table class="table" summary="CREATE INDEX Phases" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Phase</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">initializing</code></td><td>
- <code class="command">CREATE INDEX</code> or <code class="command">REINDEX</code> is preparing to create the index. This
- phase is expected to be very brief.
- </td></tr><tr><td><code class="literal">waiting for writers before build</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> or <code class="command">REINDEX CONCURRENTLY</code> 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 <code class="structname">lockers_total</code>, <code class="structname">lockers_done</code>
- and <code class="structname">current_locker_pid</code> contain the progress
- information for this phase.
- </td></tr><tr><td><code class="literal">building index</code></td><td>
- 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,
- <code class="structname">blocks_total</code> and <code class="structname">blocks_done</code>
- will contain progress data, as well as potentially
- <code class="structname">tuples_total</code> and <code class="structname">tuples_done</code>.
- </td></tr><tr><td><code class="literal">waiting for writers before validation</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> or <code class="command">REINDEX CONCURRENTLY</code> 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 <code class="structname">lockers_total</code>, <code class="structname">lockers_done</code>
- and <code class="structname">current_locker_pid</code> contain the progress
- information for this phase.
- </td></tr><tr><td><code class="literal">index validation: scanning index</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> is scanning the index searching
- for tuples that need to be validated.
- This phase is skipped when not in concurrent mode.
- Columns <code class="structname">blocks_total</code> (set to the total size of the index)
- and <code class="structname">blocks_done</code> contain the progress information for this phase.
- </td></tr><tr><td><code class="literal">index validation: sorting tuples</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> is sorting the output of the
- index scanning phase.
- </td></tr><tr><td><code class="literal">index validation: scanning table</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> 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 <code class="structname">blocks_total</code> (set to the total size of the table)
- and <code class="structname">blocks_done</code> contain the progress information for this phase.
- </td></tr><tr><td><code class="literal">waiting for old snapshots</code></td><td>
- <code class="command">CREATE INDEX CONCURRENTLY</code> or <code class="command">REINDEX CONCURRENTLY</code> is waiting for transactions
- that can potentially see the table to release their snapshots. This
- phase is skipped when not in concurrent mode.
- Columns <code class="structname">lockers_total</code>, <code class="structname">lockers_done</code>
- and <code class="structname">current_locker_pid</code> contain the progress
- information for this phase.
- </td></tr><tr><td><code class="literal">waiting for readers before marking dead</code></td><td>
- <code class="command">REINDEX CONCURRENTLY</code> 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 <code class="structname">lockers_total</code>, <code class="structname">lockers_done</code>
- and <code class="structname">current_locker_pid</code> contain the progress
- information for this phase.
- </td></tr><tr><td><code class="literal">waiting for readers before dropping</code></td><td>
- <code class="command">REINDEX CONCURRENTLY</code> 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 <code class="structname">lockers_total</code>, <code class="structname">lockers_done</code>
- and <code class="structname">current_locker_pid</code> contain the progress
- information for this phase.
- </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="VACUUM-PROGRESS-REPORTING"><div class="titlepage"><div><div><h3 class="title">27.4.2. VACUUM Progress Reporting</h3></div></div></div><p>
- Whenever <code class="command">VACUUM</code> is running, the
- <code class="structname">pg_stat_progress_vacuum</code> view will contain
- one row for each backend (including autovacuum worker processes) that is
- currently vacuuming. The tables below describe the information
- that will be reported and provide information about how to interpret it.
- Progress for <code class="command">VACUUM FULL</code> commands is reported via
- <code class="structname">pg_stat_progress_cluster</code>
- because both <code class="command">VACUUM FULL</code> and <code class="command">CLUSTER</code>
- rewrite the table, while regular <code class="command">VACUUM</code> only modifies it
- in place. See <a class="xref" href="progress-reporting.html#CLUSTER-PROGRESS-REPORTING" title="27.4.3. CLUSTER Progress Reporting">Section 27.4.3</a>.
- </p><div class="table" id="PG-STAT-PROGRESS-VACUUM-VIEW"><p class="title"><strong>Table 27.24. <code class="structname">pg_stat_progress_vacuum</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_progress_vacuum View" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Column</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="structfield">pid</code></td><td><code class="type">integer</code></td><td>Process ID of backend.</td></tr><tr><td><code class="structfield">datid</code></td><td><code class="type">oid</code></td><td>OID of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">datname</code></td><td><code class="type">name</code></td><td>Name of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">relid</code></td><td><code class="type">oid</code></td><td>OID of the table being vacuumed.</td></tr><tr><td><code class="structfield">phase</code></td><td><code class="type">text</code></td><td>
- Current processing phase of vacuum. See <a class="xref" href="progress-reporting.html#VACUUM-PHASES" title="Table 27.25. VACUUM Phases">Table 27.25</a>.
- </td></tr><tr><td><code class="structfield">heap_blks_total</code></td><td><code class="type">bigint</code></td><td>
- Total number of heap blocks in the table. This number is reported
- as of the beginning of the scan; blocks added later will not be (and
- need not be) visited by this <code class="command">VACUUM</code>.
- </td></tr><tr><td><code class="structfield">heap_blks_scanned</code></td><td><code class="type">bigint</code></td><td>
- Number of heap blocks scanned. Because the
- <a class="link" href="storage-vm.html" title="68.4. Visibility Map">visibility map</a> is used to optimize scans,
- some blocks will be skipped without inspection; skipped blocks are
- included in this total, so that this number will eventually become
- equal to <code class="structfield">heap_blks_total</code> when the vacuum is complete.
- This counter only advances when the phase is <code class="literal">scanning heap</code>.
- </td></tr><tr><td><code class="structfield">heap_blks_vacuumed</code></td><td><code class="type">bigint</code></td><td>
- Number of heap blocks vacuumed. Unless the table has no indexes, this
- counter only advances when the phase is <code class="literal">vacuuming heap</code>.
- Blocks that contain no dead tuples are skipped, so the counter may
- sometimes skip forward in large increments.
- </td></tr><tr><td><code class="structfield">index_vacuum_count</code></td><td><code class="type">bigint</code></td><td>
- Number of completed index vacuum cycles.
- </td></tr><tr><td><code class="structfield">max_dead_tuples</code></td><td><code class="type">bigint</code></td><td>
- Number of dead tuples that we can store before needing to perform
- an index vacuum cycle, based on
- <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a>.
- </td></tr><tr><td><code class="structfield">num_dead_tuples</code></td><td><code class="type">bigint</code></td><td>
- Number of dead tuples collected since the last index vacuum cycle.
- </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="VACUUM-PHASES"><p class="title"><strong>Table 27.25. VACUUM Phases</strong></p><div class="table-contents"><table class="table" summary="VACUUM Phases" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Phase</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">initializing</code></td><td>
- <code class="command">VACUUM</code> is preparing to begin scanning the heap. This
- phase is expected to be very brief.
- </td></tr><tr><td><code class="literal">scanning heap</code></td><td>
- <code class="command">VACUUM</code> is currently scanning the heap. It will prune and
- defragment each page if required, and possibly perform freezing
- activity. The <code class="structfield">heap_blks_scanned</code> column can be used
- to monitor the progress of the scan.
- </td></tr><tr><td><code class="literal">vacuuming indexes</code></td><td>
- <code class="command">VACUUM</code> is currently vacuuming the indexes. If a table has
- any indexes, this will happen at least once per vacuum, after the heap
- has been completely scanned. It may happen multiple times per vacuum
- if <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> is insufficient to
- store the number of dead tuples found.
- </td></tr><tr><td><code class="literal">vacuuming heap</code></td><td>
- <code class="command">VACUUM</code> is currently vacuuming the heap. Vacuuming the heap
- is distinct from scanning the heap, and occurs after each instance of
- vacuuming indexes. If <code class="structfield">heap_blks_scanned</code> is less than
- <code class="structfield">heap_blks_total</code>, the system will return to scanning
- the heap after this phase is completed; otherwise, it will begin
- cleaning up indexes after this phase is completed.
- </td></tr><tr><td><code class="literal">cleaning up indexes</code></td><td>
- <code class="command">VACUUM</code> is currently cleaning up indexes. This occurs after
- the heap has been completely scanned and all vacuuming of the indexes
- and the heap has been completed.
- </td></tr><tr><td><code class="literal">truncating heap</code></td><td>
- <code class="command">VACUUM</code> is currently truncating the heap so as to return
- empty pages at the end of the relation to the operating system. This
- occurs after cleaning up indexes.
- </td></tr><tr><td><code class="literal">performing final cleanup</code></td><td>
- <code class="command">VACUUM</code> is performing final cleanup. During this phase,
- <code class="command">VACUUM</code> will vacuum the free space map, update statistics
- in <code class="literal">pg_class</code>, and report statistics to the statistics
- collector. When this phase is completed, <code class="command">VACUUM</code> will end.
- </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="CLUSTER-PROGRESS-REPORTING"><div class="titlepage"><div><div><h3 class="title">27.4.3. CLUSTER Progress Reporting</h3></div></div></div><p>
- Whenever <code class="command">CLUSTER</code> or <code class="command">VACUUM FULL</code> is
- running, the <code class="structname">pg_stat_progress_cluster</code> 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.
- </p><div class="table" id="PG-STAT-PROGRESS-CLUSTER-VIEW"><p class="title"><strong>Table 27.26. <code class="structname">pg_stat_progress_cluster</code> View</strong></p><div class="table-contents"><table class="table" summary="pg_stat_progress_cluster View" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Column</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="structfield">pid</code></td><td><code class="type">integer</code></td><td>Process ID of backend.</td></tr><tr><td><code class="structfield">datid</code></td><td><code class="type">oid</code></td><td>OID of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">datname</code></td><td><code class="type">name</code></td><td>Name of the database to which this backend is connected.</td></tr><tr><td><code class="structfield">relid</code></td><td><code class="type">oid</code></td><td>OID of the table being clustered.</td></tr><tr><td><code class="structfield">command</code></td><td><code class="type">text</code></td><td>
- The command that is running. Either <code class="literal">CLUSTER</code> or <code class="literal">VACUUM FULL</code>.
- </td></tr><tr><td><code class="structfield">phase</code></td><td><code class="type">text</code></td><td>
- Current processing phase. See <a class="xref" href="progress-reporting.html#CLUSTER-PHASES" title="Table 27.27. CLUSTER and VACUUM FULL Phases">Table 27.27</a>.
- </td></tr><tr><td><code class="structfield">cluster_index_relid</code></td><td><code class="type">oid</code></td><td>
- If the table is being scanned using an index, this is the OID of the
- index being used; otherwise, it is zero.
- </td></tr><tr><td><code class="structfield">heap_tuples_scanned</code></td><td><code class="type">bigint</code></td><td>
- Number of heap tuples scanned.
- This counter only advances when the phase is
- <code class="literal">seq scanning heap</code>,
- <code class="literal">index scanning heap</code>
- or <code class="literal">writing new heap</code>.
- </td></tr><tr><td><code class="structfield">heap_tuples_written</code></td><td><code class="type">bigint</code></td><td>
- Number of heap tuples written.
- This counter only advances when the phase is
- <code class="literal">seq scanning heap</code>,
- <code class="literal">index scanning heap</code>
- or <code class="literal">writing new heap</code>.
- </td></tr><tr><td><code class="structfield">heap_blks_total</code></td><td><code class="type">bigint</code></td><td>
- Total number of heap blocks in the table. This number is reported
- as of the beginning of <code class="literal">seq scanning heap</code>.
- </td></tr><tr><td><code class="structfield">heap_blks_scanned</code></td><td><code class="type">bigint</code></td><td>
- Number of heap blocks scanned. This counter only advances when the
- phase is <code class="literal">seq scanning heap</code>.
- </td></tr><tr><td><code class="structfield">index_rebuild_count</code></td><td><code class="type">bigint</code></td><td>
- Number of indexes rebuilt. This counter only advances when the phase
- is <code class="literal">rebuilding index</code>.
- </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="CLUSTER-PHASES"><p class="title"><strong>Table 27.27. CLUSTER and VACUUM FULL Phases</strong></p><div class="table-contents"><table class="table" summary="CLUSTER and VACUUM FULL Phases" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Phase</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">initializing</code></td><td>
- The command is preparing to begin scanning the heap. This phase is
- expected to be very brief.
- </td></tr><tr><td><code class="literal">seq scanning heap</code></td><td>
- The command is currently scanning the table using a sequential scan.
- </td></tr><tr><td><code class="literal">index scanning heap</code></td><td>
- <code class="command">CLUSTER</code> is currently scanning the table using an index scan.
- </td></tr><tr><td><code class="literal">sorting tuples</code></td><td>
- <code class="command">CLUSTER</code> is currently sorting tuples.
- </td></tr><tr><td><code class="literal">writing new heap</code></td><td>
- <code class="command">CLUSTER</code> is currently writing the new heap.
- </td></tr><tr><td><code class="literal">swapping relation files</code></td><td>
- The command is currently swapping newly-built files into place.
- </td></tr><tr><td><code class="literal">rebuilding index</code></td><td>
- The command is currently rebuilding an index.
- </td></tr><tr><td><code class="literal">performing final cleanup</code></td><td>
- The command is performing final cleanup. When this phase is
- completed, <code class="command">CLUSTER</code>
- or <code class="command">VACUUM FULL</code> will end.
- </td></tr></tbody></table></div></div><br class="table-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="monitoring-locks.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="monitoring.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="dynamic-trace.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">27.3. Viewing Locks </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 27.5. Dynamic Tracing</td></tr></table></div></body></html>
|