[world-local] SQLite never uses events_by_correlation, so hook_created writes scan the whole run

Start with the schema in packages/world-local db/schema.ts, where events_by_correlation is defined, then the two correlation queries: Store.findEvent in store.ts around lines 189-191 and listEvents with a correlation ID around lines 224-236. Run the repro script in the issue with EXPLAIN QUERY PLAN to confirm the primary-key plan before changing anything. Done when those queries use events_by_correlation without ANALYZE (INDEXED BY is the recommended fix) and the 25k-event timings in the issue drop to about 0.01 ms. The steps.list index and cursor items are separate and can be left out.

The SQLite Local World from #5235 (c11fc40, not yet released; it would ship as @workflow/[email protected] in #5375) defines events_by_correlation, but SQLite never uses it. world-local never collects planner statistics (ANALYZE / PRAGMA optimize), and without them SQLite picks the events primary key for correlation lookups, which reads every event in the run.

The most visible effect is on events.create for hook_created. Its duplicate check (Store.findEvent, events-storage.ts:1105) runs inside the BEGIN IMMEDIATE write transaction. A new hook never matches, so each hook write scans the run's whole event log while holding the write lock. Workflow-writer attr_set (events-storage.ts:747) does the same check.

Details

The schema (db/schema.ts:32-40):

CREATE TABLE events (
  run_id TEXT NOT NULL, seq INTEGER NOT NULL, event_type TEXT NOT NULL,
  correlation_id TEXT, doc TEXT NOT NULL,
  PRIMARY KEY (run_id, seq)
) WITHOUT ROWID;
CREATE INDEX events_by_correlation ON events (run_id, correlation_id, seq);

Nothing in packages/world-local runs ANALYZE or PRAGMA optimize, or uses INDEXED BY. After a populated world closes, sqlite_stat1 still doesn't exist.

Query plans for Store.findEvent (store.ts:189-191) and for listEvents with a correlation ID (store.ts:224-236, behind events.listByCorrelationId):

The plans are the same on Node 22.13.0 (SQLite 3.47.2), 24.22.2 (3.53.4) and 26.13.1 (3.54.1). This was reproduced twice independently through the real API, and main at b1f9269 behaves the same way.

Measurements

These go through the real API: createWorld({ dataDir }) and events.create, on an on-disk world.db, with one run of N step_created events of about 1.7 KB each. Each number is the median of 15 calls.

A more realistic shape changes the size of the effect, not the plan. With 8 runs, step_created / step_started / step_completed triples plus hooks, and 25k events in the measured run, hook_created took 11.5 ms and listByCorrelationId 10.6 ms. After ANALYZE, both were under 0.25 ms.

Other callers of correlation lookups go through the same plan. Because listEvents reads limit + 1 rows in batches (store.ts:270), a lookup with fewer matches than that walks the run's whole log. Callers:

• core/src/runtime/invocations.ts:124 (hasResume, limit 100);
• the web UI (web/app/server/workflow-server-actions.server.ts:837);
• readPendingRetry (core/src/runtime/spec9/step-handler.ts:377). This one is milder: it only runs on retry redeliveries and stops after two events for the step, so the cost grows with how far back the step's events are. In a 25k-event run that's 0.03 ms when the step is at the tail and about 16 ms when it's at the start.

Repro

This uses the exact DDL and SQL from c11fc40 on an on-disk WAL file:

