Why observability outgrows SQLite

An embedded SQL database like SQLite is a better telemetry sink than most people expect. Appending batched rows is close to the cheapest thing a database can do, and a single-binary deployment with no cluster to operate is genuinely attractive. What breaks first is not ingest — it is reads, and specifically the two read shapes an observability dashboard is built out of: aggregations over a time window, and unindexed scans over the newest rows.

This post is about mechanism, not benchmarks. No numbers are quoted, because the useful thing to understand is which query shapes degrade, in what order, and what actually buys headroom. Once you can predict the shape of the cliff, you can size retention around it instead of discovering it during an incident.

Key takeaways

• Ingest is the easy half. Batched appends inside one transaction are cheap in almost any engine; per-row inserts are what kills throughput, and that is a client-side mistake, not a database limit.
• Three query shapes, three scaling curves. Point lookups by an indexed key stay flat. Filtered scans scale with the result set. Aggregations scale with the rows scanned — which is the whole table, in a row store.
• A row store reads bytes it does not need. Computing p95 latency per route means touching every column of every matching row. A columnar engine touches two columns.
• Rollups and retention beat tuning. Pre-aggregated metric tiers and a TTL per signal move more of the curve than any index you can add afterwards.
• The failure is uneven, and that is the dangerous part. A trace-ID lookup can stay instant while the severity filter next to it times out, so a median latency can look healthy while the page your on-call actually opens is dead.

What a dashboard actually asks the database

Open any observability UI and the queries behind it fall into three families.

1. Point lookup by an indexed key. "Show me trace 4bf92f…." "Show me exception group 2e16546cedb34a03." One row, or a handful, found through an index. Cost is roughly constant as the table grows — this is the shape databases are best at, and it is why a well-indexed store can stay usable for targeted lookups long after everything else has given up.

2. Filtered scan returning many rows. "The last 200 ERROR logs for this service." "Every log whose body contains timeout." The index narrows the candidates if one exists; a free-text body search has no index to use at all. Cost scales with how many rows match and how many have to be examined to find them.

3. Aggregation over a window. "p50/p95/p99 latency per route for the last six hours." "Request count per minute, grouped by status code." "Error rate by endpoint." No single row is interesting; the answer is a reduction over thousands to millions of them. Cost scales with rows scanned, and the window is usually "everything recent", which as the table grows means "a lot".

Family 3 is where an observability dashboard spends most of its time, and it is the family a row-oriented store handles least well.

Why writes are the easy half

Telemetry ingest is append-only, batched, and never updated after the fact. That is an unusually friendly workload. The two rules that decide whether it performs are both on the client side of the boundary:

• One transaction per batch, not per row. An OTLP export carries hundreds or thousands of items. Inserting them individually pays the transaction overhead once per row; wrapping the batch in a single transaction pays it once per batch. The difference is not a few percent.
• Never insert single rows into a columnar store. ClickHouse and friends merge data in parts; a stream of one-row inserts creates a part per row and the background merges never catch up. Anything that arrives one at a time — session replay segments, for instance — needs a batcher in front of it that accumulates rows and flushes on a size or time bound.

Get those right and ingest throughput is generally limited by CPU and disk, not by the engine. Which is exactly why write benchmarks make embedded databases look so good, and why they answer the wrong question.

Why reads break, and in what order

Take the three families again, and imagine the table growing by an order of magnitude.

The point lookup barely moves. An index lookup on a trace ID is a tree descent; ten times the rows adds a level at worst.

The filtered scan degrades in proportion to the work it does. A severity filter that matched 1,000 rows now matches 10,000, and if the filter column is not indexed, the engine reads every row to decide. A free-text body search is the worst case: no index applies, so it is always a full scan, and the scan grows linearly with the table.

The aggregation degrades fastest of all, for a reason specific to row storage. To compute a latency percentile per route, the engine needs two columns: the route and the duration. In a row store those two values are interleaved with every other column of the row — status code, trace ID, attributes, timestamps, the lot — and rows are stored contiguously. So reading two columns means reading every byte of every matching row off disk and through memory, discarding almost all of it. Then, in engines without native quantile functions, the durations have to be materialised and sorted to find p95.

That is the cliff. And it arrives unevenly: the same page can serve its trace-ID panel instantly while its aggregate panel times out. Which is why a median across "the dashboard" is a misleading health metric. During an incident you do not get to pick which panel you need.

What a columnar engine changes

Column-oriented storage inverts the layout: all values of one column are contiguous. The consequences for family 3 are direct.

• Only the needed columns are read. A p95-per-route query touches the route and duration columns and nothing else.
• Compression is dramatically better, because a column holds values of one type with heavy repetition — status codes, service names, severities. Less disk read per row scanned.
• Aggregation is native. Percentiles, time bucketing and grouped aggregations are first-class operations rather than "sort this array yourself".
• Time partitioning prunes whole chunks. Partitioning by month or day means a six-hour query never touches last quarter's data at all.

The trade-off is real, and it is the reason a columnar engine is not the universal answer: it wants batched writes, it has a background merge process to understand and tune, updates and deletes are awkward, and it is another stateful system to run. The relational side of an application — users, projects, settings, anything that gets updated — still belongs in a row store.

The four things that actually buy headroom

