# idx_orch_events_correlation_id has no query user: 44 MB and about 9% of insert cost on a real 19.8 GB database · Issue \#20534 · pingdotgg/t3code

[View on GitCafe](https://git.cafe/pingdotgg/t3code/issues/20534)

Repository: [pingdotgg/t3code](https://git.cafe/pingdotgg/t3code)

Visibility: public

State: backlog

Priority: 0

## Description

[Discussion and activity](https://git.cafe/pingdotgg/t3code/issues/20534)

---

### 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
{"arm":"all","perEventTxnMedianMs":0.093,"perEventRangeMs":[0.093,0.096]}
{"arm":"no-corr","perEventTxnMedianMs":0.085,"perEventRangeMs":[0.084,0.09]}
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