Pagination, Filtering & Fields

Paginate from day one

Every endpoint that returns a collection needs a page limit before it ships. An unbounded GET /v1/links is fine for the first customer, who has forty links. It is not fine for the customer who imports forty thousand. The server reads every row, builds one JSON array in memory, and sends tens of megabytes down one connection. Latency grows with the table, memory use grows with the table, and one slow client holds a worker the whole time. Tail latency and capacity covers what that does to your p99.

The second cost is the one people miss. Adding a limit later is a breaking change. Clients that were told "you get all of it" have loops that read the first response and stop. After you add a default page of 100, those clients silently miss data, and nothing errors. So the first version of the list endpoint returns a page and says whether more exists, even if the default page size is large.

This chapter covers the control plane of Snip: managing links and reading their data through https://api.snip.ly/v1. The redirect itself is the data plane and never paginates. A short code resolves to one URL.

Offset pagination, and why it breaks

The familiar form takes a page size and a position:

GET /v1/links?limit=20&offset=40

SELECT * FROM links
WHERE account_id = :acct
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

It is easy to build and lets a UI jump to page 7. It has two defects, and both get worse with load.

The database skips by reading. OFFSET 40 does not seek to row 41. The engine walks the index, produces 40 rows, throws them away, then returns 20. Cost is O(offset + limit). Page 1 is cheap. At offset=500000 the query reads half a million index entries to return twenty. A client that crawls your whole list therefore does quadratic total work: each page costs more than the last. Even a perfect index does not help, because the skip walks the index itself. See Scaling the database for why index scans, not seeks, set the cost here.

Pages drift when data changes. The offset names a position, not a row. Say a caller reads page 1 (rows 1–20, newest first). Then someone creates a link. Every row moves down by one. Page 2, at offset 20, now begins with the row that was last on page 1, so the caller sees a duplicate. Delete a row instead and the caller skips one. A link shortener with a busy create endpoint hits this constantly. The caller cannot detect it, because each response looks valid.

Offset is still the right choice for a small, slow-changing list read by humans: an admin screen of 200 API keys, say, where "page 7 of 10" is a real feature. For anything that grows or that scripts will walk, use a cursor.

Cursor (keyset) pagination

A cursor names the last row you returned instead of a position. The next query asks for rows strictly after that row in sort order. Snip sorts by created_at descending, and many links can share a timestamp, so the sort key needs a tiebreaker: code, which is unique.

SELECT code, url, created_at
FROM links
WHERE account_id = :acct
  AND (created_at, code) < (:last_ts, :last_code)
ORDER BY created_at DESC, code DESC
LIMIT 21;  -- limit + 1, to learn whether more rows exist

The row-value comparison (created_at, code) < (…) is standard SQL and works in Postgres and MySQL. With an index on (account_id, created_at DESC, code DESC) the engine seeks straight to the boundary and reads 21 entries. Cost no longer depends on how deep you are. Page 10,000 costs what page 1 costs. Inserts at the top do not move the boundary, so no duplicates and no skips.

The client never sees (created_at, code). It sees an opaque string:

GET /v1/links?limit=2 HTTP/1.1
Host: api.snip.ly
Authorization: Bearer sk_live_…

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

{
  "data": [
    { "code": "aZ3k9Qx", "url": "https://example.com/spring", "short_url": "https://snip.ly/aZ3k9Qx",
      "created_at": "2026-10-11T14:02:09Z", "tags": ["spring-campaign"], "total_clicks": 812 },
    { "code": "Q81mTpL", "url": "https://example.com/pricing", "short_url": "https://snip.ly/Q81mTpL",
      "created_at": "2026-10-11T09:40:51Z", "tags": [], "total_clicks": 97 }
  ],
  "next_cursor": "eyJ0IjoiMjAyNi0xMC0xMVQwOTo0MDo1MVoiLCJjIjoiUTgxbVRwTCJ9",
  "has_more": true
}

The caller passes it back unchanged:

GET /v1/links?limit=2&cursor=eyJ0IjoiMjAyNi0xMC0xMVQwOTo0MDo1MVoiLCJjIjoiUTgxbVRwTCJ9

That string is base64url of {"t":"2026-10-11T09:40:51Z","c":"Q81mTpL"}. Treat the encoding as private. If clients can read it, some will build their own, and then you can never change the sort key, add a column, or switch storage engines. Document it as "opaque; do not parse". Some teams sign or encrypt it, which also stops callers from forging a cursor that points at another account's rows. Always re-apply the account_id filter on the server; never trust the cursor for authorisation.

A cursor also changes meaning when the query changes. Reject a cursor sent with a different sort or filter than the one that produced it, with a 400 problem response (see Errors & Validation), instead of returning wrong pages.

The response envelope

Where does the cursor go? Two common answers.

GitHub uses Link headers and returns bare arrays. Stripe uses a body envelope with has_more. Snip uses the body envelope with next_cursor, for one reason: an object at the top level can grow. If the response were a bare array, adding any field later would break every parser. With data inside an object, you can add warnings or as_of next to it without a version bump. (This is the same compatibility logic as in Resource Modeling.) Nothing stops you from sending both; a Link header costs a few bytes and helps generic HTTP clients.

