Offset vs Cursor vs Keyset: The Pagination Decision That Breaks Your API at Scale

The difference is invisible until page 500. Then your database starts telling the truth.

You shipped the endpoint in November. It passed the load test. Page 1 returned in under 10 milliseconds and stayed there with 500 concurrent users. Your CTO signed off, your QA passed and nobody thought about pagination again.

Three months later, a client support ticket arrived. Page 22 was missing an item that appeared on page 21. Sometimes page 7 showed a record that had already appeared on page 6. You looked at the query. OFFSET 5000. The table had grown since launch. That query was taking several seconds on the deepest pages. And the data wasn't even consistent because rows had been inserted while users were clicking through.

Pagination is not a solved problem. Three strategies exist. Each one makes a different contract with the database and each one breaks in a different way.

What “Skip to Page N” Actually Asks Your Database to Do

Every pagination strategy is solving the same problem: given a large dataset, return a specific slice. The strategies diverge in what they tell the database to do to get there.

The naive approach, the one in every tutorial and the one most ORMs produce by default, uses OFFSET. Here is what it looks like:

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 251 (offset = 250 * 20 = 5000)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 5000;

These two queries look nearly identical. They are not. The second asks the database to find, read and discard 5,000 rows before returning the 20 you actually need. The work scales linearly with the offset value. There is no shortcut.

Offset Pagination: The Default That Hides Its Costs

OFFSET is the default in every major framework. Laravel’s paginate(), Django's built-in paginator, Rails's page(). They all produce OFFSET queries. The appeal is obvious: simple to implement, easy to explain, lets users jump to any page number without tracking state.

The cost surfaces at scale. EXPLAIN ANALYZE tells the story clearly. On a table with 250,000 rows:

-- Page 1 (OFFSET 0)
-- Execution Time: 0.125 ms | Actual rows scanned: 20

-- Page 201 (OFFSET 20000)
-- Execution Time: 5.810 ms | Actual rows scanned: 20,100

That 5.81ms figure means the database visited 20,100 rows to return 20. The index is still used; it still sorts by created_at, but it walks 20,000 entries before it starts returning data. At OFFSET 200,000, the same math applies: 200,020 rows visited, 20 returned.

The second problem is correctness. If another request inserts or deletes a row while a user is clicking through pages, the OFFSET calculation shifts underneath them. Page 3 starts one row earlier than where page 2 ended. Items appear twice. Items vanish. The database did exactly what you told it to do. The position you specified was valid. But the data at that position changed while nobody was watching.

In Laravel, paginate(20) generates the OFFSET query plus a separate COUNT(*) to calculate total pages. Two queries per request, on every page load. There is a middle option: simplePaginate(20) skips the COUNT query and only tells you whether a next page exists. Still uses OFFSET, still has the performance problem, but cuts one round-trip.

When offset pagination is actually fine: datasets under 50,000 rows that don’t grow continuously, admin panels where users need to jump to “page 47 of 52” and interfaces where occasional slowness is acceptable. The rule of thumb: if your table grows faster than you can answer the question “what is on page 500?”, offset is not your friend.

Cursor Pagination: The Right Direction, Not the Complete Answer

Cursor pagination eliminates the offset. Instead of “skip 5,000 rows,” it tells the database “start after this specific item.”

-- Page 1: no cursor needed
SELECT * FROM posts ORDER BY id DESC LIMIT 20;
-- Returns rows, last one has id = 98765

-- Page 2: use the last id as the cursor
SELECT * FROM posts WHERE id < 98765 ORDER BY id DESC LIMIT 20;

The database seeks the index entry for id 98765 and reads forward. It never looks at the rows before it. Performance is constant at any depth. Page 1 and page 10,000 cost the same, because both are index seeks on the same column.

// Laravel: paginate() uses OFFSET
$posts = Post::orderBy('created_at', 'desc')->paginate(20);

// Laravel: cursorPaginate() uses WHERE clause, no OFFSET
$posts = Post::orderBy('id', 'desc')->cursorPaginate(20);
// Response includes: next_cursor, prev_cursor (base64 encoded column values)
// No COUNT(*) query. One query per page, constant cost.

Laravel’s cursorPaginate() (available since version 8.41) encodes the cursor as a base64 string containing the column values. The next page request decodes that cursor and builds a WHERE clause instead of an OFFSET. No total page count, no COUNT(*), no row-skipping.

The API response also changes shape. With offset pagination, the client receives a total page count and can calculate progress: “page 6 of 52.” With cursor pagination, the response includes a next_cursor token and no total. Clients know whether another page exists, nothing more. This is a client-facing contract change, not just an implementation detail. If your frontend team is building page number UI, this matters before you switch strategies.

What you lose is random access. With OFFSET you can jump to page 47. With cursor pagination, you navigate forward or backward only. A URL like ?page=47 has no equivalent here. If your interface requires visible page numbers, a search result list where users reference "I was on page 6," cursor pagination breaks that expectation.

