TIL: When Cursor-Based Pagination Is Better Than Offset Pagination

Pagination usually starts with two familiar query parameters:

?page=3&limit=20

The backend turns that into an offset, the database skips the earlier records, and the API returns the next batch. It’s easy to build, easy to explain, and more than enough for many admin panels and small websites.

Then the table grows.

A few thousand records become a few million. Users start scrolling deeper into an activity feed, or a background process keeps inserting new rows while someone is paging through the results. The same pagination code still works, but it starts showing cracks.

That was the part I hadn’t paid enough attention to. Offset pagination isn’t only a UI choice. It changes how the database finds rows, and it can affect whether the user sees a stable list at all.

The usual offset approach

A basic SQL query might look like this:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 2000;

In an API, we often calculate the offset from the requested page:

const page = Math.max(Number(req.query.page) || 1, 1);
const limit = Math.min(Number(req.query.limit) || 20, 100);
const offset = (page - 1) * limit;

const articles = await db.query(
  `SELECT id, title, created_at
   FROM articles
   ORDER BY created_at DESC
   LIMIT $1 OFFSET $2`,
  [limit, offset],
);

For page 1, the database returns the first 20 rows. For page 101, it may still need to walk past the first 2,000 matching rows before returning the next 20.

The skipped rows aren’t sent to the client, but they don’t become free. Depending on the database, indexes, filters, and query plan, larger offsets can mean more work and slower requests.

That doesn’t make offset pagination bad. It makes it a poor fit for some types of data.

The consistency problem is easier to miss

Performance is only half of it.

Imagine a feed sorted by newest first. A user fetches the first page, which contains items 100 through 81. Before they request page 2, five new items are inserted at the top.

Page 2 still asks for OFFSET 20, but the first 20 rows have changed. Some items from page 1 may now appear again. In other cases, records can be skipped.

The database isn’t doing anything wrong. The meaning of “skip the first 20 rows” changed between the two requests.

This gets annoying in infinite scrolling interfaces, notification feeds, logs, audit trails, and any list that changes often.

Cursor pagination starts after a known record

Cursor-based pagination doesn’t ask the database to skip a number of rows. It says, in effect, “give me the next records after this one.”

If id is increasing and the list is sorted from newest to oldest, a simple query can use the last ID from the previous response:

SELECT id, title, created_at
FROM articles
WHERE id < 8120
ORDER BY id DESC
LIMIT 20;

The first request doesn’t need a cursor:

SELECT id, title, created_at
FROM articles
ORDER BY id DESC
LIMIT 20;

The API returns the records plus a cursor based on the last item:

{
  "items": [
    {
      "id": 8140,
      "title": "First article"
    },
    {
      "id": 8120,
      "title": "Last article on this page"
    }
  ],
  "nextCursor": "8120"
}

The next request becomes:

/articles?limit=20&cursor=8120

New records can be inserted above the current position without shifting the boundary. The user continues from the last record they actually received.

A safer cursor uses the sort fields

Using only an ID works when the ID matches the sort order. Real queries often sort by something else, such as created_at.

Timestamps also aren’t guaranteed to be unique. Two articles can have the same creation time, so using only created_at can still skip or duplicate rows around the boundary.

A common fix is to sort by the timestamp and use the ID as a tie-breaker:

SELECT id, title, created_at
FROM articles
WHERE
  created_at < $1
  OR (created_at = $1 AND id < $2)
ORDER BY created_at DESC, id DESC
LIMIT $3;

The cursor now needs both values:

type ArticleCursor = {
  createdAt: string;
  id: number;
};

function encodeCursor(cursor: ArticleCursor): string {
  return Buffer.from(JSON.stringify(cursor)).toString("base64url");
}

function decodeCursor(value: string): ArticleCursor {
  const parsed = JSON.parse(
    Buffer.from(value, "base64url").toString("utf8"),
  ) as Partial<ArticleCursor>;

  if (
    typeof parsed.createdAt !== "string" ||
    typeof parsed.id !== "number"
  ) {
    throw new Error("Invalid pagination cursor");
  }

  return {
    createdAt: parsed.createdAt,
    id: parsed.id,
  };
}

The cursor isn’t meant to be a secret. Encoding it just keeps the URL compact and lets the server change the cursor shape later without exposing several query parameters.

