Lists that scale: cursor pagination

An endpoint that returns every order works in development, where there are twelve. In production there are two million, and the response takes eight seconds and a gigabyte of memory. Every list endpoint needs a page size with a maximum the server enforces, because a client asking for ?limit=1000000 is either a bug or an attack.

The obvious approach is offset pagination: LIMIT 20 OFFSET 40 for page three. It has two problems. The database still reads and discards the first 40 rows, so page 5,000 is slow. Worse, the pages shift when data changes: if a new order arrives while someone is on page one, everything moves down a place, and the first item of page two is the last item they already saw. Deletions do the opposite and silently skip one.

Keyset (cursor) pagination fixes both. Instead of "skip 40 rows", the client says "give me the rows after this one". The server sorts by a column plus a unique tie-breaker, usually created_at then id, because two orders can share a timestamp. Then it filters to rows that sort after the last one sent. An index on those two columns makes every page equally fast, and new rows at the top can't push old ones into the next page.

select id, created_at, total
from orders
where (created_at, id) < ($1, $2)   -- the cursor: last row's created_at and id
order by created_at desc, id desc
limit 21;                           -- one more than the page size

Two details make it work. Ask for one more row than the page size: if it comes back, there is a next page, and you don't need a separate COUNT(*), which is slow on big tables. And make the cursor opaque, an encoded string the client passes back without reading, so you can change what's inside it later without breaking anyone.

Loading your workspace…