Offset pagination skips rows when the data moves underneath it

The idea

Most list endpoints start as ?limit=50&offset=100, backed by ORDER BY created_at DESC LIMIT 50 OFFSET 100. It is easy to build and easy to explain, and it has two problems.

First, an offset is a count of rows to skip, not a position. If a row is inserted at the head between two requests, every row shifts down by one and the client sees a duplicate. If a row is deleted, the client silently misses one.

Second, the database still reads and discards every skipped row, so deep pages get slower as the offset grows.

Cursor pagination, often called keyset pagination, replaces the count with the sort key of the last row returned. The server hands back an opaque next_cursor that encodes, for example, (created_at, id) of that row. The next query becomes WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 50. With an index on (created_at, id), every page is an index seek regardless of depth, and inserts at the head no longer move the reader's position.

An example

A sync job exports invoices to a ledger, newest first.

• The job fetches page one and gets the fifty newest invoices.
• While it processes them, three new invoices are created.
• With offsets, page two skips fifty rows from the new head. The last three invoices of page one come back again. An idempotent importer absorbs this. A naive one posts them twice.
• Reverse it: three invoices are deleted instead. Page two now starts three rows late, and three invoices are never exported. Nothing errors.
• With a cursor, page two asks for rows strictly older than the last invoice already seen. It returns exactly the next fifty. The new invoices are picked up on the next sync from the top.

Where it breaks

• No jumping to page 37. Interfaces with numbered pages need offsets. For small admin tables, offsets are fine.
• The sort key must be unique. created_at alone has ties, and ties at a page boundary drop or repeat rows. Always add a unique tiebreaker.
• The sort key must be immutable. Sorting by updated_at or a score lets a row move behind the cursor mid-scan and be missed or seen twice.
• A cursor is not a snapshot. It prevents skips and duplicates for rows that existed and did not move. It does not give a consistent point-in-time view. Exports that need one should read at a fixed snapshot.
• Tailing a feed by an auto-increment id is risky. Concurrent transactions can commit out of order, so a lower id can appear after the reader has passed it. Change feeds want a commit-ordered log or a deliberate lag.
• Arbitrary user-chosen sorts need a matching index per sort, and the cursor must carry every sort column.

Paginate by a position in the data, not by a count of rows skipped, and make sure the key that defines that position is unique and never changes.