Offset Pagination vs. Keyset Pagination for Deep-Page Retrieval

Question

Pagination Strategy for Large Datasets
When implementing pagination in PostgreSQL for tables with millions of rows, the choice of retrieval method impacts both performance and user experience. The primary constraint is the need to maintain consistent response times as users navigate deeper into the result set.
Performance and Navigation Trade-offs
Offset-based pagination allows users to jump to a specific page number, but it requires the database to scan and discard all preceding rows. In contrast, keyset pagination uses a cursor based on a unique, sorted column to fetch the next set of results, ensuring constant-time performance regardless of the page depth.
However, keyset pagination restricts navigation to sequential movements and requires a strictly ordered, non-null index to remain efficient.

Offset: Supports random page access; performance degrades linearly.
Keyset: Supports sequential access; performance remains stable.

Which approach is more sustainable for a dataset where deep-page access is frequent but random page jumping is a secondary requirement? Under what specific indexing conditions does keyset pagination lose its performance advantage over offset?

Tasadduq Burney · Answer

Answer
For a dataset where deep-page access is frequent and random page jumping is secondary, keyset pagination is more sustainable than OFFSET pagination. Keyset pagination loses its performance advantage over OFFSET when the ordering cannot be expressed as a stable, unique, indexed range seek, e.g., a non-unique sort column without a tie-breaker, a missing or non-covering index on the sort key, or queries with filters that prevent an index seek to the cursor position.
Confirmed facts

OFFSET pagination is implemented as LIMIT n OFFSET m. The database must identify and skip m rows before returning n rows, so cost and I/O grow with page depth.
Keyset pagination uses a range predicate on the last seen values, e.g., WHERE (sort_col, pk) > (last_sort, last_pk) ORDER BY sort_col, pk LIMIT n. This can be satisfied by an index seek to the start point.
Keyset pagination cannot jump to arbitrary page numbers efficiently. UI must support cursor-based next/previous navigation.
Keyset requires a unique immutable ordering. Ties need a tie-breaker primary key to avoid missing or duplicate rows.

Likely explanation
Deep OFFSET pages become slower and unstable under concurrent inserts and deletes, often causing duplicates or skipped rows between requests. Keyset traversal stays near constant cost and monotonic when ordering columns are indexed and unique. OFFSET remains acceptable for shallow pages, small tables, or admin exports where simplicity outweighs deep-page cost.
Steps for this case

Choose a stable unique ordering, typically (sort_col, pk) where pk is immutable.
Create an index on the ordering used for pagination: CREATE INDEX ON table (sort_col, pk).
Replace OFFSET with a keyset predicate using the last cursor values and return the last row values as the next cursor.
Use > / < for forward/backward navigation and keep the same ORDER BY as the index.

One diagnostic that changes the recommendation: Is your sort column unique and immutable and is the keyset index covering the query filters? If filters are applied on columns not in the index, the planner may fall back to scans and the advantage narrows.

Offset Pagination vs. Keyset Pagination for Deep-Page Retrieval

Pagination Strategy for Large Datasets

Performance and Navigation Trade-offs

1 answer

Answer

Confirmed facts

Likely explanation

Steps for this case

Your perspective belongs here.

1 question comment