Pagination Done Right: Cursor vs. Offset in Practice

Here's a query that's been copy-pasted into more codebases than anyone could count:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

It reads well. It passes code review. It works in every test you'll write, because your tests don't insert rows between page two and page three. And in production, on any table people are actively writing to, it will show some users the same post twice and quietly hide others from them entirely.

That second failure is the one worth talking about. Everyone knows OFFSET gets slow on deep pages. Fewer people have noticed that it's also wrong on a live table, and fewer still know that the popular fix, the cursor, has its own concurrency hole that doesn't show up in blog-post benchmarks. Let's go through both, properly.

Offset is a position, and positions move

LIMIT 20 OFFSET 40 doesn't mean "the third page of posts." It means "run the whole query again, from scratch, walk past the first 40 rows of whatever the result is right now, and give me the next 20." The page boundary is a number, and the number is recomputed against the current state of the table on every request.

Walk it through with a feed sorted newest-first:

1. You load page 1 (OFFSET 0). You see posts 100 down to 81.
2. While you're reading, two new posts get published: 101 and 102.
3. You click "next" (OFFSET 20). The query now counts from post 102. Positions 1 to 20 are 102 down to 83. Positions 21 to 40 are 82 down to 63.
4. Page 2 opens with posts 82 and 81. You already saw those.

Inserts ahead of your position push rows toward you, so you see duplicates. Deletes do the opposite. If two posts on page 1 get deleted before you click next, everything shifts up by two, and the two posts that were sitting at positions 21 and 22 slide onto page 1, a page you've already left. You never see them.

Use The Index, Luke puts it in one line: "the pages drift when inserting new sales because the numbering is always done from scratch." Microsoft's EF Core docs say the same thing about Skip/Take: "If any updates occur concurrently, your pagination may end up skipping certain entries or showing them twice." Slack hit exactly this as their APIs grew; their engineering write-up on moving away from offsets notes that with frequently added items, "the page window becomes unreliable, potentially skipping or returning duplicate results."

Duplicates are annoying. Skips are the dangerous one, because nothing looks broken. A user scrolling a feed won't notice. A nightly job paging through orders to push them into a warehouse also won't notice, and that's how you end up with a reconciliation report that's off by a handful of rows every day and nobody can say why.

The ORDER BY you forgot is its own bug

Before we even get to concurrency, there's a quieter version of the same problem. The Postgres docs are unusually blunt about it:

"When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows."

And then, a sentence later: the planner "takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET." Change the offset, maybe get a different plan, maybe get a different order. "This is not a bug," the docs add. SQL simply never promised you an order you didn't ask for.

So ORDER BY created_at DESC on its own isn't enough. The moment two posts share a timestamp (bulk imports, seeded data, anything written in the same transaction with now()), their relative order is up to the planner, and a page boundary that lands between them can hand you either one, or both, or neither. The EF Core docs add a detail people get wrong constantly: "relational databases do not apply any ordering by default, even on the primary key."

The fix is boring and mandatory: always end your sort with a unique column.

ORDER BY created_at DESC, id DESC

That tiebreaker matters for offset and for cursors. For cursors it's not optional at all, as you'll see in a second.

Why deep offsets are slow

The performance story is simpler, and it's the one everyone's heard, but the mechanism is worth a sentence. From the Postgres docs: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient."

There's no way around that with a B-tree. You'd think an index could just jump to "row 40,000," but it can't. As the CedarDB team points out, "the branches of a B-Tree do not contain a fixed number of tuples," so there's nothing to compute a jump from. The database walks the index from the start and throws away every row before your offset. Slack's version: "the database still has to read up to offset + count rows from disk." Page 1 costs 20 rows. Page 2,000 costs 40,000 rows to return 20.

How much that hurts depends on your data, your index, and whether the rows are cached, so I won't hand you a made-up millisecond figure. Markus Winand's benchmark chart on Use The Index, Luke shows the difference between offset and the seek method becoming "clearly visible from about page 20 onwards," and the curve only gets steeper from there.

