Pagination, Filtering & Sorting

Last lesson ended on a cliffhanger wearing a business suit. Your contract is clean, your nouns are plural, and a stranger confidently calls GET /products — on a catalog with two million rows. If you actually answer that question, everyone loses: your server melts serializing hundreds of megabytes of JSON, the network hauls it, a phone somewhere in a subway tries to parse it, and the user — who wanted to browse shoes — waits thirty seconds for data they will never scroll past. One naive client doing this in a loop is a denial-of-service attack that nobody meant to launch (your §5 rate limiter has opinions about them).

So every real API answers a different question than the one asked: not "give me the products" but "give me a page of the products, and a way to ask for the next one." That's pagination, and it sounds like the most boring topic in API design right up until you notice the trap hiding inside it: the collection keeps changing while the client walks through it. Orders arrive. Comments get deleted. The feed you're paging through is a moving walkway, not a bookshelf.

And that's the real subject of this lesson. There are two fundamentally different ways to remember where the client is in a collection — a position ("skip the first 40") or a value ("everything after order #17") — and under a changing collection, one of them quietly lies. Not with an error. With wrong data that looks right: duplicates the client already saw, rows it will never see. By the end of this lesson you'll know exactly when each bookmark breaks, why the fix is the one every serious API (Stripe, Slack, Twitter) converged on, and how filtering and sorting bolt into the same contract without wrecking it.

Start with the scheme everyone invents first, because it's genuinely intuitive. Offset pagination remembers your place as a count: "I've seen 40, give me the next 20."

GET /products?offset=40&limit=20

It maps one-to-one onto SQL — SELECT … LIMIT 20 OFFSET 40 — and onto the page-number UI every human has used: page 3 is offset = 2 × 20. It gives you jump-to-any-page for free ("page 47 of 300"), it's trivial to implement, and for plenty of collections it is honestly fine — an admin table of 200 customers, a settings list, anything small and mostly static.

Notice precisely what the bookmark is, though, because everything that follows hangs on it: offset=40 doesn't name any particular product. It names a position — "whatever happens to be 41st right now." The bookmark is an instruction about counting, not a reference to a thing. Keep that in mind while the data starts moving.

Here's the scene. A stranger's app is paging through your order feed, newest first. They load page 1 — orders 1 through 20 by position. They read for a few seconds. Meanwhile — because your product is alive — three new orders arrive, slotting in at the top of the feed. Every existing row silently slides down three positions.

Now the app asks for page 2: offset=20&limit=20 — "skip 20, give me the next 20." But which 20 got skipped? The current first twenty — which now includes the 3 new arrivals and only the first 17 rows the user already saw. The last 3 rows of the old page 1 have slid down into positions 21–23… and get served again on page 2. The user sees the same three orders twice. Flip the direction — rows deleted while they read — and the reverse happens: rows slide up into the already-skipped zone and are never shown at all.

Sit with how nasty this failure is: there's no error. Every response is a valid, well-formed 200 with plausible data. The client has no way to know the walk was wrong. Duplicates are annoying in a UI (your feed stutters), but the silent skips are the killer — think about an app exporting all orders for accounting, a nightly migration walking every user, a reconciliation job comparing systems. Offset-walking a live table means those jobs quietly miss rows, and you find out weeks later, in a spreadsheet dispute, with no log entry anywhere. The bookmark pointed at a position; the position moved; the walk lied.

And any write rate is enough. This isn't a "high-scale problem" — one insert, timed between two page requests, corrupts the walk. Busier feeds just make it happen every single time.

The second break has nothing to do with changing data — it's about what OFFSET makes the database do, and it surprises almost everyone the first time:

SELECT * FROM products ORDER BY created DESC LIMIT 20 OFFSET 99980;

The database does not teleport to row 99,981. There is no magic index of "positions" — position is defined by the sort order, right now. So the engine walks the sort order from the top, produces 99,980 rows, throws every one of them away, and then hands you 20. The work grows linearly with the offset: page 1 is instant, page 500 is sluggish, page 5,000 is a rack of CPUs producing garbage. One published Postgres benchmark on ~a million rows measured keyset pagination about 17× faster than offset at depth — and the gap only widens as the table grows.

This is why "a scraper is deep-paging our catalog" is a classic way for a database to catch fire: pages 1–10 cost nothing, and page 40,000 — requested by a bot that will never stop clicking next — costs a full scan. You built a rate limiter in §5 partly for this exact customer.

So the tally on offset: intuitive, jump-to-page, fine for small static lists — but the bookmark lies under writes and the cost grows with depth. Both problems have the same root (position is a made-up, shifting thing), and — beautifully — the same fix.

The fix is to change what the bookmark points at. Instead of "skip 20" — a position — remember the last item you actually saw — a value:

GET /orders?limit=20&starting_after=ord_8134

"Give me the 20 orders that come after order #8134 in the sort." That one change fixes both breaks simultaneously, and it's worth seeing why each one dies:

