Database Pagination: Offset versus Cursor in Large Tables

The Hidden Problem of Pagination in Web Applications

When developing a modern system, we often need to display large volumes of data divided into pages. On the surface, this task looks simple: just tell the database to skip a certain number of records and bring only the next ones. In practice, however, this seemingly harmless choice hides performance pitfalls capable of crashing entire servers as the database grows. Understanding the internal workings of these queries is the first step toward designing resilient and scalable systems.

In simple terms, pagination is the art of slicing a giant list into smaller pieces so users or applications can process them without choking. When a social media feed displays ten new posts or an administrative table shows twenty customers at a time, a SQL instruction (the standard language for talking with relational databases) works behind the scenes. The way we construct this instruction determines whether our application stays fast when the table grows from one thousand to ten million rows.

How Offset Pagination Works and Where It Fails

The most traditional and widely taught approach in introductory tutorials uses the OFFSET command combined with LIMIT. The term offset literally means the displacement: we tell the database to ignore the first N records and return the next X. If we request page one hundred with twenty items per page, the database calculates an offset of two thousand. For the developer, the syntax is clean and intuitive, facilitating numerical navigation buttons like page 1, 2, 3, and so on.

The major technical flaw is that the relational database lacks a magic function to jump directly to line two thousand. In practice, the database engine must physically read all two thousand previous rows, discard them one by one in memory, and only then start collecting the records we care about. When we jump to page one hundred thousand, the database performs a colossal scanning effort (known as a full table scan) to deliver a handful of data. In practice, this means the deeper the user navigates into the table, the slower the query becomes, wasting processing power and memory.

-- Classic example of a query with Offset and Limit
SELECT id, title, created_at 
FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 200000;

Beyond severe performance degradation, Offset suffers from a serious data consistency issue known as the phantom effect. Imagine a user is on page one and, right at that moment, a new record is inserted at the top of the table. When that user clicks to go to page two, the newly created record shifts all others downward. The practical result is that the user ends up seeing the same item twice across different pages, or worse, misses an item that just entered the cut-off range. In financial systems or real-time feeds, this volatility is unacceptable.

The Efficient Alternative: Cursor-Based Pagination

To bypass the catastrophic bottlenecks of Offset, engineers turn to cursor-based pagination, also known as keyset pagination. Instead of telling the database how many records to skip, we provide an anchor (the cursor) pointing directly to where we left off. This cursor is usually the unique identifier of the last received item, such as an auto-incrementing ID or a timestamp combined with the ID.

In practice, this works like reading a book: instead of counting every word from the first page to find the current paragraph, you place your finger on the last read line and continue from there. When the client requests the next page, it sends the value of the last received cursor. The database query uses a WHERE clause to filter directly for records greater or lesser than that value, leveraging table indexes for an instantaneous jump.

-- Example of cursor pagination using a reference ID
SELECT id, title, created_at 
FROM posts 
WHERE id < 98451 
ORDER BY id DESC 
LIMIT 20;

This approach completely eliminates the need for cascading scans. Because the database utilizes tree-structured indexes (such as B-Trees), finding the record corresponding to the cursor is an extremely fast operation, regardless of whether we are at the beginning or the end of a table with five hundred million rows. Response times remain constant and predictable, ensuring operational stability even under heavy traffic spikes.

Trade-offs and Operational Challenges of the Cursor Approach

Despite its technical superiority in performance, cursor pagination requires significant changes to user experience and system architecture. The most obvious trade-off is the loss of arbitrary navigation. With cursors, you can only move forward to the next page or backward to the previous one sequentially; jumping directly from page one to page two hundred becomes mathematically unfeasible because you lack the intermediate cursor.

Another critical engineering detail is the necessity of deterministic sorting. If the column used as a cursor contains duplicate values (such as dozens of records created at the exact same second), the search engine can lose the chain and omit data during pagination. To protect the system against this behavior, developers must compose multi-column cursors, combining the timestamp with a unique primary key to guarantee that every row has an absolute and unmistakable identity.

Final Considerations

Choosing between Offset and Cursor pagination is not merely a matter of code styling preference, but a fundamental architectural decision dictating the robustness of a large-scale application. While Offset offers simplicity and numerical page navigation for small databases, it becomes a silent poison to performance as tables grow and new data arrives. Cursors deliver surgical speed and absolute consistency, sacrificing only the convenience of arbitrary page numbers.

Evaluating expected data volume, user behavior, and consistency criticality before writing the first line of code prevents painful refactoring down the road. In modern high-concurrency ecosystems, mastering these techniques separates fragile systems from those capable of scaling sustainably and predictably.