REST API Pagination, Filtering and Sorting: One Contract

What REST API pagination, filtering and sorting really define

A list endpoint looks like three independent features: a filter parameter, a sort parameter and some way to ask for the next page. In practice they are one contract, and most pagination bugs come from treating them separately.

The filter selects the set of rows the client is asking about. The sort order turns that set into a sequence, and only a sequence can be split into pages. The page boundary is then a position in that sequence. Change the filter or the sort between requests and "page 3" points at a different place, so a client that keeps its page token but edits its query gets results that are neither correct nor reproducible.

There are two common ways to express that position. Offset pagination says "skip N rows, return the next M." Cursor pagination, usually implemented as keyset pagination, says "return rows that sort after this specific row." It encodes the sort-key values of the last item the client saw.

Both approaches depend on a total order. If two rows share the same created_at, the database may return them in either order from one query to the next, and a row can be duplicated or skipped across a page boundary. The fix is to end every sort with a unique tiebreaker, typically the primary key, such as created_at DESC, id DESC.

This matters most to backend and platform engineers whose list endpoints serve large collections or collections that change between requests. Those are the conditions where a loose contract fails first.

Why offset pagination breaks on large or changing data

Offset pagination persists because it is easy to build and easy to reason about. A client sends limit=50&offset=100, the server translates that into LIMIT 50 OFFSET 100, and any page is reachable directly. That makes "jump to page 37" trivial and maps neatly onto numbered page controls.

The cost shows up in two places.

Deep pages get slower

The database cannot jump to row 100,000. It has to produce the rows in sorted order, walk past the skipped ones, and throw them away before returning the page. An index on the sort key avoids a full sort, but the skipped index entries still have to be traversed.

Illustrative estimate. Assume a page size of 50 and assume the engine visits every skipped row:

• Offset 0 visits about 50 rows.
• Offset 10,000 visits about 10,050 rows.
• Offset 1,000,000 visits about 1,000,050 rows to return the same 50.

The work per request grows linearly with page depth, even though the response size stays the same.

Rows shift between requests

Offsets count positions, not items, so the result breaks when the collection changes between requests. Here is an insert-between-requests example, sorted newest first with a page size of 3:

1. A client fetches page 1 and receives items E, D and C.
2. Before the next request, a new item F is inserted at the top of the list.
3. The client requests offset=3. The list is now F, E, D, C, B, A, so the server returns C, B and A.

The client sees C twice.

Deletes cause the opposite problem. If D is removed between requests, every later row moves up one position. The row that lands at position 3 is never returned, and the client has no way to detect that it was skipped.

Copy-- Small collection, sorted newest first, page size 3.
CREATE TABLE items (
  id   INTEGER PRIMARY KEY,  -- higher id = newer item
  name TEXT NOT NULL
);

