Pagination, Filtering and Sorting

A list endpoint looks easy: GET /orders returns the orders. Then one day the table has two million rows. The endpoint tries to return all of them, the server runs out of memory, the response takes a minute, and the mobile app freezes. The fix is pagination: return the list in small pieces called pages.

How you make pages matters more than most people think. The most popular way (?page=3&per_page=20, which becomes OFFSET 40 in SQL) has two quiet problems: it gets slower on later pages, and it shows wrong results when data changes between requests. In this lesson we measure both, with real timings, and then build the better way.

This is lesson 3 of ten. Before it: Request and Response Design. After it: Versioning and Evolving an API. For the database side (indexes and EXPLAIN), Backend Lesson 7 and the SQL and Postgres series are good companions.

Words used in this lesson

• Pagination is returning a long list in small parts (pages).
• Page size / limit is how many items one page holds.
• Offset pagination says "skip N rows, then give me the next M" (OFFSET 40 LIMIT 20).
• Cursor pagination (also called keyset or seek pagination) says "give me the rows after this one". The client keeps a pointer to the last row it saw.
• Cursor / page token is that pointer, sent to the client as an opaque string. Opaque means the client must not read or build it, only send it back.
• Keyset is the sorted column (or columns) that the cursor is made from, such as (created_at, id).
• Tie-breaker is an extra unique column in the sort (usually id) so that rows with the same value still have a fixed order.
• Index is a sorted lookup structure in the database that makes finding rows fast.
• Filtering keeps only some rows (?status=paid). Sorting sets their order (?sort=-created_at).
• Total count is the number of all rows that match, not only this page.
• Link header is an HTTP header that carries URLs of related pages, like <...>; rel="next".
• Allow-list is a fixed list of accepted values. Everything else is rejected.

Rule zero: never return an unbounded list

Both big API guidelines I read say it: a list must be paginated from the first version. Google's AIP-158 points out that adding pagination to an existing endpoint is a breaking change, because old clients expect all the items in one response and will quietly see only the first page. The Zalando guideline says lists that may be larger than a few hundred entries must support pagination to protect the service and to help clients process data in batches.

So even if your table has 30 rows today, give the endpoint:

• A default limit (for example 20 or 50).
• A maximum limit (for example 100), and a rule for larger requests. Google says to coerce a value above the maximum down to the maximum, and to return an error for a negative number. Our server does the same.
• A way to ask for the next page.

Offset pagination: the simple way

It looks like this:

GET /orders?limit=20&offset=40          (or ?page=3&per_page=20)

And in SQL:

SELECT id, total_minor FROM orders ORDER BY id LIMIT 20 OFFSET 40;

It is popular for good reasons. It is easy to understand. Every web framework supports it. And the user can jump to page 7. The Zalando guideline lists these as the real advantages of offset: wide framework support, and jumping to a page. Use it when the data is small, rarely changes, and people really need page numbers (an admin table with 500 rows).

Its problems are speed and correctness. Let us measure both.

Problem 1: OFFSET gets slower the deeper you go

OFFSET 990000 does not jump. The database must walk past 990,000 rows and throw them away, and then return 20. The work grows with the offset. Here is the benchmark. The table has id, customer, total_minor, created_at, and both queries sort by id (the primary key, so an index is used in both):

const offsetQ = db.prepare("SELECT id,total_minor FROM orders ORDER BY id LIMIT ? OFFSET ?");const keysetQ = db.prepare("SELECT id,total_minor FROM orders WHERE id > ? ORDER BY id LIMIT ?");

== 5,000 rows, page size 20 (median of 5 runs, ms) ==page position        OFFSET     keyset  0% (offset       0)     0.021     0.022   same rows: true 50% (offset    2500)     0.046     0.018   same rows: true 90% (offset    4500)     0.069     0.016   same rows: true 99% (offset    4950)     0.089     0.018   same rows: true== 200,000 rows, page size 20 (median of 5 runs, ms) ==  0% (offset       0)     0.011     0.011   same rows: true 50% (offset  100000)     0.940     0.010   same rows: true 90% (offset  180000)     1.638     0.014   same rows: true 99% (offset  198000)     1.816     0.011   same rows: true== 1,000,000 rows, page size 20 (median of 5 runs, ms) ==  0% (offset       0)     0.011     0.010   same rows: true 50% (offset  500000)     4.665     0.012   same rows: true 90% (offset  900000)     8.048     0.010   same rows: true 99% (offset  990000)     8.549     0.011   same rows: true

