Why Pagination Design Matters
Returning all results for a query — all 10 million orders, all 500K users — is impractical. Pagination divides results into pages. There are three main approaches: offset-based (page 1, page 2…), cursor-based (next page token), and keyset-based. Each has different performance, consistency, and UX trade-offs. Getting pagination wrong at scale means either slow queries, inconsistent results, or broken “next page” after inserts. Every production API needs to get this right.
Approach 1: Offset Pagination
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 200; -- page 11, 20 items per page
# API: GET /orders?page=11&per_page=20
# Response: { items: [...], total: 8543, page: 11, per_page: 20 }
Pros: simple, supports random page access (jump to page 50), easy to display “Page 11 of 427”.
Cons: OFFSET n scans and discards n rows — O(n) cost grows with page number. At page 5000 with per_page=20: the DB scans 100K rows to discard them. Also: if a row is inserted while the user is on page 2, page 3 shifts — duplicate or skipped items.
Approach 2: Cursor Pagination (Recommended for APIs)
Instead of a page number, use an opaque cursor that encodes the position in the result set.
# First page
GET /orders?limit=20
Response: {
items: [...20 orders...],
next_cursor: "eyJpZCI6IjEyMyIsInRzIjoiMjAyNi0wNC0xMiJ9" -- base64({"id":"123","ts":"2026-04-12"})
}
# Next page
GET /orders?limit=20&cursor=eyJpZCI6IjEyMyIsInRzIjoiMjAyNi0wNC0xMiJ9
def get_orders(cursor=None, limit=20):
if cursor:
decoded = json.loads(base64.b64decode(cursor))
last_id = decoded['id']
last_ts = decoded['ts']
rows = db.query('''
SELECT * FROM orders
WHERE (created_at, order_id) < (:last_ts, :last_id)
ORDER BY created_at DESC, order_id DESC
LIMIT :limit
''', last_ts=last_ts, last_id=last_id, limit=limit)
else:
rows = db.query('''
SELECT * FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT :limit
''', limit=limit)
next_cursor = None
if len(rows) == limit:
last = rows[-1]
next_cursor = base64.b64encode(json.dumps({
'id': str(last.order_id),
'ts': last.created_at.isoformat()
}).encode()).decode()
return {'items': rows, 'next_cursor': next_cursor}
Pros: O(1) performance regardless of position — WHERE clause uses an index seek. Stable: insertions/deletions don’t cause skips.
Cons: no random access to arbitrary pages; only forward pagination (some implementations add prev_cursor).
Approach 3: Keyset Pagination
Similar to cursor but uses explicit sort keys in the query rather than an opaque token. More transparent but exposes internal sort fields in the API.
GET /orders?after_id=123&after_ts=2026-04-12T10:00:00Z&limit=20
SELECT * FROM orders
WHERE (created_at, order_id) < ('2026-04-12T10:00:00Z', 123)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
Total Count: The OFFSET Compromise
Cursor pagination can’t easily provide a total count (“showing 201-220 of 8,543”). Options:
- Approximate count: COUNT(*) OVER() in a window function (fast for small-medium tables)
- Cached count: store count in a summary table, updated asynchronously
- Estimated count: PostgreSQL’s pg_class.reltuples is approximate but instant
- Accept no total: Twitter, Instagram, LinkedIn infinite scroll doesn’t show totals
Index Requirements
-- Required for cursor pagination on (created_at DESC, order_id DESC) CREATE INDEX idx_orders_cursor ON orders(created_at DESC, order_id DESC); -- The WHERE (created_at, order_id) < (:ts, :id) uses this index efficiently -- Without it: full table scan on every page request
Key Design Decisions
- Cursor pagination for high-volume APIs — O(1) regardless of depth; stable under inserts/deletes
- Opaque cursor token — encode sort keys as base64 JSON; clients treat it as opaque and can’t manipulate it
- Composite sort key (timestamp + id) — timestamp alone isn’t unique; add id for stable, deterministic ordering
- next_cursor=null signals last page — cleaner than hasMore boolean + separate cursor field
- Offset for admin dashboards — where random page access and total count matter more than performance
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”What is cursor-based pagination and why is it better than offset pagination?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Cursor pagination uses an opaque token encoding the position in the result set, rather than a page number. It avoids the O(n) cost of OFFSET — instead of scanning and discarding n rows, the WHERE clause uses an indexed seek directly to the cursor position. At page 5000 × 20 = 100K rows skipped, OFFSET is catastrophically slow; cursor pagination is the same speed regardless of depth. Cursor pagination is also stable: if rows are inserted while the user pages through results, no items are skipped or duplicated.”}},{“@type”:”Question”,”name”:”How do you design a cursor token for pagination?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Encode the sort key(s) of the last item on the current page as a base64 JSON string. For results sorted by (created_at DESC, order_id DESC), the cursor encodes {"ts": "2026-04-12T10:00:00Z", "id": "order_123"}. On the next request, decode the cursor and use it in the WHERE clause: WHERE (created_at, order_id) < (:ts, :id). Make the cursor opaque to clients (base64-encoded) so they can’t manipulate it. Use a composite sort key that includes a unique column (id) to ensure deterministic ordering when created_at ties.”}},{“@type”:”Question”,”name”:”When should you use offset pagination instead of cursor pagination?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use offset pagination when: (1) users need to jump to arbitrary pages ("go to page 50"), (2) you need to display a total count ("showing 201-220 of 8,543"), (3) the dataset is small and performance is not a concern. Admin dashboards and reporting tools often need these features. For public APIs, infinite scroll feeds, and high-volume data retrieval, cursor pagination is preferred. Many systems use offset for small/bounded datasets and cursor for large/unbounded ones.”}},{“@type”:”Question”,”name”:”How do you implement bidirectional cursor pagination (prev and next)?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Return both next_cursor (pointing to the item after the last result) and prev_cursor (pointing to the item before the first result). For next page: WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC. For prev page: WHERE (created_at, id) > (:first_ts, :first_id) ORDER BY created_at ASC, then reverse the result. The cursor encodes direction as well as position. Many APIs omit prev_cursor for simplicity — they only support forward pagination (Twitter, GitHub).”}},{“@type”:”Question”,”name”:”What database index does cursor pagination require?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Create a composite index on the sort columns in the sort direction: CREATE INDEX idx_cursor ON orders(created_at DESC, order_id DESC). The WHERE (created_at, order_id) < (:ts, :id) clause uses this index for an efficient range scan — the DB seeks to the cursor position and scans forward, returning only the next page_size rows. Without this index, the DB performs a full table scan on every paginated request. For multi-tenant tables, include tenant_id as the first column: CREATE INDEX ON orders(tenant_id, created_at DESC, order_id DESC).”}}]}
Pagination and infinite scroll system design is discussed in Meta system design interview questions.
Cursor-based pagination and timeline feed design is covered in Twitter system design interview preparation.
API pagination and cursor design is discussed in Shopify system design interview guide.