• The lie dies because inserts and deletes can't move a value. Three new orders arrive at the top? Irrelevant — ord_8134 is still ord_8134, and "what comes after it" is exactly the continuation of the walk. No duplicates (you only get things after your bookmark), no skips (nothing between your bookmark and the tail can slide past you). The walk delivers each row exactly once, no matter how hot the feed is.
• The treadmill dies because "after this value" is a WHERE clause, and a WHERE on an indexed column is a seek, not a scan: WHERE (created, id) < (:last_created, :last_id) ORDER BY created DESC, id DESC LIMIT 20. The database jumps straight to the bookmark in the index and reads 20 rows forward. Page 5,000 costs the same as page 1 — O(page size), forever.

This is cursor pagination (the SQL flavor is called keyset pagination), and it's what every serious feed API converged on: Stripe (starting_after + has_more), Slack, GitHub's GraphQL API, Twitter's timelines. When the collection is alive or deep — which is most collections worth paginating — values beat positions, always.

The price, honestly stated: you lose jump-to-page-N ("page 47 of 300" has no meaning when your bookmark is a value — you walk the list), previous-page requires reversing the comparison and takes care to get right, and a cursor can dangle if the exact row it anchors on gets deleted (well-built implementations anchor on the sort tuple, not the row, so the walk just continues past the ghost). For a UI that truly needs numbered pages on a static dataset — offset remains the right, honest tool. For everything live: cursors.

Everything above is one claim: positions lie under writes, values don't. Claims like that deserve a demonstration you run yourself — so here's a live order feed with rows arriving at the top, and two clients walking the same feed side by side: one paging by offset, one by cursor.

Page them in lockstep. With the feed paused, both walk perfectly — offset looks completely innocent, which is exactly how it ships to production. Now drag the write rate up (or press insert 3 now right between two clicks of next page) and keep paging: the offset side starts flashing red — rows you already saw coming back as duplicates — while a counter tracks the rows it silently skipped past. The cursor side, walking the same shifting feed, stays at zero-duplicates, zero-missed. Forever. That's the whole argument, happening in front of you.

Watch the depth meter too: it keeps score of Break #2. By page 250, the offset query has scanned and discarded thousands of rows to serve each page, while the cursor's seek still touches ~20. Then try the sharpest version of the experiment: pause the writes, walk both clients clean for a few pages, hit insert 3, and click next once. One insert. Three duplicates. That's all it takes.

Cursor pagination has one famous foot-gun of its own, and knowing it is the difference between reading about cursors and having shipped them.

Say you cursor on created — "orders after this timestamp." Timestamps collide: bursts of orders land in the same millisecond. Now "after created = 14:02:07.113" is ambiguous — there are four rows with that exact value; the ones your page cut off in the middle get skipped or duplicated, and you've rebuilt offset's lie with extra steps, just rarer and harder to reproduce (the worst kind of bug).

The rule: always cursor on a tuple that ends in a unique column.

WHERE (created, id) < (:last_created, :last_id)
ORDER BY created DESC, id DESC

The id tie-breaker makes every bookmark unambiguous — even among a thousand rows sharing a timestamp, each has a unique (created, id) position in the total order. Two supporting disciplines complete the professional setup:

• Make the cursor opaque. Don't expose ?after_created=...&after_id=... — base64 the tuple into a token: ?after=eyJjIjoxNzIw.... The stranger treats it as a magic bookmark (per L1: it's contract, and opaque means you can change the underlying keys later without breaking anyone). Stripe's starting_after=obj_id is the friendly variant of the same idea.
• Return has_more (or a next link/cursor, or GitHub-style Link: <...>; rel="next" headers) so the client knows whether to keep walking — which is the question they actually had.

Pagination never travels alone — the same stranger who can't take 2M rows also doesn't want all products, in default order. Filtering and sorting ride the query string, and the L1 discipline (guessable, nouns, boring) applies verbatim:

GET /products?category=shoes&in_stock=true&sort=-price,created&limit=20&after=eyJj...

• Filters are field names, not verbs: ?category=shoes, ?status=refunded, ranges as ?created_after=2026-01-01. A stranger who knows your resource's fields can guess your filters — same test as ever.
• Sorting has a tiny convention that won: ?sort=-price,created — minus for descending, commas for multi-key. Boring, guessable, done.
• Whitelist both. Every filterable field and sortable key should be an explicit, documented set — not "whatever fields we pass through to the database." Arbitrary pass-through is an injection surface and, worse in practice, an invitation to sort by an unindexed column (see next point).
• The interlock with pagination is the part people miss: the cursor encodes the sort. "After (price: 79.99, id: 812)" only means something under the price sort — change sort= mid-walk and the bookmark is gibberish (good implementations bake the sort into the opaque cursor and reject mismatches). And since keyset's whole speed story is "the WHERE rides an index," every sort order you offer needs an index that supports it. Each entry in your sort whitelist is a quiet promise about your database — which is why the whitelist is short at serious shops. (How indexes make that promise cheap is a Container-2 story.)
• total_count is a trap dressed as a feature. "Just tell me how many match" means running the full matching scan on every page request — the very work pagination exists to avoid. Stripe's answer is instructive: totals are off by default and only accurate to 10,000. Return has_more; it answers the question the client actually has ("can I keep going?") for free, and offer counts only where they're cheap or truly needed.

