cockroachdb / cockroachdb/cockroach
sql/ttl: use MVCC block property filters to skip SST blocks during TTL scans
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Epic: CRDB-61798
## Summary
For tables using `ttl_expire_after`, CockroachDB creates an internal column
`crdb_internal_expiration` whose value is set to `current_timestamp() + ttl_duration`
when a row is created or updated. This means a row's MVCC write timestamp directly
determines its expiration: if a row was written at time T, it expires at T +
ttl_duration. This correlation can be exploited at the storage layer to skip entire
SST data blocks that cannot contain expired rows.
Pebble already collects min/max MVCC wall times per SST data block
(`mvccWallTimeIntervalCollector`) and supports block property filters. By installing
a filter that skips blocks where all MVCC timestamps are above `cutoff - ttl_duration`,
the TTL scan can avoid reading and decoding rows that are guaranteed not to be expired.
## Proposed approach
Add a `BlockOnlyMaxTimestamp` hint to `IterOptions` that installs a Pebble block
property filter (via `cockroachkvs.NewMVCCTimeIntervalFilter`) but does **not** set a
per-key `SkipPoint` callback. This gives block-level pruning without altering MVCC
point-in-time read semantics within non-skipped blocks.
Thread this hint through:
- `MVCCScanOptions` and the MVCC scan functions
- `ScanRequest` / `ReverseScanRequest` KV protos (as a performance hint field)
- `cmd_scan` / `cmd_reverse_scan` batch evaluation
- KV client helpers (`Txn.ScanWithBlockTimestampHint`)
- The TTL job processor, which computes the threshold as `cutoff - ttl_duration`
The optimization should only activate when all of:
1. The table uses `ttl_expire_after` (duration-based TTL with the internal column)
2. No custom `ttl_expiration_expression` is set
3. The `sql.ttl.mvcc_block_skipping.enabled` cluster setting is `true` (default)
This is safe because:
- Block skipping may cause the scan to miss rows or see older versions, but the
downstream DELETE re-checks the TTL predicate, catching any false positives.
- Missed rows will be caught by subsequent TTL scan cycles.
- The hint is purely a performance optimization; older nodes in a mixed-version
cluster will silently ignore it.
## Scope
First evaluate if the usage of `ttl_expire_after` (as compared to `ttl_expiration_expression`) is large enough to make this idea worth pursuing. This optimization will not benefit tables that are configured with `ttl_expiration_expression`.
Phase 1: Apply block skipping to `SpanToQueryBounds` boundary scans (forward/reverse
scans that find the first and last qualifying rows in each span).
Phase 2 (follow-up): Apply block skipping to the main SELECT scan, either by threading
the hint through the SQL internal executor path or by replacing the SQL SELECT with a
direct KV scan for this specific query pattern.
## Microbenchmark results
`BenchmarkMVCCScan_BlockOnlyMaxTimestamp` (50K keys, 128-byte values):
| Skippable data | Without hint | With hint | Speedup |
|----------------|-------------|-----------|---------|
| 0% | 932 MB/s | 958 MB/s | ~1x |
| 50% | 956 MB/s | 1,916 MB/s | **2x** |
| 90% | 939 MB/s | 9,389 MB/s | **10x** |
| 99% | 939 MB/s | 40,291 MB/s | **43x** |
Jira issue: none
Jira issue: CRDB-61803
Contributor guide
Research direction
Start by evaluating the stated `ttl_expire_after` versus `ttl_expiration_expression` usage, then trace `SpanToQueryBounds`, `MVCCScanOptions`, `ScanRequest`/`ReverseScanRequest`, and `Txn.ScanWithBlockTimestampHint`. Run `BenchmarkMVCCScan_BlockOnlyMaxTimestamp` to understand the performance target; the first phase is complete when boundary scans can use the hint under the listed TTL and cluster-setting conditions.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100