Pagination is one of those features that works perfectly in development. Twenty rows per page, a few hundred records in the seed data, instant responses.

Then the table grows into the millions, someone's script walks through every page of your API, and you notice that page 1 loads in a blink while page 40,000 takes long enough to make a cup of tea. Same query, same twenty rows. What changed?

The answer is OFFSET, and once you see how it works, you'll know exactly when to stop using it.

How offset pagination really works

Classic pagination is LIMIT and OFFSET. Page 3 with 20 per page is:

SELECT * FROM payments
ORDER BY id DESC
LIMIT 20 OFFSET 40;

It reads like "jump to row 40 and take 20". But databases can't jump. To skip 40 rows, MySQL has to find them, walk past them and throw them away. For page 3 that's nothing. For OFFSET 800000, it walks past eight hundred thousand rows to give you twenty.

It's like finding page 500 of a book that has no page numbers by counting every page from the start. The deeper you go, the slower it gets, and no index fixes the counting.

The second problem: rows that move

Offset has a quieter bug that has nothing to do with speed. Picture an infinite-scroll list of the newest payments:

1. The app loads page 1: payments 1 to 20.
2. While the user scrolls, three new payments arrive at the top.
3. The app loads page 2 with OFFSET 20. Everything has shifted down by three, so the user sees the last three payments of page 1 again.

Deletions do the opposite and silently skip rows. For a list on screen it's annoying. For a sync job or an export that walks through pages, it means missing or duplicated data, and nobody notices until the numbers don't add up.

Cursor pagination: "after this one"

Cursor pagination (also called keyset pagination) changes the question. Instead of "skip N rows", you say "give me the next 20 after the last row I saw":

SELECT * FROM payments
WHERE id < 918204          -- the last id from the previous page
ORDER BY id DESC
LIMIT 20;

With an index on id, the database goes straight to that position in the index and reads twenty rows. It doesn't matter if you're on the first page or the hundred-thousandth: the cost stays flat.

And because the position is anchored to a value rather than a count, new rows at the top don't push anything around. Page 2 always starts right after the last row you actually saw.

Sorting by something other than the id

Usually you sort by a date, and dates aren't unique. Two payments with the same created_at could be skipped or repeated at a page boundary. The fix is a tie-breaker: sort by the date and the id.

SELECT * FROM payments
WHERE created_at < ?
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Back it with a composite index on (created_at, id), in that order, and this stays fast at any depth.

In Laravel: one method

You don't have to write that WHERE clause yourself. Laravel's cursorPaginate() builds it from your orderBy calls:

$payments = Payment::query()
    ->where('tenant_id', $tenant->id)
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->cursorPaginate(20);

return PaymentResource::collection($payments);

Wrapped in an API resource like this, the response carries a links.next URL and a meta.next_cursor (return the paginator directly and you get next_page_url and next_cursor at the top level instead). Either way, the client simply follows the next link until it's null.

A few rules from the Laravel pagination docs worth knowing before you switch:

• The ordering must include at least one unique column (or a unique combination). That's why id is there.
• Columns used for ordering can't contain NULL values.
• You only get next and previous. There are no page numbers and no "jump to page 37".
• The cursor is an encoded string, not a secret. Treat it as opaque in the client, but don't put anything sensitive in the sort columns expecting it to be hidden.

Don't forget the hidden COUNT

There's one more cost people miss. Laravel's paginate() runs a second query, SELECT COUNT(*), to know how many pages exist. On a big table with filters, that count can be slower than the page itself.

If you need offset but don't need "page 3 of 4,812", use simplePaginate(). It skips the count and just checks whether there's a next page.

Which one should you use?

For jobs that process a whole table inside your own app, chunkById() and lazyById() use the same "after this id" idea under the hood. Plain chunk() uses offsets, and has the same skipping problem if you update the rows you're filtering on while iterating.

The short version

• OFFSET reads and discards every skipped row, so deep pages get slower.
• Offset pages drift when rows are added or removed.
• Cursor pagination says "after this value", stays fast and stable, but has no page numbers.
• Always add a unique tie-breaker to the sort, and index the sort columns together.
• paginate() also runs a COUNT. simplePaginate() doesn't.

Which style do your APIs use today, and have you ever had to migrate from one to the other?