Why OFFSET Pagination Is Killing Your PostgreSQL Queries (And How to Implement Keyset Pagination in Node.js)

Almost every API starts with the same pagination code:

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

It's simple, it works in development, and it ships. Months later, the table has millions of rows and you're seeing:

• Page 1 loads in 5 ms. Page 5,000 takes 3 seconds.
• A crawler or an export job walking through pages brings the database to its knees.
• Users report seeing the same item twice, or missing items entirely, as they page through a live feed.

All three have the same root cause: OFFSET pagination doesn't scale and isn't stable. This post explains why, then builds the alternative, keyset pagination (also called cursor or seek pagination), step by step in Node.js and PostgreSQL, including a subtle timestamp bug that trips up most first implementations.

---

Why OFFSET gets slower with every page

OFFSET 100000 doesn't mean "jump to row 100,000." PostgreSQL has no way to teleport to the Nth row of a sorted result. It has to produce the first 100,020 rows in order and throw away the first 100,000.

So the cost of a page is proportional to offset + limit, not to limit. The shape of the plan tells the story:

EXPLAIN (ANALYZE) SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 2000000;

Limit  (actual time=640.2..640.3 rows=20)
  ->  Index Scan Backward using orders_created_idx on orders  (actual time=0.03..598.4 rows=2000020)

(Illustrative numbers. Yours will differ, but the pattern is the same.) The Limit node returns 20 rows, but the scan underneath it produced 2,000,020 and discarded 2,000,000. Even with a perfect index, the database walks past every skipped row, and it also has to visit the table for each of them to fetch the columns you selected.

The result is a cost curve that climbs linearly with page depth:

Page	OFFSET	Rows the database walks
1	0	20
100	1,980	~2,000
10,000	199,980	~200,000
100,000	1,999,980	~2,000,000

Real users rarely go to page 100,000, but bots, exports, and "load everything" jobs do, and they're exactly the traffic that hurts you. An attacker can also request ?page=999999 on purpose.

Problem 2: unstable results

OFFSET pagination is positional ("skip N rows"), so if the data changes between requests, positions shift:

Page 1 (OFFSET 0):  rows A B C D E   ← user reads this
                    ── a new row Z is inserted at the top ──
Page 2 (OFFSET 5):  rows E F G H I   ← E appears AGAIN (duplicate)

Delete a row from an earlier page instead, and everything shifts the other way: a row is skipped and the user never sees it. On a busy feed, this happens constantly.

Problem 3: the "total pages" count

Classic pagination UIs need SELECT count(*) to render "Page 3 of 4,812". In PostgreSQL, counting a large table is slow, because MVCC means the database has to check row visibility rather than reading a stored counter. Running that on every request is an expensive habit. (More on alternatives later.)

---

The idea behind keyset pagination

Instead of saying "skip 40 rows", say "give me the rows that come after the last one I saw."

If the previous page ended with the row (created_at = 2026-10-22 10:00:00, id = 8841), the next page is:

SELECT * FROM orders
WHERE (created_at, id) < ('2026-10-22 10:00:00', 8841)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With a matching index, PostgreSQL seeks directly to that position in the B-tree and reads 20 rows from there. The cost is the same on page 1 and page 100,000, because nothing is skipped. It also fixes stability: your position is anchored to a value, not a row count, so inserts and deletes elsewhere don't shift it.

---

Step 1: The index

The index must match your ORDER BY columns, in the same order:

CREATE INDEX orders_tenant_created_id_idx
  ON orders (tenant_id, created_at DESC, id DESC);

• Equality filters first (tenant_id), then the sort columns. This lets Postgres jump straight to one tenant's slice and read it in order.
• id is the tiebreaker. Many rows can share the same created_at (bulk imports, batch jobs). Without a unique tiebreaker, rows with identical timestamps can be skipped or repeated across pages. The sort key must be unique as a whole.
• The DESC markers aren't strictly required when every column sorts in the same direction (Postgres can scan a B-tree backwards), but they make the intent clear and are required for mixed directions.

---

Step 2: The query

