REST API Pagination: Choosing Between Offset and Cursor-Based

During a recent refactor of our internal user activity feed, we hit a wall where simple LIMIT and OFFSET queries were causing 2-second response times on a table with roughly 4.2 million rows. As the database scanned through thousands of skipped records, the CPU spiked, and the UX degraded significantly. It was a classic lesson in why REST API pagination requires more than just a quick page=1 implementation.

The Problem with Offset Pagination

Offset pagination is the industry standard for simplicity. You tell the server GET /items?limit=20&offset=100, and it executes a SELECT * FROM items LIMIT 20 OFFSET 100. It’s intuitive, easy to implement in almost any framework, and works perfectly for small datasets where the order of items is static.

However, it breaks down under two common conditions:

1. Performance: The database must read all offset rows before discarding them. As your index grows, the cost of skipping those rows increases linearly.
2. Data Inconsistency: If a user deletes an item on page 1 while you’re viewing page 2, the item previously on page 2 shifts up to page 1. You end up seeing the same item twice or skipping one entirely.

Evaluating Your Strategy

When we hit that performance bottleneck, we had to rethink our approach. We initially tried to optimize the SQL with covering indexes, but the fundamental nature of the OFFSET clause made it impossible to avoid the deep-scan penalty. That’s when we pivoted to cursor-based pagination.

Implementing Cursor-Based Pagination

Unlike offset, cursor-based pagination doesn't rely on skipping records. Instead, it uses a pointer (the cursor) to identify the last record seen by the client. The next request asks for the "next 20 items after this specific ID or timestamp."

Here is a simplified example of how this looks in a request: GET /items?limit=20&after=eyJpZCI6MTA0NX0=

The after parameter contains an encoded string (usually Base64) representing the identifier of the last item in the previous set. This allows the database to perform a direct index lookup: SELECT * FROM items WHERE id > 1045 ORDER BY id ASC LIMIT 20.

This approach is significantly faster because the query complexity remains constant, regardless of how deep you are in the result set. If you're building high-traffic systems, cursor-based pagination for high-performance API design is almost always the right move.

When to Stick with Offset

I don't advocate for cursor-based pagination in every scenario. If you're building a dashboard where users need to "jump" to page 50, cursor-based strategies make that difficult because they aren't designed for random access.

If your dataset is small—say, a few hundred items—the complexity of managing cursor encoding, decoding, and state isn't worth the overhead. In these cases, focus on API field projection: reducing payload size and server load instead, as reducing the size of the response is often a bigger win than changing the pagination logic.

Practical Tips for Implementation

If you choose the cursor route, keep these tips in mind:

1. Opaque Cursors: Always treat your cursor as an opaque string. Don't expose the raw primary key ID in the URL. If you decide to change your sorting logic later, you won't break client-side logic.
2. Deterministic Ordering: Always include a tie-breaker in your ORDER BY clause, like created_at followed by id. If two items have the same timestamp, a non-deterministic sort will cause items to jump around, defeating the purpose of the cursor.
3. Handle Errors: If a user provides an invalid or expired cursor, return a clear 400 Bad Request with a meaningful error message. Using a standard mastering Laravel exception handling for robust API responses pattern helps ensure your clients can handle these errors gracefully.

Final Thoughts

Moving away from OFFSET was a painful migration, but it saved our database from unnecessary IO overhead. Next time, I would start with cursor-based pagination from day one for any collection that could grow beyond a few thousand entries. It’s more work upfront, but it prevents the "page 50 is slow" bug that eventually haunts every growing application.

Frequently Asked Questions

Q: Can I support jumping to specific pages with cursor-based pagination? A: No, cursor-based pagination is inherently sequential. If you require random access to pages, you might need to stick with offset or provide a separate search/filter mechanism.

Q: Is it possible to use cursor-based pagination with non-integer IDs like UUIDs? A: Absolutely. You can use any sortable field as a cursor, such as a timestamp or a combination of fields. Just ensure your index covers the sort columns.

Q: How do I handle sorting by different fields? A: Your cursor needs to represent the state of the sort. If the client changes the sort order, the old cursor is effectively useless, so you should invalidate it and start the request from the beginning.