Pagination strategies for backend APIs

Why pagination matters

A /orders endpoint without pagination is a bug. It works for the first customer with 50 orders and breaks for the customer with 50,000.

Pick a strategy before you ship the first version.

Offset pagination (?page=3&size=20)

• Easy to implement
• Easy for clients to consume
• Easy for users to understand ("page 5 of 200")
• Slow at scale — OFFSET 100000 makes Postgres scan and discard 100,000 rows before returning anything
• Inconsistent if data changes between page loads (new rows shift everything)

Fine for: admin tables, small datasets, any list under ~10,000 rows total.

Cursor pagination (?cursor=eyJpZCI6MTIzfQ)

• Stable across data changes
• Fast at any scale — uses an indexed WHERE id > cursor clause
• No "jump to page 50" — clients can only go forward (and backward, with extra work)
• Slightly more complex to implement

Fine for: any API that might return thousands of rows, infinite-scroll feeds, anywhere offset would get slow.

Keyset pagination (?after_id=12345)

Same as cursor but the cursor is exposed as a plain ID instead of an opaque token. Easier to debug, slightly less flexible.

We use keyset pagination most of the time and call it "cursor pagination" in the docs.

The real performance number

A query SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000 on a 1M-row table takes ~200ms on Postgres. The same query as WHERE id > 100000 LIMIT 20 takes <2ms. Both with the same index.

That is a 100x speedup for the page 5000 user. They are rare, but the slow path also runs when bots scrape your API.

Response envelope

{
  "data": [...],
  "next_cursor": "eyJpZCI6MTIzfQ",
  "has_more": true
}

• Always include has_more — the client should not have to count
• Do not include total_count unless you really need it; COUNT(*) is slow on large tables

What we tell clients

Pick cursor pagination as the default. Use offset only for small datasets where the UX needs "page X of Y." Mixing both in the same API is fine — /orders can be cursor while /audit-log is offset.

Pick early, stick with it.