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.