Skip and Take will betray you: offset vs keyset pagination

Every pagination tutorial teaches the same two lines, and they work beautifully in the demo:

var page = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

Clean LINQ, translates to a tidy OFFSET ... LIMIT, ships in ten minutes. And then, months later, the ops dashboard shows something odd: page 1 of the order history returns in a few milliseconds, while deep pages take longer, and the deeper the page, the longer they take. Same query. Same indexes. The only variable is the page number, and the response time is climbing with it, linearly, like the database is doing more work the further back you go.

It is. That's what OFFSET means.

OFFSET is "read and throw away"

Here's the part the tutorials don't spell out. This SQL:

SELECT o.id, o.created_at, o.total
FROM orders AS o
ORDER BY o.created_at DESC
OFFSET 40000 LIMIT 20;

does not jump to row 40,001. There is no jumping. The database walks the ordering (down the index if you're lucky, through a sort if you're not), produces rows one at a time, discards the first 40,000 of them, and hands you the next 20. The discarded rows aren't free; each one was located, read, and counted before being thrown away. Page 3 discards 40 rows and feels instant. Page 2,000 discards 40,000 and doesn't.

So offset pagination has a built-in property that no index can remove: the cost of a page grows with its distance from the front. On small tables you never notice. On the tables that actually need pagination (activity feeds, order histories, audit logs, the ones that grow forever), the deep pages get slower every month, and your friendliest users, the ones who scroll furthest or run export jobs page by page, are precisely the ones you punish.

There's a correctness hole too. Offsets are positions, and positions shift. If a new order arrives between your page-1 request and your page-2 request, every row slides down one: the last row of page 1 greets you again at the top of page 2, or a row slips through the crack between pages and is never seen at all. Any paginated export running against a live table quietly duplicates or drops rows this way.

Offset pagination

OFFSET 40000 LIMIT 20

40,000

rows read, then discarded

✗ cost grows with depth 20 kept

Keyset pagination

WHERE (created_at, id) < (@last_created_at, @last_id)

20

rows read, off the index

✓ flat cost at any depth 0 discarded

Keyset: remember where you stopped, seek to it

The alternative is to stop asking for positions and start asking for values. The client already saw the last row of the previous page; use it as the bookmark. "Give me the next 20 orders after this one" becomes a WHERE clause:

SELECT o.id, o.created_at, o.total
FROM orders AS o
WHERE (o.created_at, o.id) < (@last_created_at, @last_id)
ORDER BY o.created_at DESC, o.id DESC
LIMIT 20;

With an index on (created_at DESC, id DESC), this is a seek: the database descends the B-tree directly to the bookmark and reads 20 rows off the index, whether that's page 2 or page 20,000. The cost of a page no longer depends on where it is. And because the bookmark is a value, not a position, inserted rows can't shuffle your pages: "after this order" means the same thing no matter what arrived in the meantime.

In EF Core, the same idea reads like this:

var page = await db.Orders
    .OrderByDescending(o => o.CreatedAt).ThenByDescending(o => o.Id)
    .Where(o => o.CreatedAt < lastCreatedAt
        || (o.CreatedAt == lastCreatedAt && o.Id < lastId))
    .Take(pageSize)
    .ToListAsync();

Two details in there are load-bearing:

• The tiebreaker. CreatedAt alone isn't unique; two orders in the same timestamp would make "after this one" ambiguous, and rows could repeat or vanish at page boundaries. Adding Id to both the ORDER BY and the predicate makes the sort total and the bookmark exact. Every keyset query needs a unique final key.
• The API shape changes. Your endpoint stops taking ?page=57 and starts returning an opaque cursor (the encoded bookmark) alongside each page, which the client echoes back to get the next one. That's not incidental; it's the contract that makes the performance possible.

The honest trade-offs

Keyset pagination is not a strict upgrade, and pretending otherwise is how you end up misapplying it. You give up random access: there's no "jump to page 57," because page 57 isn't a place, and answering it would require exactly the row-counting that offset does. You give up easy "sort by any column the user clicks," since every sortable column needs a matching index and a tiebreaker'd cursor. Admin grids with a dozen sortable columns and a page picker are often better served by offset plus a sane depth cap.

The rule of thumb that holds up: infinite scroll, feeds, exports, and anything a machine walks sequentially want keyset. Small, human-browsed, jump-around grids can keep offset. Knowing which one you're building is most of the decision.

Seeing the difference instead of believing it

On paper this is a paragraph of theory. In a query plan it's one line: the offset version shows the rows it had to walk before your page began, and the keyset version shows an index seek that starts at your bookmark. Once you've read that difference in a real plan, off a table big enough for it to hurt, you'll never need the rule of thumb again; the Skip(40000) will just look expensive on sight.

That's the kind of rep Katabench's database track is built from: your LINQ runs against a real PostgreSQL database with enough rows that inefficiency shows up in the timings, you see every query your code generated, and the plan-graded puzzles go one step further, failing a solution whose plan does the walk-and-discard when a seek was available. The grading docs spell out how plan checks work. The track is on Pro, and the pages your users scroll to deepest will thank you for the practice.