Three bugs that made our time-series queries 680 slower (and how we found them)

A SELECT ... ORDER BY ts DESC LIMIT 10 on a 1M-row table took 1.2 seconds. It should have taken single-digit milliseconds. The worst part? Our EXPLAIN said we were using the fast path.

We were wrong. This is the story of the three bugs behind that number, how differential testing caught them, and how v0.12.1 of MoteDB fixed them — from 1,196ms down to 1.76ms, faster than a DuckDB full scan on the same data.

If you ship software with "fast paths", this post is for you, because every single one of these bugs survives a normal test suite.

The setup

MoteDB is an embedded database for embodied AI — robots, AR glasses, industrial arms. The single most frequent query on a robot is some variant of:

SELECT * FROM sensor WHERE device = 'arm_7' ORDER BY ts DESC LIMIT 10

"Show me the last 10 readings." It runs constantly: dashboards, anomaly drills, health checks, LLM tool calls. On our time-series layout (columnar segments, gorilla-style timestamp encoding, zone maps per segment), this should hit a top-k fast path and never decode the full table.

Except on tables where ts is declared as INT — which a lot of embedded schemas do, because timestamps-as-epoch-ints are the path of least resistance — the query took 1.2 seconds on 1M rows. And here is the detail that made it painful to find: the EXPLAIN output showed the top-k fast path as the chosen plan.

Bug #1: The decoder that couldn't decode

Our top-k by timestamp reads column segments at three decode points. All three had the same assumption baked in: timestamps are stored with the GorillaTimestamp codec.

But for INT columns we use DeltaVarint — a gorilla-style integer codec. When the top-k decoder met a DeltaVarint chunk, the code did the polite thing:

_ => continue, // not a timestamp chunk, skip

Result: zero rows out of the fast path. No error. No log. An empty candidate list, which the executor treats as "fall back to the generic scan".

That's bug number one, and it's the most dangerous kind: the fast path didn't crash, it quietly produced nothing. The fallback did the work of 1M decodes. Nobody noticed, because correctness was fine — the slow path is correct. It was only slow.

Bug #2: The zone map that pruned everything

Every columnar segment carries min/max metadata for the timestamp column, and scans use it to skip segments that can't contain rows in range. So what happens to that metadata when rows are still sitting in the write buffer?

The buffer's time-tracking code updated stats when it saw Value::Timestamp. Our INT-table rows are Value::Integer. So the tracked range for those segments stayed at its initialization value: (0, 0).

Then the zone-map gate asked, for every segment: "could this segment contain recent timestamps?" A segment with min=0, max=0 containing the most recent data — pruned. All of them. The gate was doing exactly what it was designed to do, on metadata that silently never got populated.

The fix is a semantic one, and it generalizes: (0, 0) is not "this segment has no recent data", it's "unknown" — and "unknown" must not prune. The gate now lets (0,0) segments through instead of discarding them.

Bug #3: The visibility lie

Robot writes land in a write buffer first and get folded into segments at checkpoint. snapshot_rows has an in-range check to decide whether buffered (uncommitted) rows fall inside the query's time window. For Integer-backed buffers, that check was hardcoded:

false // Integer buffers: never in range

Meaning: the last few seconds of writes — precisely the rows that "ORDER BY ts DESC LIMIT 10" exists to return — were invisible to the top-k until a checkpoint ran. On a robot that checkpoints every 30 seconds, "the latest reading" could be half a minute stale, and nothing would tell you.

Bug #4 (the humbling one): the fast path never ran

While instrumenting all this, we found something worse. The Python binding's query entry point never routed time-series top-k to the fast kernel at all. EXPLAIN cheerfully printed the fast path as the chosen plan — but that was a paper plan. The streaming entry point was missing the routing branch, so every query from Python took the generic executor from the start.

So we had:

• a fast path that produced empty results on INT columns (bug 1),
• segment pruning that hid the newest data (bug 2),
• visibility logic that hid uncommitted rows (bug 3),
• and a routing layer that meant most production traffic never reached any of it (bug 4),

…while our EXPLAIN output told us everything was fine.

The fix

The v0.12 fixes, briefly:

