Offset vs keyset pagination in SQL

Almost every list in an application needs pagination. The two main approaches behave very differently as data grows. The examples use PostgreSQL, but the ideas apply to any relational database.

Offset pagination

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;   -- page 3

It is simple, supports "jump to page N", and is fine for small tables. It has two problems at scale:

1. Cost grows with the offset. To return rows 100,001–100,020 the database must produce and throw away the first 100,000. Deep pages get slower and slower even with an index.
2. Unstable pages. If a row is inserted or deleted while a user is paging, everything shifts by one: they see a duplicate on the next page, or miss a row entirely.

Keyset (cursor) pagination

Instead of "skip N rows", say "give me rows after this one":

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

The database can jump straight to the cursor position using an index, so page 5,000 is as fast as page 1, and inserts elsewhere in the table do not shift your position.

Requirements for correctness

• A total, deterministic order. If two rows can tie on the sort column (many rows share a timestamp), results can be skipped or repeated. Always add a unique tie-breaker such as id to both the ORDER BY and the comparison. The row-value comparison (created_at, id) < ($1, $2) compares both columns together in a single step.
• A matching index. Create an index in the same order as the query, for example CREATE INDEX ON posts (created_at DESC, id DESC);.
• Same direction everywhere. Mixed directions (created_at DESC, id ASC) cannot use the simple row comparison and need a more complex condition.

Returning a cursor to clients

Return the last row's sort values as an opaque string, for example base64 of {"c":"2026-01-01T00:00:00Z","i":123}. Opaque cursors let you change the internals later. Validate cursors on input: decode, check types, and bind them as query parameters; never concatenate them into SQL. Include a next_cursor field (or null at the end) in the response so clients know when to stop.

Trade-offs

	Offset	Keyset
Jump to arbitrary page	Yes	No
Speed on deep pages	Degrades	Constant
Stable under inserts/deletes	No	Yes
Total page count	Easy (COUNT(*))	Separate query
Implementation	Trivial	Needs ordered, unique key

Use offset for small, mostly static lists and admin tables where page numbers matter. Use keyset for feeds, activity logs, APIs, and anything that can grow large. Showing an exact total with COUNT(*) is itself expensive on big tables; consider an approximate count or omit it.

Consuming a cursor API

On the client side, hide the loop behind an async generator so callers simply iterate, and add a hard page limit as a safety net. See the linked snippets for a tested example.