Page 2 should not change page 1

A dashboard table looks stable until someone else changes the data underneath it. A user is on page 2, a new order arrives, and suddenly a row they have already seen appears again — or a row they never saw disappears from the list entirely.

That is not a rendering bug. It is offset pagination doing exactly what it was told to do.

Offset says: skip the first N rows, then give me the next 20. It remembers a position, not a row. If a row is inserted before that position while the user is paging, every later page shifts by one. The row at the boundary gets repeated. If a row is deleted before that position, the window shifts the other way and a row gets skipped. On a quiet, static table nobody notices. On a live orders table, it happens all the time.

There is a second cost, and it grows with depth. To serve page 500, the database still has to compute and discard the first 10,000 rows. The deeper the page, the more work is thrown away before the 20 rows you actually want are returned.

Cursor pagination, also called keyset pagination, fixes both problems by anchoring on a row instead of a position. Instead of "skip 40", you ask for "20 rows after this one":

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

The database can seek from that boundary using an index instead of scanning past everything before it, and inserts ahead of the boundary do not disturb the window you have already walked through. The cursor itself should be opaque — an encoded token, not a raw id the client is expected to understand — so you can change the sort key later without breaking callers.

A cursor is not magic, and it has real trade-offs. You cannot jump straight to page 37, because there is no page 37 without walking the chain that leads to it. You also do not get a cheap total page count, because counting the whole result is a separate, often expensive query. If your users genuinely need numbered pages and a total, and the data is small and mostly static, offset is still the honest choice. A small admin table and a search results page are fine places for it.

The rule I use now is simple: if the list can change while someone is reading it, or if it can grow deep, use a cursor. If it is small, static, and page numbers matter more than stability, offset is fine. And whatever you choose, make the sort key total: a timestamp alone is not unique, so pair it with an id. Without a unique, stable order, even a cursor can skip or repeat rows, and you will have rebuilt the same bug with fancier syntax.