Pagination Strategies: OFFSET vs. Keyset Pagination

1. Why choosing a pagination strategy matters at all

Almost every application with larger result sets needs pagination to deliver data in manageable chunks instead of one huge response. The most obvious implementation, LIMIT and OFFSET, works flawlessly on the first pages and is therefore rarely questioned. That apparent simplicity is exactly the problem: keyset pagination solves a scaling issue that only becomes visible with OFFSET once users page deep into result lists or a dataset grows.

The difference between the two approaches is not an academic detail, it has a direct effect on user experience. An API that takes several seconds for a simple list query on page 500 while page 1 responds in milliseconds degrades load times and user experience exactly where users or automated crawlers iterate deep through data, such as export features, infinite scroll, or API consumers that systematically fetch every record.

This article explains why OFFSET does not scale structurally, how keyset pagination works as an alternative, and in which cases OFFSET nonetheless remains a reasonable choice. The principles are independent of the specific database system, even though the syntax varies slightly between MySQL, PostgreSQL and SQL Server.

The topic becomes especially relevant for systems meant to be built for growth from the start. A pagination strategy that works unnoticeably at a thousand rows can become the dominant performance problem of an entire application at ten million rows, often without a single code change being responsible, simply through organic data growth. Anyone who plans for keyset pagination from the beginning avoids a painful migration in the middle of production operation.

2. How OFFSET pagination works and why it gets slow

LIMIT and OFFSET are the most intuitive form of pagination: LIMIT 20 OFFSET 100 returns twenty rows after the first hundred have been skipped. The problem lies in execution: the database usually cannot jump directly to the hundredth row, it has to actually read and process the first hundred and twenty rows according to the sort order, then discard the first hundred and return only the last twenty. The cost of OFFSET therefore grows linearly with the number of skipped rows, even when an index supports the sort order.

At small offsets, such as the first few pages, this effect is barely noticeable because the number of rows to discard is small. At page 500 with 20 rows per page, however, the database already has to read and discard 10,000 rows before returning the 20 actually wanted. This gap between "rows read" and "rows returned" is the core of the problem that keyset pagination structurally avoids.

An additional, often overlooked effect: even when an index covers the ORDER BY column and theoretically enables an index scan instead of a full table scan, the database still has to navigate sequentially through the index to count the skipped entries. The index therefore does not prevent the fundamental problem of OFFSET, it merely makes the waste somewhat less expensive per row, without changing the linear growth rate with page depth.

-- Page 1: fast, few rows to skip
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 0;

-- Page 500: the database still reads and discards 10,000 rows first
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 10000;
-- Cost grows linearly with OFFSET, even with an index on product_id

3. Keyset pagination in detail: WHERE id > last_seen_id

Keyset pagination, also called cursor pagination, replaces the numeric page offset with a value comparison against the last seen record. Instead of "skip the first hundred rows", you ask "give me the next twenty rows after this specific value". This formulation allows the database to jump directly to the right position via the index, without having to read and discard prior rows, because a B-tree index is already sorted and enables a range search from a given value in logarithmic instead of linear time.

The basic syntax for keyset pagination is deceptively simple: WHERE id > last_seen_id ORDER BY id LIMIT 20. The client stores the ID of the last row of the current page and sends it as a parameter for the next request. The response time of this query stays constant whether you are on page 2 or page 5000, because in both cases the database only has to read twenty rows starting from the found position, instead of skipping thousands of rows beforehand.

The most important conceptual difference from OFFSET: keyset pagination no longer knows an absolute page number, only a position relative to the last seen record. For many use cases, such as infinite scroll, API pagination or export jobs, that is entirely sufficient and even more practical than absolute page numbers, while classic page numbering with a direct jump to page 47 requires additional considerations covered in the section on page numbers.

-- First page: no cursor yet, just take the first 20 rows
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20;

