← All guides

Storing OpenTelemetry traces in one SQLite file

simpletel is one Go binary and one SQLite file. This guide is what that actually costs and where it actually breaks — the write path, the bytes, the disk dial, the replication, and the ceilings, all from measurements on the real server.

Why a single file is the design, not a shortcut

Most trace stores are a query tier plus an object store plus a metadata database, wired together and operated forever. simpletel takes the opposite bet: one process owning one SQLite database on one small VM. There is no cluster to boot, no object store to configure, no version matrix to keep in step. The cost of that bet is a single writer and a vertical ceiling, and the rest of this guide is honest about both.

The single-writer rule

SQLite allows exactly one writer at a time. Rather than fight that, simpletel leans into it: the database is opened with db.SetMaxOpenConns(1), so every connection funnels through one writer and writes serialize naturally. The database is opened in WAL mode with PRAGMA journal_mode(WAL), busy_timeout(5000), and foreign keys on. WAL is what makes the single writer workable: writers append to a write-ahead log while readers keep reading consistent snapshots, so a slow query never blocks ingest and ingest never blocks a query. The busy_timeout of 5 seconds is the safety valve — if two writers ever do contend, the second waits rather than failing instantly, but with one connection they almost never do.

The single writer is also why some things that look like cron jobs live inside the server process instead. Retention runs as a worker inside serve, and the daily meter report is an in-process goroutine — because a separate simpletel-server gc process against the live file would be a second writer, and a second writer can desync Litestream's view of the WAL (see the replication section).

One savepoint per span

A batch of OTLP spans is persisted inside a transaction, but a single malformed span must not throw away the whole batch. simpletel writes each span under its own SQLite SAVEPOINT (store.Savepoint(ctx, tx, "sp", fn)): if the span's inserts fail, the savepoint is rolled back to and released, the surrounding transaction lives on, and the rest of the batch still lands. This is the difference between "one bad span" and "your whole export was rejected" — and it is the kind of thing a single-file design gives you cheaply, because there is no cross-node transaction to coordinate.

The read path is the same file

The same single connection serves reads. GET /api/v1/traces returns newest-first trace summaries scoped to the requesting team, and GET /api/v1/traces/{trace_id} returns one trace in full — spans, events, links, and correlated logs. Tenancy is enforced on every read by owner_user_id, so a team only ever sees its own rows, and attribute values are stored flattened ({"service.name":"x"}), which is what makes json_extract(attributes_json, '$."service.name"') the query primitive. WAL is what lets these reads take a consistent snapshot while ingest keeps appending in the background — no lock, no reader/writer trade-off to tune.

How fast it writes

In the measured runs the server ingested OTLP/JSON at a few thousand spans per second on a laptop, with retention and rate limiting switched off — a client-bound upper bound, not a promise, and a production Linux VM under real network latency will be slower. The shape is what matters: throughput is bounded by one writer's fsync rate rather than a cluster's coordination, and it degrades as the database grows. That is enough to make a single small VM a real backend for a real app, and it is exactly the constraint that keeps the design honest about its ceiling.

What a span costs on disk

The number that decides everything is bytes per record. Measured on the real server (marginal Δfile / Δrecords with the WAL checkpointed to zero), a typical span — eleven attributes, a 30% chance of carrying a span event, the resource and scope metadata amortized across the batch — costs ≈825 bytes. Logs are cheaper at 553 bytes, and metric points cheaper still at 502 bytes. The span figure is higher than a naive row size because each span is indexed three ways: team_id+trace_id, team_id+start DESC, and team_id+parent_span_id. Stored bytes exceed wire bytes; the indexes are what make the read path fast.

Attributes are the variable that moves the number: roughly 32 bytes per attribute, so an extra ten attributes per span is an extra ~320 bytes, nearly doubling the per-span cost. This is the single most important lever a user controls, and it is why the calculator and the comparisons keep saying the metered unit is a record, not a gigabyte: the payload shape is yours.

Retention is the disk dial

In steady state the database holds roughly one retention window of ingest, because the retention worker deletes records older than the plan's window. That makes retention — not volume — the dial you turn when the disk fills. Free keeps 7 days, Starter 30, Team 90. At 825 bytes per span, a month of 100M spans is about 77 GiB resident; the host VM's included disk is ~100 GiB, so 100M spans/month with 30-day retention fits comfortably on the smallest plan. The retention worker runs hourly by default, deleting in batches, and can be tuned or handed to a cron gc — but never against a database a live serve owns, because that would be the second writer again.

Replication with Litestream

One file on one VM is a durability risk unless it is replicated. Litestream streams the SQLite WAL to object storage (S3, Cloudflare R2, or any S3-compatible target) as LTX files, with a full snapshot every 24 hours by default, and restores on boot with litestream restore -if-db-not-exists. The remote footprint is therefore roughly one snapshot plus deltas — call it one to two times the local database depending on snapshot retention. The rule that matters most: never run a second writer against the live database. An out-of-band gc or team create can desync Litestream's WAL view, which is exactly why retention and meter reporting live inside serve.

The honest failure modes

Disk growth. Verbose payloads and high attribute counts grow the file linearly; there is no compaction or tiering that saves you. Watch bytes per span and the retention window together.

The single-VM ceiling. SQLite has one writer, so a node scales up, not out. A single small VM does a lot — the measured ingest is thousands of records per second on a laptop, and a production Linux VM under load will be slower — but there is no horizontal sharding to reach for. Past a node's capacity you add disks, not nodes.

One billion records a month needs dedicated capacity. At 825 bytes per span, 1B spans/month is ~768 GiB resident — far past the ~100 GiB included disk. It is a scale decision: extra disk (roughly tens of dollars a month on the host plan) or a dedicated machine. simpletel does not pretend a single small VM holds a billion spans a month.

WAL checkpointing. The WAL grows between checkpoints, and a long-running reader can hold checkpointing back so the WAL balloons even as the main file looks small. The measured figures above are taken after PRAGMA wal_checkpoint(TRUNCATE) for exactly this reason — always size the WAL alongside the main file, and do not let a stuck reader turn disk accounting into a guess.