I remember sitting in a windowless server room at 2:00 AM, watching a dashboard turn a violent shade of red because a junior dev thought `LIMIT` and `OFFSET` were the silver bullet for everything. They had followed a tutorial to the letter, but the tutorial didn’t mention what happens when your table hits ten million rows and every “page 500” request triggers a full index scan that chokes your database to death. Most guides focus on the happy path where your dataset is small and your latency is negligible, but if you actually want to learn how to paginate an api properly, you have to stop looking at the successful response and start looking at the resource exhaustion that follows a bad implementation.
I’m not here to give you another sanitized lecture on RESTful patterns or theoretical best practices. Instead, I’m going to show you the actual mechanics of cursor-based pagination and why your current offset logic is a ticking time bomb for your production environment. We’re going to skip the fluff and focus on the edge cases—the race conditions, the deep paging performance cliffs, and the inevitable drift that happens when data is being written while you’re trying to read it.
Table of Contents
The Offset Trap Why Your Database Performance Is Dying
Most tutorials show you `LIMIT 10 OFFSET 500` and call it a day. It looks clean, it’s easy to implement, and it works perfectly in your local development environment where you have exactly forty rows of dummy data. But I’ve spent enough time maintaining legacy payment gateways to know that “easy” is usually a debt you’ll pay back with interest later.
The problem is that the database doesn’t magically jump to row 500,001. It has to scan through every single preceding row, counting them one by one, just to figure out where your requested slice begins. When you’re handling large datasets in API design, this linear scan turns your query into a slow, agonizing crawl. You’ll see your CPU spikes and your query execution times climb steadily as the dataset grows. It’s a silent killer; your system stays upright during staging, but the moment you hit production scale, the offset vs cursor pagination performance gap becomes a canyon. You aren’t just fetching data anymore; you’re forcing the engine to do massive amounts of useless work just to throw it away.
Preventing Duplicate Results in Pagination When Data Shifts
The real headache starts when your data isn’t static. If you’re using offset-based pagination, you’re essentially betting that nothing happens to your database between requests. But in a high-concurrency payment system, things are always moving. If a user deletes a record or a new transaction lands on page one while a client is mid-fetch, the entire result set shifts. The client requests page two, but because an item was removed from page one, the last item from the previous set slides down into the first slot of the new one. Suddenly, your frontend is showing the same transaction twice, and your users are questioning if they’ve been double-charged.
This is why preventing duplicate results in pagination requires moving away from simple row counts and toward stateless pagination techniques. Instead of telling the database to “skip X rows,” you need to tell it to “start after this specific ID.” By using a unique, sequential identifier—like a timestamp or a primary key—as your anchor, you ensure that even if the underlying data shifts, the pointer remains fixed to a specific point in the stream. It’s the only way to maintain a consistent view of the data without forcing the client to manage complex state.
Beyond the Happy Path: Five Ways to Stop Breaking Your Pagination
- Stop treating your limit and offset as magic numbers; you need to enforce a hard upper bound on the maximum page size, or someone will eventually request a million rows and watch your memory usage spike into the stratosphere.
- Switch to cursor-based pagination for any high-frequency write tables, because if you’re using offsets, a single new transaction inserted at the top of the table will cause your users to see the same record twice on consecutive pages.
- Don’t return a total count in every response if you can avoid it; running a `SELECT COUNT(*)` on a massive, indexed table just to tell the frontend how many pages exist is a silent performance killer that scales poorly.
- Embed the next cursor directly in your response metadata rather than making the client calculate it, because if you let the client manage the state of the “next page,” they will eventually pass an invalid or stale pointer that crashes your query.
- Design your API to be idempotent regarding the cursor; if a client retries a request due to a network hiccup, your backend should be able to handle that specific cursor without shifting the window or returning unexpected data.
The Hard Lessons
Stop relying on OFFSET; if your dataset is growing, your queries will eventually become a bottleneck that drags your entire service to a crawl.
Use cursor-based pagination if you want a stable view of your data; otherwise, users will see the same record twice just because a new row was inserted upstream.
Design for the edge cases from the start, because the “happy path” doesn’t account for the moment your database hits a million rows and your response times spike.
## The Illusion of the Happy Path
Tutorials love to show you a clean `limit` and `offset` working perfectly on a local machine with fifty rows of dummy data, but they never show you the midnight pager call when a client hits your endpoint with a massive offset and your database spends ten seconds scanning through millions of discarded rows just to give them the next ten.
Pernille Askeland
Beyond the Happy Path
At the end of the day, proper pagination isn’t about making your API look pretty; it’s about preventing a slow, agonizing death by a thousand `OFFSET` calls. We’ve looked at why the standard approach fails when your dataset grows and why shifting data makes your results look like a glitchy mess. If you want a system that survives real-world usage, you have to stop treating your database like a magic box and start treating it like a finite resource. Stick to cursor-based pagination where you can, avoid the unbounded result sets, and always, always account for the data moving under your feet while the user is still reading the first page.
I’ve spent two decades cleaning up the wreckage of “simple” implementations that worked perfectly in a local dev environment but crumbled under the weight of actual production traffic. The difference between a junior developer and a senior one is often just the ability to anticipate the breakage before the first line of code is even written. Don’t just build for the successful request; build for the moment the database gets overwhelmed and the connection pool starts drying up. That is where the real engineering happens.
Frequently Asked Questions
If I switch to cursor-based pagination to fix the performance issues, how do I handle the frontend requirement for "jumping" to a specific page number?
You can’t. If you switch to cursor-based pagination, you are explicitly trading the ability to jump to page 47 for stability and speed. It’s a fundamental architectural choice. If your product manager insists on page numbers, they are asking for the very performance bottlenecks we just discussed. My advice? Tell them to implement “Load More” or infinite scroll. If they won’t budge, you’re better off sticking with offset pagination and just optimizing your indexes.
How much overhead does adding a sort key to my cursor actually add to the database load when I'm dealing with millions of rows?
If you’ve indexed your sort key correctly, the overhead is negligible. We’re talking about a B-tree lookup, which is $O(log n)$. Even with ten million rows, that’s only about 24 comparisons. The real cost isn’t the CPU cycles; it’s the disk I/O if your index doesn’t fit in memory. But if you’re worried about the weight of a single extra column in a cursor, you’re likely overlooking the much larger disaster of a sequential scan.
What happens to my pagination logic if the underlying data is being soft-deleted or archived while a user is mid-session?
The “ghost row” problem. If you’re using offset-based pagination, a soft-delete is a silent killer. A user requests page two, but because a record on page one was flagged as deleted, the entire dataset shifts up. Suddenly, the record that should have been at the end of page one slides into the start of page two. The user sees the same item twice, or worse, skips a row entirely. It’s messy, and it’s exactly why I prefer cursor-based logic.
