sql: TTL v1 meta issue

[otan]

This is a meta issue for the v1 TTL meta issue.

⛔ = blocked ✔️ = in review ♻️ = in progress

Done

• [x] RFC #75189
• [x] ALTER TABLE ... SET syntax #75262
• [x] ALTER TABLE ... RESET syntax #75429
• [x] Persist metadata #75602
• [x] handle each options
• [x] Placeholder for scheduled job
• [x] (optional) look into protecting scheduled job from being cancelled
• [x] Implement deletion job main logic
• [x] CREATE TABLE ... WITH
• [x] cron management for TTL
• [x] (stretch?) add jitter to scheduled jobs framework - (cancelled) https://cockroachlabs.slack.com/archives/C02DSDS9TM1/p1644461478718559
• [x] DROP TABLE/DATABASE/SCHEMA should also drop the scheduled job. (#76216)
• [x] ALTER TABLE ... SET setting up the automatic column and scheduled job for the first time (#76216)
• [x] ALTER TABLE ... SET modifying TTL expire_after's default expressions (#76216)
• [x] ALTER TABLE ... RESET dropping the TTL automatic column and scheduled job (#76216)
• [x] Break up the TTL job by ranges & add concurrency. (#76487)
• [x] Cancel deletion job if schema change relating to a PK change or TTL parameter change. (#76487)
• [x] Block FKs into a TTL table (#76409)
• [x] implement desired metrics on the TTL table (#76701)
• [x] Implement rate limiting for deletes (#76615)
• [x] backup and restore should re-instate the TTL schedule (in review: #76725)
• [x] range scans are currently unpaginated - we need to paginate them. ( in review: #76726)
• [x] implement feature flag and pause for TTL stops (in review: #76835)
• [x] cleanup: create issues for all post-v1 TODOs and tag in codebase, add unimplemented issue ttl_automatic_expiration, CODEOWNERs, only allow ascending PKs, commit with a release note for TTL (#76918)
• [x] ensure pg_class.reloptions matches SHOW CREATE TABLE (#76918)

out of scoped

• ttl_expiration_expression (maybe a stretch)

By February 25

• [x] ✔️ Plug in admission priority for DELETE. (#76837)
• [x] ✔️ implement a scan of deleted vs active rows to report as metrics: In addition to the current metrics, we need to write a loop that counts all the expired rows and existing rows in the table. For now, this will be an extra goroutine on the TTL job. This needs to be gated by a table setting and cluster setting. (in review: #76837)

Stability items

• [x] ✔️ Add telemetry (https://github.com/cockroachdb/cockroach/pull/77108)
• [ ] (happy for someone to take this) add validation builtins (see below); incorporate into debug doctor
• Write some builtins which scan the descriptors and validate all tables with TTL have an associated scheduled job & scans row level TTL jobs and ensure they map to a valid table.
• Write some builtins which can create the scheduled jobs you might expect if they don't exist.
• [ ] Re-do performance testing of TTL

Product Questions

• Figure out how TTL priority configuration should align with SET ADMISSION PRIORITY (i.e. using numbers versus enums)
• figure out advice for "child" metrics (since we use aggmetric for metrics, child labels are not applied by default... see slack)
• i'd advocate for calling row-level TTL "preview" / "experimental" for this release. should we?

Epic CRDB-10488

Jira issue: CRDB-12666

[vy-ton]

Figure out how TTL priority configuration should align with SET ADMISSION PRIORITY (i.e. using numbers versus enums)

If we think we might need the flexibility/tuning with QoS for TTL, I am ok deferring this alignment until later.

i'd advocate for calling row-level TTL "preview" / "experimental" for this release. should we?

In docs, we will call it beta support but that shouldn't apply to any exposed settings or code.

[vy-ton]

Should we file issues for out of scope items like ttl_expiration_expression?

[otan]

See https://github.com/cockroachdb/cockroach/issues?q=is%3Aissue+is%3Aopen+label%3AA-row-level-ttl / https://github.com/cockroachdb/cockroach/issues/76916.