API Pagination: Offset vs Cursor-Based

Offset pagination (also known as page-number pagination) is the most intuitive pagination scheme. The client supplies a page number and page size:

http
Copy
GET /v1/posts?page=50000&limit=20

This translates to standard SQL:

sql
Copy
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 1000000;

The Database Performance Disaster:

Relational database storage engines (PostgreSQL heap files, MySQL InnoDB clustered indexes) cannot jump directly to row number 1,000,000 in O(1) time.

To fulfill OFFSET 1000000:

1. The database query planner must scan and count through 1,000,000 index tuples or table rows.
2. It sorts and tracks all 1,000,000 rows in memory/temp disk buffers.
3. It discards the first 1,000,000 rows.
4. It finally extracts and returns the subsequent 20 rows.

Disk I/O & Latency Degradation:

• Page 1 (OFFSET 0): ≈ 1.5ms
• Page 100 (OFFSET 2,000): ≈ 8ms
• Page 5,000 (OFFSET 100,000): ≈ 140ms
• Page 50,000 (OFFSET 1,000,000): ≈ 1,800ms - 12,000ms (Often causing gateway timeouts / 504 Gateway Timeout).

In any real-time system with concurrent writes (social feeds, chat messages, financial ledgers), offset pagination causes severe data anomalies:

1. Duplicate Record Drift (On Insert):

1. User requests Page 1 (LIMIT 5 OFFSET 0). The server returns items: [Post A, Post B, Post C, Post D, Post E].
2. While the user is scrolling, another user publishes Post X at position 1.
3. User scrolls and requests Page 2 (LIMIT 5 OFFSET 5).
4. The database offsets 5 rows from the new start: [Post X, Post A, Post B, Post C, Post D, **Post E**, ...].
5. Post E is returned a second time! The client application renders duplicate content.

2. Skipped Record Drift (On Delete):

If an item on Page 1 is deleted before Page 2 is loaded, all subsequent records shift up by one position, causing the first item of Page 2 to be skipped entirely.

Cursor-based pagination (also called Keyset pagination) eliminates both the O(N) scan penalty and page drift by indexing off the last record retrieved:

The Cursor Concept:

Instead of asking for "Page 50", the client asks for: "The 20 items created immediately before Item X".

http
Copy
GET /v1/posts?cursor=eyJjcmVhdGVkX2F0IjoxNzE0MDAwLCJpZCI6MTA0Mn0&limit=20

Compound Index & SQL Query:

Assuming a compound B-Tree index on (created_at DESC, id DESC):

sql
Copy
SELECT id, title, created_at 
FROM posts
WHERE (created_at, id) < ('2026-04-12 14:30:00', 1042)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Why Keyset Pagination is O(log N) / O(1):

The database engine performs a direct B-Tree index seek to find the exact point ('2026-04-12 14:30:00', 1042) in O(log N) time, reads the next 20 contiguous leaf blocks, and stops. It never reads or counts earlier discarded rows.

Query latency remains ≈ 1-3ms whether fetching the 1st page or the 10,000,000th page!

To prevent clients from parsing or depending on database column names, encode cursor pointers as opaque Base64 strings:

json
Copy
{
  "data": [
    { "id": "post_1041", "title": "System Design Mastery", "created_at": "2026-04-12T14:29:50Z" },
    { "id": "post_1040", "title": "Understanding LSM Trees", "created_at": "2026-04-12T14:28:10Z" }
  ],
  "pagination": {
    "limit": 2,
    "has_more": true,
    "next_cursor": "ZXlKalltbGhkR1ZrWDJGMElqb3hOekUwTURBd0xDSnBaQ0k2TVRBME1Dd3Z",
    "prev_cursor": null
  }
}

Comparison Matrix: