myVertica  

Useful System Tables

Vertica provides system tables that let you monitor your database.

Overview

Vertica provides an application programming interface (API) for monitoring various features and functions within a database in the form of system tables. These tables provide a 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.

System Tables and Schemas

Vertica stores system tables in the following schemas:
  • V_CATALOG schema
    System tables in this schema provide information about objects in your database, such as tables and projections.
    Examples:
    RESOURCE_POOLS: Displays information about the parameters specified for the resource pool by CREATE RESOURCE POOL statement.
    USERS: Provides information about all users in the database.
    PROJECTIONS: Provides information about projections.
  • V_MONITOR schema
    System tables in this schema provide information about the health of your Vertica database.
    Examples:
    CONFIGURATION_PARAMETERS: Provides information about configuration parameters currently in use by the system that are configurable at the database,node, or session level.
    LOCKS: Monitors lock grants and requests for all nodes. A table call with no results indicates that no locks are in use.
    MEMORY_USAGE: Records system resource history for memory usage. This is useful for comparing memory that Vertica uses versus memory in use by the entire system.

Using System Tables

Vertica collects and retains information stored in the system tables automatically, so you do not have to manually set anything.
You query the system tables the same way you perform query operations on base tables or temporary tables – by using SELECT statements.
You can use expressions, predicates, aggregates, analytics, subqueries, and joins. You can also save system table query results into a user table for future analysis.

System Table Organization

Most of the system tables are grouped into the following categories:
  • 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.

Learn More

To view a list of all Vertica system tables, see the following topics in the Vertica core documentation:
V_CATALOG Schema System Tables
V_MONITOR Schema System Tables