When LIMIT/OFFSET Stops Working: Choosing Between Offset and Cursor Pagination

I recently worked on improving the performance of a large report, and one of the first steps seemed straightforward: add pagination.

My initial implementation used the classic LIMIT and OFFSET approach. That was the obvious choice for a first version. It is simple, familiar, easy to explain, and fits how many people think about pagination: page 1, page 2, page 3, and so on.

But after shipping that first pass, I ran into a problem.

As the dataset grew and the offset got larger, the query became increasingly expensive. This was not a small table with a lightweight lookup. The report involved a large amount of data, multiple filters, ordering, and the kind of access pattern that is common in reporting screens. In that context, offset-based pagination started to become a bottleneck instead of a convenience.

That experience was a good reminder that pagination is not just a frontend detail. It directly affects query cost, database behavior, and user experience.

The appeal of offset pagination

There is a reason LIMIT/OFFSET is so common.

It is easy to implement, easy to understand, and works well for many systems. If a user wants page 12, offset pagination maps naturally to that mental model. It is also a good fit for small or medium datasets, internal tools, and admin screens where the cost of skipping rows is still low enough that nobody notices.

A typical query looks like this:

SELECT *
FROM payments
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

At first glance, that feels efficient enough. The request is only asking for 50 rows, so it is tempting to think the database is doing a small amount of work.

That is the trap.

Why large offsets get expensive

The main issue with offset pagination is that the database usually does not jump to row 10000 for free.

To return the requested page, it often still has to walk through, sort, and discard the rows that come before it. The exact cost depends on the query plan, indexes, filters, joins, and ordering rules, but the important point is the same: skipped rows are still work.

So a query like:

LIMIT 50 OFFSET 10000

does not mean "read 50 rows starting at position 10000" in some magical constant-time way.

It more often means:

1. Find the ordered result set.
2. Move through the first 10000 rows.
3. Discard them.
4. Return the next 50.

That may be acceptable on small datasets. It becomes much more painful when the query is already expensive because of filters, joins, or sorting requirements.

In my case, that was exactly the problem. Pagination reduced the amount of data returned to the client, but it did not reduce the database work nearly as much as I expected once users moved deeper into the result set.

Cursor pagination solves a different problem

Cursor pagination approaches the problem from another angle.

Instead of asking the database to skip a fixed number of rows, the client sends a reference to the last item from the previous page. The next query asks for rows that come after that item according to a stable ordering.

That changes the database's job. It no longer needs to count and discard a large prefix of the result set. It can continue from a known position.

Using the same example, a cursor-based query can look like this:

SELECT *
FROM payments
WHERE created_at < :last_created_at
ORDER BY created_at DESC
LIMIT 50;

This is usually a much better fit for sequential navigation through large datasets, especially for reports, feeds, logs, audit trails, and high-volume APIs.

The key advantage is not that cursor pagination is more modern. The advantage is that it lets the database seek from a known boundary instead of scanning and discarding rows it does not need to return.

Stable ordering matters more than people think

One detail that matters a lot in real systems is cursor stability.

Using only created_at is often not enough, because multiple rows can share the same timestamp. If the ordering is not fully deterministic, pagination can produce duplicates, gaps, or inconsistent results between pages.

That is why a cursor usually needs a tie-breaker field, often the primary key:

SELECT *
FROM payments
WHERE
  created_at < :last_created_at
  OR (
    created_at = :last_created_at
    AND id < :last_id
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

This pattern is often called keyset pagination or seek pagination.

The naming matters less than the behavior. What matters is that the query follows the same stable ordering in both the WHERE clause and the ORDER BY clause, so the database can continue from an exact boundary.

To support that properly, the index should match the access pattern:

CREATE INDEX idx_payments_created_at_id
ON payments (created_at DESC, id DESC);

Without that alignment between ordering and indexing, cursor pagination loses much of its advantage.

Offset pagination is not wrong

It is easy to overcorrect and turn this into "offset bad, cursor good." That is not the real lesson.

Offset pagination is still useful in many cases:

• it is simple to build
• it supports numbered pages naturally
• it allows direct jumps to a specific page
• it is often good enough for small or medium datasets
• it can be perfectly reasonable for back-office tools and low-volume screens

If the query is cheap and the data size is moderate, offset pagination may be the right trade-off. The simplest solution is often the best one when the system does not need more.

The problem starts when people keep the same pagination strategy after the access pattern and data volume have changed.

Cursor pagination has real trade-offs too

Cursor pagination is usually better for large sequential reads, but it is not free.

It introduces some real constraints:

• it is less convenient when the UI depends on page numbers
• direct jumps to page 20 are not natural
• total page counts become less straightforward
• cursor design has to be deliberate
• ordering must be stable
• indexes need to support the exact traversal pattern

In other words, cursor pagination improves performance by being more explicit about how the data is traversed. That is a strength, but it also means there is more design work involved than adding OFFSET.

The practical rule I took from this

The most useful conclusion for me was not "always use cursor pagination."

It was this:

Choose pagination based on how the data is read, not just on how easy the first query is to write.

If the use case looks like traditional page navigation over a relatively small result set, offset pagination is still a solid default.

If the use case looks like a large report, an activity feed, an audit log, or an API that moves through a lot of ordered data sequentially, cursor pagination should be one of the first options you consider.

That is especially true when the query already has meaningful cost from filtering, sorting, or joins. In those cases, skipping rows is not just a cosmetic implementation detail. It can become the expensive part of the operation.

Pagination is part of query design

One thing I like about this topic is that it exposes a broader engineering habit.

Pagination often gets discussed at the API or UI layer:

• Should the endpoint return page and pageSize?
• Should the frontend show page numbers or a "load more" button?
• Should the response include a total count?

Those are valid questions, but they are not enough.

Pagination also belongs to query design.

It shapes how the database reads data. It shapes how indexes should be built. It shapes how stable ordering is defined. It shapes whether deeper navigation stays fast or gradually falls apart.

That is why I think this distinction matters.

LIMIT/OFFSET is a good default for simple cases. But when the dataset grows and the access pattern becomes more demanding, cursor-based pagination stops being an optimization trick and starts being an important design decision.

That was the real lesson from this report work for me. The pagination strategy was not just about dividing results into pages. It was about deciding how the database would pay for every page after the first one.