API Pagination, Filtering & Sorting: Cursor vs Offset

Q: How do I paginate when sorting by a non-unique column such as price?

Add a unique column as the final sort key — ORDER BY price_cents ASC, id ASC — and encode both values in the cursor. The comparison then becomes (price_cents, id) (last_price, last_id).

Q: Is there a standard for filter syntax?

Not a single one. JSON:API reserves a filter parameter family, OData defines $filter, and Google's API Improvement Proposals define a filtering grammar. Most APIs do well with simple named parameters, consistently applied and strictly validated.

Pagination, Filtering, and Sorting

Almost every API has list endpoints — orders, users, events, search results — and they are where APIs most often get slow, inconsistent, or unsafe as data grows. An endpoint that returned 40 rows in development returns 4 million in production. Deep pages take seconds because the database counts past every skipped row. Items appear twice or vanish between pages while data changes. A typo in a filter parameter silently returns everything. A sort parameter pasted into SQL becomes an injection hole.

Getting list endpoints right is mostly a matter of a few conventions applied consistently: cursor-based pagination with a stable sort, a small allowlist of filters and sort fields backed by indexes, sensible limits, and the same request and response shapes on every list endpoint in the API.

TL;DR

Prefer cursor (keyset) pagination: it stays fast at any depth and does not skip or duplicate items when data changes.
Make cursors opaque; clients pass back the value you gave them and never construct their own.
Always sort by a unique tie-breaker (such as id) after the main sort key, or pages can overlap.
Accept only allowlisted filters and sort fields, validate their types, and reject unknown parameters instead of ignoring them.
Enforce a default and maximum page size.
Avoid exact total counts on large collections; return has_more or an estimate instead.

Quick Example

A cursor-paginated request and response:

The cursor is base64url-encoded JSON holding the sort key and ID of the last row, {"c":"2026-10-19T13:58:40Z","i":"ord_9e7"}. The next request repeats the same filters with cursor=…, and the server turns it into a keyset query:

With the index, page 10,000 costs the same as page 1.

Core Concepts

Offset Versus Cursor Pagination

Offset pagination misbehaves under change. If a new order arrives while a client reads page 1, every row shifts down by one, and page 2 begins with the last row of page 1 again. Deletions shift rows up, so one is never seen.

Stable Ordering

A keyset cursor is a position in a total order. If two rows share the same created_at, their relative order is undefined, and a page boundary between them can repeat or lose rows. Add a unique column as the final sort key — ORDER BY created_at DESC, id DESC — and include it in the cursor.

Row-value comparisons such as (created_at, id) < ($2, $3) require all sort columns to run in the same direction. Mixed directions need the expanded form: created_at < $2 OR (created_at = $2 AND id > $3).

Opaque Cursors

Encode the position — sort key values and the tie-breaker — and hand clients a string they cannot interpret. Opaque cursors let you change the encoding later, keep internals private, and prevent clients from building cursors that skip validation. Decode them as untrusted input: validate the structure, reject anything malformed with 400, and always apply authorization filters independently of the cursor. Sign cursors with an HMAC if you want to detect tampering.

A cursor only makes sense with the filters and sort it was created for. Either encode them in the cursor and ignore conflicting parameters, or require the client to repeat them and reject a mismatch.

Filtering

Keep filter syntax simple and consistent across endpoints:

Rules that prevent most problems:

Allowlist the filterable fields, and back each common combination with an index.
Validate types and values — enums, dates in RFC 3339, numeric ranges — and return 400 with a clear message on invalid input.
Reject unknown parameters. A typo such as stauts=paid should fail loudly, not return every order.
Apply authorization first: tenant and ownership filters come from the authenticated identity, never from parameters.

When clients need rich boolean queries, offer a dedicated search endpoint or search engine rather than exposing a general query language over the primary database. Standards such as OData $filter and Google's AIP-160 filtering grammar exist for APIs that need more.

Sorting

A common convention is a sort parameter with comma-separated fields and a leading minus for descending order: sort=-created_at,name. Document the default order, allow only fields that have supporting indexes, and map public names to columns in code. ORDER BY cannot use bound parameters, so a sort field copied into SQL is an injection vector:

Page Size and Totals

Default and maximum: for example, 25 by default and 100 at most. Either reject larger values or clamp them and report the size actually used.
Totals: COUNT(*) over a large filtered set can cost more than the page itself. Options include returning only has_more, an estimated count, a capped count ("1,000+"), or a separate count endpoint that clients call only when needed.
Links: some APIs return navigation as an HTTP Link header (rel="next", RFC 8288) instead of, or as well as, fields in the body. Pick one approach for the whole API.

GraphQL APIs use the connection model — first, after, edges, pageInfo — which is cursor pagination with a standard shape; see GraphQL Pagination.

Best Practices

Use One Shape Everywhere

Every list endpoint should accept the same pagination, filtering, and sorting parameters and return the same envelope. Clients can then share one pagination helper, and generated SDKs can offer auto-pagination.

Default to Cursors for New Endpoints

Collections that are small today grow. Starting with cursor pagination avoids a breaking change later; offset pagination remains fine for genuinely bounded data.

Design Indexes With the Endpoint

For each supported filter and sort combination, ensure an index whose leading columns are the equality filters, followed by the sort columns in order. Limit combinations rather than promising to index all of them. See Database Indexing.

Offer Exports for Bulk Access

Clients that page through millions of rows want a snapshot. An asynchronous export endpoint is easier on your database and gives clients a consistent copy.

Common Mistakes

Sorting Without a Tie-Breaker

Deep Offsets on Large Tables

Unbounded Page Sizes

Silently Ignoring Unknown Filters

FAQ

Should I use offset or cursor pagination?

Cursor pagination for most APIs, especially large or frequently changing collections, feeds, and synchronization. Offset pagination is acceptable for small, bounded collections and internal tools that need page numbers.

How do I show "page 3 of 10" with cursors?

You cannot directly, because cursors do not know their position in the whole set. If users need numbered pages, use offset pagination for that view with a cap on depth, or show an approximate count. Many interfaces work well with "Load more" or infinite scrolling instead.

Should cursors expire?

They can. Cursors that encode only a position work indefinitely, though results reflect current data. If cursors refer to server-side state such as a snapshot or a search session, give them an expiry and return a clear error when one is used too late.

How do I paginate when sorting by a non-unique column such as price?

Add a unique column as the final sort key — ORDER BY price_cents ASC, id ASC — and encode both values in the cursor. The comparison then becomes (price_cents, id) > (last_price, last_id).

Is there a standard for filter syntax?

Not a single one. JSON:API reserves a filter parameter family, OData defines $filter, and Google's API Improvement Proposals define a filtering grammar. Most APIs do well with simple named parameters, consistently applied and strictly validated.