For sequential pagination in Cloudflare D1, keyset (cursor) pagination is usually a better fit than a deep OFFSET: it continues from the last row’s ordering key instead of advancing past an ever-growing number of earlier rows. But there is no fixed D1 row-read count or guaranteed speedup. Compare the actual query plan and meta.rows_read for your schema. Use OFFSET when users need numbered-page jumps; use a cursor for ordered traversal, and decide separately whether the results should include new inserts or represent a stable export.
How many rows does D1 read for a deep OFFSET?
There is no universal count. A deep offset can require the database to advance through earlier rows in the ordered result before it returns the requested page, so the work may grow with page depth. The exact count depends on the SQL, table data, indexes, and query plan; it cannot be inferred reliably from the number of rows returned.
As an Amazon Associate I earn from qualifying purchases.
D1’s Query API defines meta.rows_read as the number of rows read during SQL execution, including index rows; not all rows read are necessarily returned. D1 also reports SQL duration, excluding network time. See Cloudflare’s D1 observability and query metadata documentation.
D1 uses SQLite query semantics and can be queried through Workers bindings, the REST API, or Wrangler. The same pagination principles apply, but the measured plan and work depend on the query you actually run. Cloudflare’s D1 Worker API documentation describes querying D1 through Workers.
OFFSET versus keyset pagination
| Factor | OFFSET | Keyset (cursor) |
|---|---|---|
| Navigation | Convenient when users need to jump to a numbered page. | Continues from the last ordering key; best suited to sequential traversal. |
| Work at increasing depth | May have to advance past earlier rows. Measure D1 rows_read and inspect the plan. |
Can keep work bounded when the cursor predicate, ordering, and index align. Confirm with the actual plan and metadata. |
| Ordering requirements | Needs a deterministic order to make page contents predictable. | Needs a deterministic order and a cursor that captures the ordering boundary; add a unique tie-breaker if the main sort value can repeat. |
| Inserts between requests | Rows inserted before the page boundary can shift positions and cause duplicates or omissions across requests. | Avoids that positional shift, but rows inserted after the cursor may appear on a later page. |
| Stable export | Does not by itself freeze results across requests. | Does not by itself freeze results across requests; define an explicit cutoff or verified snapshot strategy if required. |
Write a keyset query with a deterministic cursor
For a table where id is unique and increasing, and pages should be returned in ascending order, the first request can fetch a page without a cursor. On each later request, pass the last id returned by the previous page:
-- First page
SELECT id, created_at, title
FROM posts
ORDER BY id
LIMIT ?;-- Later pages: bind the last id from the preceding page
SELECT id, created_at, title
FROM posts
WHERE id > ?
ORDER BY id
LIMIT ?;For descending traversal, reverse both the comparison and order: use id < ? ORDER BY id DESC. If the sort column is not unique, include a unique tie-breaker so rows sharing the same sort value are not skipped or repeated at the boundary. For example, a composite cursor for created_at and id must match the ordering:
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) > (?, ?)
ORDER BY created_at, id
LIMIT ?;Confirm the precise syntax and query plan for the SQLite version and query you use. Nullable cursor columns need explicit null-ordering treatment; otherwise the cursor comparison may not match the intended traversal. The cursor key should also reflect product behavior: an increasing ID allows later requests to see newly inserted higher IDs if they are still beyond the cursor, which may suit a live feed but not a frozen export.
What inserts between page requests do
With ORDER BY id ASC LIMIT 20 OFFSET 20, suppose the first request returns IDs 1–20. If a row with ID 0 is inserted before the second request, the ordered positions shift. The second request now starts at ID 20, repeating the previous page’s last row. This is a consequence of positional pagination, not a D1-specific benchmark.
If new rows only receive IDs larger than all existing IDs, they do not move existing rows earlier in this ascending order. A later keyset request can still return such rows when their IDs are greater than the cursor. Deletions and updates to ordering columns can also shift or move rows, so decide whether pages should reflect live changes or a fixed set.
A cursor marks where to continue; it is not a promise of a snapshot across independent HTTP requests. For a stable export, one option is to capture a maximum ID at the start and apply id <= cutoff on every page, in addition to the cursor predicate. That example is appropriate only when the chosen key and data-change rules make it an adequate boundary. Otherwise, use a separately verified transaction or snapshot design. The cited D1 documentation does not establish a cross-request snapshot guarantee.
Measure the real query plan and rows read
- Compare representative requests. Run the existing offset query at realistic depths and the proposed cursor query with the same filters, selected columns, and page size.
- Inspect the plan. Use
EXPLAIN QUERY PLAN. Cloudflare explains that the output can distinguish a fullSCANfrom aSEARCH ... USING INDEX; see Cloudflare’s D1 best-practices and indexing guidance. - Record execution metadata. Capture rows returned, D1
meta.rows_read, and SQL duration for each request. Do not treat rows returned as a proxy for rows read. - Evaluate the index trade-off. Cloudflare recommends indexes to reduce rows read, but indexes can add write work when indexed columns are updated. Judge the read benefit against the application’s write pattern using the D1 indexing guidance.
| Query | Depth or cursor | Plan | Rows returned | D1 rows_read | SQL duration |
|---|---|---|---|---|---|
| OFFSET baseline | Record actual offset | Record actual plan | Measure | Record metadata | Record metadata |
| Keyset candidate | Record cursor value | Record actual plan | Measure | Record metadata | Record metadata |
Cloudflare’s D1 Limits page, updated April 21, 2026, lists a maximum SQL query duration of 30 seconds and says each individual D1 database is inherently single-threaded, processing queries one at a time. It also lists query subrequest limits of 1,000 per Worker invocation on Workers Paid and 50 on Free. These are platform limits, not pagination row caps or an OFFSET threshold. Check the current D1 limits for the applicable plan and date.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Quick Recap
Best Value
Choose by navigation and consistency needs
- Choose
OFFSETwhen arbitrary jumps to numbered pages are a real product requirement, and measure the impact at the deepest pages users can request. - Choose keyset pagination for sequential browsing when you can define a deterministic ordering and a cursor boundary; verify a suitable index and actual plan.
- Define whether new rows should appear during traversal. A live feed and a stable export have different requirements, and neither pagination method alone creates a cross-request snapshot.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