The server should still validate every decoded value. A client can edit a Base64 cursor just as easily as any other query parameter.

Building the endpoint

A simplified Express endpoint could look like this:

app.get("/articles", async (req, res) => {
  const limit = Math.min(
    Math.max(Number(req.query.limit) || 20, 1),
    100,
  );

  const cursorValue =
    typeof req.query.cursor === "string"
      ? req.query.cursor
      : null;

  let cursor: ArticleCursor | null = null;

  try {
    cursor = cursorValue
      ? decodeCursor(cursorValue)
      : null;
  } catch {
    return res.status(400).json({
      message: "The pagination cursor is invalid",
    });
  }

  const result = cursor
    ? await db.query(
        `SELECT id, title, created_at
         FROM articles
         WHERE
           created_at < $1
           OR (created_at = $1 AND id < $2)
         ORDER BY created_at DESC, id DESC
         LIMIT $3`,
        [cursor.createdAt, cursor.id, limit + 1],
      )
    : await db.query(
        `SELECT id, title, created_at
         FROM articles
         ORDER BY created_at DESC, id DESC
         LIMIT $1`,
        [limit + 1],
      );

  const hasNextPage = result.rows.length > limit;

  const items = hasNextPage
    ? result.rows.slice(0, limit)
    : result.rows;

  const lastItem = items.at(-1);

  res.json({
    items,
    nextCursor:
      hasNextPage && lastItem
        ? encodeCursor({
            createdAt: lastItem.created_at,
            id: lastItem.id,
          })
        : null,
  });
});

Fetching limit + 1 rows is a small but useful detail. It tells us whether another page exists without running a separate count query.

If the client requests 20 items and the database returns 21, we know that there’s another batch. We remove the extra row before returning the response and create a cursor from the last visible item.

The frontend can then keep requesting results until nextCursor becomes null.

async function fetchArticles(cursor?: string) {
  const params = new URLSearchParams({
    limit: "20",
  });

  if (cursor) {
    params.set("cursor", cursor);
  }

  const response = await fetch(`/api/articles?${params}`);

  if (!response.ok) {
    throw new Error("Could not load articles");
  }

  return response.json() as Promise<{
    items: Article[];
    nextCursor: string | null;
  }>;
}

The index still matters

Changing the pagination style won’t magically fix a slow query. The database needs an index that matches the fields used for sorting and seeking.

For the previous query, that could be:

CREATE INDEX articles_created_at_id_idx
ON articles (created_at DESC, id DESC);

Without the index, the database may still need to scan and sort a large part of the table.

Filters also affect the right index. If the endpoint only returns published articles, an index that starts with the status field may make more sense:

CREATE INDEX articles_status_created_at_id_idx
ON articles (status, created_at DESC, id DESC);

The query would then keep the same cursor logic:

SELECT id, title, created_at
FROM articles
WHERE status = 'published'
  AND (
    created_at < $1
    OR (created_at = $1 AND id < $2)
  )
ORDER BY created_at DESC, id DESC
LIMIT $3;

The parentheses matter here. They make sure the cursor conditions stay inside the status = 'published' filter.

Where cursor pagination works best

Cursor pagination is a strong fit when users move forward or backward through a changing list. Infinite scroll is the obvious example, but it also works well for API exports, message histories, transaction feeds, audit logs, and large content archives.

It tends to provide more stable request times because the database can seek from an indexed value instead of counting past a growing offset. It also handles new inserts more naturally.

There are tradeoffs, though.

The UI can’t easily jump straight to page 47 because a cursor describes a position, not a page number. Showing an exact total can require a separate query, and reverse navigation needs extra logic.

Filtering and sorting also need to stay fixed while the cursor is in use. A cursor created for “newest first” shouldn’t be reused after switching to “most popular.”

Offset pagination is still useful for small datasets, back-office tables, search results where users expect numbered pages, and screens where direct page jumps matter more than deep-page speed.

The rule I’ll use next time

I won’t replace every page and limit endpoint with cursors. That would add complexity where it isn’t needed.

But when a list is large, frequently updated, or built around continuous scrolling, I’ll start with a different question: does the user really need a page number, or do they only need the next batch?

If they only need the next batch, a cursor is often the better boundary. It gives the database a real value to seek from, and it keeps the user’s position tied to an actual record instead of a row count that may already have changed.