Commit streamed provider updates in a short window: ~41% fewer SQLite commits on live traffic (follow-up to #24003)

[kvnloo]

Note

Drafted by Claude Opus 5.5 in Hermes (via T3 Code) from our own runs; posted at @kvnloo's request. Measured Nov 2–3 2026 on one Linux install. The code, harness and estimator script are linked below.

TL;DR

#24003 tracks the part of #19105 that is still open: the server commits once per event, and each commit rewrites about 29 pages. Its first suggested fix is to group a streamed turn's events into one commit. #24145 tried batching queued snapshots and was closed under the one-problem rule. In review, @simasrazinskas found that queued-snapshot batching rarely applies to live traffic, because commits arrive one event at a time (4/s across 3 threads). He suggested grouping commits across threads in EventSink instead.

That's what we tried, with a time window as the trigger. Plain node.updated, message.updated, turn_item.updated and plan.updated writes wait up to 25 ms and then commit together. Any other write commits the queue first.

• Simulated turns (A/B, 8 interleaved slots per arm): commits per turn 51.5 → 29.5 (−43%). SQLite bytes written per turn 7.79 → 6.17 MiB (−21%). Reconnect replay wire bytes are identical, and we detected no latency regression (intervals are wide; see below).
• Live traffic (replay estimate): we replayed the last 2 hours of a real install's event log (9,292 events across 21 threads, mostly Hermes ACP with delegated subagents) through the same window rule. Result: 41% fewer commits at 25 ms, 34% at 10 ms, 46% at 50 ms. About 55% of consecutive events arrive within 25 ms of each other (median gap 17 ms), so a window coalesces live streaming even when no backlog builds up.
• We'd like to know whether you want this direction, and whether opt-in or default, before opening a focused PR.

1. Why one commit per event costs so much (simulated turn)

We counted write syscalls and parsed WAL frame headers with an LD_PRELOAD shim (bench/poh/iotap.c). The counts cover the server process only, so they exclude the reaped-child I/O that the #24003 correction warns about. Turns are driven by the repo's devReplay ACP agent through the real adapter, orchestration, projections and checkpoints. Medians are over 10 turns, on a 618.8 MiB compacted (t3 doctor) copy of a real database (4 KiB pages, before #23211):

	per turn
commits	52
WAL frames	1,524 (29 per commit, 297 distinct pages, each rewritten ~5×)
SQLite bytes written	7.1 MiB, of which 83% is WAL frames

This matches #24003 (29 pages per commit) and @bompus's numbers in #6897 (~106 KB of WAL per ~1 KB event). Tuning the checkpoint doesn't help, because the cost is paid on every commit.

2. The change

Fork branch exp/poh/04-commit-batching, based on main 5b9731c (2026-11-03, before #23211). The feature is one commit: +597 lines across 6 files, 392 of them tests. It is behind T3CODE_EXPERIMENTAL_COMMIT_WINDOW_MS, off by default.

How it works:

• A single EventSink queue holds the plain node, message, turn-item and plan updates from every run and thread. The queue commits as one transaction at most N ms after its first entry.
• Any other EventSink write commits the queue first, so commit order and sequence order stay arrival order.
• Publication follows the commit, so clients never see an event that wasn't stored.
• An event whose normalization compares against a pending entity commits that entity first.
• Graceful shutdown flushes the queue.

Failure modes, which we want your view on:

• Hard crash: up to one window of events that were never published is lost.
• Batch write fails (disk full, or a lock timeout that outlasts 2 retries): the error is logged, the window's events are dropped, and the thread's next deferred write fails with the cause. Today the single write fails at its call site.

3. A/B on simulated turns

Every arm runs at the same measurement code: pilot slots 1–3 at fc7cfbe, slots 4–8 at be02c34. The diff between those two SHAs touches only the pooling script. Each slot is one boot with 2 tapped turns. Arms are interleaved in the order A B C / C B A. Δ is the shift in median vs off, with a bootstrap 95% CI.

metric (n = 16 turns per arm)	off p50 / p95	25 ms p50 / p95	Δ p50 [95% CI]
SQLite bytes per turn	7.79 / 8.99 MiB	6.17 / 6.65 MiB	−1.62 MiB [−2.90, −0.49]
commits per turn	51.5	29.5	−22 [−23, −21]
WAL frames per turn	1,580	1,207	−372 [−461, −236]
first live event	68.5 / 398 ms	50.5 / 158 ms	−18 ms [−67, +33]
first assistant delta	802 / 1,280 ms	716 / 973 ms	−86 ms [−222, +145]
event lag p50	17 / 90 ms	32.5 / 51.5 ms	+15.5 ms [−14, +20]
assistant delta gap p50	427 / 492 ms	424 / 432 ms	−3.5 ms [−15, +1.5]
turn wall time	5,710 / 7,260 ms	5,650 / 6,460 ms	−64 ms [−345, +338]
server CPU per turn	1.10 / 1.81 s	0.84 / 1.58 s	−0.27 s [−0.67, +0.45]
peak RSS	534 / 570 MiB	558 / 589 MiB	+24 MiB [−5, +41]
reconnect replay wire bytes	110 KiB	110 KiB	0

How to read it:

• Event lag is the only latency metric with a tight interval. The others are wide and rule out only large shifts.
• The window merges WebSocket frames: 3 fewer per turn, and the frame gap grows by 27.5 ms. Assistant text streams at the same pace.
• A 150 ms pilot arm (2 slots, 4 turns) saved no more bytes and added +150 ms [+118, +173] of event lag. The window has to stay short.

4. Live-traffic estimate

Read-only estimator (Python, standard library)

commit_window_estimate.py replays orchestration_events in sequence order using the ingestion timestamps (occurred_at), and counts commits under the window rule above. Today's baseline is one commit per event.

python3 commit_window_estimate.py ~/.t3/userdata/statev2.sqlite 2

It opens the database with mode=ro. A copy made with VACUUM INTO works too.

Our install, 2026-11-03 03:30–05:28 UTC, 9,292 events, no rows removed by compaction yet in that range:

window	commits	fewer than today
10 ms	6,109	34.3%
25 ms	5,471	41.1%
50 ms	5,051	45.6%

This is an estimate. It assumes one commit per event today and treats ingestion time as commit time. It doesn't model the extra flushes forced when a pending entity is compared against. It would be useful to see the same numbers from the higher-concurrency installs in #24003 (@justinehler's ~30 threads, @simasrazinskas's Claude threads).

Questions

1. Is a short time window for plain provider updates a direction you'd take as a focused perf PR? If yes, opt-in first or default-on at ~25 ms? Precedent: perf(server): batch terminal history writes every 250 ms #17183 batches terminal history every 250 ms and accepts the same abrupt-exit exposure.
2. Are the two failure modes above acceptable? If not, would you rather a failed batch retry each event on its own?
3. Should it land after perf(server): prune events the projections already cover #29806 / perf(server): return freed database pages to the filesystem #29811 so we can re-measure on top of pruning? On this database the event log is 408 of 655 MiB (incl. indexes), and pruning shrinks the tail pages each commit touches, so the per-commit cost may drop.

Caveats

• One install, Linux, under memory pressure. Bytes and commit counts are deterministic. Latency intervals are wide except event lag.
• The A/B used simulated devReplay turns. The live number is a replay estimate, not a measured A/B.
• [Bug]: Server still writes ~125-155x its event data on 0.1.3 nightly (one commit per event); thread.visited still stores the full thread snapshot #24003's macOS data (@VDrozd-Cyclis) shows the per-commit cost explains only part of the disk writes there. This change addresses only the SQLite share.

Background: we started from "could the provider own the transcript, with T3 keeping a cache?" Byte accounting showed that's the small lever. Tool output is 6% of this file and no adapter can re-serve the full transcript today. Meanwhile #29806 handles the event log. The write cost above was what remained. Full write-up with every table and figure: https://kvnloo.github.io/z0evals/t3code-storage-write-amplification/

cc @juliusmarminge @Yash-Singh1 (EventSink and SQLite writer work), @Zheruel (#24145)

[cjgayle1]

Supporting this direction with numbers from a smaller install: T3 Code 0.1.8 nightly, Linux, ext4 on a DRAM-less SATA SSD, 5 to 15 agent threads. Measured per server thread, child processes excluded.

• Server writes: 1 to 4 GB/h. About 65% goes to the WAL, 30% to checkpoints into the main file, and 4% to server.trace.ndjson.
• Commits: about 4 per second, each 30 to 43 WAL pages, mostly index pages of orchestration_events. That is against roughly 20 MB/h of event payload.
• Why commit count matters here: on this drive each commit also goes through the filesystem journal, which is shared with everything else on the disk. Fewer commits help even where bytes written fall less (your 41% fewer commits against 21% fewer bytes).

I'd vote for making it the default rather than opt-in. 25 ms is invisible in use, and the people on slow disks who need it most are the least likely to find a setting. How the freezes track disk load on this machine is in my comment on #30067.