STL_VACUUM - Amazon Redshift
Table columnsSample queries

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:

  • Started

  • Started Delete Only

  • Started Delete Only (Sorted >= nn%)

    Only the delete phase was started for a VACUUM FULL. The sort phase was skipped because the table was already sorted at or above the sort threshold.

  • Started Sort Only

  • Started Reindex

  • Finished

    Time the operation completed for the table. To find out how long a vacuum operation took on a specific table, subtract the Started time from the Finished time for a particular transaction ID and table ID.

  • Skipped

    The table was skipped because the table was fully sorted and no rows were marked for deletion.

  • Skipped (delete only)

    The table was skipped because DELETE ONLY was specified and no rows were marked for deletion.

  • Skipped (sort only)

    The table was skipped because SORT ONLY was specified and the table was already sorted fully sorted.

  • Skipped (sort only, sorted>=xx%).

    The table was skipped because SORT ONLY was specified and the table was already sorted at or above the sort threshold.

  • Skipped (0 rows).

    The table was skipped because it was empty.

  • VacuumBG.

    An automatic vacuum operation was performed in the background.

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...