Add Keyset Pagination to a SQL Endpoint

Page by a stable ordered key instead of OFFSET so deep pages stay fast and rows are neither skipped nor duplicated as data changes.

~30 minsqlpaginationperformance

Task

Replace expensive OFFSET pagination with keyset pagination using a unique, ordered cursor.

When to use this

OFFSET 100000 scans and discards a huge number of rows, and concurrent inserts shift the window so items are skipped or repeated.

Prerequisites

A table with a unique, monotonic column or a composite sort key
An index matching the sort order

Steps

1. Sort by a unique key such as (created_at, id) and return the last row as the cursor.

Expected output: Each page has an unambiguous boundary.

2. Encode the cursor opaquely so clients cannot construct arbitrary ones.

psql -c "SELECT id FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;"

Expected output: The cursor format can change without breaking clients.

3. Create a composite index that matches the sort columns exactly.

Expected output: The query seeks directly to the boundary.

4. Handle the first page without a cursor by omitting the WHERE clause.

Expected output: First-page queries are correct and still use the index.

Troubleshooting

Problem

Cursor decodes but returns duplicates

Solution

Include every sort column in the cursor and in the tie-breaking comparison.

Problem

Query planner ignores the index

Solution

Match the index column order to the ORDER BY and keep the comparison row-wise.

Limitations

Keyset pagination cannot jump to an arbitrary page number, so it suits feeds but not page-number UIs.
The cursor is tied to the sort order; changing it invalidates outstanding cursors.

Context

Replace expensive OFFSET pagination with keyset pagination using a unique, ordered cursor. OFFSET 100000 scans and discards a huge number of rows, and concurrent inserts shift the window so items are skipped or repeated.

Steps

Sort by a unique key such as (created_at, id) and return the last row as the cursor. Expected: Each page has an unambiguous boundary.
Encode the cursor opaquely so clients cannot construct arbitrary ones. Expected: The cursor format can change without breaking clients.
Create a composite index that matches the sort columns exactly. Expected: The query seeks directly to the boundary.
Handle the first page without a cursor by omitting the WHERE clause. Expected: First-page queries are correct and still use the index.

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Verify

Each page has an unambiguous boundary.
The cursor format can change without breaking clients.
The query seeks directly to the boundary.
First-page queries are correct and still use the index.

Limits

Keyset pagination cannot jump to an arbitrary page number, so it suits feeds but not page-number UIs.
The cursor is tied to the sort order; changing it invalidates outstanding cursors.