LIMIT 20 OFFSET 10000 contains two separate bugs, and they surface at different times in a project's life.
The first is a performance bug, and it hides during development because it only appears on pages nobody visits while testing. The second is a correctness bug, and it never announces itself at all: users just occasionally see a record twice, or miss one entirely, and almost nobody reports that as anything other than a vague feeling that the list is weird.
Both come from the same root. OFFSET describes a position by counting, and counting is only stable if nothing else is happening.
#OFFSET reads everything it skips
The database cannot jump to the ten-thousandth row. There is no such addressing. To satisfy OFFSET 10000, it produces rows in order, discards the first ten thousand, and returns the next twenty.
The work is proportional to the offset, not to the page size. Page one is fast. Page five hundred reads a hundred times more rows than page five to return exactly as many.
You can watch it happen:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;Compare the actual rows reported on the Limit node against the one on the node beneath it. The Limit node reports twenty, which is what your application receives. The child node reports 10,020, which is what the database actually produced. The gap between those two numbers is pure waste, and it grows every page.
The BUFFERS line tells the more expensive half of the story: those discarded rows were real page reads. On a table larger than your cache, most of them came from disk.
This shape is why the bug survives review. Your seed data has two hundred rows. Your staging environment has a thousand. The offsets that hurt are the ones only production reaches, and by then the query is load-bearing.
#The correctness bug is worse and quieter
Now the part that is not about speed.
A user is reading a feed sorted newest first. They fetch OFFSET 0 LIMIT 20 and get rows 1 to 20. While they read, three new posts are created. They scroll, and the application requests OFFSET 20 LIMIT 20.
The offset is counted against the list as it exists now. Three rows were inserted at the top, so everything shifted down by three. Rows 18, 19 and 20, which the user has already seen, are now at positions 21, 22 and 23.
They get shown three duplicates.
Deletions produce the mirror image. Remove three rows from above the window and positions 21 to 23 shift up past the boundary. Those records are never displayed, and nothing anywhere reports an error. The user simply never sees them.
This is not an edge case on a busy table. It is the normal behaviour of any list that is sorted by recency and receives writes, which is most lists worth paginating. And unlike the performance problem, adding an index does not help at all: the count is wrong because the set changed, not because the scan was slow.
#Keyset pagination remembers a place instead of a count
The fix is to stop describing position by counting and start describing it by value. Instead of "skip 10,000 rows", say "give me rows that sort after this specific one".
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;That WHERE clause is a row-value comparison, and it is the clean way to express this in PostgreSQL. It compares the tuple lexicographically: first by created_at, and only where those are equal, by id. It is exactly the ordering in the ORDER BY, written as a filter.
The database now seeks directly into the index to the position described by the parameters and reads twenty rows forward. The work is the same whether the user is on page two or page two thousand, because no rows are produced only to be thrown away.
You could write the same condition expanded:
WHERE created_at < $1
OR (created_at = $1 AND id < $2)It is logically equivalent, but the row-value form is harder to get subtly wrong, and the planner handles it well against a matching index.
#The tiebreaker is not optional
created_at is almost never unique. Two posts created in the same millisecond have equal sort keys, and the database is under no obligation to order them consistently between queries.
If your cursor only records created_at, then at any page boundary that lands inside a group of ties you will either skip or repeat the tied rows, depending on which way the planner happened to order them that time. You will have reintroduced the exact bug you set out to fix, in a rarer and much harder to reproduce form.
So the cursor must include a genuinely unique column, and that column must appear in both the ORDER BY and the comparison. The primary key is the obvious choice. The rule is simple: the sort must be a total order. Any ambiguity in the ordering becomes an intermittent pagination bug.
#Without the right index this is not faster
Keyset pagination only avoids the scan if the index can satisfy the ordering directly.
CREATE INDEX posts_created_at_id_desc_idx
ON posts (created_at DESC, id DESC);The column order must match the ORDER BY, and any column you filter by on every request should sit in front of the sort columns:
-- For: WHERE author_id = $1 AND (created_at, id) < ($2, $3)
CREATE INDEX posts_author_created_idx
ON posts (author_id, created_at DESC, id DESC);PostgreSQL can read an index backwards, so a plain ascending index often serves a descending query perfectly well. Declaring the direction explicitly matters when the sort mixes directions, such as created_at DESC, id ASC, which a single-direction index cannot satisfy in one scan.
Confirm with EXPLAIN that you get an index scan and no Sort node. A Sort in the plan means the database is materialising and ordering the whole matching set before applying your limit, which is the original problem wearing a different hat.
#Make the cursor opaque
Do not hand clients ?created_at=2026-08-19T10:00:00Z&id=8814. Encode it:
type Cursor = { createdAt: string; id: number }
function encodeCursor(cursor: Cursor): string {
return Buffer.from(JSON.stringify(cursor)).toString('base64url')
}
function decodeCursor(raw: string): Cursor | null {
try {
const parsed = JSON.parse(Buffer.from(raw, 'base64url').toString())
// Never trust the shape. This value came from a URL and goes straight
// into a query parameter.
if (typeof parsed?.createdAt !== 'string') return null
if (!Number.isInteger(parsed?.id)) return null
return parsed
} catch {
return null
}
}Three reasons this is worth the extra function.
It keeps the sort key private, so you can change from created_at to a composite score, or add a third tiebreaker, without breaking every client that had learned to construct cursors by hand.
It signals correctly. An opaque string tells a client to pass it back untouched. A pair of readable query parameters is an invitation to build one, and somebody will.
It gives you one place to validate. That string arrives from a URL and ends up as a query parameter, so it needs checking regardless. Better to do it in a single function than at every call site.
Base64 is encoding, not encryption. If the cursor must not be forgeable, sign it with an HMAC and verify on the way in. Never put anything confidential in it.
The response then carries the cursor for the next page, and nothing else:
const rows = await db.query(sql, [cursor?.createdAt, cursor?.id])
const last = rows.at(-1)
return Response.json({
data: rows,
// Absent rather than null when the page is not full, so clients have an
// unambiguous end-of-list signal instead of guessing from row count.
...(rows.length === PAGE_SIZE && last
? { nextCursor: encodeCursor({ createdAt: last.created_at, id: last.id }) }
: {}),
})#What you give up, stated honestly
Keyset pagination is not free, and the trade is real.
No jumping to an arbitrary page. You can go forward and, with a reversed query, backward. You cannot go to page forty, because there is no cursor for a position you have never visited. For infinite scroll and "load more" this costs nothing. For a numbered pager it is a blocker.
No total count, at least not cheaply. People usually discover here that SELECT COUNT(*) was already expensive: PostgreSQL's MVCC model means it has to check row visibility, so an exact count scans. If you only need a rough figure, the planner's estimate is free:
SELECT reltuples::bigint AS approximate_rows
FROM pg_class
WHERE relname = 'posts';That value comes from the last ANALYZE, so it is approximate and can be stale. For "about 40,000 results" it is entirely adequate, and it costs one index lookup rather than a full scan.
Slightly more application code. Encoding, decoding and validating a cursor is more work than incrementing an integer.
#When OFFSET is the right answer
I would rather you keep OFFSET where it belongs than migrate on principle.
It is fine when the result set is small and bounded. Paginating a user's own payment methods will never reach an offset that costs anything.
It is fine when a numbered pager is a genuine product requirement and the data is small enough to afford it. Admin tables, internal reporting, back-office tools.
It is fine when the underlying data does not change during a session, because then the drift problem simply does not exist. A generated report, an archived export, an immutable dataset.
What it is not fine for is a large, actively written, recency-sorted list that users scroll. That is the exact case where both defects show up together, and it is also the most common list in any product.
#The one-line summary
OFFSET answers "how many rows should I ignore", which is a question whose answer changes underneath you. Keyset pagination answers "where was I", which does not.
#References
- PostgreSQL documentation: LIMIT and OFFSET
- PostgreSQL documentation: row and array comparisons
- Markus Winand, No Offset, the reference treatment of seek pagination