Implementing Cursor-Based Pagination with PostgreSQL

This page is part of the Offset vs Cursor Pagination reference under Query Patterns & Data Shaping Strategies. It walks through the full migration — from diagnosing a degrading OFFSET query to a production-ready keyset implementation with an OpenAPI contract and CI/CD guards.

When to Apply This Pattern

LIMIT … OFFSET forces PostgreSQL to materialize and discard every row before the offset on each request. The cost is linear with the offset value, so performance collapses as users reach deeper pages or as the table grows. The specific triggers that indicate you need this migration are:

If two or more of these apply, the resolution is a keyset query backed by a composite index — not query tuning or caching.

OpenAPI Contract Snippet

Declare the PageInfo schema before touching the database. This anchors the response shape for both server and client:

# openapi/components/schemas/PageInfo.yaml (OpenAPI 3.1.0)
PageInfo:
  type: object
  required:
    - has_next_page
  properties:
    next_cursor:
      type: string
      nullable: true
      description: >
        Opaque Base64-encoded JSON tuple representing the last row in the
        current page. Omitted (or null) when has_next_page is false.
      example: "eyJ2IjoxLCJpZCI6IjQ5OSIsImNyZWF0ZWRfYXQiOiIyMDI1LTA4LTAxVDEwOjAwOjAwWiJ9"
    has_next_page:
      type: boolean
      description: True when at least one more page follows this one.
    sort_field:
      type: string
      enum: [created_at, updated_at]
      description: The field the cursor is anchored to.

Enforce this contract in CI with a JSON Schema assertion — if has_next_page is false, next_cursor must be absent or null. This rule catches backend bugs before they surface in generated SDKs.

Step-by-Step Resolution

Step 1 — Baseline the existing query

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on the current paginated endpoint. A plan containing Seq Scan or Index Scan Backward with a large rows removed by filter count confirms the problem:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM events
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Save this baseline plan to .ci/pagination-baseline.json so later steps can diff against it.

Step 2 — Add a composite index

A keyset WHERE (created_at, id) < (:a, :b) predicate is only efficient when the index covers both columns in the same direction as ORDER BY. Missing or misaligned indexes cause the planner to fall back to a sequential scan, which erases all performance gains.

-- Forward pagination (newest first)
CREATE INDEX CONCURRENTLY idx_events_created_at_id_desc
  ON events (created_at DESC, id DESC);

-- Backward pagination (oldest first) — only needed if your API exposes prev_cursor
CREATE INDEX CONCURRENTLY idx_events_created_at_id_asc
  ON events (created_at ASC, id ASC);

Verify index utilization after creation:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'events'
  AND indexrelname LIKE '%created_at%';

idx_scan = 0 after the next test run means the planner is still ignoring the index — check for type mismatches between the index column and the query parameter.

Step 3 — Rewrite the query to keyset style

Replace the OFFSET clause with a row-value comparison. PostgreSQL supports tuple comparison natively, which maps directly to the composite index:

-- Forward: next page after cursor
SELECT * FROM events
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Backward: previous page before cursor
SELECT * FROM events
WHERE (created_at, id) > (:cursor_created_at, :cursor_id)
ORDER BY created_at ASC, id ASC
LIMIT 50;

Why the primary key is mandatory: sorting only on created_at produces an unstable result set whenever two rows share the same timestamp. Appending id makes the sort total-ordered and guarantees that the cursor position is unique.

Step 4 — Encode the cursor

Cursors must be opaque, versioned, and URL-safe. Clients must not parse them; the server owns the format. Encode as base64url(JSON):

TypeScript (Zod):

import { z } from 'zod';

const CursorPayload = z.object({
  v: z.literal(1),
  id: z.string(),
  created_at: z.string(), // ISO 8601
});

type Cursor = z.infer<typeof CursorPayload>;

export function encodeCursor(row: { id: string; created_at: string }): string {
  const payload: Cursor = { v: 1, id: row.id, created_at: row.created_at };
  return Buffer.from(JSON.stringify(payload)).toString('base64url');
}

export function decodeCursor(raw: string): Cursor {
  const json = Buffer.from(raw, 'base64url').toString('utf-8');
  return CursorPayload.parse(JSON.parse(json));
}

Python:

import base64, json
from dataclasses import dataclass

@dataclass
class CursorPayload:
    v: int
    id: str
    created_at: str  # ISO 8601

def encode_cursor(row: dict) -> str:
    payload = {"v": 1, "id": row["id"], "created_at": row["created_at"]}
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode().rstrip("=")

