OFFSET/LIMIT vs KEYSET Pagination: Which Strategy Serves Large Query Bounding Best?

Choosing Between OFFSET/LIMIT and KEYSET Pagination

The goal is to expose a consistent, performant paginated view of a million‑row dataset while applying arbitrary WHERE clauses and JOIN filters. Two documented approaches exist:

• OFFSET/LIMIT – simple to use, works with any ORDER BY, but performance degrades linearly as page numbers increase.
• KEYSET (seek) Pagination – constant‑time lookups by using a stable, unique ordering column, yet it cannot jump to arbitrary pages and requires a suitable key.

Key constraints include:

• Need to support random page jumps for user interfaces.
• Tables may lack a single unique, immutable column.
• Concurrent inserts or deletes can shift ordering values between requests.

Given these trade‑offs, the unresolved decision is whether to adopt KEYSET pagination universally for all large tables, or to fall back to OFFSET when a suitable key is absent, balancing performance gains against implementation complexity.

Specific questions:

1. Should we enforce a unique, immutable ordering column on every large table to enable KEYSET pagination?
2. How can we detect and mitigate missing or duplicated rows caused by concurrent modifications under KEYSET pagination?
3. Is the loss of arbitrary page jump capability acceptable for the user experience, or must we retain OFFSET for that use case?