Flush Should Never Make Ingest Wait

Introduction

Durability is useful precisely when the machine dies.

The problem is that durability work can become part of the path it is supposed to protect. If flushing hot data to cold storage holds a lock that ingest needs, or advances a checkpoint before the data is actually safe, then the thing keeping you safe is also the thing slowing you down — and the crash window is exactly where data disappears.

I wanted a different default for TSE's storage engine:

Moving data to cold storage should never make ingest wait, and killing the process mid-flush should never lose a committed row.

New to TSE? Start with What TSE Is: SQLite Hot, Parquet Cold — this post assumes the vocabulary from that overview but recaps what it needs.

That led to a deliberately boring design: a drain-loop flusher, a contiguous watermark in a separate SQLite database, idempotent writes at every stage, and no distributed anything.

No LSM. No message broker. No two-phase commit.

The goal isn't literally zero-cost durability. The goal is to make the flush path cheap enough on ingest and safe enough under kill that there is no good reason to schedule it off-peak or babysit it.

The tax nobody notices

Flushing sounds like pure upside until you put it on the critical path.

A typical naive flush looks roughly like this:

Ingest now depends on the flush.

If the flush is slow, ingest is slow. If the process dies between D and E, rows are copied but the checkpoint says they aren't — or worse, the checkpoint advanced and the rows never made it. Either way the crash window is a correctness bug, not just a delay.

So teams often make flushing conditional:

"We'll flush off-peak, when load is low."

That is backwards. Burst load is exactly when the hot tier grows fastest and when a crash would hurt most. Deferring durability to the quiet hours means the data you most need to keep is the data sitting in the least durable place.

I wanted flush to be continuous and unremarkable — not because flushing is free, but because its cost should be small enough that postponing it is harder to justify than just letting it run.

One rule: ingest never waits, committed rows never lost

The architecture is built around one invariant:

The flush path may lag behind ingest. It may never block it, and it may never forget a row it already committed.

Hot ingest writes to SQLite hourly tables and returns. A background flusher notices finished hours, writes them to Parquet, and advances a watermark — all without holding any lock ingest needs:

The important boundary is simple: the flusher only ever reads hot tables and only ever advances the watermark inside the same transaction that records the new cold file. Ingest can be fast without the flush being fast, and the flush can be killed without ingest noticing.

That is the whole idea.

The shape of the code

The implementation is intentionally small. The core is a loop:

func (f *Flusher) flush(ctx context.Context) {
    for {
        committed, advanced, err := f.flushOnce(ctx)
        if err != nil {
            f.trackError(err)
            return
        }
        if committed == 0 || !advanced {
            return
        }
    }
}

Each flushOnce reads the watermark, pulls up to 100k rows above it, groups them by tenant and hour, writes the ready groups to Parquet, and commits file catalog plus watermark in a single manifest transaction. There are three decisions inside that loop that each fixed a real bug.

1. Drain loop, not one flush per tick

The first version flushed once per tick. Under burst load, a full 100k batch meant rows above the batch limit sat in hot for an entire extra tick while the watermark claimed progress. The system looked caught up and wasn't.

The fix is the loop above: repeat until a pass commits nothing or advances nothing. A tick now means "drain everything ready," not "move one batch." The kill-safety test (FlusherStop_NoDataLossAfterKill) and the SIGKILL harness both exercise this — partial progress is fine because every pass is independently atomic.

2. Injected clock, not wall clock

A group is ready when it is big (256MB outright) or old enough: at least 1000 rows or 1MB and past hourEnd + 5min, or any rows at all past that age. The aging grace lets late events land in their hour before it gets sealed.

One branch of that comparison used nanoseconds and the other used microseconds. A low-volume hour therefore looked perpetually young and never flushed — a straggler that sat in hot forever while every metric stayed green.

The fix: one nowUs injected everywhere in the flush path, and a regression test with microsecond stamps (TestReadyGroupsAt_StragglerUnitsMicroseconds). The rule I kept from that bug:

Never call the wall clock in the flush path. If a test can't freeze it, the comparison is already wrong.

3. Deterministic file IDs, not random ones

A flush killed after writing the Parquet file but before committing the manifest will retry the same rows on restart. Random file IDs would catalog the same data twice. So the file ID is derived from the content range:

// Same rows in, same ID out — retries are no-ops.
fileID := sha256(tenant + "|" + hour + "|" + minTS + "|" + maxTS + "|" + minID + "|" + maxID)

Combined with INSERT … ON CONFLICT DO NOTHING in hot and PK-conflict tolerance in the manifest, a retry produces the identical file ID, the catalog insert is absorbed, and the watermark advances exactly once. The temp-file protocol (O_EXCL 0600 → fsync → SHA → atomic rename) means a half-written file is never visible under its final name.

Kill safety without two-phase commit: idempotent writes plus a watermark that only moves inside the commit transaction. Together, these mechanisms make retries safe without requiring two-phase commit.

Why these boring tools?

There is a fashionable alternative for almost every component. I deliberately didn't use them.

Two SQLite databases instead of one

