How do you design pagination, filtering, and sorting for a collection endpoint?

Question

IT Interview Practice · Accepted Answer

Any endpoint that returns a collection needs to **limit and shape** results. Three concerns — pagination, filtering, sorting — all live in the query string.

## Pagination: offset vs cursor

**Offset (page-based)** — `?page=3&limit=20` or `?offset=40&limit=20`.
```http
GET /orders?offset=40&limit=20 HTTP/1.1
```
Simple and allows jumping to any page, but has two problems at scale: `OFFSET 100000` forces the database to scan and discard 100k rows (slow), and if rows are inserted/deleted between requests, items **shift** and you see duplicates or skips.

**Cursor (keyset)** — the client passes an opaque pointer to the last item seen.
```http
GET /orders?limit=20&cursor=eyJpZCI6MTAyMH0 HTTP/1.1
```
```http
HTTP/1.1 200 OK

{
  "data": [ /* 20 orders */ ],
  "page": { "next_cursor": "eyJpZCI6MTA0MH0", "has_more": true }
}
```
The server translates the cursor into `WHERE id > 1020 ORDER BY id LIMIT 20` — an indexed lookup that stays fast at any depth and is **stable** against inserts. The trade-off: no random access to "page 500".

```text
Offset  → small datasets, admin UIs, "jump to page N"
Cursor  → large/infinite feeds, real-time data, mobile scroll
```

## Filtering and sorting

```http
GET /orders?status=shipped&min_total=100&sort=-created_at,id HTTP/1.1
```
- **Filter** with field=value query params (`status=shipped`). Whitelist allowed fields.
- **Sort** with a `sort` param; a `-` prefix means descending (`-created_at`). Support multi-key for stable ordering.
- Always **cap `limit`** (e.g. max 100) so a client can't request a million rows.

## Why it matters

This is one of the most practical API-design questions because nearly every list endpoint hits it, and the choice has real performance consequences. The interviewer is usually probing the **offset-vs-cursor trade-off**: a candidate who reaches for offset pagination everywhere hasn't felt the pain of `OFFSET 1000000` scanning the table, or the "shifting results" bug where a user scrolling an infinite feed sees the same post twice because a new one was inserted. Cursor pagination fixes both by turning the query into an indexed keyset lookup, which is why every large-scale API (Twitter, Stripe, Slack) uses it for feeds. Strong answers also mention the operational guardrails — whitelisting filter/sort fields (to avoid unindexed queries and injection) and capping `limit` (to protect the database) — which separates someone who has run these endpoints in production from someone who has only read about them.

Pagination: offset vs cursor

Filtering and sorting

Why it matters

Related questions