system-design

Pagination, Filtering, and Sorting

Never return an unbounded list. The way you paginate seems trivial and is the difference between an API that scales and one that falls over on page 10,000.

Prerequisites: API Design Principles, Indexing Time to read: ~18 minutes

The problem

GET /users        → returns all 50 million users

🚨 This is a design error, not a missing feature. It exhausts your database (a full scan), your server’s memory (serializing 50 million records), the network (gigabytes), and the client. And retrofitting pagination onto an endpoint that clients already use is a breaking change.

Every collection endpoint needs pagination from day one, with a default limit. That’s non-negotiable.

The interesting part is how — and offset pagination, the obvious choice, is the wrong one at scale.

Offset pagination — the obvious, flawed choice

GET /users?limit=20&offset=40        → SELECT * FROM users LIMIT 20 OFFSET 40

✅ Simple, intuitive, lets you jump to any page (page=500), and shows total counts.

🚨 Two serious flaws:

1. It gets slower as you go deeper — O(offset). OFFSET 1000000 makes the database read and discard one million rows to return 20.

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 1000000;
-- The database fetches 1,000,020 rows and throws away 1,000,000. Slow, and it worsens per page.

At page 50,000, this query is unusable. Deep offset pagination is a real production problem — a scraper or an infinite-scroll UI walks to a deep page and hammers the database.

2. It’s inconsistent under writes. If a row is inserted or deleted between page requests, items shift:

Page 1: rows 1–20
(someone inserts a new row at the top)
Page 2 (offset 20): row 20 appears again — the user sees a duplicate

🚨 In a fast-changing list (a feed, a search result), offset pagination shows duplicates and skips. The user scrolling a feed sees the same post twice, or misses one entirely.

Verdict: fine for small, stable, admin-facing datasets where page-jumping matters. Wrong for large or changing data.

Cursor (keyset) pagination — the correct default

Instead of “skip N rows,” say “give me rows after this specific point.”

GET /users?limit=20                              → first page
   → returns items + a cursor pointing at the last item
GET /users?limit=20&after=eyJpZCI6MTIzfQ         → next page, after that cursor

The cursor encodes the position (usually the sort key of the last item):

-- No OFFSET. Uses the index directly. O(1) regardless of depth.
SELECT * FROM users WHERE id > :last_seen_id ORDER BY id LIMIT 20;

🚨 Why this is better:

Constant time at any depth. WHERE id > 1000000 uses the index to jump straight to the position — no scanning and discarding. Page 50,000 is as fast as page 1.
Consistent under writes. You’re anchored to a value, not a count, so inserts and deletes elsewhere don’t cause duplicates or skips.

⚖️ What you give up:

No jumping to an arbitrary page — only next/previous. (Usually fine; infinite scroll doesn’t need page numbers.)
No easy total count — you can’t say “page 3 of 500.”
The sort field must be unique and indexed — see the tie-break issue below.

🎙️ The answer that scores well: “I’d use cursor pagination. Offset gets slower the deeper you go because the database scans and discards rows, and it shows duplicates when the list changes under you. Cursor pagination is constant-time at any depth and stable under writes — the trade-off is you can’t jump to an arbitrary page, which infinite scroll doesn’t need anyway.”

The tie-break problem

🚨 A subtle cursor-pagination bug worth knowing, because it’s a common follow-up.

If you paginate by a non-unique field (created_at), rows with the same value straddle a page boundary and get skipped or duplicated:

-- ❌ Two users created at the same instant: one may be lost across the boundary
WHERE created_at > :last_created_at ORDER BY created_at LIMIT 20;

Fix: paginate by a composite of the sort field plus a unique tiebreaker (the ID):

-- ✅ Always breaks ties consistently
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id LIMIT 20;

The cursor encodes both values. This needs a composite index on (created_at, id). → Indexing

Cursor encoding

The cursor should be opaque — clients treat it as a token, not something to parse or construct.

after=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0yMiIsImlkIjoxMjN9
     = base64({"created_at": "2026-07-22", "id": 123})

🚨 Why opaque matters: if clients parse the cursor, its structure becomes a contract you can never change — you couldn’t switch sort fields or add a tiebreaker without breaking them. Base64-encoding (and ideally signing, to prevent tampering) keeps it an implementation detail. Encoding the sort parameters in the cursor also means the client can’t accidentally change the sort mid-pagination.

Page-number pagination — the middle ground