Both schemes are tools; the failure mode is not knowing which job you're holding:

The honest decision procedure is two questions. Does the collection change while clients walk it? If yes, positions will lie — cursor. Can clients go deep? If yes, offset's linear cost will find you — cursor. Both answers "no" — a static dropdown, a bounded admin table — is precisely the (real, common) niche where offset's simplicity and jump-to-page win. What's not on the menu is the default most codebases actually ship: offset everywhere, because it was the first thing that worked on the laptop, where the data never shifted and nobody paged past 3.

• "Offset pagination is legacy garbage." No — for small, static collections with a numbered-pages UI it's the better tool (simpler, and jump-to-page is real UX). It's specifically wrong for live or deep collections… which is what most public APIs serve.
• "Cursors are just fancy offsets." They're opposite species. An offset points at a position — a shifting, made-up coordinate that costs O(n) to reach. A cursor points at a value — stable under writes, reachable by index seek. Every property in the comparison table flows from that one difference.
• "My data barely changes; offset is fine." The lie needs exactly one insert timed between two page requests — and walkers that matter (exports, migrations, reconciliations) take minutes to hours, straddling thousands of writes. You won't get an error; you'll get a spreadsheet that doesn't add up, three weeks later.
• "Cursor on created_at — done." Timestamps collide; a non-unique cursor skips or duplicates at every collision, rarely and unreproducibly (the worst bug class). The rule is mechanical: tuple, ending in a unique column — (created, id).
• "Clients need total_count." Clients need to know whether to keep going — that's has_more, and it's free. A true count is a full scan per request; even Stripe won't promise it past 10,000. Sell the cheap answer that's actually wanted.

Fifteen minutes with SQLite — no setup, and you'll reproduce both breaks with your own hands:

1. Seed a feed. sqlite3 feed.db then CREATE TABLE orders(id INTEGER PRIMARY KEY, note TEXT); and insert ~50 rows (WITH RECURSIVE or a shell loop — ids 1–50, newest = highest id).
2. Walk it with offset. Page 1: SELECT id FROM orders ORDER BY id DESC LIMIT 5 OFFSET 0; — note the ids. Now be the write traffic: INSERT INTO orders(note) VALUES ('sneaky'),('sneaky'),('sneaky'); Then page 2: ... LIMIT 5 OFFSET 5;. Compare: the last rows of page 1 reappear on page 2. You just watched the silent duplicate happen in four statements.
3. Walk it with a cursor. Reset. Page 1: same first query; remember the last id you saw (say 46). Insert 3 rows again. Page 2: SELECT id FROM orders WHERE id < 46 ORDER BY id DESC LIMIT 5; — the walk continues exactly, inserts be damned. That WHERE id < is the entire technology.
4. Feel the treadmill (bonus, any Postgres): on a table with a million rows, EXPLAIN ANALYZE a LIMIT 20 OFFSET 500000 versus a keyset WHERE (created,id) < (...) LIMIT 20 — read the row counts the planner reports. One produces and discards half a million rows; one touches ~20.
5. Audit an API you use: does it paginate by ?page=/?offset= or by cursor/starting_after? Now that you know what shifts underneath, would you trust its page 2 during a busy hour? Would you trust an export built on it?

The lesson isn't SQLite syntax — it's that you've now seen a well-formed, error-free API response contain wrong data. That memory is the vaccine.

Never return everything — return a page and a bookmark. And the entire lesson is in what the bookmark points at:

• Offset = position. Intuitive, jump-to-page, one line of SQL — and under writes it lies (duplicates + silent skips, no error), while deep pages cost O(offset) in scan-and-discard. Right for small, static, numbered-page UIs.
• Cursor/keyset = value. "After item X": exact under any write rate (each row exactly once) and served by an index seek (page 5,000 = page 1). The professional default for feeds, deep lists, exports — with the tuple-ending-in-a-unique-column tie-break, an opaque token, and has_more instead of a ruinous total_count.
• Filtering and sorting ride the same contract: field-named params, -/comma sort syntax, everything whitelisted — and remember the interlock: the cursor encodes the sort, and every offered sort is a promise of an index.

One thread ties it to everything before: this was again a lesson about what the client can trust — L1 made the contract guessable; this lesson made walking it honest.

But there's a scarier trust problem waiting. The stranger's request for page 2 timed out — did it fail before or after the server did the work? They'll retry, obviously. Now: what if that request was POST /orders… and the first one had worked? Two orders, one click. Making retries safe — the same request twice landing as one effect — is idempotency, and it's the next lesson.