My LibraryBuy PDF
Part 5/Designing An API

Pagination, Filtering and Sorting

You ship GET /api/orders. It selects every row and returns a JSON array. In development, with two hundred orders in the table, it responds in 4ms and nobody thinks about it again.

Eighteen months later that table holds 1.2 million rows. The same endpoint now takes six seconds, allocates half a gigabyte to build the response, and every so often the process dies with an out-of-memory error. Nothing in the code changed. The data changed.

Pagination is the fix. It looks trivial, just bolt on a LIMIT, and then it quietly ruins your week the first time the table gets big or the rows shift while someone is mid-scroll. Let me show you where the obvious approach cracks, and what feeds actually use instead.

Offset pagination, the one everybody writes first

The natural move is LIMIT and OFFSET. Page 1 is the first 20 rows. Page 2 skips 20 and takes 20. Page N skips (N - 1) * 20.

-- page 3, 20 rows per page
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT $1 OFFSET $2;   -- $1 = 20, $2 = 40

It is easy to reason about, it gives you real page numbers, and you can jump straight to page 47. For plenty of screens that is exactly right. It also has two failure modes, and both show up precisely when your product starts working.

It gets slower the deeper you page

OFFSET 40000 does not mean “start reading at row 40000.” The database has no way to teleport there. It reads rows in sorted order and discards the ones before the offset, then returns the slice you asked for. Skip 40000 rows and it walks 40000 rows to get to yours. Every request. Even with a perfect index on the sort column, the engine still steps through those index entries one by one.

Here are real numbers from a Postgres table of a million rows, indexed on the sort key. Page 1 comes back in well under a millisecond. At OFFSET 999990, the identical query takes over 130ms, because the planner crawled almost a million index entries just to throw all but ten away. The deep page is hundreds of times slower than the first. Offset cost scales with how far in you are, not with how much you return.

Offset: LIMIT 10 OFFSET 40walk past 40 rows, throw them awayread 10the engine visits every skipped row first, so deeper pages cost moreCursor: WHERE (created_at, id) < last LIMIT 10seek here, read 10jumps straight to the cursor key. skipped rows are never touched, so depth is free
Offset walks past and discards every skipped row before it reads your slice; a cursor seeks straight to the key and reads only the page.

There is a security angle here too. Deep offsets are a cheap denial-of-service lever. A bored scraper hitting ?page=50000 in a loop forces your database to run the most expensive shape of the query over and over. Even without a single line of malice, an “export everything” button that walks to the end of a huge list will do the same to you.

The window shifts while people read

Slow is annoying. This next one is a correctness bug, and it is the reason feeds abandoned offset entirely.

An offset is a position in a list, and the list is not frozen. Say page 1 (OFFSET 0 LIMIT 3) returns rows A, B, C, newest first. Before the reader taps for page 2, someone inserts a new row X at the top. The list is now X, A, B, C, D, E, F. Page 2 is OFFSET 3, which now points at C, D, E. The reader sees C a second time and has no idea anything went wrong.

Delete a row instead of inserting one and you get the mirror image. Everything shifts up by one, and a row slips through the crack between pages and is never shown at all. On a busy feed, with rows arriving every second, this is not an edge case. It is the normal case.

list when page 1 loadsABCDEpage 1 → A B Cinsert row Xat the topevery row shifts down onelist when page 2 loadsX newABCDEpage 2 (OFFSET 3) → C D EC ends page 1 and starts page 2, so it is served twice
A row inserted at the top pushes everything down one slot, so OFFSET 3 now lands on C, a row page 1 already returned.

Cursor pagination: remember where you were

The trick is to stop thinking in positions. Instead of “skip 40, take 20,” remember the sort key of the last row you handed out, and next time ask for rows after that key. “Give me 20 orders older than this exact (created_at, id).” You will see this called keyset pagination or seek pagination. Same idea.

SELECT id, total, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)   -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT $3;

That WHERE clause is doing something you might not have seen: comparing a whole tuple. (created_at, id) < ($1, $2) is a single row-value comparison, and it reads lexicographically, exactly like comparing words. It means “created_at is before $1, or it ties $1 and then id is below $2.” That is precisely the boundary you want: everything strictly past the last row you showed, with ties broken the same way your ORDER BY breaks them. Do not rewrite it as created_at <= $1 AND id < $2. That subtly drops rows at the boundary. The tuple form gets it right in one shot.

Two things happen at once here, and both are wins.

It is fast at any depth. With an index on (created_at DESC, id DESC), the database seeks straight to the cursor position and reads the next 20 entries in order. Nothing is skipped. Page 1 and page 900,000 cost the same, because “seek to a key and read forward” does not care how far into the table that key sits. Look again at the bottom half of the first diagram: the cursor lands directly on its slice.

