What degrades when telemetry lives in SQLite

A throughput benchmark answers one question — how much can this thing accept right now — and an embedded telemetry store almost never fails that way. It fails weeks later, quietly, in one of a small number of places: the write-ahead log, the size of the tables a dashboard query has to scan, the disk the deletes never reclaimed, and the moment ingest outruns the writer. This post walks those failure modes in the order they usually arrive, with the mechanism behind each and the setting that governs it.

This is the operational companion to the SQLite throughput benchmark. That post measured hour one. This one is about what changes after it.

Key takeaways

• Write throughput stays roughly flat as the database grows. Read latency does not, and it falls off a cliff rather than sloping.
• The write-ahead log, not the table file, is usually the first thing to surprise you on disk.
• DELETE in SQLite frees pages inside the file; it does not hand space back to the filesystem unless you have asked it to.
• Retention is the only knob that keeps an embedded store healthy indefinitely. Everything else buys hours.
• Overload should be visible, not silent: an ingest path that refuses work with 503 plus Retry-After is behaving correctly, because OTLP exporters retry it.

1. Reads decay; writes mostly don't

Inserts into an append-shaped table cost about the same at 10M rows as at 10k. A dashboard query does not. Every panel on an endpoints page is an aggregation — percentiles per route, counts per bucket — and the cost of that aggregation tracks the number of rows in range.

Benchmark runs on a 2 vCPU, 8 GB box put concrete boundaries on it for the SQLite storage backend: the metrics views stayed usable to roughly 1M rows, and the spans and logs views to roughly 100k. The important part is not the exact number, it is the shape of the failure. There was no slow-but-tolerable band above those levels. Queries either answered in a couple of seconds or ran past the timeout. A ten-times step in rows turned a working page into a page that never returns.

Two consequences for anyone operating a store like this:

• Alerting on mean query time will not warn you. The distribution is bimodal; the mean tells you nothing until it is already too late.
• Capacity planning is in rows you will query, not rows you can write. That is a retention question, which is section 3.

2. The WAL is what grows first

In WAL mode, committed data lands in a sidecar file and is folded back into the main database by a checkpoint. The TracePath telemetry database sets wal_autocheckpoint(50000), which means SQLite does not attempt an automatic checkpoint until roughly fifty thousand pages have accumulated. That is deliberate: checkpointing on every few pages turns sustained ingest into a stream of stalls.

The trade is that the WAL is allowed to get large, and a checkpoint on a large WAL is not free. Under continuous ingest, a checkpoint can also be repeatedly deferred, because it cannot copy back pages that an open read transaction still needs. That is how a WAL file ends up several times the size an operator expected.

What to keep in mind:

• Size the disk for database file plus WAL, not the database file alone.
• Long-running analytical reads hold back checkpoints. A dashboard left open on a huge time range is a contributor, not an innocent bystander.
• Something has to truncate the WAL eventually. Here the hourly retention pass ends with PRAGMA wal_checkpoint(TRUNCATE), which is the only routine event that reliably gives the space back.
• synchronous=NORMAL, which the telemetry database uses, fsyncs at checkpoints rather than on every commit. That is the right trade for telemetry — a crash can lose the most recent commits, and telemetry is the one kind of data where losing the last second is survivable — but it is a trade, and worth making knowingly.

3. Deletes do not shrink the file

The retention worker runs at startup and then hourly, issuing a DELETE ... WHERE <time column> < cutoff against each telemetry table: endpoints, tasks, exception stack traces, spans, metric points, session recordings, AI traces, log records, sessions, fired notifications, profiling data, and synthetic check results. SQLITE_RETENTION_DAYS sets the window, defaults to 30, and 0 disables the worker entirely.

What surprises people is that deleting rows, on its own, leaves disk usage unchanged. SQLite marks freed pages as reusable inside the database file; it does not return them to the filesystem. Reclamation has to be asked for, which is why each retention pass ends with three statements rather than stopping at the deletes:

PRAGMA incremental_vacuum;        -- return freed pages to the filesystem
PRAGMA optimize;                  -- refresh stats the query planner uses
PRAGMA wal_checkpoint(TRUNCATE);  -- fold the WAL back and truncate it

That trio is what makes the hourly pass actually shrink the footprint, and it only works because the database was opened with auto_vacuum(incremental) — a full VACUUM would rewrite the entire file and block everything while it did. The DuckDB backend does the equivalent with a single CHECKPOINT after its deletes, for the same reason: without it, retention frees rows and reclaims nothing.

The mental model to hold: retention bounds growth. Deleting rows does not shrink a file unless something asks it to.

Two tables are deliberately excluded from time-based pruning, and the reason is worth stating because it is a rule worth copying. archived_exceptions and slow_endpoints are not time series; they carry user intent — somebody archived that issue, somebody set that threshold. Age is not a reason to delete a decision.

Logs get a second, independent bound because log volume is the least predictable of the signals. LOG_RECORDS_MAX_ROWS caps the row count, enforced by a worker that trims to the newest N rows once a minute. Read that cap honestly: it is a cleanup task, not a hard limit. Nothing throttles ingest, so the table overshoots between passes, and sustained ingest above N rows per minute keeps it permanently above the cap. Size it with headroom for your peak, not for your average.