INSERT INTO items (id, name) VALUES
  (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

-- Request 1: page 1 (limit=3&offset=0)
SELECT name FROM items
ORDER BY id DESC
LIMIT 3 OFFSET 0;
-- Returns: E, D, C

-- Between requests, a new item is inserted at the top of the list.
INSERT INTO items (id, name) VALUES (6, 'F');

-- Request 2: page 2 (limit=3&offset=3)
-- The offset counts positions, and every position has moved down by one.
SELECT name FROM items
ORDER BY id DESC
LIMIT 3 OFFSET 3;
-- Returns: C, B, A  (C is returned twice across the two pages)

-- The engine still has to produce and discard the skipped rows.
-- At offset 1,000,000 it walks past about 1,000,000 index entries
-- before it returns these 3 rows.
SELECT name FROM items
ORDER BY id DESC
LIMIT 3 OFFSET 1000000;

Two offset-paginated queries with an insert between them, sorted newest first: item C is returned on both pages. The last query notes that the engine still walks past every skipped row before it returns a deep page.

How cursor pagination works

Cursor pagination, also called keyset pagination, doesn't count rows from the start of the table. It remembers where the client stopped. The server sorts by a deterministic key, meaning a set of columns that gives every row a unique, stable position. A timestamp alone won't work, because two rows can share the same created_at and their relative order is then undefined. Adding the primary key as a tiebreaker fixes that: ORDER BY created_at, id.

To get the next page, the server takes the key of the last row it returned and asks for rows that sort after it: WHERE (created_at, id) > (:ts, :id). If your database doesn't support row-value comparison, or its planner doesn't use the index for it, write it out as created_at > :ts OR (created_at = :ts AND id > :id). Either way, you need a composite index on (created_at, id) in the same column order. The database can then seek straight to the boundary and read forward. Page 500 costs the same as page 1, and inserts or deletes behind the boundary can't shift what comes next.

Fetch limit + 1 rows. If the extra row comes back, a next page exists. Drop it before you return the results. This tells you whether there's more without running a separate count.

For a previous-page cursor, reverse both the comparison and the sort: < (:ts, :id) with ORDER BY created_at DESC, id DESC. Then reverse the returned rows again so the client always receives them in the canonical order.

Nullable sort columns need an explicit rule. Declare NULLS FIRST or NULLS LAST, or map NULL to a sentinel value. The cursor predicate also has to handle NULL on purpose, because a plain > comparison against NULL never evaluates to true, so those rows would be skipped without any error.

Copy-- PostgreSQL. Composite index in the same column order as the sort key.
CREATE INDEX orders_created_at_id_idx ON orders (created_at, id);

-- Next page: rows strictly after the last-seen key.
-- Page size is 20; ask for 21. A 21st row means a next page exists. Drop it.
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) > (:last_ts, :last_id)
-- Expanded form: created_at > :last_ts OR (created_at = :last_ts AND id > :last_id)
ORDER BY created_at, id
LIMIT 21;

-- Previous page: flip the comparison and the sort, then re-sort outside
-- so the client still gets canonical ascending order. If 21 rows come back,
-- the extra row is the earliest one (first after re-sort). Drop that one.
SELECT id, created_at, total
FROM (
  SELECT id, created_at, total
  FROM orders
  WHERE (created_at, id) < (:first_ts, :first_id)
  ORDER BY created_at DESC, id DESC
  LIMIT 21
) AS prev_page
ORDER BY created_at, id;

-- Nullable sort column: state where NULLs sort and handle them in the predicate.
-- This branch applies when the cursor's shipped_at is NOT NULL. Without the
-- IS NULL arm, every NULL row would be skipped silently.
-- Once the cursor reaches the NULL block, the predicate is:
-- shipped_at IS NULL AND id > :last_id
SELECT id, shipped_at
FROM orders
WHERE shipped_at > :last_ts
   OR (shipped_at = :last_ts AND id > :last_id)
   OR shipped_at IS NULL
ORDER BY shipped_at NULLS LAST, id
LIMIT 21;

Keyset pagination in PostgreSQL: a composite index that matches the sort, a next-page query with a row-value comparison and limit+1, a previous-page query that reverses the order and then re-sorts the result, and a NULL-aware predicate for a nullable sort column.

Implementing the contract: cursors, filters, sorts and limits

Cursors

Make the cursor opaque to clients: a base64url-encoded JSON object holding the last row's sort-key values, the active sort, a hash of the filter set, and a format version field. Opacity lets you change the internals without breaking clients. The version field lets you decode old cursors during a rollout or reject them cleanly. If clients must not tamper with cursors, sign them with an HMAC and verify the signature on decode. When decoding fails, the signature is wrong, the version is unknown, or the filter hash doesn't match the current request, return 400 Bad Request (RFC 9110). A cursor reused with different filters would otherwise return a page that looks valid but is wrong.

Filters