It is stable while data moves. The cursor is a value, not a position. Insert X at the top and it changes nothing about “older than this key.” The reader keeps walking down from exactly where they were, no duplicate, no gap. The new row appears at the top for the next person who starts fresh, which is what you actually want.

The catch: no jumping to page 500

Cursors go next, and with a second cursor, previous. They do not do “page 500,” because there is no page 500 to seek to without having walked the 499 pages in front of it. For an infinite feed or an app with a “load more” button, nobody wanted numbered pages anyway. For a back-office data grid where an operator types a page number into a box, that is a genuine limitation. Match the tool to the screen, and do not feel clever for using cursors where offset was fine.

Here is the difference you can poke at. Load a page, insert a fresh row at the top, then load the next page. In offset mode a row you already saw comes back flagged as a duplicate. Flip to cursor mode and run the same steps; the duplicate is gone.

interactiveOffset repeats a row when the list shifts; cursor does not

When offset is still the right answer

Do not cargo-cult this. Offset is fine, and often the better choice, when any of these hold:

  • the table is small and bounded (an admin list of 300 coupon codes),
  • users genuinely need to jump to an arbitrary page number,
  • the data barely moves while it is being read.

The performance cliff is a function of depth. No depth, no cliff. A 12-page settings table with a page picker should just use LIMIT/OFFSET and move on. Save keyset for the big, live, deep lists where both problems actually bite.

Designing the response

Keep the envelope lean. Items, plus a cursor for the next page. That is almost all you need.

{
  "items": [
    { "id": 8842, "total": 41.9, "created_at": "2026-07-12T09:20:11Z" },
    { "id": 8841, "total": 8.0, "created_at": "2026-07-12T09:19:54Z" }
  ],
  "nextCursor": "eyJ0IjoiMjAyNi0wNy0xMlQwOToxOTo1NFoiLCJpZCI6ODg0MX0"
}

nextCursor is an opaque string. The client sends it back untouched and never parses it. Under the hood it is just the last row’s sort key, and base64url of the (created_at, id) pair is plenty. Opacity is the point: because clients treat it as a blob, you can change the encoding later (add a field, switch the format) without breaking anyone.

import { Buffer } from "node:buffer";

const encodeCursor = (row) =>
  Buffer.from(JSON.stringify([row.created_at, row.id])).toString("base64url");

const decodeCursor = (c) => {
  const [created_at, id] = JSON.parse(Buffer.from(c, "base64url").toString());
  return { created_at, id };
};

Now the tempting mistake: the totals-heavy envelope. The instinct is to return &#123; items, page, pageSize, totalItems, totalPages &#125; so the UI can render “Page 3 of 41,912.” That total is expensive. SELECT COUNT(*) with a filter has to count every matching row, and on a large table that is its own slow scan, frequently slower than the page query it is decorating. You would pay it on every single request to compute a number most users never read.

You do still need to know one thing: whether a next page exists, so you know whether to emit nextCursor. Do not run a COUNT for that either. Ask for one row more than you plan to return. If the extra row shows up, there is a next page. Drop it, and build the cursor from the genuine last item.

const limit = Math.min(Math.max(Number(req.query.limit) || 20, 1), 100);
// ...run the SELECT with LIMIT (limit + 1)...
const rows = await runPageQuery(limit + 1, cursor);

const hasMore = rows.length > limit;
const items = hasMore ? rows.slice(0, limit) : rows;
const nextCursor = hasMore ? encodeCursor(items[items.length - 1]) : null;

res.json({ items, nextCursor });
GET /orders?limit=3no cursor: the first pageitems: 101, 102, 103nextCursor: c_Zm9vGET /orders?limit=3&cursor=c_Zm9vcursor carried over from page 1items: 104, 105, 106nextCursor: c_YmFyhand the cursor back as the next request
The nextCursor from one response is the exact input to the next request. The client just hands it back and never looks inside.

Sorting has to be deterministic

This is the pagination bug that most often sails through code review: sorting on a column that has ties, with no tiebreaker.

ORDER BY created_at DESC looks sorted, and it is, right up until two rows share a created_at. At scale they will. Batch inserts land many rows in the same transaction, and timestamps are coarser than you think. When rows tie, the database is free to return them in whatever order is convenient, and it may pick a different order on the next run. Add a LIMIT and a tie can straddle the page boundary. A row that was the last item on page 1 becomes the first item on page 2, or slips between the two and is never seen.

ORDER BY created_at DESC (tie at 10:01, no tiebreaker)page size = 3run 110:03 a110:01 r510:01 r610:01 r709:58 z9r6 sits on page 1run 210:03 a110:01 r710:01 r510:01 r609:58 z9but page 2 nowORDER BY created_at DESC, id DESC (tiebreaker)always10:03 a110:01 r710:01 r610:01 r509:58 z9same order every run
With tied created_at values and no tiebreaker, two runs return the tie group in different orders, so r6 lands on page 1 in one run and page 2 in the other. Adding id fixes the order for good.

