When LIMIT/OFFSET Stops Working: Choosing Between Offset and Cursor Pagination

I added pagination to a large report expecting it to make the query cheaper. It made the response smaller. For users who went deep into the result, the database work barely changed.

The first version used LIMIT and OFFSET, the obvious choice: easy to implement, and it maps directly onto "page 12 of 40". The report combined several filters with an ordering over a large table. The further a user paged, the slower each request became.

A note on evidence before going further: I am not publishing that report's execution plans or timings. The SQL below uses a simplified payments table to show the access pattern. It is illustrative, not a reproduction of the original investigation.

Fifty rows out, ten thousand rows of work

SELECT *
FROM payments
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 10000;

LIMIT 50 describes the output. It says nothing about how much work produces it. To return rows 10,001 to 10,050, the database has to produce the first 10,000 rows in order and throw them away. PostgreSQL's documentation says it directly: rows skipped by OFFSET still have to be computed inside the server, so a large OFFSET can be inefficient. PostgreSQL: LIMIT and OFFSET

With an index that matches the ordering, that means walking 10,050 index entries and fetching their rows. Without one, it can mean sorting the whole filtered set first. Either way, the cost grows roughly linearly with how deep the page is, and the filters and joins in a real report multiply that per-row cost.

That was the mismatch in my report. Pagination reduced the bytes sent to the client. Each deep page still paid for every page before it.

A cursor gives the database a place to start

Keyset pagination, often exposed as a cursor, replaces "skip N rows" with "continue after this row". The client sends back the sort key of the last row it received:

SELECT *
FROM payments
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Here $1 and $2 are the created_at and id of the last row on the previous page. With a matching index, the database can seek to that position and read the next 50 entries, wherever they fall in the result set:

CREATE INDEX idx_payments_created_at_id
ON payments (created_at DESC, id DESC);

An ascending (created_at, id) index works too, since a B-tree can be scanned backward. What matters is that both columns sort in a consistent direction.

A deep page now costs roughly what the first page costs. That is the whole advantage: the database receives a boundary to seek to, so the depth of the page stops mattering.

That holds for this unfiltered example. A real report adds filters, and if the index does not cover them, the seek still lands in the right place but may walk past many non-matching entries before it finds 50 rows. Equality filters fit best as leading index columns, for example (account_id, created_at DESC, id DESC) for a report scoped to one account.

Three details that decide whether the cursor works

A tie-breaker in the sort key. Ordering only by created_at is not deterministic when two rows share a timestamp. A page boundary in the middle of a tie can skip or repeat rows. Adding a unique column, usually the primary key, makes every position in the ordering exact.

The predicate shape. The same boundary can be written as an expanded OR:

WHERE created_at < $1
   OR (created_at = $1 AND id < $2)

Logically this matches the row comparison above, which compares tuples lexicographically. PostgreSQL: Row Constructor Comparison The planner does not necessarily treat the two forms the same way. The row-value form is the one you would expect to appear as a single index condition on a matching multicolumn B-tree, while the OR form can end up as a filter over a wider scan. Do not take either behavior on trust: run EXPLAIN and check whether the boundary appears under Index Cond or under Filter.

The row-value form has one restriction: every column has to sort in the same direction. ORDER BY created_at DESC, id ASC cannot be expressed as one tuple comparison and needs the expanded form, or an index and cursor designed around it.

Unchanged filters and assumptions. These examples assume created_at is non-null, id is unique, and the filters stay the same between pages. A cursor resolves ties, but it does not freeze the data. If a row's sort key changes between requests, it can cross the boundary and appear twice or not at all. A report that must reflect a single moment needs a snapshot or a materialized result.

What you give up

Cursor pagination changes the product as well as the query:

• No jumping to page 20. A cursor knows only "next" (and "previous", by reversing the comparison and ordering, then reversing the returned rows again for display).
• No cheap page count. "Page 12 of 40" needs a total, and a COUNT(*) over the filtered set can cost as much as the deep OFFSET you were avoiding.
• An API contract to design. The cursor is usually encoded as an opaque token, so clients do not depend on its internals. That token also has to be validated, since it now goes into a query.
• An index tied to the ordering. Each sortable column combination the UI offers needs its own matching index, or it loses the seek.

Offset is still the right choice in many places: small result sets, admin screens, and any UI where users genuinely jump to a page number and rarely go deep. For those, the skipped rows cost little, and the simpler contract is worth more.

The rule I took from the report

Choose pagination based on how people will move through the data. Ask:

1. Do users read sequentially, as in a feed, an audit log, an export or an API sync, or do they jump to arbitrary pages?
2. How deep do they realistically go?
3. How expensive is each row before the page is cut: filters, joins, sorting?

Sequential reading over deep or expensive results points to a cursor. Shallow, random-access navigation over cheap queries points to offset. When users need both, a common compromise is offset for the first few pages and a cursor, or a narrowing filter, beyond them.

In my report, pagination was decided as a UI detail, when it was a query design decision. It determines how much the database pays for each page after the first.