Vertica Tip: The System Table for System Tables

This blog post was authored by Sarah Lemaire.

Most of you probably know that Vertica provides system tables that allow you to monitor

• System resources
• Background processes
• Workload
• Performance
• Catalog size

These tables help you to profile, diagnose, and view historical data equivalent to load streams, query profiles, Tuple Mover operations. For detailed information, see Using System Tables.

But do you know that there’s a system table that contains information about all of Vertica’s system tables? Of course, the name of the table is SYSTEM_TABLES.

Here’s what you might see if you query the SYSTEM_TABLES system table: => SELECT * FROM SYSTEM_TABLES ... -[ RECORD 3 ]-----------------+---------------------------- table_schema_id | 8301 table_schema | v_catalog table_id | 10318 table_name | node_subscription_change_phases table_description | Phases during subscription change execution is_superuser_only | f is_monitorable | t is_accessible_during_lockdown | f -[ RECORD 4 ]-----------------+----------------------------- table_schema_id | 8301 table_schema | v_catalog table_id | 10230 table_name | storage_locations table_description | Storage Location Information is_superuser_only | f is_monitorable | t is_accessible_during_lockdown | f -[ RECORD 5 ]-----------------+----------------------------- table_schema_id | 8301 table_schema | v_catalog table_id | 10336 table_name | log_tables table_description | Audit table accesses for all categories is_superuser_only | f is_monitorable | t is_accessible_during_lockdown | f ...