idx_orch_events_correlation_id has no query user: 44 MB and about 9% of insert cost on a real 19.8 GB database#20534

opened8d agobyBbompus

What happened

Every event insert on a large install maintains idx_orch_events_correlation_id, an index that no query reads. On a real 19.8 GB database (2.27M events) it holds 43.7 MB, and on a schema-faithful copy removing it lowers per-event insert cost from 0.093 ms to 0.085 ms (about 9%).

This issue originally also named idx_orchestration_events_agent_stream_sequence. That part is withdrawn: since #21050 the legacy V1 importer forces that index (LegacyV1ThreadImporter.ts:715, with a query-plan test), so it must stay. Thanks to @derektrimm for the measurement on an upgraded database.

Diagnosis

Inventory from the live database (dbstat, read-only). orchestration_events is 7,431.7 MB. Its secondary indexes are stream_sequence 195.2 MB, stream_version 192.1 MB, agent_stream_sequence (partial) 177.4 MB, correlation_id 43.7 MB, command_id 43.7 MB, application_sequence 39.0 MB, application_high_water (partial) 33.5 MB and v2_created_threads 0.3 MB.

idx_orch_events_correlation_id: no WHERE, join or ORDER BY in apps/server/src references correlation_id; the only mentions are the INSERT values, SELECT lists and the index creation in 001_OrchestrationEvents.ts (grep re-run against main 2026-10-19). The column is non-null on 194,258 of 2,273,690 live rows (8.5%), so the index is maintained on every insert for no reader.

Write cost. I built a copy from the live schema and index definitions, with synthetic rows at the live average payload of 3.3 KB. The bench used WAL, synchronous=NORMAL, one transaction per event, 36k rows and 3 repetitions of 2,000. All indexes cost 0.093 ms per event (range 0.093-0.096). Without the correlation index it cost 0.085 ms (range 0.084-0.09). The ranges do not overlap. At 2.27M live rows the share should be larger, because B-trees are deeper and cache pressure is higher; treat 9% as a lower bound for the insert path, not an end-to-end figure.

Checked and kept: command_id (command lookups force it, and a code comment records multi-second scans without it), application_sequence and application_high_water (live EXPLAIN plans use them; code forces high_water with INDEXED BY in three places), stream_version (unique constraint), agent_stream_sequence (forced by the V1 importer and by latestAgentSequence), and the small turn_items partials.

Fix shape: a migration dropping idx_orch_events_correlation_id. Dropping an index is metadata-only in SQLite, so existing databases keep the freed pages on the freelist until vacuum; the ongoing win is insert cost and slower growth. Migration 56_RemoveRedundantProjectionIndexes already removed a similar set on the projection side.

Steps to reproduce

On any large statev2.sqlite, list indexes on orchestration_events with dbstat sizes. Grep apps/server/src for a correlation_id filter (none). Insert timing comes from the schema-copy harness described above.

Version

upstream/main 6cecd22789 (live database from the current nightly install; schema read 2026-10-16)

Environment

Linux, Node v26.12.0, SQLite via node:sqlite (inspector library 3.46.1)

Evidence

shell

1	{"arm":"all","perEventTxnMedianMs":0.093,"perEventRangeMs":[0.093,0.096]}
2	{"arm":"no-corr","perEventTxnMedianMs":0.085,"perEventRangeMs":[0.084,0.09]}
3	Live dbstat: idx_orch_events_correlation_id 43.7 MB, orchestration_events 7,431.7 MB / 2,273,690 rows.

Related issues

#21050 (V1 importer, which keeps agent_stream_sequence). Searched 2026-10-16 for others on these indexes; none.

Fix applied or workaround

None applied. I can open a pull request for this once triage confirms the direction. Not written yet; the change is the one-index drop migration, verified with the event-store tests.

Filed by

Muse Wonder Full 1.0 via Muse Code in T3 Code, on behalf of bompus

Dderektrimm6d ago

Since #21050 (merged 2026-10-18), idx_orchestration_events_agent_stream_sequence has a second forced user: the existing-message lookup in LegacyV1ThreadImporter (LegacyV1ThreadImporter.ts:715).

idx_orch_events_stream_sequence does not serve that read as well on an upgraded database. The thread's V1 rows are still in the table during the import and the stream index holds them too, so the lookup walks them; the partial index holds only V2 rows. On a real upgraded database (#20966), first boot spent 148 s in these lookups, with 5 server stalls of up to 11 s, through the stream index, against 0.3 s and no stalls with the partial index.

Dropping it would also make the import fail with no such index, which the query-plan test in LegacyV1ThreadImporter.test.ts checks. The correlation_id half of this issue is unaffected.

Bbompus6d ago

Thanks, you're right, and I checked it: since #21050, LegacyV1ThreadImporter.ts:715 reads orchestration_events INDEXED BY idx_orchestration_events_agent_stream_sequence, and its query-plan test asserts that index. So my claim that this index has no query user is wrong now, and it should stay. Dropping it would fail the import with no such index.

The correlation_id half still holds: no read in apps/server/src filters or orders by correlation_id. The narrower change is a migration that drops only idx_orch_events_correlation_id (43.7 MB on my database; in my copy, insert cost went from 0.093 to 0.085 ms per event without it, about 9%, while the 16% figure in the issue covered both indexes). I can retitle the issue to cover just that index, or open a pull request for it, whichever you prefer.

Dderektrimm6d ago

Thanks for checking. The direction is the maintainers' call rather than mine, but retitling to the correlation_id index alone seems clearest for triage, since that is the part still standing, and a PR would follow once they confirm it.

JJulius Marminge3d ago

Reproduced on main 70e5e384da: Linux, Node 24.21.0, vp run dev with isolated copied state and the paired web client. dbstat reports 24,576 bytes for idx_orch_events_correlation_id in the small dev database; a separate read-only check of 73,876 events reports 2,211,840 bytes. The server query inventory has no correlation-ID filter or ordering.

The schema-copy benchmark used WAL, synchronous=NORMAL, 36,000 initial synthetic events at roughly 3.3 KB payload, then 2,000 single-event transactions per arm, three repetitions. Removing only the correlation index reduced insert time in each pair, but VM timings were noisy, so this does not establish a reliable percentage. The migration regression test fails because the unused index remains; it also checks preservation of correlation values and the required indexes, including idx_orchestration_events_agent_stream_sequence.

Bbompus2d ago

Thanks for reproducing this, Julius. The pull request for the narrowed scope is ready: it adds a migration that drops only idx_orch_events_correlation_id and leaves idx_orchestration_events_agent_stream_sequence and the command_id index in place, since live queries name both with INDEXED BY. It is rebased on current main and has run through my fork's review: https://github.com/bompus/t3code/pull/19

On my 3.0M-event database the index is 48 MB (correlation_id is non-null on 265,973 rows), and one fewer index is maintained per event append. FoundationPersistence and ThreadSearch suites pass with the migration run in memory.

I'll open it against pingdotgg/t3code as soon as you confirm the direction.

Bbompus2d ago

Correction to my earlier comment: #23211 already drops idx_orch_events_correlation_id and is approved, so I won't open a separate pull request. My branch is superseded by it. For what it's worth, the index is 48 MB on a 3.0M-event database (correlation_id non-null on 265,973 rows) if that number is useful for the PR description.