Keyset Pagination for Stable and Efficient Database Queries

Pagination looks straightforward with LIMIT and OFFSET, but deep offsets become increasingly expensive and can produce unstable results when rows are inserted or deleted between requests.

Keyset pagination, also called seek pagination, uses the last seen sort key as the starting point for the next query.

Why OFFSET degrades

A typical query is:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;

The database still has to find and skip preceding rows before returning the page. There is also a correctness problem: if a new row is inserted at the front between requests, offsets shift and a user may see a duplicate or miss an item.

Seek from the last row instead

Suppose the first page ends with:

created_at = 2026-08-20T10:15:00Z
id = 8421

The next PostgreSQL-style query can seek after that position:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ('2026-08-20T10:15:00Z', 8421)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Where tuple comparison is unavailable or undesirable, expand the condition:

WHERE created_at < :created_at
   OR (created_at = :created_at AND id < :id)

Always make ordering deterministic

created_at may not be unique. If several rows share a timestamp, using it as the only cursor can skip or repeat rows.

Add a stable unique tiebreaker:

ORDER BY created_at DESC, id DESC

The cursor must contain every ordering field needed to identify a precise boundary.

Match the index to the query

For the example above, an index such as this can support the access pattern:

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

Tenant-scoped queries may need the tenant first:

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

The best index depends on the database engine, predicates, selected columns, and data distribution. Inspect the actual query plan rather than assuming an index is used.

Encode cursors as opaque values

A public API can encode a cursor payload such as:

{
  "created_at": "2026-08-20T10:15:00Z",
  "id": 8421
}

The server can serialize and validate this state, then expose an opaque cursor string to clients. Opaque cursors give the server room to evolve internal representation and discourage callers from manufacturing arbitrary positions.

Treat cursor input as untrusted. Validate size, types, supported versions, and query compatibility.

Filters belong to the cursor contract

An old cursor may be meaningless after the user changes filters or sort order.

A robust cursor can carry a version and enough query context to reject incompatible reuse. Alternatively, keep filter state outside the cursor but verify that the cursor belongs to the same query shape.

Inserts, deletions, and updates

Keyset pagination handles inserts before the current position well because the next query still starts after the row the user actually saw.

If the cursor row is deleted, pagination can still work because the cursor stores values rather than depending on the row’s continued existence.

Updates to sort-key columns are harder: a row can move across the cursor boundary. If strict snapshot consistency is required, use transaction snapshots or an immutable domain ordering key.

Trade-offs

Keyset pagination works well for timelines, feeds, logs, audit records, and large tables where users move sequentially.

OFFSET remains reasonable when result sets are small, arbitrary page numbers are required, users rarely reach deep pages, or the dataset is effectively static during browsing.

Keyset pagination trades random page access for stable incremental navigation.

Common pitfalls

Non-unique ordering

Without a unique tiebreaker, boundaries are ambiguous.

Inconsistent timestamp precision

If cursor serialization loses precision compared with the database column, subtle gaps can appear.

Missing index support

Seek syntax alone does not guarantee speed. Test with production-like data and inspect execution plans.

Unversioned cursor formats

Version cursor formats so sort or schema changes do not leave old clients sending permanently invalid tokens.

Conclusion

Keyset pagination asks the database to continue from a known ordering position instead of counting past every earlier row. Use deterministic composite ordering, support it with appropriate indexes, validate cursor state carefully, and be explicit about navigation trade-offs. For large changing datasets, it usually produces more predictable performance than deep OFFSET pagination.