Pagination, Filtering & Sorting Design

Concept#

The beginner framing: any endpoint returning a list of things (GET /orders) eventually needs a way to return them in manageable chunks rather than the entire table at once, pagination is how a client asks for "page 3" or "the next 20 after this one."

Offset pagination, simple, but with a real consistency bug#

GET /orders?offset=20&limit=20
-- SQL: SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 20

Offset pagination is the intuitive first design, skip N rows, take the next M. It's simple to implement and lets a client jump directly to an arbitrary page (offset=200). The genuine bug: OFFSET counts rows from the current state of the table at query time, not from a fixed snapshot, if a row earlier in the sort order is deleted (or inserted) between the client's request for page 1 and page 2, every row after that point shifts position by one. Concretely: the client fetches offset=0..19 (rows 1-20), a row at position 5 gets deleted by someone else, and the client's next request for offset=20..39 now actually returns rows 21-40 of the ORIGINAL ordering, row 21 was silently skipped, never shown to the client at all. This isn't a rare race condition; it's the guaranteed, deterministic outcome of offset semantics under any concurrent write to the underlying data, which is common for any real, live table.

Cursor (keyset) pagination, anchored to a value, not a position#

GET /orders?after=2026-07-15T10:30:00Z_ord_8f9e1c&limit=20
-- SQL: SELECT * FROM orders WHERE created_at > '2026-07-15T10:30:00Z'
        OR (created_at = '2026-07-15T10:30:00Z' AND id > 'ord_8f9e1c')
        ORDER BY created_at, id LIMIT 20

Cursor pagination anchors each page request to the actual last-seen value(s) from the previous page (after=<timestamp>_<id>), not a row COUNT. Deleting a row anywhere in the table has zero effect on this query, "give me everything after this specific value" is unaffected by what happened to rows before that value, because the query never counts positions at all, it filters directly on the sort key. The compound cursor (created_at AND id) specifically handles ties: if two orders share the exact same created_at, id acts as a tiebreaker to guarantee a strict, stable, gapless ordering, using created_at alone could non-deterministically split or duplicate rows that share a timestamp across pages.

The real tradeoff: cursor pagination cannot jump to an arbitrary page ("show me page 47") the way offset can, since there's no way to compute "the value 940 rows in" without actually walking through them, it's fundamentally a linked-list-style "next" mechanism, not a random-access one. This is why most real-world infinite-scroll / API-consumer pagination uses cursors (sequential access is the actual access pattern), while offset survives in admin-panel-style UIs that genuinely need direct page-number jumping, usually on smaller or less frequently-mutated datasets where the consistency bug matters less in practice.

Filter grammar, a small, composable query language, not one param per field#

GET /orders?filter=status:eq:shipped,total:gte:100,createdAt:lt:2026-01-01
-- vs. ad-hoc: GET /orders?status=shipped&totalMin=100&createdBefore=2026-01-01

A structured field:operator:value filter grammar scales to arbitrary fields and operators (eq, gte, lt, in, contains) without inventing a new bespoke query parameter name for every field/comparison combination, totalMin/totalMax/createdBefore/createdAfter is exactly the kind of ad-hoc parameter sprawl that becomes unmaintainable past a handful of filterable fields, while field:operator:value is a single, uniform pattern the API can validate and document generically once.

Try It#

Predict the outcome before checking the solution.

// Client is on offset-paginated page 1 (offset=0, limit=20), showing orders 1-20.
// BEFORE the client requests page 2, another user cancels (deletes) order #7.
// Client then requests: GET /orders?offset=20&limit=20

Which specific order does the client end up never seeing across both requests, and why?

Implement It Yourself#

Build a minimal cursor-pagination function over an in-memory array, the actual mechanism, without needing a real database:

function paginateByCursor(items, { after, limit }) {
  // items assumed pre-sorted by (createdAt, id), the same order the query would use
  let startIndex = 0;
 
  if (after) {
    const [afterCreatedAt, afterId] = after.split("_");
    startIndex = items.findIndex(
      (item) => item.createdAt > afterCreatedAt || (item.createdAt === afterCreatedAt && item.id > afterId)
    );
    if (startIndex === -1) startIndex = items.length

Confirmed by running both scenarios: even with ord_1 deleted between requests, paginateByCursor correctly returns ord_3 and ord_4 on page 2, because the cursor anchors to the VALUE "2026-01-02_ord_2", not a row count, the deletion of an earlier row has zero effect on what "after this value" means.

Under the Hood#

Cursor pagination's underlying query pattern (WHERE created_at > ? ORDER BY created_at LIMIT ?) is exactly the kind of query that benefits from a compound index on (created_at, id), letting the database satisfy both the filter and the sort directly from the index, without a separate sort step (the databases domain covers indexing strategy in depth). Whether a paginated list response is safe to cache at all connects directly to API Caching, offset-based pages are much harder to cache correctly BECAUSE their contents shift under concurrent writes, the same underlying issue as the consistency bug covered here. And an unbounded page limit is the same class of resource-exhaustion concern Rate Limiting covers more generally.

Common Mistakes#

1. Using offset pagination for a large, actively-mutating dataset#

GET /feed?offset=5000&limit=20 // ❌ on a live, high-write feed, the skip/duplicate bug is guaranteed eventually

The larger and more actively-written the dataset, the more likely and more frequent the offset consistency bug becomes, this isn't a theoretical edge case for any feed-like, high-churn resource; it's a near-certainty over enough pagination requests.

2. Using only a single-field cursor when the sort field has ties#

// Cursor on createdAt ALONE, when multiple orders share the same timestamp:
"WHERE created_at > ?" // ❌ can non-deterministically split or duplicate same-timestamp rows across pages

Without a tiebreaker (a unique field like id appended to the cursor and the sort), rows sharing the exact same value on the primary sort field can be inconsistently included/excluded across page boundaries, always use a compound cursor when the primary sort field isn't guaranteed unique.

3. Inventing a new query parameter for every filterable field and operator combination#

?statusEquals=shipped&totalGreaterThan=100&totalLessThan=500&createdAfter=... // ❌ doesn't scale

This grows linearly (and messily) with every new filterable field × operator combination, a structured field:operator:value grammar handles arbitrary combinations with one consistent, documentable pattern instead.

Best Practices#

• Default to cursor/keyset pagination for any endpoint backing infinite-scroll, feeds, or APIs consumed programmatically, it avoids the offset skip/duplicate bug entirely and typically performs better at deep pages (no need to scan-and-discard N skipped rows).
• Use offset pagination only where genuine random-page-jump access is a real product requirement (an admin table with page numbers), and be aware of its consistency limitations on frequently-mutated data.
• Always use a compound cursor (sort field + unique tiebreaker) when the primary sort field isn't guaranteed unique, to prevent split/duplicate rows across pages.
• Design filters as a structured, composable grammar (field:operator:value) rather than accumulating ad-hoc per-field query parameters.
• Document and enforce a maximum limit, an unbounded client-supplied page size is a real resource-exhaustion risk on both the database and the response payload.

Performance Tips#

• Offset pagination gets progressively SLOWER at deeper pages, OFFSET 10000 still requires the database to scan and discard the first 10,000 matching rows before returning the next batch; cursor pagination's WHERE created_at > ? with a proper index jumps directly to the right position regardless of how "deep" the page is.
• A compound index matching the cursor's exact sort/filter columns (as covered in the linked indexing topic) is what makes cursor pagination's performance advantage real in practice, without it, the database still has to do comparable work under the hood.