REST API 08 🌐 Pagination Strategies (Offset vs Cursor-Based)
A collection endpoint that returns every matching record in a single response is not a usable API. It is a memory hazard for the server, a bandwidth hazard for the network, and a usability hazard for the client. Pagination is the mechanism that breaks the result set into pages, and the choice of strategy determines whether the API scales to millions of records or collapses under the weight of its own offset. The two dominant strategies—offset and cursor—are not interchangeable. They have different performance profiles, different consistency guarantees, and different client experiences, and the decision between them is one of the most consequential design choices in a REST API.
Offset pagination is the intuitive approach: the client asks for ?page=3&limit=20 or ?offset=40&limit=20, and the server skips the first 40 rows and returns the next 20. It is simple to implement, it supports jumping to any page, and it can return a total count cheaply. But it has two problems that become severe at scale. The first is performance: the database must read and discard every row before the offset, so page 10,000 is dramatically slower than page 1 . The second is consistency: if a row is inserted or deleted before the current page while the client is paginating, the window shifts, and the client sees duplicates or misses records entirely .
Cursor pagination—also called keyset pagination—solves both problems. Instead of asking the server to skip a number of rows, the client asks for records that come after a specific position, identified by an opaque cursor. The database uses an index to seek directly to that position, so every page costs the same regardless of depth . And because the cursor is anchored to a specific row rather than a row number, concurrent inserts and deletes do not shift the window; the client sees a stable, consistent traversal . The cost is that the client cannot jump to an arbitrary page, and the total count requires a separate query .
Key point: Offset pagination uses LIMIT and OFFSET to skip rows and is simple but degrades linearly with depth and becomes inconsistent when data changes during traversal. Cursor pagination uses a keyset condition (WHERE (sort_key, id) > (last_value, last_id)) to seek directly to the next page; it is O(1) per page at any depth and stable under concurrent writes, but it does not support random page access and requires a deterministic, indexed sort key.
Why offset pagination degrades with depth
The scanning problem. When a database executes SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000, it does not jump to the 10,000th row. It reads the first 10,020 rows in sorted order, discards the first 10,000, and returns the last 20 . The work is proportional to the offset, not the page size. Page 1 reads 20 rows; page 500 reads 10,020 rows; page 10,000 reads 200,020 rows . The query time grows linearly with depth, and the deepest pages—the ones that crawlers and integrations eventually reach—are the slowest and most expensive.
The consistency problem. Offset pagination is defined against a snapshot of the data that may not exist by the time the client requests the next page. If a new order is inserted before the current offset, every subsequent page shifts by one row. The item that was the last item on page 1 becomes the first item on page 2, and the client sees it twice. If an item is deleted before the offset, every subsequent page shifts backward, and an item that should have appeared on page 2 is skipped entirely . The client has no way to detect this without comparing full result sets, and integrations that rely on complete, non-duplicated traversal are broken.
The deep-pagination attack problem. An API that accepts ?page=1000000 must handle that request. Even if legitimate users never go that deep, a crawler or a malicious client can issue it, and the database will perform the scan . The cost is borne by the server, and it can be used as a denial-of-service vector. Cursor pagination eliminates this because there is no arbitrary offset to request; the client can only continue from where the last page ended .
When offset is acceptable. Offset pagination is the correct choice for small datasets (under a few thousand rows), internal admin interfaces where users click page numbers and never go deep, and data that changes infrequently . The performance and consistency problems are real, but they do not manifest at the scale where offset is used. The mistake is using offset for a public API or a large dataset because it was easier to implement.
a. Cursor pagination: the keyset mechanism
Cursor pagination is built on a keyset condition. Instead of OFFSET 10000, the query uses WHERE (sort_key, id) > (last_sort_value, last_id). The database seeks directly to that position using an index, and the cost is the same whether the client is on page 1 or page 60,000 .
The sort key must be unique, or the pagination must include a tie-breaker that makes it unique. A timestamp alone is not unique: two orders can be created in the same millisecond. If the cursor uses only the timestamp, the query WHERE created_at < '2026-10-18T12:00:00Z' will skip every row with the same timestamp, and the client will miss records . The fix is a composite key: (created_at, id). The condition becomes WHERE (created_at, id) < ('2026-10-18T12:00:00Z', 'ord_8821'), which is exact even when timestamps collide .
The cursor itself is an opaque token. The server encodes the sort key values into a base64 string, and the client passes it back unchanged. The opacity is deliberate: the client cannot construct a cursor by hand, which means the server can change the encoding, add a shard hint, or switch storage engines without breaking clients . The contract is “pass back what we gave you,” and nothing more.
The index is not optional. For the keyset condition to be fast, the database needs a composite index that matches the sort columns and their order: (created_at DESC, id DESC) for a descending sort . Without the index, the database must scan and sort the entire table, and the performance advantage disappears .
-- The keyset query for a descending sort on (created_at, id)
-- The WHERE clause seeks directly to the position after the cursor
-- The index on (created_at DESC, id DESC) makes the seek fast
SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE (created_at, id) < ('2026-10-18T12:00:00Z', 'ord_8821')
ORDER BY created_at DESC, id DESC
LIMIT 25;b. The cursor envelope and client contract
A cursor API returns the data and the next cursor in a structured envelope. The client uses the next cursor to request the following page, and stops when the cursor is null.
// The response envelope for a cursor-paginated collection
// data: the current page of records
// next_cursor: opaque token for the next page, or null if this is the last
// has_more: convenience flag derived from next_cursor
{
"data": [
{ "id": "ord_8821", "total": 4500, "created_at": "2026-10-18T12:00:00Z" },
{ "id": "ord_8820", "total": 1200, "created_at": "2026-10-18T11:59:59Z" }
],
"next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0xOFQxMTo1OTo1OVoiLCJpZCI6Im9yZF84ODIwIn0",
"has_more": true
}The client’s loop is simple: request the first page with no cursor, then request the next page with the next_cursor from the previous response, until next_cursor is null . The client never constructs a cursor; it only echoes the one the server provided. This is what allows the server to change the cursor format without breaking clients.
The total count is not part of the cursor envelope by default. Counting every matching row is a separate COUNT(*) query, and for large datasets it can be as expensive as the pagination itself . If the client needs a total, it should be a separate endpoint or an optional parameter that the client explicitly requests, not a default field that every response pays for . Some APIs cap the count (e.g., “50,000+”) to bound the cost .
c. Combining cursor pagination with filtering and sorting
Cursor pagination composes with filtering and sorting, but the sort key and the filter must be designed together. The filter narrows the set, the sort orders it, and the cursor marks the position within the ordered, filtered set.
The filter is applied in the WHERE clause before the keyset condition. A request for active orders after a cursor becomes:
-- The status filter narrows the set
-- The keyset condition seeks to the cursor position within the filtered set
-- The index must cover the filter column and the sort columns
SELECT id, total, created_at
FROM orders
WHERE status = 'active'
AND (created_at, id) < ('2026-10-18T12:00:00Z', 'ord_8821')
ORDER BY created_at DESC, id DESC
LIMIT 25;The index must match the query. If the filter is on status and the sort is on (created_at, id), the index should be (status, created_at DESC, id DESC) . Without the composite index, the database cannot seek directly to the cursor position, and the performance advantage is lost.
Sorting with cursor pagination requires a deterministic order. If the client can sort by a mutable, non-unique column like status, the keyset logic becomes difficult because the cursor must encode the value of a column that can change . The practical approach is to restrict cursor pagination to stable, unique, indexed sort keys—usually (created_at, id) or (id)—and use offset pagination for the cases where random access or mutable sorting is genuinely required.
Complete Example Session
The following session traces a cursor-paginated request for a list of orders, showing the first page, the cursor, the second page, and the end of the collection.
// ============================================
// PART 1: THE FIRST PAGE
// ============================================
// The client requests the first page with no cursor
GET /api/v1/orders?limit=2
// Response:
// {
// "data": [
// { "id": "ord_8821", "total": 4500, "created_at": "2026-10-18T12:00:00Z" },
// { "id": "ord_8820", "total": 1200, "created_at": "2026-10-18T11:59:59Z" }
// ],
// "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0xOFQxMTo1OTo1OVoiLCJpZCI6Im9yZF84ODIwIn0",
// "has_more": true
// }
// ============================================
// PART 2: THE SECOND PAGE
// ============================================
// The client passes the next_cursor from the first response
GET /api/v1/orders?limit=2&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0xOFQxMTo1OTo1OVoiLCJpZCI6Im9yZF84ODIwIn0
// Response:
// {
// "data": [
// { "id": "ord_8819", "total": 8900, "created_at": "2026-10-18T11:59:58Z" },
// { "id": "ord_8818", "total": 300, "created_at": "2026-10-18T11:59:57Z" }
// ],
// "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0xOFQxMTo1OTo1N1oiLCJpZCI6Im9yZF84ODE4In0",
// "has_more": true
// }
// ============================================
// PART 3: THE THIRD PAGE (END OF COLLECTION)
// ============================================
// The client passes the new cursor
GET /api/v1/orders?limit=2&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0xOFQxMTo1OTo1N1oiLCJpZCI6Im9yZF84ODE4In0
// Response:
// {
// "data": [
// { "id": "ord_8817", "total": 1500, "created_at": "2026-10-18T11:59:56Z" }
// ],
// "next_cursor": null,
// "has_more": false
// }
// ============================================
// PART 4: THE CLIENT LOOP
// ============================================
// The client stops when next_cursor is null
// It never constructs a cursor; it echoes the one it received
// The server can change the cursor format without breaking the client
// ============================================
// PART 5: THE FILTERED VARIANT
// ============================================
// The filter narrows the set before the cursor condition
GET /api/v1/orders?status=active&limit=2&cursor=eyJ...
// The index must cover (status, created_at DESC, id DESC)
// to seek to the cursor position within the filtered setThe five parts cover the first page, the second page, the end of the collection, the client loop, and the filtered variant. What the session demonstrates is that the cursor is opaque and the client’s only job is to pass it back. The pagination is stable, the performance is constant, and the server controls the encoding.
Quick Reference
Offset vs Cursor
| Dimension | Offset | Cursor |
|---|---|---|
| Jump to arbitrary page | Yes | No |
| Deep-page performance | O(n), degrades with depth | O(1) per page |
| Stability under writes | Duplicates and gaps | Stable |
| Total count | Cheap to include | Separate query |
| Build cost | Trivial | Moderate |
| Sort requirement | Any ORDER BY | Unique, indexed sort key |
When to Use Which
| Situation | Choice |
|---|---|
| Small dataset (< 10,000 rows) | Offset |
| Admin table with page numbers | Offset |
| Infinite scroll feed | Cursor |
| Public API with crawlers | Cursor |
| Export/sync job | Cursor |
| Large dataset, sequential access | Cursor |
Cursor Query Construction
| Component | Example |
|---|---|
| Sort key | (created_at DESC, id DESC) |
| Keyset condition | WHERE (created_at, id) < (?, ?) |
| Index | (created_at DESC, id DESC) |
| Cursor encoding | Base64 of {created_at, id} |
| Next cursor | Last row’s sort key values |
Envelope Fields
| Field | Purpose |
|---|---|
data | Current page records |
next_cursor | Opaque token for next page, or null |
has_more | Boolean derived from next_cursor |
total_count | Optional, separate query, capped |
Best Practices
✅ Do This:
-- Use a composite key with a tie-breaker
WHERE (created_at, id) < ('2026-10-18T12:00:00Z', 'ord_8821')
ORDER BY created_at DESC, id DESC
-- Create the matching composite index
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);
-- Use an opaque cursor (base64)
-- The client echoes it back unchanged❌ Don’t Do This:
-- Use a non-unique sort key without a tie-breaker
WHERE created_at < '2026-10-18T12:00:00Z' -- skips rows with the same timestamp
-- Use OFFSET for deep pages
LIMIT 20 OFFSET 10000 -- scans 10,020 rows
-- Expose the raw sort key in the cursor
?cursor=created_at:2026-10-18,id:8821 -- client can manipulate itCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Duplicate items across pages | No tie-breaker on non-unique sort key | Add id to the keyset |
| Missing items | Same as above | Composite key |
| Slow deep pages | Offset scans skipped rows | Cursor pagination |
| Cursor invalid after data change | Raw key exposed | Opaque token |
| Total count expensive | Separate COUNT(*) | Make it optional or capped |
| Index not used | Missing composite index | Create index matching sort |
Real-World Examples
1. Cursor request
GET /api/items?limit=25&cursor=eyJpZCI6MTIzfQ2. Cursor response
{ "data": [...], "next_cursor": "eyJpZCI6MTQ4fQ", "has_more": true }3. Keyset SQL
SELECT * FROM orders WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 254. Composite index
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);5. Offset request
GET /api/items?page=3&limit=256. Offset SQL
SELECT * FROM orders ORDER BY created_at DESC LIMIT 25 OFFSET 507. Cursor with filter
GET /api/items?status=active&limit=25&cursor=eyJpZCI6MTIzfQ8. Base64 cursor encoding
base64('{"created_at":"2026-10-18T12:00:00Z","id":"ord_8821"}')9. Last page detection
{ "data": [...], "next_cursor": null, "has_more": false }10. Optional total count
GET /api/items?limit=25&include_total=trueVisual
Offset Pagination Scanning
┌──────────────────────────────────────────────┐
│ OFFSET 10000 LIMIT 20 │
│ │ │
│ ▼ │
│ Read rows 1..10020 │
│ Discard rows 1..10000 │
│ Return rows 10001..10020 │
│ │
│ Cost grows with the offset │
└──────────────────────────────────────────────┘Cursor Pagination Seek
┌──────────────────────────────────────────────┐
│ WHERE (created_at, id) < (?, ?) │
│ │ │
│ ▼ │
│ Index seek to the cursor position │
│ Read the next 20 rows │
│ │
│ Cost is constant at any depth │
└──────────────────────────────────────────────┘Stability Under Writes
┌──────────────────────────────────────────────┐
│ OFFSET: row inserted before the window │
│ → window shifts, item duplicated │
│ │
│ CURSOR: row inserted before the cursor │
│ → cursor is anchored to a row, not a number │
│ → new row is before the cursor, not seen │
│ → no duplicate, no skip │
└──────────────────────────────────────────────┘Cursor Envelope
┌──────────────────────────────────────────────┐
│ { │
│ "data": [...], │
│ "next_cursor": "eyJpZCI6MTQ4fQ", │
│ "has_more": true │
│ } │
│ │ │
│ ▼ │
│ Client passes next_cursor as ?cursor= │
│ Client stops when next_cursor is null │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Offset mechanism | LIMIT + OFFSET |
| Cursor mechanism | Keyset condition on indexed sort key |
| Offset deep-page cost | O(n), degrades with depth |
| Cursor per-page cost | O(1) at any depth |
| Offset stability | Duplicates/gaps under writes |
| Cursor stability | Stable, anchored to a row |
| Offset page jump | Yes |
| Cursor page jump | No |
| Cursor encoding | Opaque token (base64) |
| Cursor requirement | Unique, indexed sort key + tie-breaker |
Key takeaways:
- Offset pagination degrades with depth. The database reads and discards every row before the offset, so page 10,000 is dramatically slower than page 1. The cost is O(offset), not O(page size) .
- Offset pagination is unstable under concurrent writes. A row inserted or deleted before the current page shifts the window, causing duplicates or skipped records. Cursor pagination anchors to a row, not a number, so it is stable .
- Cursor pagination uses a keyset condition. The query is
WHERE (sort_key, id) > (last_value, last_id), and the database seeks directly to that position using an index. The cost is the same at any depth . - The sort key must be unique, or the cursor must include a tie-breaker. A timestamp alone is not unique;
(created_at, id)is. The tie-breaker prevents duplicates and skips at page boundaries . - The cursor should be opaque. Encoding the sort key values into a base64 token prevents clients from manipulating the cursor and allows the server to change the encoding without breaking clients .
- Cursor pagination composes with filtering and sorting. The filter narrows the set, the sort orders it, and the cursor marks the position. The index must cover the filter and sort columns .
- Offset is the right choice for small datasets and admin interfaces. The performance and consistency problems do not manifest at small scale, and the ability to jump to a specific page is a genuine feature .
Remember: Pagination is not a single technique. It is a decision between two strategies with different trade-offs. Offset is simple, supports random access, and is fine for small datasets and internal tools. Cursor is stable, scales to millions of records, and is the correct choice for public APIs, infinite scroll feeds, and export jobs. The mistake is not choosing one or the other—it is choosing offset for a large dataset because it was easier to implement, and then discovering the performance and consistency problems at the worst possible time. The keyset query and the composite index are the mechanism; the opaque cursor is the contract; and the choice between them is determined by how the data is consumed, not by which is easier to build.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!