Summary
On 2026-11-10 agents-stable's database was 46 to 50 GB (12.26 M pages of 4 KiB, freelist 0, WAL 175 MB). It was 36 GB on 2026-11-03, so it was growing about 2 GB a day. The container has a 4 GB memory limit, so almost every read that missed SQLite's small cache was a random read on a volume that saturates near 2k IOPS. The immediate wedge was full-transcript privacy scans on the read path (incident report, tracking issue seed#1335). Other fixes handle that. This note covers the underlying problem: nothing in the database was ever deleted.
The fix is PR seed#1338 (branch fix/agents-db-retention). It does four things:
It caps stored tool output.
It drops the durable stores of idle sessions.
It compacts the journals of long-finished workflow runs.
It sizes SQLite's memory to the container.
The note ends with a runbook for shrinking the existing file during a planned restart.
What grows, and why
Every table that holds blobs, who writes to it, and what removed rows before this change:
Table | Blob | Written by | Removed before this change |
|---|---|---|---|
|
| every turn, user verbs, triggers | only when the session or agent is deleted |
|
| every durable commit, about every 100 ms while streaming | only when the session is deleted (FK cascade) |
|
| workflow runs | never: run history survives session and agent deletion, detached |
|
| every run | never |
|
| effectful tool calls | settled rows after 2 days (hourly prune) |
|
| every trigger firing | only when the trigger or agent is deleted |
|
| client retries | never (small) |
Four ways the same bytes pile up:
The durable store is a second copy of the transcript. Pi Durable's main.jsonl holds every message the model saw, with full tool-result details, plus a terminal record per task. session_events holds the same content for the UI, so a large tool output is stored at least twice. The store also holds the agent's full system prompt (about 35 KB per session on the dev database, 66% of its durable bytes).
Private sessions rebuild their store every turn. A session that used the browser or private memory skips the append path (#importSessionLog sets appendable = false), so each turn writes a pi.reset entry and replays the whole log into the store again. Earlier entries stay. A private session's store therefore grows with the square of its length, for as long as the session is used.
Workflow journals are never cleaned up. The QuickJS replay journal records every call's input and result. That is needed while a run is live; once the run is terminal (terminal status is sticky in runs.ts), only the run inspector reads it. Each run is capped at 8 MiB and 5,000 entries, but there is no limit on the number of runs. Each call entry also carries its input twice: the replay key embeds the JSON of the input.
Tool outputs are stored whole. The model never reads more than 8 KiB of a result (boundModelToolResultText), but the event stored the complete output: whole web pages, read of large documents, exec output, MCP results up to 256 KiB.
Evidence
The dev database on Yacht (4.8 GB, dbstat):
Table | Size |
|---|---|
| 4,333 MB (90%) |
| 157 MB |
| 75 MB |
| 27 MB |
The journal is 123k entries over 3,204 workflow runs, all finished more than 30 days ago. 2.5 GB of it is result entries (mostly read outputs) and 1.7 GB is call write inputs. The median journal is 1.4 MB and the largest are at the 8 MiB cap.
On a copy, one offline retention pass took 3 minutes and freed 87% of pages. VACUUM INTO then produced a 606 MB file from the 4.8 GB one, in 3 s on SSD.
Production's split is still unknown. Pages sampled from disk during the incident were overflow pages of tool results (web pages, HTML, arXiv XML), durable session files, uploaded TSVs and research markdown. That suggests session_events and session_durable_files matter more there than on dev. The sizing script below answers this cheaply; run it before the shrink.
Retention policy (implemented)
Everything is in agents/src/db-retention.ts. All settings are environment variables with safe defaults, and report mode logs what would be removed without removing it.
Variable | Default | Effect |
|---|---|---|
|
|
|
|
| after this much idle time, drop the session's durable store ( |
|
| this long after a run finishes, compact its journal's large entries ( |
|
| disk budget for retention work |
|
| larger stores are left for the offline pass |
|
| time between passes; the first runs 15 min after boot |
|
| longest stored tool-output string, in thousands of characters ( |
Stored tool-output cap. When a tool_result event is appended, and when the durable store records a tool's details, any string longer than the cap keeps its head and gains … [truncated for storage: N chars in total]. The model reads the same result as before. Workflow scripts still receive whole results, because ctx.call returns details directly and is not affected. New writes only; existing rows are not rewritten.
Durable stores of idle sessions. A session qualifies when it has been idle (sessions.updated_at, which every event moves) for at least 14 days. Its store is skipped while a turn in this process holds it open or the session has a queued, claimed, running or waiting run. Otherwise all of the session's session_durable_files rows are deleted in one synchronous transaction. The check and the delete happen in the same tick, so no turn can open the store in between. A partly deleted store would fail to open (sidecar records out of order), while a missing one is rebuilt from the log on the next turn. That is the existing lost-store path, and there is a regression test for it. What is lost: provider replay state (reasoning items, prompt-cache affinity) for a conversation idle for two weeks. Stores over 32 MiB are counted as deferred and left for the offline pass, since deleting one blocks the event loop while SQLite walks its overflow chain.
Journals of finished runs. For runs that finished at least 30 days ago, each journal entry larger than 16 KiB is rewritten in its own statement:
Long strings become 1 KiB previews.
If the entry is still too large, input/output/delivery/data/match become {compacted: true, bytes, preview}.
As a last resort only the entry's identity stays: kind, callSeq, key, status, op, tool, ids and error.
Every compacted entry is marked compacted: {at, bytes}. The UPDATE re-checks that the run is still terminal.
Mechanics.
Candidates are found one day of timestamps at a time through existing indexes (skip-scans of sessions_by_agent and runs_by_account), so no query is unbounded.
Each policy keeps a watermark in server_config (retention.durable.watermark, retention.journal.watermark), so a pass only looks at what aged since the previous one. A session or run that had to be left alone holds the watermark back, so a later pass returns to it.
Work is throttled by bytes touched and yields the event loop between steps.
Every day of data touched logs a [agents/retention] line, and each pass logs a summary.
Shutdown stops a pass at its next step.
Deliberately not automated
These need a decision from Eric; none of them runs on its own:
Deleting session_events. The transcript is user-visible and is the canonical record. No class of event is removed.
Re-capping existing large tool outputs in session_events. The cap applies to new rows only. A one-off rewrite of old tool_result outputs over 128K characters would probably reclaim a lot on production, but it changes content people can open in the inspector. Measure first with the sizing script (maxEventInSample, estEvents), then decide.
Deleting runs or journals outright. Journals are compacted, never deleted, so the run inspector keeps every entry. Detached runs of deleted sessions and agents, which survive deletion by design, are kept too.
trigger_firings and action_idempotency retention. These are small on dev, and firings are shown as trigger history.
Dropping a private session's store on every turn. That would remove the quadratic growth at its source, but it touches crash recovery, the answered-submission check and parked delegations, so it needs its own change and tests (see open questions).
VACUUM. It is never run automatically. It needs free space the size of the live data and blocks writes for its whole duration.
SQLite settings and the memory budget
Before this change: cache_size 64 MB, mmap_size 512 MB requested, auto_vacuum NONE, no journal_size_limit (the WAL stays at its peak size, 175 MB), synchronous left at the library default.
Now set from the environment, with the values in effect logged at startup ([agents/sqlite] connection settings):
Pragma | New default | Variable | Why |
|---|---|---|---|
| 256 MiB |
| private heap, counted against the 4 GB limit; keeps index and interior pages hot |
| 1 GiB |
| file-backed and reclaimable, so not private memory; SQLite caps it at its compiled maximum (1 GiB in the macOS build checked; the Linux build's value is in the startup log and the sizing script) |
| 64 MiB |
| truncates the WAL after checkpoints |
| NORMAL | none | standard for WAL; survives process crashes, and only power loss can roll back the newest commits; one less fsync per commit |
Memory-limit risk. cache_size is the only setting that adds unreclaimable memory. 256 MiB fits next to a Bun heap of about 1 to 1.5 GB and exec sandboxes, whose microVM guests are charged to the same cgroup at 512 MiB each. Raising it to 512 MiB to 1 GiB is reasonable only after checking the container's RSS under load (docker stats). mmap and the kernel page cache are reclaimed under pressure, so they cannot cause an OOM kill, though they compete with sandboxes for the same 4 GB.
Size target. The read path stays sub-millisecond only if the hot set fits in SQLite's cache plus page cache. With about 1.5 GB of the container left for page cache after the heap and sandboxes, the target is:
Hot set at most about 1 GB. That is every index b-tree, the non-blob tables (sessions, agents, runs leaves, triggers), and the last few days of session_events, session_durable_files and run_journal.
File at most about 8 GB after the shrink, holding steady under retention.
The file size matters less than what is read. Once retention has run, dead blobs are not on any read path even before a vacuum. The shrink returns the disk space and defragments the file.
Measuring production safely
agents/scripts/db-size-report.ts is read-only and never scans a table or reads blob content:
header pragmas and file sizes, including compile options (mmap maximum, dbstat)
sqlite_stat1 row counts
every session and run id, read from small covering indexes
for 200 sampled sessions and runs, COUNT/SUM(LENGTH(blob)), which SQLite answers from record headers without loading overflow pages
It reports estimated durable-store and transcript bytes for sessions idle more than 14 days versus active ones, and journal bytes for runs finished more than 30 days ago versus recent ones. That is a few thousand random page reads, about 4 s on the dev database.
scp agents/scripts/db-size-report.ts agent.hm:/tmp/
ssh agent.hm 'docker cp /tmp/db-size-report.ts agents-stable:/tmp/ &&
docker exec agents-stable timeout 120 nice -n 19 bun /tmp/db-size-report.ts /data/agents.sqlite'Diagnostics rule. Put timeout inside the container (docker exec <c> timeout 30 bun …). A timeout on the SSH side does not kill the docker exec child, which is how a diagnostic query kept loading the disk during the incident.
Do not run dbstat, SUM(LENGTH(...)) over whole tables, COUNT(*) on the blob tables, or ANALYZE on session_events/run_journal against the live database. Each one reads tens of gigabytes, takes many minutes and saturates the disk. Offline, on the stopped database or a copy, they are fine.
Runbook: one-off shrink during a planned restart
Deleting rows frees pages for reuse but never shrinks the file (auto_vacuum is NONE). Retention alone stops the growth; reclaiming the 50 GB needs a copy.
Space. VACUUM INTO writes only live pages, so the new file needs free space equal to the live data. That is the whole 50 GB if no retention has run, and much less after it. The original stays until you delete it. The docker volume (sdb) is 250 GB with 155 GB free on 2026-11-10, but it has been 90 to 98% full before. Check df -h first. Never run an in-place VACUUM: it needs about twice the space (temporary copy plus WAL) and blocks everything for its whole run.
Writes are blocked. The service is down for the whole window. Plan for it, announce it, and do not try this on a live server.
Steps:
Before the window. Deploy the PR. Optionally set SEED_AGENTS_RETENTION=report for one pass to read what it would remove (docker logs agents-stable 2>&1 | grep agents/retention), then set it to on. Run the sizing script and note page_count, freelist and the per-table estimates.
Build the offline pass. On a workstation: bun build agents/scripts/db-retention.ts --target=bun --outfile=/tmp/db-retention.js, then scp it to the host. Find the volume's host path with docker inspect agents-stable --format '{{json .Mounts}}' and copy the file into that directory, for example <volume>/maint/.
Stop the service. Run docker stop agents-stable (it drains in about 10 s) and stop Watchtower so it cannot restart the container mid-window.
Retention, unthrottled. In a throwaway container on the same volume and image: docker run --rm -v <volume>:/data --entrypoint timeout <agents image> 3h bun /data/maint/db-retention.js /data/agents.sqlite This is one pass with no size limit on stores. It finishes with a WAL checkpoint. Add --report first for a preview. Expect it to be disk-bound. On SSD, 4.4 GB of journals took 3 min. On sdb, budget about 1 minute per 0.5 to 1 GB rewritten or deleted (random reads of overflow chains), so 30 to 90 minutes if production holds 20 to 40 GB of removable data. It can be interrupted (Ctrl-C or timeout) and resumed, because each step commits on its own and watermarks persist.
Copy the live pages. docker run --rm -v <volume>:/data --entrypoint timeout <agents image> 3h bun -e "const {Database}=require('bun:sqlite');new Database('/data/agents.sqlite').run(\"VACUUM INTO '/data/agents.sqlite.new'\")" It reads every live page in b-tree order and writes the new file sequentially. Expect 5 to 20 minutes for 5 to 10 GB of live data on sdb (worst case is random reads at about 8 MB/s).
Check. Run PRAGMA quick_check on agents.sqlite.new under timeout in the same kind of container. It reads the whole new file, a few minutes.
Swap. mv agents.sqlite agents.sqlite.old, and move agents.sqlite-wal and agents.sqlite-shm (if present) with it. An old WAL next to the new file would be replayed into it and corrupt it. Then mv agents.sqlite.new agents.sqlite.
Start. Start the container and Watchtower. Check /api/health, the startup line [agents/sqlite] connection settings (pageCount, freelistCount, mmapSize), and that a session from before the window opens and answers. Its first turn rebuilds the durable store if retention dropped it.
Clean up. Keep agents.sqlite.old until the server has been healthy for a day, then delete it to free the disk.
Rollback. Stop the container, move agents.sqlite.old (and its -wal/-shm) back, and start.
Without a window. Leaving retention running for a few days stops the growth and takes dead blobs off the read path. The file keeps its size; freed pages are reused for new writes.
Open questions and follow-ups
Production's split. Is production's bulk journals, as on dev, or transcripts and durable stores? Run the sizing script. If session_events dominates, decide on the one-off re-cap of old tool outputs above.
Private sessions. Stop rebuilding their store every turn, or drop the dead history behind each pi.reset. Their stores grow with the square of their length, and an active one is never idle long enough for retention. This is probably the main durable-store contributor on production, where ion uses private memory.
Journal keys. Journal call entries embed the full input JSON in key. A content hash would halve journal size without changing replay semantics, but it needs a compatibility path for live journals.
Table layout. session_events and run_journal are WITHOUT ROWID tables with large blobs. Each row keeps about 1 KB of its blob inline in a leaf, so few rows fit per page and range reads touch many pages. A rowid table clustered by (session_id, seq), with the blob in a side table, would make transcript reads cheaper. That is a schema migration and needs its own plan.
auto_vacuum = INCREMENTAL. Setting it on the new file during the shrink would let the server return space to the disk later. It needs a second full VACUUM after the pragma, so it is not part of this runbook.
Cache size. Raise SEED_AGENTS_SQLITE_CACHE_MIB once RSS under load is known.
Do you like what you are reading? Subscribe to receive updates.
Unsubscribe anytime