One production content workload I worked on was reading roughly 344,000 Cloudflare D1 rows per hour.
After the architecture changes, the measured rate was roughly 4,000 rows per hour.
That is about a 98.8% reduction.
Query executions also fell from roughly 3,000 per hour to 376 per hour.
There was no single magic index.
The improvement came from changing the question the application asked D1:
Instead of asking the database to rebuild the same public view for every request, can we compute bounded read models only when they actually need to change?
That shift—cache first, database second—made the biggest difference.
The starting architecture
The application was a content-heavy SSR site on Cloudflare Workers + D1.
The expensive paths included:
- homepage category sections;
- latest/trending content;
- related stories;
- tag matching;
- per-request public content aggregation;
- duplicate/hydration reads.
The database was doing too much derivation in the request path.
A visitor asking for one article could cause the system to rediscover related stories from the full content graph. A homepage refresh could recompute top stories across categories. Cache expiry could trigger the same rebuild several times.
The system worked functionally.
Its read economics were wrong.
Step 1: rank by total rows read, not by how scary the SQL looks
We used D1 analytics to identify the statements consuming the largest share of rows.
Examples from the investigation included:
- one related-content statement: ~147,820 rows across 85 executions;
- other related/tag work consuming large scan totals;
- category/recent homepage queries with high execution and scan ratios;
- later, one global homepage
WITH rankedrebuild accounting for about 76% of a measured window.
This stopped us optimizing code by intuition.
The first target should be the SQL that moves the usage graph.
For the metric distinction, see D1 Rows Read vs Query Count.
Step 2: move public cache in front of D1
The original mental model was:
request → SSR → D1 → render
The new model became:
request → edge/cache snapshot → render
with D1 used on controlled refresh paths.
For public data that can tolerate short freshness windows, this is a major architectural difference.
Examples:
- homepage snapshot;
- category snapshot;
- related-content snapshot;
- public site configuration.
A normal page request should not need to ask D1 to rediscover data that hundreds of previous visitors already requested seconds ago.
Step 3: use stale data safely during refresh
A naive TTL cache creates a cliff.
At 12:00:00 the cache expires. Ten users arrive at 12:00:01. Ten requests rebuild the same expensive data.
That is a cache stampede.
We changed the refresh behaviour so the system could:
- serve a last-known-good stale snapshot for an acceptable window;
- trigger a controlled rebuild;
- dedupe concurrent rebuild attempts;
- replace the snapshot only after a successful refresh.
This reduces D1 pressure and improves resilience when the database is temporarily constrained.
Not every data type can be stale. Payments, authorization decisions and mutable customer balances require stronger consistency. Public content recommendations do not have the same requirement.
Step 4: replace live related-content scans with snapshots
Related-content was one of the biggest offenders.
The page needed a handful of related stories, but the SQL repeatedly evaluated tags/categories/candidates.
Instead of recalculating that relationship for every article request, we moved toward an independent related-content snapshot with a refresh lifecycle.
The model became:
- request article;
- read its existing related snapshot;
- if snapshot is fresh, use it;
- if stale, serve the previous safe value and rebuild once;
- persist the new bounded result.
That eliminated a large recurring tag-matching scan from the hot request path.
The user does not care whether four related cards were calculated 30 minutes ago or 30 milliseconds ago if the content remains sensible.
Step 5: replace one global ranking query with bounded top-N queries
A later optimization taught an important lesson.
We had consolidated homepage category work into a global ranking query using a WITH ranked approach.
It reduced round trips but became a rows-read hotspot because it ranked a broad candidate set.
The better design was:
- one small top-7 query per category;
- each query shaped to use the public/category/recency index;
- queries batched into one D1 round trip.
So the network/database call count stayed controlled while each SQL statement examined a small relevant range.
This is why I do not optimize for “one SQL statement.”
I optimize for bounded work.
Step 6: add indexes that match public read paths
Indexes were still important.
A useful public recency index can let D1 answer questions such as:
Give me the latest published rows in this category.
without scanning the entire article set.
But an index only helps when query predicates/order can use it.
For every hot statement, check EXPLAIN QUERY PLAN.
Look for:
- table scans;
- temporary sort structures;
- unused indexes;
- broad
OR/EXISTSconditions; - filters applied after an expensive intermediate result.
Then shape the query around the access path you actually need.
Step 7: remove duplicate hydration/loader fetches
Once the large scans were controlled, duplicate executions became more visible.
One example was a client-side breaking/ticker component fetching data immediately after SSR had already supplied it.
The fix was small, but repeated across traffic it mattered.
Audit:
- nested loaders;
- page metadata;
- hydration effects;
- global components;
- client query libraries;
- route transitions.
If the same public data is fetched twice during one page lifecycle, decide which layer owns freshness.
Step 8: separate rebuild traffic from user traffic
A powerful architectural improvement is to make derived public data its own resource.
The homepage snapshot is not “whatever SQL the homepage happens to run today.”
It is a read model with:
- value;
- built-at timestamp;
- expiry/freshness rule;
- last successful value;
- rebuild function;
- rebuild dedupe/lock;
- observability.
This makes performance predictable.
You can measure rebuild cost separately from page traffic and change the derivation without rewriting every route.
The before/after model
Before:
Visitor
-> Worker SSR
-> latest query
-> category queries
-> tag/related scans
-> image/metadata queries
-> render
Browser hydration
-> fetch some of it againAfter:
Visitor
-> Worker
-> cache/read snapshot
-> render
Controlled refresh
-> bounded indexed D1 queries
-> build snapshot once
-> cache/store resultThe database still exists.
It is no longer in the center of every public read.
Measured result
The key measured change was approximately:
| Metric | Before | After |
|---|---|---|
| D1 rows read/hour | ~344,000 | ~4,000 |
| Query executions/hour | ~3,000 | ~376 |
| Rows-read reduction | ~98.8% |
Traffic windows are never perfectly identical, so I treat these as operational measurements rather than laboratory constants.
But the order-of-magnitude change was large enough to validate the architecture.
We had removed systemic repeated work.
What I would do first on another D1 app
If I inherited a D1 app with high row usage today, I would:
- export/query the top statements by rows read;
- calculate rows/execution;
- map each hot SQL statement to a route or rebuild;
- fix full scans and missing indexes;
- identify public data that does not need per-request freshness;
- create cache/snapshot ownership for that data;
- dedupe rebuilds;
- remove SSR/hydration duplication;
- add stale/circuit-breaker behaviour for optional public features;
- remeasure rows/hour and queries/hour.
That sequence gives you evidence after every change.
Do not optimize yourself into stale business truth
Cache-first is not a universal consistency model.
Use it aggressively for derived/public information such as:
- news sections;
- recommendations;
- marketing content;
- event schedules that change infrequently.
Be much more careful for:
- payment verification;
- inventory reservations;
- permissions;
- balances;
- unique booking slots;
- write-after-read business rules.
Architecture is choosing which data can be stale and which data must be authoritative now.
The bigger lesson
The 98.8% reduction did not come from “making D1 faster.”
It came from asking D1 fewer, smaller, more intentional questions.
That is usually the stronger optimization target.
If your Cloudflare app is near D1 limits and you want to know whether the fix is indexes, caching, query redesign or SSR architecture, contact me or see my MVP development service. I can help build the rows-read budget before the database becomes an outage trigger.
