How to Keep Your UI Under 250ms With Large Datasets (SQLite/D1 at Scale)

I run a B2B monitoring app that ingests company filings from an official public data feed every day, enriches them (revenue, industry codes, executives, real cash position extracted from financial filings pulled from a national business registry), and exposes them in filterable tables: new companies, prospecting targets, acquisition opportunities, an executive directory.

The app was running on a single region, roughly 7,700 companies, with a handful of accounts. The initial design followed a common and pragmatic pattern: the server sends a chunk of data (up to 2,000 rows per screen), and the client (Tabulator, a JS table library) handles sorting, filtering, and pagination in memory, in the browser. Fast to write, snappy to use, 0 round-trips to the server once the data is loaded.

A routine metrics check surfaced the problem: 12.9 million rows read per day on D1, against a free-tier ceiling of 5 million. On 1 region. Scaling to the entire country (roughly 4 million active companies, about 100 regions) and to a few hundred or thousand user accounts would multiply that load by a factor no "pump everything, filter client-side" architecture can absorb.

4 D1 Quirks That Bite Later

D1 is Cloudflare's distributed SQLite: each database runs on a single SQLite instance, replicated at the edge. A few properties change how you have to write queries, compared to a managed Postgres.

Billing counts rows READ, not queries or rows returned. A SELECT * FROM catalog ORDER BY cash_position DESC LIMIT 20 that has to sort the entire table before cutting the first 20 rows consumes a full scan, even though the response only contains 20 rows. That's exactly what happened here: on some routes, we measured an amplification factor of 37x (17,900 rows read for 478 actually returned to the client), and up to 156x on an admin stats screen. D1 doesn't care what you send back to the client, only what you touched getting there, kind of like HAL 9000 grading you on which pod bay doors you opened, not which ones you actually walked through. The cost doesn't show up in the HTTP response size. It only shows up in the query plan.

2 ways to hit the same database, with different constraints. The Cloudflare Worker uses a native D1 binding (env.DB), already async, with no particular round-trip limit to manage. The ingestion cron, on the other hand, runs on an external server (off the Cloudflare edge) and talks to the same database through the D1 REST API (HTTP), which caps out at roughly 100 bound variables per query. Any IN (...) clause built from a dynamic list of company identifiers has to be chunked (IN_CHUNK_SIZE = 90 in the code, with margin under the real limit). Ignoring this constraint breaks nothing in dev (local SQLite has no such limit). It only blows up in production, against the REST API.

No PRAGMA. D1 doesn't support classic SQLite pragmas, which rules out some usual idempotency shortcuts (PRAGMA user_version to track migrations, for instance). The schema is applied via CREATE TABLE IF NOT EXISTS, and adding a column goes through an ensureColumn function that inspects PRAGMA table_info at runtime (this particular pragma does work, only the session-configuration pragmas are missing) to decide whether an ALTER TABLE is needed.

HTTP conditional caching as a safety net, not a crutch. The 250ms-per-interaction budget had to hold without depending on a cache, but a cache remained the best way to avoid strictly unnecessary work: 2 identical calls between two ingestion runs shouldn't cost more than 1 real computation. The mechanism: a single-row table (dataset_meta, column v) gets incremented by any writer that touches catalog or user state (bumpDatasetVersion). Every page response computes an ETag ${version}-${hash(query_string)} and compares it, right at the top of the route, against the If-None-Match header sent by the client. On a 304, execution stops literally at that line, before any expensive query construction (pagination, counts, facets):

const meta = await db.get<{ v: number }>(`SELECT v FROM dataset_meta WHERE k = 'version'`);
const etag = `${meta?.v ?? 0}-${hashQuery(url.search)}`;
if (c.req.header("if-none-match") === etag) return c.body(null, 304);
c.header("ETag", etag);

I've been just as happy shipping a backend on Convex paired with Claude Code, a very different set of tradeoffs. For this project though, distributed SQLite at the edge won on cost and latency for a dataset that's read far more than it's written.

Remote-Only, Not Client-Side

The decision, written down in a reference document that would guide everything after it: remote-only. All the logic (pagination, sorting, filtering, faceting, counting) moves server-side. The client never receives more than 1 page (100 rows max), regardless of the real size of the dataset. Budget: 250ms per interaction, at any scale.

3 principles run through everything else.