Return next_cursor: null and has_more: false on the last page. Keep has_more even though it is derivable, because it is the field callers actually test. Get it from the LIMIT n+1 trick above: if you fetched n+1 rows, drop the extra and set the flag.

No total count by default

Callers ask for "how many links do I have?" next to every list. Resist putting total in every response. SELECT COUNT(*) over a large, filtered table is a scan, and you would pay it on every page request. It also goes stale between pages as rows change. If a UI really needs a number, offer ?include=total_estimate that returns an approximate figure from table statistics or a cached counter, and name the field so nobody mistakes it for exact. Better: a separate cheap endpoint such as GET /v1/links/count that you can cache and rate limit on its own.

Page size: a default, a ceiling, and clamping

Take limit as a query parameter. Pick a default and a maximum, and publish both. Snip uses a default of 20 and a maximum of 100.

• Default. Small enough that a casual curl prints something readable and the query is fast. Callers who want throughput raise it.
• Maximum. Set by your worst-case page cost: row width times limit, plus the time to serialize. Measure it; do not guess. 100 rows of 300 bytes is 30 KB. 10,000 rows is a spike on your tail latency.
• Oversize requests. ?limit=5000 gets clamped to 100 and served, not rejected. A clamp keeps working when you later lower the ceiling; a 400 breaks a client that was fine yesterday. The response carries the page it got, and callers who care see fewer rows than they asked for and follow next_cursor. Reject only nonsense: limit=0, limit=-3, limit=abc.

The exception: if silent clamping would hide a bug, such as a caller who expects exactly 500 rows to arrive in one response, say so in a response header (Snip-Limit-Applied: 100). That is a courtesy, not a contract.

Filtering: a whitelist, not a language

Callers will not page through ten thousand links to find the twelve tagged spring-campaign. Give them filters as plain query parameters:

GET /v1/links?tag=spring-campaign&created_after=2026-03-01T00:00:00Z&limit=50

Each filter is a field you chose, with a defined meaning, a type, and an index behind it. Snip's list is: tag (exact match, repeatable for OR), created_after and created_before (RFC 3339, half-open), and q for a prefix match on the destination domain. Anything else returns 400 with a problem response naming the unknown parameter. Silently ignoring an unknown filter is the worse failure: the caller thinks they filtered, receives everything, and deletes it.

The temptation is a general query language: ?filter=tags contains 'x' and total_clicks > 100 or …, or passing a JSON predicate through. Avoid it on a public API, for three reasons.

1. Cost is unbounded. A caller can write a predicate no index serves, and your database scans the table for each request. You have handed strangers a query planner.
2. Injection surface. A parser that builds SQL from caller input is the oldest bug there is. Even with parameters bound, field names and operators need their own whitelist.
3. It is a contract you cannot narrow. Every expression you accept today is one you must keep supporting.

Note the field total_clicks is absent from the filter list. It is eventually consistent, and filtering on it would mean an index updated on every click. Do not offer a filter you cannot keep cheap. If a product need really calls for rich queries (a reporting API, say), build it as a separate endpoint with its own per-request cost limits: a maximum number of terms, a timeout, and a bound on the time window. Expose it as an advanced option, not the default list.

Sorting

The common convention is a sort parameter with a leading minus for descending: ?sort=-created_at. Snip's default is -created_at. The set of allowed values is short and fixed: created_at, -created_at, code, -code.

Two rules.

1. Only sort on indexed columns. A sort with no matching index makes the database sort the whole result set before it applies the limit. This is the same scan problem as deep offsets, wearing a different hat. Reject other values with 400.
2. The sort key is part of the cursor. A keyset query works only when it compares against the same columns it orders by. For sort=code, the cursor holds (code); for sort=-created_at it holds (created_at, code). Always append the unique key as the last tiebreaker, or rows with equal sort values will be skipped or repeated at page boundaries. That is the most common bug in hand-rolled cursors.

Sorting by a mutable field such as total_clicks breaks the stability guarantee that makes cursors worth having: a row can move across the boundary between page requests. Do not offer it on a paginated list. Offer a "top links" report instead, computed on a snapshot.

Sparse fieldsets

A link object is small. A list of 100 of them, each with tags, metadata and counters, is not, and a dashboard that only needs code and url pays for the rest in bytes, serialization time and any join you ran to compute them. Let callers choose:

GET /v1/links?fields=code,url&tag=spring-campaign

{
  "data": [
    { "code": "aZ3k9Qx", "url": "https://example.com/spring" },
    { "code": "mR4wB7e", "url": "https://example.com/spring/signup" }
  ],
  "next_cursor": null,
  "has_more": false
}

Design notes:

