Advanced query insights reference

Select a documentation version:

This document describes the configuration parameters, telemetry dimensions, and observability views and functions for advanced query insights in AlloyDB Omni.

For more information about enabling and querying advanced query insights, see Monitor query performance using advanced query insights.

Configuration parameters

To apply parameter changes that don't require a database restart, run ALTER SYSTEM SET followed by SELECT pg_reload_conf(). The following table describes the configuration parameters for advanced query insights:

Parameter Type Default value Restart required Description
google_insights_v2.enabled boolean off Yes for initial enablement with shared_preload_libraries. No for a subsequent reload with pg_reload_conf(). Enables or disables advanced query insights telemetry collection across the instance.
google_insights_v2.qi_max_query_string_length integer 10240 No (reload with pg_reload_conf()) Sets the maximum byte length (1024 to 102400) recorded for normalized SQL templates, where literal values are masked as positional parameters such as $1 and $2.
google_insights_v2.qi_preserve_comments boolean off No (reload with pg_reload_conf()) Controls whether SQL comments are preserved inside normalized query strings.
google_insights_v2.qi_track_wait_event_types boolean on No (reload with pg_reload_conf()) Records high-level wait_class bottleneck categories, such as CPU, IO, Lock, Timeout, and IPC.
google_insights_v2.qi_track_wait_events boolean on No (reload with pg_reload_conf()) Records granular PostgreSQL wait_event names, such as DataFileExtend, transactionid, and WALWrite.
google_insights_v2.qi_track_client_address boolean off No (reload with pg_reload_conf()) Records connecting client IP addresses in query and client address statistics views.
google_insights_v2.qi_track_tag boolean off No (reload with pg_reload_conf()) Enables tracking of tags, such as SQLCommenter tags, in SQL comments.
google_insights_v2.qi_track_active_queries boolean off No (reload with pg_reload_conf()) Enables real-time tracking of active queries and open transactions.

Observability views and metrics by dimension

The following sections list the observability views and metric columns available in the google_insights_v2 schema for each telemetry dimension.

Queries

The queries dimension tracks execution statistics and normalized SQL text for queries across the instance. To preserve SQL comments in normalized query text, set google_insights_v2.qi_preserve_comments to on.

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_query_view
    • observability_service_query_coarse_1_view
    • observability_service_query_coarse_2_view
  • Related columns:
    • query_id: hash identifier of the normalized SQL query.
    • query: normalized SQL query text.
    • database: logical database where the query ran.
    • username: database role that executed the query.
    • total_execution_time: cumulative execution time in microseconds.
    • total_wait_time: cumulative wait time in microseconds.
    • total_read_time: cumulative disk read time in microseconds.
    • total_write_time: cumulative disk write time in microseconds.
    • total_rows: total number of rows returned or affected by the query.
    • total_shared_blocks_hit: total number of shared buffer hits.
    • total_shared_blocks_read: total number of shared blocks read from disk.
    • count: total number of times the query was executed.
    • recorded_on: timestamp when the telemetry interval was recorded.

Wait event types

The wait event types dimension records high-level wait_class bottleneck categories when google_insights_v2.qi_track_wait_event_types is set to on (enabled by default).

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_wait_event_no_query_view
    • observability_service_wait_event_stats_by_query_view
  • Related columns:
    • wait_class: wait event category, such as IO, Lock, LWLock, CPU, or Internal wait event.
    • wait_event: granular PostgreSQL wait event name within the wait class.
    • time_spent: cumulative wait time for the wait event type in microseconds.
    • count: total number of times the wait event type occurred.
    • database: logical database where the wait event occurred.
    • username: database role associated with the wait event.
    • tag_id: numeric identifier of the associated SQLCommenter tag set.
    • tag_string: raw SQL comment string containing the application tags.
    • recorded_on: timestamp when the telemetry interval was recorded.
    • query_id: hash identifier of the query (in observability_service_wait_event_stats_by_query_view).

Wait events

