Overview of System Tables and Views

A Redshift cluster has many system tables and views you can query to understand how your system behaves. However, before we start, here are some naming conventions. There are two types of system tables: STL and STV tables. There are also some Views. Let’s see:

STL tables: (Tables with stl_ prefix) These system tables have logs, provide a history of the system, and persist.

STV tables: (Tables with stv_ prefix) They are virtual tables with snapshots of the current system state data.

SVL views: (Views with svl_ prefix) They are system views that contain references to STL tables and logs for more detailed information.

SVV views: (Views with svv_ prefix) They are system views that contain references to STV tables and snapshots for more detailed information.: (Views with svv_ prefix) They are system views that contain references to STV tables and snapshots for more detailed information.

STL system tables for logging

We said earlier that these tables have logs and provide a history of the system. These tables reside on every node in the data warehouse cluster and take the information from the logs and format them into usable tables for system administrators.

STL log tables retain two to five days of log history, depending on log usage and available disk space. For more, you may periodically unload it into Amazon S3.

Let’s see below some important ones for an Analyst and reference:

STL_ALERT_EVENT_LOG

Records alert when the query optimizer identifies conditions that might indicate performance issues. You may use the STL_ALERT_EVENT_LOG table to identify opportunities to improve query performance.

STL_CONNECTION_LOG

This table logs all connections, disconnections, and authentication attempts.

STL_DDLTEXT

The table holds DDL statements that were running on the system. These DDL statements include:

• CREATE SCHEMA, TABLE, VIEW
• DROP SCHEMA, TABLE, VIEW
• ALTER SCHEMA, TABLE

STL_EXPLAIN

The table contains the EXPLAIN plan for a query that was submitted for execution.

STL_WLM_ Tables

The system tables with the STL_WLM_ prefix will help you understand better how your workload management strategy works.

STL_QUERY & STL_QUERYTEXT

STL_QUERY returns execution information about a database query. STL_QUERYTEXT returns the query text for SQL commands. These commands are:

• SELECT, SELECT INTO
• INSERT, UPDATE, DELETE
• COPY
• VACUUM, ANALYZE
• CREATE TABLE AS (CTAS)

STL_VACUUM

The table displays raw and blocks statistics for tables we vacuumed.

Of course, there are even more tables. So here is a full list of all the STL tables in Amazon Redshift.

STV system tables for snapshot data

STV are tables with snapshots of the current system state data.

Let’s see below some important ones for an Analyst and reference:

STV_EXEC_STATE

Use the STV_EXEC_STATE table to find out information about queries and query steps that are actively running on Amazon Redshift.

STV_LOCKS

Use the STV_LOCKS table to view any current updates on tables in the database.

STV_PARTITIONS

To monitor your current Disk Space Usage, you have to query the STV_PARTITIONS table.

STV_WLM_ Tables

The system tables with the STV_WLM_ prefix will help you understand better how your workload management strategy works.

Of course, there are even more tables. So here is a full list of all the STV tables in Amazon Redshift.

System views

The System Views provide quicker and easier access to commonly queried data found in STV and STL tables.

SVV_DISKUSAGE

The SVV_DISKUSAGE view contains information about data allocation for the tables in a database.

SVL_QUERY_SUMMARY

Always keep an eye on the SVL_QUERY_SUMMARY view. If you see queries with the is_diskbased field set to true, you might have to revise your Workload strategy and assign more memory to it.

SVV_TABLE_INFO

This is an important system table that holds information related to the performance of all queries and your cluster. In addition, SVV_TABLE_INFO contains summary information about your tables. Read more in the Monitoring Query Performance section of our Amazon Redshift guide.

SVV_VACUUM_PROGRESS

The system view SVV_VACUUM_PROGRESS returns an estimate of the remaining time for a vacuuming process that is currently running.

Of course, there are even more views. So here is a full list of all the System Views in Amazon Redshift.

System catalog tables

System catalog tables have a PG_ prefix. The system catalogs store schema metadata, such as information about tables and columns. Like PostgreSQL, Redshift has the standard PostgreSQL catalog tables like pg_namespace or pg_group.

PG_DEFAULT_ACL

The table contains information about default access privileges. For more details, go here.

PG_LIBRARY

Stores information about user-defined libraries. For more details, go here.

PG_STATISTIC_INDICATOR

Stores information about the number of rows inserted or deleted since the last ANALYZE. The PG_STATISTIC_INDICATOR table is updated frequently following DML operations, so statistics are approximate. For more details, go here.

PG_TABLE_DEF

Stores information about table columns. For more details, go here.