How to design stable, efficient pagination in Python APIs using explicit ordering, cursor contracts, limits and query evidence for changing production datasets.
Use a deterministic order, bound every page and prefer cursors when data changes or offsets become expensive.
Make order part of the contract
Without an explicit, unique ordering, two requests for the same page can return different rows as the database chooses a convenient plan. Include a stable tie-breaker such as an identifier alongside a timestamp or business sort field.
Tell clients what the order means and whether new records can appear during traversal. A useful API does not imply that a changing dataset is a static snapshot unless it actually provides one.
Understand offset cost
OFFSET becomes more expensive as the database walks past rows it will discard. It can be acceptable for small, bounded administrative lists, but it is rarely a safe default for large or frequently changing datasets.
Cursor pagination uses the last observed sort values to continue from a known position. The cursor must be opaque, validated and tied to the same filters and order as the original request.
Protect the database and the client
Enforce a maximum page size even if clients request more. A large page increases query time, memory, serialization cost and downstream retry impact, often with little user value.
Index the filter and ordering combination used by the endpoint, then verify the plan with representative data. Test duplicate, missing and out-of-order edge cases while records are inserted or removed between page requests.
Evidence to take into review
- Every paginated endpoint has deterministic ordering.
- Page size is bounded and documented.
- Cursor contents are opaque and validated.
- Queries are indexed for the filter and order used.
- Changing-data edge cases are covered by integration tests.
Explore all engineering notes.
Use PRODUCTION-7 to connect this concern with the other dimensions of a trustworthy backend.
View all articles Get the checklist