Quick answer: Offset pagination is simple and correct for static, small result sets, but it breaks down on live data: as rows are inserted or deleted between requests, pages skip or duplicate records, and deep offsets get slow because the database still counts and discards the skipped rows. Cursor pagination avoids both by returning an opaque marker that points to a stable position in an ordered set, so subsequent pages remain consistent regardless of churn and stay cheap. Choose offset for bounded admin tables and cursors for any feed, timeline, or API clients page through repeatedly.
Most APIs start with offset because it maps directly to a mental model and to SQL: page 3 is rows 20 through 30. The problems appear only under real usage, which is why they survive early testing. Two independent issues are often conflated. The correctness problem is that the row set moves between page requests. The performance problem is that OFFSET 100000 does not skip rows for free; the engine typically produces and discards them. Cursor pagination addresses both, but it changes what the client can do, so it is not a drop-in replacement.
How offset fails on changing data
Imagine a feed ordered newest first. A client fetches page 1, rows 1–20. Before page 2, a new row is inserted at the top, shifting everything down by one. The client requests offset 20, but the row that was at position 20 is now at 21, so it appears again on page 2. Deletions cause the opposite: rows jump up and an item is skipped. The pagination is correct against a snapshot and wrong against a moving table.
-- page 1 then page 2, with a row inserted in between SELECT id, created_at FROM events ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 0; -- meanwhile a new event arrives and shifts every row down SELECT id, created_at FROM events ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20; -- the boundary row is now returned twice
How a cursor stabilizes position
Instead of a numeric page, the response includes an opaque cursor representing the last row's ordering values. The next request passes that cursor and the query returns rows strictly after the marker. Because the condition references actual values rather than a counted position, earlier insertions and deletions do not shift the boundary: rows already seen stay behind it.
The tie-breaker on a unique column matters. Ordering by a non-unique timestamp alone leaves two rows with the same value ambiguous, so adding the unique id makes the ordering total and the cursor deterministic. Encode the cursor (base64url JSON) so clients treat it as opaque and never try to parse or construct one.
Offset versus cursor
| Dimension | Offset pagination | Cursor pagination |
|---|---|---|
| Consistency under writes | Skips or duplicates | Stable, no skip/duplicate |
| Deep-page cost | Grows with offset | Constant with index |
| Jump to a specific page | Yes | No, sequential only |
| Total count | Natural | Often expensive or omitted |
| Client mental model | Page numbers | Next/previous only |
| Best fit | Bounded admin tables | Feeds, timelines, infinite scroll |
The trade-off is real and worth stating. Cursors do not support jumping to page 50 or showing "page 3 of 12," because the position is opaque and the total may be unknown. For an admin dashboard that genuinely needs numbered pages over a bounded result set, offset is the honest choice. For user-facing feeds where clients move forward sequentially, cursors are strictly better.
Designing the contract
Keep the query parameters and response shape explicit in the OpenAPI document: the opaque cursor parameter, the fixed page size, and the response wrapper carrying items and the next cursor. Document that cursors are opaque and time-bounded. A stable, indexed sort order is part of the contract — changing the ordering invalidates outstanding cursors, so design for it (include a version or accept that old markers expire). Modeling this in the spec means generated clients and mocks all share the same shape instead of each inventing a pagination convention.
Frequently asked questions
Can I just add a snapshot or transaction to fix offset pagination?
A long-lived snapshot keeps one client's view consistent but is expensive to hold and does not help across independent requests or deep-offset performance. For a short bounded report it can work; for repeated feed access, cursors solve the problem without holding snapshot state.
What should the cursor contain?
The ordering values of the last row, typically a timestamp and a unique id, optionally with a version. Encode it so it is opaque to clients. Never expose raw internal row positions as a client-facing contract.
How do clients know when there are no more pages?
Return a null or absent next cursor when a page returns fewer than the page size, or when the following query would be empty. Keep the rule consistent so clients can terminate without guessing.
Do cursors work for bidirectional pagination?
Yes. Provide both next and previous cursors using the first and last rows of the current page, with reversed ordering for the backward query. Infinite-scroll UIs usually only need forward navigation, but bidirectional is straightforward to add.
This is part of the OpenAPI Guide — start there for the full set of API design decisions and how to express them in the contract.
Top comments (0)