Pagination System Low-Level Design

By /
⏱ 5 min read

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.

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

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.

Scroll to Top