API Pagination Patterns: Offset, Cursor, and Keyset Explained

API Pagination Patterns: Offset, Cursor, and Keyset Explained

If you've built or consumed APIs, you've hit the moment where a list endpoint returns everything at once. That works until it doesn't. Pagination is how you keep responses fast, memory low, and clients sane. Let's look at the three common patterns and when each makes sense.

Offset Pagination

The simplest approach: ?page=2&limit=20 or ?offset=40&limit=20. The server skips offset rows and returns limit rows.

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

Pros:

• Easy to implement and understand.
• Clients can jump to any page directly.
• Works fine for small datasets or static data.

Cons:

• Performance degrades on large offsets because the database still scans all skipped rows.
• If new rows are inserted or deleted between requests, you get duplicates or missing items (unstable results).

Use it for admin panels, internal tools, or when your dataset is small and mostly static.

Cursor Pagination

Cursor pagination uses an opaque token that points to a specific item. The client sends ?cursor=abc123&limit=20, and the server returns items after that cursor, plus the next cursor (if any).

{
  "data": [/* items */],
  "next_cursor": "xyz789"
}

How it works server-side:

SELECT * FROM posts WHERE id > :cursor_id ORDER BY id LIMIT 20;

The cursor is often an encoded version of the last item's sort key (e.g., Base64(id)).

Pros:

• Stable: even if new items are added, you won't see duplicates or miss items.
• Fast on large datasets because it uses indexed range queries, not offsets.

Cons:

• Clients can't jump to a specific page number (but do they really need to?).
• Slightly more complex to implement.

This is the go-to for real-time feeds, activity logs, or any high-volume data that changes often.

Keyset Pagination

Keyset pagination is a specific type of cursor where the cursor is the actual value of the sort column(s). For example, ?after_id=100&limit=20. It's similar to cursor but transparent.

SELECT * FROM posts WHERE id > 100 ORDER BY id LIMIT 20;

Pros:

• Simple to reason about and debug.
• Same performance benefits as cursor if the sort column is indexed.

Cons:

• Requires a unique, sortable key (often the primary key).
• If you sort by something non-unique (e.g., created_at), you need a tiebreaker like id to avoid missing rows.

Many APIs implement cursor as keyset under the hood; the difference is mostly in how you expose it.

Choosing the Right Pattern

Here's my rule of thumb:

• Small or static data: offset pagination is fine. Don't over-engineer.
• Large, dynamic data: use cursor or keyset. Your database will thank you.
• Need random access to pages: offset is the only option, but consider capping the offset (e.g., max 10,000) to avoid performance cliffs.

Also think about the client. Mobile apps often prefer infinite scroll, which fits cursors perfectly. Web dashboards with page numbers might want offset.

Real-World Example: Building a Simple Cursor

Let's say you have a posts table with an auto-increment id. Your endpoint could look like this (pseudo-code):

from flask import request, jsonify

@app.route('/api/posts')
def get_posts():
    limit = int(request.args.get('limit', 20))
    cursor = request.args.get('cursor')

    query = Post.query.order_by(Post.id.asc()).limit(limit + 1)
    if cursor:
        query = query.filter(Post.id > int(cursor))

    posts = query.all()
    has_more = len(posts) > limit
    posts = posts[:limit]

    next_cursor = posts[-1].id if has_more else None
    return jsonify({
        'data': [p.to_dict() for p in posts],
        'next_cursor': next_cursor
    })

Notice I fetch limit + 1 to know if there's a next page. That's a common trick.

Final Thoughts

Pagination is more than just adding limit and offset. It affects performance, consistency, and client experience. Start with offset if you're prototyping, but switch to cursor or keyset as soon as you expect real traffic. Your future self (and your API consumers) will appreciate it.

For more depth, check out the MDN guide on pagination (not exactly, but good for web APIs) or the JSON:API spec on pagination. Both are canonical references worth bookmarking.