System tables and views reference
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.
Topics
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 > 1Migrating provisioned-only queries to SYS
monitoring view queries
Migrating from provisioned clusters to Amazon Redshift Serverless
If you're migrating a provisioned cluster to Amazon Redshift Serverless, you may have queries using the following system views, which only store data from provisioned clusters.
-
All STL views
-
All STV views
-
All SVCS views
-
All SVL views
-
Some SVV views
-
For a full list of SVV views unsupported in Amazon Redshift Serverless, see the list at the bottom of Monitoring queries and workloads with Amazon Redshift Serverless in the Amazon Redshift Management Guide.
-
To keep using your queries, refit them to use columns defined in the SYS monitoring views that correspond to the columns in your provisioned-only views. To see the mapping relation between the provisioned-only views and the SYS monitoring views, go to System view mapping for migrating to SYS monitoring views
Updating queries while staying on a provisioned cluster
If you're not migrating to Amazon Redshift Serverless, you might still want to update your existing queries. The SYS monitoring views are designed for ease of use and reduced complexity, providing a complete array of metrics for effective monitoring and troubleshooting. Using SYS views such as SYS_QUERY_HISTORY and SYS_QUERY_DETAIL that consolidate the information of multiple provisioned-only views, you can streamline your queries.
Improving query identifier tracking using
the SYS monitoring views
SYS monitoring views such as such as SYS_QUERY_HISTORY and SYS_QUERY_DETAIL contain the query_id column, which holds the identifier for users’ queries. Similarly, provisioned-only views such as STL_QUERY and SVL_QLOG contain the query column, which also holds the query identifiers. However, the query identifiers recorded in the SYS system views are different from those recorded in the provisioned-only views.
The difference between the SYS views’ query_id column values and the provisioned-only views’ query column values is as follows:
-
In SYS views, the query_id column records user-submitted queries in their original form. The Amazon Redshift optimizer might break them down into child queries for improved performance, but a single query you run will still only have a single row in SYS_QUERY_HISTORY. If you want to see the individual child queries, you can find them in SYS_QUERY_DETAIL.
-
In provisioned-only views, the query column records queries at the child query level. If the Amazon Redshift optimizer rewrites your original query into multiple child queries, there will be multiple rows in STL_QUERY with differing query identifier values for a single query you run.
When you migrate your monitoring and diagnostic queries from provisioned-only views to SYS views, consider this difference and edit your queries accordingly. For more information on how Amazon Redshift processes queries, see Query planning and execution workflow.
Example
For an example of how Amazon Redshift records queries differently in provisioned-only and SYS monitoring views, see the following sample query. This is the query written as you would run it in Amazon Redshift.
SELECT
s_name
, COUNT(*) AS numwait
FROM
supplier,
lineitem l1,
orders,
nation
WHERE s_suppkey = l1.l_suppkey
AND o_orderkey = l1.l_orderkey
AND o_orderstatus = 'F'
AND l1.l_receiptdate > l1.l_commitdate
AND EXISTS (SELECT
*
FROM
lineitem l2
WHERE l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey <> l1.l_suppkey )
AND NOT EXISTS (SELECT
*
FROM
lineitem l3
WHERE l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey <> l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate )
AND s_nationkey = n_nationkey
AND n_name = 'UNITED STATES'
GROUP BY
s_name
ORDER BY
numwait DESC
, s_name LIMIT 100;Under the hood the Amazon Redshift query optimizer rewrites the above user-submitted query into 5 child queries.
The first child query creates a temporary table to materialize a subquery.
CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey
, l_suppkey
, s_name ) AS SELECT
l1.l_orderkey
, l1.l_suppkey
, public.supplier.s_name
FROM
public.lineitem AS l1,
public.nation,
public.orders,
public.supplier
WHERE l1.l_commitdate < l1.l_receiptdate
AND l1.l_orderkey = public.orders.o_orderkey
AND l1.l_suppkey = public.supplier.s_suppkey
AND public.nation.n_name = 'UNITED STATES'::CHAR(8)
AND public.nation.n_nationkey = public.supplier.s_nationkey
AND public.orders.o_orderstatus = 'F'::CHAR(1);The second child query collects statistics from the temporary table.
padb_fetch_sample: select count(*) from volt_tt_606590308b512;The third child query creates another temporary table to materialize another subquery, referencing the temporary table created above.
CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey
, l_suppkey) AS (SELECT
volt_tt_606590308b512.l_orderkey
, volt_tt_606590308b512.l_suppkey
FROM
public.lineitem AS l2,
volt_tt_606590308b512
WHERE l2.l_suppkey <> volt_tt_606590308b512.l_suppkey
AND l2.l_orderkey = volt_tt_606590308b512.l_orderkey)
EXCEPT distinct (SELECT volt_tt_606590308b512.l_orderkey, volt_tt_606590308b512.l_suppkey
FROM public.lineitem AS l3, volt_tt_606590308b512
WHERE l3.l_commitdate < l3.l_receiptdate
AND l3.l_suppkey <> volt_tt_606590308b512.l_suppkey
AND l3.l_orderkey = volt_tt_606590308b512.l_orderkey);The fourth child query again collects the temporary table’s statistics.
padb_fetch_sample: select count(*) from volt_tt_606590308c2efThe last child query uses the temporary tables created above to generate the output.
SELECT
volt_tt_606590308b512.s_name AS s_name
, COUNT(*) AS numwait
FROM
volt_tt_606590308b512,
volt_tt_606590308c2ef
WHERE volt_tt_606590308b512.l_orderkey = volt_tt_606590308c2ef.l_orderkey
AND volt_tt_606590308b512.l_suppkey = volt_tt_606590308c2ef.l_suppkey
GROUP BY
1
ORDER BY
2 DESC
, 1 ASC LIMIT 100;In the provisioned-only system view STL_QUERY, Amazon Redshift records five rows at the child query level, as follows:
SELECT userid, xid, pid, query, querytxt::varchar(100);
FROM stl_query
WHERE xid = 48237350
ORDER BY xid, starttime;
userid | xid | pid | query | querytxt
--------+----------+------------+----------+------------------------------------------------------------------------------------------------------
101 | 48237350 | 1073840810 | 12058151 | CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey, l_suppkey, s_name) AS SELECT l1.l_orderkey, l1.l
101 | 48237350 | 1073840810 | 12058152 | padb_fetch_sample: select count(*) from volt_tt_606590308b512
101 | 48237350 | 1073840810 | 12058156 | CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey, l_suppkey) AS (SELECT volt_tt_606590308b512.l_or
101 | 48237350 | 1073840810 | 12058168 | padb_fetch_sample: select count(*) from volt_tt_606590308c2ef
101 | 48237350 | 1073840810 | 12058170 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.
(5 rows)In the SYS monitoring view SYS_QUERY_HISTORY, Amazon Redshift records the query as follows:
SELECT user_id, transaction_id, session_id, query_id, query_text::varchar(100)
FROM sys_query_history
WHERE transaction_id = 48237350
ORDER BY start_time;
user_id | transaction_id | session_id | query_id | query_text
---------+----------------+------------+----------+------------------------------------------------------------------------------------------------------
101 | 48237350 | 1073840810 | 12058149 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.In SYS_QUERY_DETAIL, you can find child query-level details using the query_id value from SYS_QUERY_HISTORY. The child_query_sequence column shows the order the child queries are executed in. For more information on the columns in SYS_QUERY_DETAIL, see SYS_QUERY_DETAIL.
select user_id,
query_id,
child_query_sequence,
stream_id,
segment_id,
step_id,
start_time,
end_time,
duration,
blocks_read,
blocks_write,
local_read_io,
remote_read_io,
data_skewness,
time_skewness,
is_active,
spilled_block_local_disk,
spilled_block_remote_disk
from sys_query_detail
where query_id = 12058149
and step_id = -1
order by query_id,
child_query_sequence,
stream_id,
segment_id,
step_id;
user_id | query_id | child_query_sequence | stream_id | segment_id | step_id | start_time | end_time | duration | blocks_read | blocks_write | local_read_io | remote_read_io | data_skewness | time_skewness | is_active | spilled_block_local_disk | spilled_block_remote_disk
---------+----------+----------------------+-----------+------------+---------+----------------------------+----------------------------+----------+-------------+--------------+---------------+----------------+---------------+---------------+-----------+--------------------------+---------------------------
101 | 12058149 | 1 | 0 | 0 | -1 | 2023-09-27 15:40:38.512415 | 2023-09-27 15:40:38.533333 | 20918 | 0 | 0 | 0 | 0 | 0 | 44 | f | 0 | 0
101 | 12058149 | 1 | 1 | 1 | -1 | 2023-09-27 15:40:39.931437 | 2023-09-27 15:40:39.972826 | 41389 | 12 | 0 | 12 | 0 | 0 | 77 | f | 0 | 0
101 | 12058149 | 1 | 2 | 2 | -1 | 2023-09-27 15:40:40.584412 | 2023-09-27 15:40:40.613982 | 29570 | 32 | 0 | 32 | 0 | 0 | 25 | f | 0 | 0
101 | 12058149 | 1 | 2 | 3 | -1 | 2023-09-27 15:40:40.582038 | 2023-09-27 15:40:40.615758 | 33720 | 0 | 0 | 0 | 0 | 0 | 1 | f | 0 | 0
101 | 12058149 | 1 | 3 | 4 | -1 | 2023-09-27 15:40:46.668766 | 2023-09-27 15:40:46.705456 | 36690 | 24 | 0 | 15 | 0 | 0 | 17 | f | 0 | 0
101 | 12058149 | 1 | 4 | 5 | -1 | 2023-09-27 15:40:46.707209 | 2023-09-27 15:40:46.709176 | 1967 | 0 | 0 | 0 | 0 | 0 | 18 | f | 0 | 0
101 | 12058149 | 1 | 4 | 6 | -1 | 2023-09-27 15:40:46.70656 | 2023-09-27 15:40:46.71289 | 6330 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 1 | 5 | 7 | -1 | 2023-09-27 15:40:46.71405 | 2023-09-27 15:40:46.714343 | 293 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 2 | 0 | 0 | -1 | 2023-09-27 15:40:52.083907 | 2023-09-27 15:40:52.087854 | 3947 | 0 | 0 | 0 | 0 | 0 | 35 | f | 0 | 0
101 | 12058149 | 2 | 1 | 1 | -1 | 2023-09-27 15:40:52.089632 | 2023-09-27 15:40:52.091129 | 1497 | 0 | 0 | 0 | 0 | 0 | 11 | f | 0 | 0
101 | 12058149 | 2 | 1 | 2 | -1 | 2023-09-27 15:40:52.089008 | 2023-09-27 15:40:52.091306 | 2298 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 3 | 0 | 0 | -1 | 2023-09-27 15:40:56.882013 | 2023-09-27 15:40:56.897282 | 15269 | 0 | 0 | 0 | 0 | 0 | 29 | f | 0 | 0
101 | 12058149 | 3 | 1 | 1 | -1 | 2023-09-27 15:40:59.718554 | 2023-09-27 15:40:59.722789 | 4235 | 0 | 0 | 0 | 0 | 0 | 13 | f | 0 | 0
101 | 12058149 | 3 | 2 | 2 | -1 | 2023-09-27 15:40:59.800382 | 2023-09-27 15:40:59.807388 | 7006 | 0 | 0 | 0 | 0 | 0 | 58 | f | 0 | 0
101 | 12058149 | 3 | 3 | 3 | -1 | 2023-09-27 15:41:06.488685 | 2023-09-27 15:41:06.493825 | 5140 | 0 | 0 | 0 | 0 | 0 | 56 | f | 0 | 0
101 | 12058149 | 3 | 3 | 4 | -1 | 2023-09-27 15:41:06.486206 | 2023-09-27 15:41:06.497756 | 11550 | 0 | 0 | 0 | 0 | 0 | 2 | f | 0 | 0
101 | 12058149 | 3 | 4 | 5 | -1 | 2023-09-27 15:41:06.499201 | 2023-09-27 15:41:06.500851 | 1650 | 0 | 0 | 0 | 0 | 0 | 15 | f | 0 | 0
101 | 12058149 | 3 | 4 | 6 | -1 | 2023-09-27 15:41:06.498609 | 2023-09-27 15:41:06.500949 | 2340 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 3 | 5 | 7 | -1 | 2023-09-27 15:41:06.502945 | 2023-09-27 15:41:06.503282 | 337 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 4 | 0 | 0 | -1 | 2023-09-27 15:41:06.62899 | 2023-09-27 15:41:06.631452 | 2462 | 0 | 0 | 0 | 0 | 0 | 22 | f | 0 | 0
101 | 12058149 | 4 | 1 | 1 | -1 | 2023-09-27 15:41:06.632313 | 2023-09-27 15:41:06.63391 | 1597 | 0 | 0 | 0 | 0 | 0 | 20 | f | 0 | 0
101 | 12058149 | 4 | 1 | 2 | -1 | 2023-09-27 15:41:06.631726 | 2023-09-27 15:41:06.633813 | 2087 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 5 | 0 | 0 | -1 | 2023-09-27 15:41:12.571974 | 2023-09-27 15:41:12.584234 | 12260 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0
101 | 12058149 | 5 | 0 | 1 | -1 | 2023-09-27 15:41:12.569815 | 2023-09-27 15:41:12.585391 | 15576 | 0 | 0 | 0 | 0 | 0 | 4 | f | 0 | 0
101 | 12058149 | 5 | 1 | 2 | -1 | 2023-09-27 15:41:13.758513 | 2023-09-27 15:41:13.76401 | 5497 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0
101 | 12058149 | 5 | 1 | 3 | -1 | 2023-09-27 15:41:13.749 | 2023-09-27 15:41:13.772987 | 23987 | 0 | 0 | 0 | 0 | 0 | 32 | f | 0 | 0
101 | 12058149 | 5 | 2 | 4 | -1 | 2023-09-27 15:41:13.799526 | 2023-09-27 15:41:13.813506 | 13980 | 0 | 0 | 0 | 0 | 0 | 62 | f | 0 | 0
101 | 12058149 | 5 | 2 | 5 | -1 | 2023-09-27 15:41:13.798823 | 2023-09-27 15:41:13.813651 | 14828 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
(28 rows)System table query, process,
and session ids
When analyzing query, process, and session ids that appear in system tables, be aware of the following:
-
The query id value (in columns such as
query_idandquery) can be reused over time. -
The process id or session id value (in columns such as
process_id,pid, andsession_id) can be reused over time. -
The transaction id value (in columns such as
transaction_idandxid) is unique.
