cockroachdb / cockroachdb/cockroach

sql: use INSPECT index consistency check for backfill validation diagnostics

Open
#175,590 0 comments 0 reactions 1 assignee Claimed by @himanshu-shrivastava-crl View on GitHub
C-enhancement T-sql-foundations
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.**

When a non-unique forward index fails validation during backfill with a row-count mismatch, the assertion error is opaque. PR cockroachlabs/cockroach#4357 adds a physical KV-pair count to help attribute the mismatch, but it does not identify the specific offending rows. For large indexes, an ad-hoc key diff (e.g. EXCEPT ALL over the full index and primary spans) does not scale — it can consume excessive memory and fall over on big tables.

**Describe the solution you'd like**

Use the INSPECT index consistency check machinery to perform the validation comparison during backfill. INSPECT already compares two indexes at the span level, which bounds resource consumption on large tables. The main challenge is that backfill validation operates on an in-progress, non-public index via a synthetic descriptor on a historical transaction, while INSPECT currently resolves its own persisted, public descriptor and rejects non-public indexes. INSPECT would need to accept and thread an injected synthetic descriptor through its execution path.

Alternatively, the entire validation step could be done through INSPECT rather than the current count-based approach.

**Prior art: IMPORT already runs INSPECT this way.** IMPORT performs a post-import validation via INSPECT today, controlled by `bulkio.import.row_count_validation.mode` (`off` / `async` / `sync`). It builds the check list with `inspect.ChecksForTable`, starts the job with `inspect.TriggerJob(..., asOf)`, and in `sync` mode blocks on `JobRegistry.WaitForJobs`, folds the result into the IMPORT job's error, and points the operator at `SHOW INSPECT ERRORS FOR JOB `. The invocation and spawn-and-wait orchestration this issue would need therefore already exists and has a production caller — the remaining work is descriptor visibility, not job plumbing.

IMPORT sidesteps the descriptor problem rather than solving it: it publishes the table first, captures the commit timestamp of that transaction, and runs INSPECT `AS OF` that timestamp against an ordinary public descriptor. That reordering is not available here — publishing the index is precisely what validation gates, and a corrupt public index silently returns wrong query results. The AOST half does transfer: backfill validation already runs at a fixed historical timestamp with a protected timestamp covering it, which is exactly the input `TriggerJob` takes.

**Smaller pieces that could land independently**

Two parts of this do not require the full rework:

1. **The synthetic descriptor does not need to be serialized.** It is a deterministic transform of the persisted descriptor — the same one backfill validation already applies before its count queries. A boolean on `InspectDetails_Check` meaning "treat the first mutation as public" is sufficient payload; no descriptor has to travel in the job record.

2. **The hash precheck is useful on its own.** INSPECT's index consistency check already has a precheck (`sql.inspect.index_consistency_hash.enabled`) that compares `count(*)` and an `xor_agg`/`fnv64` digest of the compared columns across the two indexes. It is two aggregate scans — no join, no chunking — and is indifferent to index size and column-family count. Run on a validation failure it separates cases the row counts alone cannot: matching digests with differing counts indicate duplicates or a count-path problem, while differing digests indicate genuine data divergence. (`xor_agg` cancels duplicates pairwise, which is what makes the first signature meaningful.) This is a substantially smaller change than reusing the full check, and it covers the gap left by dropping the per-key diff from cockroachlabs/cockroach#4357.

**Describe alternatives you've considered**

- Ad-hoc EXCEPT ALL queries over the primary and index spans: does not scale for large tables (materializes full spans, memory-heavy).
- Chunking the diff using spanutils (span-to-bounds): the secondary index is not PK-ordered, so a PK-range predicate cannot constrain it; each chunk rescans the whole secondary, making it CPU-quadratic. Avoiding that requires anti-join and NULL/partial-index handling comparable to INSPECT itself.
- Size-gating the diff: bounds cost but skips the per-key diff for large indexes, limiting diagnostic value (considered in cockroachlabs/cockroach#4357 and ultimately dropped).

**Additional context**

Discussion context: cockroachlabs/cockroach#4357 (review thread on line 2403 of `pkg/sql/backfill.go`).

Jira issue: CRDB-68478

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.