• Whitelist the names. Unknown field: 400, same as filters.
• Pick a default set. With no fields, return the standard link: code, url, short_url, created_at, tags. Fields that are costly to compute, like total_clicks, can be opt-in (fields=*,total_clicks) so the common case skips the aggregate lookup. This is where sparse fields save the server work, not only bandwidth.
• Do not let it change identity. code is always present, even if omitted from fields, so callers can still correlate rows. Say so in the docs.
• Cache keys. A shared cache must key on the fields value or return the wrong shape. Normalize the order (url,code equals code,url) to avoid duplicate cache entries.

GraphQL exists largely to make this choice universal. For a REST API with a handful of list endpoints, a fields parameter on the heavy ones is enough.

The hard case: pagination for clicks

Links number in thousands per account. Clicks number in billions. GET /v1/links/{code}/clicks is the endpoint where every earlier choice gets tested: the collection is huge, grows constantly, is aggregated, and is eventually consistent (a click may take seconds to show up).

GET /v1/links/aZ3k9Qx/clicks?since=2026-10-11T00:00:00Z&until=2026-10-12T00:00:00Z&limit=100&cursor=…

Three decisions make it cheap.

A time window is required in effect. since and until are RFC 3339 and half-open (since inclusive, until exclusive). If the caller omits them, default to the last 24 hours, and cap the maximum span at, say, 31 days (return 400 beyond). The storage layer partitions by time, so a window prunes whole partitions before any row is read. That bound is what keeps a worst-case query predictable, and predictable worst cases are what capacity planning needs.

Keyset inside the window. The sort is fixed (newest first) and the cursor holds (clicked_at, click_id). No offset, ever, on this endpoint. Because the log is append-only, a cursor stays valid for the length of its window.

Eventual consistency is part of the contract. Reading page 1, then page 2 a minute later, may show clicks that were not there when page 1 was cut, but only at the head of the window, above the cursor, which a keyset cursor never revisits. Callers that must have a complete count for a closed window should wait until until is older than the documented lag (Snip says 5 minutes) before they read. State the lag in the reference and return an as_of timestamp beside data so callers can tell. That is the extra top-level field the envelope was built to carry.

For aggregates ("clicks per day last month") do not page raw rows at all. Offer GET /v1/links/{code}/clicks?group_by=day, which returns at most 31 buckets in one response. The cheapest page is the one you never make the caller fetch.

✓ Check yourself

Try these before opening the answers. Each one needs a reason, not a name.

1. A caller walks GET /v1/links?limit=100&offset=N over an account with two million links while other users create links. Name the two things that go wrong and say which one the database causes.

   Show answer

   Cost grows with every page, because the database reads and discards N rows before it returns 100. That is the database's work. Results also drift: new rows shift later pages, so the caller sees duplicates, or skips rows when others are deleted. See Offset pagination, and why it breaks.

2. Why does the cursor query order by (created_at, code) and not by created_at alone?

   Show answer

   Many links can share one timestamp. With only created_at in the key, the boundary row is ambiguous, and rows with the same timestamp get skipped or repeated at a page edge. code is unique, so it makes the order total. See Cursor pagination.

3. A client asks for limit=5000, and a teammate wants a 400. Give the case for clamping and one case where you would still say so out loud.

   Show answer

   Clamping keeps clients working when you lower the ceiling later, and the next_cursor lets them continue. Flag it with a header, or reject, only when a caller's logic depends on getting exactly that many rows in one response. See Page size.

4. Product asks for ?sort=-total_clicks on the paginated link list. What breaks, and what do you offer instead?

   Show answer

   Two things: there is no index to seek on, because the value changes with every click, and rows cross the cursor boundary between requests, so pages repeat or skip. Offer a separate top-links report computed on a snapshot. See Sorting.

5. Why does the clicks endpoint need a time window when it already has a limit?

   Show answer

   The limit bounds rows returned; the window bounds rows examined, and lets storage skip whole time partitions. Without the window, a query for a link with a billion clicks has an unbounded worst case. See The hard case: clicks.

6. Your list responses need a "total links" figure on the dashboard. How do you provide it without making every page request slower?

   Show answer

   Keep it out of the page response. Serve an approximate or cached count from a separate endpoint, or an opt-in field named as an estimate. See The response envelope.

Takeaway

• Paginate every collection from the first release; adding a limit later breaks clients without any error.
• Offset costs O(offset) and drifts under writes. Keep it for small, static, human-read lists.
• Use an opaque cursor over the full sort key plus a unique tiebreaker, so cost stays flat and pages stay stable.
• Wrap lists in an object (data, next_cursor, has_more) so you can add fields later; skip the total count by default.
• Whitelist filters, sorts and fields, each backed by an index. Clamp page size; reject unknown names.
• For huge, eventually consistent collections such as clicks, bound the work with a time window as well as a limit.

Next, what the API says when a request is wrong. Errors & Validation →

Further reading

• RFC 8288: Web Linking — the standard for the Link header and the next and prev relations.
• RFC 5988: Web Linking (obsoleted by RFC 8288) — the earlier definition, still cited by older APIs.
• MDN: Link header — syntax and examples in plain terms.
• GitHub REST API: using pagination — a production Link-header design.
• Stripe API: pagination — a production cursor design with has_more and starting_after.