Before reaching for a different database, these move the curve more than tuning does.

1. Retention per signal. Logs, traces and metrics do not deserve the same window. Raw traces are for debugging something that happened recently; raw logs are for the same; metrics are for trends and deserve to live longest — but as rollups, not raw points. Setting an explicit TTL per signal is the single highest-leverage configuration decision in any observability deployment, and the one most often left at the default until disk fills.

2. Rollups for metrics. Raw metric points answer "what happened in the last hour" and are wasteful for "what happened last month". Pre-aggregating into per-minute and per-hour tiers, each with its own retention, means a thirty-day chart reads thousands of rows instead of millions. TracePath keeps raw points for a short window, a one-minute tier for roughly a month and a one-hour tier for about a year, and the query planner picks the coarsest tier that satisfies the requested resolution.

3. Bounding the query itself. A dashboard that lets a user request a two-year window at one-second resolution will eventually be asked to. Widening the bucket so no series exceeds a fixed point count, capping the number of groups returned, and putting a hard deadline on the request turn an unbounded query into a bounded one. TracePath's metric query endpoint does all three: buckets widen so no series exceeds 2,000 points, groups are capped and the response is flagged as truncated when they are, and the whole request has a deadline after which it answers with a timeout rather than holding the connection.

4. Shedding load instead of dying. Under an ingest spike, a bounded admission gate that answers 503 with a Retry-After is strictly better than unbounded concurrency that gets the process OOM-killed. OTLP exporters and well-behaved SDKs retry on 503; nothing recovers from a dead process that then has to replay a write-ahead log before it can accept data again.

What TracePath runs, and why

The hosted platform splits storage along the axis the workload suggests:

• Telemetry goes to ClickHouse. Spans, logs, metric points, exceptions, sessions, profiles and check results are append-only, time-stamped and queried with aggregations. Percentile functions, time bucketing, per-column compression and monthly partitioning are all engine features rather than application code, and TTLs are declared on the tables.
• Relational data goes to PostgreSQL. Users, organisations, projects, dashboards, alert rules, on-call schedules — everything that gets updated after it is written, needs joins, and is small by comparison.

The engine also carries embedded backends — SQLite for both halves, or SQLite plus DuckDB for the telemetry side — because the split above is a choice per deployment, not a law. DuckDB is the interesting middle: an embedded engine that is columnar, so it answers family-3 queries with native quantile functions and per-column reads while still being a file on disk rather than a cluster. What it does not give you is the multi-instance story, which is why the hosted product is on ClickHouse.

Retention on the hosted plans follows the same logic: the free tier keeps seven days of full-resolution telemetry, paid tiers keep thirty days, and extended windows beyond that are on the roadmap rather than something you can buy today — because the useful window for raw telemetry is short, and the useful window for aggregates is long. Pricing has the exact limits per plan.

How to tell you have outgrown an embedded store

Symptoms, roughly in the order they show up:

1. One panel on a page is slow while the others are fine. Usually the aggregate one. This is the earliest signal and the easiest to dismiss.
2. Free-text log search becomes unusable before filtered search does. No index applies to a substring match, so it degrades first and fastest.
3. Widening the time range changes the experience qualitatively, not gradually — twenty-four hours is fine and seven days never returns.
4. Retention is being cut for performance reasons rather than cost reasons. That is the clearest sign: you are deleting data you want in order to keep queries answerable.
5. Ingest is fine. It nearly always is. The absence of write problems is why this gets misdiagnosed as a tuning issue.

If two or more of those are true, more indexes are not the fix. Either the data needs to get smaller (retention, rollups) or the storage layout needs to change (columnar).

FAQ

Is SQLite a bad choice for observability? No — it is a reasonable choice for a small, short-retention deployment, and it ingests well. It is a bad choice for aggregate-heavy dashboards over large tables, which is a property of row-oriented storage rather than a flaw in SQLite.

Would adding indexes fix the aggregation problem? Partially and briefly. An index helps the engine find the matching rows; it does not stop a row store from reading every column of each one. Covering indexes narrow that, at the cost of write amplification and disk, and they still lose to columnar storage on the same query.

Why not just keep less data? That is the fix, and it is the one to reach for first. The question is whether the retention you are left with still answers your questions. When "enough history to see the trend" and "queries that return" stop overlapping, it is a storage-layout problem.

Does DuckDB solve this without running a cluster? For a single instance, largely yes: it is columnar, it has native percentile and time-bucketing functions, and it is still an embedded file. What it does not solve is running several backend instances against one store, which is a deployment-topology question rather than a query-performance one.

Does this affect how I should instrument? Mostly no — instrument with OpenTelemetry and export OTLP either way. The one thing worth caring about is attribute cardinality: high-cardinality attributes on metrics multiply series counts in every backend, self-hosted or hosted.

The short version

Write benchmarks flatter embedded databases because appends are easy. Dashboards ask a different question: reduce a lot of rows to a few numbers, and do it over the newest data, repeatedly. Row storage makes that expensive in a way indexes cannot rescue, columnar storage makes it cheap, and retention plus rollups decide how long either one holds up.

That is why TracePath's hosted backend stores telemetry in a columnar engine with per-signal TTLs and pre-aggregated metric tiers — and why the useful question is never "how many rows fit", but "which query shape breaks first".