Agents Database: Postgres or SQLite
Decision document for Eric. Whether the agents server should move from bun:sqlite to Postgres, what synchronous database calls on the serving thread actually cost, the three ways out, and a recommendation with the questions that decide it.

The question

The agents server does every database call synchronously on the one JavaScript thread that also serves HTTP and WebSockets and executes runs. Eric's instinct: that is crazy, and Postgres would fix it. This document separates the two claims, because they have different answers.

What today's outage actually showed

On 2026-11-03 agents-stable went dark for minutes while still doing work. A CPU profile put ~70% of main-thread samples inside ListSessions, ~48% in one SQL statement: the busy subquery in agentActivityColumns (agents/src/api-service.ts) was pinned with INDEXED BY runs_dispatch, an index with no agent column, so every non-busy agent re-read every live run row. On the 36 GB production file that cost 120–200 ms per call, 1.5 ms without the column, 0.2 ms with a covering runs(agent_id, status) index. The desktop refetches every cached session list on each session-created, and triggers were starting 137 sessions per 10 minutes, so the storm never drained.

The 2026-09-09 episode had the same shape with a different trigger: Database.query() recompiling statements on every call, 37% of main-thread samples (see the header of agents/src/statements.ts).

What Postgres would and would not have changed today:

Part of the failure

With Postgres and an async driver

Bad plan makes one statement cost 120 ms

Unchanged. A pinned or missing index is a bad plan in both engines. Postgres's planner is better and would likely never have needed the INDEXED BY hint, but the hint-free SQLite plan was a full scan at 39 s, and the real fix is the index either way.

The statement blocks the event loop while it runs

Fixed. The loop awaits a socket; health and other actions interleave.

Hundreds of refetches queued behind each other

Moved, not fixed. The same storm would saturate the connection pool, and each client still waits 120 ms × queue depth. Health would answer, so it would look like slowness instead of an outage.

Run execution (CBOR, Pi loop, durable commits) on the same thread

Unchanged. The worker-isolation plan exists because of this part.

So Postgres turns "server is dark" into "server is slow" for this class, which is worth something, but the query budget and fan-out problems need their own fixes regardless of engine.

Why synchronous calls on the serving thread are a real problem anyway

bun:sqlite is synchronous by design. Every .all(), .get() and .run() runs SQLite's page traversal on the calling thread. With a 36 GB file and a 64 MB page cache on an 8 GB host, a cold statement is many pread calls on the event loop. Any single statement over a few milliseconds, multiplied by concurrency, starves accept(). There is no way to put a timeout on a statement, and no way for an unrelated request to proceed while one runs.

Three ways out:

    Async engine (Postgres). The thread never blocks on the database. CPU-bound JS still blocks.

    SQLite off the thread. Either a Worker with its own bun:sqlite connection behind a message bridge, or the api/runner process split in the responsiveness plan, where the API process's own statements are small and bounded and runs live elsewhere. Note that Bun's own Bun.SQL sqlite:// adapter is not an escape hatch: it has an async interface but blocks the loop (measured locally on Bun 1.3.10: a 462 ms query let zero 10 ms timer ticks fire).

    Keep sync, make it cheap and bounded. Per-statement budgets enforced in CI on a production-shaped fixture, hot data in RAM via archival, slow-statement logging. This is how the server has been operated so far, minus the enforcement.

Option 3 alone has failed three times since September. Options 1 and 2 both need option 3's budget discipline to avoid becoming slow instead of dark.

Postgres: what we gain and what it costs

Gains. True concurrency and an async driver (Bun.SQL speaks Postgres natively, no dependency). A planner with real statistics, so INDEXED BY hints and ANALYZE on open go away. VACUUM, ANALYZE and index builds online. statement_timeout to kill a runaway query instead of the server. pg_stat_statements to answer "which query is slow" in one line. Backups and replicas are a solved problem. Several API processes and runner processes share one database without WAL and lock-file rules.