Read it in three steps:

1. At 5,000 rows nobody can tell. Both take a fraction of a millisecond. This is why offset looks fine in development and in the first year.
2. Offset time grows with the page number. At one million rows, the first page is 0.011 ms and the last pages take about 8.5 ms. That is roughly 800 times slower for the same 20 rows.
3. Keyset time does not change. It is 0.010 to 0.012 ms at every position, because WHERE id > ? jumps straight to the right place in the index and reads 20 rows. The column "same rows: true" confirms that both methods returned identical rows. We changed the method, not the answer.

8 milliseconds does not sound like a problem. But this is an in-memory database with a simple index, a best case for offset. On a disk-based database with a join or a filter, the same pattern costs far more, and it burns CPU for every page of every user. Also remember crawlers and scripts: a bot that walks all pages (page=1, page=2, ... page=50000) turns your API into a full-table scan with quadratic cost. Offset pagination is a cheap way to give someone a denial of service.

The Zalando text says efficient offset processing is "hardly feasible" for very large data, or for sharded and NoSQL databases. That matches the result.

Problem 2: OFFSET shows wrong results when data changes

This one is worse, because nobody notices it in tests. A client reads page 1, then page 2, a few seconds later. If a row is added or removed in between, the rows slide, and the client gets duplicates or misses rows. Let us see it with ten posts, newest first, page size 4:

--- OFFSET: a new post arrives between page 1 and page 2page 1: [ 10, 9, 8, 7 ]page 2: [ 7, 6, 5, 4 ]  <- 7 shows AGAIN (seen on page 1)

A new post (id 11) arrived. It goes at the top, so every old row moves down by one place. OFFSET 4 now starts at 7, not 6. The client sees post 7 twice.

Now a delete:

--- OFFSET: a row is deleted between pagespage 1: [ 10, 9, 8, 7 ]page 2: [ 5, 4, 3, 2 ]  <- 6 was skipped (it slid up into page 1)

Post 9 was deleted. Everything moved up one place. OFFSET 4 now starts at 5. Post 6 is never shown. The client does not know it is missing anything. For a feed, this is an annoyance. For an export, a sync job, a billing run or an audit, it is a data loss bug.

The Zalando guidelines mention the same: offset-based pagination may create duplicates or lead to missing entries if rows are inserted or deleted between two page requests.

Now the same event with a cursor:

--- KEYSET: same eventpage 1: [ 10, 9, 8, 7 ]page 2: [ 6, 5, 4, 3 ]  <- continues from 7, nothing repeated

The second request says "give me rows with id < 7". It does not care how many rows were added above. Nothing is repeated, nothing is skipped.

Cursor (keyset) pagination: the better default

The idea is simple. Instead of "skip 40 rows", say "start after the last row I saw":

-- page 1SELECT * FROM orders ORDER BY id DESC LIMIT 20;-- the client remembers the last id it received, say 9981-- page 2SELECT * FROM orders WHERE id < 9981 ORDER BY id DESC LIMIT 20;

With an index on id, the database jumps directly to 9981 and reads 20 rows. That is the flat line in the table above.

The API wraps the position in an opaque cursor, and returns it with the page:

{ "items": [ ... ], "next": "/orders?limit=20&cursor=eyJ2Ijoi..." }

The client just follows next until it is missing. It does not do any arithmetic. Both major guidelines take this approach: Google calls it page_token and next_page_token (and says when next_page_token is empty there are no more pages), and Zalando says to prefer cursors, and that the cursor is "an opaque pointer to a page" which the client must never inspect or construct.

Why opaque?

Because a cursor is a contract you can change later. Today it holds (created_at, id). Next year you may add a new sort field, or a shard number, or switch to a different database. If clients read the cursor and build their own, you can never change it. Encode it (base64url of JSON is enough to hide the shape; sign or encrypt it if tampering matters), and document it as "opaque".

The sort must be a total order: the tie-breaker

This is the part people get wrong. A cursor says "rows after this one". For that to mean anything, every row must have a unique place in the order. If you sort by created_at only, many rows can share the same time, and "after this one" is not defined.

Let me prove it. A table of 12 rows where score has only three values (0, 1, 2), page size 5. First, the wrong way: keyset on score alone (WHERE score < last_score):

