Designing Cursor Pagination APIs That Stay Correct Under Load

Introduction

Pagination looks simple until production traffic starts changing the data while users are paging through it. Offset pagination works for many small lists, but it becomes fragile when the table grows, records are inserted between requests, or users expect a feed to feel stable.

Cursor pagination solves a different problem: it lets clients ask for records after or before a known position in a deterministic ordering. That makes it a better default for activity feeds, audit logs, admin tables, inboxes, transactions, and APIs where repeatable traversal matters.

Why Offset Pagination Breaks Down

Offset pagination usually looks like this:

SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This is easy to understand, but it has two important problems.

First, large offsets can become expensive because the database still has to walk past skipped rows before returning the requested page. Indexes help, but the query shape still asks the database to count its way into the result set.

Second, offsets are unstable when data changes. If a new order is inserted at the top of the list after the client loads page one, OFFSET 20 no longer means "the next 20 records after the records I already saw." It means "skip the first 20 records that exist right now." That can cause duplicate or missing rows.

Offset pagination is still acceptable for small static lists, back-office screens with shallow navigation, and reports where the user cares about page numbers. Cursor pagination is stronger when correctness and scale matter.

Pick a Stable Sort Order

A cursor is only as good as the ordering behind it. The order must be deterministic, indexed, and unique enough to avoid ambiguity.

For most APIs, sort by a business timestamp plus a tie-breaker:

CREATE INDEX orders_created_id_desc_idx
ON orders (created_at DESC, id DESC);

Then query after the last item from the previous page:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

The extra row (LIMIT 21 when the public page size is 20) tells the API whether another page exists without running a separate count query.

Why include id?

Timestamps often collide. Two rows can share the same created_at, especially under batch imports or high write throughput. Adding id creates a total order, so the cursor points to exactly one position instead of a fuzzy timestamp boundary.

Encode Cursors as Opaque Tokens

Clients should not build cursors themselves. They should receive a token from the API and send it back unchanged. This keeps your API free to evolve the internal cursor shape without breaking clients.

function encodeCursor(row) {
  const payload = {
    createdAt: row.created_at.toISOString(),
    id: row.id,
  }

  return Buffer.from(JSON.stringify(payload), 'utf8').toString('base64url')
}

function decodeCursor(cursor) {
  try {
    return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'))
  } catch {
    throw new Error('Invalid cursor')
  }
}

For public APIs, consider signing the token with an HMAC so clients cannot tamper with sort keys. For internal APIs, base64-encoded JSON is often enough as long as the server validates the decoded fields.

Response shape

A practical response keeps pagination metadata separate from items:

{
  "items": [
    { "id": "ord_101", "createdAt": "2026-05-26T10:31:00Z", "total": 4900 }
  ],
  "pageInfo": {
    "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA1LTI2VDEwOjMxOjAwWiIsImlkIjoib3JkXzEwMSJ9",
    "hasNextPage": true
  }
}

Avoid exposing raw offsets and cursors together for the same endpoint. Mixed models usually create edge cases where clients combine incompatible assumptions.

Implement the Endpoint

Here is a compact Express-style handler using cursor pagination with a page-size cap:

app.get('/orders', async (req, res) => {
  const limit = Math.min(Number(req.query.limit || 20), 100)
  const cursor = req.query.after ? decodeCursor(String(req.query.after)) : null

  const params = []
  let where = ''

  if (cursor) {
    params.push(cursor.createdAt, cursor.id)
    where = 'WHERE (created_at, id) < ($1, $2)'
  }

  params.push(limit + 1)

  const result = await db.query(
    `
      SELECT id, created_at, total
      FROM orders
      ${where}
      ORDER BY created_at DESC, id DESC
      LIMIT $${params.length}
    `,
    params
  )

  const rows = result.rows.slice(0, limit)
  const hasNextPage = result.rows.length > limit
  const last = rows[rows.length - 1]

  res.json({
    items: rows.map((row) => ({
      id: row.id,
      createdAt: row.created_at,
      total: row.total,
    })),
    pageInfo: {
      nextCursor: hasNextPage && last ? encodeCursor(last) : null,
      hasNextPage,
    },
  })
})

This implementation intentionally avoids a COUNT(*). Counting can be expensive on large filtered datasets, and many product flows only need to know whether another page exists.

Handle Edge Cases Explicitly

Cursor pagination is reliable when the API states its rules clearly.

Deleted records

Deleted records usually do not break cursor pagination because the cursor uses sort-key values, not a pointer to a live row. The next query asks for rows before or after those values.

Updated sort fields

Avoid cursor pagination on mutable fields unless you understand the consequences. If a row's sort value changes while a user is paging, it can move across pages. Prefer immutable timestamps such as created_at for feeds and logs.

Reverse pagination

Reverse pagination requires the opposite comparison and ordering:

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 21;

After fetching, reverse the rows before returning them if the client expects the same visual order as forward pagination.

Filtering

Every field that affects the result set must be treated as part of the pagination contract. A cursor created for status=paid should not be reused for status=refunded. You can include filter values in signed cursor payloads or reject cursors when query filters change.

Conclusion and Next Steps

Cursor pagination is not a drop-in replacement for every UI, but it is the better default for APIs that traverse changing data. The core rules are straightforward: define a stable order, include a tie-breaker, index the order, encode opaque cursors, and return one extra row to detect whether another page exists.

When you add cursor pagination to an existing API, start with one endpoint that already has performance or duplicate-row issues. Add metrics for page size, cursor errors, and query duration. Those signals will tell you whether the new contract is improving both correctness and operational cost.