Implement Stable Cursor Pagination

Paginate a changing dataset with an encoded cursor instead of offsets, keeping results stable when rows are inserted or deleted between requests.

~30 minapi-designdatabasespagination

Task

Add cursor-based pagination to a list API backed by a database.

When to use this

An API uses LIMIT/OFFSET pagination and users see duplicate or missing rows when new records are inserted during scrolling.

Prerequisites

A table with a stable sort key such as created_at plus id
An API endpoint that returns a list
A way to encode and decode opaque cursors

Steps

1. Define a deterministic order using a unique tiebreaker so the cursor identifies an exact position.

ORDER BY created_at DESC, id DESC

Expected output: Every row has a unique position even when timestamps collide.

2. Fetch one extra row to determine whether another page exists, and return an opaque cursor built from the last row's sort key.

const limit = Math.min(Number(req.query.limit) || 20, 100);
const after = decodeCursor(req.query.cursor); // { createdAt, id } or null
const rows = await db.query(
  `SELECT id, title, created_at
   FROM posts
   WHERE ($1::timestamptz IS NULL OR (created_at, id) < ($1, $2))
   ORDER BY created_at DESC, id DESC
   LIMIT $3`,
  [after?.createdAt ?? null, after?.id ?? null, limit + 1],
);
const hasMore = rows.length > limit;
const page = rows.slice(0, limit);
const nextCursor = hasMore ? encodeCursor(page[page.length - 1]) : null;

Expected output: The response contains at most the requested limit and a cursor for the next page.

3. Encode the cursor as base64url JSON and treat it as opaque. Validate its shape and reject malformed values with 400.

Expected output: Clients cannot construct arbitrary SQL positions by editing the cursor.

4. Test insertion and deletion between pages. A new row must not shift an already fetched page.

curl 'http://localhost:3000/posts?limit=2' | jq

Expected output: Following the returned cursor yields no duplicates and no skipped rows.

Troubleshooting

Problem

Duplicate rows across pages

Solution

The sort key is not unique; add id as a tiebreaker and include both fields in the cursor.

Problem

Cursor stops working after a schema change

Solution

Version the cursor payload or invalidate old cursors explicitly.

Limitations

Cursor pagination cannot jump to an arbitrary page number cheaply.
Rows updated after being read may appear again if the sort key changes; design sort keys accordingly.

Why offsets drift

OFFSET 40 means “skip the first 40 rows of the current result.” If a row is inserted before the current position, the next page repeats a row; if a row is deleted, it skips one. A cursor anchors the next page to the last row the client actually saw.

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

Encode the last row’s (created_at, id) as base64url JSON. Return nextCursor only when an extra row proves another page exists.

Verify

Page 1 returns N rows and a cursor.
Insert a new row, then fetch page 2 with the cursor: no duplicate from page 1.
Delete a row from page 1, then fetch page 2: no row is skipped.
A malformed cursor returns 400, not a 500 or an unbounded query.
The limit is capped server-side.

Trade-offs

Cursor pagination is stable and efficient with an index on the sort key, but it cannot jump to page 50 without walking cursors. Use offset pagination only for small, static datasets or admin screens where page numbers matter more than stability.