Search index rebuild: dropping the old index holds the event loop (5.2 s at 100,000 traces) #695

What happens

When the trace search index has to be rebuilt from scratch, the server renames the old index at the start and drops it in the background, before the rebuild indexes anything (reconcileSearchIndex and dropRetiredIndex in src/storage/search-index.ts). The drop is one DROP TABLE statement. With secure_delete on, SQLite rewrites and zeroes every page of the old index, and the statement holds the event loop for its whole length.

That rebuild happens when:

• a file was opened by a SQLite without FTS5 (Node's built-in node:sqlite before 22.16.0), which drops the index's triggers, and then opened again by one with FTS5; or
• the index was built before the span column (unreleased builds only).

Measured

On the benchmark machine at 100,000 traces, each an agent loop sent over OTLP (three model calls, two tool calls), the index was 225 MB. On the first path above, the drop held the event loop for 5.2 s. Requests arriving in that time wait for it to finish.

For comparison, before the span column, the same path emptied a 73 MB index during the start instead, which blocked the start for 1.4 s in the same run. Dropping a 72 MB four-column index alone took 1.2 s.

Why it is one statement

FTS5's shadow tables (*_data, *_docsize) are read-only on our connections ("table … may not be modified"), so the old index cannot be deleted a row at a time in short steps like the rest of the build. It must not be dropped without zeroing either: its pages hold words of traces that still exist, and a trace deleted later would leave its words in those free pages.

Option

Run the drop in a worker thread on its own connection, with secure_delete on and a busy_timeout, and start the rebuild's steps when it reports done. Reads carry on while it runs (WAL). A write that arrives meanwhile waits for the lock inside busy_timeout, which is synchronous and blocks the event loop for that write, so the gain applies when no trace is written during the drop.

• The worker would open the file with the same driver the adapter uses (better-sqlite3 or node:sqlite). The driver must be resolved in the main thread and passed in, as an inline worker script.
• :memory: databases cannot be shared with a worker and keep the inline drop.

Priority is low: the path needs a store that alternates between SQLite builds with and without FTS5, and the time grows with the index, so it matters only for large stores.