API Pagination Patterns: Choosing the Right Approach

API Pagination Patterns: Choosing the Right Approach

Pagination is one of those things you don't think about until your API grows. Then suddenly your endpoints are returning thousands of records, mobile clients are crawling, and the database is sweating. I've built and consumed enough APIs to know that picking the right pagination pattern early saves a lot of pain. Let's walk through the common options, their trade-offs, and when to use each.

Offset Pagination (The Classic)

This is the simplest: ?page=2&limit=20 or ?offset=40&limit=20. The server skips a number of rows and returns the next set.

SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 40;

Pros:

• Easy to implement, works everywhere.
• Clients can jump to any page directly.
• Stateless, no extra server logic.

Cons:

• Performance degrades with large offsets because the database still scans and discards rows.
• Inconsistent results if new items are inserted between requests (you might see duplicates or miss rows).

I use offset pagination for small datasets or internal admin tools where consistency isn't critical. For public APIs, I'd think twice.

Cursor-Based Pagination (Keyset)

Instead of a page number, the client sends a cursor that points to the last item from the previous page. The server returns rows after that cursor.

SELECT * FROM items WHERE id > :last_id ORDER BY id LIMIT 20;

Response includes a next_cursor field:

{
  "data": [ ... ],
  "next_cursor": "eyJpZCI6MTAwfQ"
}

The cursor is often an opaque string (base64 encoded JSON) or just the last ID.

Pros:

• Scales well: no matter how deep you paginate, the query uses an index.
• Stable results even if new items are added, because you're always moving forward from a fixed point.

Cons:

• Can't jump to a specific page number.
• Requires a unique, sortable column (usually the primary key).
• Slightly more complex to implement.

This is my go-to for most read-heavy APIs, especially feeds, timelines, and any list that grows over time.

Keyset with Composite Cursors

When you need to sort by something other than the ID, like created_at, you need a composite cursor. The cursor encodes both the sort key and the ID to break ties.

SELECT * FROM items 
WHERE (created_at, id) > (:last_created_at, :last_id) 
ORDER BY created_at, id LIMIT 20;

Make sure you have a composite index on (created_at, id) for this to be fast. I've seen people forget that and wonder why their query is slow.

Page-Based with a Token (Offset + Cursor Hybrid)

Some APIs return a token that encodes the offset, but also include a checksum or snapshot ID to detect changes. This is rare and often over-engineered. I'd avoid it unless you have a specific need.

Limit/Offset with a Stable Sort

If you must use offset pagination, at least make it consistent by ordering by a unique column and using a transaction snapshot (if your database supports it). But honestly, that's a band-aid.

Time-Based Pagination

For time-series data, you can paginate by time windows: ?before=2024-01-01T00:00:00Z. It's a form of keyset where the cursor is a timestamp.

SELECT * FROM events WHERE occurred_at < :cursor ORDER BY occurred_at DESC LIMIT 20;

Works well for logs, metrics, or any append-only data. Just be careful with duplicate timestamps; add a secondary sort key like ID.

What About GraphQL?

GraphQL often uses Relay-style connections with cursors. That's essentially cursor pagination with a standardized format. If you're building a GraphQL API, you'll likely adopt that pattern anyway.

Choosing What to Return

Regardless of the pattern, your response should include metadata:

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

For offset, you might return total_count, page, and page_size. But for cursors, total_count is often expensive to compute; I skip it unless the client really needs it.

Practical Advice

• Start with cursor-based pagination for any API that might grow. It's not that much harder.
• Always sort by a unique column to avoid missing or duplicating items.
• Use LIMIT + 1 to check if there's a next page without a separate count query.
• Document your pagination clearly; clients need to know how to navigate.

I've seen too many APIs suffer from offset pagination at scale. The shift to cursors is like moving from a bicycle to a car: you don't realize how much friction you had until you switch. For new projects, I default to cursor-based. For existing ones, I gradually migrate endpoints that show performance issues.

Pagination is a small part of the API, but it affects user experience and server load every day. Choose wisely, and your future self will thank you.