planetscale-query-insights-and-tags

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Query Insights and tags

查询洞察与标签

Purpose

目的

Use PlanetScale Insights to understand query behavior, then recommend SQLCommenter-compatible tags that make future diagnosis and Traffic Control possible. Do not change database settings or repository code without approval.

借助PlanetScale Insights了解查询行为，随后推荐兼容SQLCommenter的标签，以便未来进行问题诊断和Traffic Control操作。未经批准，请勿修改数据库设置或仓库代码。

What to inspect

检查内容

Query behavior

查询行为

For the selected database and branch, inspect:

Top queries by total time.
Top queries by time per execution.
Top queries by rows read.
Top queries by execution count.
For Postgres, top queries by CPU usage (
```
sort=cpuTime
```
or
```
sort=percentCpuTime
```
on the Insights API).
Queries with errors.
Notable queries and active anomalies.
Query patterns affected by recent deploys.
Query patterns attached to schema recommendations.
For sharded Vitess databases, vindex usage for each query pattern: the percentage of traffic using relevant vindexes and the vindex-usage trend over time. The API exposes per-pattern
```
index_usages
```
and
```
routing_index_usages
```
; get the trend from the dashboard Vindexes tab or by comparing API windows. Treat missing or declining relevant-vindex usage as an indexing or routing investigation input, not as proof that a new index is required.

针对选定的数据库和分支，检查以下内容：

总耗时最高的查询。
单次执行耗时最高的查询。
读取行数最多的查询。
执行次数最多的查询。
对于Postgres数据库，CPU使用率最高的查询（在Insights API中使用
```
sort=cpuTime
```
或
```
sort=percentCpuTime
```
）。
存在错误的查询。
值得关注的查询和活跃异常。
受最近部署影响的查询模式。
与架构建议相关的查询模式。
对于分片Vitess数据库，每个查询模式的vindex使用情况：使用相关vindex的流量占比，以及vindex使用情况随时间的趋势。API会暴露每个模式的
```
index_usages
```
和
```
routing_index_usages
```
；可从控制台的Vindexes标签页或对比不同API时间窗口获取趋势数据。将相关vindex缺失或使用占比下降的情况作为索引或路由调查的输入依据，但不能直接作为需要新增索引的证明。

Insights API surface

Insights API接口

Query Insights is public API: read-only GET endpoints under

organizations/{org}/databases/{db}/branches/{branch}

, authorized by a service token or OAuth token with

read_databases

read_database

```
/insights
```
— aggregated statistics per query pattern over the requested window. Set the window with
```
from
```
/
```
to
```
(ISO 8601) or
```
period
```
(for example
```
1h
```
,
```
24h
```
); search SQL patterns with
```
q
```
; sort server-side with
```
sort
```
and
```
dir
```
— sort keys include
```
count
```
,
```
errorCount
```
,
```
rowsRead
```
,
```
totalTime
```
,
```
cpuTime
```
,
```
ioTime
```
,
```
percentTime
```
,
```
percentCpuTime
```
,
```
p50Latency
```
,
```
p99Latency
```
,
```
maxLatency
```
,
```
egressBytes
```
, and the
```
trafficControlWarnings
```
/
```
trafficControlThrottled
```
family. Filter with
```
tablet_type
```
(
```
primary
```
,
```
replica
```
,
```
rdonly
```
) and
```
type
```
(
```
SELECT
```
,
```
INSERT
```
,
```
UPDATE
```
,
```
DELETE
```
); trim responses with
```
fields
```
; paginate with
```
page
```
/
```
per_page
```
.
```
/insights/{fingerprint}
```
— individual collected executions for a pattern (timestamps, duration, rows, username, client address, error message). Available regardless of raw query collection; raw collection adds literal parameter values to these records.
```
/insights/{fingerprint}/summary
```
returns the single-pattern aggregate;
```
/insights/queries/{id}
```
fetches one execution.
```
/insights/errors
```
— error fingerprints with counts and messages (
```
q
```
searches the error message; sort by
```
count
```
,
```
lastRun
```
,
```
totalTime
```
, or
```
timePerQuery
```
).
```
/insights/errors/{fingerprint}
```
lists the failing executions behind one error fingerprint.
```
/insights/anomalies
```
and
```
/insights/anomalies/{id}
```
— anomaly windows with per-query correlation coefficients identifying which patterns moved with the anomaly.
```
/insights/tags
```
— tag keys with observed values (
```
values_limit
```
,
```
literal_values_only
```
, and
```
fingerprint
```
/
```
keyspace
```
filters);
```
/insights/tags/{tag}
```
for a single key.
```
/insights/tags/summaries
```
groups the full statistics schema by one or more tag keys via the
```
tags
```
parameter — use it to attribute load to routes, jobs, or features without client-side aggregation.
```
/insights/{fingerprint}/traffic/budgets
```
— the Traffic Control budgets and rules that affect a fingerprint (Postgres).

