Reviewing Pagination Before Someone Requests a Million Rows

The list endpoint shipped with offset pagination and a page size parameter the client could set, and it worked beautifully in review, on the small test dataset, with no concurrent writes. All three of those conditions are absent in production, and the review that approved it examined none of them. Pagination is the most under reviewed surface in API code, because it reads as plumbing, and the plumbing is where the money goes.

This is the checklist I run on any diff that returns a list, written against Postgres 16.3 and a Node 22.14 API, with the reasoning for each item.

1. The offset that scans

LIMIT 20 OFFSET 100000 is not a window into the data. It is a command to walk the first 100,020 rows and hand you the last twenty. The cost grows with the offset, so the deepest pages, which crawlers and exporters love, are the most expensive, and the endpoint's worst case is precisely its last page.

The review question is what the database does at the maximum offset, not at the first page. If the answer is a scan proportional to the offset, the pagination is O(n) per page and the review should ask for keyset pagination, where the page is defined by the last seen key and the database seeks rather than walks:

WHERE (created_at, id) > ($last_ts, $last_id) ORDER BY created_at, id LIMIT 20

The keyset form is constant cost per page and immune to the deep offset tax, at the price of not supporting arbitrary page jumps, which is a trade most consumers never needed anyway.

2. The page size the client chooses

A page size parameter with no ceiling is a denial of service with documentation. A client, malicious or merely careless, requests a million rows and the API obliges, spending the database scan, the serialisation and the transfer from the endpoint that spent nine hundred milliseconds building JSON, on a single request.

The review wants a default that serves the UI and a hard maximum that protects the backend, enforced server side, with the maximum documented so clients fail predictably rather than silently capped. The silent cap is the export bug from the export that always returned 999 rows, reborn as an API, so if a cap exists it must be loud and in the contract.

3. The cursor that drifts

Offset pagination has a correctness defect beyond cost: between page one and page two, a row inserts at the front, and every subsequent page shifts by one, so a row is repeated or skipped. For a feed this is a visible glitch. For an export or a reconciliation, it is wrong data, which is the worse failure because it is silent.

The keyset form mostly fixes it, because the cursor is a position in the ordering rather than a count, but the review must check the ordering's stability. If two rows can share the cursor value, the comparison must include a unique tiebreaker, the id, or the cursor repeats and skips rows exactly as the offset did. A cursor on a non unique column without a tiebreaker is the offset bug wearing a new name.

4. The count query that is a second scan

Many list endpoints return the total count for the page controls, and the count is a full scan or near enough, doubling the cost of every page and lying slightly under concurrency anyway. The review should ask whether the total is needed at all, because "next page exists" is usually all the UI requires, and it is cheap, and the exact total is a luxury the list should not pay for on every request.

Where the total is genuinely needed, it belongs in a separate, cacheable, approximate endpoint, not in the hot path of every page.

5. The enumeration surface

A paginated list is a walking path over the dataset, and the review must ask who may walk it and how fast. Sequential ids as cursors make the list enumerable, the disclosure from what your incrementing order IDs tell your competitors, and an unrate limited list is a scraper's feed. Authorisation per row, applied inside the page query rather than after it, is the difference between a page of what you may see and a scan that filters, which also fixes the cost of authorising after fetching.

The shape of a good review comment

Not "looks good, consider cursor pagination someday". Instead: "offset at the maximum page walks a million rows per request, the page size is client controlled with no ceiling, and the cursor on created_at alone will skip rows on ties. Switch to keyset on (created_at, id), cap the page at one hundred in the contract, and drop the total count from the hot path."

That comment names the cost, the abuse and the correctness defect with their fixes, and it treats the list as the production surface it is, because a list endpoint is a contract about cost, enumerability and truth under concurrency, and the diff that reads as plumbing is the one shipping all three.

It is worth adding the habit that keeps the review honest over time: record the maximum observed offset and the maximum requested page size as metrics on every list endpoint, because the two numbers are the endpoint's real usage, and when the observed offset creeps toward the size where keyset stops being optional, or the requested size creeps toward the ceiling, the metrics say so at review time rather than in the query log at two in the morning. The review checklist is the gate. The metrics are the tripwire after it.

The takeaway

A list endpoint is reviewed by examining its worst page, its maximum size and its behaviour under concurrent writes, because those are the three conditions review never reproduces and production always has. Keyset over offset, capped and loud page sizes, stable cursors with a unique tiebreaker, and no expensive total in the hot path.

The plumbing is the performance, the security and the correctness, all at once, and the diff that reads as boring is the one shipping all three.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud