Redshift: System Tables and Views

System Tables and Views

-- Only some of the system tables and views can be accessed by users.
-- In some of the system tables and views, Users are able to see the data for their own activities. SuperUser will be able to see for all the users.


STL_ tables
   System History is logged in STL_ tables. Generated from logs which are persisted in the disk.

STV_ tables
    Virtual system tables which Contains current system information. 

SVV_ Views 
      Systems views that reference  transient STV table.

SVL_ views
     Views containing only references to STL tables

PG_ tables
    System catalog tables which stores schema metadata, such as table definitions, users and their privileges.
   
System tables are not included in automated or manual cluster backups (snapshots). STL log
tables only retain approximately two to five days of log history

List of widely used System tables/views

PG_TABLE_DEF - List of all table columns.

PG_USER - List of all users
 -- User: rsdb is internal user used by Amazon Redshift
 -- To list only user defined, use usesysid > 1 filter

SVL_QLOG - To see recent queries
 -- It is a view on top of STL_QUERY. Log of all queries run against database.
 -- To filter out system generated queries use the filter user_id>1

SVL_QUERY_SUMMARY
  -- contains queries executed by Redshift, not other utility and DDL commands.

STV_RECENTS - To get list of running queries.
  -- Use filter where status='Running';
 
STL_LOAD_ERRORS - To see load errors