sort by score only, keyset 'score < last' loses ties:rows seen: 9 of 12

Three rows are never returned, because they had the same score as the last row of a page, and score < last skips all of them. Now the right way: sort by (score DESC, id DESC), and condition on the pair:

WHERE (score < :s) OR (score = :s AND id < :id)ORDER BY score DESC, id DESC

sort by (score DESC, id DESC), keyset on the pair:rows seen: 12 of 12

All 12. The rule: always add a unique column (normally the id) as the last sort key, in the same direction, and put both values in the cursor.

Also create an index that matches the order, so the database does not have to sort: CREATE INDEX ... ON t(score DESC, id DESC). I checked the plan with EXPLAIN QUERY PLAN:

SEARCH t USING COVERING INDEX t_score (score<?)

There is no "USE TEMP B-TREE FOR ORDER BY" line, so the database reads the rows straight from the index in the right order. (With an index on score only, SQLite added a temporary sort step. The index and the ORDER BY must agree.) In PostgreSQL and in newer SQLite you can write the condition as a row comparison, WHERE (score, id) < (:s, :id), which is shorter and easier to read.

Costs of cursors (be honest about them)

• No jump to page 7. The client can only go forward (and backward, if you also give a prev cursor). If your product truly needs page numbers, use offset and cap the depth.
• No random access for "show the middle".
• A deleted cursor row is not a problem with keyset on a value, since the condition is id < 9981 and works even if row 9981 is gone. (The Zalando text warns that an incorrect cursor implementation can break when the cursor row is deleted. A value-based keyset does not have this issue; a cursor that stores "the id of a row I will look up" does.)
• Slightly more code and the sort must be limited to indexed columns.
• A cursor is tied to one query. If the client changes the filter or sort and keeps the old cursor, the position is meaningless. Google says all other parameters must match the call that made the token. We handle this below.

A working cursor API

Here is the real server from my lab. It has 50,000 orders, three of which share each second (to test ties), and indexes that match the sort orders. The key parts:

const SORTS = { "-created_at": ["created_at"], "-total": ["total_minor"] }; // allow-list: client word -> column...const fp = createHash("sha1").update(JSON.stringify([sortKey, customer, min_total])).digest("hex").slice(0, 8);if (cursor) {  const c = dec(cursor);                       // { v: last sort value, id: last id, f: query fingerprint }  if (c.f !== fp) return problem(res, 400, "Bad Request", "cursor was made for a different query; start again without cursor");  where.push(`(${col} < ? OR (${col} = ? AND id < ?))`); args.push(c.v, c.v, c.id);}const sql = `SELECT ... FROM orders ${where} ORDER BY ${col} DESC, id DESC LIMIT ?`;const rows = db.prepare(sql).all(...args, limit + 1);   // ask for ONE EXTRA rowconst hasMore = rows.length > limit; if (hasMore) rows.pop();

Three details to notice:

1. limit + 1. We ask for one more row than the page size. If we get it, there is a next page, and we drop the extra. This avoids both a COUNT and a last empty page.
2. The column name comes from an allow-list (SORTS), never from the client's text. Putting ?sort= straight into ORDER BY is SQL injection (see Security Lesson 1). Values go in as ? parameters. The column name cannot be a parameter, so the allow-list is the only safe way.
3. A fingerprint of sort and filters lives inside the cursor, so a cursor from one query cannot be used with another.

Now call it:

$ curl -si 'localhost:4400/orders?limit=2'HTTP/1.1 200 OKcontent-type: application/jsonlink: </orders?limit=2&cursor=eyJ2Ijoi...>; rel="next"{"items":[  {"id":50000,"customer":"c99","total":{"amount":9963,"currency":"EUR"},"created_at":"2026-01-01T04:37:46.000Z"},  {"id":49999,"customer":"c98","total":{"amount":9926,"currency":"EUR"},"created_at":"2026-01-01T04:37:46.000Z"}], "next":"/orders?limit=2&cursor=eyJ2IjoiMjAyNi0wMS0wMVQwNDozNzo0Ni4wMDBaIiwiaWQiOjUwMDAwLCJmIjoiNTUxYWM0ODgifQ"}

(Line breaks added; the cursor in the example is shortened in the header for reading.) Note the two rows have the same created_at and different ids. The tie-breaker is working. The cursor is base64url of this JSON, which I decoded for you:

decoded cursor: {"v":"2026-01-01T04:37:46.000Z","id":50000,"f":"551ac488"}

The client must not depend on this, and our docs would say "opaque".

Now the safety checks:

$ curl -s 'localhost:4400/orders?limit=0'{"type":"about:blank","title":"Bad Request","status":400,"detail":"limit must be a whole number of at least 1"}$ curl -s 'localhost:4400/orders?sort=price'{"type":"about:blank","title":"Bad Request","status":400,"detail":"sort must be one of: -created_at, -total"}$ curl -s 'localhost:4400/orders?limit=1&sort=-total&cursor=<cursor made for the default sort>'{"type":"about:blank","title":"Bad Request","status":400,"detail":"cursor was made for a different query; start again without cursor"}$ curl -s 'localhost:4400/orders?limit=1&cursor=garbage'{"type":"about:blank","title":"Bad Request","status":400,"detail":"cursor is not valid"}

And limit=100000 returned 100 items: the cap works.

The final proof: a client that follows next until it ends, for three different queries:

query "": 500 pages, 50000 rows, 50000 distinct ids, 1802 msquery "sort=-total": 500 pages, 50000 rows, 50000 distinct ids, 1612 msquery "customer=c7": 5 pages, 500 rows, 500 distinct ids, 27 ms

Every row came back exactly once, even though many rows share the same created_at or the same total, in every query. (The times include HTTP and JSON for 500 requests, on one machine.)

Page objects and links

How should the response look? The Zalando guidelines propose a page object with self, first, prev, next, last (as cursors or as links) and items. They also say it is best to use full links, so the client does not build URLs. Our server returns a next field and also sends the standard HTTP Link header: Link: </orders?...>; rel="next" (defined in RFC 8288; I did not read the RFC this time, I used it from memory, so check it). Pick one style and use it everywhere. A simple, good default:

{  "items": [ ... ],  "next": "/orders?limit=20&cursor=..."}

When there is no next, you are at the end. If you need to go back, add prev. Do not make the client guess.

Microsoft's guidelines use the names $top, $skip, $maxpagesize and a nextLink field, and say that the client must follow the nextLink as is. Google uses page_size and page_token. The names differ between companies. The structure is the same: a size, a way to continue, and a link or token for the next page.

Total counts: think before you promise

Clients love "Page 3 of 25" or "1,204 results". But SELECT COUNT(*) is not free. My timings:

5,000 rows:     COUNT(*) total  0.011 ms | filtered (no index) 0.235 ms200,000 rows:   COUNT(*) total  0.137 ms | filtered (no index) 7.281 ms1,000,000 rows: COUNT(*) total  0.640 ms | filtered (no index) 33.846 ms

A simple count of the whole table is quick in SQLite, but a count with a filter that has no index reads the entire table, and 34 ms for a count on one million rows will be much slower on a busy database. Postgres, in particular, must check row visibility to count rows, so an exact count of a big table is slow. Zalando's rule is clear: avoid a total count by default, because it is expensive and clients rarely need it. If one consumer really needs it, you can offer it only on request, for example with the Prefer: return=total-count header, as they describe.

Alternatives that cost less:

• has_more / a missing next is enough for infinite scroll and "Load more".
• An approximate count ("about 1,200 results") from database statistics or a cached number.
• A capped count: count up to 1,000 and show "1,000+".

Filtering and sorting

Pagination, filtering and sorting live together. A few rules:

Filtering

• Use query parameters named after fields: ?status=paid&customer=c7&min_total=5000. Keep a simple pattern for ranges: created_after and created_before, or min_ and max_. Pick one convention for the whole API.
• Multiple values: ?status=paid,shipped (comma separated) or repeat the key. Zalando's guideline says to define the collection format for query parameters; whichever you use, document it.
• Filtering is part of the cursor's query. A new filter means a new list, so start again from the first page.
• Validate filters like any input. Unknown filter names: reject (so typos like custmer=c7 do not silently return everything) or document that they are ignored. I prefer reject, because a typo that returns everything is dangerous.
• Complex queries (status in (...) and total > 100 or ...) are where designs get stuck. Microsoft defines a small filter language ($filter=...) and Google's AIP-132 mentions a filter string. For a first version, a few explicit parameters are easier to document, index and secure than a free-form language. Zalando suggests simple query parameters for simple cases, and a JSON body (POST /orders/search) for complex ones.
• Every filter needs an index that fits your sort. Otherwise a filter is a full scan in disguise (see the filtered count above).