Hot uses WAL + synchronous=NORMAL + 5s busy timeout + 256MB cache: tuned for ingest speed. The manifest uses synchronous=FULL: tuned for truth. One database can't have two durability settings, so the watermark lives apart from the data it tracks. That separation is the point — the control plane is allowed to be slower than the data plane.

Parquet ZSTD instead of a custom cold format

Hourly groups sorted by (agent, ts) compress extremely well under dictionary encoding: 14.5–19.8 bytes per event, 22–29x smaller than raw JSON. I didn't design a format; I sorted the rows so an existing format could do its job.

One writer goroutine instead of a lock pool

There is no need to turn SQLite into a concurrency puzzle. One goroutine owns the hot connection; every batch arrives through it. The flusher reads through the same serialization point without ever taking a lock ingest contends on:

Watermark plus idempotence instead of 2PC or an LSM

A real log-structured merge tree would give me memtables, compaction, and sequence numbers with better write amplification. It would also give me a database to operate.

The watermark plays a role similar to a sequence number, the hot tier behaves somewhat like a mutable memtable, and flushing resembles compaction — rented from SQLite and Parquet rather than built.

What the benchmark says

All numbers from results-2026-09-23.csv: 50k-event runs, 3 repeats, 30-rep p50/p99, single Windows/NVMe box, pure-Go, CGO_ENABLED=0. Two corpora: uniform synthetic (~327 B/ev) and reallog-shaped (LOLBin/EVTX, 8% large 2KB bodies).

The important number is the flush rate relative to ingest: in this benchmark, the drain loop processes rows roughly 3–4x faster than the measured ingest rate, giving the watermark enough throughput headroom to catch up after bursts.

There is an important caveat. These are single-box numbers with synchronous=NORMAL on hot. A slower disk, a FULL-sync hot tier, or 4 concurrent readers (which push aggregate merge to ~183k ev/s but contend on I/O) all change the shape. And merged query latency follows fan-out math — with hot p50 $p_h$ and cold p50 $p_c$, merged p50 $\approx \max(p_h, p_c)$, because the ±10min overlap window bounds how much both tiers scan.

The numbers are not proof for every disk. They are a regression guard: if a change makes flush slower than ingest, the watermark lag metric says so before users do.

Failure should move in one direction

This is the most important failure policy in the design. If the process is killed mid-flush:

If the disk fills, new writes are rejected with ErrDiskFull (95% gate, warning from 85%) — committed rows stay committed, the router keeps answering from what exists. If the cold tier errors, queries degrade to hot-only with a Warnings[] entry rather than a 500.

The system degrades from:

fresh and complete

to:

stale but committed

rather than from:

durable

to:

silently missing rows.

Three drills I actually ran, all green:

• Flood: 65k queue overflows to disk spill (JSONL, 16MB rotate), then to a drop counter — ingest stays up, telemetry loss is counted, not hidden
• Kill: SIGKILL mid-flush, restart, merged result == 50000 with zero duplicates
• Fill-disk: injected full-disk fault rejects WriteBatch, committed data still queries clean

Shutdown is part of durability

There is an easy-to-miss problem with background flushing: if the process exits mid-flushOnce, the in-flight batch evaporates from memory — but the watermark never moved, so restart replays it. Clean shutdown just makes that faster by draining the in-flight pass before exiting:

Background does not mean:

"Persist whenever you feel like it."

It means:

"Persist independently from ingest, but still manage the lifecycle correctly."

The honest limit

The design has real weaknesses.

The 100k-row batch cap means a single flushOnce never handles more than that — the drain loop compensates, but each pass re-reads the watermark, so pathological fragmentation (many tiny tenant-hours) pays per-pass overhead. The current benchmark shows roughly 500–650ms to flush 50k rows; sub-second freshness for cold data is not what this is for — hot queries handle that. A straggler hour waits out its 5-minute aging grace even if you want it sealed now; there is no force-seal. It is single-node only, and the schema is v1-frozen (new fields ride data_raw/annotations, unindexed).

That means the system chooses:

never lose a committed row

over:

flush instantly.

For an event store, that is the current invariant. But it is still an invariant with a cost: if your workload needs cold data visible in under a second, query the hot tier — that is what it is for. A future version could expose the trade-off explicitly:

I would rather expose that trade-off than pretend a single policy serves both.

The actual goal

The goal was never to make flushing literally free.

CPU isn't free. Disk isn't free. The Parquet files aren't free.

The goal was to make the cost of durability lower than the cost of losing data — cheap enough on ingest that nobody schedules around it, and safe enough under kill that nobody babysits it.

That requires one architectural rule:

The component that makes data safe must never be load-bearing for the component that accepts data.

For TSE, a drain loop, a contiguous watermark, and idempotent writes are enough to make that practical. No LSM to operate. No coordinator to elect. No checkpoint to fear.

And most importantly, the SIGKILL drills show that the flusher can be killed mid-write, restarted, and recover the committed rows without duplicates. That's the kind of durability I want underneath a security event store: boring, local, measurable, and difficult to accidentally lose data with.