API Pagination, Versioning and Idempotency

1The core idea#

Three separate problems that share one root cause: the real world is messy, and a naive API assumes it is not.

Pagination: "how do I return a large result set without killing the server or the client."

• You never return a million rows in one response.
• 1 KB per row times a million rows is a 1 GB payload: it blows up server memory, saturates the client's connection, and times out long before it finishes.
• You hand rows back in chunks of 20 to 100 and give the client a way to ask for the next chunk.

Versioning: "how do I change my API without breaking the apps already using it."

• Once a client ships, you do not control when it updates.
• A mobile app from eight months ago is still out there hitting your endpoints.
• On Android, roughly 20 to 30 percent of your install base can lag a release by months.
• Your new field or renamed response has to not break it.

Idempotency: "what happens when the same request arrives twice."

• Networks drop responses. Clients retry.
• If a retry charges a customer a second time, you have a very bad day.
• An idempotent operation can run many times and leave the system in the same state as running it once.

2How it works#

Pagination#

Two main styles, and the choice matters.

Offset pagination uses limit and offset.

• Page three at 20 per page is LIMIT 20 OFFSET 40. Simple, jumps to any page, and every engineer gets it on sight.
• The catch: the database still scans and discards the skipped rows.
• At OFFSET 100000 it scans 100,000 rows just to throw them away, so a query that takes 2 ms on page one can take 400 ms on page 5,000.
• It also drifts: insert a row at the top while the user pages and every item shifts down by one, so you see duplicates across page boundaries or skip rows entirely.

Cursor pagination (also called keyset) uses a pointer to the last item you saw.

• Instead of "skip 40," you say "give me items after this id."
• The query becomes WHERE id > :cursor ORDER BY id LIMIT 20, which rides an index and stays flat at roughly 2 ms whether you are on page 1 or page 50,000, because it seeks straight to the cursor instead of counting from the start.
• The cursor is usually an opaque base64 token so clients do not build logic on its contents.
• The cost: you cannot jump to "page 47." You only go forward or back from where you are.

Versioning#

Three common places to put the version.

• URI path: /v1/orders, /v2/orders. Loud, obvious, trivial to route and cache. Most public APIs use this, including Stripe-style path prefixes and Twilio.
• Header: Accept: application/vnd.myapi.v2+json. Keeps URLs clean, but it is invisible in a browser and easy to forget.
• Query param: /orders?version=2. Easy, but it muddies caching and feels hacky.

The deeper principle is additive change.

• Adding an optional field is safe: an old client just ignores it.
• Removing a field, renaming one, or changing a type (an amount that was an integer in cents becoming a decimal string) is a breaking change and needs a new version.
• Good teams version rarely and deprecate on a clear timeline.

Idempotency#

The standard pattern is an idempotency key.

• The client generates a unique key (a UUID) for an operation and sends it in a header such as Idempotency-Key.
• The server stores the key with the result of the first request.
• If the same key shows up again, the server returns the stored result instead of running the work twice.

Here is the flow for a retried payment:

Which methods are already idempotent by design.

• GET, PUT, and DELETE are meant to be safe to repeat.
• PUT sets a resource to a state, so running it twice lands the same state.
• POST is the dangerous one because it usually creates something, and that is exactly where you bolt on idempotency keys.

Here is how the pieces sit in a request path:

3A worked example: a feed API at 10M rows#

Make it concrete with real numbers, because interviewers want to see you reason about scale, not recite definitions.

The setup.

• You are building the timeline API for a social app.
• The posts table has 10,000,000 rows, each post about 1 KB serialized.
• You page at 20 posts per request, and a user scrolls deep, say 2,000 pages in.

With offset pagination.

• Page 2,000 is LIMIT 20 OFFSET 40000. Postgres walks the index and counts off 40,000 rows before returning 20.
• Response time climbs with depth: roughly 2 ms near the top, 150 to 400 ms this deep.
• While the user scrolls, 50 new posts land at the top. Every one shifts the offset window down, so the user re-sees posts and silently skips others. That feels broken.

With cursor pagination.

• You sort by a stable key. Post id alone is not enough if two posts can share a created_at, so the cursor is a composite: (created_at, id).
• The query is WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 20.
• With a composite index on (created_at, id), the database seeks straight to the cursor position in roughly 2 ms, flat, whether page 1 or page 2,000.
• New posts at the top do not disturb the window because the cursor anchors to a fixed point in the ordering.
• You hand the client an opaque token like eyJ0cyI6MTcwMDAwMDAwMCwiaWQiOjQyfQ (base64 of the created_at and id) so nobody builds logic on the raw values.

3Trade-offs#

Pagination: offset vs cursor#

My rule:

• Offset for small admin tables and dashboards where someone wants page numbers and the data fits in a few thousand rows.
• Cursor for anything that scrolls, anything large, and anything where correctness under concurrent writes matters: a social feed, an audit log.

Versioning: how aggressively to version#

The trade is agility versus maintenance cost.

• Every live version is code you keep running, test, and secure.
• I have seen a company stuck supporting v1 for three years because one enterprise customer never upgraded, which meant every bug fix had to be written and tested twice.

The clean path:

