Free Knowledge Library
Sign in with a free account to unlock all 6 chapters, copy production code snippets, and mark guides as read.
API Pagination: Offset vs Cursor
A deep technical breakdown of Offset vs Cursor-based (Keyset) pagination — analyzing database B-Tree index scan mechanics, the deep offset performance cliff, data drift anomalies, and implementing stable encoded cursor tokens.
In relational databases, `OFFSET N` does not jump directly to row N. The database engine must scan, read, and discard all N preceding rows from disk or buffer memory before returning the requested rows.
Use Offset pagination for small tables (<50,000 rows) or admin dashboards requiring direct jump-to-page navigation. Use Cursor pagination for infinite-scroll mobile feeds, high-write datasets, and public APIs.
- Understand why SQL LIMIT / OFFSET degrades linearly ($O(N)$) on large tables
- Explain the data drift problem: missing or duplicated rows during infinite scrolling
- Implement high-performance Keyset Cursor pagination using indexed WHERE clauses ($O(\log N)$)
- Encode multi-column sort cursors securely using Base64 tokens
1. Offset-Based Pagination Mechanics
Free Chapter PreviewOffset pagination is the most common implementation in web applications:
GET /api/v1/products?limit=20&page=5The server calculates: offset = (page - 1) * limit = 80 and executes:
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 80;Advantages:
- Simple to implement with ORMs.
- Allows clients to jump directly to any arbitrary page (e.g. Page 7).
- Returns total count (
SELECT COUNT(*)) for displaying total pages in UI navigation.
2. The Offset Performance Cliff ($O(N)$ Degradation)
In this section, we examine the production failure mechanics, internal socket buffers, and kernel-level trace diagnostics...
Sign In to Read the Full Guide
Unlock all remaining 5 chapters of API Pagination: Offset vs Cursor. Get complete production code snippets, architectural trade-offs, and track your reading completion.