Keyset pagination (the seek method) selects the next page with "everything after the last row shown": WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 21. With a composite index on (created_at, id) the database reads only the rows of the page, so speed does not depend on depth, and new records do not shift the pages. The trade-off: you cannot jump straight to page N — the client gets a cursor and pages from it.
After this you will be able to
- replace
LIMIT … OFFSETwith a(created_at, id) < ($1, $2)condition and pick an index for it - return an opaque base64url cursor from the API and validate it before querying the database
- page forward and back without duplicates or gaps, even while new rows arrive
- measure the difference with
EXPLAIN (ANALYZE, BUFFERS)on a million rows
Know beforehand
Verified with
PostgreSQL 17 (17.11, postgres:17-alpine)Node.js 24 LTS (24.18, 24.20)node-postgres (pg) 8.23.1
Checked against docs: October 12, 2026
Contents
The problem: why OFFSET is slow and pages "jump"
Classic pagination is LIMIT 20 OFFSET 40: skip 40 rows and return 20. It has two problems. The first is speed: the PostgreSQL documentation states that rows skipped by OFFSET still have to be computed inside the server, so a large OFFSET is inefficient. To return page 25,001 the database has to walk through half a million rows. The second is stability: if a new record appears between two page requests, everything shifts by one position, and the user sees rows from the first page again on the second.
The test bed is an orders table with a million rows in PostgreSQL 17. Three orders per minute: the timestamp is not unique, and id does not follow time order — as in real data where records are imported in batches.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
customer text NOT NULL,
total numeric(12, 2) NOT NULL
);
-- 1 million orders: three per minute; id order does not match time order
INSERT INTO orders (created_at, customer, total)
SELECT timestamptz '2026-01-01 00:00:00+00' + ((g::bigint * 7919) % 333334) * interval '1 minute',
'customer-' || (g % 5000),
100 + (g % 90000) / 3.0
FROM generate_series(1, 1000000) AS g;
ANALYZE orders;This is what OFFSET does even with a suitable index (created below): a backward index scan reads 500,020 rows to throw away 500,000.
$ psql -d shop -f sql/02-offset.sql
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Limit (actual time=191.505..191.520 rows=20 loops=1)
Buffers: shared hit=503855
-> Index Scan Backward using orders_created_at_id_idx on orders (actual time=0.014..181.588 rows=500020 loops=1)
Buffers: shared hit=503855
Planning:
Buffers: shared hit=103
Planning Time: 0.427 ms
Execution Time: 191.548 msHow big the difference is, we measured separately on the same schema: a 1,000,000-row table, PostgreSQL 17, five page depths, three runs of 10 rounds. The median server time of OFFSET on the last page was 243.1 ms, keyset on the same page 0.059 ms; it is one machine with a warm cache, so the shape of the curve matters more than the exact milliseconds.
Ready SQL: a condition on the (created_at, id) pair and an index
Keyset pagination remembers the last row shown instead of a page number, and asks for "everything after it". The sort order must be unambiguous: created_at alone is not enough, since three orders share each timestamp. So we add a unique tail — id — and compare the pair as a whole with a row constructor comparison: (created_at, id) < ($1, $2). PostgreSQL compares the elements left to right and stops at the first unequal pair — exactly what the created_at DESC, id DESC order needs.
-- one B-tree serves both ORDER BY created_at DESC, id DESC (backward scan) and ASC
CREATE INDEX orders_created_at_id_idx ON orders (created_at, id);A B-tree stores entries in ascending order and can be scanned backward, so one index on (created_at, id) serves both ORDER BY created_at DESC, id DESC and ORDER BY created_at, id. The same page 25,001 via keyset:
-- the same page 25,001: everything after the last row of page 25,000
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, created_at, customer, total
FROM orders
WHERE (created_at, id) < ('2026-04-26 17:47:00+00', 166667)
ORDER BY created_at DESC, id DESC
LIMIT 21;LIMIT 21 instead of 20: the extra row tells us whether there is a next page$ psql -d shop -f sql/03-keyset.sql
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Limit (actual time=0.030..0.150 rows=21 loops=1)
Buffers: shared hit=24
-> Index Scan Backward using orders_created_at_id_idx on orders (actual time=0.029..0.145 rows=21 loops=1)
Index Cond: (ROW(created_at, id) < ROW('2026-04-26 17:47:00+00'::timestamp with time zone, 166667))
Buffers: shared hit=24
Planning:
Buffers: shared hit=102
Planning Time: 0.449 ms
Execution Time: 0.195 msThe condition landed in Index Cond: the database descends the tree straight to the boundary and reads 21 rows. Measurements on the same test bed (Docker on a developer laptop, data cached — look at orders of magnitude, not exact milliseconds):
OFFSET 500000, no index
- Plan
- Parallel Seq Scan + on-disk sort (external merge)
- Buffers
- 8,422 + temp files
- Execution time
- 179.7 ms
keyset, no index
- Plan
- Parallel Seq Scan + top-N heapsort
- Buffers
- 8,364
- Execution time
- 24.8 ms
OFFSET 500000, with index
- Plan
- Index Scan Backward, 500,020 rows
- Buffers
- 503,855
- Execution time
- 175.9–241.2 ms (3 runs)
keyset, with index
- Plan
- Index Scan Backward, 21 rows
- Buffers
- 24
- Execution time
- 0.195–0.266 ms (3 runs)
An index does not rescue OFFSET: the rows still have to be walked through. Keyset without an index is not fast either — it needs a full table scan. Only the "keyset + index on the sort key" pair wins. The API needs three queries: the first page, the next one and the previous one. For the previous page we go up from the first row of the current page in ascending order, and the application then reverses the result:
-- first page
SELECT id, created_at, customer, total FROM orders
ORDER BY created_at DESC, id DESC LIMIT $1;
-- next: everything below the last row of the page
SELECT id, created_at, customer, total FROM orders
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC LIMIT $3;
-- previous: everything above the first row of the page; reverse the result
SELECT id, created_at, customer, total FROM orders
WHERE (created_at, id) > ($1::timestamptz, $2::bigint)
ORDER BY created_at ASC, id ASC LIMIT $3;$1–$3, $3 = limit + 1The cursor in the API: a Node.js handler
The client has no reason to know what is inside the cursor. The handler encodes [created_at, id] of the boundary row as base64url and returns next_cursor and prev_cursor; the client sends them back in ?after= or ?before=. The cursor format can then change without a new API version — more on this in the API design lesson.
import http from "node:http";
import pg from "pg";
const COLUMNS = "id, created_at, customer, total";
const MAX_LIMIT = 100;
// keep timestamptz (OID 1184) as a string: a Date drops microseconds, and the cursor starts losing rows
const types = {
getTypeParser: (oid, format) => (oid === 1184 ? (value) => value : pg.types.getTypeParser(oid, format)),
};
const TIMESTAMP = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d{1,6})?[+-]\d{2}(:\d{2})?$/;
// the cursor is an opaque string: base64url of [created_at, id] of the boundary row
export function encodeCursor(row) {
return Buffer.from(JSON.stringify([row.created_at, row.id])).toString("base64url");
}
export function decodeCursor(cursor) {
try {
const [createdAt, id] = JSON.parse(Buffer.from(cursor, "base64url").toString("utf8"));
if (TIMESTAMP.test(createdAt) && /^\d{1,18}$/.test(id)) return { createdAt, id };
} catch {
// not base64url or not JSON — return null below
}
return null;
}
// newest orders first: ORDER BY created_at DESC, id DESC
export async function listOrders(db, { limit = 20, after = null, before = null } = {}) {
let query;
if (after) {
query = {
text: `SELECT ${COLUMNS} FROM orders
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC LIMIT $3`,
values: [after.createdAt, after.id, limit + 1],
};
} else if (before) {
// backwards: walk up from the cursor in ascending order, then reverse
query = {
text: `SELECT ${COLUMNS} FROM orders
WHERE (created_at, id) > ($1::timestamptz, $2::bigint)
ORDER BY created_at ASC, id ASC LIMIT $3`,
values: [before.createdAt, before.id, limit + 1],
};
} else {
query = {
text: `SELECT ${COLUMNS} FROM orders ORDER BY created_at DESC, id DESC LIMIT $1`,
values: [limit + 1],
};
}
const { rows } = await db.query({ ...query, types });
const hasMore = rows.length > limit; // the extra (limit + 1)th row tells us there is more
const items = rows.slice(0, limit);
if (before) items.reverse();
const hasNext = before ? items.length > 0 : hasMore;
const hasPrev = before ? hasMore : Boolean(after) && items.length > 0;
return {
items,
next_cursor: hasNext ? encodeCursor(items.at(-1)) : null,
prev_cursor: hasPrev ? encodeCursor(items[0]) : null,
};
}
function problem(res, status, detail) {
res.writeHead(status, { "content-type": "application/problem+json" });
res.end(JSON.stringify({ title: http.STATUS_CODES[status], status, detail }));
}
export function createServer(db) {
return http.createServer(async (req, res) => {
const url = new URL(req.url, "http://localhost");
if (req.method !== "GET" || url.pathname !== "/api/orders") return problem(res, 404, "No such URL");
const params = url.searchParams;
const limit = Number(params.get("limit") ?? 20);
if (!Number.isInteger(limit) || limit < 1 || limit > MAX_LIMIT) {
return problem(res, 400, `limit must be an integer from 1 to ${MAX_LIMIT}`);
}
if (params.has("after") && params.has("before")) return problem(res, 400, "Pass after or before, not both");
const raw = params.get("after") ?? params.get("before");
const cursor = raw === null ? null : decodeCursor(raw);
if (raw !== null && cursor === null) return problem(res, 400, "The cursor is malformed");
try {
const page = await listOrders(db, {
limit,
after: params.has("after") ? cursor : null,
before: params.has("before") ? cursor : null,
});
res.writeHead(200, { "content-type": "application/json" });
res.end(JSON.stringify(page));
} catch (err) {
console.error(err);
problem(res, 500, "Internal server error");
}
});
}import pg from "pg";
import { createServer } from "./orders-api.js";
// DATABASE_URL=postgres://user:password@localhost:5432/shop node server.js
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const port = Number(process.env.PORT ?? 3000);
createServer(pool).listen(port, () => console.log(`http://localhost:${port}/api/orders`));timestamptzcomes back as a string, not aDate. The node-postgres documentation warns that a JavaScriptDateholds milliseconds, so microseconds from PostgreSQL get truncated. A cursor with a truncated time points at the wrong row: in our test withDate, the walk lost some orders.- The cursor is validated before the query: base64url → JSON → a timestamp matching the pattern and an all-digit
id. A broken cursor gets a 400, and the values reach the query only as$1,$2parameters, never through string concatenation. - The driver returns
bigintidandnumericvalues as strings — so JSON does not lose precision on large numbers. - The query asks for
limit + 1rows. If the extra one arrives, there is a next page; the extra row is not sent to the client.
How to plug it in
Pick an unambiguous order
The sort key plus a unique column at the end:
(created_at, id),(total, id). Both columns areNOT NULL: with aNULL, the row comparison yields an unknown result, and the row drops out of the selection.Create an index on the same columns
On a live database use
CREATE INDEX CONCURRENTLY: a regular index build locks out writes to the table until it finishes, whileCONCURRENTLYdoes not, at the cost of taking longer.Rewrite the list queries
The first page is
ORDER BY … LIMIT n + 1; after that, a(created_at, id) < ($1, $2)condition with the sameORDER BY. The comparison follows the sort direction:<forDESC,>forASC.Change the API contract
Instead of
pageandoffset—limit,afterandbefore; the response carriesnext_cursorandprev_cursor,nullat the ends of the list. Put an upper bound onlimit.Teach the client to page by cursor
Infinite scroll and next/previous buttons work right away. Jumping "to page 37" does not: drop it, or keep
OFFSETfor the first few pages.
How to check: tests, API calls and an insert between pages
The test starts the handler on a free port, fills a test database with 45 orders — three per timestamp, with microseconds — and checks what matters: walking forward and back returns each row once in the right order, and new orders do not shift a walk that has already started.
import assert from "node:assert/strict";
import { once } from "node:events";
import { after, before, test } from "node:test";
import pg from "pg";
import { createServer, decodeCursor } from "./orders-api.js";
// a separate test database: TEST_DATABASE_URL=postgres://postgres:test@localhost:5432/shop_test
const pool = new pg.Pool({ connectionString: process.env.TEST_DATABASE_URL });
let server;
let baseUrl;
before(async () => {
await pool.query("DROP TABLE IF EXISTS orders");
await pool.query(`CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
customer text NOT NULL,
total numeric(12, 2) NOT NULL)`);
await pool.query("CREATE INDEX orders_created_at_id_idx ON orders (created_at, id)");
// 45 orders: three per timestamp, timestamps with microseconds
await pool.query(`INSERT INTO orders (created_at, customer, total)
SELECT timestamptz '2026-10-01 12:00:00.123456+00' + (g % 15) * interval '1.000001 second',
'customer-' || g, 100 + g
FROM generate_series(1, 45) AS g`);
server = createServer(pool).listen(0);
await once(server, "listening");
baseUrl = `http://localhost:${server.address().port}/api/orders`;
});
after(async () => {
server.close();
await pool.end();
});
async function get(params) {
const res = await fetch(`${baseUrl}?${new URLSearchParams(params)}`);
return { status: res.status, body: await res.json() };
}
async function idsInOrder() {
const { rows } = await pool.query("SELECT id FROM orders ORDER BY created_at DESC, id DESC");
return rows.map((row) => row.id);
}
const ids = (page) => page.items.map((order) => order.id);
test("forward via next_cursor: every row exactly once and in order", async () => {
const seen = [];
let page = (await get({ limit: 10 })).body;
seen.push(...ids(page));
while (page.next_cursor) {
page = (await get({ limit: 10, after: page.next_cursor })).body;
seen.push(...ids(page));
}
assert.deepEqual(seen, await idsInOrder());
assert.match(decodeCursor((await get({ limit: 10 })).body.next_cursor).createdAt, /\.\d{6}\+00$/);
});
test("backward via prev_cursor: the same pages in reverse", async () => {
const forward = [];
let page = (await get({ limit: 10 })).body;
forward.push(ids(page));
while (page.next_cursor) {
page = (await get({ limit: 10, after: page.next_cursor })).body;
forward.push(ids(page));
}
const backward = [];
while (page.prev_cursor) {
page = (await get({ limit: 10, before: page.prev_cursor })).body;
backward.unshift(ids(page));
}
assert.deepEqual(backward, forward.slice(0, -1));
assert.equal(page.prev_cursor, null); // reached the first page
});
test("new orders do not shift the next page, while OFFSET produces duplicates", async () => {
const expected = await idsInOrder();
const first = (await get({ limit: 10 })).body;
await pool.query("INSERT INTO orders (customer, total) SELECT 'customer-new', 500 FROM generate_series(1, 5)");
const second = (await get({ limit: 10, after: first.next_cursor })).body;
assert.deepEqual(ids(second), expected.slice(10, 20));
const { rows } = await pool.query("SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 10");
const onFirstPage = new Set(ids(first));
assert.equal(rows.filter((row) => onFirstPage.has(row.id)).length, 5);
});
test("a broken cursor and a bad limit return 400", async () => {
const forged = Buffer.from(JSON.stringify(["2026-10-01", "1; DROP TABLE orders"])).toString("base64url");
for (const params of [{ after: "abc" }, { before: forged }, { limit: 1000 }, { after: "a", before: "b" }]) {
const { status, body } = await get(params);
assert.equal(status, 400, JSON.stringify(params));
assert.equal(body.title, "Bad Request");
}
});$ TEST_DATABASE_URL=postgres://postgres:test@localhost:5432/shop_test node --test
✔ forward via next_cursor: every row exactly once and in order (124.8456ms)
✔ backward via prev_cursor: the same pages in reverse (108.526ms)
✔ new orders do not shift the next page, while OFFSET produces duplicates (30.7239ms)
✔ a broken cursor and a bad limit return 400 (62.5518ms)
ℹ tests 4
ℹ suites 0
ℹ pass 4
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 457.9316Calls to the running API on the million-row test bed. The first page, the next one via next_cursor, and a broken cursor:
$ curl -s "localhost:3000/api/orders?limit=2"
{"items":[{"id":"964223","created_at":"2026-08-20 11:33:00+00","customer":"customer-4223","total":"21507.67"},{"id":"630889","created_at":"2026-08-20 11:33:00+00","customer":"customer-889","total":"396.33"}],"next_cursor":"WyIyMDI2LTA4LTIwIDExOjMzOjAwKzAwIiwiNjMwODg5Il0","prev_cursor":null}
$ curl -s "localhost:3000/api/orders?limit=2&after=WyIyMDI2LTA4LTIwIDExOjMzOjAwKzAwIiwiNjMwODg5Il0"
{"items":[{"id":"297555","created_at":"2026-08-20 11:33:00+00","customer":"customer-2555","total":"9285.00"},{"id":"928444","created_at":"2026-08-20 11:32:00+00","customer":"customer-3444","total":"9581.33"}],"next_cursor":"WyIyMDI2LTA4LTIwIDExOjMyOjAwKzAwIiwiOTI4NDQ0Il0","prev_cursor":"WyIyMDI2LTA4LTIwIDExOjMzOjAwKzAwIiwiMjk3NTU1Il0"}
$ curl -s -i "localhost:3000/api/orders?after=oops"
HTTP/1.1 400 Bad Request
content-type: application/problem+json
…
Transfer-Encoding: chunked
{"title":"Bad Request","status":400,"detail":"The cursor is malformed"}The same insert effect, straight in SQL on a million rows. The transaction rolls back at the end, so the test data stays unchanged:
BEGIN;
-- page 1 as the user sees it
CREATE TEMP TABLE page1 AS
SELECT id, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;
-- while they read it, 5 new orders arrive
INSERT INTO orders (customer, total)
SELECT 'customer-new', 500 FROM generate_series(1, 5);
-- page 2 via OFFSET: how many rows repeat page 1
SELECT count(*) AS offset_duplicates
FROM (SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20) AS page2
WHERE id IN (SELECT id FROM page1);
-- page 2 via keyset from the last row of page 1
SELECT count(*) AS keyset_duplicates
FROM (SELECT id FROM orders
WHERE (created_at, id) < (SELECT created_at, id FROM page1 ORDER BY created_at, id LIMIT 1)
ORDER BY created_at DESC, id DESC LIMIT 20) AS page2
WHERE id IN (SELECT id FROM page1);
ROLLBACK;$ psql -d shop -f sql/05-stability.sql
BEGIN
SELECT 20
INSERT 0 5
offset_duplicates
-------------------
5
(1 row)
keyset_duplicates
-------------------
0
(1 row)
ROLLBACKLimitations: when OFFSET is still fine
`OFFSET` is fine
- Small tables and shallow pages: the database skips a few hundred rows without anyone noticing
- You need to jump to any page and show page numbers in the UI
- Data barely changes: reference tables, archives, reports for a closed period
You need keyset
- Feeds, logs, orders, messages — data grows and people page far
- Infinite scroll in a mobile app or on a website
- Exporting a whole table in batches for an integration or a background job
- Sorting by another field needs its own key and index: for "by amount" that is
(total, id), the condition(total, id) > ($1, $2)and the amount in the cursor instead of the date. - Row comparison works when all columns sort in the same direction. For a mixed order (
created_at DESC, id ASC) the condition is written out —created_at < $1 OR (created_at = $1 AND id > $2)— and the index is created with the same directions. - The cursor is not secret: a client can decode and forge it. Do not put access filters or a user ID in it without a signature — permissions are checked in the query on the server.
- If the boundary row is deleted, the cursor keeps working: the condition compares values rather than looking the row up.
- Keyset does not give you a total row count.
count(*)on a large table is a separate, expensive query; a "there is more" flag is often enough.
Links and next step
- "Indexes: speeding up queries without slowing down writes" — how a B-tree works and why column order in a composite index matters.
- "Query optimisation: EXPLAIN ANALYZE in practice" — how to read plans like the ones above and find slow queries.
- "API design: resources, versioning, errors, pagination" — the cursor pagination contract, the error format and the OpenAPI description.
- Official sources are listed below: the PostgreSQL documentation on
LIMIT/OFFSET, row comparison and indexes forORDER BY, the node-postgres documentation and Markus Winand's article on the seek method.
How this was verified5 · passed1 · partialchecked 12 October 2026
“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.