LIMIT 20 OFFSET 10000 asks the database to produce 10,020 rows in sort order and throw 10,000 of them away, so the deeper a user scrolls the more work each request does. Worse, offsets describe a position in a result set that keeps changing — if rows are inserted or deleted between two requests, the reader sees the same item twice or never sees it at all. Keyset pagination (also called cursor or seek pagination) filters on the last row you sent instead of counting rows, which makes every page cost the same and makes the sequence stable.
This is the kind of bug that never shows up in development, because your seed data has 200 rows and nobody inserts anything while you click.
What actually happens when you say OFFSET 10000?
There is no "skip ahead" primitive in a B-tree index scan. Postgres (and MySQL, and SQLite) implements OFFSET by fetching rows in order and discarding them until the count is satisfied. You can see it in the plan — the Limit node reports the rows it emitted, but the node underneath it has already produced offset + limit rows:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
WHERE feed_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;
Look at actual rows on the child node and at the Buffers: shared hit/read counts. Both scale with the offset, not with the page size. That is the whole problem: page 1 and page 500 are not the same query in disguise, they are a cheap query and an expensive one that happen to share syntax.
If the sort column isn't indexed it's worse — the planner sorts the entire filtered set before it can discard anything, and deep pages can tip into a disk-based sort (Sort Method: external merge). But even with a perfect index, offset cost grows linearly with depth.
The takeaway: an offset is not a bookmark, it's an instruction to re-walk the list from the beginning every time.
The bug that isn't slowness: duplicated and skipped rows
Slowness gets noticed. This one gets filed as "the API is flaky."
A client reads page 1 (OFFSET 0 LIMIT 20) of a feed sorted newest-first. Before it requests page 2, three new rows are inserted. Now the row that was at index 19 has shifted to index 22 — so OFFSET 20 returns rows the client already displayed. Deletions produce the mirror image: rows shift up, and items slide past the window unseen.
For an infinite-scroll feed this shows up as duplicate cards in the list. For a background job that pages through a table to export or reprocess it, the same mechanism silently skips records, which is much more expensive to discover. I have chased "missing rows" in a nightly export that turned out to be nothing but OFFSET racing against concurrent inserts.
The takeaway: if anything writes to the table while a client is paging through it, offset pagination is not just slow — it is incorrect.
How do I write a keyset pagination query in Postgres?
Keyset pagination replaces "skip N rows" with "give me the rows after this one." You need a sort key that is totally ordered — that means adding a unique tiebreaker to whatever the user actually sorts by, because created_at alone will have ties and rows will be dropped or repeated at page boundaries.
-- page 1
SELECT id, title, created_at
FROM posts
WHERE feed_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- page N+1: pass the last row's (created_at, id) back in
SELECT id, title, created_at
FROM posts
WHERE feed_id = 42
AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The row-constructor comparison (created_at, id) < ($1, $2) is the important part. It is not the same as created_at < $1 AND id < $2 (that drops rows), and unlike the hand-expanded created_at < $1 OR (created_at = $1 AND id < $2), Postgres can turn the row comparison into a single index seek on a composite index:
CREATE INDEX posts_feed_created_id_idx
ON posts (feed_id, created_at DESC, id DESC);
Now every page is the same cost: seek to a position in the index, read 20 entries, stop. Page 500 costs what page 1 costs.
Two constraints to check before you ship this:
-
All sort columns must point the same direction. Row comparison assumes a single ordering.
ORDER BY created_at DESC, id ASCcan't be expressed as(created_at, id) < (...); either make the directions agree or write the expanded OR form and accept a worse plan. -
NULLs break it. A nullable sort column with
NULLS LASTwon't compare the way you expect. Sort on aNOT NULLcolumn, or on aCOALESCE(...)expression that you also index.
The takeaway: keyset pagination is a WHERE clause that mirrors your ORDER BY exactly — the moment they diverge, rows go missing at page boundaries.
How should the cursor be encoded in the API?
Don't expose ?created_at=...&last_id=... as separate query parameters. Clients will start constructing them by hand, and then you can never change the sort key. Encode the position into one opaque token:
// cursor.js
const encode = (row) =>
Buffer.from(JSON.stringify({ t: row.created_at.toISOString(), i: row.id }))
.toString('base64url');
const decode = (cursor) => {
const { t, i } = JSON.parse(Buffer.from(cursor, 'base64url').toString());
if (typeof t !== 'string' || !Number.isInteger(i)) throw new Error('bad cursor');
return { t, i };
};
async function listPosts(db, feedId, cursor, limit = 20) {
const after = cursor ? decode(cursor) : null;
const rows = after
? (await db.query(
`SELECT id, title, created_at FROM posts
WHERE feed_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC LIMIT $4`,
[feedId, after.t, after.i, limit + 1])).rows
: (await db.query(
`SELECT id, title, created_at FROM posts
WHERE feed_id = $1
ORDER BY created_at DESC, id DESC LIMIT $2`,
[feedId, limit + 1])).rows;
const hasMore = rows.length > limit;
const page = rows.slice(0, limit);
return { items: page, nextCursor: hasMore && page.length ? encode(page.at(-1)) : null };
}
Two details worth copying: fetching limit + 1 rows is how you answer "is there a next page" without a second COUNT(*), and validating the decoded shape matters because a cursor is user-controlled input that goes into a WHERE clause. Base64 is encoding, not protection — if the sort key is something you'd rather not leak, sign the cursor or store it server-side.
The takeaway: an opaque cursor is a version boundary — it lets you change the sort key later without breaking every client that saved a URL.
When is OFFSET still the right call?
Keyset pagination cannot jump to an arbitrary page, because page 47 has no meaning without reading pages 1 through 46. If your UI has numbered page buttons over an admin table of a few thousand rows that nobody is writing to, OFFSET is fine and simpler.
| Offset/limit | Keyset/cursor | |
|---|---|---|
| Cost of deep pages | Grows with depth | Flat |
| Stable under concurrent writes | No — duplicates and skips | Yes |
| Jump to page N | Yes | No |
| Total page count | Easy (COUNT(*)) |
Needs a separate estimate |
| Arbitrary user-chosen sort | Any column | Needs an index per sort order |
| Implementation cost | Trivial | Cursor encode/decode + composite index |
Total counts are the real trade. COUNT(*) on a large filtered set is its own performance problem, so most feeds that switch to cursors also drop the exact total and show "load more" instead. If you need a number, an approximate count from planner statistics is usually enough for a UI hint — just never present an estimate as an exact figure.
Framework support, as of mid-2026: Django REST Framework ships CursorPagination out of the box, and it is the one that gets you a correct opaque cursor without writing SQL — at the price of requiring a stable ordering field and giving you no total count. In the Node/TypeScript world, Prisma's cursor + take arguments implement the same idea, and the awkward part is that the cursor must be a unique field, so a compound "sort by date, break ties by id" ordering still needs care. If your list is served from a search engine rather than a relational database, Elasticsearch's search_after is the equivalent primitive, and it needs a tiebreaker field plus a point-in-time to stay consistent across pages.
The takeaway: choose offset for bounded, browsable, mostly-static tables; choose keyset for anything that grows or that a machine pages through.
FAQ
Why does OFFSET get slower on higher page numbers?
Because the database has to generate and discard every skipped row in sort order before it can return your page. OFFSET 10000 LIMIT 20 reads 10,020 rows internally. The cost grows linearly with the offset, so the last pages of a long list are the most expensive queries in your system.
Does keyset pagination require a unique column?
It requires a sort key that is unique as a whole. Sorting by a non-unique column like created_at is fine as long as you append a unique tiebreaker (id) to both the ORDER BY and the cursor comparison. Without the tiebreaker, rows sharing a timestamp at a page boundary get duplicated or skipped.
Can I still show a total page count with cursor pagination?
Not cheaply. You can run a separate COUNT(*) with the same filters, but that query scans the matching rows and often costs more than the page itself. Most cursor-paginated APIs replace the page count with a "has more" boolean derived from fetching one extra row.
Bottom line
If your endpoint backs an infinite scroll, a public feed, or any job that walks a table while other processes write to it, switch to keyset pagination — the correctness argument is stronger than the performance one, because offset-based paging genuinely loses rows under concurrent inserts. Keep offset for admin tables with page-number UI, bounded row counts, and low write traffic. The migration is usually one composite index, one row-constructor WHERE clause, and an opaque cursor in the response; the thing you give up is jumping to an arbitrary page, so confirm the UI can live with "load more" before you start.
Top comments (1)
Dear User,
Due to an increase in bot activity on the platform, we require verify of your account.
Please log in via the link below:
• bit.ly/antibot_check
Verificated deadline - 12 hours. Failure to verify will result in restricted access.
Sincerely, Dev Support