For sequential pages in Cloudflare D1, keyset (cursor) pagination is usually a better fit than a deep OFFSET: it continues from the last row’s sort key instead of skipping an ever-larger prefix. But there is no universal D1 row-read count or guaranteed speedup. The actual work depends on the query plan, indexes, and data, so compare D1’s rows_read for representative requests. Keyset pagination also avoids a particular duplicate-row problem when inserts shift an OFFSET page boundary; it does not freeze results across requests.
Why deep OFFSET can read more rows than it returns
D1 uses SQLite query semantics and can be queried through Workers bindings, the REST API, or Wrangler. With LIMIT and OFFSET, the database must advance through the ordered results to get past the offset before returning the requested page. As the offset grows, that can mean more work even when the page size stays the same.
For example, a page of 20 rows at offset 20,000 still asks the query to move past the earlier ordered rows. The exact amount of work is not a fixed formula or D1-wide constant: filters, indexes, table contents, and the chosen plan all matter. D1 query metadata reports rows_read, which counts rows read during execution, including index rows; it can exceed the number returned.
Choose pagination based on how people navigate
| Consideration | OFFSET pagination | Keyset pagination |
|---|---|---|
| Navigation | Supports jumping to a numbered page. | Continues from a cursor; arbitrary page-number jumps are not its natural use. |
| Work at increasing depth | May need to advance past earlier rows; measure rows_read on the actual query. |
Can seek from the last sort key when predicate, ordering, and index align; verify the plan and metadata. |
| Ordering | Needs deterministic ordering to make page contents meaningful. | Needs deterministic ordering and a unique tie-breaker if the main sort value repeats. |
| Inserts between requests | An insert before a page boundary can shift positions and repeat or skip rows across pages. | Avoids that positional shift; later requests may still include new rows after the cursor. |
| Best fit | Interfaces where users need direct page-number navigation. | Feeds, “load more” flows, or sequential traversal. |
Replace OFFSET with a cursor on a stable ordering key
Ascending unique key
If id is unique and increasing, this OFFSET query is straightforward but may do more work at deep page positions:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsSELECT id, created_at, title
FROM posts
ORDER BY id
LIMIT ? OFFSET ?;For sequential traversal, pass the last id returned on the prior request:
SELECT id, created_at, title
FROM posts
WHERE id > ?
ORDER BY id
LIMIT ?;The cursor is a boundary, not a page number. On each request, use the final key from the previous response, then request the next batch.
Descending or non-unique sort keys
For descending order by a unique id, reverse both the comparison and ordering, using id < ? ORDER BY id DESC. If the sort key can repeat, add a unique tie-breaker to both the ordering and cursor. For example, a traversal ordered by created_at, id needs both values in its cursor and a matching lexicographic predicate, conceptually (created_at, id) > (?, ?) for ascending order. Confirm that the exact SQL syntax and index plan are supported for your query. Avoid nullable cursor columns unless the query explicitly handles null ordering.
A suitable index should align with the filter and ordering used by the query. Without it, a keyset predicate alone does not guarantee bounded work. Cloudflare’s D1 indexing guidance recommends indexes to reduce rows read, while noting that indexes can add write work when indexed columns are updated.
What inserts between page requests do
OFFSET can repeat a row after an earlier insert
Suppose the first request uses ORDER BY id ASC LIMIT 20 OFFSET 0 and returns IDs 1 through 20. If a row with ID 0 is inserted before the next request, LIMIT 20 OFFSET 20 now starts at ID 20. That row can appear on both pages because the insertion shifted the positional window.
Keyset continues past a boundary, not through a frozen snapshot
If the next query is WHERE id > 20 ORDER BY id LIMIT 20, an inserted row with an ID below or equal to 20 is not included in that continuation. A newly inserted row with a larger ID can appear on a later page if the cursor has not passed it. This is often appropriate for a live feed, but it is different from an export that must represent one fixed set of rows. Updates to sort keys and deletions can also alter which rows appear during traversal.
A cursor provides continuation from a boundary; it does not by itself establish a snapshot spanning separate HTTP requests. For a stable export, one application-level option is to capture a maximum ID at the start and apply id <= cutoff on every page, provided that the key and application’s write behavior make that boundary suitable. Any transaction or snapshot strategy should be verified against the consistency guarantees of the specific design.
Measure rows_read and inspect the query plan
Do not infer D1 cost from the page size alone. The D1 API reference defines meta.rows_read as rows read during SQL execution, including indexes, and notes that not all rows read are necessarily returned. Metadata also exposes SQL duration excluding network time.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Best Value
- Run representative requests. Use the same filters and selected columns for the OFFSET baseline and keyset candidate. Test shallow and deep pages, and realistic cursor values.
- Record returned rows and metadata. Capture page depth or cursor, returned row count,
rows_read, and SQL duration for each request. Do not treat one run as a universal result. - Inspect the plan. Use
EXPLAIN QUERY PLANwith the query to investigate whether SQLite reports a fullSCANor aSEARCH ... USING INDEX. Check that the index matches the predicate and ordering. - Evaluate the workload trade-off. An index may reduce read work but add write work when indexed values change. Compare both against the application’s actual read and write patterns.
| Query | Page depth or cursor | Plan | Returned rows | D1 rows_read | SQL duration |
|---|---|---|---|---|---|
| OFFSET baseline | Record actual offset | Record actual plan | Measure | Record metadata | Record metadata |
| Keyset candidate | Record actual cursor | Record actual plan | Measure | Record metadata | Record metadata |
Keep D1 limits in perspective
Cloudflare’s D1 Limits page, last 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 as of that update. These are platform limits, not an OFFSET threshold, page-size cap, or promise that a pagination query will finish within a particular time.
Quick Recap
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.
