2026-09-04 · 8 min read
- sql
- pagination
- api-design
Pagination divides an ordered result into requests, but SQL tables have no inherent row order. A query that sorts only by a non-unique timestamp or status can return tied rows in different positions, producing duplicates and gaps even without concurrent writes.
A reliable contract defines a total order, carries every boundary value needed to continue that order, and applies filters and authorization consistently before selecting a page.
Create a deterministic total order
Append a stable unique tie-breaker to the requested business order. For a newest-first feed, ORDER BY created_at DESC, id DESC makes rows with equal timestamps deterministic.
- Keep null ordering explicit and portable where required.
- Use immutable ordering values when practical.
- Preserve database timestamp and identifier precision.
- Reject unsupported client sort fields and directions.
Understand offset behavior
LIMIT with OFFSET is simple and supports page numbers, but the database may scan or discard increasingly many rows for deep pages. Inserts and deletes before the offset shift later page positions between requests.
Offset pagination can be appropriate for small administrative lists or a consistent database snapshot. Document that an ordinary sequence of requests does not automatically share one snapshot.
Build compound keyset predicates
Keyset pagination continues strictly after the final ordered tuple from the previous page. For descending created_at and id, the predicate must account for an earlier timestamp or the same timestamp with a lower ID, matching the ORDER BY directions exactly.
- Encode every cursor component with a versioned schema.
- Bind cursor values as typed parameters.
- Authenticate opaque cursors if clients must not modify them.
- Use a matching compound index and inspect the query plan.
Test movement and boundaries
Use Flashman's SQL formatter for query review, timestamp converter for exact boundary values, UUID generator for synthetic tie-breakers, diff for adjacent pages, and JSON formatter for cursor envelopes.
Test empty and partial final pages, equal timestamps, nulls, forward and backward navigation, inserts, deletes, changed sort keys, stale cursors, altered filters, authorization changes, replica lag, large offsets, and query plans with production-like data.