API Pagination in PHP: Cursor-Based vs. Offset-Based Strategies Compared

1. Why pagination becomes a problem with growing data

Every list endpoint of a PHP API needs a strategy for pagination as soon as the underlying table grows beyond a few thousand rows. Without pagination, a single request could potentially load millions of records, block the backend, and overwhelm the client with a huge JSON response. Choosing the right pagination strategy decides not only response time, but also result consistency when records are inserted or deleted concurrently while navigating multiple pages.

In practice there are two dominant approaches to pagination in PHP APIs: classic offset-based pagination with LIMIT and OFFSET, and cursor-based pagination, often called keyset pagination. Both solve the same underlying problem, but differ significantly in performance characteristics, implementation effort, and behavior when the dataset changes during navigation across multiple pages.

Which pagination strategy is right depends heavily on the use case. An admin backend with page by page navigation and jumps to arbitrary page numbers benefits more from offset pagination, a public API feed with constantly growing data and infinite scroll interfaces benefits almost always from cursor pagination. This article covers both strategies in detail and shows when each choice makes sense.

2. Offset-based pagination in detail

Offset-based pagination is the most intuitive implementation: the client requests a page number and a page size, the API translates that into LIMIT and OFFSET in the SQL query. On page 3 with 20 entries per page, the query reads LIMIT 20 OFFSET 40, the database skips the first 40 rows and returns the next 20. This model is easy to understand, easy to implement, and lets the client jump directly to any page number.

The decisive drawback only shows up as the table grows: with OFFSET 100000, the database actually has to traverse the first 100000 rows to discard them before returning the actually requested rows. This cost grows linearly with the offset value, regardless of page size, which makes deep pages in large tables noticeably slower than the first pages.

<?php

declare(strict_types=1);

/**
 * Classic offset-based pagination against a products table.
 */
