Thread-list reads still run synchronously on the event loop after #1204 #1207

⚠ Agent-generated: An AI agent produced these measurements. Please verify
details before you act on them.

Summary

The thread-list route still reads SQLite synchronously on the event loop. After
#1204, the cost is bounded but not zero.

PR #1198 moved these reads to a worker thread. We closed that PR because #1204
removed the reason for it. This issue keeps the measurements and the option.

Why we closed #1198

PR #1198 answered an 11.2 second event-loop stall. #1204 showed that the stall
came from listLatestGoalEventRowsByThreadIds, not from the thread-list query,
and cut it from 10.5s to 0.10s cold. A worker thread moves a slow read off the
loop; it does not make the read fast. #1204 makes it fast.

#1198 also carried a large amount of permanent machinery: a worker thread, a
32-slot queue cap, request deadlines, abort handling, worker restart, safe
shutdown, and a new 503 contract. Three automated review rounds and two
adversarial review passes found six defects in it, including one bad database
row disabling all thread lists until restart, and every client abort writing an
error log and a 500. The maintenance cost looked high against a residual that
is no longer catastrophic.

What remains

Measured on a rebuilt 1.45GB database with 26K threads, with read IOPS capped at
1000 in a cgroup:

• About 1,700 to 2,000 scattered page reads to fetch the returned rows.
• About 2 seconds cold for a full 26K-thread list.
• About 50 to 170 ms warm, which is per-row CPU.

This cost is inherent. It comes from row scatter in a threads table
interleaved with a large events table. The investigation tested a 24-column
covering index (the planner does not use it reliably) and a full VACUUM with
an ideal sequential scan (still 0.85s at 1000 IOPS). No index removes it.

Removing the GROUP BY and ORDER BY temporary B-trees improved cold time by
only about 4 percent.

When to revisit

Reconsider a worker thread, or another way off the event loop, if any of these
becomes true:

• Real installs report event-loop stalls after Fix the #1131 cold stall at its source: index the latest-goal lookup, drop the thread-list GROUP BY #1204 and listActiveBackgroundTaskCountsByThreadIds throws 'too many SQL variables' at ≥16,377 visible threads, failing the thread-list route #1205 land.
• The residual grows because thread counts grow.
• Another route needs the same treatment, so the infrastructure serves more
  than one caller.

Related

• Prevent synchronous SQLite reads and retention sweeps from blocking the server #1131 — the original stall report.
• Fix the #1131 cold stall at its source: index the latest-goal lookup, drop the thread-list GROUP BY #1204 — fixes the root cause.
• listActiveBackgroundTaskCountsByThreadIds throws 'too many SQL variables' at ≥16,377 visible threads, failing the thread-list route #1205 — the next query-level fix on this same route.
• Move thread list reads off the event loop #1198 — the closed worker-thread PR. The branch is kept.

Not covered here

The retryable field on API errors is produced today on main (for example
apps/server/src/services/threads/thread-create.ts:146 and several host-daemon
contract errors) and no client code reads it. That is a separate pre-existing
bug and needs its own issue.