Costs.

    A second stateful service on agentic, or a managed instance: provisioning, upgrades, backups, connection limits, monitoring. Today the whole server is one Bun process and one file.

    Migration of 24 tables and every statement. Counted in agents/src (non-test): 162 stmt( sites, 16 db.transaction( sites, 17 RETURNING, 16 ON CONFLICT, 9 INSERT OR IGNORE, 5 INDEXED BY, 5 PRAGMA, 11 files importing bun:sqlite. Parameter style changes from ? to $1. Transactions are synchronous closures today (db.transaction(() => {...})), which become await sql.begin(...) with every caller turned async: the 14,700-line Service class gets a large async-coloring refactor.

    The 36 GB data migration (1.5 M session_events, 1.26 M session_durable_files rows) with write downtime, or a dual-write period.

    The desktop. The desktop app spawns the same server as a compiled Bun binary (agents/scripts/build-binary.ts, frontend/apps/desktop/src/agents-server-process.ts) with its data under the user's app directory. Postgres cannot ship inside that. The options are PGlite (Postgres compiled to WASM: single connection, in-process, its own persistence story, a different performance profile) or keeping SQLite on desktop behind a storage interface and maintaining two backends. Both are ongoing cost, not one-time.

SQLite kept, done right

WAL mode with several processes is a supported, well-trodden configuration; the lease queue in runs (lease_owner, lease_expires_at) was built for multiple claimants. The responsiveness plan's api/runner split gives the API process a thread that only ever runs short statements, and busy_timeout plus short write transactions keep the single writer from being a problem at our write rates (hundreds of writes per minute, not thousands per second). Archival of finished sessions' events brings the hot file under RAM so cold reads stop touching disk. CI budgets and a slow-statement log keep the next bad plan out of production.

What it cannot do: one writer at a time across all processes; no statement_timeout; a statement that is slow still blocks whichever process runs it; no server-side pooling or replicas; and the Bun API stays synchronous, so run execution and API serving must be separate processes or threads to get isolation.

A middle path: a storage interface now

Introduce a storage layer in agents/src with the SQLite implementation as its only backend, so a Postgres (server) or PGlite (desktop) backend can be added later without touching Service. Cost: the 162 statement sites become methods on that layer, roughly two person-weeks of mechanical work with high test coverage already in place, and it is the same refactor the Postgres path needs as its first step. Benefit: it makes the decision reversible and lets the server and desktop diverge if that is ever wanted. Risk: an abstraction nobody uses for a year is maintenance with no payoff, and leaky abstractions over two SQL dialects have a poor track record; it only pays if a second backend is actually planned.

Effort and risk

Option

Effort

Risk

Fixes from the plan

Hotfix + budgets + lag SLO + push-not-refetch (P2, P3, P4)

1–2 weeks

Low

Today's class, and detection of the next

api/runner process split on SQLite (P1)

2–3 weeks

Medium: singleton placement, emit forwarding, sandbox caps per runner

Isolation of API from runs and from any slow statement in a runner

Archival, VACUUM, CPU limits, own box (P5, P6)

1–2 weeks plus a maintenance window

Low

Hot set in RAM, host contention

Storage interface, SQLite only

2 weeks

Low–medium

Nothing by itself; enables the next two rows

Postgres on the server, SQLite on desktop

6–10 weeks after the interface

High: dual backends, data migration, new ops surface

Loop blocking on the database (not on JS); planner, tooling, timeouts

Postgres on the server, PGlite on desktop

8–12 weeks after the interface

High: PGlite maturity and performance on laptops

Same, with one dialect

Recommendation

Do not adopt Postgres now. Do the first three rows in order, which the responsiveness plan already sequences, because they fix the whole failure class including the part Postgres leaves untouched (CPU-bound JS on the serving thread). Then measure for a month with the lag gauge in place. If after the split the API process still shows statement-bound stalls, or operational needs appear that SQLite cannot meet (replicas, several servers sharing one database, per-statement timeouts), build the storage interface and move the server to Postgres, keeping SQLite on desktop.

The reasoning: the problem Eric is reacting to is "the server goes dark", and the shortest path to never-dark is isolating the API from everything heavy and bounding what it runs. Postgres is a bigger project that solves one of the three contributing causes and introduces a desktop problem we do not have today. Its gains are real, and the storage interface keeps the door open at the moment it is actually needed.

Questions that decide it:

    Is the desktop-embedded agents server a must-keep? If yes, Postgres means two backends or PGlite, and the cost table above stands. If the desktop could talk only to hosted servers, Postgres gets much cheaper.

    Is a managed Postgres acceptable for production (cost, data location), or must it run on agentic next to the sandboxes on a 4 vCPU / 8 GB box?

    How soon must thread-blocking on the database be gone? Days: the hotfix and the split. Quarters: the split plus Postgres behind an interface.

Do you like what you are reading? Subscribe to receive updates.

Unsubscribe anytime

Connections0
Linked document · Outgoing linkLinked documentLinked document · Outgoing linkLinked documentLinked document · Outgoing linkLinked documentAgents Database: Postgres or SQLiteAgents Database: Postgres…
Citations 0Children & links 3

Loading child documents…

Connections show where other documents or comments link to this document.

Related documents

Loading connections…