def decode_cursor(raw: str) -> CursorPayload:
    # Restore padding
    padding = 4 - len(raw) % 4
    padded = raw + "=" * (padding % 4)
    data = json.loads(base64.urlsafe_b64decode(padded))
    assert data.get("v") == 1, f"Unknown cursor version: {data.get('v')}"
    return CursorPayload(**data)

Reject cursors with an unknown v value with 400 Bad Request and an RFC 7807 problem+json body.

Step 5 — Wire into the API response

Return the encoded cursor alongside has_next_page. Fetch limit + 1 rows to determine whether a next page exists without a separate COUNT(*) query:

// Express handler (TypeScript)
app.get('/v1/events', async (req, res) => {
  const limit = Math.min(Number(req.query.limit) || 50, 200);
  const rawCursor = req.query.cursor as string | undefined;

  let whereSql = '';
  const params: unknown[] = [limit + 1];

  if (rawCursor) {
    const c = decodeCursor(rawCursor);
    whereSql = `WHERE (created_at, id) < ($2::timestamptz, $3::uuid)`;
    params.push(c.created_at, c.id);
  }

  const rows = await db.query(
    `SELECT id, created_at, payload
     FROM events
     ${whereSql}
     ORDER BY created_at DESC, id DESC
     LIMIT $1`,
    params,
  );

  const hasNext = rows.length > limit;
  const page = rows.slice(0, limit);
  const lastRow = page[page.length - 1];

  res.json({
    data: page,
    page_info: {
      has_next_page: hasNext,
      next_cursor: hasNext ? encodeCursor(lastRow) : null,
    },
  });
});

Step 6 — Client-side pagination loop

Python async iterator with retry on conflict:

import asyncio
from httpx import AsyncClient, HTTPStatusError

async def paginate_events(client: AsyncClient, limit: int = 50):
    cursor = None
    attempt = 0
    while True:
        params = {"limit": limit}
        if cursor:
            params["cursor"] = cursor
        try:
            resp = await client.get("/v1/events", params=params)
            resp.raise_for_status()
            page = resp.json()
            yield page["data"]
            info = page["page_info"]
            if not info["has_next_page"]:
                break
            cursor = info["next_cursor"]
            attempt = 0
        except HTTPStatusError as exc:
            if exc.response.status_code == 409 and attempt < 5:
                await asyncio.sleep(0.5 * (2 ** attempt))
                attempt += 1
            else:
                raise

RFC and Standard Compliance

RFC 9110 (HTTP Semantics) does not mandate a pagination mechanism, but it constrains the contract:

• Stable URI semantics: a cursor URL must return the same page regardless of concurrent inserts or deletes. Keyset pagination satisfies this; offset pagination does not.
• 400 Bad Request for malformed or unknown cursor versions (RFC 9110 §15.5.1).
• 410 Gone is appropriate when a cursor references a sort key that no longer exists due to a schema migration and cannot be decoded.

Idempotency and Caching

Cursor-based GET requests are safe and idempotent. A cursor encodes an exact position in the sort order, not a page number, so the same cursor always returns the same rows as long as the underlying data and index are unchanged. This makes cursor responses highly cacheable:

Cache-Control: max-age=60, stale-while-revalidate=30
ETag: "v1-cursor-eyJ2IjoxLCJpZCI6Ijk5OSJ9"

Do not cache responses for the first page (no cursor) beyond a short TTL — new rows appear at the top of a descending sort and stale first pages mislead clients. For deeper pages, longer TTLs are safe because older rows rarely change. This aligns with the broader statelessness and caching strategies applied across the API surface.

SDK and Codegen Downstream Effect

The OpenAPI PageInfo schema propagates into generated clients. Before vs after the keyset migration:

- // offset-based generated type
- interface PaginationParams { page: number; limit: number; }
- interface PaginationResponse { total: number; page: number; }

+ // cursor-based generated type
+ interface PaginationParams { cursor?: string; limit: number; }
+ interface PaginationResponse {
+   page_info: { has_next_page: boolean; next_cursor: string | null };
+ }

Generators that mark next_cursor as non-nullable when nullable: true is missing from the schema will emit code that crashes on the final page. Run a generator smoke-test in CI using the PageInfo schema fixture before merging spec changes.

Common Mistakes

FAQ

Related

• Offset vs Cursor Pagination — up-link: the strategy comparison and selection matrix that frames this implementation guide
• Query Patterns & Data Shaping Strategies — up-link: the parent reference covering filtering, sorting, and projection alongside pagination
• Building Efficient Multi-Column Sort Endpoints — multi-field ORDER BY patterns that interact directly with composite cursor indexes
• Handling Complex Boolean Filtering in REST APIs — combining cursor pagination with server-side filter predicates
• RFC 7807 Problem+JSON Implementation — standard error body format for 400/410 cursor rejection responses