Skip to content
← All posts

Nov 22, 2026

Your pagination is skipping rows

OFFSET pagination drifts when rows change between pages and slows down linearly at depth. Keyset pagination fixes both — here's the shape that works.

paginationPostgreSQLREST APIsdashboards

Every list endpoint I have ever written started the same way:

SELECT * FROM alerts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Page 3. Twenty rows. Done.

It works in development. It works in staging. Then the table grows, the data starts moving while people are reading it, and two things break at once. Neither one throws an error.

Problem 1: the page drifts

OFFSET counts rows from the top of the result set. It does not know which rows the user already saw.

A new alert lands between page 1 and page 2. Every row below it shifts down by one position. The user sees the last row of page 1 again at the top of page 2 — and one row at the bottom silently never appears.

On a monitoring dashboard this is not a cosmetic bug. An alert that never shows up in the list is an alert nobody acknowledges.

Problem 2: deep pages get slower forever

OFFSET 100000 does not jump to row 100,001. The database walks through the first 100,000 matching rows, counts them, throws them away, and only then starts collecting the 20 you asked for. The cost grows with the offset, not with the page size.

Page 1 is fast. Page 50 is slower. Page 5,000 is a slow query in your logs, and the table only grows.

The fix: paginate by position, not by count

Instead of "skip 40 rows," say "give me rows after this specific row." The client sends back the last row it saw, and the server seeks straight to it:

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

Two details matter here.

The composite key. Sorting by created_at alone breaks the moment two rows share a timestamp — and they will, especially with bulk imports or millisecond truncation. Pairing the sort column with the primary key as a tiebreaker gives a strict total order, which is what makes the comparison unambiguous.

Fetch limit + 1. I ask for 21 rows and return 20. If the 21st row exists, there is a next page. That gives me has_next_page without a separate COUNT(*) query — and on a large table, that count can be as expensive as the query I am trying to paginate.

The API never exposes raw column values. The server encodes the last row's sort key into an opaque cursor and the client passes it back verbatim:

// Express endpoint — personal project shape
app.get("/api/alerts", async (req, res) => {
  const cursor = req.query.cursor
    ? JSON.parse(Buffer.from(req.query.cursor as string, "base64").toString())
    : null;

  const rows = await pool.query(
    `SELECT * FROM alerts
     WHERE ($1::timestamptz IS NULL OR (created_at, id) < ($1, $2))
     ORDER BY created_at DESC, id DESC
     LIMIT 21`,
    [cursor?.created_at ?? null, cursor?.id ?? null],
  );

  const hasNextPage = rows.length > 20;
  const page = rows.slice(0, 20);
  const last = page[page.length - 1];
  const nextCursor = hasNextPage
    ? Buffer.from(JSON.stringify({ created_at: last.created_at, id: last.id })).toString("base64")
    : null;

  res.json({ alerts: page, next_cursor: nextCursor, has_next_page: hasNextPage });
});

The cursor is opaque on purpose. The client cannot construct one, guess one, or depend on its shape. If I change the sort key later, old cursors just stop working — the correct failure mode, not a silent wrong page.

What you give up

There is no page 47. A cursor only knows "after this row," so the client walks forward one page at a time. Total counts do not come free either — counting is a separate query, and on a big table you should think twice before running it on every request.

That is the whole trade. And for most of the lists I build — alert feeds, activity logs, infinite scroll — nobody actually needs page 47. They need the next page to be fast and to contain exactly the rows they have not seen yet.

OFFSET is still the right call for a small, mostly-static table, or an admin screen where jumping to an arbitrary page is a real feature. That is a decision, not a default.

For anything that grows without bound, or anything where rows move while people are reading — a feed, a log, a live dashboard list — start with a cursor. It is about ten extra lines, and it is the last time you rewrite that endpoint.