Observability reference

The WarehousePG (WHPG) Observability extension creates the observability schema in each database where you install the WHPG Extension. The schema provides a number of views used by the WHPG Exporter to retrieve database and cluster metrics.

Cluster health and configuration

View name	Description
`gp_segment_configuration`	Provides direct access to the `pg_catalog.gp_segment_configuration` table.
`segments_not_in_preferred_role`	Shows segments whose current role does not match their preferred role.
`gp_segment_configuration_status`	Provides details of segment configuration.
`table_gp_segment_config`	Provides the live segment configuration table, listing hostname, content ID, role, status, and directory for each segment.
`coordinator_counts`	Provides counts of the coordinator and standby nodes, including status (up/down) and synchronization mode.
`segment_counts`	Provides counts of all segments, broken down by primary/mirror roles, status (up/down), and synchronization status.
`pg_hba_file_rules`	Provides the rules defined in the `pg_hba.conf` file, showing types, databases, users, and authentication methods.
`pg_settings`	Provides access to the current PostgreSQL and WarehousePG configuration settings.

Query and activity monitoring

View name	Description
`user_queries`	Provides currently running user queries, including PID, user name, state, query text, and whether the query is waiting on a lock.
`user_queries_count`	Provides a count of currently running user queries.
`user_queries_120s`	Shows active queries that have been running longer than 120 seconds, including the duration and the full query text.
`user_queries_count_by_state`	Provides a breakdown of user queries by their state (active, idle, idle in transaction) and counts of blocked and long-running queries.
`user_queries_count_by_user`	Provides a count of user queries grouped by the connecting user name.
`transaction_counts`	Provides the total number of committed and rolled-back transactions executed in the database.
`pg_stat_database`	Provides access to the `pg_stat_database` statistics, including transaction commit/rollback counts.

Database size and maintenance

View name	Description
`gp_database_size`	Provides access to the total database size.
`table_with_most_dead_rows`	Provides a list of the Top 20 tables with the highest number of dead rows, which may indicate a need for vacuuming.
`bloated_table_list`	Provides a list of the Top 20 tables with the highest ratio of dead rows to total rows.
`top_n_largest_tables`	Provides the Top 20 largest tables by disk size.
`top_n_not_analyzed_tables`	Provides the Top 20 tables with the largest number of columns missing statistics.
`top_n_not_vacuumed_tables`	Provides the Top 20 tables with dead rows that have never been vacuumed or auto-vacuumed.
`top_n_skewed_tables`	Provides the Top 20 tables with a data skew coefficient greater than 1.5.
`table_need_index`	Provides tables that may need an index, identified by a high number of sequential scans compared to index scans.
`object_list`	Provides a list of tables and views grouped by schema, including a count of total objects, tables, and views per schema.

Spill files

View name	Description
`gp_spill_file_details`	Provides access to the details of spill files used by running queries.
`total_spill_file_details`	Provides the total spill file count and size in bytes, aggregated per database.
`spill_count_per_primary_seg`	Provides the total spill file count and size in bytes, aggregated per primary segment ID.
`total_segment_spill_file_details`	Provides the total spill file count and size per segment, joining spill details with segment configuration status.
`top_n_spilling_queries`	Provides the Top 20 queries currently causing the most disk spill.

Resource groups

View name	Description
`resgroup_config`	Provides the resource group configuration (columns are different between Warehouse versions 6.x and 7.x).
`resgroup_status`	Provides the status of resource groups, including running, queued, and total executed queries, and total queue time.
`resgroup_status_config`	Provides a combination of resource group status and configuration details.
`resgroup_status_per_host`	Provides resource group usage status per host (e.g., CPU, memory usage).
`resgroup_status_per_segment`	Provides resource group usage status per segment.

Could this page be better? Report a problem or suggest an addition!