final class OffsetProductRepository
{
    public function __construct(private readonly \PDO $pdo)
    {
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function findPage(int $page, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;

        $stmt = $this->pdo->prepare(
            'SELECT id, name, price, created_at
             FROM products
             ORDER BY created_at DESC, id DESC
             LIMIT :limit OFFSET :offset'
        );
        $stmt->bindValue(':limit', $perPage, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}

3. The weaknesses of OFFSET on large tables

Beyond the pure performance question, offset pagination has a second, subtler problem: result consistency during navigation. If a new record is inserted between loading page 1 and page 2 that slides into the sort order before the current position, all subsequent rows shift by one position. The user then either sees the same entry twice or skips an entry entirely, a phenomenon known as page drift.

On very large tables with millions of rows, offset pagination can reach response times in the seconds on deep pages, even with matching indexes, because the database physically has to read the skipped rows to count them. For public APIs with unpredictable user behavior, for example search engine crawlers that systematically dig deep into results, this behavior quickly becomes a real performance risk for the entire infrastructure.

4. Cursor-based pagination: the core principle

Cursor-based pagination, also called keyset pagination, solves both problems at once. Instead of specifying a number of rows to skip, the client remembers the value of the sort column from the last seen record and sends this value as a cursor with the next request. The database can then jump directly to the matching position via the index, without having to read any preceding rows, regardless of how deep in the result set the cursor sits.

This shift from "skip N rows" to "give me everything after this value" makes cursor pagination independent of the position within the overall dataset: page 2 and page 20000 cost the database the same effort, as long as a matching index exists. The price for this: the client can no longer jump directly to an arbitrary page number, only navigate forward or backward starting from the last seen cursor.

5. Implementing keyset pagination in PHP

Implementing keyset pagination in PHP follows a fixed pattern: the sort column, usually a timestamp or an ID, is used as a WHERE condition instead of an OFFSET. With descending sort by created_at, the condition for the next page reads created_at < :cursor_value, with ascending sort correspondingly >. This condition uses the same index that should exist for the ORDER BY clause anyway, which keeps the query consistently fast.

The advantage over offset pagination shows directly in the database's execution plan: instead of a full table scan up to the offset position, the database uses an index seek, which takes constant time regardless of position within the overall dataset. For PHP APIs with large, constantly growing tables, this is the decisive performance gain over the classic alternative.

<?php

declare(strict_types=1);

/**
 * Keyset (cursor-based) pagination against a products table.
 */
final class CursorProductRepository
{
    public function __construct(private readonly \PDO $pdo)
    {
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function findAfter(?string $cursorCreatedAt, ?int $cursorId, int $perPage = 20): array
    {
        $sql = 'SELECT id, name, price, created_at
                FROM products
                WHERE (:no_cursor = 1)
                   OR (created_at < :cursor_created_at)
                   OR (created_at = :cursor_created_at AND id < :cursor_id)
                ORDER BY created_at DESC, id DESC
                LIMIT :limit';

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':no_cursor', $cursorCreatedAt === null ? 1 : 0, \PDO::PARAM_INT);
        $stmt->bindValue(':cursor_created_at', $cursorCreatedAt ?? '', \PDO::PARAM_STR);
        $stmt->bindValue(':cursor_id', $cursorId ?? 0, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $perPage, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}

6. Encoding and decoding opaque cursor tokens

A cursor should be an opaque string to the client, not the raw sort values in plain text. This prevents clients from relying on the internal structure of the cursor or manipulating it to skip records they have no permission for. In PHP, the cursor is typically encoded as a Base64 encoded JSON object that contains all values needed to continue.

This opacity also allows the internal format of the cursor to change later, for example adding an extra sort column, without breaking existing client integrations, as long as the encoding and decoding logic stays centrally encapsulated in a single class.

<?php

declare(strict_types=1);

/**
 * Encodes and decodes opaque pagination cursors.
 */
final class PaginationCursor
{
    private function __construct(
        public readonly ?string $createdAt,
        public readonly ?int $id,
    ) {
    }

    public static function initial(): self
    {
        return new self(null, null);
    }

    public static function fromToken(?string $token): self
    {
        if ($token === null || $token === '') {
            return self::initial();
        }

        $decoded = json_decode(base64_decode($token, true) ?: '', true);

        if (!is_array($decoded) || !isset($decoded['created_at'], $decoded['id'])) {
            throw new \InvalidArgumentException('Invalid pagination cursor.');
        }

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

    public static function fromRow(array $row): string
    {
        $payload = ['created_at' => $row['created_at'], 'id' => $row['id']];

        return base64_encode(json_encode($payload, JSON_THROW_ON_ERROR));
    }
}

7. Stable sorting: unique tie-breaker columns

Sorting by a timestamp alone is not sufficient for cursor pagination, because several records can share the same timestamp, especially during bulk imports or high frequency writes. Without a unique tie-breaker, records with the same timestamp can appear twice or get skipped, the same underlying problem that cursor pagination is supposed to fix.

The solution is a composite sort made of the actual sort criterion and a guaranteed unique column such as the primary key ID, as already implemented above with ORDER BY created_at DESC, id DESC. The index for this query should mirror exactly the same column order, a composite index over (created_at, id), so the database can evaluate the condition without an additional sort step.

8. Pagination metadata in the API response

Besides the actual records, every paginated response should include metadata that allows the client to navigate: a next_cursor for the next page, a has_more flag signaling whether further records exist, and optionally a prev_cursor for backward navigation. This metadata consistently belongs in its own object, usually called meta or pagination, rather than being mixed into the data list itself.

{
  "data": [
    { "id": 4821, "name": "Wireless Keyboard", "price": 59.90 },
    { "id": 4820, "name": "USB-C Hub", "price": 34.50 }
  ],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNy0zMCIsImlkIjo0ODIwfQ==",
    "has_more": true,
    "per_page": 20
  }
}

9. Offset vs. cursor compared directly

The following table summarizes the key differences between the two pagination strategies.

A pragmatic middle ground for many PHP APIs: keep offset pagination for smaller, administrative endpoints and consistently switch public, data heavy list endpoints to cursor pagination as soon as it is foreseeable that the table will grow substantially over time.

10. Summary

API pagination in PHP is not a purely cosmetic decision, it directly affects database load and response times as tables grow. Offset-based pagination remains a legitimate, simple choice for small to medium datasets and admin interfaces with direct page jumps. Cursor-based keyset pagination, on the other hand, solves both the performance problem of deep pages and the consistency problem under concurrent changes, and is almost always the more robust option for public, data heavy PHP APIs.

The technical implementation of cursor pagination in PHP needs three building blocks: a composite, unique sort with a tie-breaker column, a WHERE condition that uses the existing index instead of skipping rows, and opaque, encoded cursor tokens that hide internal details from the client. Whoever gets these three points right builds list endpoints whose response time stays constant regardless of table size.

11. FAQ: API Pagination in PHP