What TSE Is: SQLite Hot, Parquet Cold

Introduction

TSE is a single-node event store I built for high-volume telemetry.

The target workload is straightforward: endpoint agents and log pipelines continuously produce events, recent events need to be queried quickly, and older events still need to remain available for months. The system also needs to recover cleanly when the machine or process disappears in the middle of a write or flush.

The core design is deliberately simple:

Recent data stays in SQLite. Completed time ranges are flushed into compressed Parquet files. A small manifest database records what has been safely persisted to the cold tier.

The important part is not that TSE uses SQLite and Parquet. The important part is how the two tiers move from one state to another without making ingestion depend on the flush path.

Hot SQLite for recent data, cold Parquet for historical data, and a watermark that defines the boundary between them.

This post is the architectural overview. Two follow-ups will go deeper: one on the flush path that moves data between tiers, one on the query router that reads from both. No prior reading required — everything below is self-contained.

Why two tiers?

TSE has two different storage problems.

Recent events are still being written and are likely to be queried frequently. They benefit from a transactional database, indexes, and efficient incremental writes.

Older events are no longer changing. Once an hour of events is complete, keeping that data in an actively writable database is unnecessary overhead. Historical data can instead be stored in immutable columnar files that are cheaper to keep and efficient to scan.

So TSE separates storage by age:

• Hot: SQLite tables partitioned by hour, such as edr_events_YYYYMMDDHH. SQLite runs in WAL mode and handles the active write path.
• Cold: ZSTD-compressed Parquet files stored under events/<tenant>/<date>/<hour>/. Files are written once and then treated as immutable.
• Manifest: A separate SQLite database tracks cold files and maintains the flush watermark.
• Router: Queries both tiers when necessary, merges the results, and exposes partial failures as warnings rather than silently returning incomplete data.

The resulting lifecycle is:

The flush process is asynchronous. Ingestion does not wait for Parquet generation to finish.

That separation is the central design decision in TSE.

The vocabulary

Five components cover most of the system:

These components are intentionally small. Most of the reliability properties come from the boundaries between them.

1. The watermark is the recovery boundary

The watermark is the system's persisted record of how far the flush process has safely progressed.

It is stored as a single row in the manifest database and records the last safely flushed event ID and timestamp.

The important property is that the watermark only advances as part of the transaction that records the corresponding cold-file metadata.

That gives recovery a concrete starting point.

If the process dies during a flush, TSE does not need to guess which files on disk are authoritative. It can recover from the last persisted watermark and retry the unfinished range.

The exact ordering matters. A Parquet file existing on disk is not, by itself, enough to declare the data flushed.

The manifest transaction is what makes the state visible as completed.

2. Writes are designed to be idempotent

A crash can happen at inconvenient times. Retrying a flush therefore needs to be safe.

TSE uses idempotency at several layers:

• Hot SQLite ignores duplicate event IDs.
• Manifest records tolerate repeated attempts.
• Cold-file identity is deterministic for a particular content range.
• Retrying a previously interrupted operation therefore does not intentionally create another logical copy of the same data.

This is more important than making the happy path fast.

A storage system will eventually encounter retries. The useful question is whether those retries corrupt state or simply repeat safe work.

3. TSE borrows ideas from LSM storage

There is an interesting analogy here.

TSE's hot SQLite tier behaves somewhat like a mutable write layer. The flusher periodically converts completed ranges into immutable files, similar in spirit to how an LSM-based system moves data from mutable structures into immutable storage.

The manifest plays a role similar to metadata describing the durable state of those files.

The analogy is useful, but TSE is not an LSM tree.

I did not implement an LSM engine. I am using SQLite and Parquet as existing building blocks and borrowing some of the same storage ideas:

• mutable recent state
• immutable historical files
• explicit metadata about persisted state
• ordered progression through the data

That distinction matters because TSE is intentionally much smaller than a general-purpose storage engine.

Two conventions make the rest simpler

TSE uses two important identifiers consistently:

• Timestamps are stored as epoch microseconds (ts_us).
• Event IDs use UUIDv7, giving IDs a time-ordered structure.

That combination simplifies several parts of the system.

The flusher can reason about ordered event ranges. The router can merge records from different tiers using the same ordering. Pagination can use the event ID rather than maintaining a separate ordering mechanism.

A small convention repeated everywhere is often worth more than another abstraction.

The numbers

The following measurements come from a single Windows/NVMe machine using the pure-Go build (CGO_ENABLED=0) and 50k-event runs.

These numbers are measurements of the current implementation, not general performance guarantees.

The cold-storage figure is particularly workload-dependent. Compression effectiveness depends on the event distribution, schema, encoding, and compression settings, so it should be treated as a characteristic of the benchmark dataset rather than a universal Parquet storage ratio.

The SIGKILL result is also intentionally described narrowly: I ran two failure drills and observed zero lost rows in those runs. That is evidence for the recovery design, not proof that every possible failure mode has been exhausted.

The detailed methodology and larger benchmark tables live in the linked write-up.

What TSE is not

It is useful to define the boundaries explicitly.

• Not distributed. TSE is a single-node store with a single active writer. Multi-node operation is future work, not an implicit property of the current design.
• Not a real-time analytics engine. Historical data has to pass through the flush path before it becomes cold data. Recent data remains available through the hot tier.
• Not schemaless. The Parquet schema is versioned and currently frozen at v1. Additional information can be carried through fields such as data_raw and annotations, but that does not make the storage layer fully schemaless.
• Not automatically the right choice for small workloads. If a workload is comfortably below roughly 1k events/sec and retention is under 30 days, plain SQLite may be the simpler engineering choice.

That last point is important.

TSE exists because I wanted to explore the boundary where a single SQLite database starts becoming an awkward fit for high-volume, long-retention event storage. It is not an argument that every application needs a custom event store.

Where to go next

This overview gives the architecture. The interesting parts are in the failure boundaries, and I'll cover them in two follow-ups.

If you care about writes

The next post will go into the drain loop, the microsecond-versus-nanosecond straggler bug, deterministic file IDs, and the SIGKILL recovery tests.

If you care about reads

The post after that will cover the 10-minute overlap window, the k-way merge with deduplication, and why a failed storage tier becomes a query warning instead of turning the entire request into a 500.

The code is available at github.com/yanmyoaung2004/tse as a standalone Go module. The repository also contains the full architecture specification in ARCHITECTURE.md and the measurement details in PAPER.md.

Start here for the architecture. Then follow the write path or the read path once those posts land.