Page 40,000 Should Not Be Slower Than Page One

I remember sitting in a dimly lit office three years ago, watching a production database crawl to a literal standstill because someone thought they could solve a scale problem with a simple `OFFSET` clause. The monitor was just a blur of spinning loaders, and the sysadmin was breathing heavily into his headset while the transaction logs swelled like a bruised limb. Most tutorials make it look like a breeze—just a few lines of SQL and you’re done—but they never tell you how to actually handle how to paginate large tables when the dataset grows into the millions and your queries start timing out.

I’m not here to walk you through the “happy path” where everything works perfectly on your local machine with fifty rows of dummy data. I want to talk about the uncomfortable reality of database locks, index fragmentation, and why your clever implementation will eventually choke your connection pool. I’m going to show you the specific, messy patterns that actually work in high-concurrency environments, focusing on what happens when the data gets heavy and the easy solutions fail.

The Offset Trap Watching Your Database Performance Optimization Tank

Most tutorials start with `LIMIT 10 OFFSET 500`. It’s clean, it’s intuitive, and it works perfectly in your local development environment where you have maybe fifty rows of dummy data. But I’ve spent enough time maintaining legacy payment engines to know that “intuitive” is often a synonym for “expensive.”

The problem is that `OFFSET` isn’t a pointer; it’s a set of instructions to skip. When you ask for page 1,000, the database doesn’t just jump to that spot. It has to fetch all the preceding rows, count them, and then discard them just to give you the ten you actually asked for. As your table grows, you’ll watch your database performance optimization efforts vanish because the engine is doing an absurd amount of wasted work.

I’ve seen production queries that were perfectly valid SQL suddenly start timing out once a client’s transaction history hit a certain threshold. You aren’t just fetching data anymore; you’re forcing the engine to scan through massive chunks of the index for no reason. This is the classic trap of limit and offset vs keyset pagination: one is easy to write, but the other is what actually keeps your service alive.

When Limit and Offset vs Keyset Becomes a Death Sentence

The problem with `LIMIT` and `OFFSET` isn’t just that it’s slow; it’s that it’s fundamentally dishonest about how much work the engine is doing. When you ask for `OFFSET 100000`, the database doesn’t magically jump to that row. It has to scan through every single one of those hundred thousand records, counting them one by one, just to throw them away and give you the 100,001st. It’s a massive waste of IO, and if you’re looking at your query execution plan optimization, you’ll see the cost scaling linearly with every page your user clicks.

This is where the shift from `limit and offset vs keyset` becomes a matter of survival for your service. In a keyset approach, you aren’t telling the database how many rows to skip; you’re telling it exactly where you left off using a unique identifier, like a timestamp or an ID. Instead of a blind scan, you’re performing a targeted seek. It turns a heavy, unpredictable crawl into a surgical strike, ensuring that fetching the millionth row is just as fast as fetching the first.

Five ways to stop your pagination from killing the production server

• Stop relying on `SELECT *`. If you’re pulling every column just to show a summary table, you’re wasting IO and memory for no reason. Only fetch the IDs and the specific fields you need to render the current view.
• Index your sort keys. If you’re paginating by `created_at` but that column isn’t indexed, the database has to perform a full table scan for every single page request. It’s not “slow”; it’s mathematically doomed.
• Avoid the “jumping data” bug by using deterministic ordering. If you sort by a non-unique column like `last_name`, the database might return rows in a different order between page 1 and page 2, making items appear twice or disappear entirely. Always tie your sort to a unique ID.
• Implement a “max limit” hard cap. Users (or malicious actors) will eventually try to pass `?limit=1000000` in the URL. If your code doesn’t intercept that and force it back down to a sane number, they will effectively perform a self-inflicted DDoS attack.
• Cache the count, but don’t obsess over it. Running `SELECT COUNT(*)` on a multi-million row table every time a user clicks “Next” is a waste of resources. Use an estimate from the database metadata or cache the total count for a few minutes instead.

The Cost of Ignoring the Edge Cases

Stop treating `OFFSET` like a magic wand; it’s a performance tax that grows heavier every time a user clicks “Next Page.”

If your data is constantly shifting—new transactions coming in or rows being deleted—standard pagination will serve your users duplicate or missing data, and no amount of frontend polish can fix that.

Keyset pagination (cursor-based) isn’t just a “nice-to-have” optimization; it’s the only way to keep your database from choking once your tables move past the trivial stage.

The Illusion of the Happy Path

Most tutorials show you pagination working perfectly with a hundred rows of clean, predictable data; they don’t show you the exact moment your database hits page 5,000 and the query execution time jumps from milliseconds to seconds because you’re asking it to scan everything it’s already read.

Pernille Askeland

The Cost of Getting It Wrong

At the end of the day, pagination isn’t just about moving through a list; it’s about managing how much weight your database is actually carrying. We’ve seen how the standard `OFFSET` approach feels seamless in a staging environment with a few hundred rows, only to turn into a resource-hogging nightmare once you hit production scale. If you rely on skipping rows, you aren’t just skipping data—you are forcing the engine to read and discard every single record leading up to your target. Moving toward keyset pagination or cursor-based methods isn’t just a “nice-to-have” optimization; it is the difference between a system that scales predictably and one that collapses under its own weight the moment your dataset grows.

I’ve spent too many late nights untangling performance bottlenecks that could have been avoided if the initial architecture had accounted for the inevitable growth of the data. Don’t wait for the monitoring alerts to start screaming before you decide to refactor your queries. Build with the assumption that your tables will eventually become massive, and write your code to handle that reality from day one. It’s much easier to implement a robust pagination strategy now than it is to perform open-heart surgery on a live payment system while your users are staring at a frozen screen.

Frequently Asked Questions

If I switch to keyset pagination, how do I handle users who need to jump to a specific page number instead of just clicking "next"?

You can’t. That’s the trade-off. If you want the performance of keyset pagination, you have to give up the “Jump to Page 42” button. Keyset pagination is a cursor, not a map; it knows where you are, but it has no idea how many rows exist behind you. If your product requirements demand specific page jumps, you’re stuck with the offset trap. You have to decide: do you want a button, or do you want a database that actually responds?

How much does my index strategy actually matter if I'm already using keyset pagination?

It matters more than you think. Keyset pagination isn’t magic; it’s just a way to avoid the offset scan. If you aren’t indexing the columns in your `WHERE` clause—specifically the one used for the cursor—the database still has to perform a full table scan to find your starting point. You’ll swap a slow scan for a fast one, sure, but if that index is missing, you’re just walking through the dark with a slightly better map.

What happens to my pagination logic when a record is deleted or inserted while a user is halfway through scrolling?

This is where the “happy path” tutorials fail you. If you’re using offset-based pagination, a single deletion mid-scroll shifts your index. The user clicks “Next,” and suddenly they’re seeing the same record twice—or worse, they skip a row entirely. It’s a ghost in the machine. If you want stability, you have to move to keyset pagination. You don’t paginate by page number; you paginate by the last seen ID.