Keyset Pagination

title: "Keyset Pagination — Efficient Database-Level Pagination" description: "Keyset pagination (seek method) uses WHERE filters on indexed columns to paginate, offering the best database performance for large datasets." date: 2026-06-28 lastmod: 2026-06-28 weight: 15 tags: [apis, pagination] }

Keyset pagination (also known as seek pagination) uses database-level filtering with WHERE clauses on indexed columns, providing the best performance for large datasets without OFFSET overhead.

What You'll Learn

• How keyset pagination works with SQL
• Composite keys for stable sorting
• Performance comparison with offset

Why It Matters

Keyset pagination handles millions of records efficiently because it uses indexed lookups instead of scanning and discarding rows with OFFSET.

Code Examples

# Keyset pagination (seek method)
@app.route('/users')
def list_users():
    after_id = request.args.get('after_id', type=int)
    limit = min(request.args.get('limit', 20, type=int), 100)

    if after_id:
        users = db.execute("""
            SELECT * FROM users
            WHERE id > ?
            ORDER BY id ASC
            LIMIT ?
        """, [after_id, limit])
    else:
        users = db.execute("""
            SELECT * FROM users
            ORDER BY id ASC
            LIMIT ?
        """, [limit])

    next_id = users[-1].id if len(users) == limit else None

    return jsonify({
        "data": [u.to_dict() for u in users],
        "pagination": {
            "next_id": next_id,
            "has_more": len(users) == limit
        }
    })

# Composite keyset pagination
@app.route('/orders')
def list_orders():
    after = request.args.get('after')
    limit = min(request.args.get('limit', 20, type=int), 100)

    if after:
        cursor = json.loads(base64.b64decode(after))
        orders = db.execute("""
            SELECT * FROM orders
            WHERE (created_at, id) < (%s, %s)
            ORDER BY created_at DESC, id DESC
            LIMIT %s
        """, [cursor['created_at'], cursor['id'], limit + 1])
    else:
        orders = db.execute("""
            SELECT * FROM orders
            ORDER BY created_at DESC, id DESC
            LIMIT %s
        """, [limit + 1])

-- Performance comparison: OFFSET vs Keyset
-- OFFSET (scans 100,100 rows):
SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 100000;

-- Keyset (uses index, scans 100 rows):
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 100;

Common Mistakes

1. Using Keyset Without an Index

Keyset requires an index on the filter column. No index = full table scan.

2. Non-Unique Sort Columns

Use a composite key (e.g., created_at + id) for stable pagination.

3. No Backward Pagination

Implement both > and < directions for full navigation.

4. Exposing Raw IDs

Use encoded cursors to hide database internals.

5. Handling Ties Incorrectly

When sort values are equal, include a tiebreaker (usually id).

Practice Questions

1. How does keyset pagination differ from offset?
2. Why must the sort column be indexed?
3. What is a composite keyset?
4. Why is keyset pagination faster for deep pages?
5. How do you handle equal sort values?

Answers:

1. Keyset filters with WHERE; offset scans and discards rows.
2. Without an index, the database still scans all rows.
3. Using multiple columns (e.g., created_at + id) for unique ordering.
4. It only reads the rows it returns, regardless of position.
5. Add a unique tiebreaker column (usually id) to the ORDER BY.

Challenge: Implement keyset pagination for a table with 1 million rows. Compare query performance with offset pagination for deep page numbers.

FAQ