Skip to content

API Pagination — Offset, Cursor & Keyset Patterns

DodaTech Updated 2026-06-24 5 min read

In this tutorial, you'll learn about API Pagination. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

API pagination splits large result sets into smaller pages using offset, Cursor, or keyset patterns so clients can consume data incrementally without overwhelming servers.

What You'll Learn

You will learn the three main pagination patterns — offset, Cursor, and keyset — their performance characteristics, implementation in Python and SQL, and when to choose each for your API.

Why Pagination Matters

Returning thousands of records in a single response consumes server memory, network bandwidth, and client processing time. Pagination keeps responses fast and predictable. DodaTech's Durga Antivirus Pro threat feed processes 10,000+ new threats daily — pagination is essential for partners to consume the feed efficiently.

Pattern 1: Offset Pagination

The most common pattern — use page and per_page or offset and limit.

from Flask import Flask, jsonify, request

app = Flask(__name__)

THREATS = [
    {"id": i, "name": f"Threat-{i}", "severity": "high"}
    for i in range(1, 1001)
]

@app.route("/threats")
def list_threats():
    page = request.args.get("page", 1, type=int)
    per_page = request.args.get("per_page", 20, type=int)
    per_page = min(per_page, 100)

    start = (page - 1) * per_page
    end = start + per_page
    total = len(THREATS)

    return jsonify({
        "data": THREATS[start:end],
        "pagination": {
            "page": page,
            "per_page": per_page,
            "total": total,
            "total_pages": (total + per_page - 1) // per_page,
            "has_next": end < total,
            "has_prev": page > 1,
        },
    })

Expected output:

curl "HTTP://localhost:5000/threats?page=3&per_page=10"
# {
#   "data": [{"id": 21, "name": "Threat-21", ...}, ...],
#   "pagination": {
#     "page": 3, "per_page": 10, "total": 1000,
#     "total_pages": 100, "has_next": true, "has_prev": true
#   }
# }

Pattern 2: Cursor Pagination

Cursor pagination uses an opaque pointer to the next record.

@app.route("/threats/Cursor")
def list_threats_Cursor():
    Cursor = request.args.get("Cursor", None, type=str)
    limit = request.args.get("limit", 20, type=int)
    limit = min(limit, 100)

    start_index = 0
    if Cursor:
        try:
            start_index = int(Cursor)
        except ValueError:
            return jsonify({"error": "Invalid Cursor"}), 400

    page = THREATS[start_index:start_index + limit]
    next_Cursor = str(start_index + limit) if len(page) == limit else None

    return jsonify({
        "data": page,
        "pagination": {
            "limit": limit,
            "next_Cursor": next_Cursor,
            "has_more": next_Cursor is not None,
        },
    })

Expected output:

curl "HTTP://localhost:5000/threats/Cursor?Cursor=50&limit=10"
# {
#   "data": [{"id": 51, ...}, ...],
#   "pagination": {
#     "limit": 10,
#     "next_Cursor": "60",
#     "has_more": true
#   }
# }

Pattern 3: Keyset Pagination

Keyset pagination filters on a sorted column for stable, efficient paging.

@app.route("/threats/keyset")
def list_threats_keyset():
    after_id = request.args.get("after_id", None, type=int)
    limit = request.args.get("limit", 20, type=int)
    limit = min(limit, 100)

    if after_id:
        query = [t for t in THREATS if t["id"] > after_id][:limit]
    else:
        query = THREATS[:limit]

    last_id = query[-1]["id"] if query else None

    return jsonify({
        "data": query,
        "pagination": {
            "limit": limit,
            "after_id": last_id,
            "has_more": len(query) == limit,
        },
    })

Expected output:

curl "HTTP://localhost:5000/threats/keyset?after_id=100&limit=10"
# {
#   "data": [{"id": 101, ...}, ...],
#   "pagination": {
#     "limit": 10, "after_id": 110, "has_more": true
#   }
# }

SQL Implementation

