Your paginated list returns 10 results per page. User is on page 3. While they’re reading, someone inserts a new row at the top of the list. The user clicks “next”. The row they already saw on page 2 reappears on page 3 - it got pushed down by the insertion.

Or a row is deleted: they skip an entry entirely, and a product that exists doesn’t appear.

Offset pagination has this problem by design. It’s not a bug you can fix - it’s how the mechanism works.

What Offset Does

OFFSET 20 LIMIT 10 tells the database: skip the first 20 rows, return the next 10. The database doesn’t bookmark row 21. Every time you request page 3, it re-runs the query from the beginning, counts to row 21, and starts returning from there.

Two problems:

Data mutation between pages. If a row is inserted or deleted while a user is paginating, the “window” shifts. The rows at position 21-30 are different than they were when you computed page 2. You get duplicates or gaps. This isn’t a timing edge case - it happens regularly in any active system.

Cost at depth. OFFSET 100000 LIMIT 10 makes the database scan 100,010 rows to return 10. It discards the first 100,000. On a table with millions of rows, deep pagination is slow even with an index, because the index scan itself is O(offset + limit).

-- This query scans 100,010 rows to return 10
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 100000;

-- Execution time grows linearly with offset

For a product with a few thousand rows, neither problem is visible. For a product with millions of rows and active write traffic, both hit you in production.

How Cursor Pagination Works

Instead of “give me rows 100-110”, the client asks “give me 10 rows that come after row X”, where X is a reference to the last row they saw.

-- Cursor from last page was: created_at = '2026-07-15 14:23:00', id = 8847
SELECT * FROM posts
WHERE created_at < '2026-07-15 14:23:00'
   OR (created_at = '2026-07-15 14:23:00' AND id < 8847)
ORDER BY created_at DESC, id DESC
LIMIT 10;

This query uses a range condition on indexed columns. With an index on (created_at DESC, id DESC), the database seeks to the cursor position in O(log n) and scans forward 10 rows. It doesn’t touch the rows before the cursor.

The cursor encodes the position of the last row returned. The client passes it back on the next request. The API looks like:

// Response
{
  "data": [...],
  "next_cursor": "2026-07-15T14:23:00Z_8847",
  "has_more": true
}

// Next request
GET /posts?cursor=2026-07-15T14:23:00Z_8847&limit=10

On the server:

def get_posts(cursor=None, limit=10):
    if cursor:
        created_at, post_id = decode_cursor(cursor)
        posts = db.query("""
            SELECT * FROM posts
            WHERE created_at < %(created_at)s
               OR (created_at = %(created_at)s AND id < %(post_id)s)
            ORDER BY created_at DESC, id DESC
            LIMIT %(limit)s + 1
        """, {'created_at': created_at, 'post_id': post_id, 'limit': limit})
    else:
        posts = db.query("""
            SELECT * FROM posts
            ORDER BY created_at DESC, id DESC
            LIMIT %(limit)s + 1
        """, {'limit': limit})

    has_more = len(posts) > limit
    if has_more:
        posts = posts[:limit]

    next_cursor = encode_cursor(posts[-1]) if has_more else None

    return {'data': posts, 'next_cursor': next_cursor, 'has_more': has_more}

The LIMIT + 1 trick: fetch one more row than needed. If you get it back, there are more results. If not, you’re at the end. This avoids a separate COUNT query.

Why the Composite Cursor Matters

A common mistake: using only created_at as the cursor. What happens when two rows have the same timestamp? The cursor is ambiguous. Rows tied at the boundary may be included twice or skipped.

The composite cursor (timestamp + id) is unambiguous. Two rows cannot have the same combination. The OR condition handles the tie correctly: rows with an earlier timestamp, or rows with the same timestamp but a lower ID.

If your ordering column is unique (like an auto-increment ID), a single-field cursor is fine. Otherwise, always include the primary key as a tiebreaker.

-- Works reliably because (created_at, id) is unique
WHERE created_at < %(ts)s
   OR (created_at = %(ts)s AND id < %(id)s)

The Tradeoffs

Cursor pagination is not strictly better than offset. The choice depends on the use case.

Cursor pagination:

• Stable under concurrent writes - no duplicates or skips
• Constant performance regardless of depth
• Only supports sequential navigation (next/previous), not “jump to page 47”
• Requires stateful cursor from client
• More complex to implement

Offset pagination:

• Supports arbitrary page jumps and numbered UI (“Page 4 of 12”)
• Simpler to implement and debug
• Degrades under concurrent writes (duplicates and gaps)
• Slow at high offsets on large tables

Use cursor pagination when: the UI is infinite scroll or next/previous, the table has high write traffic, the dataset is large (hundreds of thousands of rows or more), or duplicate results would cause user-visible bugs.

Use offset pagination when: you need page numbers in the UI, the dataset is small and relatively static, or the development simplicity matters more than correctness under edge cases.

The one case where offset is genuinely acceptable: internal admin tools with bounded datasets (a few thousand rows) where the occasional duplicate on a busy day is an acceptable tradeoff for simplicity.