GET /users?page=3&per_page=20

Just offset pagination with friendlier parameters (offset = (page-1) × per_page). Same flaws. Use it only for the same cases offset is acceptable: small, stable, admin-facing data where users expect page numbers and totals.

The comparison

	Offset / page-number	Cursor / keyset
Jump to page N	✅	❌ Next/previous only
Total count	✅ Easy	❌ Hard/expensive
Deep pages	❌ O(offset), gets slow	✅ O(1), constant
Stable under writes	❌ Duplicates/skips	✅ Consistent
Complexity	Simple	Moderate
Best for	Small, stable, admin UIs	Large data, feeds, infinite scroll, public APIs

Filtering and sorting

Filtering via query parameters:

GET /orders?status=shipped&min_total=1000&created_after=2026-01-01

🚨 Every filterable field must be indexed, or filtering triggers a full scan — the same problem pagination was solving. Don’t advertise a filter you can’t serve efficiently. → Indexing

Complex filters — for anything beyond simple equality, either a structured query language (RSQL, or a JSON filter object) or a dedicated search engine. Don’t build a half-baked query language into your REST params.

Sorting:

GET /orders?sort=-createdAt,total      → createdAt descending, then total ascending

🚨 Sortable fields must be indexed, and 🚨 sorting interacts with cursor pagination — the cursor must encode the sort. If a client can sort by any field, you need cursor logic (and an index) per sort option, which is why public APIs usually restrict sorting to a few indexed fields.

The total count cost: returning a total ("totalCount": 48213) requires a COUNT(*) with the same filters, which can be as expensive as the query itself on a large table. Options: omit it (cursor style), cache/approximate it, or compute it only when explicitly requested (?include_total=true).

Practical response shape

{
  "data": [ /* the items */ ],
  "pagination": {
    "nextCursor": "eyJpZCI6MTQzfQ",
    "hasMore": true,
    "limit": 20
  }
}

Rules:

Always enforce a maximum limit (limit=1000000 must be capped, e.g. at 100). 🚨 Otherwise a client asking for everything is back to the unbounded-list problem.
A sensible default limit (20–50) when none is given.
hasMore is cleaner than a total for cursor pagination — the client knows whether to keep going without an expensive count.
Consistent shape across every collection endpoint — pick one pagination style and structure and apply it everywhere. → Principles

⚖️ Trade-offs

Choice	Gain	Cost
Offset pagination	Page-jumping, easy totals, simple	Slow at depth; duplicates/skips under writes
Cursor pagination	Constant-time, stable under writes	No page-jumping; hard totals; sort field must be indexed+unique
Opaque cursors	Free to change internals	Clients can’t construct or reason about them
Returning totals	Useful UI (“page 3 of 500”)	Expensive `COUNT(*)` on large tables
Rich filtering/sorting	Powerful	Every field needs an index; complexity to secure

In the real world

Twitter, Facebook, Instagram, and Slack all use cursor pagination for their feeds and timelines, precisely because these are large, constantly-changing lists where offset would show duplicates and degrade at depth. Their public API docs specify cursors, not page numbers.
Stripe’s API uses cursor pagination (starting_after / ending_before) with opaque cursors — a clean reference implementation of the pattern.
The “deep offset” incident is a recognized failure: a scraper or a misbehaving client requests ?page=100000, the database chews through millions of rows per request, and the endpoint takes down the database. Cursor pagination or a hard depth cap prevents it.

🚨 Interview traps

Unbounded list endpoints. Every collection is paginated with a default and maximum limit.
Offset pagination for a large or changing dataset without knowing its two flaws.
Not knowing the tie-break problem with non-unique cursor fields.
Filterable/sortable fields with no index. That’s the full-scan problem again.
Parseable (non-opaque) cursors that lock you into a structure.
Returning a total count on a huge table without noting the COUNT(*) cost.
No maximum limit, so a client can request everything.

🎙️ Soundbites

“Cursor pagination as the default. Offset gets slower the deeper you go — the database scans and discards rows — and it shows duplicates when the list changes underneath. Cursor is constant-time and stable; you just give up jumping to an arbitrary page.”
“I’d paginate by a composite of the sort field and the unique ID, so rows with the same timestamp don’t get skipped across a page boundary — and encode both in an opaque cursor.”
“The cursor is opaque and base64-encoded so its structure isn’t a contract — I can change the sort or add a tiebreaker without breaking clients.”
“Every filterable and sortable field needs an index, or we’ve just moved the full-scan problem from pagination to filtering. Public APIs usually restrict sorting to a few indexed fields.”
“I’d cap the limit and return hasMore rather than a total — a COUNT(*) on a large filtered table can cost as much as the query itself.”

