planetscale-query-insights-and-tags

PlanetScale Insights와 SQLCommenter 스타일의 쿼리 태그를 사용하여 데이터베이스 부하를 속성화하고, 위험한 쿼리를 식별하며, 안전한 Traffic Control 또는 스키마를 준비합니다.

npx skills add https://github.com/planetscale/skills --skill planetscale-query-insights-and-tags

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.

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.

Insights API surface

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.

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.

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.

SQLCommenter tag schema

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.

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.

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.

Recommendation classes

Add tags

Recommend SQLCommenter instrumentation when query attribution is weak.

Improve tag normalization

Recommend replacing high-cardinality tags with bounded values.

Add Traffic Control warning budget

For Postgres only, recommend warn mode budgets for expensive but important routes, jobs, analytics, exports, or third-party integrations.

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.

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.