Keyset: remember the row, not the number

The fix for both problems is the same idea. Instead of telling the database how many rows to skip, tell it where you stopped. This is keyset pagination, also called the seek method.

-- page 1
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- page 2: pass in the (created_at, id) of the last row on page 1
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

That WHERE (created_at, id) < ($1, $2) is a row-value comparison. It's lexicographic: compare created_at first, and only if they're equal, compare id. With an index on (created_at, id), Postgres seeks straight to that point in the index and reads 20 entries. Page 2,000 costs about what page 1 costs.

CREATE INDEX posts_created_at_id_idx ON posts (created_at, id);

A B-tree can be scanned in either direction, so this one index serves both ASC, ASC and DESC, DESC.

And here's why it survives concurrent writes: the boundary is a value, not a position. New posts published after you loaded page 1 have a larger created_at, so they sort before your bookmark and the < filter excludes them. Posts deleted from page 1 don't matter, because you're not counting them. As the CedarDB post puts it, "the last element that we saw is never part of the next page."

This is also why the tiebreaker is mandatory here. If you paginate on created_at alone and 30 rows share the same timestamp, WHERE created_at < $1 skips every one of them that didn't fit on the previous page. With (created_at, id), the id picks up exactly where you left off inside the tie.

The database-specific bits

Row values are standard SQL, but support is uneven, and this is where a lot of keyset implementations silently fall back to a full scan.

• PostgreSQL handles it well. Use The Index, Luke notes that PostgreSQL (since 8.4) and Db2 LUW (since 10.1) properly support row value predicates and use them to access the index.
• MySQL evaluates the row comparison correctly, but its optimizer doesn't always use the full index for it. The MySQL 8.4 manual's section on row constructor optimization shows (c2, c3) > (1, 1) using only part of an index, and says: "Rewriting the row constructor expression using an equivalent nonconstructor expression may result in more complete index use."
• EF Core can't express row values in LINQ yet (the docs point at issue #26822), so you write the expanded form.

The expanded form works everywhere. It's uglier, and it's what you should reach for on MySQL:

-- MySQL: expand the row comparison so the optimizer can use the whole index
SELECT id, title, created_at
FROM posts
WHERE created_at < ?
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Check the plan with EXPLAIN either way. A keyset query that ends up doing a filesort over the whole table is an offset query with extra steps.

Gotchas that bite in production

Mixed sort directions. ORDER BY price ASC, id DESC can't be expressed as a single row-value comparison, because the tuple comparison runs every column in the same direction. You have to write the expanded OR form by hand, with > on one column and < on the other. Easier: keep the tiebreaker in the same direction as the main key unless the product really needs otherwise.

Nullable sort columns. A comparison against NULL isn't true or false, it's NULL, so rows where the sort key is NULL fall out of a WHERE (a, id) < (...) filter. If you sort on a nullable column, you need NULLS FIRST/NULLS LAST plus explicit handling in the predicate, or you coalesce to a sentinel. Better yet, paginate on columns that are NOT NULL.

Mutable sort keys. Keyset handles inserts and deletes cleanly, but if you sort by something that changes, like updated_at or score, a row can move from ahead of your bookmark to behind it while you're paging. It'll either be skipped or show up twice, and no cursor design fixes that. It's inherent to paginating by a value that moves.

The concurrency hole cursors don't close

This is the part most pagination articles leave out, and it's the one that matters most if you're using pagination for sync rather than for a human scrolling a feed.

Keyset assumes that once you've read past a key, no new row will ever appear behind it. For ascending ids and timestamps, that sounds safe. It isn't. The Sequin team spells it out: "There are no locks on Postgres sequences, so Postgres sequences can commit out-of-order. And timestamps are no better; when setting updated_at to now(), you can have a row with an older timestamp commit after a row with a newer timestamp."

Here's the sequence, as a hypothetical:

1. Transaction A inserts an order and gets id = 1001 from the sequence. It then does some slow work and hasn't committed.
2. Transaction B inserts an order, gets id = 1002, and commits right away.
3. Your sync job pages forward: WHERE id > 1000 ORDER BY id LIMIT 100. It sees 1002 (committed) but not 1001 (not visible yet). It stores 1002 as its cursor.
4. Transaction A commits. Row 1001 now exists.
5. The next run asks for id > 1002. Row 1001 is never read.

No duplicates, no errors, one missing order. It's the exact failure that offset has, just rarer and harder to reproduce, because it needs two transactions to overlap at the page boundary. now() in Postgres returns the transaction start time, so a long transaction can commit a row with a timestamp older than rows that are already visible, and the same skip happens with created_at cursors.

For a user scrolling a feed, this rarely matters. For a job whose whole purpose is "don't miss any rows," it's the bug. The mitigations all trade something:

• Lag the cursor. Only page up to rows older than some safety margin, like created_at < now() - interval '1 minute', so in-flight transactions have time to commit before you read past them. The margin has to be longer than your longest write transaction, and you give up freshness.
• Re-read an overlap window. Start each run a bit before the last cursor and dedupe on id. Cheap and effective if your consumer is idempotent, which it should be anyway.
• Use the transaction snapshot. Postgres exposes the snapshot through functions like pg_current_snapshot() and pg_snapshot_xmin(), which tell you the oldest transaction still in flight. Change-data-capture tools lean on this kind of information. It's more machinery than most teams need.
• Don't paginate for sync at all. If what you really want is "every change, exactly once," a change feed (logical replication, an outbox table, CDC) is the right tool. Pagination is for reading a list, not for replicating one.

Sequin's own verdict is honest: "There isn't a clean silver bullet here." I'll go further. If your sync job is built on WHERE id > :last and nobody on the team has thought about commit order, it's probably missing rows right now, and you should go check.

"Cursor" is an API contract, keyset is the query

People use "cursor pagination" and "keyset pagination" interchangeably. They're related but different, and separating them makes the design easier.

Keyset is the SQL technique: WHERE (sort_key, id) > (...).

Cursor is the API contract: the server hands the client an opaque token, and the client passes it back to get the next page. What's inside the token is the server's business. Usually it's a keyset position, but it could be anything.

(And neither of these is a database-side cursor, the DECLARE ... CURSOR kind that holds a result set open on the server between fetches. That's a third thing, and it doesn't fit stateless HTTP.)

Look at how real APIs do it:

• Stripe uses object ids as cursors: starting_after and ending_before, which "are mutually exclusive," with limit between 1 and 100 (default 10), and a has_more boolean in the response.
• Slack moved from offsets to a cursor parameter and returns next_cursor inside response_metadata, empty when you've reached the end. Their cursors are base64 strings; the example in their write-up decodes to user:W07QCRPA4.
• Shopify removed the page parameter from the REST Admin API in version 2019-07 (the remaining endpoints followed in 2019-10) and switched to cursor-based pagination via page_info and the Link header.
• GraphQL has a standard for it: the Relay Cursor Connections spec, with first/after, edges { cursor node }, and a pageInfo object carrying hasNextPage, hasPreviousPage, startCursor, and endCursor.

The opaque part is the important design choice. If your API takes ?after_created_at=...&after_id=..., you've published your sort key as part of your public contract, and you can never change the index or the ordering without breaking clients. Wrap it in a token and you can.

Encoding it is a few lines. Two rules: encode the full sort key (not just the id), and treat the incoming token as untrusted input, because base64 is not encryption and clients will decode it and fiddle with it.

:::tabs

type Cursor = { createdAt: string; id: number };

export function encodeCursor(c: Cursor): string {
  return Buffer.from(JSON.stringify(c)).toString("base64url");
}

export function decodeCursor(token: string): Cursor {
  const raw = JSON.parse(Buffer.from(token, "base64url").toString("utf8"));
  if (typeof raw?.createdAt !== "string" || !Number.isInteger(raw?.id)) {
    throw new Error("invalid cursor");
  }
  return { createdAt: raw.createdAt, id: raw.id };
}

