Every list endpoint eventually needs pagination, and almost every list endpoint starts with LIMIT and OFFSET because it’s the first thing you find in the SQL docs and it works perfectly in the demo. Then the table grows past a few hundred thousand rows, someone opens page 400 of the admin panel, and the query that used to take 5ms now takes 3 seconds. Worse, users start reporting that “records are missing” or “duplicated” when they page through a list while new rows are being inserted.

None of this is a database bug. It’s a mismatch between what offset pagination guarantees and what people assume it guarantees. Understanding the three main pagination strategies — offset, cursor, and keyset — and when each one is appropriate will save you a rewrite later.

Offset Pagination: Simple, and Simply Wrong at Scale

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

This is intuitive to write and maps directly onto “page 21 of 20 items per page.” Its two failure modes both come from the same root cause: OFFSET doesn’t skip rows by identity, it skips rows by position in the current result set at query time.

The drifting-page problem. If a row is inserted or deleted between page 1 and page 2 of a paginated request, the position of every subsequent row shifts. A user paging through a feed sorted by created_at DESC will see the same article twice, or miss one entirely, purely because someone else published something while they were reading. This isn’t a rare race condition — on any actively-written table, it’s the common case.

The performance cliff. OFFSET 400 doesn’t jump to row 400 — Postgres still has to scan and discard the first 400 matching rows before it can return the next 20. On a table with millions of rows, deep pages get linearly slower, and if the ORDER BY column isn’t the primary sort of an index, the database may be doing a full sort of the entire result set before it even starts counting.

-- Runtime grows with the offset — O(offset + limit), not O(limit)
EXPLAIN ANALYZE
SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
--  Limit  (cost=... rows=20) (actual time=180.442..180.460 rows=20 loops=1)
--    ->  Index Scan Backward using articles_created_at_idx

Offset pagination is fine for small, mostly-static tables (a settings list, a country dropdown) or admin UIs where “jump to page 40” is a genuinely useful feature and the table is small enough that the cost is negligible. For anything user-facing at scale, it’s the wrong default.

Keyset Pagination: Pagination by Value, Not Position

Keyset pagination (also called “seek” pagination) replaces “skip N rows” with “give me rows after this specific value.” Instead of an offset, the client sends the last row it saw, and the query resumes from there using an index range scan.

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

-- Page 2: client sends back the last row's (created_at, id)
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-05-17T09:12:00Z', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This has two properties offset pagination lacks. First, performance is constant regardless of how deep you page — the WHERE clause is a direct index seek, not a scan-and-discard. Second, it’s stable under concurrent writes: a newly inserted row either sorts before or after the client’s last-seen position, so it never causes a duplicate or a skip.

Why the composite key matters. Sorting by created_at alone breaks the moment two rows share the same timestamp (which happens more often than people expect, especially with millisecond-truncated timestamps or bulk imports). Pairing the sort column with a unique tiebreaker — almost always the primary key — guarantees a strict total order, which is required for the WHERE (col, id) < (val, id) comparison to be unambiguous.

-- Supports both the initial page and the seek efficiently
CREATE INDEX idx_articles_created_at_id ON articles (created_at DESC, id DESC);

The trade-off: keyset pagination can only move forward and backward relative to a known position — there’s no clean way to jump to “page 40” without walking there, because there’s no concept of an absolute page number. For infinite scroll, feeds, and API pagination, that’s rarely a problem people actually need.

Cursor Pagination: Keyset With an Opaque Wrapper

“Cursor pagination” is often used interchangeably with keyset pagination, but the useful distinction is at the API layer: a cursor is an opaque token the client passes back verbatim, rather than raw column values the client can see or construct.

interface SortKey {
  createdAt: string;
  id: string;
}

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

