[Bug]: Server: the thread list read blocks the event loop and every other database call for seconds on large databases

Before submitting

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

Area

apps/server

Steps to reproduce

Run a V2 server (t3code/codex-turn-mapping, or the v0.0.45-preview.20261001.2518 build) on a large statev2.sqlite. Ours is 33 GB with about 3,400 threads and 250,000 turn items.
Let it work normally with a client connected. getShellSnapshot runs on every client connect and from the pull request sync sweep, roughly once a minute.
Watch the server's event loop while a snapshot runs, for example with a 2 ms setInterval that records the longest gap between ticks.

Expected behavior

Reading the thread list does not stop the server from answering websocket pings, running other requests, or committing writes.

Actual behavior

ProjectionStore.getShellSnapshot runs selectShellThreadRows (about 15 correlated subqueries per thread, including two COUNT(*) over turn items) inside sql.withTransaction. The SQLite client (packages/shared/src/nodeSqliteClient.ts) has one synchronous node:sqlite connection behind a one-permit semaphore on the main thread, so for the whole read:

the event loop is blocked, and
every other statement, including writes, waits for the lock.

On a clone of our database, the longest main-thread gap per snapshot was 1.41 to 2.24 s (12 runs). Live server traces showed the same query at 1.4 to 2.6 s about once a minute, with the stall monitor reporting 2.0 and 2.5 s stalls right after. Clients drop the connection after missing keepalives, and each reconnect fetches the snapshot again, so disconnects arrive in clusters.

Impact

Major degradation or frequent failure

Version or commit

t3code/codex-turn-mapping @ de95adc (also v0.0.45-preview.20261001.2518)

Environment

macOS 26 (Apple Silicon), server run as a background service, 33 GB statev2.sqlite

Logs or stack traces

Measured with the real getShellSnapshot against an APFS clone of the database, timer gap recorded with a 2 ms setInterval:

snapshot_ms  max_main_thread_gap_ms
2375         2240
1791         1677
1876         1760
1994         1884
1745         1650
1502         1411

@saphid

Note

Grok responding on behalf of Julius.

Triage

Thanks for the timings and the clear breakdown of the query, @saphid! This is a real bug. I confirmed it on t3code/codex-turn-mapping at de95adc336e, the branch tip you named. main doesn't do this read, since V1 already stores the shell fields on projection_threads and selects them directly.

ProjectionStore.getShellSnapshot wraps selectShellThreadRows in sql.withTransaction. That SELECT runs 13 correlated subqueries per thread, including the two COUNT(*)s over orchestration_v2_projection_turn_items (item_count and runless_item_count). The same transaction then runs selectShellRunItemCounts, a GROUP BY count over that table, and decodes every thread payload before it commits.

packages/shared/src/nodeSqliteClient.ts has a single node:sqlite DatabaseSync connection behind a semaphore that allows one statement at a time. statement.all runs synchronously on the calling thread, and the transaction keeps the semaphore until it commits. So for the whole snapshot the event loop is stuck, and every other statement, writes included, has to wait. That's why your main-thread gap is almost the same as the snapshot time.

Two callers run this on a live server:

Every shell subscription. subscribeOrchestrationV2Shell loads getShellSnapshot({ location: "active" }) without unsettledOnly, inside another sql.withTransaction (apps/server/src/ws.ts). Every reconnect repeats the full read of active threads.
The pull request discovery pass. ThreadPullRequestService queues a full pass every minute, and that pass calls getShellSnapshot({ location: "active", unsettledOnly }). If a backfill is still pending, it reads every active thread instead. The snapshot sync sweep isn't the culprit here: PullRequestSyncReactor also runs every minute, but it calls getThreadsWithPullRequests(), which doesn't use this query.

unsettledOnly is meant to skip settled threads before their item rows are counted, and the filter sits in the outer WHERE, so only the remaining threads get counted. A 1.4 to 2.6s trace once a minute means most of those threads are still unsettled, or a backfill is forcing the full read. A client connect always takes the full path.

