Keyset Pagination in SQL for Fast, Stable APIs

7 min read .

Published on 01 Sep 2026

Keyset Pagination in SQL for Fast, Stable APIs

Pagination looks simple until a table becomes large or new rows are inserted while a client is paging through results. LIMIT and OFFSET are easy to understand, but deep offsets can become expensive and changing data can make rows appear twice or disappear between requests.

Keyset pagination, also called seek pagination, avoids those problems by asking for rows after a known position instead of asking the database to skip a number of rows.

This guide explains the SQL pattern, why deterministic ordering matters, how to design a cursor, and where keyset pagination is not the right choice.

Why OFFSET pagination becomes problematic

A common API query starts like this:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

The query requests 20 rows, but the database still has to find the rows that come before the requested page. An index can make the ordering much cheaper, but a large offset still means advancing past many entries before returning the page.

There is also a correctness problem when the underlying data changes. Suppose a client reads page one, then another user inserts a new post at the beginning of the result set. The old last row of page one moves into page two. An OFFSET 20 query can therefore return that row again.

Deletes can create the opposite effect and cause a row to be skipped.

For small administrative screens or shallow result sets, this may not matter. For large feeds and public APIs, it often does.

Keyset pagination uses the last row as the position

Instead of sending a page number, the client sends information from the last row it received.

For a table ordered by descending creation time, a first page can be fetched with:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

If the final row has created_at = 2026-08-30 12:00:00+00 and id = 8142, the next page starts below that row:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) <
      (TIMESTAMPTZ '2026-08-30 12:00:00+00', 8142)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The tuple comparison syntax shown here is supported by PostgreSQL. Other database systems can express the same ordering with explicit predicates, but their syntax and optimizer behavior should be checked against that database’s documentation.

Why the ID is part of the cursor

Ordering only by created_at is not deterministic because multiple rows can have the same timestamp. If the cursor contains only the timestamp, rows sharing that timestamp can be skipped or repeated.

The id provides a unique tie-breaker:

created_at DESC, id DESC

The cursor and the ORDER BY clause must describe the same ordering. If the API sorts by three fields, the cursor generally needs enough information to resume that three-field order unambiguously.

Write the comparison to match the sort direction

For descending order, the next page asks for values smaller than the last row:

WHERE (created_at, id) < (:created_at, :id)
ORDER BY created_at DESC, id DESC

For ascending order, the comparison reverses:

WHERE (created_at, id) > (:created_at, :id)
ORDER BY created_at ASC, id ASC

Mixing the comparison direction and sort direction is a common source of missing or repeated rows.

Use an index that supports the ordering

A suitable PostgreSQL index for the example is:

CREATE INDEX posts_created_at_id_idx
    ON posts (created_at DESC, id DESC);

The useful index depends on the complete query, not only its ORDER BY. If the endpoint also filters by tenant, for example:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = :tenant_id
  AND (created_at, id) < (:created_at, :id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

then an index beginning with the equality filter is often a better candidate:

CREATE INDEX posts_tenant_created_id_idx
    ON posts (tenant_id, created_at DESC, id DESC);

Do not add an index solely because it looks plausible. Inspect representative queries with the database’s query-plan tools and realistic data. PostgreSQL provides EXPLAIN and EXPLAIN ANALYZE; remember that EXPLAIN ANALYZE actually executes the statement.

Turn the position into an opaque API cursor

An API does not need to expose its database comparison directly. It can encode the cursor fields into an opaque token.

A decoded cursor might logically contain:

{
  "created_at": "2026-08-30T12:00:00Z",
  "id": 8142
}

The API can serialize that structure and encode it with URL-safe Base64. Encoding is not encryption or authentication: clients can decode and modify ordinary Base64 values.

If changing cursor fields could bypass authorization or other security boundaries, validate the decoded values independently and consider signing the cursor. A cursor should never be trusted as proof that a client is allowed to access a resource.

Keep filters outside the cursor when possible

A request such as:

GET /posts?status=published&cursor=...

should normally apply the same status=published filter to every page. If a client changes filters while reusing an old cursor, the cursor may no longer represent a meaningful position.

An API can reject incompatible combinations or include a normalized filter fingerprint in a signed cursor when stronger guarantees are needed.

Fetch one extra row to determine whether another page exists

If the API page size is 20, request 21 rows:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:created_at, :id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

When 21 rows are returned, send the first 20 and build the next cursor from row 20. The extra row tells the application that more data existed at the time of the query without requiring a separate COUNT(*).

A total count is a different product requirement. Do not make every pagination request calculate an exact count unless clients actually need one.

Forward-only pagination is the simplest design and works well for feeds, event streams, and APIs consumed sequentially.

Bidirectional navigation is possible, but the query needs to reverse both the comparison and ordering. For example, to find rows immediately before a cursor in a descending feed, query in ascending order and reverse the returned rows in the application before presenting them.

This logic deserves dedicated tests because it is easy to introduce off-by-one and ordering errors.

What happens when rows change

Keyset pagination gives a stable position in an ordering; it does not create a snapshot of the database.

New rows inserted ahead of the cursor will not push already-seen rows into the next page, which prevents the common duplicate-row problem caused by offsets. However, updates to columns used in the sort key can move an existing row from one side of the cursor to the other.

If a workflow requires an exact, immutable view across many requests, ordinary pagination is not enough. Consider a snapshot identifier, a fixed upper-bound timestamp, versioned data, or another application-specific consistency strategy.

Common pitfalls

Using a non-unique sort key

ORDER BY created_at DESC is not sufficient when timestamps can repeat. Add a stable, unique tie-breaker such as the primary key.

Applying an inclusive comparison

Using <= for the next page includes the cursor row again. For the examples above, use strict < or > comparisons.

Forgetting NULL semantics

SQL comparisons involving NULL do not behave like comparisons between ordinary values. If a sort column is nullable, define an explicit NULL ordering and cursor strategy, or use a non-null sort key.

Letting cursor fields drift from ORDER BY

If the query changes from created_at, id to another ordering but the cursor format does not change, pagination can silently become incorrect. Treat the cursor schema and ordering as one contract.

Assuming Base64 makes a cursor secure

Base64 only encodes bytes. Validate cursor structure, enforce authorization independently, reject unreasonable values, and sign tokens when tamper detection is required.

Expecting arbitrary page jumps

Keyset pagination is optimized for moving relative to a known position. It does not naturally support “jump to page 500.” If arbitrary page numbers are a hard requirement and the dataset is modest, offset pagination may still be the better interface.

When keyset pagination is a good fit

Keyset pagination works especially well for activity feeds, audit logs, timelines, message histories, transaction lists, and APIs that process large ordered datasets sequentially.

Offset pagination remains useful for small datasets, reporting interfaces where users expect numbered pages, and queries where random access matters more than deep-page performance.

The important decision is not to replace every OFFSET query. Use keyset pagination when the product naturally moves forward or backward through a stable ordering and when predictable performance on large result sets matters.

Practical checklist

Before shipping a keyset-paginated endpoint, verify that the ordering is deterministic, every cursor field matches the ORDER BY, comparison directions are correct, and the supporting index matches the real filters and ordering. Test duplicate sort values, inserts between requests, deleted rows, invalid cursors, empty pages, and the final page.

Finally, inspect the query plan with production-like data. Keyset pagination gives the database an efficient access pattern, but schema design, indexes, filters, data distribution, and database version still determine the actual plan.