Accept only filter parameters from an allowlist, and document the semantics of each one. A repeated parameter such as status=open&status=pending means OR within that field. Different fields combine with AND. Ranges use explicit bounds such as created_after, and you should state whether each bound is inclusive. Parse every value into its declared type before it reaches the query layer. Map allowlisted names to fixed column identifiers, and never interpolate client-supplied field names into SQL. Return 400 for unknown filters. Silently ignoring them makes a typo look like an empty or unfiltered result.

Sorts and limits

A parameter such as sort=-created_at,id should accept only fields that have a supporting index. Always append a unique tiebreaker so the order is total. Document a default limit and enforce a maximum. Either clamp oversized values or reject them, document which one you do, and apply that rule consistently.

Copyimport base64, binascii, hashlib, hmac, json

SECRET = b"example-cursor-signing-key-from-config"  # placeholder; load from secret storage
CURSOR_VERSION = 1

class BadRequest(Exception):
    """The web layer maps this to HTTP 400."""

def filter_hash(filters: dict) -> str:
    # Pass already-parsed, normalized filters, e.g. with multi-values sorted.
    canonical = json.dumps(filters, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode()).hexdigest()

def encode_cursor(last_row: dict, sort: list, filters: dict) -> str:
    # Sort-key values must be JSON-safe (serialize datetimes as ISO 8601 strings).
    keys = [last_row[field.lstrip("-")] for field in sort]
    payload = {"v": CURSOR_VERSION, "sort": sort, "f": filter_hash(filters), "k": keys}
    body = json.dumps(payload, separators=(",", ":")).encode()
    sig = hmac.new(SECRET, body, hashlib.sha256).digest()  # always 32 bytes
    return base64.urlsafe_b64encode(sig + body).decode().rstrip("=")

def decode_cursor(token: str, sort: list, filters: dict) -> list:
    try:
        raw = base64.urlsafe_b64decode(token + "=" * (-len(token) % 4))
    except (binascii.Error, ValueError):
        raise BadRequest("malformed cursor")
    sig, body = raw[:32], raw[32:]
    # Check the signature before parsing anything the client controls.
    expected = hmac.new(SECRET, body, hashlib.sha256).digest()
    if not hmac.compare_digest(sig, expected):
        raise BadRequest("invalid cursor")
    payload = json.loads(body)
    if not isinstance(payload, dict) or payload.get("v") != CURSOR_VERSION:
        raise BadRequest("unsupported cursor version")
    # A cursor is valid only for the sort and filter set that produced it.
    if payload.get("sort") != sort or payload.get("f") != filter_hash(filters):
        raise BadRequest("cursor does not match current sort or filters")
    return payload["k"]

A minimal Python implementation of an opaque, HMAC-signed, versioned base64url cursor that returns 400 when a cursor is malformed, tampered with, or reused with a different sort or filter set.

Response shape, navigation links and total counts

Every page should carry the same envelope: an items array and a next_cursor. When there are no more results, next_cursor is null or absent. Pick one, document it, and never use an empty string, because clients test for it in a loop condition and an empty string will often pass that test. A prev_cursor is optional. Include it only if you can serve backward pages correctly, which usually means running the query with the sort order reversed and then flipping the results. If the cursors are opaque, clients will not try to build them, and you can change the encoding later without breaking anyone.

You can also send a Link header with rel="next" (Web Linking, RFC 8288). Generic HTTP clients and crawlers can then follow pages without parsing your JSON body. Treat the header as a mirror of the body, not a replacement. RFC 9110 defines the semantics of GET and of the target URI, including its query component, but it specifies no pagination scheme. The page contract is yours to define.

Total counts are a separate feature with a separate cost. A cursor page reads limit + 1 rows through an index. An exact COUNT(*) over the same filters may have to visit every matching row, and it runs again on every request. The count also stops being accurate as soon as writes land. Make it opt-in, for example with include_total=true, or return an approximate count clearly labeled as approximate. Many interfaces only need has_more, which costs nothing because you already fetched limit + 1 rows.

