Offset pagination loses rows while you read, and the user never knows

TL;DR#

LIMIT 20 OFFSET 40 means "skip the first 40 rows of the current result". If a row is inserted above the window between two requests, every subsequent offset points one row later — so one row is skipped entirely and, on a delete, one is returned twice.

Nobody notices, because the user cannot see what they did not receive. We found it in an export job: 50,000 orders paginated at 500 per page produced 49,880 rows, and the 120 missing ones were only visible against a count.

Keyset pagination fixes it by making the cursor a value in the data rather than a position in a result, which is stable under inserts. It is also faster, because the database stops counting rows it is about to discard.

The case this post is about#

A paginated orders API, and an export job that consumes it:

@Get("/orders")
suspend fun orders(page: Int, size: Int = 20): List<OrderView> =
    db.query("""
        SELECT * FROM orders
        WHERE user_id = :uid
        ORDER BY created_at DESC
        LIMIT :size OFFSET :offset
    """, offset = page * size)

// the export job
var page = 0
while (true) {
    val batch = api.orders(page = page++, size = 500)
    if (batch.isEmpty()) break
    csv.write(batch)
}

Standard, and correct on a static table.

The symptom#

An export with the wrong number of rows:

orders in the database for this user:  50,000
rows in the exported CSV:              49,880
missing:                                  120

And on a different run, duplicates:

rows in the CSV:   50,043
duplicate order ids:  43

The cause, visible once we logged the timeline:

t=0     page 0: OFFSET 0     rows 1–500 of 50,000
t=0.4s  a new order is created (it sorts FIRST — created_at DESC)
t=0.5s  page 1: OFFSET 500   → the row that was #500 is now #501
                              → it is SKIPPED

One insert above the window skips one row per subsequent page boundary. 120 inserts during a 40-second export, 120 skipped rows. A delete does the reverse: rows shift up, and the row that was #501 becomes #500 and is returned on both pages.

It was also slow, in a way that scaled badly:

page   OFFSET    query time
0           0        3ms
25     12,500       41ms
50     25,000       78ms
99     49,500      151ms

The database must locate and discard OFFSET rows before returning any. Page 99 reads 50,000 rows to return 500.

And nobody had noticed in the UI — a user scrolling a feed does not know that order #500 was never shown. The export is where it became countable, which is the only reason we found it at all.

Why the obvious fix failed#

"Sort by something stable." The sort was already stable-ish — created_at DESC — and that is not the problem. The problem is that OFFSET counts rows in a result set whose membership changes, regardless of how deterministically it is ordered.

"Snapshot the data first." A REPEATABLE READ transaction held across all 100 pages works for an export and is unusable for a user-facing API: you cannot hold a transaction open across HTTP requests, and holding one for 40 seconds blocks vacuum.

"Add a created_at < start_time filter." This genuinely fixes inserts, and we shipped it for the export. It does nothing for deletes or for updates that change the sort key, and it changes the semantics for a user-facing feed (new items never appear).

"Use a bigger page size so there are fewer boundaries." Fewer boundaries, same bug, slower queries. The loss rate falls and the per-page cost rises.

The actual mechanism#

OFFSET n is positional. It means "discard the first n rows of the result computed right now", and the result is recomputed on every request.

So between two requests, three things can change the mapping from position to row:

An insert sorting above the window shifts every later row down one position → the row at the boundary is skipped.

A delete above the window shifts every later row up one → the boundary row is returned twice.

An update to the sort key moves a row across the boundary in either direction.

A keyset cursor is value-based: "give me rows ordered after this specific row". That anchor is a fact about the data, not a count, so inserts and deletes elsewhere cannot move it.

offset:  "skip 500 rows"          → meaning depends on the current table
keyset:  "after (2026-11-02T10:14:22Z, order 88214)"   → meaning is fixed

OFFSET is a position in a result that no longer exists by the time you use it. A keyset cursor is a value in the data, which is why it is stable — and why it does not need the database to count rows it will throw away.

The fix#

Keyset pagination, with the tiebreaker that makes it correct.

@Get("/orders")
suspend fun orders(cursor: String? = null, size: Int = 20): PagedResponse<OrderView> {
    val after = cursor?.let { Cursor.decode(it) }

    val rows = db.query("""
        SELECT * FROM orders
        WHERE user_id = :uid
          AND (:after_ts IS NULL OR (created_at, id) < (:after_ts, :after_id))
        ORDER BY created_at DESC, id DESC
        LIMIT :limit
    """, limit = size + 1, after_ts = after?.createdAt, after_id = after?.id)

    val hasMore = rows.size > size
    val page = rows.take(size)
    return PagedResponse(
        items = page.map { it.toView() },
        nextCursor = page.lastOrNull()?.let { Cursor(it.createdAt, it.id).encode() }
            .takeIf { hasMore },
    )
}