1. Grouped decode — pass 2 decodes per-segment instead of re-decoding the segment's needed columns for every surviving row (the old loop's cost was quadratic in surviving rows per segment).
2. Zone gate semantics — (0,0) metadata no longer prunes; it's treated as unknown.
3. Visibility — in_range handles Integer buffers; uncommitted rows are visible to top-k immediately.
4. Routing + projection backfill — the streaming entry routes ORDER BY ts LIMIT k to the top-k kernel, and pass-2b backfills the ts values when the projection omitted the column.

Results on 1M rows (Apple Silicon, release build, reproducible via the repo's benchmark suite):

Query	Before	After
ORDER BY ts DESC LIMIT 10	1,196 ms	1.76 ms
DuckDB full scan (reference)	—	2.1 ms

A 680× improvement, and the fast path now actually beats scanning everything — which is the entire point of a fast path.

The lessons (the part worth sharing)

1. A fast path without a differential test is a rumor. All four bugs kept every test green, because the slow path is correct. What caught the correctness half was our SQLite-as-oracle differential harness; what finally surfaced the performance half was an adversarial benchmark that compares the fast path's output and its timing against the generic path. If you maintain two code paths for the same query, they need to be diffed against each other on every commit — same inputs, same outputs, and the fast one had better actually be fast.

2. EXPLAIN is a claim, not a measurement. Bug #4 means our EXPLAIN output described a plan that never executed. Since then, any latency investigation starts with wall-clock timing per stage, and EXPLAIN gets checked against reality, not trusted.

3. Type-generality is a correctness surface. The root cause chain here is one abstraction leak: "timestamps" were treated as Timestamp in a dozen places, and every one of them needed a separate decision about what to do with Integer. Each place we made that decision independently, we made a different mistake. If your engine has a fast path per (query shape × column type), budget test coverage proportional to that product — it grows fast.

4. "Unknown" must not prune. Any optimizer gate built on metadata needs a distinct answer for "this segment provably can't match" versus "we don't know". Collapsing both into a zero value turns missing stats into wrong results — in our case, into a silently empty newest segment.

What else shipped in v0.12

The top-k fix was one item in a large release. The highlights:

Hybrid search — BM25 full-text and vector KNN fused with Reciprocal Rank Fusion in one call, no score calibration needed:

import motedb

db = motedb.Database("robot.mote")
db.execute("CREATE VECTOR INDEX ev_emb ON events (emb)")
db.execute("CREATE TEXT INDEX ev_note ON events (note)")

rows = db.hybrid_search(
    "ev_note", "bearing noise",   # text list
    "ev_emb",  query_vec.tolist(), # vector list
    k=10,
)
# each row carries __rrf__, __bm25__, __distance__

Arrow/pandas interop — query_arrow() returns a pyarrow.Table with VECTOR(n) columns mapped to Arrow's canonical fixed_size_list<float32>; insert_arrays bulk-loads numpy arrays at ~200K rows/s with durability on.

Filtered vector search that doesn't drop results — WHERE zone = 'bay_3' ORDER BY emb <-> ? LIMIT 10 used to filter after top-k, so a highly-selective predicate could return fewer rows than asked, or none. Candidate depth now deepens iteratively until it has k survivors.

Scan UPDATE/DELETE predicate pushdown — 112.6K rows/s, past SQLite's 110K at the same durability level.

ANN tail latency — p99 30ms → 1.5ms; the "tail" was cold page faults during the first ~30 queries after open, fixed with madvise(WILLNEED) warmup.

One breaking change to flag: multi-word MATCH now defaults to AND (SQLite FTS5-compatible). Use MATCH('a OR b') for the old behavior.

Try it

cargo add motedb          # Rust
pip install motedb-python # Python (macOS + Linux wheels)

Everything above is reproducible — the benchmark suite, the differential harness, and the full changelog live in the repo:

👉 github.com/motedb/motedb — MIT, pre-1.0, very active. Star it if embedded multimodal storage is your kind of problem, and tell us in the issues what workload you'd throw at it.

---

What's the sneakiest silent-fallback bug you've shipped? Wrong answers (and right ones) in the comments — curious whether differential testing is common practice or still niche outside databases.