Burst-Proofing a SQLite Writer: Measuring Spill-to-Disk Overflow

Introduction

When I load-tested the writer from my observability post, I pushed it toward 5,000 writes per second and hit two walls at once: SQLite's database is locked under concurrent writers, and a 4,096-slot queue that fills in under a second and answers overflow with dropped.inc().

database is locked turned out to be a concurrency symptom, not a throughput ceiling — one writer with busy_timeout and batched commits removes in-process contention. And SQLite wasn't what broke first. The queue was.

New here? Start with Observability for Agents Should Cost Nothing — this post upgrades its writer for bursts.

Keep one writer, batch bigger, and spill to disk instead of dropping.

The tax nobody notices

The instinctive fix is more writers. That is exactly what produces the lock error:

SQLite serializes writes: extra writers add contention, not throughput — with the default busy_timeout=0, the loser errors instead of waiting. The quieter failure is the queue: at 5,000 events/sec against 4,096 slots, $t_{fill} = 4096/5000 \approx 0.8s$ with no consumer draining. Under a second of burst fills it, and the writer drops by policy before SQLite sweats. Two problems, opposite directions: the queue needs overflow space, the database needs fewer writers.

One rule: spill, don't drop

Last post's invariant was that tracing may lose telemetry but never slow the agent. For bursts this becomes:

Overflow goes to disk, not to the floor — for events that reach the spill. The agent waits only on spill fsync during overflow.

Queue full → append to a JSONL spill file (fsync'd, 16MB rotate) → explicit Replay recovers it later:

Loss window shrinks from "everything past 4,096" to explicit cases: in-memory events still die with the process; spilled events survive iff their fsync completed and their segment is replayed before any Close. Power loss under synchronous=NORMAL can additionally lose recently committed SQLite rows — a risk to the hot DB's own transactions, distinct from spill survival. An intact, fsynced spill covers that SQLite gap only while it exists: Close deletes un-replayed segments, spill-limit overflow drops with a counter, and a dead spill disk drops too. The spill is a backstop with its own failure modes, not a universal one.

The shape of the code

All snippets are the real TSE implementation (Go), trimmed with ... only for length.

// tse/queue/queue.go — Enqueue: 100ms block, then spill, then drop.
func (q *IngestQueue) Enqueue(ctx context.Context, e *storage.Event) error {
	select {
	case q.ch <- e:
		return nil
	case <-time.After(q.timeout): // DefaultEnqueueTimeout = 100ms
		if q.spill != nil {
			if err := q.spill.Write(e); err != nil {
				q.dropped.Add(1) // spill full or unwritable
				return ErrEventDropped
			}
			return nil // spilled, not dropped
		}
		q.dropped.Add(1)
		return ErrEventDropped
	case <-ctx.Done():
		return ctx.Err()
	}
}

// tse/queue/queue.go — DiskSpill.Write: fsync per write, 16MB rotate.
func (s *DiskSpill) Write(e *storage.Event) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	data, err := json.Marshal(e)
	...
	if _, err := s.cur.Write(data); err != nil { return err }
	if err := s.cur.Sync(); err != nil { return err } // per-write fsync
	...
}

// tse/queue/queue.go — Replay is explicit, operator-called, NOT automatic.
// Nothing in Dequeue() pumps the spill; no production caller auto-replays.
func (s *DiskSpill) Replay(ctx context.Context, inject func(*storage.Event) error) error {
	...
	for _, seg := range s.segments {
		if err := s.replaySegment(ctx, seg, inject); err != nil { return err }
		os.Remove(seg) // only after the whole segment injects clean
	}
	...
}

// tse/batch/writer.go — flush: 5 in-memory attempts, then DLQ counter.
if err := sink(ctx, batch); err != nil {
	...
	if n >= maxFlushAttempts { // maxFlushAttempts = 5
		metrics.Global.EventsDropped.Add(uint64(len(batch)))
	} else {
		w.batch = append(batch, w.batch...) // re-queue to front
	}
}

// tse/sqlite/hot_store.go — one Tx, idempotent re-insert.
tx, err := s.db.BeginTx(ctx, nil)
defer tx.Rollback()
... // chunked multi-row INSERTs, one Tx
// INSERT ... ON CONFLICT(id) DO NOTHING  -> replayed rows absorbed
return tx.Commit()

There is no spill.ack.

os.Remove(seg) runs after replaySegment returns clean, but clean only means every inject callback returned nil. If inject merely re-enqueues into the in-memory channel (q.ch <- e), the segment is deleted while events are still queued, not committed — a crash there loses them. Verified in queue.go:224-241: no production caller passes a commit-backed inject today (the only caller in-tree-adjacent is TestDiskSpill_Replay, which counts, not commits).