The fix is one column: a unique tiebreaker on the end of every sort. The primary key is the obvious pick.

ORDER BY created_at DESC, id DESC   -- id makes the ordering total

Now there are no ties left, the order is fully determined, and your cursor comparison has a single exact boundary to seek to. This is why every keyset example above carries id in both the ORDER BY and the tuple. A deterministic sort is not a nice-to-have for pagination. It is a precondition, and offset paging is quietly broken without it too.

Filtering that lands on an index

Filters are where a paginated endpoint silently turns back into a full table scan. WHERE status = $1 is cheap when status is indexed and brutal when it is not. On a million rows that is the difference between a couple of milliseconds and a couple of seconds. And the filter and the sort interact: to serve WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20 without touching the whole table, you want one composite index that leads with the filtered column and continues into the sort keys.

CREATE INDEX ON orders (status, created_at DESC, id DESC);

With that index the database jumps to the paid section, which is already sitting in created_at order, and reads twenty rows off the front. Without it, it reads every row, keeps the paid ones, then sorts the survivors, on every request.

No matching index · Seq Scanpendingpaidrefundedpaidpendingpaidrefundedreads every row,then filters and sortsComposite index · Index Scanstatus = pending …status = paid, in created_at orderread 3,then stopstatus = refunded …seeks to paid, already ordered,reads exactly one page
No matching index means a full scan then a sort; a composite index on (status, created_at, id) lets the query seek to the paid section, already ordered, and read one page.

Indexes are their own deep topic, and the way a composite index maps onto this seek-and-range plan is covered in database indexes. Two rules keep the filtering layer honest:

  • Whitelist sortable and filterable fields. You cannot parameterize a column name or a sort direction; $1 only stands in for a value. So never splice a client string into ORDER BY or into an identifier. Validate the incoming sort against a fixed map and reject anything unknown.
  • Always parameterize values. WHERE status = $1, never string concatenation. Pagination endpoints carry a lot of query parameters, which makes them a favorite target for injection. There is more on treating every input as hostile in validating input.
const SORTS = {
  newest: "created_at DESC, id DESC",
  oldest: "created_at ASC, id ASC",
  biggest: "total DESC, id DESC",
};
const orderBy = SORTS[req.query.sort] ?? SORTS.newest;   // never the raw string

Cap the page size

If limit is a query parameter, some caller will send ?limit=1000000. Maybe a runaway sync job, maybe a fat-fingered script, maybe someone probing to see if they can tip you over. Clamp it on the server, always. Pick a sane default (20 to 50 is typical) and a hard ceiling (100 is a common one), and enforce both.

const limit = Math.min(Math.max(Number(req.query.limit) || 20, 1), 100);

Stripe’s API caps limit at 100 and defaults to 10; most large public APIs live in that range. The exact ceiling matters far less than the fact that one exists. An uncapped limit is the same unbounded-endpoint bug from the very top of this page, just wearing a query parameter as a costume.

The N+1 trap hiding behind your page

One more, because it undoes everything above. You paginate a list down to a tidy 20 items, then loop over them and fire one extra query per item to fetch something related (the customer, the line-item count, a thumbnail). Your clean 20-row page just became 21 database round-trips, and it grows with the page size. That is the N+1 problem, and the fix is a single batched query or a join rather than a loop of lookups. It is worth its own read: see N+1 queries. Pagination bounds the outer query. It does nothing for what you do inside the loop, and a slow page is a slow page whether the cost is in the SELECT or in the 20 sneaky ones after it.

Summary

  • The endpoint that returns everything works until the table is big. Bound it before that day, not after.
  • Offset (LIMIT/OFFSET) is simple and gives real page numbers, but it slows down the deeper you page (the engine walks every skipped row) and it duplicates or drops rows when the list shifts under the reader.
  • Cursor / keyset pagination remembers the last row’s sort key and seeks past it with a tuple comparison like (created_at, id) < ($1, $2). Fast at any depth, stable while data moves, at the cost of no jump-to-page-N.
  • Offset is still the right call for small, bounded, mostly-static tables where users want numbered pages. Match the tool to the screen.
  • Return items plus an opaque nextCursor. Skip the totals-heavy envelope; an exact COUNT is its own slow scan. Fetch limit + 1 rows to learn whether a next page exists.
  • Sorting must be deterministic. Always append a unique tiebreaker such as id, or tied rows reshuffle between requests and pages leak.
  • Filters must land on an index, ideally a composite index that leads with the filter and continues into the sort keys. Whitelist sortable fields; parameterize every value.
  • Cap the page size server-side, and watch for an N+1 query hiding inside the loop that hydrates each row.