On the disconnects: the client sends a Ping every 5 seconds and closes the socket if no Pong has arrived by the next tick. A stall only drops a client when it runs past that deadline, so a single snapshot doesn't always cause a drop. But each drop leads to a reconnect, the reconnect runs the full snapshot again, and that can snowball into the clusters you saw.

Suggested fix

Stop counting turn items in the shell list. Either store those counts on the thread row or leave them out of this query.
Have the once-a-minute discovery pass skip getShellSnapshot. It only needs the branch, settlement, and pull request fields.
Don't hold the only connection for the whole read. Even once the SQL is cheaper, a long statement.all on this client will still block the event loop. The correlated counts are what stretch it to seconds.

Query	Calls	Max
Thread search (`WITH candidate AS (SELECT threads.thread_id, ..., json_extract(messages.payload_json, '$.text') AS match_text ...`)	2	15.2 s
`SELECT * FROM scheduled_tasks WHERE enabled = 1 AND next_run_at <= ? ...`	431	8.4 s
Settlement query (`SELECT t.payload_json, r.run_id, r.completed_at, item.payload_json AS failure_payload_json, (SELECT json_extract(session.payload_json, '$.lastError') ...`)	431	6.4 s
`SELECT payload_json FROM orchestration_v2_projection_threads WHERE deleted_at IS NULL AND json_extract(payload_json, '$.archivedAt') IS NULL ...`	73	5.6 s

The thread search started at 12:53:06 and ran for 15.2 s. The 15.3 s stall was logged at 12:53:22. Thread search runs json_extract over every message payload, with no index or FTS behind it.

scheduled_tasks has 0 rows, so its 8.4 s is time spent waiting for the single connection, not query cost. One slow read, such as the search, delays every other database call queued behind it. That matches the one-connection-behind-a-semaphore diagnosis above. On top of the shell snapshot, thread search should be moved off the main thread or backed by FTS.

Another data point, plus a local mitigation in case it helps as a reference.

Preview 0.0.46-preview.20261002.2598 (V2) in a Linux container. statev2.sqlite is 10.4 GB, with about 1,200 threads and 166,000 turn items. The browser showed "reconnecting" most of the time, often for minutes before a message could be sent, along with "Some requests are slow" toasts for 20–30 requests at once. A local HTTP probe every 5 s over 24 hours had 15.0% of requests over 1 s and 10.0% with no answer within 2 s. A probe every 0.5 s showed 1.5–5 s stalls about every five seconds.

We rebuilt the server at the same revision with three changes. The patch is in https://gist.github.com/Dropje97/4a5002ee4cbe52292fdf37b7ed45fcbc (based on b37fcea, with focused tests):

nodeSqliteClient yields (Effect.yieldNow) after each completed native statement, so socket I/O can run between the statements of a long read or transaction.
The persistence layer sets PRAGMA cache_size = -65536 (64 MiB), so repeated projections reuse pages.
Storage cleanup coalesces queued sweeps, and on provider-session.updated only sweeps when the session has stopped.

With that build the UI responded immediately and stopped reconnecting, even with eight parallel CPU-heavy import jobs running on the host. The same probe dropped to 4.4% over 1 s and 1.9% without an answer. We did not measure the three changes separately.

This is a mitigation: it cannot help with a single long statement, and the worker-thread reads in #14703 look like the proper fix. We can test #14703 on this workload once it reaches the preview channel.

Another read that produces this stall, with a different cause: T3 never collects SQLite query-planner statistics, so on a large orchestration_events the command-ID lookup picks the wrong index.

Setup: 0.0.46 nightly (20261005 builds), desktop on Windows 11 and on macOS, SQLite 3.53.4 via node:sqlite. Nothing in the server runs ANALYZE or PRAGMA optimize (Sqlite.ts on main at c4481b9 sets only busy_timeout, foreign_keys, journal_mode and journal_size_limit), and sqlite_stat1 was absent on both machines.

Mechanism: the agent-event read in OrchestrationEventStore.readApplicationRows with onlyAgentEvents: true filters on the sequence range, application_event_version = 2 AND aggregate_kind = 'thread', and command_id = ?. Unlike the other branch it has no INDEXED BY. Without statistics the planner chooses:

