cockroachdb / cockroachdb/cockroach
systemschema, sqlstats: store fingerprint metadata in separate system table
- 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
Assessment
This issue has not been assessed yet.