Implementing Cursor-Based Pagination in Technical Detail

1. Why offset pagination becomes unreliable on changing data

Pagination on most REST APIs starts out deceptively simple: a client asks for a slice of a list with ?page=3&limit=20 or ?offset=40&limit=20, and the database returns exactly that slice via OFFSET and LIMIT. As long as the underlying dataset stays put between two requests, this pattern works reliably and takes only a few lines of SQL. The trouble starts as soon as rows get inserted or deleted while a client is still working through the result list, because every subsequent page shifts relative to the original request.

A concrete example makes the problem tangible: a list of orders sorted descending by created_at, a user loads page 1 with the twenty newest entries. If a new order arrives between loading page 1 and page 2, the entire dataset shifts by one position, and OFFSET 20 now returns an item that was already visible on page 1, a duplicate in the UI. If a row is deleted instead, an item can be skipped entirely without the client ever noticing. For lists with high write frequency, such as activity feeds or order lists, this is not an edge case, it is the norm.

2. How cursor pagination solves the problem structurally

Cursor pagination drops the idea of a numeric position (the nth row) and instead anchors to an actual record as the starting point: give me the next twenty entries after this one. That anchor stays stable even if rows change before it, because it does not refer to a position in the result set but to a concrete value in the sort key. This is why the technique is also called keyset pagination, the key rather than the position defines the starting point for the next page.

In practice, this anchor is handed back to the client as an opaque cursor token, a string the client stores and resends unchanged on the next request without knowing or interpreting its contents. Behind the scenes, that token is usually a combination of the sort key value (for example created_at) and a unique id, encoded as JSON and then base64 encoded so it can travel as a single, URL-safe string. This opacity is intentional: the client should never construct or manipulate the token itself, only pass it along as a black box.

3. Implementing it with Symfony and Doctrine: the core query

The conceptual SQL condition for keyset pagination is WHERE (sort_key, id) > (cursor_key, cursor_id), a row value comparison that MySQL 8 and PostgreSQL support natively. Since Doctrine's QueryBuilder does not map that comparison directly, the condition is expressed in practice as a logical disjunction: either the sort key is strictly greater than the cursor value, or it is equal and the id is greater. Both formulations return the same result, but the OR version is more portable and works in any Doctrine version without extra setup.

It matters that both the sort key and the id are part of the same composite index, otherwise the database still has to scan a large part of the table despite the lean WHERE clause. For the first page there is no cursor yet, in that case the WHERE condition is simply omitted and the query returns the first n rows in sort order. The repository below shows the full implementation, including encoding and decoding of the cursor token.

<?php
declare(strict_types=1);

final class ProductCursorRepository
{
    public function __construct(private readonly Connection $connection)
    {
    }

    /**
     * Loads the next page via keyset pagination.
     * Sort key: created_at, tie breaker: id (both indexed).
     */
    public function findNextPage(?string $cursor, int $limit): array
    {
        $qb = $this->connection->createQueryBuilder()
            ->select('id', 'name', 'price', 'created_at')
            ->from('product')
            ->orderBy('created_at', 'ASC')
            ->addOrderBy('id', 'ASC')
            ->setMaxResults($limit + 1);

        if ($cursor !== null) {
            [$cursorCreatedAt, $cursorId] = $this->decodeCursor($cursor);
            $qb->andWhere('(created_at > :createdAt) OR (created_at = :createdAt AND id > :id)')
                ->setParameter('createdAt', $cursorCreatedAt)
                ->setParameter('id', $cursorId);
        }

        return $qb->executeQuery()->fetchAllAssociative();
    }

    private function decodeCursor(string $cursor): array
    {
        $decoded = json_decode(base64_decode($cursor, true), true, flags: JSON_THROW_ON_ERROR);

        if (!isset($decoded['created_at'], $decoded['id'])) {
            throw new InvalidArgumentException('Cursor token is malformed or incomplete.');
        }

        return [$decoded['created_at'], (int) $decoded['id']];
    }