SEARCH orchestration_events USING INDEX idx_orchestration_events_application_sequence (application_event_version=? AND sequence>? AND sequence<?)

which walks every v2 event, instead of idx_orch_events_command_id.

Cost:

Machine	Events	Lookup without stats	After `ANALYZE`
Windows	~1.1M	~570 ms (500–675)	~0.1 ms
macOS	~211k	4–5 ms	0.01–0.03 ms

On Windows a CPU profile put ~99% of samples in StatementSync.all for these repeated lookups. The backend stopped answering, the app took minutes to open and showed "reconnecting", and updating the nightly or reinstalling didn't help. ANALYZE orchestration_events; took ~65 ms on the Mac, changed no rows, and switched the plan. On Windows the plan has stayed on the command-ID index since, now at ~1.37M events (0.015 ms per lookup).

Fresh database: I built a copy of the same schema with 600k synthetic events and never analyzed it. The same lookup chose application_sequence and took 39 ms. Either of these switched it to idx_orch_events_command_id (0.02 ms):

PRAGMA optimize=0x10002 on open. This took 0.3 s once on that copy; adding the 0x10 bit caps it with a temporary analysis_limit.
Running the query, then a plain PRAGMA optimize before closing.

Suggested fix: follow SQLite's recommendation for long-lived connections (https://www.sqlite.org/pragma.html#pragma_optimize). Run PRAGMA optimize=0x10002 when the server opens the database, then PRAGMA optimize periodically (hourly or daily) and at shutdown, so statistics exist before the event table gets large. It's also worth running after migrations that add indexes. Pinning the agent-event command lookup with INDEXED BY idx_orch_events_command_id would protect that one query, but not other plans that depend on statistics.

@3mdistal

We looked into this on a 21 GB statev2.sqlite and found three separate costs in the same read. Each has its own focused PR with a test.

1. The `last_error` lookup is quadratic (#17842)

One more cause of this stall, separate from the turn-item counts: the last_error lookup in selectShellThreadRows. It walks every provider session on the thread's provider instance, for every thread.

The subquery joins provider_sessions to provider_session_bindings without a fixed order. With no sqlite_stat1 (as @3mdistal found), SQLite picks:

SEARCH session USING INDEX orchestration_v2_projection_provider_sessions_instance_status_idx (provider_instance_id=?)
SEARCH binding USING COVERING INDEX sqlite_autoindex_orchestration_v2_projection_provider_session_bindings_1 (provider_session_id=? AND thread_id=?)

So one snapshot costs threads × sessions-per-instance probes. Most threads usually share one provider instance, so the cost grows quadratically.

On 0.0.46-nightly.20261007.2787 (main has the same SQL): 21.6 GB statev2.sqlite, 4,445 threads, 1,903 sessions with 1,900 on one instance. Read-only, every 10th thread, warm:

	per thread	445 threads
as shipped	904 µs	402 ms
bindings outermost (`CROSS JOIN`)	42 µs	18.6 ms

The 445-row sample fell from 402.3 ms to 18.6 ms warm and returned identical rows. The new full statement returned 4,440 rows in 159.7 ms warm. We did not run the old full statement in this benchmark or compare the full old/new result sets; the roughly 4 s old-full estimate is extrapolated from the sample, consistent with earlier 3.0-3.6 s server traces. We have not run a patched server against this database.

Separately, earlier server traces on that install showed the shell SQL span at 3.0-3.3 s per MCP thread list, 3.3-3.6 s per client shell load, and 1.3-1.8 s for every minute's pull request pass. getLimitRecoveryCandidates has the same lookup (the "Settlement query" in @SpyrosPsarras's table).

