API Pagination Patterns: Offset, Cursor, and Keyset

Every API that returns lists needs pagination. Without it, a request for “all users” could return millions of rows, crushing your database and timing out the client. But pagination has tradeoffs—and choosing wrong can hurt performance or cause data inconsistencies.

Offset Pagination

The classic approach. Simple to implement, simple to understand:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
@app.get("/users")
def list_users(limit: int = 20, offset: int = 0):
    users = db.query(
        "SELECT * FROM users ORDER BY id LIMIT %s OFFSET %s",
        (limit, offset)
    )
    total = db.query("SELECT COUNT(*) FROM users")[0][0]
    
    return {
        "data": users,
        "pagination": {
            "limit": limit,
            "offset": offset,
            "total": total
        }
    }

Pros:

Easy to implement
Clients can jump to any page
Total count is available

Cons:

Slow at scale: OFFSET 1000000 makes the database scan 1,000,000 rows before returning 20
Inconsistent: If rows are inserted/deleted between requests, you’ll skip or duplicate items
COUNT is expensive: On large tables, COUNT(*) can be slow

Use when: Small datasets, admin interfaces, or when random page access is required.

Cursor Pagination

Instead of “skip N rows,” use “rows after X”:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import base64
import json

def encode_cursor(user_id, created_at):
    data = {"id": user_id, "created_at": created_at.isoformat()}
    return base64.urlsafe_b64encode(json.dumps(data).encode()).decode()

def decode_cursor(cursor):
    data = json.loads(base64.urlsafe_b64decode(cursor))
    return data["id"], data["created_at"]

@app.get("/users")
def list_users(limit: int = 20, after: str = None):
    if after:
        last_id, last_created = decode_cursor(after)
        users = db.query("""
            SELECT * FROM users 
            WHERE (created_at, id) > (%s, %s)
            ORDER BY created_at, id 
            LIMIT %s
        """, (last_created, last_id, limit + 1))  # Fetch one extra to check if more exist
    else:
        users = db.query("""
            SELECT * FROM users 
            ORDER BY created_at, id 
            LIMIT %s
        """, (limit + 1,))
    
    has_more = len(users) > limit
    users = users[:limit]
    
    return {
        "data": users,
        "pagination": {
            "next_cursor": encode_cursor(users[-1].id, users[-1].created_at) if has_more else None,
            "has_more": has_more
        }
    }

Pros:

Fast at any depth: No scanning past rows—database seeks directly to position
Consistent: Insertions/deletions don’t cause skips or duplicates
Scalable: Performance is constant regardless of page number

Cons:

Can’t jump to arbitrary pages (page 50 requires fetching pages 1-49’s cursors)
No total count (expensive to compute anyway)
Cursor must be opaque to clients

Use when: Large datasets, infinite scroll, real-time feeds.

Keyset Pagination

Cursor pagination without the encoding—just use the sort key directly:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
@app.get("/users")
def list_users(
    limit: int = 20, 
    created_after: datetime = None,
    id_after: int = None
):
    if created_after and id_after:
        users = db.query("""
            SELECT * FROM users 
            WHERE (created_at, id) > (%s, %s)
            ORDER BY created_at, id 
            LIMIT %s
        """, (created_after, id_after, limit))
    else:
        users = db.query("""
            SELECT * FROM users 
            ORDER BY created_at, id 
            LIMIT %s
        """, (limit,))
    
    return {"data": users}

Pros:

Same performance benefits as cursor pagination
Transparent—clients see the actual values
Clients can construct their own “cursors”

Cons:

Exposes internal sort keys
More complex for multi-column sorts
Clients might manipulate values incorrectly

Use when: You trust your clients (internal APIs, server-to-server).

The Index Matters

Pagination performance depends on indexes:

1
2
3
4
5
6
7
8
-- For offset pagination (still slow, but less slow)
CREATE INDEX idx_users_id ON users(id);

-- For cursor/keyset pagination (fast seeks)
CREATE INDEX idx_users_created_id ON users(created_at, id);

-- For filtered + paginated queries
CREATE INDEX idx_users_status_created_id ON users(status, created_at, id);

The query must match the index order. If you’re sorting by created_at, id, your WHERE clause must use the same columns in the same order.

Handling Deletes and Inserts

Offset pagination breaks with concurrent modifications:

Cursor pagination handles this gracefully:

For real-time feeds: Use cursor pagination. The cursor is a stable reference point regardless of what happens to other rows.

Response Format

Be consistent and include everything clients need:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
{
  "data": [...],
  "pagination": {
    "limit": 20,
    "has_more": true,
    "next_cursor": "eyJpZCI6MTIzfQ==",
    "prev_cursor": "eyJpZCI6MTAyfQ=="
  },
  "links": {
    "next": "/users?limit=20&after=eyJpZCI6MTIzfQ==",
    "prev": "/users?limit=20&before=eyJpZCI6MTAyfQ=="
  }
}

Including full URLs in links makes clients simpler—they just follow the link.

Quick Reference

Pattern	Random Access	Performance at Scale	Consistency	Best For
Offset	✅ Yes	❌ Slow	❌ Inconsistent	Small datasets, admin UIs
Cursor	❌ No	✅ Fast	✅ Consistent	Large datasets, feeds
Keyset	❌ No	✅ Fast	✅ Consistent	Internal APIs

Default recommendation: Start with cursor pagination. It handles scale and consistency from day one. Only use offset if you truly need random page access—and be prepared for the performance cost.

Offset Pagination#

Cursor Pagination#

Keyset Pagination#

The Index Matters#

Handling Deletes and Inserts#

Response Format#

Quick Reference#

📬 Get the Newsletter

Offset Pagination

Cursor Pagination

Keyset Pagination

The Index Matters

Handling Deletes and Inserts

Response Format

Quick Reference