cockroachdb / cockroachdb/cockroach
sql: cut SQL stats views to always-on CPU and remove vestigial base statistics columns
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Summary**
Follow-up to the 26.4 work that added always-on SQL CPU aggregate columns (`sql_cpu_time_nanos_sum{,_sq}`) to `system.statement_statistics` / `system.transaction_statistics` as storage-only, additive columns (gated by `V26_4_AddSQLCPUTimeStatisticsColumns`), while leaving the SQL stats views on the deprecated sampled `cpu_sql_nanos*` columns. This issue (a) completes the SQL CPU cut-over and (b) removes a set of vestigial base per-row computed columns that duplicate the `statistics` JSONB and are no longer read by any query path.
Targeted at **27.1**: a 26.4→27.1 upgrade requires 26.4 finalized first, so the always-on physical columns are guaranteed present on every node, making the view cut-over and the column drops mixed-version safe. (Why this couldn't ship in 26.4: view definitions are binary-versioned and name resolution fails against a not-yet-migrated base column during the upgrade window; broader fix tracked in #172725.)
---
**Workstream 1 — SQL CPU cut-over**
Bring SQL CPU in line with the KV CPU surface (which exposes only `kv_cpu_time_nanos_sum{,_sq}`, no base mean):
1. In `information_schema.crdb_statement_statistics` / `crdb_transaction_statistics`, replace `cpu_sql_nanos`, `cpu_sql_nanos_sum`, `cpu_sql_nanos_sum_sq` with `sql_cpu_time_nanos_sum`, `sql_cpu_time_nanos_sum_sq`, sourced from the always-on physical columns (covering-index-backed). No base mean column.
2. Drop the sampled physical columns `cpu_sql_nanos_sum`, `cpu_sql_nanos_sum_sq` and rebuild the covering indexes to no longer store them.
**Workstream 2 — remove vestigial base columns**
3. Drop these `STORED` computed columns from both system tables. None are in a covering index, so **no index rebuild**: `service_latency`, `cpu_sql_nanos`, `contention_time`, `total_estimated_execution_time`, `p99_latency`.
4. Remove these columns from the views that expose them (they are unread — all consumers read the equivalent values from the `statistics` JSONB):
- `information_schema.crdb_statement_statistics` / `crdb_transaction_statistics`
- `crdb_internal.statement_statistics_persisted` / `crdb_internal.transaction_statistics_persisted`
5. Rewrite the debug-zip query `system.statement_statistics_limit_5000` — it does `ORDER BY cpu_sql_nanos desc` to find top-CPU transactions; point it at the always-on `sql_cpu_time_nanos_sum` (covering-index-backed, and a better metric than the sampled base).
**Both workstreams**
6. New 27.1 version gate + post-finalization migration; follow the system-table-change checklist (descriptor defs, golden-file regen, migration test asserting the dropped columns are gone and the covering index no longer stores the sampled CPU sums).
**Consumer analysis (verified against master)**
No read path selects the dropped base columns — `combined_statement_stats.go`, `server/statement_details.go`, and the cluster-ui APIs (`contentionApi`, `schemaInsightsApi`, `indexDetailsApi`) all read these metrics from the `statistics` JSONB or select only identity columns. The base columns are leftovers from dedicated sort indexes removed in the v26_2 migration. `indexDetailsApi.ts` uses `SELECT *` + a JSONB-based converter — verify `convertStatementRawFormatToAggregatedStatistics` doesn't map the removed columns positionally.
The SQL Activity flush (`sql_activity_update_job.go`) and `crdb_internal`'s `_avg`/`_var`/insights columns are unaffected (JSONB / in-memory protos).
**Compatibility**
Removing columns from `information_schema.crdb_statement_statistics` / `crdb_transaction_statistics` is a **breaking change to a supported surface**; needs a release note and a deprecation call-out even though no internal consumer breaks. Note `p99_latency` has no aggregate replacement and will only be reachable via the `statistics` JSONB after this change (`total_estimated_execution_time` is effectively replaced by the retained `svc_lat_sum`).
Related: #172725
Jira issue: CRDB-66058
Contributor guide
Research direction
Start with the system-table descriptor definitions, the 26.4 migration, and the information_schema and crdb_internal statistics views; inspect combined_statement_stats.go, server/statement_details.go, and convertStatementRawFormatToAggregatedStatistics for consumers. Follow the system-table-change checklist, then run the migration tests and regenerate golden files. Done means the 27.1 gate and migration remove the listed columns, update the covering index and debug-zip query, and include the required release note.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql, typescript
- Domain
- databases
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100