[Bug]: Server freezes ~80 s per thread open after a long uptime: planner stats loaded at startup are never refreshed #19916

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

1. Start t3 serve while orchestration_v2_projection_nodes is nearly empty (a new install, for example). layerSetup runs PRAGMA optimize = 0x10012, which analyzes the small table.
2. Keep the server running while the node table grows. Ours reached 19k rows in about 21 hours of delegated-task work.
3. Open a thread with a large retained node cohort. The bounded snapshot's recursive ancestor walk (WITH RECURSIVE retained … in ProjectionStore.getThreadSnapshotWindow) now scans the node table on every step.

The planner behavior reproduces deterministically outside T3 (Node 26.8.1, SQLite 3.53.4):

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec(`
  CREATE TABLE nodes (node_id TEXT PRIMARY KEY, thread_id TEXT NOT NULL, parent_node_id TEXT);
  CREATE INDEX nodes_thread_idx ON nodes(thread_id);
`);
const insert = db.prepare("INSERT INTO nodes VALUES (?, 't', ?)");
for (let i = 0; i < 3; i++) insert.run(`n${i}`, null);
// What layerSetup runs when the server starts, here on a near-empty table.
db.exec("PRAGMA optimize = 0x10012");
// The table grows while the server stays up: 20k nodes, the newest 1,000 in one chain.
for (let i = 3; i < 20_000; i++) insert.run(`n${i}`, i > 19_000 ? `n${i - 1}` : null);

const walk = `
  WITH RECURSIVE retained(node_id) AS (
    SELECT 'n19999'
    UNION
    SELECT parent.parent_node_id FROM nodes AS parent
    INNER JOIN retained ON parent.node_id = retained.node_id
    WHERE parent.thread_id = 't' AND parent.parent_node_id IS NOT NULL
  )
  SELECT count(*) AS walked FROM retained`;
const run = (label) => {
  const plan = db.prepare(`EXPLAIN QUERY PLAN ${walk}`).all().map((row) => row.detail);
  const step = plan.slice(plan.indexOf("RECURSIVE STEP") + 1, plan.indexOf("RECURSIVE STEP") + 3);
  const start = performance.now();
  const { walked } = db.prepare(walk).get();
  console.log(`${label}: walked ${walked} nodes in ${Math.round(performance.now() - start)} ms | ${step.join(" ; ")}`);
};
run("stats from startup");
db.exec("PRAGMA optimize");
run("after PRAGMA optimize");

stats from startup: walked 1000 nodes in 469 ms | SCAN parent ; SCAN retained
after PRAGMA optimize: walked 1000 nodes in 1 ms | SCAN retained ; SEARCH parent USING INDEX sqlite_autoindex_nodes_1 (node_id=?)

Real node rows are a few KB each, so on the affected server each step of the walk read the whole 64 MB table.

Expected behavior

Query plans track table growth on a long-running server, and opening a large thread takes milliseconds. SQLite's guidance for long-lived connections is to run PRAGMA optimize periodically ("perhaps once per day or once per hour"), not only when the connection opens.

Actual behavior

On a server up about 21 hours, opening one large thread blocked the event loop for 77–80 s. Nothing in the process completed during the freeze, so every connected client (iOS and Linux desktop) dropped with "Failed to connect. Retrying…". Clients that still had the thread open reopened it on reconnect, so the freeze repeated every few minutes.

The sqlite_stat1 rows in the database file were current (18,443 nodes), because t3 CLI commands open the database and run the same pragma. But a SQLite connection loads stats only when it opens, so the server's connection kept the row counts it started with. That is why a fresh connection could not reproduce the slow plan, and why a restart cleared it: after restarting, the same snapshot loaded in 28–33 ms.

Impact

Major degradation or frequent failure

Version or commit

0.0.46-nightly.20261014.3105. The same code is on main at 42da166.

Environment

Ubuntu 24.04, headless t3 serve as a systemd user service, 12 vCPU / 62 GB. The statev2.sqlite was 1.4 GB, with 19k nodes and 31k turn items. Clients were the iOS app and the Linux desktop app.

Logs or stack traces

During a freeze, the server's main thread was in state R:

• perf record of that thread showed kernel _copy_to_iter/filemap_read, plus readDbPage, sqlite3BtreeNext and getAndInitPage in the t3 binary.
• strace -e pread64 for 4 s showed 88,206 page reads, all but one in orchestration_v2_projection_nodes. That is the whole 64 MB table, read about 5 times.

On a copy of the database with the stale stats loaded:

• The recursive step planned as a per-step scan, and the walk took 15.7 s.
• With current stats it planned as SEARCH parent USING INDEX sqlite_autoindex_orchestration_v2_projection_nodes_1 (node_id=?) and took 4 ms, returning the same 651 rows.

Screenshots, recordings, or supporting files

No response

Related: #17765 (snapshot handlers have no server-side timeout, and the single SQLite permit lets one slow snapshot hold up others).