2026-09-04 · 6 min read · Rahul Chitturi
- sql
- pagination
- api
LIMIT and OFFSET can return missing or repeated rows when the ordering is not unique or records are inserted and deleted between page requests. The SQL remains valid while each page observes a moving position in the result set.
An ORDER BY created_at alone is not deterministic when many rows share the same timestamp. The database may return tied rows in different orders across executions or query plans.
Define a total order
Add a stable unique tie-breaker to the business sort, such as ORDER BY created_at DESC, id DESC. Return and accept both values when the next-page boundary depends on both.
- Keep sort direction consistent across every keyset predicate
- Use database types without lossy timestamp or ID conversion
- Index the filter and ordering columns in the needed sequence
- Define how updated sort keys affect an active traversal
Choose offset or keyset intentionally
Offset pagination is useful for small, stable results and direct page numbers, but large offsets can become expensive and concurrent changes shift positions. Keyset pagination continues after the last observed ordered values and is usually steadier for feeds.
Neither approach creates a snapshot automatically. If a complete export must represent one consistent point in time, use an appropriate database snapshot or an explicit upper boundary.
A Flashman workflow
Use the SQL formatter to inspect ordering and predicates, timestamp converter for boundary fixtures, UUID generator for unique tie-breakers, and diff to compare adjacent sanitized pages.
Test equal sort values, inserts and deletes between requests, direction changes, nulls, large offsets, stale cursors, filtered queries, index plans, and authorization applied before pagination.