Pagination Choices and the Bugs They Cause

Offset pagination is the default in almost every web framework, and it is almost always the wrong choice for anything that changes. The mechanism is simple: the client asks for page three, twenty-five items per page, and the server runs a query with LIMIT 25 OFFSET 50. The database counts past fifty rows it will never return. That count is cheap on a small table and expensive on a large one, but the real damage shows up elsewhere.

Rows shift under the query. A new record inserted near the top pushes everything down by one position. A deleted record pulls everything up. The client requests page four and receives a row it already saw on page three. Or it skips a row entirely, and nobody notices until a customer asks why their order never appeared in the admin panel.

This is the bug that survives code review. It does not throw an exception. It does not fail a test. It produces a page that looks correct, with the right number of rows, in the right order, and one of them is a duplicate of something from the previous screen.

The insertion that shifts every row below it

Picture a support queue sorted by creation time, newest first. An agent opens page one and starts working through it. A new ticket arrives. The agent clicks to page two. Because everything shifted down by one, the last ticket on page one has now moved to the top of page two, and the agent reads it twice while the ticket that used to sit at the bottom of page two has been pushed to page three and is never seen.

For a queue that processes a few tickets an hour, this costs a minute of confusion. For a moderation system handling thousands of reports a day, it means items quietly drift past the reviewer. The pagination did exactly what it was told. The data moved, and the page boundary no longer meant what the client assumed it meant.

The same failure appears in every list sorted by a mutable field. Sort by last-modified timestamp and any edit reshuffles the order mid-session. Sort by score and a single vote moves a row across the page boundary. The list is a snapshot of a moment that has already passed, but the client treats it as a stable address.

Why deep offsets get slower, not just wrong

There is a second cost that engineers notice before the correctness problem: the query itself degrades. OFFSET 500000 asks the database to walk half a million rows and discard them. On a table with a covering index, that walk is fast per row but the total is brutal. On a table without the right index, it is a full scan with a discard at the end.

The slowdown is not linear in a friendly way. It is linear in the worst way: each additional page costs roughly the same as the previous one, so page ten thousand costs as much as the first ten thousand pages combined. A nightly export that iterates through results by incrementing offset will start fast and finish hours later, and the database will spend that time reading rows it throws away.

Some teams "fix" this by caching the count. The count query is usually the most expensive part of a paginated endpoint, so they cache the total and skip the count on subsequent requests. That removes the count cost but leaves the offset walk in place. The page still shifts when data changes, and the deep pages still crawl.

The count query nobody budgeted for

Nearly every paginated interface wants to show "1,247 results" at the top. That number comes from a COUNT(*) with the same WHERE clause as the page query, run on every request. On a table with a few million rows and a non-trivial filter, the count can take longer than the page itself.

The count is also a lie the moment it is rendered. Between the count query and the page query, rows are inserted and deleted. The header says 1,247 results while the pages actually contain 1,251. Users rarely notice, but the inconsistency is baked in: two queries, two moments, two different truths.

A common patch is to cap the count. Show "1,000+" once the total exceeds a threshold and skip the expensive scan. That trades accuracy for speed, which is a reasonable bargain, but it does not touch the offset problem underneath. The pages still shift, and the deep pages still cost full walks.

Cursor pagination and the stable sort it requires

Cursor pagination (sometimes called keyset pagination) replaces the offset with a pointer. Instead of "skip fifty rows," the client sends the last row it saw, and the server asks for the next twenty-five rows after that value in the sort order. The database seeks directly to the position and reads forward. No walk, no discard, no shift.

This is the correct default for feeds, queues, and any list that changes while a user is reading it. It is also where most teams introduce a new class of bug: the unstable sort key. If the cursor column is not unique, the seek becomes ambiguous. Two rows share a timestamp, the cursor lands between them, and one of them is skipped or duplicated.

The fix is a composite cursor. Sort by (created_at, id) and seek on both. The id breaks ties, the sort becomes total, and every row has exactly one position. This detail is easy to miss. A team ships cursor pagination on a single timestamp column, the tests pass because the fixture data has distinct timestamps, and production silently drops rows whenever two records land in the same millisecond.