Aggregates cover the requested window. Duration fields use names like

sum_total_duration_millis

, with explicit share-of-window percent fields (

sum_total_duration_percent

); both totals and percentages are reliable for the window requested.

The response schema is shared across engines, but some fields are engine-specific: CPU/IO durations and block-cache statistics (

sum_cpu_duration_millis

blocks_read

block_cache_hit_ratio

, …) are populated for Postgres; shard queries, keyspaces,

tablet_type

, and routing-index (vindex) usage are populated for Vitess.

Query Insights是公开API：在

organizations/{org}/databases/{db}/branches/{branch}

路径下的只读GET接口，通过拥有

read_databases

read_database

权限的服务令牌或OAuth令牌授权访问。

```
/insights
```
— 在请求的时间窗口内，按查询模式聚合的统计数据。通过
```
from
```
/
```
to
```
（ISO 8601格式）或
```
period
```
（例如
```
1h
```
、
```
24h
```
）设置时间窗口；使用
```
q
```
搜索SQL模式；通过
```
sort
```
和
```
dir
```
进行服务端排序——排序键包括
```
count
```
、
```
errorCount
```
、
```
rowsRead
```
、
```
totalTime
```
、
```
cpuTime
```
、
```
ioTime
```
、
```
percentTime
```
、
```
percentCpuTime
```
、
```
p50Latency
```
、
```
p99Latency
```
、
```
maxLatency
```
、
```
egressBytes
```
，以及
```
trafficControlWarnings
```
/
```
trafficControlThrottled
```
系列。可通过
```
tablet_type
```
（
```
primary
```
、
```
replica
```
、
```
rdonly
```
）和
```
type
```
（
```
SELECT
```
、
```
INSERT
```
、
```
UPDATE
```
、
```
DELETE
```
）进行过滤；使用
```
fields
```
精简响应内容；通过
```
page
```
/
```
per_page
```
实现分页。
```
/insights/{fingerprint}
```
— 某一模式下收集到的单次执行记录（时间戳、耗时、行数、用户名、客户端地址、错误信息）。无论是否开启原始查询收集，该接口均可用；开启原始收集后，这些记录会包含字面参数值。
```
/insights/{fingerprint}/summary
```
返回单个模式的聚合统计数据；
```
/insights/queries/{id}
```
获取单次执行记录。
```
/insights/errors
```
— 错误指纹及其计数和信息（
```
q
```
用于搜索错误信息；可按
```
count
```
、
```
lastRun
```
、
```
totalTime
```
或
```
timePerQuery
```
排序）。
```
/insights/errors/{fingerprint}
```
列出某一错误指纹对应的失败执行记录。
```
/insights/anomalies
```
和
```
/insights/anomalies/{id}
```
— 异常时间窗口，以及每个查询的相关系数，用于识别哪些模式随异常变化。
```
/insights/tags
```
— 带有已观测值的标签键（支持
```
values_limit
```
、
```
literal_values_only
```
以及
```
fingerprint
```
/
```
keyspace
```
过滤）；
```
/insights/tags/{tag}
```
用于单个标签键。
```
/insights/tags/summaries
```
通过
```
tags
```
参数按一个或多个标签键对完整统计架构进行分组——无需客户端聚合即可将负载归因到路由、任务或功能。
```
/insights/{fingerprint}/traffic/budgets
```
— 影响某一指纹的Traffic Control预算和规则（仅Postgres）。

聚合统计覆盖请求的时间窗口。耗时字段使用类似

sum_total_duration_millis

的命名，同时包含明确的窗口占比字段（

sum_total_duration_percent

）；请求窗口内的总计值和百分比均可靠。

响应架构在不同引擎间通用，但部分字段为引擎专属：CPU/IO耗时和块缓存统计数据（

sum_cpu_duration_millis

、

blocks_read

、

block_cache_hit_ratio

等）仅在Postgres中填充；分片查询、keyspace、

tablet_type

和路由索引（vindex）使用情况仅在Vitess中填充。

Tag coverage

标签覆盖情况

For each expensive or anomalous query, determine:

Is it tagged?
Which service produced it?
Which route, job, controller, or action produced it?
Which deployment SHA produced it?
Is the tag cardinality safe?
Are tags consistent across frameworks and languages?
Use the tags API to answer these questions:
```
/insights/tags
```
shows which keys and values are present, and
```
/insights/tags/summaries?tags=...
```
attributes load per tag value. In the Vitess dashboard, filter the query table with
```
tag:key:value
```
and drill into query details to see tags on individual executions. Built-in query metadata and SQLCommenter tags are both valid attribution sources.

