Pagination

Pagination

Pagination is not just a UI concern. It is how you bound query cost, keep response times stable, and prevent a single client from scanning an entire dataset in one request. Offset pagination is easy to explain, but cursor pagination usually wins once the table is large or frequently changing.

Minimal Example

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

What It Solves

• Caps per-request work so APIs stay responsive even when the dataset grows by orders of magnitude.
• Allows clients to resume traversal without re-fetching the entire collection.
• Creates a stable contract for feeds, exports, and admin tables.

Failure Modes

1. Using large offsets on hot tables and forcing the database to walk rows it will not return.
2. Sorting by a non-unique column so page boundaries duplicate or skip records under concurrency.
3. Encoding cursors without versioning or validation, then breaking old clients on schema changes.

Production Checklist

• Choose a deterministic sort with a tiebreaker, usually timestamp plus primary key.
• Keep cursor contents opaque to clients but explicit in server code.
• Test inserts and deletes between page requests to confirm the traversal still behaves sanely.

Closing

Good pagination is really query design under API constraints. The winning design is the one that still behaves when the dataset is both large and moving.

---