Using System Tables

Vertica provides system tables for obtaining information about system resources, background processes, workload, and performance—for example, load streams, query profiles, and tuple mover operations. Vertica collects and refreshes this information automatically.

You can query system tables using expressions, predicates, aggregates, analytics, subqueries, and joins. You can also save system table query results into a user table for future analysis. For example, the following query creates a table, mynode, selecting three node-related columns from the V_CATALOG.NODES system table:

=> CREATE TABLE mynode AS SELECT node_name, node_state, node_address FROM nodes;
CREATE TABLE
=> SELECT * FROM mynode;
    node_name     | node_state |  node_address  
------------------+------------+----------------
 v_vmart_node0001 | UP         | 192.168.223.11
(1 row)

You cannot query system tables if the database cluster is in a recovering state. The database refuses connection requests and cannot be monitored. Vertica also does not support DDL and DML operations on system tables.

Where System Tables Reside

System tables are grouped into two schemas:

V_CATALOG: Provides information about persistent objects in the catalog
V_MONITOR: Provides information about transient system state

These schemas reside in the default search path so there is no need to specify schema.table in your queries unless you change the search path to exclude V_MONITOR or V_CATALOG or both.

The system tables that make up the monitoring API are described in the SQL Reference Manual. You can also use the following command to view all the system tables and their schema:

SELECT * FROM system_tables ORDER BY table_schema, table_name;

How System Tables Are Organized

Most system tables are grouped into the following areas:

System information
System resources
Background processes
Workload and performance

Vertica reserves some memory to help monitor busy systems. Using simple system table queries makes it easier to troubleshoot issues. See also SYSQUERY and SYSDATA pools under Built-in pools topic in SQL Reference Manual.

You can use external monitoring tools or scripts to query the system tables and act upon the information, as necessary. For example, when a host failure causes the K-safety level to fall below the desired level, the tool or script can notify the database administrator and/or appropriate IT personnel of the change, typically in the form of an e-mail.

Privileges

You can GRANT and REVOKE privileges on system tables, with the following restrictions:

You cannot GRANT privileges on system tables to the SYSMONITOR or PSEUDOSUPERUSER roles.
You cannot GRANT on system schemas.

Querying Case-Sensitive Data in System Tables

Some system table data might be stored in mixed case. For example, Vertica stores mixed-case identifier names the way you specify them in the CREATE statement, even though case is ignored when you reference them in queries. When these object names appear as data in the system tables, you'll encounter errors if you use the equality (=) predicate because the case must match the stored identifier. In particular, V_CATALOG.TABLES.TABLE_SCHEMA and V_CATALOG.TABLES.TABLE_NAME columns are case sensitive with equality predicates.

If you don't know how the identifiers are stored, use the case-insensitive operator ILIKE instead of equality predicates.

For example, given the following schema:

=> CREATE SCHEMA SS;
=> CREATE TABLE SS.TT (c1 int);
=> CREATE PROJECTION SS.TTP1 AS SELECT * FROM ss.tt UNSEGMENTED ALL NODES;
=> INSERT INTO ss.tt VALUES (1);

If you run a query using the = predicate, Vertica returns 0 rows:

=> SELECT table_schema, table_name FROM v_catalog.tables WHERE table_schema ='ss'; 
table_schema | table_name
--------------+------------
(0 rows)

Using the case-insensitive ILIKE predicate returns the expected results:

=> SELECT table_schema, table_name FROM v_catalog.tables WHERE table_schema ILIKE 'ss'; 
table_schema | table_name
--------------+------------
 SS           | TT
(1 row)

Examples

The following examples illustrate simple ways to use system tables in queries.