Cursor vs Offset Pagination: Scalability Benchmarks

The Fatal Flaw of Offset Pagination

Offset pagination is the most common pattern taught to beginners:

GET /v1/transactions?page=5000&limit=20

Behind the scenes, your database executes:

SELECT * FROM transactions 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 100000;

Why This Fails at Scale:

1. O(N) Degrading Latency: The database engine must scan and sort all 100,020 rows off disk, discard the first 100,000, and return the final 20. When your dataset hits millions of records, page 5,000 takes several seconds and burns CPU.
2. Page Drift / Duplicate Items: If a user is viewing page 1 and a new transaction is inserted, every subsequent row shifts down by one index. When the user clicks page 2, the last item from page 1 appears again at the top of page 2.

---

How Cursor-Based Pagination Works

Cursor pagination (also called keyset pagination) does not use numerical offsets. Instead, it uses a deterministic pointer to a specific row in the indexed dataset:

GET /v1/transactions?cursor=eyJpZCI6MTAwfQ==&limit=20

The database query leverages indexed B-Tree seeks:

SELECT * FROM transactions 
WHERE id < 100 
ORDER BY id DESC 
LIMIT 20;

Why Cursor Pagination Wins:

• O(log N) Constant Execution Time: The database jumps straight to the record with an index seek, whether you are on record #10 or record #10,000,000.
• Immune to Page Drift: Even if 1,000 new items are inserted at the top of the table, the query only looks for records that existed after the specified cursor ID.

---

Designing Opaque Cursor Tokens

Never expose raw internal database column names in URLs (e.g. ?after_id=100). Always encode cursors as an opaque base64 string:

// Server-side cursor serialization (TypeScript)
function encodeCursor(lastItem: { id: string; created_at: Date }): string {
  const payload = JSON.stringify({
    id: lastItem.id,
    created_at: lastItem.created_at.toISOString()
  });
  return Buffer.from(payload).toString('base64url');
}

function decodeCursor(cursorToken: string): { id: string; created_at: string } {
  const json = Buffer.from(cursorToken, 'base64url').toString('utf-8');
  return JSON.parse(json);
}

The Standard Response Envelope

{
  "data": [
    { "id": "tx_99", "amount": 450.00 },
    { "id": "tx_98", "amount": 120.50 }
  ],
  "pagination": {
    "next_cursor": "eyJpZCI6InR4Xzk4IiwiY3JlYXRlZF9hdCI6IjIwMjYtMTAtMDQifQ",
    "has_more": true,
    "limit": 20
  }
}

When has_more is false, the client knows it has reached the end of the collection.