cockroachdb / cockroachdb/cockroach

systemschema, sqlstats: store fingerprint metadata in separate system table

Open
#139,763 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement T-observability
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**
`system.statement_statistics` stores historical statement statistics by fingerprintID aggregated on an hourly basis. For each row we store the fingerprint id's associated metadata in JSON (see below) - this means we currently store duplicated (statementFingerprintID, metadata) information.

**Describe the solution you'd like**
To reduce storage and improve lookups for fingerprint ids -> queries, we should normalize `system.statement_statistics` and create a new table storing `(fingerprintId, ...metadataFields)` for the entries in the historical table.

We should do the same for the transactions table

**Describe alternatives you've considered**

**Additional context**
This was a consideration in the past but was tabled due to the number of system tables being capped. That is not currently an issue.

Example entry from `system.statement_statistics` (omitting other cols):
```
fingerprint_id | metadata
---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\x1d4f8de858fd2ecc | {"db": "defaultdb", "distsql": false, "fullScan": false, "implicitTxn": false, "query": "INSERT INTO system.job_info(job_id, info_key, written, value) VALUES (_, _, now(), _)", "querySummary": "INSERT INTO system.job_info(job_id, info_ke...)", "stmtType": "TypeDML", "vec": true}
```

Jira issue: CRDB-46803

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.