OFFSET Pagination Doesn’t Scale. Here’s What Does

Figure 1 — Graph showing the comparison between three pagination strategies (Local DB)

You are building a web app, and it comes to a point where you have to list all items (properties, user transactions, documents, etc.), you can’t just throw a huge pile on your user; rather, you paginate, which is a better UX to help them navigate better and find things easily.

But what comes to the mind of a developer building an application that needs pagination? The old SQL syntax LIMIT and OFFSET

SELECT id, title, created_at
FROM tracks
ORDER BY id DESC
LIMIT 10 OFFSET 20;

This simply tells the database engine to start from row 20 and fetch me the next 10 music tracks, and let vibe.

LIMIT 10 OFFSET 50000;
This tells the database engine to start from row 50000 and fetch me the next 10 music tracks, and let vibe.

I bet at this point neither your user nor your database engine at this point is vibing.

THE BOTTLENECK OF TRADITIONAL PAGINATION

A database engine can not teleport straight to row 50,000 and fetch the next 10 rows. What happens is that the database reads the first 50,010 rows and picks the last 10 rows. Can you see how expensive such a query is? 50,010 rows scan just to return 10 rows.

So as your page number/depth increases, your query execution time increases linearly — O(n). Page 1 loads in 10ms, Page 100 in 170ms, Page 50000 in 1300ms.

SOLUTION: CURSOR PAGINATION

This is simply like the real vibe, as each request for a new page takes roughly the same time, irrespective of the page number/depth — O(1). Page 1 and page 50,000 respond in under 10 milliseconds. That flat green line in Figure 1 is cursor pagination refusing to slow down, no matter how deep you go.

But the truth is that cursor pagination doesn’t really have to do with pages; it has to do with the cursor (this indicates the ID of the last item the user saw).

To make this work, you need a primary key/ID that is chronologically sortable and strictly unique. BigSerial can handle this for a single server. But Snowflake ID and its variation UUID7 also handle this gracefully for a distributed system (as they embed the exact milliseconds of creation into the ID).

Because the ID is sorted, the database’s B-Tree index can instantly jump to that exact location on the disk. It skips the 50,000 rows instantly without reading them. The time complexity becomes O(1) — constant, lightning-fast response times, no matter how deep the user scrolls.

The Implementation: Raw SQL

Here is the exact architectural blueprint for implementing this purely in SQL.

Step 1: The Initial Query (Page 1)

When the user first opens the app, you do not have a cursor yet. You just ask for the 10 most recent rows.

-- Fetching the first batch of data
SELECT id, title, created_at
FROM tracks
ORDER BY id DESC
LIMIT 10;

Your API sends these 10 rows to the frontend. The frontend looks at the id of the very last item in that list (let’s say it is 018dc336–1234–7567–89ab-cdef01234567) and saves it.

Step 2: The Cursor Query (Next Page)
When the user scrolls to the bottom of the screen, the frontend makes a new request and passes that saved ID back to your backend as the cursor.

Instead of using OFFSET, you use a WHERE clause to ask the database for strictly older rows (less than) the cursor if sorting from newest to oldest.

-- Fetching the next batch using the cursor
SELECT id, title, created_at
FROM tracks
WHERE id < '018dc336–1234–7567–89ab-cdef01234567'
ORDER BY id DESC
LIMIT 10;

Why this is blazingly fast: The database engine looks at the WHERE id <…clause. It traverses the B-Tree index, does a direct index seek directly to 018dc336–1234–7567–89ab-cdef01234567, and just scoops up the next 10 blocks of data. Zero discarded rows. Zero wasted I/O.

APPLICATION

This is what social media platforms use when you scroll through your feeds, it gives you the illusion of endless scrolling until you hit your very first post. It is also used in bank apps when browsing through transaction history. Discord uses it while you scroll upward for older messages.

LIMITATION

Cursor pagination has one rigid constraint, and that is you cannot jump to a specific page. There is no “go to page 40.” You can only go next.

For infinite scroll interfaces, this is not a limitation at all, as nobody wants to jump to page 40 of their Twitter feed. But for certain use cases, page numbers are genuinely necessary:

• A financial auditor who needs to jump to a specific date range in a transaction report
• An e-commerce product search page Do not panic about going to the old friend OFFSET PAGINATION, because there is a better shortcut that still uses OFFSET but in a more intuitive way.

Figure 2 — Amazon search page uses page numbers

DEFERRED JOIN

The traditional OFFSET PAGINATION reads through 50,010 full rows (each row has id, created_at, audio file url, cover image url, etc.). That’s a lot of fat data to scan and throw.

But with a deferred join, you force the database engine to scan only the lightweight index, which is the skinny version of your data containing only the ID. Once you have the 10 IDs you actually need, you use an INNER JOIN to fetch the full row data for just those 10 records.

 --Step 1: The inner query ONLY reads the lightweight 'id' from the index (50,010 of them).
 -- Step 2: The outer query joins those 10 IDs back to the main table.
SELECT main_table.id, main_table.title, main_table.cover_image_url, main_table.created_at
FROM tracks AS main_table
INNER JOIN (
  SELECT id
  FROM tracks
  ORDER BY id DESC
  LIMIT 10 OFFSET 50000
) AS skinny_index
ON main_table.id = skinny_index.id
ORDER BY main_table.id DESC;

As you can see in Figure 1, deferred join (orange line) is significantly faster than naive offset at deep pages, though it still degrades as pages get deeper, unlike offset pagination, which degrades so badly. It is the ultimate compromise: page numbers for the UX, optimised index scan for the architecture.

WHICH SHOULD ONE USE

Cursor pagination for infinite scroll, social media feeds, chat history, and notification page

Deferred join for admin dashboards, search results

Offset Pagination for demos, small datasets under 100k rows (to prevent over-engineering)

The chart at the top of this article is not theoretical. It is real query times measured against a 1 million row PostgreSQL transaction table seeded with financial transaction data.

The local benchmark gives you the clean theoretical curve. But when I deployed the same demo against a live Aiven PostgreSQL instance, something more interesting happened, but at the end of the day, cursor pagination still stays nearly flat while offset shots upward.

Figure 3 — This comparison is from my live demo using Aiven PostgreSQL database

I built a live interactive demo so you can see this difference yourself: the three pagination strategies, one million transaction records, and real query times updating as you click next.

You can interact with the live demo here: https://cursor-pagination-delta.vercel.app/
Github Repo: https://github.com/opesam42/cursor-pagination

When you run yours, you might notice offset starts higher on page 1 than expected. That is PostgreSQL’s buffer cache warming, as the first query has to read from disk before the data pages are loaded into memory. Subsequent queries benefit from cached data.

Author: Gbenga Opeyemi
Website: https://gbengaopeyemi.vercel.app/
Medium: https://medium.com/@opesam42
GitHub: https://github.com/opesam42
LinkedIn: https://www.linkedin.com/in/opeyemi-oluwagbemiga-2ba61423b/
X: https://x.com/gbengaopeyemi04