function decodeCursor(cursor: string): SortKey {
  const json = Buffer.from(cursor, "base64url").toString("utf8");
  return JSON.parse(json);
}
app.get("/api/articles", async (req, res) => {
  const limit = Math.min(Number(req.query.limit) || 20, 100);
  const cursor = req.query.cursor ? decodeCursor(String(req.query.cursor)) : null;

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

  const hasMore = rows.rows.length > limit;
  const page = rows.rows.slice(0, limit);
  const nextCursor = hasMore ? encodeCursor({
    createdAt: page[page.length - 1].created_at,
    id: page[page.length - 1].id,
  }) : null;

  res.json({
    data: page,
    pageInfo: {
      hasNextPage: hasMore,
      nextCursor,
    },
  });
});

Fetching limit + 1 rows and checking if the extra row exists is a cheap, reliable way to compute hasNextPage without a separate COUNT(*) query, which on large tables can be as expensive as the query you’re trying to paginate.

Opaque cursors have real advantages over exposing raw sort values in the URL: the client can’t construct or tamper with a cursor to skip validation, you’re free to change the underlying sort key encoding later without breaking API compatibility, and you can embed a version marker or a signature inside the token if you need to detect stale or forged cursors.

{
  "data": [
    { "id": "4821", "title": "Idempotency in APIs", "created_at": "2026-05-17T09:12:00Z" },
    { "id": "4820", "title": "Pagination Done Right", "created_at": "2026-05-16T14:03:00Z" }
  ],
  "pageInfo": {
    "hasNextPage": true,
    "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA1LTE2VDE0OjAzOjAwWiIsImlkIjoiNDgyMCJ9"
  }
}

Comparing the Three

OffsetKeyset (raw)Cursor (opaque keyset)
Query cost at deep pagesGrows with offsetConstantConstant
Stable under concurrent writesNo — driftsYesYes
Jump to arbitrary page NYesNoNo
Client can construct/tamper with positionYesYesNo (opaque)
API surface complexityTrivial (page, limit)Simple (after, limit)Slightly more (encode/decode)
Good fitSmall/static tables, admin “go to page” UIsInternal APIs, own frontendPublic APIs, infinite scroll, feeds

Handling Backward Navigation and Total Counts

Two things people ask for that keyset pagination makes awkward are “go to the previous page” and “show total result count.” For “previous,” store both a forward and backward cursor and flip the comparison operator and sort direction:

-- Previous page: reverse the comparison and the order, then re-reverse in application code
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;

For total counts, accept that an exact live count is expensive at scale and either cache an approximate count (Postgres’s pg_class.reltuples, refreshed periodically, is good enough for “about 12,400 results”), or drop the total count from the UI entirely in favor of “load more.” Most infinite-scroll and feed UIs don’t show a total for exactly this reason.

Composite Keys Beyond created_at + id

The same technique generalizes to any sort order, not just recency. Sorting by (score DESC, id DESC) for a leaderboard, or (last_name ASC, first_name ASC, id ASC) for an alphabetical directory, works identically — the composite tuple comparison is what makes the seek efficient and unambiguous, not the specific columns involved. The one hard requirement is a covering index on exactly that column order, in that direction, or the “constant time at any depth” property disappears and you’re back to a sequential scan with extra steps.

Key Takeaways

  • Offset pagination skips by row position, not identity — it drifts under concurrent writes and gets slower the deeper you page.
  • Keyset (seek) pagination resumes from the last row’s sort key using an indexed range scan, giving constant-time performance at any depth.
  • Always pair the sort column with a unique tiebreaker (the primary key) to guarantee a strict total order for the seek comparison.
  • Wrap the sort key in an opaque, base64-encoded cursor at the API layer so clients can’t construct or tamper with positions, and so you can evolve the encoding later.
  • Fetch limit + 1 rows to compute hasNextPage cheaply instead of running a separate count query.
  • Reserve offset pagination for small or rarely-written tables, and admin UIs that genuinely need “jump to page N.”
  • Exact total counts are expensive at scale — prefer an approximate or omitted count over a COUNT(*) on every request.