API Pagination Low-Level Design: Cursor, Offset, and Keyset Strategies

By /

Pagination is not optional for any endpoint that returns a list — without it, a single request can return millions of rows, exhaust server memory, and timeout the client. There are three meaningful strategies: offset pagination (simple but broken at scale), cursor pagination (scalable and consistent), and keyset pagination (fastest for very large datasets). The right choice depends on whether your clients need random page access and how large the dataset is.

Offset Pagination (and Why It Breaks)

-- Client sends: GET /posts?page=5&per_page=20
SELECT * FROM Post
ORDER BY created_at DESC
LIMIT 20 OFFSET 100;  -- skip 100 rows, return 20

Problems: (1) Performance degrades with depth — OFFSET 100000 scans and discards 100,000 rows before returning 20. At page 5000, a single query takes seconds. (2) Data drift — if a new post is inserted between page 1 and page 2 fetches, the user sees a duplicate (the post that was on the boundary shifts to the next page). (3) No stable cursor — if rows are deleted, page numbers shift.

Use offset pagination only for small datasets (<10,000 rows) or admin interfaces where users rarely go past page 10.

Cursor Pagination (Standard for APIs)

-- Client sends: GET /posts?limit=20&cursor=eyJpZCI6MTIzNH0=
-- Server decodes cursor: {"id": 1234, "created_at": "2024-01-15T10:00:00Z"}