CopyGET /v1/orders?status=shipped&sort=-created_at&limit=2 HTTP/1.1
Host: api.example.com

HTTP/1.1 200 OK
Content-Type: application/json
# Mirrors next_cursor from the body so generic clients can follow pages (RFC 8288)
Link: <>; rel="next"

{
  "items": [
    { "id": 9042, "status": "shipped", "created_at": "2026-10-19T08:12:00Z" },
    { "id": 9041, "status": "shipped", "created_at": "2026-10-18T17:40:00Z" }
  ],
  "next_cursor": "eyJjIjoiMjAyNi0xMC0xOCIsImlkIjo5MDQxfQ",
  "has_more": true
}

# Last page: next_cursor is null (never ""), no Link rel=next, has_more is false.
# Total is opt-in via include_total=true and labeled when approximate.
GET /v1/orders?status=shipped&sort=-created_at&limit=2&cursor=eyJjIjoiMjAyNi0wMS0wMiIsImlkIjoxMn0&include_total=true HTTP/1.1
Host: api.example.com

HTTP/1.1 200 OK
Content-Type: application/json

{
  "items": [
    { "id": 11, "status": "shipped", "created_at": "2026-01-01T09:00:00Z" }
  ],
  "next_cursor": null,
  "has_more": false,
  "total": { "value": 4800, "approximate": true }
}

A middle page and a last page from the same endpoint. Both use the same envelope: the next page's Link header mirrors next_cursor, the final page returns a null cursor, and the total count appears only when the client requests it and is labeled approximate.

When to use cursor pagination, when to avoid it, and the alternatives

No single strategy wins on every axis. The choice depends on whether clients need to jump to arbitrary pages, how deep they go, and how much the data changes while they read.

Make cursor pagination the default for feeds, sync jobs, exports and any table that grows without bound. Its cost does not depend on how deep the client goes, and inserts ahead of the cursor do not shift later pages.

Keep offset pagination for small or stable datasets where users need a "page N of M" interface, such as admin lists or reference data. Cap the maximum offset so a crawler cannot push the database into expensive deep scans. When a client hits the cap, tell it to narrow the query with filters instead of paging further.

A cursor is consistent relative to the sort key, not to a snapshot. Suppose a client sorts by updated_at. A row that gets updated mid-iteration moves past the cursor and shows up again, which is a duplicate. The reverse also happens: if a row's sort key moves to a position the client has already passed, the client never sees it. If a consumer needs every row exactly once, sort by an immutable key or use a snapshot.

Impact on cost, engineering time and testing

Every sort field you expose commits you to a matching composite index (the sort key plus the tie-breaking ID) and a cursor encoding for it. Each index adds work to every insert and update and consumes storage. An endpoint with six sortable fields pays that cost on every write, even if almost no one sorts. Arbitrary combinations of sorts and filters are worse. The query planner may choose a different strategy for each combination, so latency becomes unpredictable, and you cannot index for all of them. Richer filtering, such as OR logic, nested expressions and full-text search, widens the validation surface. It also lets any client submit an expensive query, which is a denial-of-service risk even without malice.

The cursor format is part of the API too. Clients store cursors, so changing the encoding or the fields inside it requires versioning, or a transition period in which old cursors still decode.

A constrained contract has three parts: an allowlist of sort fields, a fixed set of filter operators and a capped limit. It gives up some flexibility up front. In return, you spend less time on slow-query incidents and "missing row" bug reports, because every permitted query shape is known, indexed and tested.

Correctness tests

• Concurrent writes: Page through the whole collection while another process inserts and deletes rows. Assert that no ID appears twice and that every row present for the entire run was returned.
• Empty results: The first page has no items and no next cursor.
• Exact multiples of page size: Define whether the final full page has a next cursor followed by an empty page or no next cursor at all, and test that behavior.
• Ties in the sort key: Seed many rows with the same sort value and verify the tie-breaker keeps the order stable across page boundaries.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to rest api pagination, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.