Six fixes along the path a result takes — from the database, through the cache, into the browser — each one found by measuring rather than guessing. A 27MB cached result serves in a tenth of the time, the lookup behind it went from 66ms to 0.06ms, and taking a 20,000-row result into the browser went from 919ms to 101ms.
| What | Before | After | How it was found |
|---|---|---|---|
| Serving a 27MB cached result | 547 ms | 99 ms | Timed the serializer directly |
| Cache lookup, 100k rows of history | 66.7 ms | 0.064 ms | EXPLAIN ANALYZE |
| Buffers read per lookup | 3,356 | 4 | Same |
| Result eviction ceiling | 28,800/day | 288,000/day | Arithmetic on the job schedule |
| DuckDB connects per query | 1 + extensions | 0 | Reading the runner lifecycle |
| Taking in a 20,000-row result | 919 ms | 101 ms | Timed the same fixture before and after |
| Result lookup by hash and data source | 20 buffers | 7 buffers | EXPLAIN (analyze, buffers) |
| A live dashboard’s check-in, 12 widgets × 4 viewers | 48 statements | 4 statements | Counted with a cursor listener |
| Five widgets of one query, one dashboard load | 5 fetches | 1 fetch | Counted the requests in the browser |
Query results are stored in Postgres as JSON text. They leave over HTTP as JSON text. In between, the old path built the entire Python object graph and threw it away.
Splice the stored bytes instead of re-encoding them
Postgres TEXT ──▶ json_loads() ──▶ Python dicts ──▶ json_dumps() ──▶ HTTP
328 ms 663 ms
Postgres TEXT ─────────────────────────────────────────────▶ HTTP
payload passes through untouched
The surprise was inside the encode. _sanitize_data, which walks the whole
object graph looking for NaN and values JSON cannot represent, cost
291ms — more than the 252ms serializer it guards. It could never find
anything: unrepresentable values fail at insert time, so on the read path it was pure
overhead.
The metadata is still encoded normally, which is cheap because it is tiny. The payload is spliced into the slot it would have occupied, using a fresh random token per call rather than a fixed marker — so a payload cannot contain the string that would displace it, because it cannot know the token.
The decode is skipped too, by deferring the mapped column. Deferring is transparent, so the CSV and Excel paths that genuinely need decoded data trigger an ordinary lazy load and behave exactly as before.
Checking for a cached result filters on three columns and sorts on a fourth. Only one of them was indexed.
A composite index matching the actual query
-- before: every row that ever shared the hash, then filter, then sort
Limit
-> Sort (Sort Key: retrieved_at DESC)
-> Bitmap Heap Scan on query_results
Recheck Cond: query_hash = ...
Filter: data_source_id = ... AND retrieved_at + interval >= now()
-- after: one descent, no sort
Limit
-> Index Scan using ix_query_results_cache_lookup
Index Cond: query_hash = ... AND data_source_id = ...
The cost was linear in how much history a query had accumulated — and a query refreshing
every five minutes accumulates roughly 105,000 rows a year. The index is
(query_hash, data_source_id, retrieved_at DESC), with retrieved_at
descending so the first row the index yields is the answer and no sort is needed.
The old single-column index was dropped at the same time. Every query on this table that
filters query_hash also filters data_source_id, so the composite's
leading column covers it — and two indexes where one serves taxes every insert on the
most-written table in the schema.
Both operations run CONCURRENTLY, so no write lock is held on a table that
could hold millions of rows. Upgrade and downgrade were both exercised before shipping.
Lift the eviction cap, and say when it binds
288 runs/day × 100 rows/run = 28,800 rows/day maximum
≈ 20 results a minuteA few hundred queries on short schedules exceeds that, and past that point the table grows without bound — forever, with nothing saying so. The cap is now 1,000 per run.
The more useful change is the second one: the job now warns when it deletes its full quota. The failure mode was never the number, it was the silence. A disk filling up is a bad way to learn that cleanup has been losing a race for months.
Cache connections per process, and health-check them
DataSource.query_runner is a property, not a cached attribute — so every
access built a new runner, which opened a new DuckDB connection and reran
INSTALL and LOAD for each configured extension. Per query.
Connections are now cached per process, keyed by the configuration that produced them so two differently configured sources never share one. Each RQ worker is its own process, so the cache is naturally per-worker.
A cached connection outlives any single query and can be invalidated underneath you — the database file replaced, say. Without a health check, one dead connection would fail every subsequent query in that process, which is strictly worse than not caching at all. So the cached connection is checked before reuse and transparently replaced if it has died.
Reading a result into the browser spent most of its time on one column. A 20,000-row result with a single timestamp column took 919 ms to take in, on the main thread, before anything could be drawn.
Almost none of that was the date library being slow. It was being asked the same question twice. One function did a strict parse purely to decide whether a cell was a timestamp, threw the answer away, and a second function then parsed the identical string again to produce it. Parsing once, by hand, took the same fixture to 101 ms.
The obvious next step — dropping the date objects entirely for plain numbers — was measured and not taken. Of the 20 ms the parse now costs per 20,000 cells, 10.6 is the parse and 9.1 is building the objects: 3.6% of the original, in exchange for changing eleven places that read them. The measurement is what said no.
A live dashboard’s viewers check in every few seconds. Each check-in asked the database for the newest result of every widget, one statement at a time: a twelve-widget dashboard with four people watching was forty-eight statements every few seconds to produce twelve answers.
They are one statement per data source now, and the check-in no longer decides what to run at all — working that out means rendering every parameterized query, so it happens in a job while the viewer gets its reply. The periodic sweep that refreshes those dashboards also takes a lock, because it was started every ten seconds and allowed sixty to finish.
Every figure on this page came from running something on the actual stack — timing the
serializer against a real 100,000-row result, and EXPLAIN ANALYZE against a table
loaded with a year of realistic history. None of them are estimates.
That mattered more than once. An early version of the index looked like it changed nothing, because the test table had four rows in it and the planner was right to ignore an index at that size. The improvement only became visible — and only became real — once the table held the history a production instance would actually have.
Next up: replacing the polling loop with server-sent events, and a concurrency model that does not spend a whole OS process on each waiting connection. See the roadmap.