System tables and views reference - Amazon Redshift

System tables and views reference

System tables and views

Amazon Redshift has many system tables and views that contain information about how the system is functioning. You can query these system tables and views the same way that you would query any other database tables. This section shows some sample system table queries and explains:

  • How different types of system tables and views are generated

  • What types of information you can obtain from these tables

  • How to join Amazon Redshift system tables to catalog tables

  • How to manage the growth of system table log files

Some system tables can only be used by AWS staff for diagnostic purposes. The following sections discuss the system tables that can be queried for useful information by system administrators or other database users.

Note

System tables are not included in automated or manual cluster backups (snapshots). STL system views retain seven days of log history. Retaining logs doesn't require any customer action, but if you want to store log data for more than 7 days, you have to periodically copy it to other tables or unload it to Amazon S3.

Types of system tables and views

There are several types of system tables and views:

  • SVV views contain information about database objects with references to transient STV tables.

  • SYS views are used to monitor query and workload usage for provisioned clusters and serverless workgroups.

  • STL views are generated from logs that have been persisted to disk to provide a history of the system.

  • STV tables are virtual system tables that contain snapshots of the current system data. They are based on transient in-memory data and are not persisted to disk-based logs or regular tables.

  • SVCS views provide details about queries on both the main and concurrency scaling clusters.

  • SVL views provide details about queries on main clusters.

System tables and views do not use the same consistency model as regular tables. It is important to be aware of this issue when querying them, especially for STV tables and SVV views. For example, given a regular table t1 with a column c1, you would expect that the following query to return no rows:

select * from t1 where c1 > (select max(c1) from t1)

However, the following query against a system table might well return rows:

select * from stv_exec_state where currenttime > (select max(currenttime) from stv_exec_state)

The reason this query might return rows is that currenttime is transient and the two references in the query might not return the same value when evaluated.

On the other hand, the following query might well return no rows:

select * from stv_exec_state where currenttime = (select max(currenttime) from stv_exec_state)

Visibility of data in system tables and views

There are two classes of visibility for data in system tables and views: visible to users and visible to superusers.

Only users with superuser privileges can see the data in those tables that are in the superuser-visible category. Regular users can see data in the user-visible tables. To give a regular user access to superuser-visible tables, grant SELECT privilege on that table to the regular user. For more information, see GRANT.

By default, in most user-visible tables, rows generated by another user are invisible to a regular user. If a regular user is given SYSLOG ACCESS UNRESTRICTED, that user can see all rows in user-visible tables, including rows generated by another user. For more information, see ALTER USER or CREATE USER. All rows in SVV_TRANSACTIONS are visible to all users. For more information about data visibility, see the AWS re:Post knowledge base article How can I allow Amazon Redshift database regular users permission to view data in system tables from other users for my cluster?.

For metadata views, Amazon Redshift doesn't allow visibility to users that are granted SYSLOG ACCESS UNRESTRICTED.

Note

Giving a user unrestricted access to system tables gives the user visibility to data generated by other users. For example, STL_QUERY and STL_QUERY_TEXT contain the full text of INSERT, UPDATE, and DELETE statements, which might contain sensitive user-generated data.

A superuser can see all rows in all tables. To give a regular user access to superuser-visible tables, GRANT SELECT privilege on that table to the regular user.

Filtering system-generated queries

The query-related system tables and views, such as SVL_QUERY_SUMMARY, SVL_QLOG, and others, usually contain a large number of automatically generated statements that Amazon Redshift uses to monitor the status of the database. These system-generated queries are visible to a superuser, but are seldom useful. To filter them out when selecting from a system table or system view that uses the userid column, add the condition userid > 1 to the WHERE clause. For example:

select * from svl_query_summary where userid > 1