Pagination, filtering and batching

Why it matters

Reading one record is easy. Reading "all the orders for this customer" is where latency, database load and correctness problems live: unbounded lists time out, offset pages skip or repeat items while the data changes underneath, and naive clients make one request per item. Every list endpoint is a scaling decision disguised as a convenience, and interviewers probe it because the right answer requires knowing how the database executes the query.

The idea

Never return an unbounded list. Every collection endpoint has a page size with a default and a maximum, a way to get the next page, and a bounded set of filters the storage can serve efficiently.

Offset versus cursor

Offset pagination (?limit=50&offset=5000) is simple and lets clients jump to any page. The database must scan and discard the skipped rows, so page 1,000 costs a thousand times page 1, and if rows are inserted or deleted between requests the client skips or repeats items.

Cursor pagination (also keyset or seek) returns an opaque token encoding the position of the last item, typically its sort key and id: ?limit=50&after=eyJ.... The next query is WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 50, which an index serves in constant time regardless of depth, and which is stable under concurrent changes. The cost is that clients cannot jump to page 37, and the cursor is tied to the sort order.

	Offset	Cursor (keyset)
Deep pages	Slow: scans skipped rows	Constant: index seek
Changing data	Skips or repeats items	Stable
Jump to page N	Yes	No; next/previous only
Total count	Natural to show	Expensive; estimate or omit
Implementation	Trivial	Needs a unique, indexed sort key; opaque token encoding
Use for	Small admin tables, search result pages with few pages	Feeds, logs, exports, anything large or live

Filtering and sorting that stay fast

Expose filters as query parameters with documented semantics (status=open, created_after=), and only on fields the storage indexes; a filter on an unindexed column is a full scan dressed as a feature. Sort options should be a fixed list, each backed by an index that also contains the pagination tie-breaker. Reject unknown parameters with 400 rather than ignoring them, so a typo does not silently return everything. For complex ad-hoc querying, back the endpoint with a search engine rather than bending the primary database.

Batching: fewer, larger requests

The N+1 problem appears when a client lists 50 orders and then fetches each customer separately. Fix it on either side: embed or include related data (?include=customer), or offer a bulk endpoint (GET /customers?ids=1,2,3 or POST /customers:batchGet) so one request returns many. On the server, a data loader that coalesces lookups within a request does the same for GraphQL resolvers. For writes, a bulk endpoint that accepts up to a bounded number of items and returns per-item results keeps imports from becoming thousands of calls.

Exports and very large results

When a client genuinely needs everything, do not page through millions of rows over HTTP. Offer an asynchronous export: POST /exports returns 202 and a job id; the client polls or receives a webhook; the result is a file in object storage behind a signed URL. It moves the work off the request path and off the primary database.

Trade-offs

Cursors are correct and fast and give up random access and counts. Embedding related data cuts round trips and bloats responses and couples endpoints. Bulk endpoints reduce calls and need careful limits and partial-failure semantics. Each choice is a question of who pays: the client in round trips, the server in work, or the design in complexity.

In an interview

Name the pagination style with its reason: "Cursor-based, keyed on (created_at, id), because the feed is large and live and offset pages would skip items under writes. Default 20, maximum 100. Filters on status and date only, both indexed." Then pre-empt the N+1: "The list embeds the fields the screen needs, and there is a batch endpoint for the rest." For reports: "An asynchronous export job, not a 10,000-page list."