API Pagination & Filtering: The Interview-Ready Design Choices

If your API serves large datasets, pagination and filtering are not optional UI conveniences — they are essential performance controls. This guide explains the trade-offs, patterns, and implementation details you should know for interviews and real projects.

---

Why pagination and filtering matter

• Protects servers and databases from large, expensive queries.
• Reduces payload size and latency for clients.
• Enables predictable UX for browsing long lists.
• With the right design, they scale as your dataset grows.

---

Pagination strategies

Two common pagination patterns: offset/limit and cursor (keyset).

1) Offset / Limit

• Example request: /products?limit=20&offset=100
• Pros: simple to implement and reason about.
• Cons: performance degrades as offset grows — the database often scans or counts many rows. Can return duplicate/skipped items if underlying data changes while paginating.

When to use: small datasets, admin UIs, or endpoints where deep paging is rare.

2) Cursor / Keyset (recommended at scale)

• Uses a stable sort key (typically an indexed column like id or created_at) and a cursor token rather than offset.
• Example request: /events?limit=50&cursor=eyJpZCI6IjEyMzQifQ==
• Backend pattern (SQL):

-- assume ordering by created_at DESC, id DESC
SELECT *
FROM events
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

• Pros: stable and fast at scale because it leverages indexes and avoids large offsets.
• Cons: slightly more complex (requires opaque cursor encoding and careful ordering). Not suitable for arbitrary jumps ("go to page 10") unless you maintain mapping.

When to use: user feeds, long timelines, any high-volume list where deep paging occurs.

---

Filtering: query param design

Design clear, consistent query parameters that make filtering intuitive and indexable.

Examples:

• Simple equality filters: /products?category=shoes&brand=nike
• Numeric ranges: /products?price_min=50&price_max=200
• ISO date ranges or bracketed style: /orders?created_at[gte]=2023-01-01&created_at[lte]=2023-01-31
• Boolean flags: /users?is_active=true
• Multi-value params: /articles?tag=react&tag=javascript (OR semantics) or tags=react,javascript (client/server agreement needed)

Best practices:

• Use predictable param names: category, price_min/price_max or created_at[gte]/[lte].
• Prefer ISO 8601 for dates.
• Support compound filters (combine multiple params) but ensure indexes support the common access patterns.
• Avoid deep, ad-hoc filtering that can't be indexed — that will degrade performance.

Indexing tip: create indexes on frequently filtered columns and consider composite indexes (e.g., (user_id, created_at)) when filters and ordering are commonly used together.

---

Response metadata you must return

Always include pagination metadata so clients can navigate and handle edge cases.

Minimal recommended fields for cursor pagination:

{
  "data": [ /* items */ ],
  "next_cursor": "eyJpZCI6IjEyMzQifQ==",  
  "has_more": true
}

For offset pagination, include page info:

{
  "data": [ /* items */ ],
  "page": 5,
  "per_page": 20,
  "total": 1234,     
  "total_pages": 62
}

Notes:

• For cursor APIs, avoid returning total when it's expensive to compute. has_more or presence/absence of next_cursor suffices.
• Provide consistent cursor encoding (opaque tokens) so clients don’t depend on internal DB fields.

---

Rules & hard limits (opinions you should defend in interviews)

• Cap the maximum limit (e.g., 100) to avoid huge payloads.
• Validate and sanitize filter params to prevent injection.
• Enforce stable ordering for cursor pagination (e.g., created_at DESC, id DESC) so results are deterministic.
• Document example queries and responses clearly in your API docs.
• Return useful HTTP cache headers where appropriate (ETag, Cache-Control).

---

Implementation tips

• Encoding cursors: pack the last row's ordering values into a small JSON object and base64-encode it (opaque to clients).
• When filtering by range + ordering, make sure the index supports the WHERE and ORDER BY pattern to avoid full table scans.
• Consider background counting or approximate counts if total is required but expensive.
• For realtime feeds, use server-generated tokens to handle deletions/insertions gracefully.

---

Example: cursor flow

1. Client requests first page: /messages?limit=50
2. Server returns 50 items and next_cursor.
3. Client requests next page: /messages?limit=50&cursor={next_cursor}
4. Server decodes cursor and uses it in the WHERE clause to fetch the next slice.

Sample response:

{
  "data": [{ "id": 501, "text": "..." }, { "id": 500, "text": "..." }],
  "next_cursor": "eyJhIjo1MDB9",
  "has_more": true
}

---

Quick checklist for interviews

• Explain the trade-offs between offset and cursor pagination.
• Show an example query and a sample JSON response with metadata.
• Describe how you’d encode and decode a cursor safely.
• Mention indexing decisions for common filters and sorts.
• State limits (max page size) and why you’d avoid exposing raw DB offsets for deep pages.

---

Pagination and filtering are foundational API design choices. With the right patterns — cursor pagination for scale, clear filter conventions, index-aware queries, capped limits, and good metadata — your API will be both efficient and predictable.

If you want, I can generate example SQL queries and cursor encoding/decoding snippets in the language of your choice (Node, Python, Go, etc.).