-- Next page: use the last seen product_id from the previous page as the cursor
SELECT product_id, product_name, price
FROM products
WHERE product_id > 10240  -- last_seen_id from the previous page
ORDER BY product_id
LIMIT 20;
-- Constant-time lookup via the index, no rows skipped and discarded

4. Why a stable sort order is strictly required

Both OFFSET and keyset pagination require a deterministic, stable sort order, but with keyset pagination this requirement is even stricter, because the cursor itself is based on the sort column. If you sort, for example, by a creation date that is not unique because several rows can carry the same timestamp, the order of these equal-valued rows is undefined, and the cursor can skip rows or return them twice.

The solution is to always sort by a unique column or column combination, typically the primary key or a combination of a business sort column plus the primary key as a tie-breaker. ORDER BY created_at, id is considerably more robust than ORDER BY created_at alone, because the ID as a unique tie-breaker resolves any ambiguity between rows with identical timestamps. This requirement for a stable sort order is the most important reason why keyset pagination has to be planned more carefully than a naive OFFSET solution.

Another important point: the sort column has to be indexed, otherwise the entire speed advantage of keyset pagination evaporates, because without an index the database would still have to perform a full scan to find the next rows after the cursor value. A composite index over (created_at, id) supports both the sorting and the comparison in the WHERE part with a single index usage.

-- Unstable: created_at is not unique, ties can be skipped or duplicated
SELECT order_id, created_at FROM orders
WHERE created_at > '2026-07-01 10:15:00'
ORDER BY created_at
LIMIT 20;

-- Stable: id as a unique tie-breaker guarantees a deterministic order
SELECT order_id, created_at FROM orders
WHERE created_at > '2026-07-01 10:15:00'
ORDER BY created_at, order_id
LIMIT 20;

-- Supporting composite index for both sorting and the WHERE comparison
CREATE INDEX idx_orders_created_id ON orders (created_at, order_id);

5. Multi-column sorting and composite cursors

As soon as the sort covers more than one column, the cursor for keyset pagination becomes correspondingly more complex. Instead of a single comparison, you need a composite condition that correctly reflects the lexicographic ordering of multiple columns. For ORDER BY created_at, id, the correct WHERE condition is not simply two separate comparisons, but a tuple condition: either created_at is greater than the cursor value, or created_at is equal and id is greater.

Many databases, including PostgreSQL and MySQL from version 8, directly support tuple comparisons in the form WHERE (created_at, id) > (cursor_date, cursor_id), which makes the condition considerably more readable than the spelled-out OR combination. This syntax is functionally equivalent but less error-prone, because you do not have to manually replicate the priority of the columns in a nested OR condition. For databases without native tuple comparisons, the spelled-out form remains the reliable alternative for keyset pagination with multiple sort columns.

-- Multi-column keyset pagination: sort by created_at, then id as tie-breaker
-- Explicit form, works on virtually every SQL database
SELECT order_id, created_at, total_amount
FROM orders
WHERE (created_at > '2026-07-01 10:15:00')
   OR (created_at = '2026-07-01 10:15:00' AND order_id > 88213)
ORDER BY created_at, order_id
LIMIT 20;

-- Row-value comparison, supported by PostgreSQL and MySQL 8+
SELECT order_id, created_at, total_amount
FROM orders
WHERE (created_at, order_id) > ('2026-07-01 10:15:00', 88213)
ORDER BY created_at, order_id
LIMIT 20;

6. The problem with page numbers and jumping directly to page N

A commonly cited drawback of keyset pagination is that it does not support jumping directly to an arbitrary page number, because every page depends on the cursor of the previous page. OFFSET, in contrast, theoretically allows jumping straight to page 47 without having seen the previous 46 pages, even though internally it still reads all skipped rows.

