YantrikDB

A single-index fix for a defect that was burning ~55% of a 32-core machine while idle. Field-reported by a Hermes user, root-caused here, and independently measured by the reporting workspace on the affected hardware before this tag existed.

The defect

The materializer's drain poll scanned the entire oplog on every tick, on every worker, ten times a second. Cost grew with total history depth — and it was worst when the system was doing nothing, because with no pending ops there was no LIMIT to short-circuit the scan. At depth it also consumed the capacity the ingest path needed, so writes failed with Backpressure: ingest queue full.

The cause, precisely

idx_oplog_pending was added in MIGRATE_V23_TO_V24 and never added to SCHEMA_SQL. Migrations only run for databases that already carry a version, so every database created since v24 — every current install — never had a pending index at all. The drain query (WHERE applied = 0 ORDER BY hlc, op_id LIMIT n) therefore fell back to SCAN oplog USING INDEX idx_oplog_hlc.

Databases upgraded through v23→v24 did have the index and were fine. So the artifact that read as coverage was real, correct, deliberately added — and applied to a population of zero. Every cheap check passed: the line is in the schema file, git log shows it added on purpose, and on an upgraded dev box the query plan even proves it working. Only a plan check on a fresh install fails.

The fix

CREATE INDEX idx_oplog_pending_ordered ON oplog(hlc, op_id) WHERE applied = 0;

Declared in SCHEMA_SQL — the placement is the primary fix, so it exists on every database on every open — and applied to existing databases by MIGRATE_V37_TO_V38, which also drops the subsumed predecessor rather than leaving an artifact that reads like coverage. Partial on the sort keys, so it both filters and orders: the temp B-tree disappears and an idle poll touches ~zero rows regardless of history depth.

Measured on the affected machine (32 logical CPUs, 6 engines, median of 3×6s samples after quiesce)

| records | before (v0.10.0) | after | |
|---|---|---|---|
| 500 | 1.25% of machine | 0.22% | 5.7× |
| 2,000 | 4.65% | 0.24% | 19× |
| 6,000 | 34.41% | 0.14% | 246× |

The curve is flat — 0.22 → 0.24 → 0.14 across a 12× increase in history, drifting down within noise. The superlinear shape is gone, not merely scaled.

• Write starvation eliminated: Backpressure events while seeding 6,000 records went 7 → 0.
• Steady-state floor: one engine at 10,000 records, sampled only after pending_ops == 0, sits at 1.3% of one core.
• Consumer acceptance gates (thresholds chosen by the reporting consumer, not by us): scaling invariant cpu(10k) ≤ 1.25 × cpu(1k) measured 0.64 (pre-fix: 27.5); absolute ceiling < 5% of one core measured 1.3%. Both pass.

Schema

SCHEMA_VERSION 37 → 38, additive. Version stamping remains forward-only, so an older binary still opens a v38 database without refusing or rewinding. If you assert an exact schema version anywhere, relax it to >= — we had exactly one such equality pin in our own test suite and it broke on this bump with every surface intact.

Regression protection

The drain SQL now lives in a single const executed by both the drain and its test, so a test cannot pin a private copy that passes while the real query drifts. The test asserts the query plan — deterministic, where a latency budget would be flaky on CI and would only fail once a user's corpus grew large enough to hurt — and separately asserts that a fresh database carries the index, which is the check that would have caught the original defect. It is proven to fail against the pre-fix schema.

Still open (tracked in #113, deliberately not bundled here)

Idle backoff instead of the fixed 100 ms tick, the 16-workers-per-engine multiplier, and exposed worker/poll tuning knobs. Post-fix these dropped from shipping-blockers to hygiene: six engines now idle at ~0.14% of a machine.

Reported by a Hermes user via yantrikdb-hermes-plugin, whose measurements made the defect findable, whose fresh-database probe corrected our first mechanism, and whose false-alarm catch produced the acceptance protocol (block on pending_ops == 0, never a timed sleep — a fixed interval silently measures drain and calls it idle).