How do you write efficient pagination for millions of rows?
Efficient pagination is absolutely critical when dealing with millions of rows in PostgreSQL — especially if you care about performance, index usage, and UX for APIs or UI pages.
1. OFFSET/LIMIT Pagination (a.k.a. “Skip-Limit”)
SELECT * FROM orders
ORDER BY created_at
OFFSET 10000
LIMIT 50;
Pros:
Simple to implement
Page numbers are intuitive for UIs
Cons:
Slower as OFFSET increases — PostgreSQL must scan and discard the skipped rows
Non-deterministic under concurrent updates (rows can shift across pages)
Doesn’t scale beyond 100k rows
When to use:
Small datasets (or <100 pages)
Admin dashboards or static data
Temporary/internal tooling
2. Keyset Pagination (a.k.a. Seek Method)
Description:
Use a cursor (like created_at, id) to fetch the next page.
-- First page
SELECT * FROM orders
ORDER BY created_at, id
LIMIT 50;
-- Next page (cursor = last row)
SELECT * FROM orders
WHERE (created_at, id) > ('2024-07-01 10:00:00', 'uuid-123')
ORDER BY created_at, id
LIMIT 50;
Pros:
O(1) pagination — always uses index seek
Safe under high concurrency
Deterministic (no skipping/overlaps)
Ideal for real-time apps
Cons:
Can't jump to arbitrary page (e.g., page 10)
Cursor management required
Index recommendation:
CREATE INDEX ON orders (created_at, id);
3. Hybrid Keyset + Offset (for Jumping to Specific Pages)
Description:
Use OFFSET for initial jump + keyset after that.
E.g., cache the
(created_at, id)of the first row on page 10 → use that as keyset.
Pros:
Allows page jumping
Still uses index efficiently from that point onward
Cons:
Adds complexity (requires cursor cache)
Still slow on very high page numbers if not cached
Use case:
- UIs where both page numbers and infinite scroll are required
4. Cursor-based Pagination (Encoded Cursors)
This is commonly used in APIs (GraphQL, REST with cursor tokens).
Description:
Return a base64-encoded cursor like:
{
"next_cursor": "created_at=2024-07-01T10:00:00&id=abc-123"
}
In the next query:
SELECT * FROM orders
WHERE (created_at, id) > (:created_at, :id)
ORDER BY created_at, id
LIMIT 50;
Pros:
API-friendly, stateless pagination
Easy to cache cursors
Works with dynamic filters
How to Handle UPDATEs / Concurrent Changes
| Challenge | Problem |
|---|---|
| Rows inserted/deleted during pagination | Causes "row shifting" on OFFSET pagination |
| Rows updated (e.g., created_at changed) | Might move between pages if paginating on that column |
For Large Scale Dataset pagination
| Tip | Why it Matters |
|---|---|
| Use composite indexes | (created_at, id) is better than created_at alone |
| Avoid OFFSET over 10k | degrades performance fast |
| Consider materialized views or temp tables | for heavy analytical pagination |
| Expose cursor in your API layer | makes UX smooth & stateless |
| Cache row cursors by page | hybrid offset-keyset model |
Use REPEATABLE READ isolation |
if pagination happens within a transaction to avoid data shifts |
When to Use Each Method
| Use Case | Recommended Pagination |
|---|---|
| Admin dashboard < 10k rows | OFFSET/LIMIT |
| Real-time web/mobile UI | Keyset pagination |
| Reporting with stable pages | Window function with ROW_NUMBER() |
| APIs with infinite scroll | Cursor-based (encoded cursor tokens) |
| UI with both pages & infinite scroll | Hybrid keyset + cursor caching |