In practice, this drawback is smaller than it first appears. Most users click through pages sequentially or use infinite scroll, both of which fit keyset pagination excellently. For the rare cases where a direct jump to a specific page is actually needed, for example in an admin interface with a page-number input field, hybrid solutions covered in the next section are available, or a deliberate restriction to the first few hundred pages, while sequential navigation only is allowed beyond that.

For search engine crawlers and API consumers that want to systematically fetch all data, the missing direct jump is irrelevant anyway, since these use cases naturally iterate sequentially through the entire result set. This is exactly where keyset pagination shows its biggest advantage, because the constant response time is maintained across the entire iteration, while OFFSET would get slower with every further step.

7. Consistency under concurrent writes

A subtle but practically relevant advantage of keyset pagination concerns consistency under concurrent writes. If a new row is inserted while paging with OFFSET, and that row sorts before the current position, the offset of all subsequent rows shifts by one. The result: a row gets skipped, or a row appears twice across two consecutive pages, depending on whether it was inserted or deleted.

Keyset pagination is structurally more robust against this problem, because the cursor is based on a concrete, already seen value, not a relative position. If a new row is inserted that sorts after the current cursor value, that has no effect on the next page, because the WHERE condition still correctly returns all rows after the cursor, regardless of how many rows were inserted before it in the meantime. If a row is inserted before the cursor, that also does not affect pages already delivered or still pending.

This property makes keyset pagination particularly suitable for data streams with a high write frequency, such as activity feeds, log analysis, or order lists in an active e-commerce system, where OFFSET-based pagination would regularly produce visible inconsistencies for end users due to concurrent writes.

8. Hybrid approaches and when OFFSET is still fine

Despite all the advantages of keyset pagination, OFFSET is not fundamentally wrong. For small tables with a few thousand rows, where even the deepest page only skips a few thousand rows, the performance difference is barely measurable, and the simplicity of OFFSET, especially support for direct page jumps, cannot justify the additional implementation effort of keyset pagination.

A proven hybrid approach combines both techniques: for the first few dozen pages, where users typically want to navigate directly, you use OFFSET, because the performance downside remains negligible there. Beyond a certain depth, the application automatically switches to keyset pagination, or offers no page numbers at all anymore, just "next" navigation. This combination delivers the usability of page numbers for the frequently used first pages and the scalability of keyset pagination for deep paging.

For new systems, especially APIs designed from the start for large and growing data volumes, it is usually worth starting directly with keyset pagination, rather than having to migrate a working OFFSET implementation later when data volume grows and performance problems become visible.

-- Hybrid approach: OFFSET for shallow pages (page <= 50), keyset beyond that
-- Shallow page: simple OFFSET is fine, the cost is negligible
SELECT product_id, product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 980;  -- page 50

-- Deep page: switch to keyset pagination using the last seen cursor
SELECT product_id, product_name, price
FROM products
WHERE product_id > 20450  -- cursor carried over from page 50
ORDER BY product_id
LIMIT 20;

9. OFFSET and keyset compared directly

The following table summarizes the most important differences between OFFSET pagination and keyset pagination.

This comparison makes clear that there is no universally correct answer, but a deliberate decision based on data volume, usage pattern, and requirements for direct page navigation. For most growing applications, however, the advantages of keyset pagination clearly outweigh the drawbacks once data volume grows past a few thousand rows.

10. Summary

OFFSET-based pagination is simple to implement but scales poorly by design, because the database has to read and discard all previous rows on every page, leading to linearly growing response times. Keyset pagination solves this problem by replacing the page offset with a comparison against the last seen value, enabling constant response times regardless of page depth, provided the sort order is unique and supported by a suitable index.

The choice between the two strategies depends on the concrete use case: small, static data volumes with a need for direct page navigation tolerate OFFSET without issue, while growing data volumes, infinite-scroll interfaces, and APIs with systematic iteration clearly benefit from keyset pagination. The additional implementation effort, especially with multi-column sorting, pays off early for any system meant to grow in the long run.

11. FAQ: Pagination Strategies