Cursor pagination instead of offsets: pages that stay stable when data changes

What it is

Offset pagination means ORDER BY erstellt DESC LIMIT 20 OFFSET 400. The PostgreSQL documentation states that the rows skipped by OFFSET still have to be computed inside the server, so a large OFFSET can be inefficient. It also states that LIMIT without an ORDER BY clause that enforces a unique order returns an unpredictable subset. Cursor pagination (also called keyset or seek method) instead remembers the last entry of the previous page and queries only the rows that follow it, as Markus Winand describes in the German edition of "SQL Performance Explained" cited here: WHERE (erstellt, id) < (?, ?) ORDER BY erstellt DESC, id DESC FETCH FIRST 20 ROWS ONLY. Comparing with row values (row value syntax) treats the pair as a single unit, so a composite index can seek directly to it.

Why it matters

Between two offset requests, every inserted or deleted row shifts all later pages. A client sees entries twice or misses them. For a human this is annoying; for an agent that is supposed to process a whole data set, it is a correctness bug. In addition, the cost per page grows with depth, so the last pages of a large list are the most expensive requests of the API. A cursor tied to a stable sort key returns each entry exactly once and costs the same on page 5000 as on page 1.

How to apply

• Sort by a unique, indexed key, or by a combination that ends with a unique field (erstellt, id). The index must contain the same columns in the same order and direction.
• Return an opaque next_cursor to the client that encodes the key values of the last row (Base64 of a JSON tuple is enough). Sign or encrypt it if clients must not build their own cursors.
• Carry the filter and sort order in the cursor, or reject requests whose parameters differ. A cursor is valid for exactly one query.
• Limit the page size with a default and an upper bound. Signal the end of the list by a missing next_cursor, not by an empty page.
• For backward paging, offer a prev_cursor with the comparison direction reversed, or leave it out and document that.

Pitfalls

Sorting by a non-unique column without a tiebreaker loses or repeats rows at page boundaries. Cursors cannot jump to "page 37". If the interface needs page numbers, offset with a limited maximum depth remains the more honest choice. Timestamps with one-second resolution produce ties, which is why the unique key is the second field. A cursor that carries internal IDs in plain text reveals growth rates and invites tampering.