Cursor-based pagination is the gold standard for high-performance API design. Learn why it beats offset pagination and how to implement it in your system.
We’ve all been there: an on-call alert fires because the database is spiking at 99% CPU. You trace the query, and it’s a simple OFFSET 50000 LIMIT 20 on a table with millions of rows. The database engine is scanning through 50,000 records just to throw them away and return the last twenty. It’s a classic performance bottleneck, and it happens because we rely on naive offset pagination.
When you're building systems that handle high-volume streams, you need a more robust approach to data retrieval. That’s where cursor-based pagination comes in. It’s not just an optimization; it’s a requirement for any distributed system that needs to remain performant as data scales.
Offset pagination works by skipping a set number of rows. The logic is simple: SELECT * FROM logs ORDER BY created_at DESC LIMIT 20 OFFSET X. As X grows, the database has to perform a full scan up to that offset. This leads to linear performance degradation.
Beyond the performance hit, it’s fundamentally broken for dynamic datasets. If a new record is inserted while a user is paginating, the index shifts. Your user ends up seeing the same record twice on page two that they just saw on page one. It’s a common source of data integrity bugs in production.
Instead of telling the database how many rows to skip, you provide a reference point—a "cursor"—that tells the system exactly where to resume. This cursor is typically an opaque string (often base64-encoded) containing the sort key and the unique ID of the last processed record.
When we migrated our primary event feed, we saw latency drop from roughly 400ms on deep pages to a flat 25ms regardless of the page depth. Here’s the basic flow:
/events?limit=20.next_cursor (e.g., eyJpZCI6IDEyMzQsICJjcmVhdGVkX2F0IjogMTcwMDAwMDAwMH0=)./events?limit=20&cursor=....The query becomes an index-seek: SELECT * FROM events WHERE created_at <= :cursor_val AND id < :cursor_id ORDER BY created_at DESC, id DESC LIMIT 20. Because you’re using the index, the lookup time is constant, regardless of how far into the dataset you are.
While cursor-based pagination is superior for performance, it forces you to think differently about API design.
You lose the ability to jump to an arbitrary page (e.g., "Go to page 500"). If your product requirements demand "Go to page X," you might need to reconsider your UI or accept that deep-paging isn't a primary use case. We’ve found that most users don't actually need to jump to page 500; they just need to scroll through a feed.
We initially tried to implement a hybrid approach where we allowed offsets for the first 100 pages and switched to cursors after that. It was a maintenance nightmare. We eventually scrapped it in favor of pure cursor-based navigation.
When you're designing your schema, ensure your sort keys are indexed. If you’re sorting by created_at, that column must be part of a composite index. Otherwise, the database will still perform a filesort, negating the benefits of the cursor.
Cursor-based pagination plays well with other robust patterns. For instance, when dealing with high-volume mutations, you might use API design for asynchronous processing: Mastering high-volume job offloading to handle the underlying data writes while your API returns a cursor to track the progress of the job.
If you’re concerned about concurrency during pagination, consider API Concurrency with ETag-Based Optimistic Locking Strategies to ensure that the data state hasn't changed significantly while the user is traversing the stream.
Q: Should the cursor be encrypted? A: Not necessarily, but it should be opaque. Don't expose your internal database IDs or raw timestamps directly if you can avoid it. Base64 encoding is usually sufficient to discourage clients from trying to "guess" the next cursor.
Q: What if the record used as a cursor is deleted? A: This is a risk. If your cursor points to an ID that no longer exists, your query might fail. We mitigate this by using a composite key (Timestamp + UUID) in the cursor. Even if the specific record is gone, the timestamp allows the query to find the next valid record in the sequence.
Q: Does this work with every database?
A: It works best with RDBMS engines like PostgreSQL or MySQL. If you’re using NoSQL (like DynamoDB), you’re likely already using LastEvaluatedKey, which is essentially a native cursor.
Moving to cursor-based pagination is one of the highest-leverage changes you can make for API performance. It shifts the burden from the database engine’s scanning logic to its indexing logic.
Next time, I’d like to experiment with "keyset pagination" using multi-column sorts to handle more complex filtering, but for now, the cursor approach has cleaned up our tail latency spikes significantly. It’s not a silver bullet, but it’s definitely the right tool for the job when you need to scale.
API design for asynchronous processing is critical when scaling high-volume mutations. Learn to build reliable, scalable job queues for your distributed systems.
Read moreAPI design for webhooks requires robust delivery guarantees and payload security. Learn how to implement retries, idempotency, and HMAC signing in your systems.