🛠️ Try it

1. Feel the deep-offset problem. Load 10 million rows. Time LIMIT 20 OFFSET 0, then OFFSET 5000000, then OFFSET 9000000. Watch the query time climb linearly with the offset. Then run the equivalent cursor query (WHERE id > :x LIMIT 20) at each depth and watch it stay flat. This single comparison is the whole argument.

2. Cause duplicates. Paginate a table with offset while inserting new rows at the top between page requests. Observe items appearing on two pages. Then switch to cursor pagination and confirm the duplicates disappear.

3. Hit the tie-break bug. Paginate by a non-unique created_at where several rows share a timestamp across a page boundary. Confirm one gets skipped. Then paginate by (created_at, id) and confirm it doesn’t.

4. Build opaque cursors. Implement base64-encoded cursors carrying (sort_value, id). Then try to change your sort field and confirm existing cursors still decode sensibly (or are cleanly rejected) — that’s the flexibility opaque cursors buy.

Check yourself

1. What are the two problems with offset pagination at scale?

**Performance degrades with depth.** `LIMIT 20 OFFSET 1000000` forces the database to fetch and discard the first million rows to return 20 — it's O(offset), so each deeper page is slower than the last, and deep pages become unusable. A scraper or infinite-scroll UI that walks to a deep offset hammers the database. **Inconsistency under writes.** Because pages are defined by a count of rows to skip, any insert or delete between page requests shifts every subsequent row: a new row at the top means the last item of page 1 reappears as the first item of page 2 (a duplicate), and a deletion means an item is skipped. On a fast-changing list like a feed, users see duplicates and miss items.

2. Why is cursor pagination constant-time regardless of depth?

Because it uses a `WHERE` condition on an indexed column rather than an `OFFSET`. Instead of "skip one million rows then return 20," it says "return 20 rows where id > 1,000,000" — and the database uses the index to seek directly to that position and read forward, without touching the preceding rows at all. There's no scanning-and-discarding, so fetching page 50,000 costs the same as page 1. The cursor carries the sort-key value of the last item seen, which becomes the `WHERE` anchor for the next request. The requirement is that the sort field is indexed (and unique, or composited with a unique tiebreaker).

3. What is the tie-break problem in cursor pagination?

When you paginate by a non-unique field like `created_at`, rows sharing the same value can straddle a page boundary. If the cursor is just `WHERE created_at > :last_created_at`, then several rows with the identical timestamp of the last item on a page get skipped entirely — they're neither `>` the cursor (so excluded from the next page) nor were they all returned on the previous page (limited to 20). The fix is to paginate by a composite of the sort field and a unique tiebreaker: `WHERE (created_at, id) > (:last_created_at, :last_id) ORDER BY created_at, id`, with both values encoded in the cursor and a composite index on `(created_at, id)`. This gives a strict, unambiguous total ordering.

4. Why should cursors be opaque to clients?

Because if clients parse or construct cursors, the cursor's internal structure becomes a contract you can never change. You'd be unable to switch the sort field, add a tiebreaker, change the encoding, or alter pagination internals without breaking every client that hard-coded assumptions about the cursor format. Base64-encoding an internal token (and ideally signing it against tampering) keeps the structure an implementation detail — clients treat it as an opaque "give me what comes after this" token. It also lets you encode the sort parameters inside the cursor, preventing a client from accidentally changing the sort mid-pagination, which would produce nonsensical results.

5. Why is returning a total count expensive, and what are the alternatives?

Because computing an accurate total for a filtered query requires a `COUNT(*)` with the same `WHERE` conditions, which on a large table means scanning all matching rows — potentially as expensive as, or more expensive than, the page query itself, and it can't be served from the index alone if the filter isn't fully indexed. Alternatives: omit the total entirely and return a `hasMore` boolean (the cursor style — the client knows whether to keep paging without a count); compute an *approximate* count (Postgres's `reltuples` estimate, or a cached/periodically-refreshed value); or compute the exact total only when the client explicitly opts in (`?include_total=true`), accepting the cost when it's genuinely needed for a "page 3 of 500" UI.