Usage
All scripts can be executed using Node.js. Replace <param_name> and
<param_value> with actual values.
Bash: node <skill_dir>/scripts/<script_name>.js '{"<param_name>": "<param_value>"}'
PowerShell: node <skill_dir>/scripts/<script_name>.js '{\"<param_name>\": \"<param_value>\"}'
Note: The scripts automatically load the environment variables from various .env
files. Do not ask the user to set vars unless skill executions fails due to env
var absence.
Scripts
get_query_metrics
Fetches query level cloudmonitoring data (timeseries metrics) for queries
running in Postgres instance using a PromQL query. Take projectID and instanceID
from the user for which the metrics timeseries data needs to be fetched. To use
this tool, you must provide the Google Cloud projectId and a PromQL query.
Generate PromQL query for Postgres query metrics. Use the provided metrics and
rules to construct queries, Get the labels like instance_id, query_hash from
user intent. If query_hash is provided then use the per_query metrics. Query
hash and query id are same.
Defaults:
- Interval: Use a default interval of
5m for _over_time aggregation
functions unless a different window is specified by the user.
PromQL Query Examples:
- Basic Time Series:
avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m])
- Top K:
topk(30, avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Mean:
avg(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Minimum:
min(min_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Maximum:
max(max_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Sum:
sum(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Count streams:
count(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
- Percentile with groupby on resource_id, database:
quantile by ("resource_id","database")(0.99,avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))
Available Metrics List: metricname. description. monitored resource. labels.
resource_id label format is project_id:instance_id which is actually instance
id only. aggregate is the aggregated values for all query stats, Use aggregate
metrics if query id is not provided. For perquery metrics do not fetch
querystring unless specified by user specifically. Have the aggregation on query
hash to avoid fetching the querystring. Do not use latency metrics for anything.
cloudsql.googleapis.com/database/postgresql/insights/aggregate/latencies:
Aggregated query latency distribution. cloudsql_instance_database. user,
client_addr, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time:
Accumulated aggregated query execution time since the last sample.
cloudsql_instance_database. user, client_addr, project_id,
resource_id.cloudsql.googleapis.com/database/postgresql/insights/aggregate/io_time:
Accumulated aggregated IO time since the last sample.
cloudsql_instance_database. user, client_addr, io_type,
project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/aggregate/lock_time:
Accumulated aggregated lock wait time since the last sample.
cloudsql_instance_database. user, client_addr, lock_type,
project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/aggregate/row_count:
Aggregated number of retrieved or affected rows since the last sample.
cloudsql_instance_database. user, client_addr, project_id,
resource_id.cloudsql.googleapis.com/database/postgresql/insights/aggregate/shared_blk_access_count:
Aggregated shared blocks accessed by statement execution.
cloudsql_instance_database. user, client_addr, access_type,
project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/latencies:
Per query latency distribution. cloudsql_instance_database. user,
client_addr, querystring, query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/execution_time:
Accumulated execution times per user per database per query.
cloudsql_instance_database. user, client_addr, querystring,
query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/io_time:
Accumulated IO time since the last sample per query.
cloudsql_instance_database. user, client_addr, io_type,
querystring, query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/lock_time:
Accumulated lock wait time since the last sample per query.
cloudsql_instance_database. user, client_addr, lock_type,
querystring, query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/row_count:
The number of retrieved or affected rows since the last sample per query.
cloudsql_instance_database. user, client_addr, querystring,
query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/perquery/shared_blk_access_count:
Shared blocks accessed by statement execution per query.
cloudsql_instance_database. user, client_addr, access_type,
querystring, query_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/latencies:
Query latency distribution. cloudsql_instance_database. user,
client_addr, action, application, controller, db_driver,
framework, route, tag_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/execution_time:
Accumulated execution times since the last sample.
cloudsql_instance_database. user, client_addr, action,
application, controller, db_driver, framework, route, tag_hash,
project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/io_time:
Accumulated IO time since the last sample per tag.
cloudsql_instance_database. user, client_addr, action,
application, controller, db_driver, framework, route, io_type,
tag_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/lock_time:
Accumulated lock wait time since the last sample per tag.
cloudsql_instance_database. user, client_addr, action,
application, controller, db_driver, framework, route, lock_type,
tag_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/shared_blk_access_count:
Shared blocks accessed by statement execution per tag.
cloudsql_instance_database. user, client_addr, action,
application, controller, db_driver, framework, route,
access_type, tag_hash, project_id, resource_id.cloudsql.googleapis.com/database/postgresql/insights/pertag/row_count: The
number of retrieved or affected rows since the last sample per tag.
cloudsql_instance_database. user, client_addr, action,
application, controller, db_driver, framework, route, tag_hash,
project_id, resource_id.
Parameters
| Name |
Type |
Description |
Required |
Default |
| projectId |
string |
The Id of the Google Cloud project. |
Yes |
|
| query |
string |
The promql query to execute. |
Yes |
|
get_query_plan
Generate a PostgreSQL EXPLAIN plan in JSON format for a single SQL
statement—without executing it. This returns the optimizer's estimated plan,
costs, and rows (no ANALYZE, no extra options). Use in production safely for
plan inspection, regression checks, and query tuning workflows.
Parameters
| Name |
Type |
Description |
Required |
Default |
| query |
string |
The SQL statement for which you want |
Yes |
|
| : : : to generate plan (omit the EXPLAIN : : : |
|
|
|
|
| : : : keyword). : : : |
|
|
|
|
get_system_metrics
Fetches system level cloudmonitoring data (timeseries metrics) for a Postgres
instance using a PromQL query. Take projectId and instanceId from the user for
which the metrics timeseries data needs to be fetched. To use this tool, you
must provide the Google Cloud projectId and a PromQL query.
Generate PromQL query for Postgres system metrics. Use the provided metrics
and rules to construct queries, Get the labels like instance_id from user
intent.
Defaults:
- Interval: Use a default interval of
5m for _over_time aggregation
functions unless a different window is specified by the user.
PromQL Query Examples:
- Basic Time Series:
avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m])
- Top K:
topk(30, avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Mean:
avg(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Minimum:
min(min_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Maximum:
max(max_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Sum:
sum(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Count streams:
count(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
- Percentile with groupby on database_id:
quantile by ("database_id")(0.99,avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))
Available Metrics List: metricname. description. monitored resource. labels.
database_id is actually the instance id and the format is
project_id:instance_id.
cloudsql.googleapis.com/database/postgresql/new_connection_count: Count of
new connections added to the postgres instance. cloudsql_database.
database, project_id, database_id.cloudsql.googleapis.com/database/postgresql/backends_in_wait: Number of
backends in wait in postgres instance. cloudsql_database. backend_type,
wait_event, wait_event_type, project_id, database_id.cloudsql.googleapis.com/database/postgresql/transaction_count: Delta count
of number of transactions. cloudsql_database. database,
transaction_type, project_id, database_id.cloudsql.googleapis.com/database/memory/components: Memory stats
components in percentage as usage, cache and free memory for the database.
cloudsql_database. component, project_id, database_id.cloudsql.googleapis.com/database/postgresql/external_sync/max_replica_byte_lag:
Replication lag in bytes for Postgres External Server (ES) replicas.
Aggregated across all DBs on the replica. cloudsql_database. project_id,
database_id.cloudsql.googleapis.com/database/cpu/utilization: Current CPU utilization
represented as a percentage of the reserved CPU that is currently in use.
Values are typically numbers between 0.0 and 1.0 (but might exceed 1.0).
Charts display the values as a percentage between 0% and 100% (or more).
cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/disk/bytes_used_by_data_type: Data
utilization in bytes. cloudsql_database. data_type, project_id,
database_id.cloudsql.googleapis.com/database/disk/read_ops_count: Delta count of data
disk read IO operations. cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/disk/write_ops_count: Delta count of data
disk write IO operations. cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/postgresql/num_backends_by_state: Number
of connections to the Cloud SQL PostgreSQL instance, grouped by its state.
cloudsql_database. database, state, project_id, database_id.cloudsql.googleapis.com/database/postgresql/num_backends: Number of
connections to the Cloud SQL PostgreSQL instance. cloudsql_database.
database, project_id, database_id.cloudsql.googleapis.com/database/network/received_bytes_count: Delta count
of bytes received through the network. cloudsql_database. project_id,
database_id.cloudsql.googleapis.com/database/network/sent_bytes_count: Delta count of
bytes sent through the network. cloudsql_database. destination,
project_id, database_id.cloudsql.googleapis.com/database/postgresql/deadlock_count: Number of
deadlocks detected for this database. cloudsql_database. database,
project_id, database_id.cloudsql.googleapis.com/database/postgresql/blocks_read_count: Number of
disk blocks read by this database. The source field distingushes actual
reads from disk versus reads from buffer cache. cloudsql_database.
database, source, project_id, database_id.cloudsql.googleapis.com/database/postgresql/tuples_processed_count: Number
of tuples(rows) processed for a given database for operations like insert,
update or delete. cloudsql_database. operation_type, database,
project_id, database_id.cloudsql.googleapis.com/database/postgresql/tuple_size: Number of tuples
(rows) in the database. cloudsql_database. database, tuple_state,
project_id, database_id.cloudsql.googleapis.com/database/postgresql/vacuum/oldest_transaction_age:
Age of the oldest transaction yet to be vacuumed in the Cloud SQL PostgreSQL
instance, measured in number of transactions that have happened since the
oldest transaction. cloudsql_database. oldest_transaction_type,
project_id, database_id.cloudsql.googleapis.com/database/replication/log_archive_success_count:
Number of successful attempts for archiving replication log files.
cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/replication/log_archive_failure_count:
Number of failed attempts for archiving replication log files.
cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/postgresql/transaction_id_utilization:
Current utilization represented as a percentage of transaction IDs consumed
by the Cloud SQL PostgreSQL instance. Values are typically numbers between
0.0 and 1.0. Charts display the values as a percentage between 0% and 100% .
cloudsql_database. project_id, database_id.cloudsql.googleapis.com/database/postgresql/num_backends_by_application:
Number of connections to the Cloud SQL PostgreSQL instance, grouped by
applications. cloudsql_database. application, project_id,
database_id.cloudsql.googleapis.com/database/postgresql/tuples_fetched_count: Total
number of rows fetched as a result of queries per database in the PostgreSQL
instance. cloudsql_database. database, project_id, database_id.cloudsql.googleapis.com/database/postgresql/tuples_returned_count: Total
number of rows scanned while processing the queries per database in the
PostgreSQL instance. cloudsql_database. database, project_id,
database_id.cloudsql.googleapis.com/database/postgresql/temp_bytes_written_count:
Total amount of data (in bytes) written to temporary files by the queries
per database. cloudsql_database. database, project_id, database_id.cloudsql.googleapis.com/database/postgresql/temp_files_written_count:
Total number of temporary files used for writing data while performing
algorithms such as join and sort. cloudsql_database. database,
project_id, database_id.
Parameters
| Name |
Type |
Description |
Required |
Default |
| projectId |
string |
The Id of the Google Cloud project. |
Yes |
|
| query |
string |
The promql query to execute. |
Yes |
|
list_active_queries
List the top N (default 50) currently running queries (state='active') from
pg_stat_activity, ordered by longest-running first. Returns pid, user, database,
application_name, client_addr, state, wait_event_type/wait_event,
backend/xact/query start times, computed query_duration, and the SQL text.
Parameters
| Name |
Type |
Description |
Required |
Default |
| min_duration |
string |
Optional: Only |
No |
`1 |
| : : : show queries : : minute` : |
|
|
|
|
| : : : running at : : : |
|
|
|
|
| : : : least this long : : : |
|
|
|
|
| : : : (e.g., '1 : : : |
|
|
|
|
| : : : minute', '1 : : : |
|
|
|
|
| : : : second', '2 : : : |
|
|
|
|
| : : : seconds'). : : : |
|
|
|
|
| exclude_application_names |
string |
Optional: A |
No |
`` |
| : : : comma-separated : : : |
|
|
|
|
| : : : list of : : : |
|
|
|
|
| : : : application : : : |
|
|
|
|
| : : : names to : : : |
|
|
|
|
| : : : exclude from : : : |
|
|
|
|
| : : : the query : : : |
|
|
|
|
| : : : results. This : : : |
|
|
|
|
| : : : is useful for : : : |
|
|
|
|
| : : : filtering out : : : |
|
|
|
|
| : : : queries from : : : |
|
|
|
|
| : : : specific : : : |
|
|
|
|
| : : : applications : : : |
|
|
|
|
| : : : (e.g., 'psql', : : : |
|
|
|
|
| : : : 'pgAdmin', : : : |
|
|
|
|
| : : : 'DBeaver'). The : : : |
|
|
|
|
| : : : match is : : : |
|
|
|
|
| : : : case-sensitive. : : : |
|
|
|
|
| : : : Whitespace : : : |
|
|
|
|
| : : : around commas : : : |
|
|
|
|
| : : : and names is : : : |
|
|
|
|
| : : : automatically : : : |
|
|
|
|
| : : : handled. If : : : |
|
|
|
|
| : : : this parameter : : : |
|
|
|
|
| : : : is omitted, no : : : |
|
|
|
|
| : : : applications : : : |
|
|
|
|
| : : : are excluded. : : : |
|
|
|
|
| limit |
integer |
Optional: The |
No |
50 |
| : : : maximum number : : : |
|
|
|
|
| : : : of rows to : : : |
|
|
|
|
| : : : return. : : : |
|
|
|
|
list_database_stats
Parameters
| Name |
Type |
Description |
Required |
Default |
| database_name |
string |
Optional: A specific |
No |
`` |
| : : : database name pattern to : : : |
|
|
|
|
| : : : search for. : : : |
|
|
|
|
| include_templates |
boolean |
Optional: Whether to |
No |
false |
| : : : include template : : : |
|
|
|
|
| : : : databases in the : : : |
|
|
|
|
| : : : results. : : : |
|
|
|
|
| database_owner |
string |
Optional: A specific |
No |
`` |
| : : : database owner name : : : |
|
|
|
|
| : : : pattern to search for. : : : |
|
|
|
|
| default_tablespace |
string |
Optional: A specific |
No |
`` |
| : : : default tablespace name : : : |
|
|
|
|
| : : : pattern to search for. : : : |
|
|
|
|
| order_by |
string |
Optional: The field to |
No |
`` |
| : : : order the results by. : : : |
|
|
|
|
| : : : Valid values are 'size' : : : |
|
|
|
|
| : : : and 'commit'. : : : |
|
|
|
|
| limit |
integer |
Optional: The maximum |
No |
10 |
| : : : number of rows to : : : |
|
|
|
|
| : : : return. : : : |
|
|
|
|
list_locks
Identifies all locks held by active processes showing the process ID, user,
query text, and an aggregated list of all transactions and specific locks
(relation, mode, grant status) associated with each process.
list_query_stats
Lists performance statistics for executed queries ordered by total time,
filtering by database name pattern if provided. This tool requires the
pg_stat_statements extension to be installed. The tool returns the database
name, query text, execution count, timing metrics (total, min, max, mean), rows
affected, and buffer cache I/O statistics (hits and reads).
Parameters
| Name |
Type |
Description |
Required |
Default |
| database_name |
string |
Optional: The database name |
No |
`` |
| : : : to list query stats for. : : : |
|
|
|
|
| limit |
integer |
Optional: The maximum number |
No |
50 |
| : : : of results to return. : : : |
|
|
|
|
| : : : Defaults to 50. : : : |
|
|
|
|
long_running_transactions
Identifies and lists database transactions that exceed a specified time limit.
For each of the long running transactions, the output contains the process id,
database name, user name, application name, client address, state, connection
age, transaction age, query age, last activity age, wait event type, wait event,
and query string.
Parameters
| Name |
Type |
Description |
Required |
Default |
| min_duration |
string |
Optional: Only show |
No |
5 minutes |
| : : : transactions running at : : : |
|
|
|
|
| : : : least this long (e.g., '1 : : : |
|
|
|
|
| : : : minute', '15 minutes', '30 : : : |
|
|
|
|
| : : : seconds'). : : : |
|
|
|
|
| limit |
integer |
Optional: The maximum |
No |
20 |
| : : : number of long-running : : : |
|
|
|
|
| : : : transactions to return. : : : |
|
|
|
|
| : : : Defaults to 20. : : : |
|
|
|
|
1---2name: cloud-sql-postgres-health3description: Use these skills when you need to audit database health, identify storage bloat, find invalid indexes, analyze table statistics, and manage maintenance configurations like autovacuum.4license: Apache-2.05---67## Usage89All scripts can be executed using Node.js. Replace `<param_name>` and10`<param_value>` with actual values.1112**Bash:** `node <skill_dir>/scripts/<script_name>.js '{"<param_name>":13"<param_value>"}'`1415**PowerShell:** `node <skill_dir>/scripts/<script_name>.js '{\"<param_name>\":16\"<param_value>\"}'`1718Note: The scripts automatically load the environment variables from various .env19files. Do not ask the user to set vars unless skill executions fails due to env20var absence.2122## Scripts2324### get_query_metrics2526Fetches query level cloudmonitoring data (timeseries metrics) for queries27running in Postgres instance using a PromQL query. Take projectID and instanceID28from the user for which the metrics timeseries data needs to be fetched. To use29this tool, you must provide the Google Cloud `projectId` and a PromQL `query`.3031Generate PromQL `query` for Postgres query metrics. Use the provided metrics and32rules to construct queries, Get the labels like `instance_id`, `query_hash` from33user intent. If query_hash is provided then use the per_query metrics. Query34hash and query id are same.3536Defaults:37381. Interval: Use a default interval of `5m` for `_over_time` aggregation39 functions unless a different window is specified by the user.4041PromQL Query Examples:42431. Basic Time Series:44 `avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m])`452. Top K: `topk(30,46 avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`473. Mean:48 `avg(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`494. Minimum:50 `min(min_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`515. Maximum:52 `max(max_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`536. Sum:54 `sum(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`557. Count streams:56 `count(avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`578. Percentile with groupby on resource_id, database: `quantile by58 ("resource_id","database")(0.99,avg_over_time({"__name__"="cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time","monitored_resource"="cloudsql_instance_database","project_id"="my-projectId","resource_id"="my-projectId:my-instanceId"}[5m]))`5960Available Metrics List: metricname. description. monitored resource. labels.61resource_id label format is `project_id:instance_id` which is actually instance62id only. aggregate is the aggregated values for all query stats, Use aggregate63metrics if query id is not provided. For perquery metrics do not fetch64querystring unless specified by user specifically. Have the aggregation on query65hash to avoid fetching the querystring. Do not use latency metrics for anything.66671. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/latencies`:68 Aggregated query latency distribution. `cloudsql_instance_database`. `user`,69 `client_addr`, `project_id`, `resource_id`.702. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/execution_time`:71 Accumulated aggregated query execution time since the last sample.72 `cloudsql_instance_database`. `user`, `client_addr`, `project_id`,73 `resource_id`.743. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/io_time`:75 Accumulated aggregated IO time since the last sample.76 `cloudsql_instance_database`. `user`, `client_addr`, `io_type`,77 `project_id`, `resource_id`.784. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/lock_time`:79 Accumulated aggregated lock wait time since the last sample.80 `cloudsql_instance_database`. `user`, `client_addr`, `lock_type`,81 `project_id`, `resource_id`.825. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/row_count`:83 Aggregated number of retrieved or affected rows since the last sample.84 `cloudsql_instance_database`. `user`, `client_addr`, `project_id`,85 `resource_id`.866. `cloudsql.googleapis.com/database/postgresql/insights/aggregate/shared_blk_access_count`:87 Aggregated shared blocks accessed by statement execution.88 `cloudsql_instance_database`. `user`, `client_addr`, `access_type`,89 `project_id`, `resource_id`.907. `cloudsql.googleapis.com/database/postgresql/insights/perquery/latencies`:91 Per query latency distribution. `cloudsql_instance_database`. `user`,92 `client_addr`, `querystring`, `query_hash`, `project_id`, `resource_id`.938. `cloudsql.googleapis.com/database/postgresql/insights/perquery/execution_time`:94 Accumulated execution times per user per database per query.95 `cloudsql_instance_database`. `user`, `client_addr`, `querystring`,96 `query_hash`, `project_id`, `resource_id`.979. `cloudsql.googleapis.com/database/postgresql/insights/perquery/io_time`:98 Accumulated IO time since the last sample per query.99 `cloudsql_instance_database`. `user`, `client_addr`, `io_type`,100 `querystring`, `query_hash`, `project_id`, `resource_id`.10110. `cloudsql.googleapis.com/database/postgresql/insights/perquery/lock_time`:102 Accumulated lock wait time since the last sample per query.103 `cloudsql_instance_database`. `user`, `client_addr`, `lock_type`,104 `querystring`, `query_hash`, `project_id`, `resource_id`.10511. `cloudsql.googleapis.com/database/postgresql/insights/perquery/row_count`:106 The number of retrieved or affected rows since the last sample per query.107 `cloudsql_instance_database`. `user`, `client_addr`, `querystring`,108 `query_hash`, `project_id`, `resource_id`.10912. `cloudsql.googleapis.com/database/postgresql/insights/perquery/shared_blk_access_count`:110 Shared blocks accessed by statement execution per query.111 `cloudsql_instance_database`. `user`, `client_addr`, `access_type`,112 `querystring`, `query_hash`, `project_id`, `resource_id`.11313. `cloudsql.googleapis.com/database/postgresql/insights/pertag/latencies`:114 Query latency distribution. `cloudsql_instance_database`. `user`,115 `client_addr`, `action`, `application`, `controller`, `db_driver`,116 `framework`, `route`, `tag_hash`, `project_id`, `resource_id`.11714. `cloudsql.googleapis.com/database/postgresql/insights/pertag/execution_time`:118 Accumulated execution times since the last sample.119 `cloudsql_instance_database`. `user`, `client_addr`, `action`,120 `application`, `controller`, `db_driver`, `framework`, `route`, `tag_hash`,121 `project_id`, `resource_id`.12215. `cloudsql.googleapis.com/database/postgresql/insights/pertag/io_time`:123 Accumulated IO time since the last sample per tag.124 `cloudsql_instance_database`. `user`, `client_addr`, `action`,125 `application`, `controller`, `db_driver`, `framework`, `route`, `io_type`,126 `tag_hash`, `project_id`, `resource_id`.12716. `cloudsql.googleapis.com/database/postgresql/insights/pertag/lock_time`:128 Accumulated lock wait time since the last sample per tag.129 `cloudsql_instance_database`. `user`, `client_addr`, `action`,130 `application`, `controller`, `db_driver`, `framework`, `route`, `lock_type`,131 `tag_hash`, `project_id`, `resource_id`.13217. `cloudsql.googleapis.com/database/postgresql/insights/pertag/shared_blk_access_count`:133 Shared blocks accessed by statement execution per tag.134 `cloudsql_instance_database`. `user`, `client_addr`, `action`,135 `application`, `controller`, `db_driver`, `framework`, `route`,136 `access_type`, `tag_hash`, `project_id`, `resource_id`.13718. `cloudsql.googleapis.com/database/postgresql/insights/pertag/row_count`: The138 number of retrieved or affected rows since the last sample per tag.139 `cloudsql_instance_database`. `user`, `client_addr`, `action`,140 `application`, `controller`, `db_driver`, `framework`, `route`, `tag_hash`,141 `project_id`, `resource_id`.142143#### Parameters144145Name | Type | Description | Required | Default146:-------- | :----- | :---------------------------------- | :------- | :------147projectId | string | The Id of the Google Cloud project. | Yes |148query | string | The promql query to execute. | Yes |149150--------------------------------------------------------------------------------151152### get_query_plan153154Generate a PostgreSQL EXPLAIN plan in JSON format for a single SQL155statement—without executing it. This returns the optimizer's estimated plan,156costs, and rows (no ANALYZE, no extra options). Use in production safely for157plan inspection, regression checks, and query tuning workflows.158159#### Parameters160161| Name | Type | Description | Required | Default |162| :---- | :----- | :----------------------------------- | :------- | :------ |163| query | string | The SQL statement for which you want | Yes | |164: : : to generate plan (omit the EXPLAIN : : :165: : : keyword). : : :166167--------------------------------------------------------------------------------168169### get_system_metrics170171Fetches system level cloudmonitoring data (timeseries metrics) for a Postgres172instance using a PromQL query. Take projectId and instanceId from the user for173which the metrics timeseries data needs to be fetched. To use this tool, you174must provide the Google Cloud `projectId` and a PromQL `query`.175176Generate PromQL `query` for Postgres system metrics. Use the provided metrics177and rules to construct queries, Get the labels like `instance_id` from user178intent.179180Defaults:1811821. Interval: Use a default interval of `5m` for `_over_time` aggregation183 functions unless a different window is specified by the user.184185PromQL Query Examples:1861871. Basic Time Series:188 `avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m])`1892. Top K: `topk(30,190 avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`1913. Mean:192 `avg(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`1934. Minimum:194 `min(min_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`1955. Maximum:196 `max(max_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`1976. Sum:198 `sum(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`1997. Count streams:200 `count(avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`2018. Percentile with groupby on database_id: `quantile by202 ("database_id")(0.99,avg_over_time({"__name__"="cloudsql.googleapis.com/database/cpu/utilization","monitored_resource"="cloudsql_database","project_id"="my-projectId","database_id"="my-projectId:my-instanceId"}[5m]))`203204Available Metrics List: metricname. description. monitored resource. labels.205database_id is actually the instance id and the format is206`project_id:instance_id`.2072081. `cloudsql.googleapis.com/database/postgresql/new_connection_count`: Count of209 new connections added to the postgres instance. `cloudsql_database`.210 `database`, `project_id`, `database_id`.2112. `cloudsql.googleapis.com/database/postgresql/backends_in_wait`: Number of212 backends in wait in postgres instance. `cloudsql_database`. `backend_type`,213 `wait_event`, `wait_event_type`, `project_id`, `database_id`.2143. `cloudsql.googleapis.com/database/postgresql/transaction_count`: Delta count215 of number of transactions. `cloudsql_database`. `database`,216 `transaction_type`, `project_id`, `database_id`.2174. `cloudsql.googleapis.com/database/memory/components`: Memory stats218 components in percentage as usage, cache and free memory for the database.219 `cloudsql_database`. `component`, `project_id`, `database_id`.2205. `cloudsql.googleapis.com/database/postgresql/external_sync/max_replica_byte_lag`:221 Replication lag in bytes for Postgres External Server (ES) replicas.222 Aggregated across all DBs on the replica. `cloudsql_database`. `project_id`,223 `database_id`.2246. `cloudsql.googleapis.com/database/cpu/utilization`: Current CPU utilization225 represented as a percentage of the reserved CPU that is currently in use.226 Values are typically numbers between 0.0 and 1.0 (but might exceed 1.0).227 Charts display the values as a percentage between 0% and 100% (or more).228 `cloudsql_database`. `project_id`, `database_id`.2297. `cloudsql.googleapis.com/database/disk/bytes_used_by_data_type`: Data230 utilization in bytes. `cloudsql_database`. `data_type`, `project_id`,231 `database_id`.2328. `cloudsql.googleapis.com/database/disk/read_ops_count`: Delta count of data233 disk read IO operations. `cloudsql_database`. `project_id`, `database_id`.2349. `cloudsql.googleapis.com/database/disk/write_ops_count`: Delta count of data235 disk write IO operations. `cloudsql_database`. `project_id`, `database_id`.23610. `cloudsql.googleapis.com/database/postgresql/num_backends_by_state`: Number237 of connections to the Cloud SQL PostgreSQL instance, grouped by its state.238 `cloudsql_database`. `database`, `state`, `project_id`, `database_id`.23911. `cloudsql.googleapis.com/database/postgresql/num_backends`: Number of240 connections to the Cloud SQL PostgreSQL instance. `cloudsql_database`.241 `database`, `project_id`, `database_id`.24212. `cloudsql.googleapis.com/database/network/received_bytes_count`: Delta count243 of bytes received through the network. `cloudsql_database`. `project_id`,244 `database_id`.24513. `cloudsql.googleapis.com/database/network/sent_bytes_count`: Delta count of246 bytes sent through the network. `cloudsql_database`. `destination`,247 `project_id`, `database_id`.24814. `cloudsql.googleapis.com/database/postgresql/deadlock_count`: Number of249 deadlocks detected for this database. `cloudsql_database`. `database`,250 `project_id`, `database_id`.25115. `cloudsql.googleapis.com/database/postgresql/blocks_read_count`: Number of252 disk blocks read by this database. The source field distingushes actual253 reads from disk versus reads from buffer cache. `cloudsql_database`.254 `database`, `source`, `project_id`, `database_id`.25516. `cloudsql.googleapis.com/database/postgresql/tuples_processed_count`: Number256 of tuples(rows) processed for a given database for operations like insert,257 update or delete. `cloudsql_database`. `operation_type`, `database`,258 `project_id`, `database_id`.25917. `cloudsql.googleapis.com/database/postgresql/tuple_size`: Number of tuples260 (rows) in the database. `cloudsql_database`. `database`, `tuple_state`,261 `project_id`, `database_id`.26218. `cloudsql.googleapis.com/database/postgresql/vacuum/oldest_transaction_age`:263 Age of the oldest transaction yet to be vacuumed in the Cloud SQL PostgreSQL264 instance, measured in number of transactions that have happened since the265 oldest transaction. `cloudsql_database`. `oldest_transaction_type`,266 `project_id`, `database_id`.26719. `cloudsql.googleapis.com/database/replication/log_archive_success_count`:268 Number of successful attempts for archiving replication log files.269 `cloudsql_database`. `project_id`, `database_id`.27020. `cloudsql.googleapis.com/database/replication/log_archive_failure_count`:271 Number of failed attempts for archiving replication log files.272 `cloudsql_database`. `project_id`, `database_id`.27321. `cloudsql.googleapis.com/database/postgresql/transaction_id_utilization`:274 Current utilization represented as a percentage of transaction IDs consumed275 by the Cloud SQL PostgreSQL instance. Values are typically numbers between276 0.0 and 1.0. Charts display the values as a percentage between 0% and 100% .277 `cloudsql_database`. `project_id`, `database_id`.27822. `cloudsql.googleapis.com/database/postgresql/num_backends_by_application`:279 Number of connections to the Cloud SQL PostgreSQL instance, grouped by280 applications. `cloudsql_database`. `application`, `project_id`,281 `database_id`.28223. `cloudsql.googleapis.com/database/postgresql/tuples_fetched_count`: Total283 number of rows fetched as a result of queries per database in the PostgreSQL284 instance. `cloudsql_database`. `database`, `project_id`, `database_id`.28524. `cloudsql.googleapis.com/database/postgresql/tuples_returned_count`: Total286 number of rows scanned while processing the queries per database in the287 PostgreSQL instance. `cloudsql_database`. `database`, `project_id`,288 `database_id`.28925. `cloudsql.googleapis.com/database/postgresql/temp_bytes_written_count`:290 Total amount of data (in bytes) written to temporary files by the queries291 per database. `cloudsql_database`. `database`, `project_id`, `database_id`.29226. `cloudsql.googleapis.com/database/postgresql/temp_files_written_count`:293 Total number of temporary files used for writing data while performing294 algorithms such as join and sort. `cloudsql_database`. `database`,295 `project_id`, `database_id`.296297#### Parameters298299Name | Type | Description | Required | Default300:-------- | :----- | :---------------------------------- | :------- | :------301projectId | string | The Id of the Google Cloud project. | Yes |302query | string | The promql query to execute. | Yes |303304--------------------------------------------------------------------------------305306### list_active_queries307308List the top N (default 50) currently running queries (state='active') from309pg_stat_activity, ordered by longest-running first. Returns pid, user, database,310application_name, client_addr, state, wait_event_type/wait_event,311backend/xact/query start times, computed query_duration, and the SQL text.312313#### Parameters314315| Name | Type | Description | Required | Default |316| :------------------------ | :------ | :-------------- | :------- | :------ |317| min_duration | string | Optional: Only | No | `1 |318: : : show queries : : minute` :319: : : running at : : :320: : : least this long : : :321: : : (e.g., '1 : : :322: : : minute', '1 : : :323: : : second', '2 : : :324: : : seconds'). : : :325| exclude_application_names | string | Optional: A | No | `` |326: : : comma-separated : : :327: : : list of : : :328: : : application : : :329: : : names to : : :330: : : exclude from : : :331: : : the query : : :332: : : results. This : : :333: : : is useful for : : :334: : : filtering out : : :335: : : queries from : : :336: : : specific : : :337: : : applications : : :338: : : (e.g., 'psql', : : :339: : : 'pgAdmin', : : :340: : : 'DBeaver'). The : : :341: : : match is : : :342: : : case-sensitive. : : :343: : : Whitespace : : :344: : : around commas : : :345: : : and names is : : :346: : : automatically : : :347: : : handled. If : : :348: : : this parameter : : :349: : : is omitted, no : : :350: : : applications : : :351: : : are excluded. : : :352| limit | integer | Optional: The | No | `50` |353: : : maximum number : : :354: : : of rows to : : :355: : : return. : : :356357--------------------------------------------------------------------------------358359### list_database_stats360361#### Parameters362363| Name | Type | Description | Required | Default |364| :----------------- | :------ | :----------------------- | :------- | :------ |365| database_name | string | Optional: A specific | No | `` |366: : : database name pattern to : : :367: : : search for. : : :368| include_templates | boolean | Optional: Whether to | No | `false` |369: : : include template : : :370: : : databases in the : : :371: : : results. : : :372| database_owner | string | Optional: A specific | No | `` |373: : : database owner name : : :374: : : pattern to search for. : : :375| default_tablespace | string | Optional: A specific | No | `` |376: : : default tablespace name : : :377: : : pattern to search for. : : :378| order_by | string | Optional: The field to | No | `` |379: : : order the results by. : : :380: : : Valid values are 'size' : : :381: : : and 'commit'. : : :382| limit | integer | Optional: The maximum | No | `10` |383: : : number of rows to : : :384: : : return. : : :385386--------------------------------------------------------------------------------387388### list_locks389390Identifies all locks held by active processes showing the process ID, user,391query text, and an aggregated list of all transactions and specific locks392(relation, mode, grant status) associated with each process.393394--------------------------------------------------------------------------------395396### list_query_stats397398Lists performance statistics for executed queries ordered by total time,399filtering by database name pattern if provided. This tool requires the400pg_stat_statements extension to be installed. The tool returns the database401name, query text, execution count, timing metrics (total, min, max, mean), rows402affected, and buffer cache I/O statistics (hits and reads).403404#### Parameters405406| Name | Type | Description | Required | Default |407| :------------ | :------ | :--------------------------- | :------- | :------ |408| database_name | string | Optional: The database name | No | `` |409: : : to list query stats for. : : :410| limit | integer | Optional: The maximum number | No | `50` |411: : : of results to return. : : :412: : : Defaults to 50. : : :413414--------------------------------------------------------------------------------415416### long_running_transactions417418Identifies and lists database transactions that exceed a specified time limit.419For each of the long running transactions, the output contains the process id,420database name, user name, application name, client address, state, connection421age, transaction age, query age, last activity age, wait event type, wait event,422and query string.423424#### Parameters425426| Name | Type | Description | Required | Default |427| :----------- | :------ | :------------------------- | :------- | :---------- |428| min_duration | string | Optional: Only show | No | `5 minutes` |429: : : transactions running at : : :430: : : least this long (e.g., '1 : : :431: : : minute', '15 minutes', '30 : : :432: : : seconds'). : : :433| limit | integer | Optional: The maximum | No | `20` |434: : : number of long-running : : :435: : : transactions to return. : : :436: : : Defaults to 20. : : :437438--------------------------------------------------------------------------------
tmolavi