System Views ​

This section is a reference for a set of system views that capture information about resource utilization, inflight queries, active loads, and other operational statistics. You can query these views via the Query Editor in Yellowbrick Manager, ybsql, or other SQL query tools.

Note: The information_schema tables and views are provided only for compatibility with Postgres-compliant tools. For database administration, Yellowbrick recommends using the views in the sys schema, as documented in this section. These views provide significant Yellowbrick-specific information that is not available under information_schema.

The system views belong to the sys schema. For example, in ybsql run this command:

premdb=# set schema 'sys';
SET

Then list the views that belong to the schema:

\dvS
                  List of relations
 Schema |             Name              | Type | Owner
--------+-------------------------------+------+--------
 sys    | alert                         | view | ybuser
 sys    | backup                        | view | ybuser
 sys    | backup_chain                  | view | ybuser
 sys    | backup_snapshot               | view | ybuser
 sys    | blade                         | view | ybuser
 sys    | cluster                       | view | ybuser
 sys    | cluster_node_status           | view | ybuser
 sys    | cluster_usage                 | view | ybuser
 sys    | column                        | view | ybuser
...

Alternatively, you can specify the schema in the query itself. For example:

premdb=# select * from sys.log_query;
...

No one can create and modify tables in the sys schema.

premdb=# create table table2(c1 int, c2 int) distribute on(c1) sort on(c2);
ERROR:  permission denied to create "sys.table2"
DETAIL:  System catalog modifications are currently disallowed.

You cannot combine user-defined tables and system tables or views in the same query. One exception to this rule is the sys.const table, which can be queried in conjunction with regular tables.

Retention policy ​

A log retention policy determines the amount of history that is retained in the following sys.log_* views:

• sys.log_analyze
• sys.log_backup
• sys.log_authentication
• sys.log_cluster_event
• sys.log_load
• sys.log_query
• sys.log_query_analyze
• sys.log_replica_status
• sys.log_restore
• sys.log_retention
• sys.log_session
• sys.log_unload

For more details, see Retention policies for system relations.

Visibility of Data in System Views ​

Note that all users can see the list of system views:

premdb=# set schema 'sys';
SET
premdb=# \dS
               List of relations
 Schema |         Name         | Type |  Owner
--------+----------------------+------+----------
 sys    | alert                | view | ybdadmin
 sys    | backup               | view | ybdadmin
...

However, regular users may not have access to the data they return. Superusers can see all rows for all databases. The visibility of system views for non-superusers depends on the privileges those users have been granted.

To grant access to the data in system views, use the GRANT command. For example, you can grant VIEW QUERY TEXT or TRACE QUERY privileges so that non-superusers can see rows for queries that they did not run themselves:

premdb=# create user tracer1 password '******';
CREATE ROLE
premdb=# grant trace query on system to tracer1;
GRANT

User tracer1 can now see all queries that are running or have been run on the current database but has visibility into all databases on the system. The user would have to connect to different databases to see the queries for each database.

The following table defines the visibility of a number of different system views for regular users (non-superusers):