Stop Using OFFSET: Faster Pagination with Cursors

Most of us start building pagination using the classic LIMIT and OFFSET combo. It is the default approach taught in almost every web development tutorial. You get an array of items, pass in page=2, multiply by your page size, and you have your offset. It works perfectly on your local machine, and it runs great in production during the first few months.

But as your application grows and your database tables accumulate millions of rows, something breaks. Users start complaining that navigating to older pages or scrolling deep into an infinite feed feels sluggish. If you check your database logs, you will likely see those simple pagination queries grinding your database to a halt.

Let's talk about why OFFSET doesn't scale, and how Cursor Pagination (also known as Keyset Pagination) solves the problem elegantly.

The Hidden Cost of OFFSET

To understand the problem, we have to look at how relational databases like PostgreSQL or MySQL execute an OFFSET query.

Let's say you write a query like this:

SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

You might assume the database engine simply jumps to the 100,000th row and grabs the next 20. Unfortunately, that is not how it works. The database does not magically know where the 100,000th row lives. To fulfill this query, it has to scan, read, and then discard the first 100,000 rows just to fetch the 20 rows you actually wanted.

This means the performance of OFFSET degrades linearly. Page 1 is lightning fast. Page 10 is fine. Page 5,000 is going to scan a massive chunk of your disk, evicting useful data from memory caches, and spiking your CPU usage. For a high-traffic app, this is a silent killer.

Enter Cursor Pagination

Instead of telling the database "skip 100,000 rows," cursor pagination tells the database "start fetching right after this specific item."

A "cursor" is simply a pointer to a specific row in your dataset. Usually, it is a unique, sequential column like an auto-incrementing id or a created_at timestamp.

When you fetch the first page, the query looks standard:

SELECT * FROM articles
ORDER BY id DESC
LIMIT 20;

You take the id of the last item in that result set and pass it back to the client. This is your cursor. When the user scrolls down to request the next page, they send that cursor back. Your next query looks like this:

SELECT * FROM articles
WHERE id < 15234 -- The cursor from the last page
ORDER BY id DESC
LIMIT 20;

Notice what is missing? There is no OFFSET. If the id column is indexed, the database engine uses the B-Tree index to jump directly to the row with ID 15234 and reads the next 20 rows. It doesn't matter if the user is on page 2 or page 10,000; the query executes in constant time (O(1) relative to the offset).

Designing the API

When building modern APIs, it is best practice not to expose the raw database columns directly as your cursor. Instead, you can encode the cursor in Base64.

For example, instead of ?last_id=15234, your API should accept ?cursor=eyJpZCI6MTUyMzR9 (which is just {"id":15234} encoded).

This gives you flexibility. If you ever need to change the underlying implementation—perhaps switching the cursor from an ID to a timestamp—you can do so without breaking older client applications. It also prevents users from trying to manually guess or iterate the IDs.

The Sorting Trap: Tie-Breakers

Cursor pagination is not without its challenges. The biggest gotcha happens when you try to sort by a column that is not unique.

Let's say you want to sort articles by views. You might write:

SELECT * FROM articles
WHERE views < 5000
ORDER BY views DESC
LIMIT 20;

What happens if there are fifty articles that all have exactly 5,000 views? The database cannot guarantee a consistent order. Some items might be skipped entirely, while others might show up twice across different pages.

To fix this, you must always include a unique column as a tie-breaker. The combination of your sort column and the unique column becomes your new, composite cursor.

SELECT * FROM articles
WHERE (views, id) < (5000, 15234)
ORDER BY views DESC, id DESC
LIMIT 20;

Your API cursor would then encode both values: {"views": 5000, "id": 15234}. This guarantees deterministic sorting, no matter how many duplicate values exist in the primary sort column.

The Trade-offs

Nothing in software engineering is free. While cursor pagination fixes performance, it forces you to give up the ability to jump to arbitrary pages.

With OFFSET, rendering a classic pagination UI [1] [2] ... [8] [9] is trivial. With cursors, you cannot easily jump from page 1 directly to page 9 because you don't know the cursor for page 9 until you fetch page 8.

However, ask yourself: how often do users actually click "Page 45"? Modern interfaces—especially infinite scroll feeds on mobile apps, or "Load More" buttons—rarely need arbitrary page jumping.

The Verdict

Don't over-engineer from day one. If you are building an internal admin dashboard with a few thousand records, stick to OFFSET. It is simple, effective, and supports standard table pagination out of the box.

But if you are building an infinite scroll feed, an API for external consumers, or working with massive datasets, drop OFFSET. Adopting cursor pagination early will save your database from collapsing under its own weight as your application scales.