package pagination

import (
    "encoding/base64"
    "encoding/json"
    "errors"
    "time"
)

type Cursor struct {
    CreatedAt time.Time `json:"createdAt"`
    ID        int64     `json:"id"`
}

func Encode(c Cursor) (string, error) {
    b, err := json.Marshal(c)
    if err != nil {
        return "", err
    }
    return base64.RawURLEncoding.EncodeToString(b), nil
}

func Decode(token string) (Cursor, error) {
    var c Cursor
    b, err := base64.RawURLEncoding.DecodeString(token)
    if err != nil {
        return c, errors.New("invalid cursor")
    }
    if err := json.Unmarshal(b, &c); err != nil || c.ID <= 0 {
        return c, errors.New("invalid cursor")
    }
    return c, nil
}

import base64
import json
from datetime import datetime


def encode_cursor(created_at: datetime, row_id: int) -> str:
    payload = json.dumps({"createdAt": created_at.isoformat(), "id": row_id})
    return base64.urlsafe_b64encode(payload.encode()).decode().rstrip("=")


def decode_cursor(token: str) -> tuple[datetime, int]:
    padded = token + "=" * (-len(token) % 4)
    try:
        raw = json.loads(base64.urlsafe_b64decode(padded))
        return datetime.fromisoformat(raw["createdAt"]), int(raw["id"])
    except (ValueError, KeyError, TypeError) as exc:
        raise ValueError("invalid cursor") from exc

:::

If you'd rather clients couldn't tamper with it at all, sign the payload with an HMAC and reject tokens whose signature doesn't match. Since the decoded values only ever go into bound query parameters, a forged cursor can't do much harm; it just lands the client on a different page. Validating the shape is the minimum.

One more trick every cursor API should use: fetch limit + 1 rows. If you got the extra row, there's a next page; drop it and return a next_cursor built from the last row you keep. That gives you has_more without a second COUNT(*) query.

What you give up

Keyset isn't free, and pretending otherwise is how teams end up ripping it out again.

No jumping to page 47. A cursor only knows "after this row." Use The Index, Luke lists it plainly: you "cannot fetch arbitrary pages." Slack's write-up admits their cursor scheme gave up page jumping and total counts too.

No cheap total. "Page 3 of 1,204" needs a COUNT(*) over the whole filtered set, which on a big table costs about as much as the deep offset you just got rid of. Offset UIs usually pay for it quietly on every request.

Backwards is extra work. To go to the previous page you flip every comparison and sort direction, run the query, then reverse the rows in memory before returning them. It's mechanical, but it's code you have to write and test.

Sort options multiply. Every sort the UI offers needs its own index and its own cursor shape. "Sort by any column, ascending or descending" turns into a lot of indexes.

So which one?

Here's my position: keyset behind an opaque cursor should be your default for anything that's an API, a feed, an infinite scroll, or a batch job. Offset should be the exception you choose on purpose, not the thing you get because it was in the ORM tutorial.

Offset is fine when:

• The table is small, and will stay small, so deep pages never exist.
• The data barely changes while people page through it (a reference list, last quarter's archived invoices).
• Humans really do need numbered pages and random jumps, like an admin grid where someone says "it's on page 12."

Keyset wins when:

• The table is large or growing without bound.
• Rows get inserted or deleted while clients page through, which is to say most tables you actually care about.
• Clients are programs, not people. A program never wants page 47; it wants "everything after what I already have."

And if you need both, the EF Core docs suggest the sensible hybrid: use keyset "when navigating to the next/previous page, and offset navigation when jumping to any other page." Jumps are rare and nobody expects them to be perfectly consistent. Next and previous are the hot path, and they get the correct, fast version.

Whichever you pick, the checklist is short. Order by a unique key. Index exactly that order. Never trust the client's cursor. And if pagination is feeding a job that must not miss a single row, remember that neither offset nor keyset gives you that on its own. Out-of-order commits will get you eventually, so build in an overlap or a lag, or use a real change feed.

Originally published at andriiboyko.com.