// node repro.mjs   (Node >= 22.13)
import { DatabaseSync } from 'node:sqlite';
import { rmSync } from 'node:fs';
const file = '/tmp/wl-repro.db';
for (const s of ['', '-wal', '-shm']) rmSync(file + s, { force: true });
const db = new DatabaseSync(file);
db.exec(`PRAGMA journal_mode = WAL;
CREATE TABLE events (run_id TEXT NOT NULL, seq INTEGER NOT NULL, event_type TEXT NOT NULL,
  correlation_id TEXT, doc TEXT NOT NULL, PRIMARY KEY (run_id, seq)) WITHOUT ROWID;
CREATE INDEX events_by_correlation ON events (run_id, correlation_id, seq);`);
const N = 25_000; // default WORKFLOW_MAX_EVENTS ceiling
const ins = db.prepare('INSERT INTO events VALUES (?, ?, ?, ?, ?)');
const doc = 'x'.repeat(1600);
db.exec('BEGIN');
for (let i = 1; i <= N; i++) ins.run('wrun_A', i, 'step_created', `step_${i}`, doc);
db.exec('COMMIT');
// Store.findEvent (hook_created duplicate check inside events.create)
const FIND = `SELECT doc FROM events WHERE run_id = ? AND correlation_id = ? AND event_type = ?
       ORDER BY seq LIMIT 1`;
const args = ['wrun_A', 'hook_new', 'hook_created'];
const run = (label) => {
  const plan = db.prepare(`EXPLAIN QUERY PLAN ${FIND}`).all(...args).map((r) => r.detail);
  const st = db.prepare(FIND);
  const t = performance.now();
  for (let i = 0; i < 20; i++) st.get(...args);
  console.log(label, plan, ((performance.now() - t) / 20).toFixed(3), 'ms');
};
console.log(`node ${process.version}, sqlite ${process.versions.sqlite}, ${N} events`);
run('before ANALYZE:');
db.exec('ANALYZE');
run('after ANALYZE: ');

node v26.13.1, sqlite 3.54.1, 25000 events
before ANALYZE: [ 'SEARCH events USING PRIMARY KEY (run_id=?)' ] 14.048 ms
after ANALYZE:  [ 'SEARCH events USING INDEX events_by_correlation (run_id=? AND correlation_id=?)' ] 0.010 ms

Possible fixes

• INDEXED BY events_by_correlation on the two correlation queries (recommended). This works with no statistics at all: at 25k events findEvent dropped from 32.5 ms to 0.006 ms and listEvents from 31.1 ms to 0.040 ms. It's the only option that doesn't depend on the shape of the data.
• PRAGMA optimize, run periodically or on close. It works once the table has data, but it's less dependable:
  • Running ANALYZE or optimize against a fresh empty database writes no stats, so running it once when a new database opens does nothing.
  • Runs start with run_created and run_started, which have no correlation ID. Stats collected from many short runs (for example, 200 runs of 3 events) keep the primary-key plan; the plan only flips once runs average about 8 events.
  • Stale stats from earlier short runs keep the scan for a later large run until optimize runs again.
• Adding event_type to the index does not help on its own: (run_id, correlation_id, event_type, seq) is still ignored without stats.

Smaller issues

• steps.list (steps-storage.ts:46-48, ORDER BY created_at, step_id for a run) plans as SEARCH steps USING PRIMARY KEY (run_id=?) | USE TEMP B-TREE FOR ORDER BY, even after ANALYZE, so every page reads and sorts all of the run's steps with their payloads (171 ms at 100k steps). An index on steps (run_id, created_at, step_id) is used without stats (first page: 13.1 ms → 0.12 ms at 8,333 steps), but it needs the row-value cursor below with it. With the index and the current OR cursor, deep pages get slower than today: 38 ms against 6.4 ms at 8,000 steps deep. With the row-value cursor it's 0.11 ms at any depth. hooks.list with a runId has the same shape.
• The entity cursor clause (store.ts:530), created_at < ? OR (created_at = ? AND id < ?), can't seek: runs.list plans as SCAN runs USING INDEX runs_by_created, so deeper pages cost more. A row-value comparison (created_at, id) < (?, ?) can seek. For runs alone the effect is small (0.05 ms → 0.45 ms at 19.7k runs deep).

What stays fast: event replay pages (run_id, seq > ?), appends, stream chunk reads and end-of-stream checks, and point lookups of steps, waits and hooks all use primary-key seeks, and their cost doesn't grow with run size.