cockroachdb / cockroachdb/cockroach

sql/ttl: use MVCC block property filters to skip SST blocks during TTL scans

Open
#166,213 1 comment 0 reactions 0 assignees View on GitHub
A-row-level-ttl C-enhancement T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.