针对每个高耗时或异常查询，确定以下内容：

是否已添加标签？
由哪个服务生成？
由哪个路由、任务、控制器或操作生成？
由哪个部署SHA生成？
标签的基数是否安全？
不同框架和语言间的标签是否一致？
使用标签API回答这些问题：
```
/insights/tags
```
显示存在哪些键和值，
```
/insights/tags/summaries?tags=...
```
按标签值归因负载。在Vitess控制台中，使用
```
tag:key:value
```
过滤查询表格，并深入查询详情查看单次执行的标签。内置查询元数据和SQLCommenter标签均为有效的归因来源。

Raw query collection

原始查询收集

Check whether raw query / complete query collection is enabled. On Postgres the effective state is the

pginsights.raw_queries

cluster parameter (per branch, dashboard Extensions tab, default

false

); the database API object's

insights_raw_queries

field is a separate surface. When the two differ, report the cluster parameter as the effective state and do not describe the difference as an inconsistency. On Vitess there is no cluster parameter; the database API's

insights_raw_queries

field is the effective state.

Report it as a capability state, not a risk posture. Raw query collection records literal parameter values per execution, which pattern-level Insights data does not provide. It is the mechanism for isolating which specific invocation of a pattern is pathological. Execution-level records are retrievable from

/insights/{fingerprint}

with or without raw collection; raw collection adds the literal parameter values to those records.

When it is disabled, the finding is a capability gap: identify the query patterns in this assessment where pattern-level data is insufficient (unexplained latency variance within a fingerprint, tenant- or parameter-dependent behavior) and state that raw collection would resolve them. State the operational property once, as fact: literal values become visible to the observability pipeline. Where the customer's data-handling requirements constrain this, scoped enablement (incident windows, defined retention) and leaving collection disabled are both valid outcomes — record the rationale rather than a default judgment in either direction.

Tags and raw collection are complementary instruments: tags attribute a pattern to a code path; raw collection identifies the specific invocation. Assessments should evaluate both.

检查是否开启了原始查询/完整查询收集。在Postgres中，有效状态由

pginsights.raw_queries

集群参数决定（按分支设置，可在控制台Extensions标签页查看，默认值为

false

）；数据库API对象的

insights_raw_queries

字段是独立的配置入口。当两者不一致时，以集群参数作为有效状态，且不要将这种差异描述为不一致。在Vitess中没有集群参数，数据库API的

insights_raw_queries

字段即为有效状态。

将其作为能力状态而非风险态势进行报告。原始查询收集会记录每次执行的字面参数值，而模式级别的Insights数据不提供这些信息。它是用于隔离某一模式下具体哪个调用出现问题的机制。无论是否开启原始收集，均可从

/insights/{fingerprint}

获取执行级记录；开启原始收集后，这些记录会添加字面参数值。

当原始查询收集处于禁用状态时，发现的问题属于能力缺口：识别本次评估中模式级数据不足以分析的查询模式（同一指纹内延迟差异无法解释、依赖租户或参数的行为），并说明开启原始收集可解决这些问题。需客观陈述一次操作特性：字面参数值会进入可观测 pipeline。如果客户的数据处理要求对此有约束，那么仅在事件窗口内开启、定义保留期限，或者保持禁用状态都是合理的结果——需记录理由，而非默认倾向某一方向。

标签和原始收集是互补工具：标签将模式归因到代码路径；原始收集识别具体调用。评估需同时考量两者。

SQLCommenter tag schema

SQLCommenter标签架构

Recommend this baseline tag set:

```
application
```
: stable app name.
```
service
```
: service or process name.
```
environment
```
: production, staging, development.
```
route
```
: normalized route template, for example
```
/accounts/:id/orders
```
, not
```
/accounts/123/orders
```
.
```
controller
```
: framework controller name where applicable.
```
action
```
: framework action name where applicable.
```
job
```
: background job class or worker name.
```
queue
```
: background queue.
```
feature
```
: bounded feature name for traffic classes like export, report, search, billing, checkout.
```
release_sha
```
: short git SHA or deploy identifier.
```
source
```
: app, worker, script, agent, mcp, bi, integration.
```
tenant_tier
```
: free, pro, enterprise, internal, only if bounded.

Do not recommend these tags by default:

```
user_id
```
```
request_id
```
```
tenant_id
```
```
email
```
```
session_id
```
raw URL
unbounded GraphQL operation text
access token
secret

If the customer needs tenant-level isolation, recommend a bounded abstraction first, such as tenant tier, cell, shard, or customer class. Tenant ID is only acceptable with explicit approval after cardinality and privacy review.