• Version only for genuinely breaking changes.
• Push everything else through additive optional fields behind feature flags.
• Ship v2, return a Sunset header on v1 with a date about 6 months out, email the top integrators, then watch v1 traffic drain before you pull it.

Idempotency: where to store keys and for how long#

• The key store needs fast lookups and a TTL. Redis is the common pick.
• 24 hours is a typical window: retries happen in minutes, not weeks, so holding keys past a day just wastes memory.
• The sharp edge is the race: two identical requests arrive at the same instant before either finishes.
• You handle it with a lock or a unique constraint on the key, so the second request waits or returns a "request in progress" signal rather than both doing the work.
• Get this wrong and you quietly reintroduce the exact double-charge you were trying to prevent.

4Where it is used#

• Payment systems lean hard on idempotency keys. Charging a card is the textbook case where a double submit is unacceptable, so the key is required on every create-charge call. Stripe has made the Idempotency-Key header part of its API for years.
• Social feeds and timelines use cursor pagination. An infinite scroll that re-shows posts or skips them as new content lands feels broken, and keyset pagination fixes both.
• Large public platforms version in the URI path and run multiple versions side by side with hard sunset dates, giving third-party developers months of lead time.
• Internal microservices often skip heavy versioning and coordinate deploys instead, but they still need idempotency because retries between services are constant and automatic, usually baked into the RPC client as a default.

When to reach for them: the moment your API has external clients, large datasets, or money on the line. A throwaway internal tool with ten rows does not need any of this, so do not over-engineer it.

5Common pitfalls and gotchas#

• Deep offset pagination in production. OFFSET 500000 looks innocent in dev with 50 rows. In prod it is a half-second query that pages someone at 3 a.m. when it starts eating a database core under load. Switch to cursor before it bites.
• Leaky cursors. If your cursor is a plain integer id, clients will start crafting their own, and now that is part of your contract forever. Encode it opaquely as base64 so you stay free to change the underlying key.
• Mislabeling additive vs breaking. Teams either version for every tiny change and drown in maintenance, or they rename a field in place and break every old client silently. Adding an optional field is safe; removing or renaming one is not.
• Idempotency key that ignores the request body. If the same key arrives with a different payload, that is a client bug, and returning the old cached result hides it. Store a hash of the body with the key and reject mismatches with a 422.
• No TTL on keys. Your key store grows forever and eventually falls over on memory. Always expire them; 24 hours is a sane default.
• Assuming retries are rare. They are not. Mobile networks, load balancer timeouts (a gateway that cuts off at 30 seconds while the backend keeps working), and client libraries with automatic retry all generate duplicates constantly. Design for it as the normal case.

6How it shows up in interviews#

Interviewers rarely ask "explain pagination" directly. It surfaces inside a larger design: "design a payment service," "design a news feed," "design a URL shortener's analytics API." They are watching whether you raise these concerns without prompting.

Weak vs strong answers#

On pagination.

Weak: "I'd return the results with LIMIT and OFFSET, and the client passes a page number."

• A dev-with-50-rows answer. It never mentions scale, never notices OFFSET 40000 scans 40,000 rows, and ships a feed that re-shows posts under concurrent inserts.

Strong: "I'd use cursor pagination for the feed. Offset works for a small admin table, but at 10M rows OFFSET 40000 scans and discards 40,000 rows, so latency climbs from 2 ms to a few hundred ms as you scroll, and new posts at the top shift the window so users re-see or skip items. With a composite cursor on created_at and id, the query seeks straight to the position and stays flat at about 2 ms. I'd hand back an opaque base64 cursor so clients can't depend on the raw key."

• Names both styles, quantifies why offset degrades, picks cursor for the right reason (concurrent inserts, not just speed), and mentions opaque encoding.

On versioning.

Weak: "I'd put v1 in the URL."

• True, and empty. It stops at the easy part.

Strong: "Version in the URI path for public clients. But I'd version only on breaking changes, renames, type changes, removed fields, and push everything else through additive optional fields that old clients ignore. When I do cut v2, I set a Sunset header on v1 with a date about 6 months out and warn the big integrators, because old mobile builds never upgrade on my schedule."

• Distinguishes additive from breaking, explains you version only when forced, and talks about deprecation timelines and sunset headers.

On idempotency.

Weak: "Before creating the charge, I'd check if it already exists."

• Check by what, exactly? This hand-waves the hard part and still double-charges under a race.

Strong: "The client sends an Idempotency-Key UUID on the create-charge POST. I store the key plus a hash of the body in Redis with a 24 hour TTL. On a retry with the same key I return the stored result, no second charge. The race is two identical requests arriving before the first finishes, so I guard with a unique constraint or lock on the key: the second one waits or gets a request-in-progress response. GET, PUT, and DELETE are already idempotent, so I only add the key on POST."

• Introduces the key, walks the retry-after-timeout scenario, handles the concurrent-duplicate race with a lock or unique constraint, and notes which methods need the key at all. That last detail signals you have shipped this.

The meta-signal: you treat the network as unreliable and clients as out of your control, and you design defensively because of it. Say that out loud.

For a full end-to-end walkthrough, see the related System Design Question on designing a payment service, where idempotency and retries take center stage.