STL_VACUUM
Displays row and block statistics for tables that have been vacuumed.
The view shows information specific to when each vacuum operation started and finished, and demonstrates the benefits of running the operation. For information about the requirements for running this command, see the VACUUM command description.
This view is visible only to superusers. For more information, see Visibility of data in system tables and views.
Table columns
Column name | Data type | Description |
---|---|---|
userid | integer | The ID of the user who generated the entry. |
xid | bigint | The transaction ID for the VACUUM statement. You can join this table to the STL_QUERY view to see the individual SQL statements that are run for a given VACUUM transaction. If you vacuum the whole database, each table is vacuumed in a separate transaction. |
table_id | integer | The Table ID. |
status | character(30) | The status of the VACUUM operation for each table. Possible values are the following:
For more information about the VACUUM sort threshold setting, see VACUUM. |
rows | bigint | The actual number of rows in the table plus any
deleted rows that are still stored on disk (waiting to be vacuumed).
This column shows the count before the vacuum started for rows with
a Started status, and the count after the
vacuum for rows with a Finished status.
|
sortedrows | integer | The number of rows in the table that are sorted.
This column shows the count before the vacuum started for rows with
Started in the Status column, and the
count after the vacuum for rows with Finished
in the Status column. |
blocks | integer | The total number of data blocks used to store the
table data before the vacuum operation (rows with a
Started status) and after the vacuum
operation (Finished column). Each data block
uses 1 MB. |
max_merge_partitions | integer | This column is used for performance analysis and represents the maximum number of partitions that vacuum can process for the table per merge phase iteration. (Vacuum sorts the unsorted region into one or more sorted partitions. Depending on the number of columns in the table and the current Amazon Redshift configuration, the merge phase can process a maximum number of partitions in a single merge iteration. The merge phase will still work if the number of sorted partitions exceeds the maximum number of merge partitions, but more merge iterations will be required.) |
eventtime | timestamp | When the vacuum operation started or finished. |
Sample queries
The following query reports vacuum statistics for table 108313. The table was vacuumed following a series of inserts and deletes.
select xid, table_id, status, rows, sortedrows, blocks, eventtime from stl_vacuum where table_id=108313 order by eventtime; xid | table_id | status | rows | sortedrows | blocks | eventtime -------+----------+----------------------+------------+------------+--------+--------------------- 14294 | 108313 | Started | 1950266199 | 400043488 | 280887 | 2016-05-19 17:36:01 14294 | 108313 | Finished | 600099388 | 600099388 | 88978 | 2016-05-19 18:26:13 15126 | 108313 | Skipped(sorted>=95%) | 600099388 | 600099388 | 88978 | 2016-05-19 18:26:38
At the start of the VACUUM, the table contained 1,950,266,199 rows stored in 280,887 1 MB blocks. In the delete phase (transaction 14294) completed, vacuum reclaimed space for the deleted rows. The ROWS column shows a value of 400,043,488, and the BLOCKS column has dropped from 280,887 to 88,978. The vacuum reclaimed 191,909 blocks (191.9 GB) of disk space.
In the sort phase (transaction 15126), the vacuum was able to skip the table because the rows were inserted in sort key order.
The following example shows the statistics for a SORT ONLY vacuum on the SALES table (table 110116 in this example) after a large INSERT operation:
vacuum sort only sales; select xid, table_id, status, rows, sortedrows, blocks, eventtime from stl_vacuum order by xid, table_id, eventtime; xid |table_id| status | rows |sortedrows|blocks| eventtime ----+--------+-----------------+-------+----------+------+-------------------- ... 2925| 110116 |Started Sort Only|1379648| 172456 | 132 | 2011-02-24 16:25:21... 2925| 110116 |Finished |1379648| 1379648 | 132 | 2011-02-24 16:26:28...