Offset pagination is a deferred performance problem. Most production apps have already deferred it.
Every CRUD app ships with pagination. And almost every CRUD app ships with the same implementation: LIMIT 20 OFFSET 0, LIMIT 20 OFFSET 20, LIMIT 20 OFFSET 40. The first 10 pages are fast. Your load tests pass. You ship it, you move on.
You will not notice the problem for months, possibly years. Then your data grows, your background sync jobs start crawling, your API responses start timing out on page 300 and the first developer to investigate will find that the underlying query was always wrong. It just wasn’t wrong enough to be visible yet.
That’s the specific kind of problem that’s easy to miss: one that degrades linearly with scale rather than breaking at a threshold. Nobody files a bug report. Queries just get a little slower every week, until one day they’re very slow.
Why OFFSET Costs What It Costs
The behavior of LIMIT/OFFSET is not a bug in PostgreSQL or MySQL. It's how relational databases fundamentally work, and understanding it makes the performance problem obvious.
When you run:
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;The database does not jump to row 10,001. There’s no magic index that maps offset values to disk locations. Instead, it scans the rows in order from the beginning, counts 10,000 of them, discards every single one and then returns the next 20.
You asked for 20 rows. The database touched 10,020.
The cost of this query is not proportional to your LIMIT. It's proportional to your OFFSET. Double the offset, double the work. Hit page 500 with a page size of 20 and you're asking the database to process 10,000 rows to return 20. Hit page 2,000 and it's 40,000 rows processed to return 20. The ratio keeps getting worse.
Adding an index on your ORDER BY column helps, but it doesn't solve the problem. Even with a perfect index, the database has to traverse the index from the beginning and count its way to the offset position. The index makes it faster. It doesn't make it constant time.
The Numbers That Put This in Perspective
Shopify ran into this problem at scale with their REST API. Their engineering team published a detailed breakdown of what they found: accessing large product catalogs through offset-based page parameters was hundreds of times slower than using cursor-based navigation at equivalent depths. On average across real production traffic, switching from offset to cursor pagination on their /admin/products.json endpoint made requests about 11x faster. In worst-case scenarios, deep page requests were 400 times faster with cursors.
That range (11x to 400x) is important. At low page depths, the difference is small. At high page depths, it’s enormous. Most of your users never go to page 200. But your integrations do. Your data sync jobs do. Your admin export tools do. Your customer success team’s “show all orders” view does. The deeper the page, the worse the offset approach looks.
The Problem You Didn’t Know Was Already Happening
There are two failure modes from offset pagination that are subtler than slow queries.
The first is row drift. Offset pagination assumes the result set stays stable between page fetches. It doesn’t. If a user is browsing a product list and a new product gets inserted while they’re on page 3, every subsequent page shifts by one. They’ll see a duplicate on page 4 or skip a record entirely depending on the sort order and where the insert landed. This is not theoretical. It happens every time your data mutates while someone is paginating through it.
The second is background job scale. You might reasonably argue that most of your users never go past page 10. That’s probably true. But background processes don’t paginate like humans. A sync job that pulls all orders from your API to an external system doesn’t stop at page 10. It runs to completion, however deep that is. As your order count grows, those sync jobs get linearly slower. They don’t error out. They just take longer and longer and longer, until one day a sync job that used to take two minutes takes twenty, and then forty, and then starts timing out.
How Cursor Pagination Actually Works
Cursor-based pagination, also called keyset pagination, works differently. Instead of telling the database “skip the first 10,000 rows,” you tell it “give me the next 20 rows after this specific row.”
Concretely, a cursor is an encoded reference to the last record you returned. On the next request, you decode that cursor, use the values to build a WHERE clause that targets rows after that position, and apply a fresh LIMIT. The database uses the index on your sort column to jump directly to the right position. No row counting. No discarding.
The query for page 2 looks like this:
SELECT * FROM orders
WHERE (created_at, id) < ('2026-01-15 14:22:00', 99872)
ORDER BY created_at DESC, id DESC
LIMIT 20;No OFFSET. The cost of this query is the same whether you're fetching page 2 or page 2,000. Performance is constant regardless of depth. Row drift disappears because you're tracking position by value, not by count.
Doing This in Laravel
Laravel has had cursor pagination since version 8.41, released in 2021. If you’re not using it, it’s a one-word API change.
The offset version:
$orders = Order::orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->paginate(20);This generates LIMIT 20 OFFSET X for some offset that grows with each page. The cursor version:
$orders = Order::orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->cursorPaginate(20);Same API shape, same result set structure, fundamentally different query. The response includes next_cursor and prev_cursor values instead of page numbers. Clients pass those cursors as a query parameter on the next request. Laravel handles the encoding and decoding.
The cursor approach requires that your ORDER BY clause includes at least one unique column, or a combination of columns that together produce a unique ordering. Using created_at alone isn't sufficient because multiple records can share the same timestamp. Adding id as a tiebreaker makes the ordering unambiguous, which is what makes the cursor reliable.
Your Eloquent scope can stay the same. Your resource transformations stay the same. The response shape changes slightly (cursors instead of page numbers), which is the main reason teams don’t make this switch without thinking: any client that expects numeric page navigation will need updating.
The Trade-offs Are Real
Cursor pagination has genuine limitations. You can’t jump to an arbitrary page. There’s no “go to page 50” functionality. You navigate forward and backward from your current position, and that’s it.
For end-user interfaces with visible page numbers, this is a real constraint. Users who expect to see “Page 1 of 847” and jump to any page won’t get that experience from a cursor-based API. If you have a UI where arbitrary page navigation matters to users, offset pagination is the right call for that specific feature.
But most APIs that get paginated at scale are not serving direct human navigation. They’re serving:
- Sync integrations that pull full datasets
- Admin export tools that iterate through everything
- Background jobs that process records in batches
- Mobile apps with infinite scroll
- Any infinite scroll UI where “jump to page 300” is not a thing users do
For all of these cases, cursor pagination is strictly better. The absence of page numbers is not a problem because the use case doesn’t involve page numbers.
Migrating an Existing API
If you already have an API with offset pagination and external clients depending on it, the migration path matters.
The cleanest approach is versioning. Add /v2/orders with cursor pagination alongside /v1/orders with offset pagination. Document the performance characteristics of both. Give integrators a timeline to migrate. Offset pagination under v1 continues working while v2 becomes the default recommendation.
If versioning isn’t feasible, you can run both pagination types behind a query parameter. A cursor query parameter triggers cursor-based pagination. Requests without it fall back to offset. This lets old clients continue working while new integrations adopt the more efficient path.
What you shouldn’t do is switch the underlying query on an existing endpoint without coordination. Cursor-based responses have a different shape than offset-based responses. Clients consuming page numbers will break if you silently swap to cursors.
The Part Nobody Talks About
Here’s what makes this problem persistent: the teams most affected by it often don’t know it’s happening.
Offset pagination doesn’t produce errors. Queries don’t fail. Response times increase gradually, a few milliseconds at a time, as your dataset grows. The performance profile of your database degrades slowly enough that no individual deployment looks like the cause. By the time someone is investigating why sync jobs take 40 minutes, the root cause is six months of organic data growth on top of a query pattern that was always wrong.
The fix is cheap. cursorPaginate() instead of paginate() is a one-word change if you're starting fresh or if your clients are internal. The cost of fixing it goes up every month you wait, because more external integrations get built against the offset API, more clients have to migrate and the data set itself is larger when you finally address it.
The query that works fine today is the same query that will be slow in a year. The only difference is how much data sits between offset zero and wherever your sync jobs currently are.
What to Actually Do
If your app is early-stage and your clients are under your control: switch to cursor pagination now. One word change, free performance ceiling.
If you have external integrations already built against an offset API: version the endpoint. Run cursor pagination on v2, give integrators a migration window and set a deprecation date for v1.
If your use case genuinely requires arbitrary page navigation (not just infinite scroll): keep offset for that specific feature and switch everything else. The two approaches can coexist in the same application.
The thing to stop doing is assuming that performance you can’t measure right now is performance you don’t have to think about. Offset pagination’s cost is deferred, not absent. The dataset you ship with and the dataset you’re running a year from now are different things, and the query that serves one well doesn’t necessarily serve the other at all.