Five decisions, each load-bearing:

(created_at, id) as a composite key. created_at alone is not unique — two orders in the same millisecond would make the cursor ambiguous and either skip or repeat them. The id tiebreaker is what makes the ordering total, and omitting it is the most common way a keyset implementation stays subtly broken.

Row-value comparison (a, b) < (x, y), which is the correct semantics for a composite cursor and compiles to an index-friendly predicate. The hand-written alternative is a disjunction that is easy to get wrong:

-- equivalent, verbose, and what people write when row values are unavailable
(created_at < :ts OR (created_at = :ts AND id < :id))

LIMIT size + 1 to compute hasMore without a separate COUNT, which on a large table is its own performance problem.

An opaque, encoded cursor. Clients must not construct or interpret it:

data class Cursor(val createdAt: Instant, val id: Long) {
    fun encode(): String = Base64.encode("$createdAt|$id")   // opaque to the client
    companion object { fun decode(s: String): Cursor = … }
}

Opacity is what lets you change the sort key later without breaking every client.

And a matching index, or none of this is fast:

CREATE INDEX orders_user_created_id_idx
    ON orders (user_id, created_at DESC, id DESC);

The export job becomes a loop over cursors:

var cursor: String? = null
do {
    val page = api.orders(cursor = cursor, size = 500)
    csv.write(page.items)
    cursor = page.nextCursor
} while (cursor != null)

One honest trade: keyset pagination cannot jump to "page 47". If your UI needs numbered pages, you need offset — and then you need to accept the instability, or bound it (search results capped at 10 pages, which is why most search UIs do exactly that).

How we proved it#

The export, counted:

50,000 orders, ~3 inserts/second during the run
                  rows exported   missing   duplicated
offset pagination        49,880       120            0
offset + created_at <    50,000         0            0   (inserts only)
keyset                   50,000         0            0

A deterministic test for the skip, which is what turned this from anecdote into a fixed bug:

@Test
fun aRowInsertedMidPaginationIsNotSkipped() = runTest {
    seedOrders(1_000)
    val page1 = api.orders(size = 100)

    insertOrder(createdAt = now())      // sorts FIRST under created_at DESC

    val page2 = api.orders(cursor = page1.nextCursor, size = 100)
    val seen = (page1.items + page2.items).map { it.id }

    assertEquals(seen.size, seen.distinct().size)                 // no duplicates
    assertTrue(expectedFirst200.all { it in seen || it == newId }) // nothing skipped
}

offset:  FAILED — 1 expected id missing from the union
keyset:  PASSED

And a delete test, because the two directions fail differently:

delete one row above the window, then fetch page 2
  offset:  1 duplicate id across the two pages
  keyset:  0 duplicates

Query performance, which was the unexpected bonus:

page position     offset    keyset
early (p0)           3ms       2ms
middle (p50)        78ms       2ms
late (p99)         151ms       2ms

Constant time, because the index seek lands directly on the cursor rather than counting forward.

And the missing tiebreaker, verified as a real failure rather than a theoretical one:

10,000 orders with 240 sharing a created_at millisecond
  keyset WITHOUT the id tiebreaker:  7 rows skipped, 4 duplicated
  keyset WITH it:                    0, 0

That result is why the composite key is in the fix rather than a footnote.

One limit: this assumes the sort key is immutable for a given row. If created_at can be edited — or if you paginate by a mutable field like updated_at — a row can move across the cursor and be skipped or repeated even with keyset. Paginating by a mutable key is stable only if you accept that; for a feed ordered by updated_at the honest answer is that no cursor scheme makes it exact.

What this generalizes to#

A positional reference into mutable data is a bug waiting for a write. Offsets, array indices held across mutations, line numbers in a changing file, "the third item". Anchor to an identity or a value, not to a position.

Silent data loss is the hardest kind to find. The user cannot see the row they did not receive. We found this only because an export could be counted against a total — which is an argument for having at least one consumer whose output you can reconcile.

A total ordering needs a tiebreaker. Any cursor, checkpoint, or resumption token built on a non-unique key is ambiguous at ties, and ties happen far more often than intuition suggests — 240 rows in one millisecond, in our case.

The stable option is often the fast one. Keyset is both correct under concurrent writes and O(1) per page instead of O(offset). When a correctness fix is also a performance fix, the original design was fighting the database's grain.

Related#

• The N+1 query that looked fast in development — another query shape that hides locally
• The index I added made the query slower, and the planner was right — reading the plan for the index above
• The webhook that arrived twice, and the order that shipped twice — duplicates from a different cause

Keep reading#

This is part of 100 practices of senior engineers — the habits that separate five years of experience from one year five times. Follow MobileByteSensei for the rest.