Offset vs Cursor Pagination: Why Page 4,000 Is Slow and How Keyset Queries Fix It

The mobile team files a ticket: "the activity feed gets slower the more you scroll". On page 1 the API answers in 15 ms. Somebody who scrolled for a long time hits page 4,000 and waits 2.8 seconds. Meanwhile the nightly export job that walks ?page=1..N over 3 million orders has crept from 4 minutes to 40, and the support team reports that some orders appear twice in the export while others are missing.

All three symptoms have the same root cause: LIMIT ... OFFSET .... Offset pagination is the default in every framework because it is easy to reason about, but it does work proportional to how deep you are, and it describes a position ("row number 80,000") instead of a place in the data ("after this exact row"). Keyset pagination, also called cursor pagination, fixes both problems.

Why OFFSET Gets Slow

The database cannot jump to row 200,000. It has no idea where row 200,000 is until it has produced the first 199,999 rows in the requested order. Even with a perfect index on the sort columns, the engine walks the index from the start, counts and discards OFFSET entries, then returns LIMIT rows.

-- Page 1: fast
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

-- Page 10,000: the server reads 200,020 index entries and throws 200,000 away
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

Without a covering index it is worse: MySQL may fetch the full row for every skipped entry or fall back to a filesort. Run EXPLAIN ANALYZE on a deep page and you will see the "rows examined" number grow linearly with the offset while the result stays at 20 rows. Page 1 and page 10,000 return the same amount of data, but the second one costs ten thousand times more work.

There is also a correctness problem. An offset is relative to the current state of the table:

• A new post is inserted at the top while a user is on page 3. Everything shifts down by one, and the first row of page 4 is the last row they already saw on page 3. Duplicate.
• A post on page 2 is deleted. Everything shifts up by one, and a row that would have been the first item of page 4 is now the last item of page 3, which the user already loaded. Skipped.

In an admin table that is an annoyance; in an export or a feed it is a bug.

How Keyset Pagination Works

Instead of telling the database "skip N rows", you tell it "give me rows that come after this one". The client sends back the sort values of the last row it received, and the next query turns them into a WHERE clause:

-- First page
SELECT id, title FROM posts
ORDER BY id DESC
LIMIT 20;

-- Next page: the client sends back the last id it saw, e.g. 981234
SELECT id, title FROM posts
WHERE id < 981234
ORDER BY id DESC
LIMIT 20;

With an index on id, the engine does an index seek straight to 981234 and reads 20 entries. The cost is the same on page 1 and page 1,000,000: roughly O(log n + limit).

It is also stable. Inserting a newer post does not change which rows are older than 981234, and deleting a row on an earlier page does not move anything. Every row is returned exactly once, in order, as long as the sort key itself does not change.

Here is the full round trip, including the "fetch one extra row" trick that tells you whether a next page exists without running COUNT(*):

Composite Cursors for Non-Unique Sort Columns

Sorting by id alone is rare. Feeds sort by created_at, shops by price. None of these are unique: a bulk import can create 500 rows with the same created_at second. If your cursor is only created_at < '2026-10-20 08:15:00', every row that shares that timestamp and did not fit on the previous page is silently skipped. If you use <=, they are repeated forever.

The fix is a tiebreaker: add a unique column (usually the primary key) as the last sort key, and make the cursor a tuple of all sort columns.

CREATE INDEX posts_created_at_id_index ON posts (created_at, id);

-- Row constructor form (MySQL 8 and PostgreSQL can use the index for this range)
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('2026-10-20 08:15:00', 981234)
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- Expanded form: same meaning, works everywhere, and also handles mixed directions
SELECT id, title, created_at FROM posts
WHERE created_at < '2026-10-20 08:15:00'
   OR (created_at = '2026-10-20 08:15:00' AND id < 981234)
ORDER BY created_at DESC, id DESC
LIMIT 21;

Three rules make this work:

1. The ORDER BY must be fully deterministic: the column list ends with something unique.
2. The WHERE comparison uses the same columns in the same order and direction as the ORDER BY.
3. There is an index whose columns match that order, so the database seeks rather than scans. In InnoDB, a secondary index on (created_at) already carries the primary key, but declaring (created_at, id) explicitly makes the intent obvious and keeps the plan predictable.

