Offset pagination skipped a row and repeated another

The record nobody ever saw

Your moderation queue is an index of submissions, newest first, 25 to a page. A reviewer works it top to bottom every morning: page 1, page 2, page 3, until the list runs out. On Thursday a submission from Tuesday turns up unactioned, and the reviewer is certain it was never on screen.

They are right. It was never on a page.

While they were reading page 1, four new submissions arrived. Each one sorts ahead of everything they were looking at, so by the time they clicked through to page 2 the whole result set had shifted four rows to the right. OFFSET 25 now points four rows later into a longer list. The four rows that used to occupy positions 26 to 29 are now at 30 to 33 — and page 2 starts at 26. Those four were skipped. If the traffic had gone the other way and four rows had been deleted, page 2 would have re-served rows the reviewer had already seen on page 1.

Nothing failed. No exception, no log line, no slow query. paginate() did exactly what it is specified to do, and the specification is weaker than the thing you assumed.

What paginate() actually runs

Turn on query logging and ask for page 2 of 4:

use App\Models\Article;
use Illuminate\Support\Facades\DB;

DB::listen(fn ($query) => print($query->sql . "\n"));

Article::query()->orderBy('id')->paginate(perPage: 4, page: 2);

Two queries:

select count(*) as `aggregate` from `articles`
select * from `articles` order by `id` asc limit 4 offset 4

Two separate trips to the database, with your application code, the network and anything else that is happening on that table in between. The count tells the view how many page links to draw. The second query positions a window by counting rows from the start and throwing away everything before it.

That is the whole mechanism, and two things follow from it that people do not expect.

The first is the consistency problem above. OFFSET 25 is not a bookmark. It does not mean "the rows after the ones I showed you last time" — it means "skip 25 rows of whatever this query returns right now". When the set changes between requests, the window slides over different rows, and there is no page number you can pass that fixes it.

The second is cost. OFFSET 100000 does not jump to row 100,000. The database generates and discards a hundred thousand rows to find where to start, so your last page is dramatically more expensive than your first — on the same index, with the same LIMIT. Page 1 of a large table is instant and page 4,000 times out, which is why this surfaces as "pagination is slow" only once the table is big and only at the end of it.

The ordering that makes it worse for free

The skipping above needs writes to happen. There is a version that needs nothing at all:

$submissions = Submission::query()->orderBy('status')->paginate(25);

status is not unique. Hundreds of rows share each value, and SQL gives you no guarantee at all about the relative order of rows that tie. The database returns them in whatever order the chosen plan produces. That order is usually stable while nothing changes — but "usually" is doing real work in that sentence. Add an index, change the row count enough to shift the plan, run it on the replica instead of the primary, and the tied rows come back in a different arrangement. Your page boundaries move, and you skip and duplicate rows on a table nobody wrote to.

The fix costs one line. Always finish an orderBy with something unique:

$submissions = Submission::query()
    ->orderBy('status')
    ->orderBy('id')
    ->paginate(25);

Now the sort is total. Every row has exactly one position, ties are broken deterministically, and the same query returns the same ordering every time. Treat this as mandatory on every paginated query: if the columns you sort by are not collectively unique, the pagination is undefined, not merely untidy.

Stop paying for a count you do not render

Look at the first query again. select count(*) runs on every page view, over the full filtered set, so you can draw page-number links. If your view renders "Next" and "Previous" and no numbers, you are buying a full count and throwing it away.

simplePaginate() does not buy it:

Article::query()->orderBy('id')->simplePaginate(perPage: 4, page: 2);

That compiles to a single statement:

select * from `articles` order by `id` asc limit 5 offset 4

One query, and note limit 5 for a page of 4. That extra row is how the paginator knows whether a next page exists without counting anything. You lose total(), lastPage() and the numbered links. On a big table that is a very good trade, and on an "infinite scroll" list you were never rendering them anyway.

This does nothing about the sliding window — simplePaginate still uses OFFSET.

Cursor pagination is a where clause, not an offset

cursorPaginate() is the one that fixes the correctness problem, because it stops counting rows and starts comparing values:

$first = Article::query()->orderBy('id')->cursorPaginate(4);
$next  = Article::query()->orderBy('id')->cursorPaginate(4, cursor: $first->nextCursor());

Those two calls compile to this:

select * from `articles` order by `id` asc limit 5
select * from `articles` where (`id` > ?) order by `id` asc limit 5   -- bindings: [4]

The second page says "give me rows after id 4". Rows inserted ahead of id 4 cannot shift that window, because there is no window — there is a boundary value, carried in the query string as an encoded cursor:

?cursor=eyJpZCI6NCwiX3BvaW50c1RvTmV4dEl0ZW1zIjp0cnVlfQ

No count, no offset, and page 4,000 costs the same as page 2, because the database seeks straight to the boundary on the index. With multiple sort columns it compiles the compound comparison correctly for you:

select * from `articles`
where (`category` > ? or (`category` = ? and (`id` > ?)))
order by `category` asc, `id` asc limit 5

The constraints are real, though, and they are why this is not simply the default. The docs require an order by, and the columns you order by must belong to the table being paginated — so you cannot cursor-paginate a list sorted by a joined column. On a query builder query, leaving out the ordering throws immediately:

RuntimeException: You must specify an orderBy clause when using this function.

An Eloquent query is more forgiving: with no explicit orderBy it falls back to ordering by the model's primary key, which means the mistake you actually make there is not an exception but a list sorted by id when you meant to sort by date. Check the compiled SQL rather than trusting the absence of an error.

You also give up jump-to-page-7 and the total count permanently, because neither is expressible as a boundary comparison. And a cursor encodes the sort column's value for one specific row — if that row is deleted or its sort value is edited, the cursor is stale and the reader's position is gone.

What to change

Open every paginated query in your application and check the orderBy. If the columns in it are not collectively unique, append the primary key now; that is the cheapest correctness fix in this article and it needs no other decision.

Then take the two screens that matter most and answer one question each: does the view actually render a total or numbered page links? If not, move it to simplePaginate() and delete the count query. If the screen is a feed, a reviewer queue, an export or anything else read strictly front to back over a table that is being written to while people read it, move it to cursorPaginate() and accept losing the page numbers — on those screens the page numbers were lying to you anyway.