Implementing cursor-based pagination with PostgreSQL

High-throughput API endpoints degrade predictably under concurrent load when pagination relies on row offsets. This guide provides a diagnostic-to-implementation workflow for backend engineers and platform teams migrating to keyset (cursor) pagination in PostgreSQL. It covers query degradation triage, index alignment enforcement, deterministic cursor encoding, and CI/CD contract validation to eliminate duplicate records, skipped pages, and timeout regressions.

Symptom Diagnosis & Query Degradation

Offset-based pagination (LIMIT x OFFSET y) forces PostgreSQL to materialize and discard y rows on every request. Under high concurrency, this manifests as:

• Slow query logs showing Seq Scan or Index Scan Backward with rows=100000+ but actual rows=50
• Duplicate or missing records across adjacent pages during concurrent INSERT/DELETE operations
• API timeout spikes correlating with dataset growth beyond 100k rows

Diagnostic Steps

1. Capture baseline execution plans:

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

2. Audit CI query timeout thresholds. If statement_timeout > 500ms triggers on paginated routes, initiate migration to Offset vs Cursor Pagination for high-concurrency workloads.
3. Validate OpenAPI page_info object responses against schema. Missing has_next_page or malformed next_cursor strings indicate incomplete contract enforcement.

CI Requirement: Fail builds if pg_stat_statements reports mean execution time > 200ms for paginated endpoints, or if EXPLAIN shows Seq Scan on tables > 50k rows.

Root Cause Analysis: Index Alignment & Sort Stability

Cursor pagination fails silently when sort columns lack deterministic tiebreakers or composite indexes. PostgreSQL requires strict total ordering to guarantee cursor continuity.

Index Scan Failures

Missing composite indexes force sequential scans. Verify index utilization:

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

If idx_scan = 0 on a high-traffic table, the planner is ignoring the index due to type mismatches, collation differences, or missing primary key tiebreakers.

Non-Deterministic Ordering

Sorting solely on created_at (or any non-unique column) produces unstable result sets when multiple rows share identical timestamps. This breaks cursor continuity and causes duplicate/skipped records.

Architectural Enforcement: All paginated endpoints must declare a deterministic sort chain: (sort_field, primary_key). This aligns with broader architectural decisions documented in Query Patterns & Data Shaping Strategies.

CI Requirement: Integrate a schema migration linter that rejects CREATE INDEX statements lacking a UNIQUE or PRIMARY KEY column as the final sort key.

Resolution Workflow: Composite Indexes & Keyset Pagination

1. Composite Index DDL

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

-- Backward pagination (oldest first)
CREATE INDEX idx_events_created_at_id_asc ON events (created_at ASC, id ASC);

2. Keyset Query Pattern

Replace OFFSET with row-value comparison. PostgreSQL natively supports tuple comparison:

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

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

3. Cursor Encoding & Boundary Handling

Cursors must be opaque, versioned, and URL-safe. Encode as Base64(JSON):

// Cursor payload structure
interface CursorPayload {
 v: 1;
 sort: 'created_at';
 id: string;
 created_at: string; // ISO 8601
}

• Forward/Backward Navigation: Decode cursor, inject into WHERE clause, reverse ORDER BY for backward traversal.
• Termination: If result.length < limit, set has_next_page: false and omit next_cursor.
• Backward Compatibility: Version cursors (v: 1). Reject unknown versions with 400 Bad Request.

CI Requirement: Unit tests must verify cursor serialization/deserialization round-trips and validate that WHERE (a, b) < (x, y) matches PostgreSQL tuple comparison semantics.

Client Generation & Contract Enforcement

Automate SDK generation to enforce strict cursor parsing and error handling.

OpenAPI 3.1 Contract

components:
 schemas:
 PageInfo:
 type: object
 required: [has_next_page]
 properties:
 next_cursor:
 type: string
 nullable: true
 description: Opaque Base64-encoded JSON tuple. Omitted or null on final page.
 has_next_page:
 type: boolean
 sort_fields:
 type: string
 enum: [created_at, updated_at]

Mismatch Case & Validation

If the spec defines next_cursor as integer but PostgreSQL returns Base64, generated parsers fail. Enforce contract validation via JSON Schema tests:

{
 "if": { "properties": { "has_next_page": { "const": false } } },
 "then": { "properties": { "next_cursor": { "const": null } } }
}

Client Implementations

TypeScript SDK (Zod + Encoder):

import { z } from 'zod';

const CursorSchema = z.object({ v: z.literal(1), id: z.string(), created_at: z.string() });

export function decodeCursor(raw: string) {
 return CursorSchema.parse(JSON.parse(Buffer.from(raw, 'base64').toString()));
}

export function encodeCursor(payload: z.infer<typeof CursorSchema>) {
 return Buffer.from(JSON.stringify(payload)).toString('base64');
}

Python Async Iterator (Retry on Mutation Conflict):

async def paginate_events(client, cursor=None, limit=50):
 while True:
 try:
 resp = await client.get("/events", params={"cursor": cursor, "limit": limit})
 resp.raise_for_status()
 yield resp.json()["data"]
 if not resp.json()["page_info"]["has_next_page"]:
 break
 cursor = resp.json()["page_info"]["next_cursor"]
 except HTTPStatusError as e:
 if e.response.status_code == 409:
 await asyncio.sleep(0.5 * (2 ** attempt)) # Exponential backoff
 continue
 raise

cURL/HTTPie Template:

curl -G "https://api.example.com/v1/events" \
 --data-urlencode "cursor=eyJpZCI6IjEyMyIsImNyZWF0ZWRfYXQiOiIyMDIzLTEwLTAxVDAwOjAwOjAwWiIsInYiOjF9" \
 --data-urlencode "limit=50"

CI/CD Guardrails & Regression Testing

Prevent pagination regressions by embedding query plan validation and synthetic dataset mutation tests into deployment pipelines.

GitHub Actions Pipeline Snippet

jobs:
  pagination-regression:
    runs-on: ubuntu-latest
    services:
  postgres:
    image: postgres:15
    env: { POSTGRES_DB: test, POSTGRES_PASSWORD: postgres }
    steps:
      - uses: actions/checkout@v4
      - name: Seed Synthetic Dataset
        run: |
          psql -h localhost -U postgres -d test -c "INSERT INTO events (id, created_at)
          SELECT generate_series(1, 500000), NOW() - (random() * interval '365 days');"
      - name: Query Plan Diff
        run: |
          EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM events WHERE (created_at, id) < (NOW(), '500000') ORDER BY created_at DESC, id DESC LIMIT 50;
          # Compare against baseline plan stored in .ci/pagination-baseline.json
          # Fail if Seq Scan appears or cost increases > 20%
      - name: Cursor Continuity Validation
        run: |
          # Fetch pages 1-5, verify no duplicate IDs, verify monotonic sort
          python tests/pagination_continuity.py

CI Requirement: Block merges if synthetic dataset tests detect duplicate IDs across page boundaries, or if EXPLAIN output deviates from the stored baseline plan without explicit pg_hint_plan overrides.

Common Pitfalls & Mitigation

FAQ