The tuple comparison (created_at, id) < ($1, $2) is a row-value comparison, and it's the cleanest way to express "after this position" for a multi-column sort. It compares created_at first, and id only when the timestamps are equal. PostgreSQL can use a matching B-tree index to evaluate it efficiently.

const { rows } = await pool.query(
  `SELECT id, status, total_cents, created_at
     FROM orders
    WHERE tenant_id = $1
      AND (created_at, id) < ($2::timestamptz, $3::bigint)
    ORDER BY created_at DESC, id DESC
    LIMIT $4`,
  [tenantId, cursor.t, cursor.id, limit + 1],
);

Notice limit + 1. That's the trick for knowing whether there's a next page without a count query: fetch one extra row. If you got limit + 1 rows, there's more; return only limit of them.

---

The trap: JavaScript dates lose precision

Here's the bug in most first implementations. PostgreSQL timestamptz has microsecond precision. A JavaScript Date has millisecond precision. When node-postgres parses a timestamp into a Date, the last three digits are silently dropped:

Postgres value:   2026-10-22 10:00:00.123456
After JS Date:    2026-10-22 10:00:00.123       ← .000456 lost

If you build your cursor from that Date, the cursor points to a position slightly earlier than the real last row, and the effect depends on the sort direction:

-- Newest first (DESC). The real last row was ...123456, but the cursor says ...123000
WHERE (created_at, id) < ('2026-10-22 10:00:00.123', 8841)

Any rows with timestamps between ...123000 and ...123456 should come next, but they're greater than the truncated cursor, so < excludes them. Those rows are silently skipped. In ascending order the mirror image happens: with > and a truncated cursor, the real last row itself is greater than the cursor, so it's returned again as a duplicate.

Either way, it only happens when timestamps have sub-millisecond digits and several rows land close together, which is why it passes casual testing.

Fix: build the cursor from Postgres's own text

Have Postgres give you the timestamp as text, and pass that exact string back:

SELECT id, status, total_cents, created_at,
       created_at::text AS created_at_raw     -- full microsecond precision, as text
  FROM orders ...

created_at_raw = '2026-10-22 10:00:00.123456+00'

Put that string in the cursor and cast it back with $2::timestamptz. No precision is lost, because the value never passes through a JS Date. (An alternative that avoids the issue entirely is sorting by a monotonically increasing bigint id, but time-based feeds usually need timestamps.)

---

Step 3: The cursor

Make the cursor opaque: encode it so clients treat it as a token rather than building their own. That lets you change the internals later without breaking clients.

import { z } from 'zod';

const TS_RE = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d{1,6})?[+-]\d{2}(:\d{2})?$/;

const cursorSchema = z.object({
  t: z.string().regex(TS_RE),           // created_at as Postgres text
  id: z.string().regex(/^\d{1,19}$/),   // bigint as string (pg returns bigint as string)
});
export type Cursor = z.infer<typeof cursorSchema>;

export const encodeCursor = (c: Cursor) =>
  Buffer.from(JSON.stringify(c)).toString('base64url');

export function decodeCursor(raw: string): Cursor {
  try {
    const json = JSON.parse(Buffer.from(raw, 'base64url').toString('utf8'));
    return cursorSchema.parse(json);
  } catch {
    throw new BadRequestError('Invalid cursor');
  }
}

Always validate the decoded cursor. It comes from the client, so treat it as untrusted input. Parameterized queries already protect against SQL injection, but validation turns a malformed token into a clean 400 instead of a database error (an invalid timestamp cast would otherwise fail with a 22007 error).

Cursor tampering is also not a security boundary. A user can forge any cursor, which only changes where in their own results they start. Authorization must come from the query's WHERE clause (tenant_id = $1 above), never from anything inside the cursor.

---

Step 4: The complete function

