Cursor Vs Offset

title: "Cursor vs Offset Pagination — When to Use Each Strategy" description: "Cursor pagination offers stable results for real-time data while offset pagination provides simplicity and page jumping for smaller, static datasets." date: 2026-06-28 lastmod: 2026-06-28 weight: 16 tags: [apis, pagination] }

Cursor and offset pagination serve different needs: offset for simple, page-based navigation on static data; cursor for stable, real-time results on frequently changing data.

What You'll Learn

• Tradeoffs between cursor and offset pagination
• Performance characteristics of each
• Choosing based on your use case

Why It Matters

Choosing the wrong pagination strategy causes phantom reads, duplicate items, or poor performance at scale.

Comparison

Code Examples

# When to use each: decision function
def paginate(strategy, data_type, query):
    if strategy == "offset":
        return offset_pagination(query)
    elif strategy == "cursor":
        return cursor_pagination(query)

# Offset: simple admin panel
@app.route('/admin/users')
def admin_users():
    page = request.args.get('page', 1)
    offset = (page - 1) * 20
    users = db.execute("SELECT * FROM users OFFSET ? LIMIT 20", [offset])
    return jsonify(users)

# Cursor: real-time activity feed
@app.route('/feed')
def user_feed():
    cursor = request.args.get('cursor')
    query = "SELECT * FROM events WHERE id < ? ORDER BY id DESC LIMIT 20"
    events = db.execute(query, [decode_cursor(cursor)]) if cursor else \
             db.execute("SELECT * FROM events ORDER BY id DESC LIMIT 20")
    return jsonify(events)

Common Mistakes

1. Using Offset for Real-Time Feeds

New items shift page boundaries, causing duplicate or missed items.

2. Using Cursor When Page Jumping Is Required

If users need to jump to page 100, offset is the only option.

3. Not Documenting Pagination Tradeoffs

Tell developers which strategy you use and why.

4. Switching Strategies Between Endpoints

Use the same strategy across all list endpoints for consistency.

5. Ignoring Performance Characteristics

Offset becomes slow at page 10,000+. Cursor stays fast at any depth.

Practice Questions

1. When should you choose offset over cursor?
2. What is the phantom read problem with offset?
3. Why is cursor pagination faster for deep pages?
4. Can you offer both strategies?
5. How do you handle total counts with cursor pagination?

Answers:

1. For admin panels, small datasets, or when users need to jump to specific pages.
2. New items shift page boundaries, so the same item appears on multiple pages.
3. Cursor uses indexed WHERE clauses; offset scans and discards rows.
4. Yes, document which to use for each use case.
5. Estimate from query statistics or accept the tradeoff.

Challenge: Design a pagination strategy for a photo gallery app. Users need to jump to page 5 for old photos but also view a real-time home feed. Use different strategies for each.

FAQ