4. What overload should look like

An ingest path with no admission control has exactly one failure mode under a burst of large batches: memory climbs until the process is killed. That is worse than it sounds on an embedded store, because the restart then has to replay a write-ahead log before it can serve anything.

The fix is a gate in front of ingest. Every telemetry endpoint passes through an admission limiter that caps concurrently processed requests — INGEST_MAX_CONCURRENT, defaulting to twice the core count with a floor of four — and bounds the number of requests allowed to wait for a slot. Requests beyond that are refused immediately with 503 and a Retry-After header rather than parking a goroutine and a timer.

503 is a deliberate status choice. OTLP exporters and the OpenTelemetry Collector treat 503 as retryable and back off; several other statuses are treated as permanent and the batch is dropped on the floor. Getting this wrong means data loss that looks, from the server side, like a successful rejection.

The same logic runs through the body limits. OTLP request bodies are capped at 10 MB, while the browser and mobile ingest path allows far more, because session-replay segments are a different size class and the SDKs re-queue a rejected batch indefinitely — a cap you can actually hit becomes a permanent wedge rather than shed load.

5. One process, one machine

The embedded builds are single-instance by construction. The databases are local files and the in-process project cache learns about writes only from its own process, so a second instance pointed at the same directory is not a supported configuration — it is data corruption waiting for a schedule. Running more than one backend instance requires the client-server storage backends (PostgreSQL plus ClickHouse), which is a different deployment, not a flag.

Inside the single process, the connection pools differ on purpose. The relational database runs with a single connection, because SQLite serializes writes at the file level anyway and one connection makes lock behavior predictable. The telemetry database runs with four, so WAL readers can work while a write is in flight.

The practical consequence: every long operation that holds that single relational connection is a global stall. That is why the ingest path never opens a transaction it does not need, and why the org-wide read endpoints fetch their project list in a short transaction and then run the telemetry queries outside it.

6. What to watch

Four signals catch nearly everything above before a user does:

The deep-health endpoint is GET /api/health/deep, and it is an operator endpoint, not a public one: its payload is instance-wide, so it is gated behind the HEALTH_DEEP_TOKEN bearer secret and is disabled outright when that variable is unset.

One backend-specific detail worth knowing if you compare the two embedded engines: the DuckDB backend drops an individual row it cannot append, counts it, and reports a rate-limited exception, so one poison row cannot wedge an SDK's retry loop. The SQLite backend fails the whole request instead. Neither is wrong, but they produce different dashboards, and the drop counters only exist on one of them.

When to stop tuning

There is a point where the answer is not another pragma. Two conditions reliably mark it:

1. A signal's working set crosses its read cliff and retention cannot pull it back without deleting data somebody needs.
2. You need a second instance — for availability, for rolling deploys, or because one machine no longer has the headroom.

Either one means a columnar, client-server store. That is the comparison in SQLite vs DuckDB, which moved every read cliff by a factor of one hundred on the same hardware, and it is why TracePath Cloud runs the ClickHouse backend rather than an embedded one.

If you would rather not operate any of this, that is the entire proposition of the hosted platform: point an OTLP exporter at it, per the OpenTelemetry setup guide, and retention becomes a line on a plan instead of a worker you tune.

OTEL_EXPORTER_OTLP_PROTOCOL=http/protobuf
OTEL_EXPORTER_OTLP_ENDPOINT=https://ingest.tracepath.dev/api/otel
OTEL_EXPORTER_OTLP_HEADERS=Authorization=Bearer <project_token>

FAQ

Why can disk usage stay flat after deleting rows?

Because a DELETE frees pages inside the database file and leaves them there for reuse. Returning that space to the filesystem is a separate step. The retention pass here runs PRAGMA incremental_vacuum and PRAGMA wal_checkpoint(TRUNCATE) after its deletes for exactly that reason; a retention job that only deletes will keep a large file forever.

How big should the WAL be allowed to get?

Large enough that checkpoints are rare, small enough that one checkpoint is not a visible stall. The default here is roughly fifty thousand pages. If you tune it, tune it against a sustained ingest run rather than an idle box, because the checkpoint behaviour that matters only appears under continuous writes.

Is a 503 from an ingest endpoint a bug?

No, it is backpressure working. OTLP exporters and the Collector retry 503 with backoff. A saturated ingest path that returns 503 with Retry-After loses nothing; one that accepts everything until it is killed loses whatever was in flight, plus whatever arrives during the restart.

Can two instances run against the same SQLite files?

No. The embedded storage backends are single-instance by construction. More than one instance requires the client-server pair, PostgreSQL plus ClickHouse, which is what TracePath Cloud runs.

Which signal runs out first?

Logs, in both directions. They had the lowest write ceiling of the three signals on SQLite and one of the earliest read cliffs, and they are usually the highest-volume signal a service emits. If you are going to cap one thing, cap logs first.