export async function listOrders(opts: { tenantId: string; limit: number; cursor?: string }) {
  const limit = Math.min(Math.max(opts.limit, 1), 100);   // always clamp client-supplied limits
  const cursor = opts.cursor ? decodeCursor(opts.cursor) : undefined;

  const params: unknown[] = [opts.tenantId];
  let where = 'tenant_id = $1';
  if (cursor) {
    params.push(cursor.t, cursor.id);
    where += ' AND (created_at, id) < ($2::timestamptz, $3::bigint)';
  }
  params.push(limit + 1);

  const { rows } = await pool.query(
    `SELECT id, status, total_cents, created_at,
            created_at::text AS created_at_raw
       FROM orders
      WHERE ${where}
      ORDER BY created_at DESC, id DESC
      LIMIT $${params.length}`,
    params,
  );

  const hasMore = rows.length > limit;
  const page = hasMore ? rows.slice(0, limit) : rows;
  const last = page[page.length - 1];

  return {
    data: page.map(({ created_at_raw, ...row }) => row),
    nextCursor: hasMore && last
      ? encodeCursor({ t: last.created_at_raw, id: String(last.id) })
      : null,
  };
}

The only string interpolated into the SQL is the where fragment, which is built from fixed literals. All values still go through parameters.

And the route:

app.get('/orders', async (req, res) => {
  const limit = Number(req.query.limit ?? 20);
  const result = await listOrders({
    tenantId: req.user.tenantId,
    limit: Number.isFinite(limit) ? limit : 20,
    cursor: typeof req.query.cursor === 'string' ? req.query.cursor : undefined,
  });
  res.json(result);   // { data: [...], nextCursor: "eyJ0Ijoi..." | null }
});

A client just follows nextCursor until it's null:

let cursor: string | null | undefined;
do {
  const page = await api.get('/orders', { params: { limit: 100, cursor } });
  handle(page.data);
  cursor = page.nextCursor;
} while (cursor);

---

Going backwards (previous page)

To page backwards from a cursor, flip the comparison and the sort, then reverse the rows in memory:

SELECT ... FROM orders
WHERE tenant_id = $1 AND (created_at, id) > ($2::timestamptz, $3::bigint)
ORDER BY created_at ASC, id ASC       -- flipped
LIMIT $4;

const pageRows = rows.slice(0, limit).reverse();   // restore display order

Return a prevCursor built from the first row of the page, and use the same limit + 1 trick to know whether an earlier page exists. This is also more work than a "load more" button, so many apps just support forward paging.

---

Gotchas

Mixed sort directions

Row-value comparison needs every column to sort the same way. For something like "highest score first, then oldest id first" (score DESC, id ASC), spell the condition out:

WHERE score < $1
   OR (score = $1 AND id > $2)
ORDER BY score DESC, id ASC

Create the index with the same directions ((score DESC, id ASC)) and check the plan with EXPLAIN. The OR form can be harder for the planner than a plain row comparison, and on large tables some people split it into two range queries joined with UNION ALL. Test on your own data.

NULLs in the sort column

Comparisons involving NULL evaluate to NULL, not true, so rows with a null sort value can silently drop out of keyset queries. Prefer NOT NULL sort columns. If you can't, normalize (for example, order by COALESCE(col, 'infinity')) and index that exact expression.

Sort columns must be stable

If a sort value changes between requests (say, sorting by updated_at, and a row is updated mid-pagination), it can jump positions and show up twice or be missed. For "never miss anything" exports, sort by something immutable like created_at or an increasing id.

Filters change the cursor's meaning

A cursor is only valid for the same filters and sort order that produced it. If you let users filter or re-sort, either bind that into the cursor (and reject mismatches) or require a fresh query without a cursor whenever the filters change.

Jumping to "page 47"

This is the real trade-off. Keyset pagination can't jump to an arbitrary page, because positions are relative to the previous page. It fits infinite scroll, "load more", APIs, and exports very well. It fits "go to page N" UIs poorly. Options when you need both:

• Use OFFSET for shallow pages only (cap it, say at a few hundred rows deep) and keyset beyond that
• Offer jump-to-date navigation: seed a keyset query from created_at < $date
• Keep OFFSET for small, rarely-changing tables where it's genuinely fine. It still is.

---

What about the total count?

If you need a "total" and can't drop it:

• Don't count on every request. Cache it, or compute it asynchronously.
• Show an estimate: the planner statistics give an approximate row count cheaply (reltuples in pg_class, or the row estimate from EXPLAIN), which is fine for "about 4.8M results".
• Cap the count: SELECT count(*) FROM (SELECT 1 FROM orders WHERE ... LIMIT 1001) t lets you show "1,000+" while bounding the work.
• Skip it, and use hasMore from the limit + 1 trick, which is all infinite scroll needs.

---

Verify it yourself

Don't take the performance claims on faith. Build a big table and compare:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  status TEXT NOT NULL,
  total_cents INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

INSERT INTO orders (tenant_id, status, total_cents, created_at)
SELECT 't1', 'paid', (random() * 10000)::int,
       now() - (g * interval '1 second') * random()
FROM generate_series(1, 5000000) g;

CREATE INDEX orders_tenant_created_id_idx ON orders (tenant_id, created_at DESC, id DESC);
ANALYZE orders;

Then compare deep pages:

-- OFFSET: cost grows with depth
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 't1'
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 2000000;

-- Keyset: constant, using a (created_at, id) taken from a row near that position
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 't1'
  AND (created_at, id) < ('2026-09-01 00:00:00+00', 1000000)
ORDER BY created_at DESC, id DESC LIMIT 20;

Compare the rows scanned and the execution time. You should see the OFFSET query do work proportional to the offset while the keyset query touches roughly 20 index entries.

Test the correctness, not just the speed

The stability and tiebreaker claims deserve a test, especially with the bad case baked in, a pile of rows sharing the same timestamp:

it('walks every row exactly once, even with identical timestamps', async () => {
  await seedOrders({ count: 500, sameTimestampGroups: 50 });   // many rows share created_at

  const seen: string[] = [];
  let cursor: string | undefined;
  do {
    const page = await listOrders({ tenantId: 't1', limit: 37, cursor });  // odd page size on purpose
    seen.push(...page.data.map((r) => String(r.id)));
    cursor = page.nextCursor ?? undefined;
  } while (cursor);

  expect(seen).toHaveLength(500);
  expect(new Set(seen).size).toBe(500);            // no duplicates, no skips
});

Also seed rows with microsecond-precision timestamps in this test. That's what catches the Date truncation bug.

---

OFFSET vs keyset at a glance

	OFFSET / LIMIT	Keyset (cursor)
Cost of page N	Grows with N	Constant
Stable under inserts/deletes	No (duplicates and skips)	Yes
Jump to arbitrary page	Yes	No
Needs a unique, indexed sort key	No	Yes
Handles total counts	Needs a separate count(*)	Needs a separate approach
Complexity	Lowest	Moderate
Good for	Small tables, admin tables with page numbers	Feeds, APIs, exports, infinite scroll

Checklist

• ✅ Index matches WHERE equality columns, then the sort columns
• ✅ Sort key is unique (add id as a tiebreaker)
• ✅ Row-value comparison (a, b) < ($1, $2) for same-direction sorts
• ✅ Fetch limit + 1 to learn hasMore without a count
• ✅ Build timestamp cursors from created_at::text, never a JS Date
• ✅ Cursor is opaque, validated on decode, and carries no authorization
• ✅ Client-supplied limit is clamped
• ✅ Test with duplicate and microsecond-precision timestamps

Key takeaways

1. OFFSET makes the database read and discard every skipped row, so deep pages get progressively slower.
2. It's also unstable: inserts and deletes between requests cause duplicates and skipped rows.
3. Keyset pagination anchors to a value instead of a position: "rows after this one." With the right index, every page costs about the same.
4. You need a unique sort key, usually the timestamp plus id, and an index that matches it.
5. Beware timestamp precision. JS Date truncates Postgres microseconds. Round-trip the value as text.
6. Keyset can't jump to page N. Choose it for feeds and APIs, keep OFFSET for small tables, or combine them.
7. Test with ties and sub-millisecond timestamps, since those are what expose subtle pagination bugs.

Have you migrated an endpoint from OFFSET to keyset? I'd love to hear how it went and what the speedup looked like in the comments.

---

Further reading: the PostgreSQL docs on row-wise comparison and LIMIT/OFFSET, and Markus Winand's "Use The Index, Luke" on why not to use offset.