推荐以下基准标签集：

```
application
```
：稳定的应用名称。
```
service
```
：服务或进程名称。
```
environment
```
：生产、预发布、开发。
```
route
```
：标准化的路由模板，例如
```
/accounts/:id/orders
```
，而非
```
/accounts/123/orders
```
。
```
controller
```
：适用时的框架控制器名称。
```
action
```
：适用时的框架操作名称。
```
job
```
：后台任务类或工作器名称。
```
queue
```
：后台队列。
```
feature
```
：流量类别的限定功能名称，例如导出、报表、搜索、计费、结账。
```
release_sha
```
：简短的git SHA或部署标识符。
```
source
```
：应用、工作器、脚本、agent、mcp、bi、集成。
```
tenant_tier
```
：免费、专业、企业、内部，仅在有限范围内使用。

默认不推荐以下标签：

```
user_id
```
```
request_id
```
```
tenant_id
```
```
email
```
```
session_id
```
原始URL
无限制的GraphQL操作文本
访问令牌
密钥

如果客户需要租户级隔离，建议优先使用有限抽象，例如租户层级、单元、分片或客户类别。仅在经过基数和隐私审查并获得明确批准后，才可使用租户ID标签。

Cardinality rules

基数规则

Flag a tag as unsafe when:

Values are unbounded.
Values include IDs, UUIDs, emails, slugs, or raw paths.
The same query pattern emits many unique tag combinations.
The tag would make Insights or Traffic Control aggregation noisy.

Recommend normalizing at the application boundary.

当出现以下情况时，标记标签为不安全：

值无限制。
值包含ID、UUID、邮箱、slug或原始路径。
同一查询模式生成大量唯一标签组合。
标签会导致Insights或Traffic Control聚合数据产生噪声。

建议在应用边界进行标准化处理。

Analysis output

分析输出

For each top query pattern, produce:

Fingerprint or normalized query.
Current metrics.
Current tags.
Missing tags.
Likely source in application code.
Whether it is a schema recommendation candidate.
Whether it is a Traffic Control candidate.
Whether it is an application optimization candidate.

针对每个顶级查询模式，生成以下内容：

指纹或标准化查询语句。
当前指标。
当前标签。
缺失的标签。
在应用代码中的可能来源。
是否适合作为架构建议候选。
是否适合作为Traffic Control候选。
是否适合作为应用优化候选。

Recommendation classes

建议类别

Add tags

添加标签

Recommend SQLCommenter instrumentation when query attribution is weak.

当查询归因能力较弱时，建议添加SQLCommenter埋点。

Improve tag normalization

优化标签标准化

Recommend replacing high-cardinality tags with bounded values.

建议用有限值替换高基数标签。

Add Traffic Control warning budget

添加Traffic Control警告预算

For Postgres only, recommend

warn

mode budgets for expensive but important routes, jobs, analytics, exports, or third-party integrations.

仅针对Postgres，建议为高耗时但重要的路由、任务、分析、导出或第三方集成设置

warn

模式预算。

Add schema recommendation workflow

添加架构建议流程

For Vitess, recommend turning open schema recommendations into branch/deploy-request work. For Postgres, recommend turning them into reviewed migrations against a non-production branch.

针对Vitess，建议将开放的架构建议转化为分支/部署请求工作。针对Postgres，建议将其转化为针对非生产分支的已审核迁移。

Fix code path

修复代码路径

Recommend a repository PR when the expensive query is caused by N+1, missing pagination, accidental eager load, unbounded export, broad search, or polling.

当高耗时查询由N+1查询、缺失分页、意外预加载、无限制导出、宽泛搜索或轮询导致时，建议提交仓库PR进行修复。

Safety rules

安全规则

Do not:

Enable raw query collection.
Add tags to code.
Change Traffic Control budgets.
Apply schema recommendations.
Run production EXPLAIN ANALYZE on expensive queries.

Without explicit approval.

未经明确批准，请勿：

开启原始查询收集。
向代码中添加标签。
修改Traffic Control预算。
应用架构建议。
在生产环境中对高耗时查询运行EXPLAIN ANALYZE。

Output

输出内容

Return:

Query risk table.
Tag coverage table.
Bad/high-cardinality tag table.
Recommended tag schema for this application.
Candidate Traffic Control slices.
Candidate schema and code changes.
Proposed changes requiring approval.

End with:

“No Insights, tag, repository, or Traffic Control changes have been applied.”

查询风险表。
标签覆盖情况表。
不良/高基数标签表。
针对该应用的推荐标签架构。
候选Traffic Control切片。
候选架构和代码变更。
需要批准的拟议变更。

结尾需包含：

“未应用任何Insights、标签、仓库或Traffic Control变更。”