2. `t3_thread_list` computes every project (#17843)

A third caller of this read, besides the client shell load and the pull request pass: the MCP t3_thread_list tool, and it reads the most.

ThreadManagementService.listProjectThreads calls orchestrator.getShellSnapshot() with no options. selectShellThreadRows then runs over every non-deleted thread in every project, archived ones included, and only afterwards does JS keep the requested project's active threads. Every page of a paged listing repeats the full read.

On 0.0.46-nightly.20261007.2787 (main has the same code): 21 GB statev2.sqlite, 4,409 threads in 14 projects. Server traces showed 3.2 to 6.5 s per t3_thread_list call. An agent that listed 6 projects every 4 minutes held the main thread about 20 s per cycle, 8.6% of wall time. Read-only, Node 24.18.1 node:sqlite:

statement	rows	time
shell SQL as shipped (what `t3_thread_list` runs)	4,409	3,448 ms
same SQL plus `AND t.project_id = ?`	331	413 ms

Archiving threads does not lower this cost, since the list reads archived threads too.

3. The server runs on a 2 MiB page cache (#17844)

@Dropje97's patch above sets PRAGMA cache_size = -65536 alongside two other changes, measured together. I measured the cache change by itself, in #17844.

The server's connection runs at SQLite's 2 MiB default, so on a large database it reads the same pages from the OS again on every query. On a 21.8 GB statev2.sqlite (4,492 threads, Linux, a separate read-only connection in one read transaction), main's per-thread selectShellThreadRows(threadId), the statement getThreadShell runs on every thread event, did this for 50 recent threads:

	2 MiB (main)	64 MiB
read syscalls	35,050 (137 MiB)	1
wall time	21.4 ms	10.1 ms
kernel CPU	10.8 ms	0.0 ms

On a synthetic 509 MiB database built by the repo's migrations (4,000 threads), getShellSnapshot went from 13,962 reads to 2, and the cache survived the connection's own writes. Wall time didn't change there, since each synthetic thread has only a few pages.

Limits:

I did not measure the full shell list's working set. Samples of the list on the 21.8 GB database, run with the bindings-first SQL rewrite from perf(server): drive shell last-error lookup from thread bindings #17842, read 37.7 MiB for 10% of the threads and 70.1 MiB for 25% on a fresh connection. Those are read-volume estimates, not distinct-page counts, and the larger sample already exceeds 64 MiB. The list still needs the query fixes from the triage above.
SQLite empties a connection's cache whenever another connection commits. In the synthetic run, 200 getThreadShell calls, each after another connection's commit, cost 8,102 page reads at both sizes. The reader worker in fix(server): thread list reads no longer freeze the server on large databases #14703 is a second connection, so a bigger cache there would help only within one read transaction.
Each process gets its own cache. The CLI uses the same setup, so a warmed server and CLI can together hold roughly 128 MiB of configured cache budget plus overhead, though a short CLI command may touch far fewer pages. In a synthetic benchmark, reading a 339.1 MiB database at the new bound added about 65 MiB of RSS over the default; that is one observation, not a ceiling.

Model and harness: Claude Opus 5.5 (Claude Code in T3 Code) produced the changes and measurements; GPT-6.1 Sol reviewed them.

🤖 Generated with Claude Code

@mathew-grw

Note

Claude posting on behalf of @mathew-grw.

Another occurrence, on a smaller database, where host CPU saturation turns fast reads into long event-loop stalls. This is an edge case: the machine was overloaded by other work, so it may fall outside the scope of this issue and not be a priority. I'm adding it for context.

Environment

Desktop app 0.0.46-nightly.20261012.3035, local backend, macOS 27.0 arm64, 10 cores
statev2.sqlite 2.6 GB: 973 threads, 77k turn items, 374k orchestration events. No sqlite_stat1.

What happened
The desktop app kept showing " is reconnecting". Over 32 minutes, ws.rpc.subscribeServerConfig was interrupted 9 times, once per reconnect.

Evidence

server.eventLoop.stall fired 28 times in 35 minutes: delayMaxMs 2,056–8,504, utilization 0.93–1.0.
In the same 30 s windows the server used only 1.2–2.9 s of CPU (cpuUserMs + cpuSystemMs), with 12k–28k involuntary context switches. Load average was 53–75 on 10 cores, with 0% idle. The load came from other apps, Chrome and test runs. T3 itself was about 8%.
In the server, sqlite spans for selectShellThreadRows took 3.3–9.8 s. On a copy of the database, on the same machine, the same query takes 0.2 s warm. Running ANALYZE on the copy didn't change that (0.21–0.36 s).
Each reconnect resubscribes and reruns the shell snapshot, so stalls bunch up after a drop.

Why it matters here
When the host is busy, the main thread is likely being descheduled while it holds the only SQLite connection. That turns a 0.2 s read into seconds of blocked loop and missed pings. Moving SQLite off the main thread, or limiting the shell snapshot, would keep the server responsive on a busy machine, not only on a large database.

Diagnosed with t3 triage by Claude Code (Claude Opus 5.5).

Another occurrence on 0.0.46-nightly.20261013.3053, observed on 2026-10-13. The local desktop showed the environment reconnecting and several chats stopped updating together. A native backend sample caught synchronous SQLite on the main thread while traces recorded long shell-snapshot and thread-history reads.

The same investigation found a separate duplicate-owner problem on an M1 remote host. Its process evidence and verified service-only workaround are in the report on #6097. The stalled client Mac had one desktop-owned database server. We have not established that the M1 conflict caused this SQLite stall.

Environment and workload

Packaged desktop on Apple Silicon, macOS 27.2 build 26B5101f, 48 GiB RAM, 18 logical CPUs.
T3 Code 0.0.46-nightly.20261013.3053, release target 6524a8d43795dc543f7cfb572388cdea3662ad48, embedded Node 24.21.0 and Electron 44.4.5.
Roughly 22.8 GB statev2.sqlite, 2,057 threads, and 360,500 turn items. The largest observed thread had 33,132 items.
Several Codex turns were running. Three headless browser processes each used roughly 293-324% CPU. Around 16 GB of swap was used, with active paging and compression. The initial one-minute load average was about 83.

This was a live incident under substantial host pressure, not an isolated query benchmark. The host-load amplification described in the preceding comment is relevant here too.

Redacted native stack and trace excerpts

A three-second /usr/bin/sample capture began at 12:12:14 UTC. All 1,920 sampled main-thread stacks included node::sqlite::StatementSync::All. Selected frames are below, with intervening frames and addresses omitted. The pread frame is one branch within the sample.

Process: T3 Code (Nightly), backend PID 56382, desktop parent PID 56317
Code Type: ARM64
Date/Time: 2026-10-13 05:12:14.559 -0700

1920 main thread, com.apple.main-thread, serial
1920 node::sqlite::StatementSync::All(v8::FunctionCallbackInfo<v8::Value> const&)
1920 node::sqlite::StatementExecutionHelper::All(node::Environment*, node::sqlite::DatabaseSync*, sqlite3_stmt*, bool, bool)
 967 pread, libsystem_kernel.dylib

The captured completed spans include:

Span end, UTC	Operation	Duration
12:12:08.838352	Turn-item history SELECT within `t3_thread_wait`	26,826.100416 ms
12:12:24.039006	Provider-thread history SELECT	14,677.541916 ms
12:13:05.713221	`selectShellThreadRows` SQL	23,967.135416 ms
12:13:07.364461	Parent `http.orchestration.loadShellSnapshot`	25,618.489583 ms

These are wall-clock span durations, which can include scheduling and contention. The native sample directly establishes main-thread synchronous SQLite execution during the incident. It does not measure how much of each span was CPU time.

Selected trace records with SQL parameter values and application data omitted

[
  {
    "name": "sqlite",
    "durationMs": 26826.100416,
    "endedAt": "2026-10-13T12:12:08.838352+00:00",
    "query": "\n                SELECT payload_json\n                FROM orchestration_v2_projection_turn_items\n                WHERE thread_id = ?\n                  AND type IN (SELECT value FROM json_each(?))\n    \u2026[truncated]"
  },
  {
    "name": "sqlite",
    "durationMs": 14677.541916,
    "endedAt": "2026-10-13T12:12:24.039006+00:00",
    "query": "\n            SELECT payload_json\n            FROM orchestration_v2_projection_provider_threads\n            WHERE thread_id = ?\n               OR owner_node_id IN (\n                 SELECT node_id\n    \u2026[truncated]"
  },
  {
    "name": "sqlite",
    "durationMs": 23967.135416,
    "endedAt": "2026-10-13T12:13:05.713221+00:00",
    "query": "\n            SELECT\n              t.thread_id,\n              t.payload_json,\n              CASE\n                WHEN json_extract(t.payload_json, '$.forkedFrom.type') = 'run'\n                  THEN js\u2026[truncated]"
  },
  {
    "name": "http.orchestration.loadShellSnapshot",
    "durationMs": 25618.489583,
    "endedAt": "2026-10-13T12:13:07.364461+00:00"
  }
]

Selected shell-query plan lines

These are excerpts from the captured EXPLAIN QUERY PLAN, not a measured execution-time breakdown.

SCAN t
CORRELATED SCALAR SUBQUERY 4
SEARCH binding USING INDEX orchestration_v2_projection_provider_session_bindings_thread_idx (thread_id=?)
SEARCH session USING INDEX sqlite_autoindex_orchestration_v2_projection_provider_sessions_1 (provider_session_id=?)
...
CORRELATED SCALAR SUBQUERY 13
CO-ROUTINE per_run
SEARCH i USING COVERING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=?)
SCAN per_run
...
CORRELATED SCALAR SUBQUERY 14
SEARCH i USING COVERING INDEX orchestration_v2_projection_turn_items_thread_run_idx (thread_id=? AND run_id=?)
USE TEMP B-TREE FOR ORDER BY

The provider-session lookup used the binding index first in this capture. We have not established that the session-first plan discussed earlier in this issue explains this incident.

Source correlation and recovery

In the installed release, nodeSqliteClient.ts opens DatabaseSync, calls statement.all, and uses a one-permit semaphore. ProjectionStore.ts contains the shell query and transactional snapshot read. The captured history SELECT is also relevant to t3_thread_read and t3_thread_wait, beyond the sidebar snapshot path.

At about 12:29 UTC, the desktop was quit normally and reopened on the same build. Nine active turns were cancelled during shutdown. All nine were subsequently resumed and verified through fresh assistant responses and command activity. Saved histories and drafts were retained.

Loopback HTTP observations were:

Before restart:
12:17:31.967306Z  timeout after 8.052 s
12:17:40.019090Z  HTTP 200, 6.647 s
12:17:46.666628Z  HTTP 200, 1.494 s

After restart:
12:30:11-12:30:31Z  five HTTP 200 responses, 2-18 ms
12:33:40-12:34:00Z  five HTTP 200 responses, 2-22 ms

The probed /health path returned fallback application HTML. These values show HTTP responsiveness only, not a dedicated health check or successful RPC execution. Fresh thread responses and commands provided the separate continuation check.

Restart was a workaround. It also reduced the active workload and swap use, so this is not a controlled before/after performance comparison. Post-restart traces still included SQLite spans around 1.5-1.7 seconds.

No source patch or live-database tuning was applied, and no minimal reproduction has been built. #14703 is still open and unmerged at the time of this report. We have not tested it. A useful follow-up would hold workload constant on a private database copy and compare both shell snapshots and MCP history reads, recording event-loop delay, returned history size, CPU scheduling, and paging on the same timeline.

Follow-up analysis with Kimi K3

An independent Kimi K3 analysis identified two useful refinements. Codex checked both against the saved redacted capture.

First, the native sample contains three disjoint pread leaf branches with 967, 12, and 615 samples. Together, these account for 1,594 of 1,920 main-thread samples, about 83%, in the three-second capture. This quantifies the read-system-call path within synchronous SQLite. It does not distinguish physical disk waits, page-cache reads, or descheduling, and it is not a percentage of CPU time. Some intervening Electron frames resolve to ares_dns_rr_get_ttl with very large offsets. Those nearest-symbol labels are not evidence of DNS activity.

Second, long SQLite spans extended beyond the shell and history reads. Additional selected records from the same capture were:

Span end, UTC on 2026-10-13	SQL operation	Duration
12:14:04.623056	Project-list SELECT	29,689.074 ms
12:14:08.213198	Due scheduled-tasks SELECT	25,451.090833 ms
12:14:08.244378	Session-list SELECT	25,455.24375 ms
12:15:29.577296	Failure-detail SELECT	64,296.730958 ms
12:15:29.607641	Scheduled-task UPDATE by task ID	12,514.666666 ms

This broadens the observed impact across reads and a write. These remain elapsed span times, not isolated query costs. The capture does not establish whether each span was dominated by SQL execution, serialization, I/O, or scheduling. The long outer t3_thread_wait MCP spans are also not query benchmarks, since they can include intentional waiting.

The most useful next comparison remains an idle-host run and a controlled-load run on a private database copy, measuring both shell snapshots and history reads. The additional sample counts do not establish a causal link to the separate M1 ownership problem.

Prepared with Codex / GPT-6. The excerpts omit credentials, private addresses, home paths, thread identifiers, prompt content, and SQL parameter values. Raw traces, provider transcripts, and databases remain private.

The turn-item counting is still on main after #15033.

The shell query still groups every selected thread's turn items by run and separately counts runless items.
The fork-source count query also counts item rows.

For scale, I timed just those two count expressions, unchanged, on a read-only copy of a heavily used database: 2,240 threads and about 350,000 turn items.

The first pass over 2,097 active threads took 1.79 s.
Warm passes took a median of 102 ms (97–112 ms over 10 runs).

That's standalone SQL timing (Python sqlite3 3.45, Linux), not a full snapshot or event-loop measurement.

Would you want a focused PR that keeps per-thread, per-run counts in a small table and has the shell queries read those instead?

How the counts stay correct: they are backfilled in a migration and maintained in the same transaction by insert, delete and key-move triggers on the turn-item table. Payload-only updates, which is all a streaming message does, leave the counts untouched. Runless, rolled-back-run, missing-run and fork-source results stay as they are today.
Relation to #14703: that PR moves these reads off the main connection, and this would make the reads cheaper, so the two are complementary.
Scope: I'd keep it to the count table, the shell reads and focused tests.
Tests: backfill, insert, delete and move; rollback; projection rebuild and replay; shell results compared before and after, including rolled-back runs and fork inheritance.
Measurements: count-query and full-snapshot timings before and after, plus write overhead and migration time.

Drafted with Claude Code and Codex (GPT-6.1 Sol).

Navigation Menu

Repository navigation

[Bug]: Server: the thread list read blocks the event loop and every other database call for seconds on large databases #14701

Before submitting

Area

Steps to reproduce

Expected behavior

Actual behavior

Impact

Version or commit

Environment

Logs or stack traces

Triage

Suggested fix

Related

1. The `last_error` lookup is quadratic (#17842)

2. `t3_thread_list` computes every project (#17843)

3. The server runs on a 2 MiB page cache (#17844)

Environment and workload

Redacted native stack and trace excerpts

Source correlation and recovery

Follow-up analysis with Kimi K3

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[Bug]: Server: the thread list read blocks the event loop and every other database call for seconds on large databases #14701

Description

Before submitting

Area

Steps to reproduce

Expected behavior

Actual behavior

Impact

Version or commit

Environment

Logs or stack traces

Activity

juliusmarminge commented on Oct 2, 2026

Triage

Suggested fix

Related

SpyrosPsarras commented on Oct 6, 2026

Dropje97 commented on Oct 6, 2026

3mdistal commented on Oct 7, 2026

only21mil commented on Oct 10, 2026

1. The last_error lookup is quadratic (#17842)

2. t3_thread_list computes every project (#17843)

3. The server runs on a 2 MiB page cache (#17844)

mathew-grw commented on Oct 12, 2026

andrewfree commented on Oct 13, 2026

Environment and workload

Redacted native stack and trace excerpts

Source correlation and recovery

Follow-up analysis with Kimi K3

tannerpolley commented on Oct 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

1. The `last_error` lookup is quadratic (#17842)

2. `t3_thread_list` computes every project (#17843)