Why Your API Pagination Breaks When Records Are Deleted Mid-Page

Offset pagination is the default because it's obvious. LIMIT 20 OFFSET 40 means "give me rows 41 through 60." Every ORM supports it. Every SQL tutorial uses it. It maps directly onto page numbers in a UI.

It's also wrong in the presence of concurrent writes.

The Arithmetic of the Bug

Say you're paginating through 100 records, 20 per page. Page 2 is rows 21–40. Between the time the client requests page 2 and page 3, someone deletes row 30.

Now rows 31–50 shift left by one position in the ordering. Your page 3 query (OFFSET 40) starts at what was row 41. Row 40 was never returned. It was on page 2 when you fetched that, but it shifted to position 40 before you fetched page 3 — and offset 40 starts at position 41. Row 40 is silently skipped.

Now run it in reverse: someone inserts a row between page 2 and page 3. Rows shift right. Page 3 starts at what was row 40, which you already saw at the end of page 2. Duplicate.

The bug is invisible in a low-traffic, low-mutation environment. It surfaces in production when you're paginating through a table that's actively written to, or when users are slow readers on lists with background cleanup jobs running.

Cursor-Based Pagination

The fix is to anchor pagination to the data, not to a position in the result set.

-- Offset-based (broken under mutation)
SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 40;

-- Cursor-based (stable under mutation)
SELECT * FROM events
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

The cursor (last_seen_id) is the ID of the last record returned on the previous page. The next page is "everything with a higher ID." This query returns a stable result regardless of what's inserted or deleted before the cursor position — those records are simply excluded from future pages.

The index on id makes this fast. The query plan is a range scan, not a full table scan with an offset. At large offsets, cursor pagination is significantly faster than offset pagination for the same reason: OFFSET N requires scanning and discarding N rows; the cursor query starts at the right place.

Composite Sort Keys

Keyset pagination generalizes the pattern to composite sort keys. If you're sorting by updated_at DESC, id DESC:

SELECT * FROM events
WHERE (updated_at, id) < (:last_updated_at, :last_id)
ORDER BY updated_at DESC, id DESC
LIMIT 20;

The (updated_at, id) < comparison is a row-valued comparison — it lexicographically compares the tuple. This requires an index on (updated_at DESC, id DESC) to be efficient, but the query is correct and stable.

Composite cursors are opaque to the client. Encode them as a base64 string containing the JSON-serialized values. The client passes the cursor back; your server decodes it.

The Stripe Pattern

Stripe's API uses starting_after and ending_before parameters, each taking an object ID. This is keyset pagination with an ergonomic name. The implementation is exactly WHERE id > starting_after ORDER BY id LIMIT N.

Stripe explicitly documents that IDs are not sequential in a meaningful way — they're just opaque cursors. The client should not interpret them as numbers. This is the right stance: a cursor is a pointer into a dataset, not a position.

What Cursor Pagination Does Not Solve

Cursor pagination is not a magic fix for all pagination requirements.

Arbitrary page jumps: You cannot jump to page 7 with a cursor. You don't have a cursor for page 7 without traversing pages 1–6. If your UI requires "go to page 7," you need offset pagination or a different data model.

Exact total count: Cursor pagination doesn't tell you how many results exist. COUNT(*) on a large table is expensive, and the count changes as records are inserted and deleted. Most APIs return a has_more flag rather than a total count. If you need the count, compute it separately with a cache.

Reverse traversal without a second index: Going backward through a cursor-based list requires either a second query with reversed ordering (and a corresponding index) or storing the history of cursors client-side.

Migration Path

If you're changing a live API:

Add cursor support alongside offset. Accept both page and cursor parameters.
Document the deprecation of page in your changelog with a specific sunset date.
After one API version cycle, remove page support from the implementation.

Don't remove offset support without a deprecation period. Clients build retry logic and pagination state machines around your API's contract. Breaking page-based pagination silently is the kind of change that causes production incidents in systems you've never heard of.

Pagination Is a Consistency Problem

Offset pagination is a UX metaphor — "pages" — incorrectly implemented as a database query. The mismatch is that pages are conceptually stable (page 3 is always page 3) but offset-based pages are not stable under mutation.

Cursor pagination models the actual requirement: "give me the next N records I haven't seen yet, relative to the last one I have." That requirement is achievable with stable semantics. The page metaphor is not.

Most teams figure this out after a bug report from a user who noticed a missing record or a duplicate in a long list. The fix is straightforward. The lesson is that the pagination model in your API is part of your consistency contract, and most SQL tutorials don't mention that.

Building something? Share your progress on builds.anethoth.com — public build dossiers for software projects in progress. Free to list.