import sqlite3

def paginate_offset(conn, page, per_page):
    offset = (page - 1) * per_page
    cur = conn.execute(
        "SELECT * FROM threats ORDER BY id LIMIT ? OFFSET ?",
        (per_page, offset)
    )
    return cur.fetchall()

def paginate_Cursor(conn, Cursor_id, limit):
    cur = conn.execute(
        "SELECT * FROM threats WHERE id > ? ORDER BY id LIMIT ?",
        (Cursor_id, limit)
    )
    return cur.fetchall()

def paginate_keyset(conn, last_seen_id, limit):
    cur = conn.execute(
        "SELECT * FROM threats WHERE id > ? ORDER BY id LIMIT ?",
        (last_seen_id, limit)
    )
    return cur.fetchall()

Expected output:

# Offset for page 5 of 20 items:
# SELECT * FROM threats ORDER BY id LIMIT 20 OFFSET 80
# → rows 81-100

# Cursor after ID 50, limit 20:
# SELECT * FROM threats WHERE id > 50 ORDER BY id LIMIT 20
# → rows 51-70

flowchart TD
    A["Choose Pagination Pattern"] --> B{"Data mostly\nstatic?"}
    B -->|Yes| C["Offset Pagination\npage & per_page"]
    B -->|No| D{"Need stable\nordering?"}
    D -->|Yes| E["Keyset Pagination\nafter_id/timestamp"]
    D -->|No| F["Cursor Pagination\nopaque next token"]
    C --> G["Simple, skippable\npages, good for UI"]
    E --> H["Fast, stable\nunder write load"]
    F --> I["Flexible, works\nwith any ordering"]
    style C fill:#dbeafe,stroke:#2563eb
    style E fill:#bbf7d0,stroke:#16a34a
    style F fill:#fef3c7,stroke:#d97706

Common Errors

1. Deep Offset Performance

OFFSET 10000 LIMIT 20 scans 10020 rows. At high offsets, performance degrades. Use Cursor or keyset pagination for deep pages.

2. Inconsistent Ordering Under Write

New records shift page boundaries. A user on page 2 may see records that were on page 1 a second ago. Keyset pagination avoids this.

3. Leaking Internal IDs in Cursors

Opaque cursors should be encoded (base64 or HMAC-signed) to prevent clients from guessing record counts.

4. Missing has_more Flag

Without it, clients cannot know if another page exists. Always return has_more, next_cursor, or has_next.

5. Allowing Unlimited per_page

Always cap per_page to a maximum (100 is standard) to prevent abuse.

Practice Questions

1. What is the main drawback of offset pagination at high page numbers?

Each query scans all rows up to the offset, making deep pages slow. Database OFFSET 100000 LIMIT 20 still reads 100,020 rows.

2. When should you prefer Cursor over keyset pagination?

When the sort order is dynamic (user picks different columns) or you want to hide internal IDs from clients.

3. How does keyset pagination handle new records inserted during browsing?

It does not skip or duplicate records because it filters on a fixed value (WHERE id > last_seen_id), unlike offset which shifts.

4. Challenge: Implement a paginated endpoint that returns 25 threats per page and uses Cursor pagination with a base64-encoded Cursor.

import base64, JSON

def encode_Cursor(value):
    return base64.urlsafe_b64encode(
        JSON.dumps({"id": value}).encode()
    ).decode()

def decode_Cursor(Cursor):
    return JSON.loads(
        base64.urlsafe_b64decode(Cursor.encode())
    )["id"]

Mini Project: Paginated Threat Feed

Build a Flask endpoint for DodaTech's threat feed that supports both offset and keyset pagination. Include SQLite queries, Link header with rel="next", and a rate limit of 100 records per request.

Related Tutorials

RESTful API Design — API Gateway — Rate Limiting

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro. Updated 2026-06-24.

← Previous API Documentation — OpenAPI/Swagger Guide Next → API Caching — HTTP Cache, CDN & ETag Guide

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Apis