The tie-breaker that quietly saves the feed

A feed sorted by "popularity score" is worse. Scores change constantly, so the cursor value the client holds may no longer correspond to a stable position. The row that was at score 84 when the cursor was issued has dropped to 79, and the seek jumps past rows it should have returned.

For mutable sort keys, the cursor has to be frozen at the moment of the first request. Snapshot the ranking, assign each row a stable position, and paginate on that. This is more work, and it is the only way to keep a "sort by trending" feed internally consistent across pages. The alternative is a feed that reshuffles between page loads and shows users items they already scrolled past, which feels broken even when the code is doing what it was told.

Even a stable key needs care when the sort direction changes. A cursor encodes a position in an ordering. Flip the order from descending to ascending and the cursor is now meaningless, or worse, points to the wrong end of the list. The server has to detect the direction change and reject or translate the cursor, and most implementations do not.

Total counts that drift between pages

Cursor pagination solves the shifting problem, but it removes the ability to jump to page forty. There is no random access. The client can only move forward (or backward, with a reverse cursor). For an admin table where an operator wants to leap to the end, this is a genuine limitation, and it is why offset pagination survives in back-office tools long after it should have been retired.

The compromise most teams land on is offset for admin, cursor for public feeds. That is defensible. The operator can tolerate a shifted row because they are looking at a known record and can search for it directly. The public feed cannot tolerate duplicates because the user has no way to tell that a repeated item is a bug rather than a repost.

What is not defensible is using offset for a feed and calling the result "eventually consistent." The duplicates and skips are not a consistency tradeoff. They are a correctness defect that shows up as soon as the list has any write traffic.

Changing page size mid-scroll and the off-by-one it creates

Page size is usually a client-side setting, and users change it. A reader on page three at twenty items per page switches to fifty per page. The client recomputes offset as (page - 1) * size, which gives 100, and requests rows 100 to 149. But the row the reader last saw was row 59 in the old pagination, and the new offset jumps past rows 60 through 99 without showing them.

The bug is trivially reproducible and almost never tested. It requires the client to track the boundary by row position rather than page number, which means the "page size" control is really a "rows per request" control, and the UI has to reconcile the two. Most implementations silently drop the skipped rows and let the reader assume the content ended.

The same arithmetic breaks when the requested page exceeds the last page. Offset beyond the end returns an empty array, and the client renders "no results" instead of "you have reached the end." For a search interface, that reads as a failed query. The user retypes the term and gets the same empty screen.

Deletion at the boundary and the row that vanishes

Deletion is the mirror of insertion, and it produces the opposite error. A moderator removes a comment from page one while a reader is on page two. Every row on page two shifts up by one, and the first row, the one the reader was about to see, moves onto page one and is never shown.

In a moderation tool, this is the failure mode that matters most. The comment that gets deleted is often the one that should have been reviewed, and the review queue silently loses the item immediately after it. The moderator sees a shorter list and assumes the work is done.

Bulk deletion makes it worse. Removing fifty rows from the top of a list shifts every subsequent page by fifty positions. A reader paging through results will skip entire blocks of content and never know it. The UI shows a coherent list, just not the list that existed when the session started.

A short list of symptoms worth grepping for

Teams rarely discover offset bugs through monitoring, because the pages return 200 OK with plausible data. The signals show up in support tickets and manual QA. A user reports seeing the same item twice. A report says an order is missing from the admin list but exists in the database. An export produces a row count that does not match the table.

The code smells are equally specific. Look for OFFSET in a query that runs against a table with concurrent writes. Look for a cursor implementation with a single non-unique sort column. Look for a COUNT(*) that runs on every request. Look for a page-size control that recomputes offset from page number. Each one is a bug waiting for traffic.

The choice between offset and cursor is not a matter of taste. Offset is correct for static data and small tables. Cursor is correct for anything that changes while a user reads it. The mistake is treating the default as neutral. It is not. The default pagination scheme in most frameworks is the one that quietly duplicates and drops rows, and it will do so for as long as the data keeps moving.