SELECT * FROM Post
WHERE (created_at, id) < ('2024-01-15T10:00:00Z', 1234)  -- keyset condition
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Server encodes next cursor from last row of results:
next_cursor = base64(json({"id": last_row.id, "created_at": last_row.created_at}))
def paginate_posts(cursor_token: str | None, limit: int = 20) -> dict:
    limit = min(limit, 100)  # cap at 100
    if cursor_token:
        cursor = json.loads(base64.decode(cursor_token))
        rows = db.fetchall("""
            SELECT id, title, created_at FROM Post
            WHERE (created_at, id)  limit
    rows = rows[:limit]
    next_cursor = None
    if has_next and rows:
        last = rows[-1]
        next_cursor = base64.encode(json({'id': last['id'], 'created_at': last['created_at']}))
    return {'data': rows, 'next_cursor': next_cursor, 'has_next': has_next}

Required Index for Cursor Pagination

-- Composite index matching the ORDER BY and WHERE clause
CREATE INDEX idx_post_pagination ON Post(created_at DESC, id DESC);

-- This makes the keyset condition (created_at, id) < (ts, id) an index scan
-- bounded by the cursor position — O(log N + page_size), not O(N)

Bi-directional Pagination

Some UIs need both forward and backward navigation (e.g., a chat thread). Encode direction in the cursor and flip the inequality:

// Cursor encodes: {id, created_at, direction}
// Forward: WHERE (created_at, id)  (cursor.created_at, cursor.id) ORDER BY ASC
//           then reverse the result in application code to maintain descending display order

// Also return a prev_cursor from the first row and next_cursor from the last row
return {
    data: rows,
    next_cursor: encode({...last_row, direction: 'forward'}),
    prev_cursor: encode({...first_row, direction: 'backward'})
}

Filtering with Cursor Pagination

When filters are applied, the cursor must be stable with those filters. The cursor encodes the position within the filtered result set, not the full table. The filter columns must be part of the index:

-- Filter by user_id (filtered feed)
CREATE INDEX idx_post_user_pagination ON Post(user_id, created_at DESC, id DESC);

SELECT * FROM Post
WHERE user_id = :user_id
  AND (created_at, id) < (:cursor_ts, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Key Interview Points

  • Always return has_next (boolean) — computed by fetching limit+1 rows and checking if the extra row exists. Never make the client guess.
  • Cursors must be opaque to clients — base64-encoded JSON. Never expose raw IDs or offsets; that couples clients to your DB structure.
  • For total count: avoid COUNT(*) on large tables. Instead, return an approximate count from pg_class.reltuples or a separate counter table, or omit the count entirely (most mobile feeds don’t show “page 42 of 8,293”).
  • The cursor is a position, not a page number — it’s valid only for the current sort order and filter set. Changing the sort order requires a new first-page request.
  • Keyset pagination (the WHERE clause above) is O(log N) — performance is identical whether you’re on page 1 or page 10,000.
  • For search results (Elasticsearch), use search_after instead of from/size — it’s the ES equivalent of keyset pagination.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you implement total page count in a paginated API without COUNT(*)?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”COUNT(*) on a large table is a full index scan — on a table with 50 million rows, it takes 500ms-2s and adds that cost to every paginated request. Three alternatives: (1) Omit the total count. Most mobile feeds (Instagram, Twitter) show no total count — users infinite-scroll without knowing the total. (2) Approximate count from pg_class: SELECT reltuples::BIGINT FROM pg_class WHERE relname=’post’. Updated by ANALYZE, accurate within ~1-5%. Fast — a single metadata lookup. (3) Maintain a counter table: a separate table that stores the pre-computed count, updated via triggers or application code on insert/delete. O(1) lookup, exact, but adds write overhead. Choose based on how critical exact counts are — most UIs work fine with approximate or no count.”}},{“@type”:”Question”,”name”:”What is the difference between cursor pagination and keyset pagination?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”These terms are often used interchangeably, but they describe different layers: keyset pagination is the SQL technique (WHERE (col1, col2) < (val1, val2)) that makes pagination efficient by using an index range scan instead of OFFSET. Cursor pagination is the API contract — the server encodes a position into an opaque token (cursor) and returns it to the client, who passes it back on the next request. Cursor pagination is typically implemented using keyset conditions under the hood. You can implement cursor pagination without keyset conditions (using OFFSET internally with a cursor that encodes the offset number), but this loses the performance benefit. The correct implementation: cursor pagination at the API level, keyset conditions in the SQL, composite index matching the ORDER BY.”}},{“@type”:”Question”,”name”:”How do you handle a cursor becoming invalid after the referenced row is deleted?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”If the row encoded in the cursor is deleted, the keyset condition WHERE (created_at, id) < (cursor_ts, cursor_id) still works correctly — it returns the next rows after the deleted row’s position without error. The deleted row is simply absent from results. This is a feature, not a bug: cursor pagination is inherently resilient to row deletions. Contrast with offset pagination: deleting a row shifts all subsequent rows one position, causing offset-based cursors to skip or duplicate a row. For soft-deletes (WHERE deleted_at IS NULL), the deleted row is excluded by the filter, and the cursor position is still valid — results naturally skip the soft-deleted row. Document this behavior explicitly in API docs so clients understand they may see gaps after deletions.”}},{“@type”:”Question”,”name”:”How does Elasticsearch implement cursor pagination and what is search_after?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Elasticsearch’s from/size pagination (equivalent to SQL OFFSET/LIMIT) degrades severely past from=10000 — Elasticsearch must fetch and sort from+size documents across all shards, then discard the first from. The hard limit is index.max_result_window=10000. search_after is the ES equivalent of keyset pagination: it uses the sort values of the last document as a cursor, fetching only the next page of results. Usage: sort: [{created_at: "desc"}, {_id: "desc"}], search_after: [cursor_timestamp, cursor_id]. This is O(log N) regardless of depth. Use the point-in-time (PIT) API with search_after for consistent pagination across index refreshes — the PIT snapshots the index state so concurrent indexing doesn’t affect page results.”}},{“@type”:”Question”,”name”:”How do you paginate a feed that requires real-time updates between page fetches?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The core challenge: user is on page 3 of a feed, new items are added, page 4 is now shifted. With cursor pagination, this is handled correctly — the cursor encodes the exact position, not a page number. New items inserted before the cursor’s position don’t affect what comes after it. New items inserted after the cursor’s position are not returned in forward pagination (you’re moving backward in time). To show new items: implement a separate "new items" endpoint that returns items inserted after the most recent item the user has seen (tracked by the first item’s id from the first page fetch). The main feed is paginated forward; new items appear as a banner ("5 new posts — tap to refresh") and are loaded separately.”}}]}

API pagination and infinite scroll feed design is discussed in Twitter system design interview questions.

API pagination and feed pagination design is covered in LinkedIn system design interview preparation.

API pagination and product catalog pagination design is discussed in Shopify system design interview guide.

Scroll to Top