[Bug]: 26.5 GB V2 state database: thread-shell and recovery queries take 2–22 s; six indexes cut startup from ~137 s to ~17 s #22659

Before submitting

• I searched existing issues and did not find a duplicate.
• I included enough detail to reproduce or investigate the problem.

Related, but each covers only part of this: #21578 (one partial index on unfinished turn items), #21193 (windowed snapshot indexes), #21840 (long-thread open blocks the writer), #17955 / #21122 (planner statistics).

Area

apps/server

Steps to reproduce

1. Use a V2 state database at large scale: ~/.t3/userdata/statev2.sqlite of about 26.5 GB, 3,404 threads, event sequence about 4.95 million.
2. Start the app, or keep it running with several agent sessions active.
3. Time server startup, and time the projection queries listed below on a copy of the database.

Expected behavior

Startup, thread-list loading and turn start/recovery lookups stay fast as the database grows, and the server stays responsive to MCP tool calls and queued messages.

Actual behavior

The app froze repeatedly. Startup took about 137 s. Symptoms seen from agent sessions while the backend was busy:

• MCP tool calls to the T3 server failed with ECONNRESET, connect timeouts or "Error POSTing".
• Queued messages stayed held for hours, while newer messages sent to the same thread started runs immediately.
• After a server restart, running agent sessions had their MCP authorization rejected until they reconnected. This may be a separate issue.

Benchmarks on a copy of the database showed several projection queries taking 2–22 s each. Adding the six indexes below brought startup to about 17 s, on the same machine and database. Every benchmarked query returned identical results before and after (same row counts, same SHA-256 digest of the result rows).

Each query was run twice in a row; times are in seconds as (first run, second run). "Control" is a second baseline pass without indexes, to separate cache warm-up from index effects.

So some lookups get fast once cached even without indexes. The thread-shell list stays at about 19–20 s warm without them and drops to 0.25 s warm with them.

Impact

Major degradation or frequent failure

Version or commit

T3 Code (Nightly) 0.1.2-nightly.20261021.3457

Environment

macOS 26.6.2, Apple Silicon (Mac16,10), desktop app; state database about 26.5 GB

Logs or stack traces

The six indexes applied as a local workaround:

CREATE INDEX t3_recovery_test_items_status_cover
ON orchestration_v2_projection_turn_items(thread_id, status, type, run_id, node_id, provider_thread_id)
WHERE status IN ('pending', 'running', 'waiting');

CREATE INDEX t3_recovery_test_message_order
ON orchestration_v2_projection_turn_items(thread_id, json_extract(payload_json, '$.messageId'), ordinal)
WHERE type IN ('user_message', 'assistant_message');

CREATE INDEX t3_recovery_test_runs_status_order_cover
ON orchestration_v2_projection_runs(thread_id, ordinal DESC, run_id DESC, status);

CREATE INDEX t3_recovery_test_runs_last_executed
ON orchestration_v2_projection_runs(thread_id, (completed_at IS NULL) DESC, completed_at DESC, ordinal DESC, run_id DESC)
WHERE status <> 'queued' AND NOT (status = 'cancelled' AND json_extract(payload_json, '$.startedAt') IS NULL);

CREATE INDEX t3_recovery_test_interrupt_lookup
ON orchestration_v2_projection_turn_items(thread_id, run_id)
WHERE type = 'run_interrupt_request';

CREATE INDEX t3_recovery_test_pending_items_order
ON orchestration_v2_projection_turn_items(thread_id, ordinal, turn_item_id)
WHERE status IN ('pending', 'running', 'waiting');

Build time per index on the 26.5 GB database (about 7 minutes in total, holding the writer). A migration would need to plan for this:

EXPLAIN QUERY PLAN, before and after (only the lines that changed):

recovery_1
  before: SEARCH latest USING INDEX orchestration_v2_projection_runs_thread_ordinal_idx (thread_id=?)
  after:  SEARCH latest USING COVERING INDEX t3_recovery_test_runs_status_order_cover (thread_id=?)
  before: SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=? AND run_id>?)
  after:  SEARCH item USING COVERING INDEX t3_recovery_test_items_status_cover (thread_id=? AND status=? AND type=? AND run_id>?)

recovery_3
  before: SEARCH orchestration_v2_projection_runs USING INDEX orchestration_v2_projection_runs_recovery_idx (status=? AND thread_id=?)
  after:  SEARCH orchestration_v2_projection_runs USING COVERING INDEX t3_recovery_test_runs_status_order_cover (thread_id=?)
  before: SEARCH item USING INDEX orchestration_v2_projection_turn_items_recovery_idx (thread_id=?)
  after:  SEARCH item USING COVERING INDEX t3_recovery_test_items_status_cover (thread_id=? AND status=? AND type=?)
  before: SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
  after:  SEARCH item USING INDEX t3_recovery_test_items_status_cover (thread_id=? AND status=? AND type=?)

recovery_6 (both turn-item subqueries)
  before: SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
  after:  SEARCH item USING COVERING INDEX t3_recovery_test_items_status_cover (thread_id=? AND status=?)

recovery_10
  before: SEARCH item USING INDEX orchestration_v2_projection_turn_items_thread_ordinal_idx (thread_id=?)
  after:  SEARCH item USING INDEX t3_recovery_test_pending_items_order (thread_id=?)
  before: SEARCH orchestration_v2_projection_runs USING INDEX orchestration_v2_projection_runs_recovery_idx (status=? AND thread_id=?)
  after:  SEARCH orchestration_v2_projection_runs USING COVERING INDEX t3_recovery_test_runs_status_order_cover (thread_id=?)

shell_all (correlated subqueries per thread)
  before: SEARCH r USING INDEX orchestration_v2_projection_runs_thread_ordinal_idx (thread_id=?)
  after:  SEARCH r USING COVERING INDEX t3_recovery_test_runs_status_order_cover (thread_id=?)
  before: SEARCH candidate USING INDEX orchestration_v2_projection_runs_thread_ordinal_idx (thread_id=?)
          USE TEMP B-TREE FOR ORDER BY
  after:  SEARCH candidate USING INDEX t3_recovery_test_runs_last_executed (thread_id=?)
  before: SEARCH stop_request USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=? AND run_id=?)
  after:  SEARCH stop_request USING COVERING INDEX t3_recovery_test_interrupt_lookup (thread_id=? AND run_id=?)

message_order
  before: SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
          USE TEMP B-TREE FOR GROUP BY
  after:  SEARCH orchestration_v2_projection_turn_items USING INDEX t3_recovery_test_message_order (thread_id=?)

The pattern: lookups filter turn items by status without type, or order runs per thread by ordinal/completed_at, and the existing indexes do not match, so they read each thread's full turn-item or run history. With planner statistics cleared on open (#21122), nothing compensates for the mismatch.

Screenshots, recordings, or supporting files

No response

Workaround

The six indexes above, applied locally after a full database backup. Nothing was deleted, and the event sequence and thread count were unchanged afterwards. Rollback is DROP INDEX IF EXISTS for each of the six names.

This is a local workaround only. It could conflict with a future migration, and it does not survive a fresh database. Ideally a T3 migration would own equivalent indexes, or the queries would change to fit existing ones, with the build time above in mind for large installs.