Offset pagination works fine until your table has real data and real writes happening concurrently. Then it gets slow, and it starts skipping or duplicating rows. Cursor pagination fixes both, at the cost of a little more code.

Why offset falls apart

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

The database still has to scan and discard the first 10,000 rows before it can return your 20. That scan gets more expensive as the offset grows — page 1 is instant, page 500 is not. On a large table this turns pagination into the slowest endpoint in your API.

Worse than the slowness: correctness. If a row is inserted or deleted between page 1 and page 2, every row after it shifts by one. Users see the same row twice, or never see a row at all, depending on which direction the shift goes. This is silent — nothing errors, the data is just wrong.

How cursor pagination fixes it

Instead of “skip N rows,” a cursor says “give me rows after this specific one”:

SELECT * FROM orders
WHERE created_at < '2026-03-01T12:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

The cursor is the created_at (or id) of the last row you saw. The database uses the index to jump straight there — no scanning past discarded rows, and the cost stays flat no matter how deep you page.

app.get('/orders', async (req, res) => {
  const { cursor, limit = 20 } = req.query
  const where = cursor ? { created_at: { lt: new Date(cursor) } } : {}

  const orders = await db.orders.findMany({
    where,
    orderBy: { created_at: 'desc' },
    take: Number(limit) + 1, // fetch one extra to know if there's a next page
  })

  const hasMore = orders.length > limit
  const page = hasMore ? orders.slice(0, limit) : orders

  res.json({
    data: page,
    next_cursor: hasMore ? page[page.length - 1].created_at : null,
  })
})

The tie-breaker you’ll forget

created_at alone isn’t unique — two orders can share a timestamp, especially with bulk inserts. Without a tie-breaker, rows with the same timestamp can be skipped or duplicated across pages. Add the id as a secondary sort key and a compound cursor:

SELECT * FROM orders
WHERE (created_at, id) < ('2026-03-01T12:00:00Z', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Encode both values in the cursor (base64 a JSON blob of {created_at, id} if you want an opaque token clients can’t reason about or tamper with).

When offset is still fine

• Admin tables under a few thousand rows, where “page 40” as a concept matters more than performance.
• UIs that need “jump to page 12,” which cursors can’t do — cursors only support next/previous.
• Anything where the underlying data barely changes, so the shifting-row problem doesn’t come up.

Default to cursor for anything customer-facing and growing. Reach for offset only when you specifically need page-number jumping and the table is small enough that the performance cliff never arrives.