Measure Cloudflare D1 usage instead of guessing: scanned rows, indexes and caching
A practical method to evaluate D1 reads and writes and measure the effects of SQL, indexes and cache choices.
HTTP requests are not a database cost metric
Counting Worker requests alone is a poor proxy for D1 usage. One HTTP request can execute many SQL statements, and the number of rows a query scans can differ from the number it returns. Start by attributing rows_read and rows_written to specific endpoints and queries. For example, a page that counts an entire large table on every visit may scan many rows even when only a few records are displayed.
Compare indexes with query plans
A compound index aligned with predicates and ordering can reduce many full-table scans. Yet additional indexes require maintenance on writes, so adding them indiscriminately is not free. D1 is SQLite-based: inspect EXPLAIN QUERY PLAN, test against representative data distributions and compare the execution path. Record scanned rows, write overhead and CPU behavior rather than relying on a single millisecond reading.
EXPLAIN QUERY PLAN
SELECT id, title FROM portal_news
WHERE status = 'published' AND event_year = 2025
ORDER BY event_date DESC LIMIT 12;Caching helps only when freshness is correct
A short-lived response cache for public, infrequently changing pages can lower Worker and D1 activity. But an excessive TTL that hides newly published content produces a fast yet incorrect system. Define an explicit freshness contract between the admin write and the public view, using versioned keys or suitable invalidation when content changes.
Run a controlled comparison
Compare old and new queries on the same dataset under comparable traffic. Report errors, response latency, D1 rows_read/rows_written and cache hit rate together. A query that silently omits records cannot be declared successful because it is faster. Follow the linked D1 pricing documentation for current limits instead of assuming static prices.
Comments
0 real commentsNo real reader comments yet.
✎ Write a comment