Every list endpoint needs pagination, and nearly every one starts with LIMIT and OFFSET because it is the obvious translation of page numbers. It has two problems, both of which appear only at scale, which is why they arrive as production bugs rather than review comments.
The first is correctness. OFFSET counts rows at query time, so if something is inserted or deleted between requests, the window shifts. A user reading page one, during which a new item is added at the top, will see the last item of page one again at the top of page two. Deletions cause the opposite - rows are skipped entirely and nobody notices. For a UI this is mildly annoying; for a client synchronising data through the API it is silent data loss.
The second is performance. OFFSET 100000 does not jump to row 100,001 - the database generates and discards a hundred thousand rows first. Page one is instant and page two thousand times out, and because most users never go deep, this is discovered by the export script or the integration partner rather than in testing.
Keyset pagination fixes both. Instead of asking for the next twenty rows by position, you ask for the next twenty rows after a specific point: WHERE (created_at, id) < (last_seen_created_at, last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 20. The index seeks directly to the position, so page one thousand costs the same as page one, and inserts elsewhere in the list cannot shift your window. The tie-breaker on id matters - without a unique final sort column, rows sharing a timestamp can be skipped or repeated.
The trade-off is real and worth stating: keyset pagination gives you next and previous, not arbitrary page numbers, because it has no way to know what row begins page fifty. For infinite scroll, feeds, exports and API consumers, that is no loss. For a table where users genuinely jump to a numbered page, OFFSET is acceptable if you cap how deep it goes.
Expose it as an opaque cursor rather than raw column values - a base64 string encoding the last row's sort keys. Clients then cannot construct their own, which means you can change the underlying sort or add a tie-breaker later without breaking every integration. And always return a stable total separately, or not at all: computing an exact count on a large table on every page request is frequently more expensive than fetching the page itself.
