Using System Tables

Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system's resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical data equivalent to load streams, query profiles, tuple mover operations, and more. Because Vertica collects and retains this information automatically, you don't have to manually set anything.

You can write queries against system tables with full SELECT support the same way you perform query operations on base and temporary tables. 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:

VMart=> CREATE TABLE mynode AS SELECT node_name, node_state, node_address 
FROM nodes;
CREATE TABLE
VMart=> 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 the following schemas:

  • V_CATALOG information about persistent objects in the catalog
  • V_MONITORinformation 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 fully 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 of the 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 query uses the VMart example database (see Introducing the VMart Example Database) to obtain the number of rows and size occupied by each table in the database.

=> SELECT t.table_name AS table_name, 
    SUM(ps.wos_row_count + ps.ros_row_count) AS row_count,
    SUM(ps.wos_used_bytes + ps.ros_used_bytes) AS byte_count
   FROM tables t
   JOIN projections p ON t.table_id = p.anchor_table_id
   JOIN projection_storage ps on p.projection_name = ps.projection_name
   WHERE (ps.wos_used_bytes + ps.ros_used_bytes) > 500000
   GROUP BY t.table_name
   ORDER BY byte_count DESC;    
     table_name     | row_count | byte_count
--------------------+-----------+------------
 online_sales_fact  |   5000000 |  171987438
 store_sales_fact   |   5000000 |  108844666
 store_orders_fact  |    300000 |    9240800
 product_dimension  |     60000 |    2327964
 customer_dimension |     50000 |    2165897
 inventory_fact     |    300000 |    2045900
(6 rows)

The rest of the examples illustrate simple ways to use system tables in queries.

=> SELECT table_name FROM columns WHERE data_type ILIKE 'Numeric' GROUP BY table_name;
table_name 
------------
 n1
(1 row)
=> SELECT current_epoch, designed_fault_tolerance, current_fault_tolerance 
FROM SYSTEM;
 current_epoch | designed_fault_tolerance | current_fault_tolerance
---------------+--------------------------+-------------------------
           492 |                        1 |                       1
(1 row)
=> SELECT node_name, total_user_session_count, executed_query_count FROM 
query_metrics;
 node_name           | total_user_session_count | executed_query_count 
---------------------+--------------------------+----------------------
 v_vmart_node0001    |                       53 |                   42
 v_vmart_node0002    |                       53 |                    0
 v_vmart_node0003    |                       42 |                  120
 v_vmart_node0004    |                       53 |                    0
(4 rows)
=> SELECT table_schema FROM primary_keys;
 table_schema
--------------
 public
 public
 public
 public
 public
 public
 public
 public
 public
 store
 online_sales
 online_sales
(12 rows)