Amazon RedShift: Key highlights on "System Tables"

This post has key points on System Tables used in Redshift.

• Redshift has following system tables
• STL (System Table Logging) - These system tables are generated from log files. Logging tables has STL prefix.
• STV (System Table Virtual) -  These virtual system tables has snapshots of the current system data. These virtual tables has STV as prefix.
• System Views - Subset of data found in STL and STV tables is available in the system views, SVL (System View Logging) and SVV (System View Virtual) respectively. 
• System Catalog tables - These tables store schema metadata, such as information on tables and columns. These tables have PG prefix.
• For system tables to return a table metadata, the schema of the table should be added to the search_path. Below command adds sql_class database to search_path. Once it is done, we can retrieve any table metadata in sql_class database.
• set search_path to '$user','public','sql_class';
• All system tables exists in pg_catalog database in Redshift.
• Some of the Redshift system tables:
• pg_table_def: Contains table information like columns, datatypes etc.
• pg_aggregate 
• svv_diskusage : Is used to find the data distribution skew in each table like number of rows stored in each slice for a given table.
• svl_statementtext : Checks all statements that used the analyze command.
• stv_blocklist : Is used to find how many 1MB blocks of disk space are used for each table
• stl_load_commits: we can check the details of the COPY operation.
• stl_query: Can find out when a table has been last analyzed
• stv_table_perm: Has table Id's.
• svl_qlog: Contains elapsed time of queries
• svl_query_summary: We can determine if query is writing to disk. If is_diskenabled field is ("t") for any step, then that step wrote data to disk.

View my next post on Key Highlights on Compression.