1. Data is global, a view is a filter. A per-user table that duplicated which companies each account could see was removed. It duplicated something computable (an account's scope is its criteria applied to the global dataset), was expensive to keep in sync, and kept drifting from the code paths that bypassed it anyway. What an account sees is now a SQL predicate derived from its scope (regions, industry codes, minimum revenue), applied at read time on a single dataset. Only state (flags, follow-up notes, manual additions) stays per-user. That's genuinely account-specific data, not a view of the same data.

2. Aggregates are computed on write, never on read. A denormalized service table (catalog, 1 row per company) carries every column a screen displays, sorts, or filters on: latest filing, consolidated financials, insolvency status, whether a website exists, group membership. Every writer that touches a source table (companies, filings, financials, plus secondary signal tables like insolvency proceedings or group links) recomputes the affected row right after writing. A nightly job recomputes everything as a drift safety net.

The computation itself happens outside the database: the code reads source tables in batches of 80 identifiers (CATALOG_CHUNK = 80, a margin under the REST limit), assembles and computes the columns in JavaScript, then writes 1 row per company via a roughly 60-parameter INSERT ... ON CONFLICT. Writes within a batch run at bounded concurrency (10 at a time), recomputing ~7,700 companies serially from the external cron (1 HTTP round-trip per company) would have taken an unreasonable amount of time.

3. 0 unindexed scans on the hot path. Every sort or filter exposed to the user must map to an existing composite index. A registry of "sortable" columns is the source of truth (SORTABLE in page-query.ts): not in the registry, not sortable in the UI. Every sort x filter combination is covered by a test that runs EXPLAIN QUERY PLAN and fails if the query hits a full SCAN instead of an indexed SEARCH.

OR vs. UNION ALL: The Real Story

An account's visibility naturally reads as an OR of conditions: the region is in its list, OR the industry code is, OR revenue is above its threshold, plus any companies it added manually (an EXISTS against an additions table). The problem shows up with that last EXISTS. Measured with EXPLAIN QUERY PLAN, on tables ranging from 240 to 50,000 rows (to rule out a small-volume artifact), 4 configurations emerged:

• Scope alone, no manual additions, simple sort: the composite index is used directly (SEARCH catalog USING INDEX idx_cat_region_cash), bounded cost.
• OR including manual additions, single-column sort: the index is still used, but as an ordered scan rather than a bounded seek (SCAN catalog USING INDEX idx_cat_cash). Acceptable.
• OR including manual additions, multi-column sort (the real case on several screens): SQLite abandons the index and materializes a temporary B-tree for sorting (SCAN catalog + USE TEMP B-TREE FOR ORDER BY). That's a full table scan on every page, exactly the behavior we set out to eliminate.

The fix wasn't avoiding the EXISTS. It was splitting the query into 2 disjoint branches joined by UNION ALL: a "scope" branch (without manual additions) that keeps the composite index regardless of sort order, and a "manual additions" branch (a handful of rows, direct primary-key seek) that explicitly excludes the scope to guarantee no row appears twice. The final sort only runs on the bounded union of the two branches (at most 2x the requested page size), never on the full table. The original flat OR predicate didn't disappear, it's still used as-is for counts and facets, paths where no sort is involved and where it's still a perfectly good fit. Only the paginated-and-sorted path needed a different version. The lesson: on SQLite, as on many engines, a logically correct disjunction can compile to radically different execution plans depending on whether a multi-column sort is present, and measurement (EXPLAIN QUERY PLAN) beats intuition every time.

Rolling It Out in Batches

Rewriting everything at once wasn't realistic: 5 screens, several different sort bases, a live system with active users. The work was split into batches, each shipped, merged, and verified in production before starting the next.

Batch 1: targeted patches (before the rewrite)

Before laying down the new foundation, a patch pass attacked the most expensive read paths in the existing architecture without changing the architecture itself:

• The executive directory reloaded, on every page view, the financials of every company the user had ever discovered (roughly 30,000 rows reread per visit), when a targeted lookup by company identifier was enough.
• A backlog-stats function against the national registry ran an expensive correlated EXISTS, short-circuited by a simpler condition whenever it was sufficient.
• Executive search used an unindexed CROSS JOIN between 2 tables, replaced with an indexed join (with query-plan tests added as guardrails).
• A daily digest posted to a team chat tool unconditionally ran several expensive COUNT(*) queries (roughly 40,000 rows each), they now only run if the digest actually needs the data.
• A composite index on a parcel-data table, with an ANALYZE to refresh the planner's statistics behind it. The one-time cost of the ANALYZE was measured (984,000 rows read, a one-off), for a recurring gain of roughly -360,000 reads/day on the acquisition screen, later verified as a COVERING INDEX.

Cumulative gain from this batch: roughly -3.4 million reads/day, from a starting point of 13.25 million/day.

Batch 2: the remote foundation and the first migrated screen

This batch introduces catalog, the generic query builder (page-query.ts, with the SORTABLE registry and the UNION ALL decision described above), the ETag/304 mechanism, and migrates the first screen (the main discoveries table) to full remote pagination.

2 other notable fixes in this same batch:

• A per-user discovery counter, previously recomputed on the fly by an expensive correlated COUNT on every admin account-list view (measured at roughly 1 million reads/day), was materialized and incremented directly by the 4 writers involved, with a daily reconciliation job to catch any drift.
• 2 partial indexes for 2 backfill queries that were systematically scanning the entire companies table looking for a tiny subset of rows.

Cumulative gain from this batch: roughly -2.5 million additional reads/day.

Batch S: decoupling the marketing site from the product

A single Cloudflare Worker served both the public marketing site and the authenticated app, routed by hostname at runtime. The issue wasn't performance, it was deployment coupling: shipping a marketing page redeployed the product right along with it, with the risk that an unapplied database migration could break the app in the process. Splitting into 2 separate Workers (2 wrangler.toml files, 2 deploy commands) removed that coupling: each one only knows its own routes, no more double mapping between hostnames and paths to keep in sync.

Batches 3 and 4: the remaining screens, on the same foundation

The prospecting and acquisition screens were migrated without building a new query generator: a screen's scope (nationwide for prospecting, restricted to followed regions for acquisitions) is expressed as a degenerate filter object passed into the same buildPage, with all the business-specific selectivity for those screens (targeted fiscal year, sector exclusions, dismemberment thresholds...) added as new optional fields on the existing filter type rather than as a separate code path.

The executive directory needed more work: the old implementation aggregated each executive's companies in JavaScript, on every request, from a CROSS JOIN between all companies and all known executives, the kind of boss-fight query that one-shots your D1 quota, no XP gained (the source of a one-off spike to 22 million rows read in a single day). A dedicated materialized table (1 cluster per executive after name-variant deduplication, roughly 10,100 clusters on initial backfill) replaced that on-the-fly computation, with the same discipline as catalog: recomputed by the relevant writers, with a daily recompute safety net.

3 traps hit along the way, worth generalizing well beyond this project:

A deduplication bucket should never rely on a column that can be empty. Executive clustering groups by surname. An early version read directly from a dedicated column that was empty for any legacy data stored as a single plain-text string (name not already split into first/last). The silent result: 0 clusters produced for any executive coming from that legacy format. The fix derives the surname from the display label itself (last token, normalized), an operation that doesn't depend on any column that might be missing.

A global materialization should never be served raw to an individual user. An executive's total company count and cumulative cash position are statistics computed once for the entire dataset, valid for every account at the same time. The same executive can appear in multiple accounts' scopes. Serving these columns raw leaks, as soon as a cluster has even 1 company in 1 account's scope, the aggregate total across companies belonging to other accounts that account can't actually see. The fix re-aggregates these columns in JavaScript after pagination, filtered to the user's real scope (bounded to the displayed page, so cheap). A pre-existing multi-user isolation test, silent until then for lack of a triggering scenario, caught the issue once completed.

An empty scope is not a national scope. A new builder's scope calculation had written, in effect, if (!regions.length) return null, where null meant "no restriction": an account with 0 active criteria fell into that branch and saw the entire national scope instead of nothing. The fix: an explicit 1=0 clause for that specific case. The same logical bug already existed, correctly handled this time, in the project's very first query builder, proof that a good pattern written once doesn't automatically propagate to new code until it's documented as a cross-cutting rule to check systematically.

Batch 5: search at scale, quotas, and the big cleanup

3 closing efforts, in the order they were tackled.

Full-text search via FTS5. Name search used to scan the entire table with instr(UPPER(name), ...), workable at 7,700 rows, impossible at national scale. A 30-minute spike confirmed FTS5 (SQLite full-text search) was available on D1, both remote and local, with a trigram tokenizer (indexing by groups of 3 characters, which allows mid-word matching, "phin" finding "DOLPHINS", without depending on a prefix). The virtual table:

CREATE VIRTUAL TABLE IF NOT EXISTS catalog_fts USING fts5(
  company_id UNINDEXED,
  name,
  city,
  executives,
  tokenize = 'trigram'
);

This table's rowid is cast directly from the company identifier (CAST(company_id AS INTEGER)): that identifier is always 9 digits, comfortably fits a 64-bit integer, and the mapping is injective, which avoids maintaining a separate rowid-to-identifier lookup table. 2 subtleties surfaced during testing: a search term under 3 characters can't form a single trigram, and MATCH then consistently returns 0 rows (without raising an error) rather than rejecting the query, hence an explicit fallback to the old instr() for that specific case. Escaping a user-supplied term as a literal FTS5 phrase (doubling internal quotes) is enough to guarantee no term can be interpreted as a query operator (AND/OR/NOT/*), tested explicitly against negative cases.

Anti-scraping quotas. A dedicated table (serve_quota, keyed on (user_id, day)) counts rows actually served (not queried) per account per calendar day. The check runs at the very end of the route, once the response's actual row count is known:

if (total >= limit) {
  // one alert per (user, day), an `alerted` column prevents spam
  return { ok: false, total, limit };
}
// otherwise: atomic increment, the request proceeds normally

The default threshold (20,000 rows/day) is overridable per account through a dedicated column, deliberately kept separate from the existing preferences JSON blob: that blob only round-trips a subset of fields on every user save and would have silently overwritten an override stored inside it. Combined with the hard cap of 100 rows per page already in place, the worst case an account can now impose on the server is bounded on both sides: per request, and per day.

The big cleanup. Once an exhaustive code search confirmed no reader or writer still touched the old per-user discoveries table (made redundant by batch 2), it was dropped via a destructive migration, with a verified backup taken beforehand and explicit confirmation before applying it. The remaining client-side "pump everything, filter in memory" code was removed from the last screens still carrying it.

The Test Suite Went Hermetic

Along the way, a side issue surfaced: the test suite sometimes hit real external APIs (government open data) instead of mocks, at poorly isolated boundaries. Hundreds of real network calls on every full run, a random government API having a bad day was basically our own personal raid wipe. A guardrail was added at the test-runner level: any outbound network call not explicitly mocked is now rejected with an explicit error, instead of silently hitting the real network.

(Completely unrelated: my Mac's fan started sounding like a jet engine that same week, from a background Docker container nobody remembered to stop. Different problem, same feeling of relief once it's found.)

A positive side effect: the suite went from 14.4 to 10.9 seconds of runtime, and, more importantly, it became deterministic. No more false positives tied to a third-party API's momentary availability.

6.74 Million Reads a Day

1 day after the last batch shipped (a measurement that includes backfill recomputation from batches 4 and 5, so less favorable than a normal steady-state day): 6.74 million rows read over 24 hours, down from 12.9 million at the start, against 1.15 million rows written in the same period (the backfills). Nearly a 50% cut in read volume, over a period that still includes a one-off write spike absent from the initial measurement.

Did We Actually Hit the 250ms Budget?

Read volume is one axis. The other axis is the number the whole rewrite was built around: 250ms of server processing per interaction. This is the same instinct that pushed me to give Claude a tool to watch my SaaS apps instead of eyeballing dashboards one by one. Read counts don't answer the latency question on their own, so a Server-Timing header was shipped to production (commit 714ef83) that exposes total (end-to-end server processing) and db (cumulative time spent inside D1). Methodology: 10 runs per endpoint, sanity-checked counts, measured from Cloudflare's Paris (CDG) edge.

Server-side processing latency in milliseconds, excluding network and TLS, by endpoint (D1 requests per page, then median, p90, max):

• /api/decouvertes sorted by cash position: 9 requests, median 214, p90 350, max 406
• /api/decouvertes with facets: 9 requests, median 209, p90 227, max 446
• /api/decouvertes?q= (FTS5 search): 7 requests, median 156, p90 228, max 470
• /api/prospects: 7 requests, median 124, p90 139, max 141
• /api/cession: 8 requests, median 160, p90 166, max 230
• /api/dirigeants sorted by cash position: 13 requests, median 220, p90 231, max 313

End-to-end latency measured from a French client: roughly 250 to 420ms, which is the server processing time above plus about 130ms of network and TLS overhead.

3 things worth taking away from this:

The 250ms budget holds at the median, not at the tail. Every screen comes in under budget in median terms (124 to 220ms), but the distribution has a tail that occasionally breaks it: p90 up to 350ms, isolated peaks around 450ms. That tail is D1's own variable latency (replication, cold paths), not application code. Worth saying plainly: the median validates the target, the p99 doesn't.

Server time is essentially 100% D1 time. On every run, total tracks db almost exactly. The application-level work (building the query, paginating, serializing 100 rows to JSON) is sub-millisecond.

At this scale, latency is a database problem, not a compute problem.

The real lever left is the number of D1 round-trips per page, ranging from 7 for prospecting to 13 for the executive directory, which is also the slowest endpoint. That's the next optimization target if the goal is to push latency down further: batching or merging those queries.

4 Ways to Shave Off the Next 100ms

The D1 round-trip count is the lever, but here's what pulling it actually looks like, roughly ordered by effort versus payoff.

1. Parallelize independent queries, the big win for low effort. On /api/decouvertes (app.tsx:3155-3196), the page rows, the 2 countPerimeter calls, and the facet queries don't depend on each other. 3 waves (check the ETag, then everything else through Promise.all, then the quota check) would take the median from roughly 214ms to somewhere around 70-100ms. Nothing changes in the adapter, portable across BunDb and D1Db as-is. Same story on /api/dirigeants, the slowest endpoint: 9-13 sequential requests, including the allInChunks calls that parallelize just as easily.

2. Extend ETag/304 to the other routes, minimal effort. Only /api/decouvertes has the ETag (app.tsx:3093-3105). /api/dirigeants, /api/prospects, and /api/cession redo everything on every visit. The plumbing (dataset_meta.v) already exists. A 304 means 1 D1 request instead of 13. Big win on revisits, going back to the list from a detail view, 0 gain on first load.

3. Smart Placement, 2 config lines worth measuring. [placement] mode = "smart" in wrangler.toml moves the Worker close to D1, each round-trip could drop from roughly 24ms to 1ms. The catch: if D1 is already sitting somewhere in Western Europe near the CDG edge, the gain is modest, and the hop between the user and the Worker can stretch instead. Reversible, and the Server-Timing header already in production makes a clean before/after comparison possible.

4. Merge queries, worth squeezing once the rest is done. The 2 countPerimeter calls collapse into 1 query with conditional aggregates, the facet queries collapse into a UNION ALL, and enforceServeQuota (a GET followed by a RUN) collapses into a single UPSERT. Or add batch() to the Db interface, 1 round-trip for N statements. More invasive, saved for a second pass.

Ingestion Still Runs on 1 Region

The read architecture is ready for the target scale (the whole country, several hundred to several thousand accounts). What isn't ready yet: ingestion itself, which today only runs for a single region, needs to be parallelized to cover the entire country without blowing up daily run times. And one open question isn't technical, it's economic: even optimized, read volume at national scale and several hundred accounts will almost certainly exceed Cloudflare D1's free tier. Moving to a paid plan is a cost decision, not an engineering one, and I think it's waiting for its moment.

4 Rules Worth Stealing

"The server sends everything, the client filters" is a reasonable choice at small scale. Not a beginner mistake, more a pattern that shows no symptoms until it breaks all at once, the day volume crosses a threshold no functional test exercises. The good news: the signal that triggers the rethink (a measured reads-to-ceiling ratio, not a hunch) costs almost nothing to instrument from day one of a project, well before you need it.

The rest comes down to 4 rules that generalize well beyond this project: materialize aggregates on write instead of recomputing them on read, put automated guardrails in place (a test that fails on an unindexed query plan) instead of relying on human review, treat any aggregate computed at a global scale as a potential leak until it's been explicitly re-scoped to the right visibility level before being served, and be wary of a logical disjunction that looks correct on paper but that the query planner compiles into a different execution plan depending on the requested sort, something only measurement (EXPLAIN QUERY PLAN) reveals.

Sources

• Cloudflare D1 documentation
• SQLite FTS5 documentation

This post may contain affiliate links. If you click them, I might earn a small commission (costs you nothing, and helps me keep shipping quality articles every day for your reading pleasure).

---

When your SQLite queries read 37x more rows than you return, the bill arrives before you notice. The demo-vs-product checklist in the kit walks through query performance audits (EXPLAIN ANALYZE, indexing, structural logging) so you catch these before production scaling.

→ Get the welcome kit