How to Implement Cursor-Based Pagination

What's wrong with OFFSET pagination

OFFSET N LIMIT 20 tells Postgres to scan and discard the first N rows, then return the next 20. At page 500 that's 10,000 rows thrown away - latency is O(offset), not O(1). There's also a correctness problem: if a new row is inserted while someone scrolls, every subsequent page shifts by one and the user sees duplicates or skips.

How keyset cursors work

Instead of tracking a page number, track the last row the client saw. For a created_at DESC, id DESC feed the cursor encodes the pair (created_at, id) of the final row on the previous page. The next query asks Postgres for rows that come after that boundary:

SELECT id, event_type, payload, created_at
FROM events
WHERE (created_at, id) < (%s, %s)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Postgres's tuple comparison evaluates left-to-right: created_at is compared first; id breaks ties when two events share a timestamp. Both columns need the same sort direction so the composite index covers both the WHERE and the ORDER BY.

The index that makes it O(log n)

CREATE INDEX ix_events_created_id
    ON events (created_at DESC, id DESC);

Without this, the cursor predicate is a sequential scan. With it, Postgres seeks directly to the boundary row - constant seek time regardless of how many pages deep the client has scrolled.

FastAPI implementation

import json, base64
from datetime import datetime
import psycopg2
from fastapi import FastAPI, HTTPException

app = FastAPI()
DB = "postgres://postgres@localhost:5432/app"

def _encode_cursor(created_at: datetime, id_: int) -> str:
    raw = json.dumps({"ts": created_at.isoformat(), "id": id_}).encode()
    return base64.urlsafe_b64encode(raw).decode().rstrip("=")

def _decode_cursor(cursor: str) -> tuple:
    pad = "=" * (-len(cursor) % 4)
    try:
        data = json.loads(base64.urlsafe_b64decode(cursor + pad))
        return datetime.fromisoformat(data["ts"]), int(data["id"])
    except Exception:
        raise HTTPException(status_code=400, detail="invalid cursor")

@app.get("/api/events")
def list_events(cursor: str | None = None, limit: int = 20):
    limit = max(1, min(limit, 100))
    conn = psycopg2.connect(DB)
    cur = conn.cursor()
    if cursor:
        ts, id_ = _decode_cursor(cursor)
        cur.execute(
            "SELECT id, event_type, created_at FROM events"
            " WHERE (created_at, id) < (%s, %s)"
            " ORDER BY created_at DESC, id DESC LIMIT %s",
            (ts, id_, limit),
        )
    else:
        cur.execute(
            "SELECT id, event_type, created_at FROM events"
            " ORDER BY created_at DESC, id DESC LIMIT %s",
            (limit,),
        )
    rows = cur.fetchall()
    conn.close()
    next_cursor = _encode_cursor(rows[-1][2], rows[-1][0]) if len(rows) == limit else None
    return {"events": rows, "next_cursor": next_cursor, "limit": limit}

The cursor is base64-encoded JSON - opaque to clients so you can change the internal format later without breaking callers. Invalid cursors return 400.

Tradeoffs to know

Keyset pagination has one real limitation: there is no random access. You cannot jump to "page 47 of 200" - you can only move forward from a cursor. For most infinite-scroll feeds that's fine. For UIs that need "page N of M" navigation, a common hybrid keeps offset for the first page count and switches to a cursor for subsequent pages.