DEV Community: OPEYEMI OLUWAGBEMIGA

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

OPEYEMI OLUWAGBEMIGA — Fri, 03 Jul 2026 10:38:27 +0000

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

Why Standard Indexes Fail: The Architecture of the Covering Index

OPEYEMI OLUWAGBEMIGA — Sat, 02 May 2026 06:16:39 +0000

In my last article, I broke down how and why to use indexes wisely for efficient lookups and data retrieval by identifying fields to index and which not to index. Can read that here.

But adding a standard index in some cases is half the battle.

Adding an index to a column creates a separate, organized B-Tree for that column. And at the bottom of the tree (leaf node) is the pointer. That pointer tells the database engine where the full row lives on the heap. The heap is the main table where the full rows live. It is unsorted, massive, and slow to search as it forces a full-table scan. Indexes help to make data retrieval faster.

Standard indexes make searching fast, but they introduce a hidden bottleneck. Let’s look at a real-world example to see how and when to use a Covering Index to fix it.

Spotify “Recently Played” Screen

The Problem: The “Recently Played” Feed

Think of any music streaming platform like Spotify or Apple Music. Every time you open the app, it instantly fetches your most recently played tracks, around 10 tracks initially.

The query looks like this:

SELECT track_id, played_at 
FROM listen_history 
WHERE user_id = '018dc336-1234...' 
ORDER BY played_at DESC 
LIMIT 10;

Method 1: No Index

Without an index, the query is forced to do a full-table scan. Let’s say Spotify has 100 million records on its listen_history table, which means for each query.

100 million rows will be scanned just to isolate this single user’s history
Then it is sorted in memory.
And then the top 10 i s returned. At scale, this doesn’t just increase response times. This manual RAM sort will choke your disk I/O, exhaust your connection pool, and eventually bring down your application with 500 server errors.

Execution Time roughly 217ms with no index as a result of the Parallel Sequential Scan with 1,000,000 rows scanned. (Nano Banana for sharpening text in the terminal)

Method 2: Using Standard Index

CREATE INDEX idx_user_history ON listen_history (user_id);

SELECT track_id, played_at 
FROM listen_history 
WHERE user_id = '018dc336-1234...' 
ORDER BY played_at DESC 
LIMIT 10;

Here, an index is added on user_id, and the database engine creates a new B-Tree for this column. So let's say a user has played 5000 different tracks after signing up. No need for a 100 million row scan. It takes O(log n) time to traverse through the B-Tree to get the records and their pointers (where n is the number of tracks played by the user).

Is it faster, right? But here is the hidden catch.

But the index only contains user_id. The query is asking for track_id and played_at. Because these columns are missing from the index, the database engine must use the pointers to jump from the B-Tree to the main heap to get the missing data.

You know what that means? 5000 physical jump to the Heap. And after the jump, sorting is done in the memory using the played_at column, and then it returns the top 10. This physical jump is what’s called the Heap Fetch, and this destroys I/O performance.

The standard index eliminates the Sequential Scan, but introduces a new bottleneck, which are the implicit Heap Fetch and heap sort in RAM. (Nano Banana for sharpening text in the terminal)

Method 3: Covering Index

CREATE INDEX idx_user_recent_plays 
ON listen_history (user_id, played_at DESC) 
INCLUDE (track_id);

SELECT track_id, played_at 
FROM listen_history 
WHERE user_id = '018dc336-1234...' 
ORDER BY played_at DESC 
LIMIT 10;

Look closely at this query:

The user_id and the played_at DESC are used to build the core structure of the B-Tree. With this, the data is sorted by user_id and then by played_at in descending order
The INCLUDE keyword bolts the track_id to the leaf nodes of the B-Tree

So when the SELECT query is run:

The database engine jumps to the user_id in the B-Tree.
And because the tree is pre-sorted using the played_at, no need to take all tracks and sorting; all the database engine does is to fetch the first 10 rows as it is exactly the 10 newest tracks.
It grabs the track_id from the leaf node, and returns the data to the user. It never jumps to the Heap. It never sorts data in memory. That is why it is called an Index-Only Scan, and it executes at the speed of memory.

The Covering Index doesn’t require the Heap Fetch and in-memory sorting isn’t required, reducing execution time to a blistering 0.106ms.
The magic. From a 217ms execution time to a 0.106ms execution time

NOTE: If you run this exact experiment locally immediately after doing a 1-million-row bulk insert, your initial Index-Only Scan might show a small number of Heap Fetches (e.g., Heap Fetches: 10). This is not a failure of the index. This happens because PostgreSQL hasn't had time to update its Visibility Map. The database checks the heap to ensure that another transaction hasn't deleted those specific 10 rows. Running a manual VACUUM on the table updates the map and instantly drops the Heap Fetches back down to zero.

WHEN TO USE COVERING INDEX:

Covering indexes are powerful, but if misused, they can slow down write speeds. They should not be abused.

Use them strictly for:

Highly critical, high-frequency read queries (endpoints hit thousands of times a second).
Queries that return a small, lightweight columns (Integers, UUIDs, Timestamps, Booleans) . Never INCLUDE massive text blocks or JSON data.

Slapping Secondary Indexes on Random Fields is Silently Killing Your Database.

OPEYEMI OLUWAGBEMIGA — Tue, 03 Mar 2026 06:17:52 +0000

Why do we add indexes to our SQL fields? To make the search faster, right?

But do you know it has a massive downside? Writes become slower, forcing developers to be strategic about which fields should be labelled as secondary indexes.

What is the magic behind indexes? Let's say we have a table of books called “book” with fields (id, title, author, pub_date, isbn).

SELECT * FROM book WHERE author=”C.S. Lewis”

This causes the database engine to search through the Heap (a physical unstructured file where all data in the database actually lives). Let's say we have a total of 1 million rows in the table and only 20 Lewis books. The database engine would scan through all the 1 million rows, even when it had gotten the total of 20 Lewis books, just to be sure it didn’t miss any.

This is a Full Table Scan with O(n) complexity. It is brutally slow.

THE FIX

When you add the field “author” as a secondary index

CREATE INDEX idx_author ON book(author);

The database engine creates a separate, highly organized B-Tree structure. It stores the Author names in alphabetical order, paired with a pointer (physical addresses) to where the full row actually lives.

So instead of scanning through all 1 million rows, the database traverses through the B-Tree in O(log n) time to get the 20 records and their pointers. It then uses the pointers to get the data it needs from the heap. That’s how secondary indexes make reads faster.

BUT WHY DO WRITES GET SLOWER?

Adding or updating data no longer means just changing the heap data, but also the additional B-Tree created by the secondary indexes. So if you blindly add 5 secondary indexes to a table, every single INSERT or UPDATE means writing to the Heap plus updating 5 separate B-Trees on the disk.

That is the hidden cost. As a backend developer, your job isn’t to index everything; instead, your job is to understand the tradeoff.

TIPS FOR SECONDARY INDEXES

Do not index fields that are constantly updated, like page visits and view counts. This is to reduce the overhead of the database’s write performance.

Only index fields that you actively use in your WHERE, JOIN, or ORDER BY clauses.