Why offset pagination breaks at scale, and what to use instead

If your feed uses LIMIT and OFFSET, it feels fine in development and falls apart in production. The reason is simple: the database still has to walk every row it skips.

The problem with OFFSET

Say you paginate posts 20 at a time:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

To return page 501, the database reads and discards 10,000 rows before it gets to the 20 you want. The deeper the page, the slower the query. Page 1 is instant. Page 500 crawls. And because rows shift as new posts arrive, a user paging through a live feed can see duplicates or skip items entirely.

Cursor-based pagination

Instead of "skip N rows," you say "give me the rows after this one." The cursor is just a stable, ordered value, usually the sort key plus a tiebreaker:

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

\(1 and \)2 are the created_at and id of the last row on the previous page. With an index on (created_at, id), the database seeks straight to that point and reads 20 rows. Page 1 and page 500 cost the same.

Why the tiebreaker matters

If two posts share a created_at, ordering by that column alone is ambiguous, and you can drop or repeat a row at the page boundary. Adding id as a secondary sort makes the cursor unique and the ordering total. Always pair your sort column with a unique tiebreaker.

Returning the cursor

Encode the cursor so clients treat it as opaque. Base64 of created_at:id is enough:

const cursor = Buffer.from(`\({row.created_at}:\){row.id}`).toString("base64");

The client sends it back as ?after=<cursor>, you decode it, and the query above does the rest.

The trade-off

You lose random access. There is no "jump to page 47," because pages are defined relative to a cursor, not an absolute offset. For infinite-scroll feeds, timelines, and APIs, that is exactly the right trade. For a paginated admin table where users click page numbers, keep offset, the data is small and bounded.

Rule of thumb: if the list grows without limit and users scroll it, use a cursor. If it is small and users jump around, offset is fine.

Pick based on how the data is actually read, not on which one is easier to write.