The row constructor form (a, b) < (x, y) is concise and both MySQL 8 and PostgreSQL optimize it as a range. The expanded OR form says the same thing and is the one you need when directions are mixed, such as ORDER BY price ASC, id DESC: flip the operator per column (price > ? OR (price = ? AND id < ?)) and back it with a matching (price ASC, id DESC) index.

MySQL 8.0 added real descending indexes, so (price ASC, id DESC) can be scanned in one pass. On older versions mixed directions often meant a filesort.

Laravel cursorPaginate

Laravel ships keyset pagination out of the box. Swap paginate() for cursorPaginate() and make sure the ordering ends with a unique column:

namespace App\Http\Controllers\Api;

use App\Http\Resources\PostResource;
use App\Models\Post;
use Illuminate\Http\Request;
use Illuminate\Http\Resources\Json\AnonymousResourceCollection;

final class PostFeedController
{
    public function __invoke(Request $request): AnonymousResourceCollection
    {
        $perPage = min((int) $request->integer('per_page', 20), 100);

        $posts = Post::query()
            ->select(['id', 'title', 'excerpt', 'created_at'])
            ->where('status', 'published')
            ->orderByDesc('created_at')
            ->orderByDesc('id')
            ->cursorPaginate($perPage)
            ->withQueryString();

        return PostResource::collection($posts);
    }
}

Under the hood the paginator fetches perPage + 1 rows, reads the order columns from the query, and builds the expanded where ... or (... and ...) condition from the decoded cursor. The cursor itself is a base64-encoded JSON object of the sort column values plus a flag that says whether it points forward or backward, and it travels in the cursor query string parameter. Returned through an API resource, the meta block contains path, per_page, next_cursor and prev_cursor, and links.next is a ready-to-call URL.

Notice what is missing: there is no total, no last_page, no current_page. The paginator never runs COUNT(*), which on a large filtered table is often the slowest query of the whole request. prev_cursor works too, so "back" navigation is supported.

Things the framework expects from you:

• Every orderBy column must be selected and must not contain NULL values. Cursor values are compared with < and >, and SQL comparisons with NULL are never true.
• Ordering by an expression only works if it is aliased and present in the select. Expressions with bound parameters are not supported.
• The cursor is encoded, not encrypted or signed. A client can decode it, edit the values and send it back. That is harmless for public feeds because the query still applies your where filters, but do not put anything secret in the sort columns.

Rolling Your Own Cursor

Sometimes you want control over the format: a signed cursor that cannot be tampered with, a short string for a mobile client, or the same pagination logic over a raw query builder or an Elasticsearch result. The pattern is small enough to write yourself. First, a value object that encodes and signs the tuple:

namespace App\Support\Pagination;

use InvalidArgumentException;

final class FeedCursor
{
    public function __construct(
        public readonly string $createdAt,
        public readonly int $id,
    ) {}

    public static function fromRow(object $row): self
    {
        return new self((string) $row->created_at, (int) $row->id);
    }

    public function encode(string $key): string
    {
        $payload = json_encode([$this->createdAt, $this->id], JSON_THROW_ON_ERROR);
        $body = rtrim(strtr(base64_encode($payload), '+/', '-_'), '=');
        $sig = substr(hash_hmac('sha256', $body, $key), 0, 16);

        return $body . '.' . $sig;
    }

    public static function decode(string $cursor, string $key): self
    {
        [$body, $sig] = array_pad(explode('.', $cursor, 2), 2, '');

        if (! hash_equals(substr(hash_hmac('sha256', $body, $key), 0, 16), $sig)) {
            throw new InvalidArgumentException('Invalid cursor.');
        }

        $json = base64_decode(strtr($body, '-_', '+/'), true);
        $data = $json === false ? null : json_decode($json, true);

        if (! is_array($data) || count($data) !== 2 || ! is_int($data[1])) {
            throw new InvalidArgumentException('Malformed cursor.');
        }

        return new self((string) $data[0], $data[1]);
    }
}

In the query, decode the incoming cursor, apply the same expanded created_at < ? OR (created_at = ? AND id < ?) condition shown earlier, fetch limit + 1 rows, and encode FeedCursor::fromRow() of the last kept row only when the extra row came back.

