The 8.7-Second Query That Froze Every Page (and Blamed the Wrong One)

We got a bug report on Feed Me Data that read like a routing problem: "When I'm on the videos or podcasts page and click to another page, the browser shows the destination but the page never loads."

If you've maintained a SvelteKit app (or any SPA-ish framework), you know exactly where your mind goes: a teardown bug in those pages, a leaked effect, a stuck audio player, maybe a preload gone wrong. We read every line of the videos and podcasts pages, their layouts, their cards, the global mini-player. All innocent. Their queries ran in 3–5 milliseconds.

The actual culprit was a query that had nothing to do with either page, and the way it failed is a small case study in how single-threaded servers make performance bugs lie to you about where they live.

The symptom pointed one way; the timing pointed another

The first useful measurement wasn't a profile. It was three curl timings:

• A static file: 359ms
• A one-row API call (/api/articles?limit=1): 7,410ms
• Every SSR page, warm: 6,500–8,500ms

That pattern is diagnostic gold. When one page is slow, suspect that page. When everything the framework touches is uniformly slow but static files are fine, the problem lives in shared request handling, or the event loop itself is blocked and every request is just waiting in line.

One more clue: the dev server had consumed 6.6 CPU-hours in 5.5 hours of uptime. Something wasn't just slow: it was running almost constantly.

The culprit

Feed Me Data stores articles in SQLite via better-sqlite3, which executes queries synchronously on Node's single thread. That's normally a feature: no async overhead, great point-lookup latency. It becomes a trap the moment any one query gets expensive, because a synchronous query doesn't just slow its own endpoint; it stops the world.

This was the offender:

SELECT t.name, COUNT(at.article_id) as count
FROM tags t
INNER JOIN article_tags at ON t.id = at.tag_id
WHERE t.name NOT IN (...denylist...)
GROUP BY t.id
ORDER BY count DESC
LIMIT 50

Fifty rows out. 1.37 million rows in. 8,739ms, measured directly against the database. The join order forced every article_tags row through the tags join before grouping could happen.

And it ran everywhere: in the root layout load (every SSR render), in the homepage load, and (the real killer) in /api/articles, which the homepage views call on every infinite-scroll fetch. A sibling query aggregating articles-per-source added another ~570ms to the same paths.

So the "videos page bug" was actually this: you browse the site, various requests kick off 8.7-second synchronous aggregates, and then you click a nav link. Your navigation's data request joins the queue behind them. The URL changes and the page shell appears, but the content never arrives. The pages you were on took the blame for a queue they were merely stuck in. Hover-preloading on the video card grids made it worse: every card you moused over on the way to the nav bar added another request to the pile.

The fix: 80x from SQL shape, the rest from not running it at all

We made two changes, each verified before we believed it:

1. Aggregate first, join second. Group the big table by ID alone (SQLite satisfies that from a covering index), and only join names for the fifty survivors:

SELECT t.name, c.count
FROM (
  SELECT tag_id, COUNT(*) as count
  FROM article_tags
  GROUP BY tag_id
  ORDER BY count DESC
  LIMIT 50 + <denylist size>  -- headroom for filtered rows
) c
INNER JOIN tags t ON t.id = c.tag_id
WHERE t.name NOT IN (...denylist...)
ORDER BY c.count DESC
LIMIT 50

8,739ms → ~110ms. Same result set.

2. Cache it, because it barely changes. Tag counts only move when ingestion writes new articles. A module-level cache with a 5-minute TTL serves the hot path, and the ingestion pipelines explicitly clear it when they finish: event-driven invalidation for the writers we control, TTL as the staleness bound for the ones we don't.

The results, end to end:

Navigation is instant again. The fix touched about forty lines and needed neither a framework change nor a new dependency.

What we'd tell past us

Indexes were never going to save this. The table already had a covering index. A COUNT … GROUP BY over 1.37M rows has to touch 1.37M index entries no matter how clever your indexing is. When a query's cost scales with corpus size, the fix is to stop paying that cost per request: restructure it, precompute it, or cache it.

This is the OLTP/OLAP boundary, in miniature. Designing Data-Intensive Applications draws a line we'd blurred: tag counts are derived data, and we were re-deriving them from the system of record on every read. The book's prescription is to move work from the read path to the write path when reads dominate: a summary table maintained at ingest, or a materialized view. Our TTL cache is a materialized view with a cheap invalidation strategy; the incremental summary table is the upgrade path if staleness ever matters. (We also looked hard at DuckDB, the right tool for exactly this query shape, and decided it's the right tool for a different part of the app: the data-visualization endpoints that genuinely do ad-hoc analytics. For a hot path that now costs 110ms every five minutes, a second database engine is a solution shopping for a problem.)

Synchronous databases turn slow queries into outages-in-miniature. better-sqlite3's synchronous model means head-of-line blocking is your failure mode: p99 latency explodes for every endpoint, and the user-visible symptom appears wherever someone happens to click next. Which leads to the last lesson.

*Trust uniform timings over user intuition about where. The bug report said "videos and podcasts." The timings said "everywhere." When those disagree, the timings are telling you the problem is in the queue, not the page. Time a static asset, then a trivial endpoint, then the suspect page. The shape* of those three numbers locates the bug faster than reading any amount of page code.

The queue doesn't care which page you blame. It just wants you to stop putting 8.7-second queries in it.