Every API with a list endpoint eventually hits the same wall. It works great with a thousand rows in the table. Then the table grows to five million rows, someone requests page 4,000, and the database starts scanning and discarding four million rows just to return twenty of them. The query that used to take eight milliseconds now takes four seconds, and nobody touched the code. The data just grew.
This is the moment most teams discover that LIMIT and OFFSET were never a real pagination strategy. They were a placeholder that happened to work at small scale. Cursor-based pagination is the fix, and it is not particularly hard to build once you understand why offset pagination breaks in the first place.
Why offset pagination falls apart
OFFSET tells the database "skip this many rows, then give me the next batch." The database has no shortcut for skipping. It still has to walk through every row before the offset, even though it's about to throw all of them away. As the offset grows, so does the work, and the growth is linear. Page 1 is fast. Page 2,000 is not.
This isn't a quirk of one database engine. It shows up in PostgreSQL and MySQL alike, because the underlying execution strategy is the same everywhere: scan, count, discard, then start returning rows. Rows skipped by an offset still have to be read and counted inside the server, so they're not free just because the client never sees them.
There's a second problem that has nothing to do with speed: correctness. Offset pagination assumes the underlying result set holds still between requests. It rarely does. If a user is paging through a list of orders and five new orders land while they're on page 3, every subsequent page shifts. Rows repeat, rows get skipped, and the user has no way to know it happened. On a support dashboard or a billing export, that's not a cosmetic bug. That's data someone acted on incorrectly.
Cursor pagination solves both problems at once, because it replaces "skip N rows" with "give me everything after this specific row." It's the same underlying idea behind keyset pagination that database practitioners have written about for years, and it's why most large-scale public APIs favor cursor or keyset-style tokens over raw page numbers once traffic gets serious, a pattern documented in the JSON:API specification.
What a cursor actually is
A cursor is an opaque pointer to a position in an ordered result set, usually built from the sort column plus a tiebreaker. If you're sorting by created_at, your cursor is a combination of created_at and id, encoded so the client can pass it back verbatim on the next request.
GET /api/orders?limit=25&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0wOFQxNDozMjowMFoiLCJpZCI6NDgxMjJ9Decoded, that cursor is just {"created_at": "2026-07-08T14:32:00Z", "id": 48122}. The query behind it looks like this:
SELECT * FROM orders
WHERE (created_at, id) < ('2026-07-08T14:32:00Z', 48122)
ORDER BY created_at DESC, id DESC
LIMIT 25;Notice what's missing: there's no OFFSET anywhere. The database uses the index on (created_at, id) to seek directly to the right starting point, then reads forward exactly limit rows. Page 4,000 costs the same as page 1, because the query plan doesn't know or care that this is "page 4,000." It's just "the next 25 rows after this point."
Why you need a compound cursor, not a single column
A cursor built from created_at alone breaks the moment two rows share the same timestamp, which happens constantly with millisecond-precision columns under real traffic. Without a tiebreaker, rows with identical timestamps can be skipped or duplicated across page boundaries depending on how the database happens to order ties.
The fix is a compound key: the sort column plus a unique column, almost always the primary key. (created_at, id) guarantees a strict, unambiguous order even when timestamps collide, because no two rows share the same id. This is the detail that separates pagination that works in staging from pagination that silently corrupts results under production concurrency.
"The bug I see most often isn't a broken cursor implementation, it's a single-column cursor that worked fine in testing because nobody generated two rows in the same millisecond. Compound cursors aren't an optimization, they're the actual correctness requirement." - Dennis Traina, founder of 137Foundry
Encoding the cursor so clients can't (usefully) tamper with it
Don't hand clients raw database values as the cursor. Base64-encode the JSON payload at minimum, so the cursor is opaque from the client's perspective and you're free to change its internal shape later without breaking the API contract. If the cursor needs to be tamper-resistant (say, it encodes something sensitive like a tenant boundary), sign it with an HMAC and verify the signature before decoding on the next request.
import base64
import json
def encode_cursor(created_at: str, row_id: int) -> str:
payload = json.dumps({"created_at": created_at, "id": row_id})
return base64.urlsafe_b64encode(payload.encode()).decode()
def decode_cursor(cursor: str) -> dict:
payload = base64.urlsafe_b64decode(cursor.encode())
return json.loads(payload)Treat the decoded cursor as untrusted input. Validate its shape before using it in a query, the same way you'd validate any other request parameter. If you want a standard token format instead of rolling your own, a signed JWT works well for this since verification and expiry are handled by well-tested libraries rather than custom code.
How this compares to GraphQL's Relay cursor spec
If you've worked with a GraphQL API, this pattern will look familiar. The Relay cursor connections specification formalized opaque cursor pagination years ago with edges, node, and pageInfo fields that map almost directly onto what's described here. You don't need GraphQL to use the idea, but it's worth knowing the REST version and the GraphQL version are solving the identical problem with nearly identical mechanics: an opaque pointer instead of a page number, and a boolean flag telling the client whether more data exists.
Handling both directions: forward and backward paging
Most implementations only handle "next page" and forget users sometimes need to go back. The trick is that backward pagination is the same query with the comparison operator and sort direction flipped, then the result set reversed in application code before returning it.
-- Forward (next page)
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
-- Backward (previous page)
WHERE (created_at, id) > (:cursor_created_at, :cursor_id)
ORDER BY created_at ASC, id ASC
-- then reverse the returned rows before sending the responseReturn both next_cursor and previous_cursor in the response envelope so the client doesn't have to reconstruct them. A response shape like this keeps the contract predictable:
{
"data": [ /* 25 orders */ ],
"next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0wOFQx...",
"previous_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0wN1Qw...",
"has_more": true
}The index you actually need
None of this is fast without the right index. The compound cursor query needs a composite index matching the sort order exactly: CREATE INDEX idx_orders_cursor ON orders (created_at DESC, id DESC);. Without it, the database falls back to a sequential scan and you've rebuilt offset pagination's performance problem with extra steps.
Check your query plan with EXPLAIN ANALYZE after building the index. You're looking for an index scan or index-only scan on the cursor query, not a sequential scan. If you see a sequential scan, the index either doesn't match the sort direction or the query isn't using the columns in the order the index expects. The Use the Index, Luke reference is the best free resource for understanding exactly how databases choose between scan types, and it's worth bookmarking for any team doing serious query optimization work beyond just pagination.
If you're running this on a managed database console, confirm the composite index actually gets created the way you expect before trusting it in production. Some managed consoles apply default index options that don't match a hand-written CREATE INDEX statement, and the only way to know for sure is to check the query plan directly rather than assume the index exists.
When offset pagination is still fine
Cursor pagination isn't free. It can't jump to "page 47" the way offset pagination can, because there's no concept of a page number, only a position. If your product genuinely needs numbered pages with direct jump-to-page controls, and your table is small enough that a full scan costs nothing, offset pagination is simpler to implement and there's no reason to add the complexity of cursor encoding. The decision point is usually table size and whether the underlying data mutates while users are browsing it. Static or slow-growing reference tables under a few hundred thousand rows rarely need cursors. High-write tables like orders, events, logs, or activity feeds need them almost immediately.
Testing pagination under concurrent writes
The bug that ships to production almost never shows up in a test with a static dataset. Write a test that inserts new rows between page requests and asserts that no row appears twice and no row is silently skipped. This is the scenario offset pagination fails and cursor pagination is specifically designed to survive, so it's the one test that actually proves the implementation works.
def test_cursor_pagination_stable_under_concurrent_inserts():
page_one = fetch_page(limit=10)
insert_new_orders(count=5) # simulates concurrent writes
page_two = fetch_page(limit=10, cursor=page_one["next_cursor"])
ids_seen = {row["id"] for row in page_one["data"] + page_two["data"]}
assert len(ids_seen) == len(page_one["data"]) + len(page_two["data"])If that test passes with offset pagination in place, you got lucky with timing. Run it a few hundred times and the flakiness reveals itself.
Rolling it out without breaking existing clients
If you're migrating an API that already has offset-based consumers, support both simultaneously rather than forcing a hard cutover. Accept page/per_page as before, and add cursor/limit as an additive option. Document the cursor approach as preferred and let high-volume consumers migrate on their own schedule. A hard breaking change on a public API is rarely worth the support burden compared to running both for a deprecation window.
Cursor pagination is one of those pieces of infrastructure that's invisible when it's working and expensive when it's missing. Build it once, index it correctly, and page 4,000 costs exactly what page 1 costs. For teams weighing whether to build this internally or bring in outside help, our web development service and broader services hub cover API design work like this alongside the surrounding application. You can read more about our approach on our about page or start from the 137Foundry homepage.
Cursor pagination pairs naturally with rate limiting and caching layers further up the stack, both of which we've written about in the context of data integration work where large paginated pulls from third-party APIs are the norm rather than the exception.