Truncating the signature to 16 hex characters keeps the cursor short and still makes forgery impractical. Rotating the key invalidates every outstanding cursor, so clients should treat 400 Invalid cursor as "restart from page one".

API Design With Cursors

Keyset pagination changes the contract, so design the API around it rather than bolting a cursor onto a page-number API:

• Make the cursor opaque. Document it as "a string you send back unchanged", so adding a sort column never breaks clients.
• Return next_cursor and has_more, or null at the end. That is all an infinite scroll or a sync job needs.
• Bind the cursor to the query. If the client changes sort or a filter, the old cursor is meaningless. Either reject it (store a hash of the filters inside the cursor) or document that filters must stay identical across pages.
• Cap limit. min($perPage, 100) stops one client from asking for 50,000 rows in a single request.
• Offer total counts separately, if at all. If the UI needs "1,234 results", serve an approximate count from a cached or estimated value, not a COUNT(*) on every page.
• Use cursors for sync endpoints. "Everything changed since X" is keyset on (updated_at, id).

Testing Keyset Pagination

The bugs live at the boundaries: duplicated timestamps, the last page, and data that changes between requests. A good test walks every page by following links.next until it is null, then checks that every row was seen exactly once. Seeding a block of rows with an identical created_at is what catches a missing tiebreaker.

use App\Models\Post;
use Illuminate\Support\Carbon;

it('walks every post exactly once even when timestamps collide', function () {
    $sameMoment = Carbon::parse('2026-10-20 08:00:00');

    Post::factory()->count(30)->create(['status' => 'published', 'created_at' => $sameMoment]);
    Post::factory()->count(25)->create(['status' => 'published']);

    $seen = [];
    $url = '/api/posts?per_page=7';

    while ($url !== null) {
        $response = $this->getJson($url)->assertOk();
        $seen = [...$seen, ...array_column($response->json('data'), 'id')];
        $url = $response->json('links.next');
    }

    expect($seen)->toHaveCount(55)
        ->and(array_unique($seen))->toHaveCount(55)
        ->and($seen)->toEqualCanonicalizing(Post::pluck('id')->all());
});

A second test worth having: load page 1, insert newer posts, load page 2 and assert the two pages share no ids. A per_page of 7 puts page boundaries inside the identical-timestamp group, so removing orderByDesc('id') fails the test at once.

Common Pitfalls

• No unique tiebreaker. The most common bug. Rows silently disappear at page boundaries whenever the sort column has duplicates.
• Index does not match the order. Keyset is only fast if the database can seek. Check EXPLAIN: you want a range access on your composite index, not filesort.
• Nullable sort columns. NULL breaks comparisons. Use COALESCE into a sentinel and index that expression, or exclude NULL rows, or sort by a non-null column.
• Mutable sort keys. If you sort by updated_at and a row is updated while someone is paging, it can jump ahead of the cursor and be seen twice, or jump behind it and be missed. For sync jobs that is usually what you want; for feeds, sort by something immutable.
• Expecting "jump to page 57". Keyset only knows next and previous. If the product needs numbered pages, keep offset for that screen.
• Filtering after fetching. If you drop rows in PHP after the query (permission checks, soft visibility rules), pages come out short. Push those conditions into SQL.

When to Use Offset and When to Use Keyset

Use offset (paginate() or simplePaginate()) when:

• the data set is small or users never go beyond a few pages, like an admin table with filters;
• the UI needs page numbers, a total count, or "jump to page N";
• the sort order is arbitrary and user-chosen across many columns, and you cannot index every combination.

Use keyset (cursorPaginate() or a hand-written cursor) when:

• the table is large and people or jobs go deep: infinite scroll, activity feeds, notifications, chat history;
• you export, sync or backfill a whole table in batches. For one-off jobs inside Laravel, lazyById() and chunkById() already use keyset internally;
• rows are inserted or deleted while clients are paging and duplicates or gaps are unacceptable;
• the API is public and you want predictable latency regardless of how deep a client goes.

A common middle ground: admin screens keep offset with numbered pages, while the public API and every background job use cursors. The query that took 2.8 seconds on page 4,000 becomes the same 15 ms seek as page 1, and the export stops losing orders.