DEV Community

tanmay kuamr chaurasia
tanmay kuamr chaurasia

Posted on

Offset vs Cursor Pagination: Why Your API Breaks at Scale

Most APIs start with offset-based pagination. It feels natural: GET /items?page=500&limit=20 is easy to understand, easy to build, and works seamlessly when your database tables contain a few thousand records.

However, once your database table grows to hundreds of thousands or millions of rows, offset pagination quietly degrades performance and causes subtle data anomalies that frustrate end users.

Here is why offset pagination fails at scale and how cursor-based pagination fixes it.

The Hidden Cost of OFFSET

To understand why offset pagination slows down, look at what the database engine does under the hood when executing an OFFSET query.

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
Enter fullscreen mode Exit fullscreen mode

When you request OFFSET 100000, the database does not magically jump to row 100,001. It must scan and sort 100,020 rows in memory or disk, throw away the first 100,000 rows, and return only the remaining 20 rows.

As the offset increases, query execution time increases linearly ($O(N)$ time complexity). What took 2 milliseconds on page 1 can take several seconds on page 5,000, consuming massive CPU and RAM resources.

The Data Consistency Problem

Performance is only half the issue. Offset pagination also suffers from window shifting during concurrent writes.

Imagine a user is viewing Page 1 (items 1 through 20). While they are reading, 5 new records are inserted into the database. When the user navigates to Page 2 (OFFSET 20), the database fetches rows 21 through 40 based on the current state.

Because 5 new items were inserted at the top:

  • The last 5 items from Page 1 have shifted into Page 2.
  • The user sees duplicate records on Page 2.

If records are deleted instead, items get silently skipped, and the user never sees them. In high-frequency write systems like audit logs, social feeds, or IoT streams, this makes pagination unreliable.

How Cursor Pagination Fixes Both Issues

Cursor pagination (also known as keyset pagination) changes the query strategy. Instead of telling the database "skip N rows," you pass a pointer (the "cursor") to the last item retrieved and ask for "the next N items after this specific record."

This relies on an indexed column, such as an auto-incrementing ID or a timestamp combined with a unique identifier.

-- Fetching the first page
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Fetching the next page using the last item's values as the cursor
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-03-30T10:00:00Z', 84920)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Because the WHERE clause leverages an index, the database jumps directly to the matching row without scanning previous records. Performance remains $O(1)$ regardless of how deep into the dataset you paginate.

Additionally, inserting or deleting items above or below the current cursor will not shift the relative position of the remaining items, keeping pagination results stable.

Encoding Cursors in API Responses

In practice, backend APIs typically encode cursor values into a single opaque string (often Base64) to abstract the implementation details from the client.

Here is a conceptual Node.js handler pattern returning a cursor response:

app.get('/api/articles', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  const cursor = req.query.cursor ? decodeCursor(req.query.cursor) : null;

  let query = 'SELECT id, title, created_at FROM articles';
  let params = [];

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

  query += ' ORDER BY created_at DESC, id DESC LIMIT $' + (params.length + 1);
  params.push(limit);

  const { rows } = await db.query(query, params);
  const lastItem = rows[rows.length - 1];

  const nextCursor = lastItem 
    ? encodeCursor({ createdAt: lastItem.created_at, id: lastItem.id }) 
    : null;

  res.json({
    data: rows,
    pagination: {
      next_cursor: nextCursor,
      has_more: rows.length === limit
    }
  });
});
Enter fullscreen mode Exit fullscreen mode

Choosing the Right Strategy

Cursor pagination is superior for performance and accuracy, but it comes with tradeoffs: you lose the ability to jump directly to an arbitrary page number (e.g., "Jump to Page 42").

Use Offset Pagination when:

  • You are building internal admin dashboards or small reporting tools.
  • Users explicitly need page numbers and jump-to-page navigation.
  • The total dataset size is small and fixed.

Use Cursor Pagination when:

  • You are building public REST or GraphQL APIs.
  • The application serves infinite-scroll feeds, activity logs, or event streams.
  • The underlying tables experience heavy concurrent write operations.

Understanding your data access patterns early prevents expensive database migrations and API redesigns down the road.

About me

Tanmay builds AI agents and automation for businesses and is open to freelance projects. Connect with me on LinkedIn to discuss backend architectures, system design, or engineering automation.

Top comments (0)