    public function encodeCursor(string $createdAt, int $id): string
    {
        return base64_encode(json_encode(['created_at' => $createdAt, 'id' => $id], JSON_THROW_ON_ERROR));
    }
}

4. How the cursor token is built and encoded

A cursor token essentially contains exactly the values needed for the next page's WHERE clause: the sort key value and the id of the last item on the current page. These values are serialized as an associative array into JSON and then base64 encoded, so that arbitrary date formats, special characters, or numbers travel safely inside a single URL parameter without extra escaping.

When decoding on the server, an incoming token should never be trusted blindly: a tampered or simply malformed base64 fragment must result in a clean 400 response instead of an unhandled exception. Type checking the decoded values matters just as much, since a client could in theory send a syntactically valid but semantically wrong token, for example an id encoded as a string instead of a number. A robust implementation therefore explicitly validates that the expected fields are present and of the expected type before they flow into the query.

5. Why cursor pagination performs noticeably better on large tables

With OFFSET n, the database first has to read (or at least walk through the index positions of) n rows and discard them before returning the rows actually requested. That cost grows linearly with page depth: page 1000 with OFFSET 20000 is noticeably slower than page 1, even though both requests return the same number of rows. On tables with several million rows this effect quickly becomes a visible performance problem, especially for infinite scroll interfaces that page deep into a list.

The WHERE clause used by cursor pagination, by contrast, translates into a classic index seek: the database jumps directly to the right position in the composite index and reads the next n rows from there, regardless of how deep that position sits in the overall list. Response time therefore stays roughly constant across pages, a decisive advantage for APIs with large, growing datasets where users routinely page or scroll deep into a list.

6. Why the id as a tie-breaker is not optional

A plain sort key like created_at is rarely truly unique in practice: if several rows are created within the same second or millisecond, for example through a batch import, they share the exact same timestamp. Without a second, guaranteed unique comparison value, the database cannot reliably tell which rows were already delivered and which still remain when sort keys tie, leading right back to the skipped or duplicated entries that cursor pagination was supposed to fix in the first place.

The fix is combining the sort key with a unique id as a tie-breaker, both in the ordering and in the WHERE clause. That produces a genuine total order over the result set, every row has a unique position relative to every other row. This requires a composite index on exactly these two columns in exactly this order, without that index the performance advantage over offset pagination mostly evaporates.

7. Paging forward and backward: bidirectional cursors

A complete pagination API usually needs more than a next_cursor, it also needs a way to page back to the previous page. In practice that means every response carries both a cursor for the next page and one for the previous page, together with hasNextPage and hasPreviousPage flags. To determine hasNextPage reliably without an extra COUNT query, implementations commonly fetch one row more than they display (limit plus one), and its presence signals that another page exists.

Paging backward requires flipping both the comparison direction in the WHERE clause and the sort direction, so the database effectively walks backward through the index. The resulting rows come back in reverse order though, and need to be flipped once more before returning them to the client, so the display order stays consistent across both paging directions. This logic is worth encapsulating centrally once, rather than reimplementing it in every endpoint.

8. Limits and downsides of cursor pagination

The most obvious downside is losing the ability to jump directly to an arbitrary page: with offset pagination, jump straight to page 47 is trivially OFFSET 940, with cursor pagination that is not structurally possible, since every cursor only knows the immediately next position. For UIs with classic page-number navigation (1, 2, 3, ... 47), cursor pagination is a poor fit or requires a compromise, such as a hybrid approach for the first few pages only.

Cursor pagination also does not solve the problem of showing an exact total result count, since a full COUNT query over a very large table stays expensive regardless of the pagination technique used. If an exact total is genuinely required, it needs a separate strategy, such as a cached, periodically refreshed counter or a deliberately approximate estimate, instead of counting the entire table live on every request.

9. When cursor pagination beats offset pagination, and vice versa

The choice between the two techniques comes down to a handful of criteria: how volatile the underlying data is, how large the table is, and whether the UI needs classic page-number navigation or rather infinite scroll or feed-style loading. The table below summarizes the key points for a quick decision on a concrete project.

10. Summary

11. FAQ: Cursor-Based Pagination: The Essentials at a Glance