Keyset vs Offset Pagination: Counts and Random Access

Choose a pagination strategy from the reader's task. A sequential export, a numbered page navigator and a result with random page jumps need different state. An exact count is another requirement: selecting rows with a keyset boundary does not itself count the whole matching set.

TeaQL's existing Order examples distinguish ordinary offset/page requests, continuous ID-seek pagination and retained-ID pagination. This guide compares those choices before you select a native API. It uses the recorded fixed-data SQLite examples, with their original runtime and release qualifications.

Which pagination strategy should I use?

Reader task	Starting point	Work/state to account for
Read a small numbered page	Bounded offset/page request with deterministic ordering.	Offset, limit and optional provider count; no retained matching-ID set is needed.
Export matching rows in sequence	Continuous pagination with an eligible ID order.	Keep one context and sequence namespace. Inspect seek versus offset fallback and stop at a checked end condition.
Jump directly to page 3, back to page 1 and forward again	Retained-ID pagination for a result that fits its capacity.	Build and retain the complete ordered ID set, then fetch each page's identities. Store TTL, capacity and invalidation matter.
Display an exact matching total	Request an exact count through the chosen native terminal.	The total is separate from visible row count and from a bounded ID-scan lower bound.
Export while other writers change the data	Define a consistency contract first.	The fixed-data examples do not establish a transactionally stable snapshot across pages.

This is a capability/contract comparison, not a latency benchmark. A seek plan, an offset plan and a full matching-set COUNT perform different work; measure the provider SQL and plan for your own workload before making a performance claim.

Offset vs keyset: what changes in the row query?

An offset request selects a bounded slice at a position in an ordered matching result. A keyset request uses a previously observed sort boundary to continue through it. The recorded continuous Order recipe orders by a single ID and keeps the context that holds those boundaries through the sequence.

The native optimizer selects a seek only for an eligible request with a usable boundary. A namespace by itself does not restore a cursor in a new process. Unsupported ordering or missing state can select an explicit offset fallback; this is why callers should inspect the native observation after each request rather than assume that enabling an optimizer means every page uses keyset SQL.

For the nine-order export fixture, the buyer-only predicate matches eight Orders. The ascending export has:

Offset	Rows	Exact matching total	Recorded plan
0	3	8	OFFSET_FALLBACK:FIRST_PAGE
3	3	8	CURSOR_SEEK
6	2	8	CURSOR_SEEK
9	0	8	OFFSET_FALLBACK:CACHE_MISS

The SQL retained in the pagination receipt shows the ID boundary on seek pages. The empty final page stops this bounded export normally. Its fallback observation does not mean the export failed. Composite total/ID ordering has native fallback reasons that differ by runtime; these examples do not establish arbitrary-column composite keyset support. Ascending and descending exports are separate sequences, not proof that a caller can reverse any cursor freely.

Can keyset pagination return an exact count?

Yes, the recorded native request combines continuous row fetching with an exact matching-set count. The provider performs COUNT on every exported page. The seek boundary narrows the row fetch; it must not narrow the count to “rows remaining after this cursor”. An empty tail can therefore have zero rows and exact total 8.

This does not make COUNT free or turn the cursor into a count cache. If the consumer does not need an exact total, decide that separately at the API layer; do not silently remove a total that the consumer contract requires. Follow the native terminal and total-count carrier in the language tutorial below.

How do I support random page access?

The retained-ID profile builds the complete ordered matching ID sequence and retains it in a store. A later request can select a different slice of those identities. This supports a different user journey from following the last boundary in a sequential export.

The ten-order insights fixture includes an extra export-buyer EUR control. Retained queries filter buyer and USD, sort by total then ID, and match eight Orders. The jump sequence 6, 0, 3, 9, 0 with page size 3 yields BUILD, HIT, HIT, HIT, HIT, including the empty tail. The native labels are ID_SET_BUILD and ID_SET_HIT. Nonempty hit pages read rows using the retained identities; an empty hit may need no row query.

The explicit normal configuration is TTL 300 seconds and capacity 20 IDs. Capacity bounds identities in a matching set, not the visible page size. With capacity 4, the bounded scan observes at least 5 matching IDs and selects ID_SET_FALLBACK_LIMIT_EXCEEDED. It retains no partial set; the exact provider total remains 8. Do not display the lower bound 5 as an exact total.

The insights receipt also records empty sets, predicate switching, actual TTL expiry and invalidation of the observed query key. Retained membership does not freeze every row value or prove cross-process/distributed cache behavior. TypeScript's store and Swift's invalidation adapter are application-owned; keep their native tutorial wiring.

Keep scope and consistency explicit

A buyer, currency or tenant change must not reuse another request's identities. Apply the same authorized predicates to rows, count, summaries and retained keys. A namespace is execution configuration, not a permission grant. The v1.0 query recipes demonstrate business filtering; the separate buyer/tenant isolation tutorials explain trusted session ownership and root/buyer authorization on model v1.1.

The export and retained-ID recipes use separate fresh databases. Running the buyer-only export over the ten-order insights fixture matches nine Orders, including its EUR control; that count mismatch is a fixture/filter issue before it is a pagination issue.

For changing data, decide how additions, deletions, reorderings and authorization changes should affect the user's sequence. These fixed-fixture receipts do not supply a concurrent-write snapshot or revocation contract. Choose that integration boundary before extending the example's guarantee.

Native APIs and executable walkthroughs

Language	Continuous export	Retained IDs and random jumps
Java	Native export recipe	Store and ID-set recipe
Rust	Native export recipe	Store and ID-set recipe
TypeScript	Native export recipe	Store and ID-set recipe
Python	Native export recipe	Store and ID-set recipe
Go	Native export recipe	Store and ID-set recipe
C# / .NET	Native export recipe	Store and ID-set recipe
Swift	Native export recipe	Store and ID-set recipe

Use the API lookup for exact native method spelling, observation accessor and result carrier. The examples require the retained built workspace; clean-machine distribution remains a documented gap. For this scope, Java and Swift retain published-runtime passes. Go, TypeScript, Rust, Python and .NET remain WORKSPACE PASS — RELEASE PENDING; generator repairs pending deployment and all historical School/Order receipts remain scoped.

Diagnose the observed result

Symptom	Inspect before changing the query
Every export page is a fallback	Eligibility, sort shape, offsets and whether the same context actually survives through the sequence.
Empty page still has a nonzero total	Page rows versus the complete matching-set COUNT; the recorded tail total is correctly 8.
Random access rebuilds every time	Context/store recreation, TTL, capacity, predicate identity and actual observed keys.
Observed ID count differs from total	A bounded lower bound under overflow is different from an exact provider count.
Another buyer's rows appear	Authorized predicates and runtime condition-key identity; do not hide a scope defect by merely renaming a namespace.
The export never reaches its end	Page budget, deterministic order and intended data-consistency contract; the sample fails on budget exhaustion rather than silently truncating.

For related query design choices, compare generated query APIs with handwritten SQL. Changing the authoring API does not remove the need to choose counts, bounds, state ownership and consistency.