So the crash-safe invariant does NOT hold for a queue-reinjecting callback — it holds only if inject synchronously commits to SQLite (or a later stage) before returning. Until an inject callback with that property exists and is wired in, replay is durable-recovery tooling, not a proven crash-safe guarantee. Duplicates-on-retry still hold via ON CONFLICT(id) DO NOTHING; loss-after-inject-then-crash is the open hole. Queue+spill order is approximate, not FIFO.

1. One writer, correct pragmas. One thread owns the write connection; readers use short-lived connections, never held open (a slow dashboard blocks WAL checkpointing). WAL lets reads proceed during writes; busy_timeout waits up to 5s — it reduces contention, it does not make locking impossible.

2. Bounded deadline batches. Max 5,000 rows or 50ms — the max bounds transaction size, the deadline bounds wait. At 5,000 rows/sec sustained, the 50ms deadline fires first (~250 rows → ~20 commits/sec) regardless of cap — 256, 1,000, or 5,000. The cap matters only when a burst fills it before the deadline fires. The 0.8s fill is the no-drain case; with a consumer, fill rate is incoming minus outgoing.

3. Spill, not drop. Appends to a different file never contend with SQLite. Replay order is approximate; the guarantee is survival, not FIFO.

What the numbers say

Reproducible via cmd/burst-spill-check in the repo (github.com/yanmyoaung2004/tse): 20,000 events, 4,096-slot queue, 8 producers, no consumer during offer, then drain+commit in 1,000-row batches. CGO_ENABLED=0 go run ./cmd/burst-spill-check, one run, ~180 B/ev. The harness prints every row of the table below plus [INTEGRITY] unique/dups; count committed rows via hot.Query and unique IDs via the seen map exactly as the tool does:

The bottleneck leads, not the throughput: 88 ev/s at 103ms p50 is the no-consumer overflow regime only — not normal drained operation — while this test's write path committed 7–9k ev/s batched. In this run the overflow ladder, not SQLite, set the pace (8 producers serialize under one spill mutex); that does not prove SQLite cannot bottleneck other workloads. Never waits on SQLite, always waits on the spill.

Recovery scope: this proves spill replay (20,000/20,000, 0 dups) — not crash recovery. No SIGKILL this run. Crash cover today is ON CONFLICT DO NOTHING + delete-after-clean-inject + passing TestDiskSpill_Replay / TestIngestQueue_SpillAndDrain. Kill-mid-spill with restart is the next run before stronger claims.

One machine, one run — a data point, not a universal claim. Sustained overload unrun; the spill would grow unbounded there. Guard stays: burst with dropped == 0, hot-count == offered.

Failure moves one way; recovery is manual

Spill pressure waits for explicit Replay; SQLite errors retry 5× in memory, then count to the shared EventsDropped DLQ counter instead of blocking forever (writer.go:flush). Spill-full drops feed the same counter via q.dropped + OnDrop — so drops surface in two counters (spill-overflow, batch-DLQ), not one. Commit-then-crash replays duplicates next Replay, absorbed by ON CONFLICT(id) DO NOTHING — but inject-then-crash-before-commit loses (see above), so the duplicate direction is proven and the loss direction is the open hole.

Recovery procedure, not a shutdown guarantee — verified in source. IngestQueue.Close calls q.spill.Close(), and DiskSpill.Close loops os.Remove(seg) over every segment: closing the queue with un-replayed spill deletes the spill.

No production shutdown handler invokes Replay first (WriterGoroutine.Close, drains only the writer channel). So the lifecycle as written can destroy exactly the data spill exists to preserve — replay must happen before close, by the operator.

The diagram below is the required procedure that does not exist in code yet, not an implemented guarantee:

Replay is explicit (DiskSpill.Replay), run by the operator; the injected callback must commit before returning, and today no caller wires that up. This post's benchmark decoded spill JSONL manually for the same reason.

The honest limit

The central result: SQLite committed at 7–9k ev/s batched — sufficient for the tested rate — while producers managed 88 ev/s at ~100ms per event once the queue filled and spill writes serialized. Storage throughput was never the problem; synchronous overflow spilling was.

Open holes, named: inject-then-crash-before-commit loses (no commit-backed Replay caller exists yet), IngestQueue.Close deletes un-replayed spill, sustained overload grows spill unbounded, and crash recovery is unrun. Trade stands: never drop silently over never touch disk on the agent path.