API Design at Scale

Pagination: OFFSET is O(offset)

OFFSET n is not a seek. Nothing can jump to the n-th row, so the database produces rows 1..n, discards them, and returns the next page. Cost grows linearly with page number: the deepest page is your most expensive query, and the clients asking for it are bots that fetch everything.

The second failure is quieter. An offset is a position in a result set mutating underneath you: insert a row before the client's position and one shifts past the boundary, never returned; delete one and a row comes back twice. Silently.

Keyset pagination replaces the position with a value — the last row seen:

SELECT id, created_at, total FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)   -- omit on page 1
ORDER BY created_at DESC, id DESC
LIMIT 50;

With an index on (tenant_id, created_at DESC, id DESC) that is one seek plus 50 sequential entries: same cost on page 8,000 as on page 1, with index order matching the ORDER BY (indexing). The id tiebreaker is mandatory, or rows sharing a timestamp get skipped. You give up jump-to-page and total counts.

Encode the cursor as opaque base64: once clients hand-build one, its internals are your contract.

Versioning: version reluctantly

Path versioning usually wins: it survives proxies, CDNs, curl and log search. Header versioning needs the caching layer keyed on it or a v2 client gets a v1 body.

That matters far less than the discipline: most changes should be additive, so most APIs should have one version. Breaking means removing a field, renaming anything, changing a type, tightening validation (max length 500 to 100), changing a default, changing the status code for an existing condition, or making an optional field required. An added optional field with a behaviour-preserving default is safe; an added response field only for clients that ignore unknown keys; an added enum value breaks any exhaustive switch.

Every version you ship is one you maintain: two code paths, two test matrices, two sets of bugs. Cut v2 only when a change cannot be additive.

Mutations: PUT is free, POST is not

PUT and DELETE are idempotent by definition — setting a resource twice leaves the same state. POST creates something new each time, so a retry after a lost response creates a duplicate order. Give every POST an Idempotency-Key: a client UUID the server stores against the response for 24 hours and replays. Same key, different body is 422; a key in flight gets 409. Mechanics in idempotency.

Errors: a shape, not a sentence

{ "error": {
  "code": "insufficient_funds",
  "message": "Card declined.",
  "retryable": false,
  "details": [{ "field": "amount", "issue": "exceeds_balance" }],
  "request_id": "req_01HK3M7QF"
} }

code is a stable machine string, the only part clients branch on; message is for humans; request_id ties a screenshot to your logs. Never leak a stack trace, SQL fragment or internal hostname — map exceptions onto a fixed public code set. The status carries the bit clients cannot guess: retry or don't.

Rate limits the client can see

A bare 429 tells a client it lost, not how to stop losing. Send Retry-After: 30 plus RateLimit-Limit: 1000, RateLimit-Remaining: 4 and RateLimit-Reset: 27 on every response, so clients self-regulate instead of learning the limit by failing. The cost is reporting remaining budget cheaply: a shared counter needs coordination per request, so most systems use per-node buckets of limit / N — cheap, wrong under skew.

Bulk endpoints and partial failure

Ninety-seven of a hundred items succeed. 200 lies, 500 lies harder, and all-or-nothing needs a transaction you cannot afford. Return 200 with a per-item array:

{ "results": [
  { "index": 0, "status": "ok",    "id": "ord_9f2" },
  { "index": 1, "status": "error", "error": { "code": "invalid_sku", "retryable": false } }
] }

Echo the index, reuse the error shape per item, and mark retryability so the client resubmits three items, not a hundred. Put the idempotency key on the batch, and cap size at 100 to 1,000 so a request fits a bounded latency budget.

Chattiness and the N+1 boundary

A client fetches 50 orders, then one request per order for the customer: at a 40ms round trip, two seconds of serialised network. Field expansion (?expand=customer,items) costs one round trip but multiplies response shapes and fragments cache keys. Batch fetch (GET /customers?ids=a,b,c) stays cacheable per id but pushes orchestration onto the client. GraphQL gives the exact graph the client wants — and hands it query cost.

GraphQL's danger is one request nesting deeply and fanning out to millions of resolver calls: a denial of service in your own query language. Defences in increasing strength — max depth (say 10), static cost analysis against a per-query budget, per-caller complexity quotas, persisted queries where only pre-registered hashes run. Batch resolvers too, or N+1 just moved into the database.

Long-running operations

Never hold a connection open for a four-minute export. Proxies time out at 30 to 60 seconds (load-balancing), the client cannot tell a slow success from a dead socket, and its retry doubles the work. Return 202 Accepted with Location: /exports/exp_123 and a pending-status body; the client polls it, paced by Retry-After, while the job sits on a durable queue (queues-and-logs). Take an idempotency key on submit, or a retry starts two exports.

What to take away

• OFFSET is O(offset) and skips or duplicates rows under writes; keyset is O(1) and stable.
• Keep cursors opaque, or their internals become your contract.
• Additive is free; removing, renaming, retyping, tightening and requiring break clients.
• POST needs an idempotency key; PUT and DELETE get one free.
• Errors need a stable machine code and a status class separating retryable from fatal.
• Surface limits with 429 plus headers, per-item results for batches, 202 for slow work.