The hidden problem appears when your sort column isn’t unique. Sort by id (auto-increment): no ties, no problem. Sort by created_at: two posts created within the same second share a timestamp. If the cursor encodes only created_at, a group of same-timestamp rows may be split incorrectly across pages. Items from that timestamp window can be duplicated or skipped entirely.

Keyset Pagination: What Cursor Pagination Becomes When You Handle It Properly

Keyset pagination is cursor pagination with a compound sort key. Instead of a single cursor column, it uses a composite value that is guaranteed unique: a timestamp paired with an auto-increment id.

-- Keyset pagination with a compound cursor (sort: created_at DESC, id DESC)
SELECT * FROM posts
WHERE (created_at < '2026-03-15 10:00:00')
   OR (created_at = '2026-03-15 10:00:00' AND id < 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The OR condition handles ties. When two rows share the same created_at, the id column breaks the tie. The result is deterministic. Every page starts exactly where the last one ended, regardless of concurrent writes, regardless of inserts happening between page fetches.

The index must match the ORDER BY exactly:

CREATE INDEX idx_posts_created_at_id
ON posts (created_at DESC, id DESC);

If the index directions don’t match (say you created (created_at ASC, id ASC) but query ORDER BY created_at DESC, id DESC), PostgreSQL falls back to a sequential scan. The query still returns correct results. Just very, very slowly. This is the failure most teams don't catch because it only becomes visible at production data volumes. Run EXPLAIN ANALYZE before you ship. Look for "Seq Scan" on your posts table. That is the symptom.

The Decision Framework

Use offset when:

1. Dataset is small (under 50,000 rows) and doesn’t grow continuously
2. Users need to jump to arbitrary page numbers
3. You are building an admin interface where occasional slowness is acceptable

Use cursor when:

1. The primary sort column is unique (auto-increment id)
2. The interface is “load more” or infinite scroll, with no page numbers needed
3. Performance must stay constant regardless of how much data accumulates

Use keyset when:

1. Sort column is non-unique (timestamps, status values, user-defined ordering)
2. Data changes frequently while users are paginating (activity feeds, notification logs, audit trails)
3. You need cursor pagination speed without the tie-breaking failure

Three Strategies, Three Contracts

Offset pagination

• Performance at depth: degrades linearly. OFFSET 100,000 scans 100,020 rows to return 20.
• Data consistency: rows can be skipped or duplicated on concurrent writes.
• Random page access: supported. Jump to any page number.
• Reach for it when the dataset is small, stable and the UI actually needs page numbers.

Cursor pagination

• Performance at depth: constant. The DB seeks to the cursor position and reads only the page size.
• Data consistency: stable when sort column is unique. Unreliable when it isn’t.
• Random page access: not supported. Forward and backward navigation only.
• The trade-off: fast and correct, but you lose page numbers and must manage cursor state between requests.

Keyset pagination

• Performance at depth: constant, same as cursor.
• Data consistency: most stable. Compound sort key handles concurrent writes correctly.
• Random page access: not supported.
• Worth the extra complexity when the sort column is non-unique or data changes frequently between pages.

The Index Check That Catches the Failure Before Users Do

The most common production problem with keyset pagination isn’t the query logic. It is the index. Teams write the compound WHERE clause, test on 5,000 rows in development, see fast results and ship. Two months later at 2 million rows, an endpoint that should return in 2ms returns in 900ms. Nobody changed the query.

Run this before you ship any cursor or keyset endpoint:

EXPLAIN ANALYZE
SELECT * FROM posts
WHERE (created_at < '2026-03-15 10:00:00')
   OR (created_at = '2026-03-15 10:00:00' AND id < 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Check two things in the output. First: “Index Scan” or “Index Only Scan.” If you see “Seq Scan,” the index isn’t being used. Second: the “actual rows” value. It should be close to your page size (20), not 20,000. If it’s reading tens of thousands of rows to return 20, the composite index is either missing or the directions don’t match.

The fix is always mechanical: confirm the index exists with the exact column order and direction, drop and recreate it if needed, re-run EXPLAIN ANALYZE to confirm the plan changed. This takes five minutes. Not catching it takes six months.

What Makes This Decision Hard to Undo

The pagination strategy you ship becomes an API contract. Once clients are consuming ?page=N, migrating to ?cursor=X requires new query parameters, new response fields and a deprecation period. It is a breaking change. You cannot refactor this quietly in a patch version.

The time to make this decision is before launch. Not after your table reaches 2 million rows and monitoring flags the reports endpoint.

Offset for small stable data. Cursor when your sort key is unique. Keyset when it isn’t. The wrong choice doesn’t fail immediately. It fails three months later, on page 250, in a client support ticket you weren’t expecting.