Picture an admin panel for an e-commerce company that lists orders, 50 to a page, newest first. The developer used the most common approach: LIMIT 50 with an OFFSET of (page − 1) × 50. Page 1 loads in about 20 milliseconds. Someone in finance jumps to page 5,000 to look at old orders and waits eight seconds. Meanwhile the support team complains that the same order sometimes appears at the bottom of page 3 and again at the top of page 4. Both problems come from the same design choice, and both go away with a different one.
What OFFSET actually asks the database to do
An offset is an instruction to skip rows, not to jump to them. To serve page 5,000, the database must produce the first 250,000 rows in the requested order (offset 249,950 plus 50 to return), throw away 249,950 of them, and send you the last 50. Even with an index on the sort column, the database still walks through roughly a quarter of a million index entries, and often visits the table for each of them, before it reaches the rows you want.
That is why the cost grows with page depth. Page 1 touches 50 rows. Page 100 touches about 5,000. Page 5,000 touches about 250,000. Nothing about the query looks expensive in the code, because the expensive part is hidden inside one innocent number. Most relational databases, including PostgreSQL and MySQL, behave this way; there is no general trick by which the engine can know where row 249,951 sits without counting its way there.
Why the same order shows up twice
The duplicate problem is not a database bug. It is a consequence of the data changing between two requests. Suppose a support agent loads page 3, which shows orders at positions 101 to 150. While they read, five new orders arrive. Every existing order is now pushed five positions further down the list. When the agent clicks to page 4, the database returns positions 151 to 200, but the first five of those are orders that were at positions 146 to 150 a minute ago. The agent sees them again. In the opposite direction, if orders are deleted or cancelled out of a filtered view, some rows slide up past the page boundary and are never shown at all, which is worse because nobody notices.
There is a second, quieter cause. If the sort column is not unique, the order between ties is not guaranteed. Two hundred orders created in the same second might come back in one order for page 3 and a different order for page 4, because the database is free to return tied rows however it likes. Sorting by creation time alone is a common way to get duplicates and gaps even when no data changes.
Keyset pagination: remember where you stopped
Keyset pagination (also called seek or cursor pagination) changes the question. Instead of “skip 249,950 rows”, the client says “give me the next 50 rows after the last one I saw”. The last one I saw is identified by the values of the sort key, for example its creation timestamp and its id.
For a list sorted newest first by created_at and then id as a tie-breaker, the first page is simply the top 50. The response includes a cursor holding the created_at and id of the 50th row. The next request filters for rows whose (created_at, id) pair is strictly less than that cursor, sorts the same way, and takes 50. PostgreSQL and MySQL both support row-value comparison, so the condition can be written as a single tuple comparison; in databases that do not, it is expanded into “created_at is less than X, or created_at equals X and id is less than Y”.
With a composite index on (created_at, id), the database can seek directly to the cursor position in the index and read the next 50 entries. The work is the same whether you are on the second page or the five-thousandth. In the illustrative admin panel, page 5,000 would cost roughly what page 1 costs.
The duplicate problem also disappears. New orders arriving at the top of the list do not shift anything, because the next page is defined by a value, not a position. Rows after the cursor are still rows after the cursor. And because id is unique, the sort order is fully determined, so ties cannot shuffle between requests.
What you give up
Keyset pagination is not free. The trade-offs are real and decide when it fits.
| Aspect | Offset | Keyset |
|---|---|---|
| Cost of deep pages | Grows with page number | Roughly constant |
| Jump to page 437 | Easy | Not directly possible |
| Stable under inserts and deletes | No | Yes, for rows after the cursor |
| Total page count | Needs a separate count | Needs a separate count |
| Changing sort order | Trivial | Each sort needs its own cursor and ideally its own index |
The biggest loss is random access. A numbered pager with “1 2 3 … 5,000” assumes you can compute where any page starts, and keyset cannot. That is usually acceptable. Infinite scroll, “load more” buttons, mobile feeds and APIs that export data in batches all move forward one page at a time. Very few humans genuinely need page 4,217; what the finance user really wanted was “orders from March”, which is better served by a date filter than by guessing a page number.
Sorting on user-chosen columns adds work. If users can sort by amount, customer name or status, each sort needs its own cursor format and, for speed, an index whose leading columns match the sort plus a unique tie-breaker. Nullable sort columns need extra care, because nulls sort first or last depending on the database and the query, and the cursor condition must handle them consistently.
Practical design notes
- Make the cursor opaque. Encode the sort values (for example as base64 of a small JSON object) and hand clients a token rather than raw column values. That lets you change the cursor format later without breaking clients, and discourages clients from constructing cursors by hand.
- Always include a unique tie-breaker. The primary key is the usual choice. Without it, keyset pagination can skip rows that share the last seen timestamp.
- Match the index to the sort. The index should cover the sort columns in the same order and direction the query uses. A keyset query without a supporting index can still end up scanning and sorting large ranges.
- Fetch one extra row. Asking for 51 rows and returning 50 tells you whether a next page exists without a separate count query.
- Be careful with total counts. Showing “1,24,56,310 orders” requires counting, which on a large table can be slower than the page itself. Many products show an approximate count or none at all.
When offset is still fine
Offset pagination is not wrong; it is a tool with a range. For a settings screen with 300 rows, a report users rarely page beyond the first few pages, or data that does not change while someone browses, offset is simpler and the cost never becomes noticeable. Some teams keep offset for the first few pages and cap the maximum page number, nudging users toward filters for anything deeper. Search engines and large marketplaces commonly do something similar by limiting how far you can page through results.
The useful habit is to ask two questions whenever you build a paginated list: how deep will people realistically go, and does the data change while they are reading it? If the answers are “not very” and “no”, offset is fine. If the list is large, live, or consumed by an API client that walks every page, remember the last row instead of counting to it.
Written by the RP Strativa Team. Figures and the admin panel in the examples are illustrative.
Leave a Reply