APIs
Pagination: SQL and code reading
Crux Read real SQL, an EXPLAIN line, and a cursor handler, then predict the pagination behaviour and pick the highest-leverage fix.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitPagination bugs live in the query plan and the cursor handler, not in the spec. Read each snippet, predict what happens at depth and under concurrent writes, then choose the fix a senior engineer makes first.
Goal
Practise the loop you run on every slow or flaky list endpoint: read the SQL and the plan, predict where the cost or the drift comes from, and reach for the change that fixes the root cause.
Snippet 1 — the deep-offset plan
EXPLAIN ANALYZE
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;
-- Limit (rows=20) (actual rows=20 loops=1)
-- -> Index Scan ... on articles (actual rows=200020 loops=1) Quiz
Completed
The plan uses the index yet still takes seconds. What does 'actual rows=200020' on the Index Scan tell you, and what is the fix?
Heads-up The plan already shows an Index Scan. An index makes ordering cheap but cannot make the engine skip the 200000 rows the offset still forces it to read and discard. Keyset removes the skip.
Heads-up actual rows is the measured count for this run, not an estimate. The scan genuinely produced 200020 rows so the Limit could throw away all but the last 20.
Heads-up There is no expensive sort here — the index supplies order. The cost is reading and discarding offset rows, which work_mem does not change. Keyset is the fix.
Snippet 2 — the cursor handler
// GET /articles?after=<cursor>&limit=20
async function listArticles({ after, limit = 20 }) {
const { createdAt, id } = decodeCursor(after); // Base64 JSON -> {createdAt, id}
const rows = await db.query(`
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3
`, [createdAt, id, limit]);
const last = rows[rows.length - 1];
return {
items: rows,
nextCursor: encodeCursor({ createdAt: last.created_at, id: last.id }),
hasNextPage: true,
};
} Quiz
Completed
The seek and ordering are correct, but hasNextPage is wrong. What is the bug and the standard fix?
Heads-up The cursor only needs the sort key (created_at, id) to seek. title is payload, not part of the keyset, and adding it changes nothing about hasNextPage.
Heads-up The tuple comparison is exactly right for keyset; switching to OFFSET reintroduces the deep-page scan and drift. The defect is the hardcoded hasNextPage, not the seek.
Heads-up DESC ordering with the '<' comparison is consistent for forward paging. The actual bug is that hasNextPage is always true instead of derived from an n+1 probe.
Snippet 3 — the ORDER BY mismatch
CREATE INDEX idx_articles_feed ON articles (created_at DESC, id DESC);
-- handler issues:
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-05-01 09:00:00', 918273)
ORDER BY created_at ASC, id ASC -- note: ASC
LIMIT 20; Quiz
Completed
The composite index exists and the tuple comparison looks right, but the feed shows the wrong rows and latency is not flat. What is wrong?
Heads-up The literal just stands in for a decoded cursor. The real defect is that ORDER BY ASC contradicts the '<' comparison and the DESC index, breaking both correctness and the flat-latency seek.
Heads-up Postgres supports row-value comparison (a, b) < (x, y) directly and it is the idiomatic keyset form. The bug is the mismatched ORDER BY direction.
Heads-up The index column order must match the ORDER BY (created_at then id), not the primary key. Reordering to (id, created_at) would itself break the seek.
Snippet 4 — the count footer
const [{ total }] = await db.query(`SELECT COUNT(*) AS total FROM articles`);
const items = await listArticles({ after, limit: 20 });
return { items, total }; // renders "Showing 1–20 of {total}" Quiz
Completed
The keyset page is ~10 ms but the request p99 is seconds. The COUNT(*) is the culprit. What is the highest-leverage change?
Heads-up Caching can soften it, but the senior move is to question whether an exact total is needed at all. An estimate or a hasNextPage probe removes the scan entirely with no cache to stale.
Heads-up Under MVCC the engine still walks rows to determine visibility for your transaction; an index does not yield an exact count for free. Estimate or drop the total instead.
Heads-up A window count over the full set is the same full scan, just folded into the page query — it does not remove the cost. Drop or estimate the total.
Recap
Pagination is diagnosed in the plan and the handler: ‘actual rows’ far above the page size is the offset scan-and-discard signature, and keyset replaces the skip with a tuple seek; a cursor handler must fetch n+1 to derive hasNextPage rather than hardcode it; the ORDER BY direction, the comparison operator, and the composite index must all agree or you lose both correctness and flat latency; and an exact COUNT(*) per request is an MVCC full scan that dwarfs a fast page — estimate it or drop it. Read the plan, fix the root cause, then re-check the plan.