If your API still uses OFFSET/LIMIT pagination on large tables, you are paying a hidden performance tax on every page click. In this guide, you will build a production-ready cursor pagination pattern using PostgreSQL keyset queries, covering indexes, and EXPLAIN ANALYZE so response times stay fast as data grows from thousands to millions of rows.
Why OFFSET/LIMIT Gets Slow at Scale
OFFSET/LIMIT looks simple, but PostgreSQL still has to walk and discard rows before returning the page you asked for. Page 1 can be fast, page 500 usually is not. This creates inconsistent API latency and can hurt crawl budget, user experience, and backend costs.
Keyset pagination solves this by using the last seen sort key (for example, created_at + id) as a cursor. Instead of skipping rows, PostgreSQL jumps directly to the next range.
Data Model and Index Strategy
Let us assume an events table for an activity feed API.
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
event_type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Covering index for keyset pagination + common response fields
CREATE INDEX idx_events_created_id_cover
ON events (created_at DESC, id DESC)
INCLUDE (user_id, event_type);
This index is the core optimization. The sort keys (created_at, id) match your ORDER BY, and INCLUDE columns reduce heap lookups for common list responses.
Build the Keyset Query
First Page
SELECT id, user_id, event_type, created_at
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20;
Next Pages with Cursor
Assume the last row from page 1 has cursor values:
created_at = '2026-04-15T08:43:22Z'id = 987654
SELECT id, user_id, event_type, created_at
FROM events
WHERE (created_at, id) < ('2026-04-15T08:43:22Z', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The tuple comparison keeps ordering stable and avoids duplicate or skipped rows when timestamps collide.
Encode a Safe Cursor in Your API
Use opaque cursors (not raw SQL snippets). A simple approach is Base64-encoded JSON with timestamp + id.
import base64
import json
from datetime import datetime
def encode_cursor(created_at: datetime, row_id: int) -> str:
data = {"created_at": created_at.isoformat(), "id": row_id}
return base64.urlsafe_b64encode(json.dumps(data).encode()).decode()
def decode_cursor(cursor: str) -> tuple[str, int]:
raw = base64.urlsafe_b64decode(cursor.encode()).decode()
data = json.loads(raw)
return data["created_at"], int(data["id"])
In your handler, decode the cursor, bind values as query parameters, and return a new cursor from the last row in the response.
Benchmark with EXPLAIN ANALYZE
Always verify with real plans, not assumptions.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, event_type, created_at
FROM events
WHERE (created_at, id) < ('2026-04-15T08:43:22Z', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Good signs:
- Index scan using
idx_events_created_id_cover - Low shared read blocks for warm traffic
- Stable execution time across deep pages
If plans are not optimal, check data distribution, vacuum health, and whether ORDER BY exactly matches your index definition.
Common Pitfalls and Fixes
1) Missing tie-breaker in ORDER BY
Ordering only by created_at can cause unstable pagination when multiple rows share the same timestamp. Add id as the second key.
2) Returning mutable sort keys
If your sort column can change after insert, old cursors may become inconsistent. Prefer immutable columns for cursor logic.
3) Using keyset for random page jumps
Keyset is ideal for “next/previous” navigation, feeds, logs, and timelines. If users need direct jump to page 500, keep a separate strategy for that niche workflow.
Where This Fits in a Modern Backend Stack
This pagination pattern pairs well with resilient APIs and observability-first services. If you are building robust backends, these guides complement this approach:
- Python async API clients with retries and rate limits
- Fast analytics pipeline with Polars and DuckDB
- Zero-trust internal API platform on AWS
- Transaction-safe event-driven Java service design
Implementation Checklist
- Define stable ordering keys (
created_at,id) - Create matching covering index
- Use tuple cursor predicate for next-page fetches
- Return opaque, signed or encoded cursors
- Validate with
EXPLAIN ANALYZEon production-like data
Production Hardening Tips
- Add cursor validation: reject malformed or stale cursors with a clean 400 response and a clear error code.
- Protect consistency: keep a deterministic ORDER BY contract in one shared query builder so different endpoints do not drift.
- Observe pagination health: track p50/p95 latency by page depth and alert if deeper pages start regressing.
- Load test realistically: benchmark with mixed read/write traffic, because frequent inserts can expose ordering and cache edge cases.
FAQ
Is PostgreSQL keyset pagination always better than OFFSET/LIMIT?
For large, ordered datasets and sequential navigation, yes. It is typically faster and more stable. OFFSET/LIMIT can still be fine for small tables or admin interfaces.
Can I use UUID instead of numeric ID as the tie-breaker?
You can, but performance and index size may differ. A monotonic numeric key is usually more efficient for hot pagination paths.
How do I support previous-page navigation?
Run an inverted query with > and ascending order, then reverse rows in application code before returning the response.
Does this help SEO directly?
It helps indirectly by improving page speed and crawl efficiency for listing endpoints that render server-side content. Faster response times usually improve UX and technical SEO signals.
Leave a Reply