Offset Pagination Is Lying to You

Every list endpoint starts the same way. You write ->paginate(20), wire it to a table, click through the first three pages in development, and ship it. It works. It keeps working for a year.

Then someone builds a report that walks all 800 pages, or a user with 50,000 rows opens the last page, and the endpoint takes eleven seconds. Meanwhile support is getting tickets about records showing up twice in an export.

Both of those are the same bug.

What OFFSET actually does

LIMIT 20 OFFSET 8000 reads like "start at row 8000." The database doesn't have that ability. It produces rows in order, counts off the first 8,000, throws them away, and returns the next 20.

So the cost of page 400 is the cost of page 1 plus the cost of every page in between. Your query plan looks identical at every page size, which is exactly why this never shows up in local testing where the seeder made 200 rows.

A second problem hides behind it: paginate() in Laravel also runs a COUNT(*) on the full filtered set so it can render "Page 12 of 391." On a large table with a few joins, that count can cost more than the page itself.

If you only ever needed "load more," you paid for a page counter nobody looked at. simplePaginate() drops the count and is a free win in that case.

The correctness problem

Slowness is annoying. This part is worse.

Offset pagination assumes the result set holds still between requests. It doesn't.

Say you're listing players ordered by created_at desc. A client loads page 1, which returns rows 1 to 20. While they're reading, two new players get registered. They request page 2, which is OFFSET 20. Those two new rows have pushed everything down by two positions, so rows 19 and 20 (already seen on page 1) come back again on page 2.

Deletes do the reverse. Delete two rows and the next page skips two records the user never sees.

This is invisible in a UI where people click around casually. It is very visible when a background job walks the whole list to build a report, and ends up with duplicated and missing records with no error anywhere in the logs.

Cursor pagination

The fix is to stop describing position by counting and start describing it by value.

Instead of "skip 8,000 rows," you say "give me the rows that come after this specific one." The query becomes a range scan:

select * from players
where (created_at, id) < ('2026-03-11 09:14:22', 4471)
order by created_at desc, id desc
limit 20;

With an index on (created_at, id), the database seeks straight to the starting point. Page 400 costs the same as page 1. There's no count, no discarded rows, and new inserts at the top of the list don't shift anything, because the cursor points at a row, not at a position.

In Laravel

Laravel ships this. Swap the method:

$players = Player::query()
    ->where('team_id', $team->id)
    ->orderBy('created_at', 'desc')
    ->orderBy('id', 'desc')
    ->cursorPaginate(20);

The response gives you next_cursor and prev_cursor instead of page numbers. The cursor itself is a base64 string holding the values of the ordering columns for the boundary row.

Two rules make or break this:

Your ordering must be deterministic. If you order only by created_at and two rows share a timestamp, the database is free to return them in either order, and the cursor lands in an ambiguous spot. Always append a unique tie-breaker, normally the primary key.

Your ordering columns must be indexed together, in the same order. A composite index on (team_id, created_at, id) for the query above. Without it you've replaced a slow scan with a different slow scan.

On the Vue side

Cursor pagination pairs naturally with "load more" and infinite scroll, because that's the same access pattern: always forward, never jump.

The state you keep shrinks. There's no current page, no total pages, no page-size math. You hold an array of loaded items and a single cursor string:

const items = ref([])
const cursor = ref(null)
const done = ref(false)
const loading = ref(false)

async function loadMore() {
  if (loading.value || done.value) return
  loading.value = true

  const { data, meta } = await api.get('/players', {
    params: { cursor: cursor.value },
  })

  items.value.push(...data)
  cursor.value = meta.next_cursor
  done.value = meta.next_cursor === null
  loading.value = false
}

Treat the cursor as opaque. Don't parse it, don't build one by hand, don't try to reconstruct "page 3" from it. The moment you decode it in the client you've coupled your frontend to your sort columns.

What you give up

Cursor pagination is not a free upgrade. You lose:

• Jumping to an arbitrary page. There's no way to ask for page 7 without walking to it. If your users genuinely navigate by page number, you need offset, or a hybrid.
• Total counts. You can still run a separate cached count if the number matters, but it stops being free.
• Bidirectional freedom. Going backwards works, but shuffling between arbitrary points does not.

For admin tables where someone sorts by three different columns and jumps around, offset with a sane page cap is still the right call. For feeds, activity logs, notifications, exports, mobile lists, and anything an API consumer will iterate end to end, cursors are correct and offset is a bug waiting for enough data.

A reasonable default

Pick per endpoint, not per app:

• Infinite scroll or "load more" in the UI → cursorPaginate()
• Public API that clients will iterate fully → cursorPaginate()
• Admin table with page numbers and a small dataset → paginate()
• Admin table with page numbers and a large dataset → simplePaginate() plus a cached count, and cap how deep people can go

And whichever you choose, enforce a maximum page size on the server. ?per_page=100000 is the oldest denial of service in the book, and it doesn't care which pagination strategy you picked.