Sorting

• One parameter: ?sort=-created_at (a leading minus for descending) or ?sort=created_at:desc. Allow several keys with commas: ?sort=-total,created_at.
• Allow-list the sortable fields. You cannot index every column. Only the ones in the list can use cursors efficiently. Everything else is rejected with 400.
• Always add the tie-breaker yourself. The client says sort=-total; you run ORDER BY total DESC, id DESC.
• A default sort must exist (usually newest first) and be documented. A list without an order has no pages: the database may return the rows in any order, and that order can change from request to request.
• Google's AIP-132 also says a list method should support ordering by documented fields with order_by. Microsoft has $orderby. Again: different names, same job.

Other patterns you will meet

• Time-based or "since" cursors for sync. GET /events?after=<id> for clients that keep a local copy. Same idea as keyset, with a monotonically increasing id.
• Page numbers with a cap. Some products keep offset but forbid deep pages ("only the first 100 pages"). Search engines do this. It is an honest trade-off when users want page numbers.
• Streaming or export. For very large downloads (a million rows), do not page at all: offer an export job (202 Accepted, then a file URL, see lesson 5) or a streaming format (NDJSON).
• Pagination in GraphQL uses the "connections" pattern (edges, pageInfo, cursors). It is the same cursor idea (lesson 8).

Choosing between them

My default: cursor for any list that can grow or change, offset only for small admin tables. And in both cases: default limit, max limit, stable order.

Try it

1. Create a SQLite table with 100,000 rows and write the OFFSET and keyset queries from this lesson. Time the last page of each. Do your numbers show the same pattern?
2. In the anomaly example, what would happen with offset if a post were deleted from page 3 while the user is on page 1?
3. You sort by price DESC and the table has many rows with price 999. Write the ORDER BY and the cursor condition that returns every row exactly once.
4. A client sends ?sort=price;DROP TABLE orders. What happens in our server, and why?
5. Your product manager wants "Page 3 of 25" at the bottom of the product list (about 500 products). Which method do you choose? What if the list had 50 million rows?
6. Change the server so a request without limit returns 50 items and the maximum is 200.

Answers

1. Offset time should grow with the page number and keyset time should stay flat. The exact times will differ from mine, and the pattern should hold from tens of thousands of rows. If you do not see it, check that you have an index on the sort column and that you are comparing the same query shape.
2. Every row after the deleted one moves up one place. Rows that were at the start of page 4 slide into page 3's slot, which the client had already fetched, so they are skipped (the same effect as the "post 6 is missing" example, but shifted).
3. ORDER BY price DESC, id DESC and the condition (price < :p) OR (price = :p AND id < :id). The cursor holds p and id of the last row. A matching index: (price DESC, id DESC).
4. The server returns 400, because price;DROP TABLE orders is not in the allow-list. The text never reaches SQL. This is why the column comes from a fixed map and not from the request.
5. For 500 products, offset with page numbers is fine: the table is small and the product manager's feature is real. For 50 million rows, offer cursor pagination (next/prev) and a capped or approximate count, and explain why.
6. Change the default 20 to 50 and the cap Math.min(limit, 100) to Math.min(limit, 200). Document both numbers.

Interview questions

Key takeaways

• Every list endpoint gets a default limit, a maximum limit, a stable order, and a way to ask for the next page, from version one.
• Offset gets slower with depth: at one million rows the last pages took about 8 ms against 0.01 ms for keyset (SQLite in memory, your numbers will differ), and it duplicates or skips rows when data changes.
• Keyset (cursor) pagination uses WHERE key < last with an index, so cost is flat and results are stable.
• Always sort with a unique tie-breaker (usually id) and put both values in the cursor. Without it my test lost 3 of 12 rows.
• Make cursors opaque, validate them, tie them to the query, and return a next link (and optionally a Link header).
• Allow-list the sort fields and map them to column names. Never paste client text into ORDER BY.
• Avoid total counts by default. They are expensive and hard to remove once clients use them.

What's next

Next: Versioning, Compatibility and Evolving an API, Lesson 4. The first public version is the easy part. The hard part is changing it later while thousands of clients still use the old one. We will see which changes break clients and which do not, compare URL, header and date versions, and add deprecation headers. Practise now: take one list endpoint you know and write its query with a tie-breaker and the matching index.