The wait events dimension records granular PostgreSQL wait_event names when google_insights_v2.qi_track_wait_events is set to on (enabled by default).

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_wait_event_no_query_view
    • observability_service_wait_event_stats_by_query_view
  • Related columns:
    • wait_event: specific PostgreSQL wait event name, such as DataFileExtend, DataFileRead, WALWrite, or Internal wait event.
    • wait_class: wait event category that the wait event belongs to.
    • time_spent: cumulative wait time for the wait event in microseconds.
    • count: total number of times the wait event occurred.
    • database: logical database where the wait event occurred.
    • username: database role associated with the wait event.
    • tag_id: numeric identifier of the associated SQLCommenter tag set.
    • tag_string: raw SQL comment string containing the application tags.
    • recorded_on: timestamp when the telemetry interval was recorded.
    • query_id: hash identifier of the query (in observability_service_wait_event_stats_by_query_view).

Databases

The databases dimension tracks resource consumption and query volume per logical database when google_insights_v2.enabled is set to on.

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_db_load_conn_view
    • observability_service_query_view
    • observability_service_wait_event_no_query_view
  • Related columns:
    • database: logical database name.
    • username: database role that executed queries in the database.
    • total_execution_time: cumulative query execution time in the database in microseconds.
    • total_wait_time: cumulative wait time in the database in microseconds.
    • count: total number of query executions in the database.
    • recorded_on: timestamp when the telemetry interval was recorded.

Users and client IP addresses

The users and client IP addresses dimension tracks load by database role (username) by default. To also collect connecting client IP addresses (client_address), you must set google_insights_v2.qi_track_client_address to on (off by default).

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_query_view
    • observability_service_client_address_stats_by_query_view
  • Related columns:
    • client_address: connecting client IP address (when google_insights_v2.qi_track_client_address is on).
    • username: database role name.
    • database: logical database name.
    • query_id: hash identifier of the normalized SQL query.
    • time_spent: cumulative query execution time for the client address in microseconds.
    • count: total number of query executions for the user or client IP address.
    • recorded_on: timestamp when the telemetry interval was recorded.

Tags

The tags dimension tracks query execution statistics by application tag metadata. To collect tag metrics, you must set google_insights_v2.qi_track_tag to on (off by default). When set to off, these views return no rows.

The following views and columns are available for this dimension:

  • Observability views:
    • observability_service_db_load_tag_view
    • observability_service_tag_view
  • Related columns:
    • tag_id: numeric identifier of the application tag combination.
    • tag_string: raw SQL comment string containing the application tags.
    • application: application name extracted from SQLCommenter tags.
    • db_driver: database driver extracted from SQLCommenter tags.
    • framework_name: application framework name extracted from SQLCommenter tags.
    • framework_controller: framework controller extracted from SQLCommenter tags.
    • framework_action: framework action extracted from SQLCommenter tags.
    • framework_route: framework route extracted from SQLCommenter tags.
    • traceparent: W3C trace context extracted from SQLCommenter tags.
    • database: logical database name where the tagged query ran.
    • username: database role that executed the tagged query.
    • total_execution_time: cumulative query execution time for the tag combination in microseconds.
    • count: total number of query executions for the tag combination.
    • recorded_on: timestamp when the telemetry interval was recorded.

Active queries

The active queries dimension tracks running queries and open transactions in real time. To query active sessions, you must set google_insights_v2.qi_track_active_queries to on. When set to off, querying these views returns an error.

The following views and columns are available for this dimension:

  • Observability views:
    • stat_activity
    • stat_activity_v3
  • Related columns:
    • pid: backend process ID. To terminate a session, pass the process ID to the PostgreSQL pg_terminate_backend(pid) system function.
    • normalized_query_hash: hash identifier of the normalized SQL query.
    • normalized_query: normalized SQL text of the active query.
    • state: current backend state, such as active or idle in transaction.
    • backend_start: timestamp when the current session started. To calculate session duration in seconds, compute EXTRACT(EPOCH FROM (clock_timestamp() - backend_start)).
    • xact_start: timestamp when the current transaction started. To calculate transaction duration in seconds, compute EXTRACT(EPOCH FROM (clock_timestamp() - xact_start)).
    • query_start: timestamp when the current query started. To calculate query duration in seconds, compute EXTRACT(EPOCH FROM (clock_timestamp() - query_start)).
    • wait_event_type: wait event category if the backend is waiting.
    • wait_event: wait event name if the backend is waiting.
    • database: logical database name for the active session.
    • username: database role name for the active session.
    • application_name: connecting application name.
    • client_addr: connecting client IP address.