Introduction

Collection endpoints can contain thousands or millions of resources. Returning an entire collection in one response can consume excessive database, memory, serialization, network, and client-processing resources.

Pagination divides a collection into smaller result sets that clients can retrieve incrementally.

A complete pagination contract defines:

• The default page size
• The maximum page size
• The result ordering
• The navigation parameters
• The response metadata
• The behaviour under concurrent inserts and deletes
• The treatment of filters and sorting
• The cursor format and lifetime
• The availability of total counts
• The behaviour at collection boundaries

In the System Design curriculum, Pagination is Topic 4.5 under API Design & Service Contracts. It follows resource modeling and precedes versioning, idempotency, authentication versus authorization, and rate-limit semantics.

Prerequisites

Why Pagination Is Required

Without pagination, a single collection request can attempt to retrieve, serialize, and transfer every matching resource.

Unbounded collection request
        |
        v
Large database result
        |
        v
High application memory usage
        |
        v
Large response payload
        |
        v
Long transfer and client-processing time

Pagination provides:

• Bounded response sizes
• Predictable memory usage
• Lower network transfer per request
• Incremental client rendering
• Protection against accidental full-table retrieval
• Better control over database work
• A clear navigation contract

Pagination Strategies

Page-number Pagination

Page-number pagination allows clients to request a numbered page with a selected page size.

GET /orders?page=3&pageSize=20 HTTP/1.1
Host: api.example.com
Accept: application/json

The server commonly calculates the offset as:

\[ Offset = (Page - 1) \times PageSize \]

For page 3 with a page size of 20:

\[ Offset = (3 - 1) \times 20 = 40 \]

SQL Example

SELECT
    id,
    customer_id,
    status,
    total_amount,
    created_at
FROM orders
ORDER BY
    created_at DESC,
    id DESC
LIMIT 20
OFFSET 40;

Page-number pagination is easy for users to understand and supports direct navigation to a particular page.

Offset Pagination

Offset pagination allows the client to specify how many matching rows should be skipped before the server returns the next result set.

GET /orders?limit=20&offset=40 HTTP/1.1
Host: api.example.com
Accept: application/json

SQL Example

SELECT
    id,
    customer_id,
    status,
    total_amount,
    created_at
FROM orders
ORDER BY
    created_at DESC,
    id DESC
LIMIT 20
OFFSET 40;

Offset Pagination Advantages

• Easy to understand
• Easy to implement
• Supports direct page navigation
• Works naturally with many database frameworks
• Can accompany a total count

Offset Pagination Limitations

• Large offsets can require increasing database work
• Concurrent inserts can shift page boundaries
• Concurrent deletes can cause items to be skipped
• Clients can receive duplicate items between pages
• Deep page requests can create expensive queries

Page Drift

Page drift occurs when the collection changes between offset-based page requests.

Initial ordered collection:

A B C D E F


Page 1 with limit 3:

A B C


A new item X is inserted at the beginning:

X A B C D E F


Page 2 with offset 3:

C D E


Result:

C appears on both pages.

Deletions can produce the opposite problem, where an item shifts into a previously skipped range and is never returned to the client.

Cursor Pagination

Cursor pagination uses an opaque continuation token that represents a position within an ordered result set.

First Request

GET /orders?limit=20 HTTP/1.1
Host: api.example.com
Accept: application/json

First Response

{
  "items": [
    {
      "id": "ord_9001",
      "status": "confirmed",
      "createdAt": "2026-09-22T08:30:00Z"
    }
  ],
  "page": {
    "limit": 20,
    "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTA5LTIyVDA4OjMwOjAwWiIsImlkIjoib3JkXzkwMDEifQ",
    "hasMore": true
  }
}

Next Request

GET /orders?limit=20&after=eyJjcmVhdGVkQXQiOiIyMDI2LTA5LTIyVDA4OjMwOjAwWiIsImlkIjoib3JkXzkwMDEifQ HTTP/1.1
Host: api.example.com
Accept: application/json

The client treats the cursor as an opaque value. The client should not parse, modify, or create cursor values.

Keyset Pagination

Keyset pagination uses the values of the last returned item's sort keys to retrieve the next page.

Ordering:

created_at DESC,
id DESC


Last item from previous page:

created_at = 2026-09-22T08:30:00Z
id = ord_9001


Next page:

Return rows positioned after that
created_at and id combination.

SQL Example

SELECT
    id,
    customer_id,
    status,
    total_amount,
    created_at
FROM orders
WHERE
    created_at < :cursor_created_at
    OR (
        created_at = :cursor_created_at
        AND id < :cursor_id
    )
ORDER BY
    created_at DESC,
    id DESC
LIMIT 21;

The query requests one more row than the public page size. If the page size is 20 and the query returns 21 rows, the server can return the first 20 and report that more results are available.

Stable and Deterministic Ordering

Every pagination strategy requires explicit ordering. Cursor and keyset pagination require ordering that uniquely determines each item's position.

ORDER BY created_at DESC

Several rows can have the same created_at value, so the order between those rows is not uniquely defined.

ORDER BY
    created_at DESC,
    id DESC

A unique tiebreaker ensures that every item has one deterministic position in the ordered result.

Offset vs Cursor Pagination

Opaque Cursor Design

A cursor can internally contain the values required to continue the ordered query.

{
  "version": 1,
  "createdAt": "2026-09-22T08:30:00Z",
  "id": "ord_9001",
  "direction": "forward"
}

The server can serialize and encode this structure before returning it to the client.

A cursor can include:

• A cursor-format version
• Sort-key values
• A unique tiebreaker
• The navigation direction
• A filter or query fingerprint
• An expiration value where required

Encoding is not encryption or integrity protection. Sensitive cursor data should not be exposed merely by converting it to Base64.

Cursor Validation

A server should treat every cursor as untrusted input.

Cursor validation can include:

• Maximum encoded length
• Valid encoding
• Valid structure
• Supported cursor version
• Expected field types
• Supported direction
• Filter and sort compatibility
• Expiration policy
• Integrity verification where used

Invalid Cursor Response

HTTP/1.1 400 Bad Request
Content-Type: application/problem+json

{
  "type": "invalid-pagination-cursor",
  "title": "Invalid pagination cursor",
  "status": 400,
  "detail": "The supplied cursor cannot be used for this collection request."
}

Cursors, Filters, and Sorting

A cursor generated for one filtered and sorted result set should not be reused with incompatible parameters.

Original request:

status = confirmed
sort = createdAt descending


Next request using cursor:

status = cancelled
sort = total ascending


Problem:

The cursor position no longer describes
the requested ordered result set.

The API can choose one of these contracts:

• Reject changed filters or sorting when a cursor is supplied
• Include a query fingerprint inside the cursor
• Bind the cursor to stored server-side pagination state

Forward and Backward Pagination

Some APIs support only forward traversal. Others support both directions.

Forward Request

GET /orders?limit=20&after=NEXT_CURSOR HTTP/1.1

Backward Request

GET /orders?limit=20&before=PREVIOUS_CURSOR HTTP/1.1

Backward pagination requires careful reversal of comparison and ordering logic. Results should still be presented in the collection's documented public order.

Pagination Response Contract

{
  "items": [
    {
      "id": "ord_9001",
      "status": "confirmed"
    },
    {
      "id": "ord_9000",
      "status": "processing"
    }
  ],
  "page": {
    "limit": 20,
    "nextCursor": "opaque-next-cursor",
    "previousCursor": null,
    "hasMore": true
  },
  "links": {
    "self": "/orders?limit=20",
    "next": "/orders?limit=20&after=opaque-next-cursor"
  }
}

A cursor response can include:

• The returned items
• The effective page size
• The next cursor
• The previous cursor where supported
• A flag indicating more results
• Navigation links

Total Counts

Some clients require a total matching count. Calculating an exact total can require a separate database aggregation over the complete filtered result.

SELECT
    COUNT(*)
FROM orders
WHERE
    tenant_id = :tenant_id
    AND status = :status;

The API contract should define whether the count is:

• Exact
• Estimated
• Cached
• Computed only when explicitly requested
• Unavailable for selected collection types

Every page request performs:

1. The paginated data query
2. A complete matching COUNT query

Even when the client does not display the total.

Default response:

Returns page items and hasMore.


Optional response:

Returns total count only when the
client requests and is authorized for it.

PHP Cursor Pagination Example

<?php

declare(strict_types=1);

const DEFAULT_PAGE_SIZE = 20;
const MAXIMUM_PAGE_SIZE = 100;

function readPageSize(
    array $query
): int {
    $requested =
        filter_var(
            $query['limit'] ??
                DEFAULT_PAGE_SIZE,
            FILTER_VALIDATE_INT
        );

    if ($requested === false ||
        $requested < 1) {

        throw new InvalidArgumentException(
            'The page size must be a positive integer.'
        );
    }

    return min(
        $requested,
        MAXIMUM_PAGE_SIZE
    );
}

function encodeCursor(
    string $createdAt,
    string $id
): string {
    $payload =
        json_encode(
            [
                'version' => 1,
                'createdAt' => $createdAt,
                'id' => $id
            ],
            JSON_THROW_ON_ERROR
        );

    return rtrim(
        strtr(
            base64_encode($payload),
            '+/',
            '-_'
        ),
        '='
    );
}

function decodeCursor(
    string $cursor
): array {
    if (strlen($cursor) > 1000) {
        throw new InvalidArgumentException(
            'The cursor is too long.'
        );
    }

    $padding =
        strlen($cursor) % 4;

    if ($padding !== 0) {
        $cursor .=
            str_repeat(
                '=',
                4 - $padding
            );
    }

    $decoded =
        base64_decode(
            strtr(
                $cursor,
                '-_',
                '+/'
            ),
            true
        );

    if ($decoded === false) {
        throw new InvalidArgumentException(
            'The cursor encoding is invalid.'
        );
    }

    $payload =
        json_decode(
            $decoded,
            true,
            16,
            JSON_THROW_ON_ERROR
        );

    if (
        !is_array($payload) ||
        ($payload['version'] ?? null) !== 1 ||
        !is_string(
            $payload['createdAt'] ?? null
        ) ||
        !is_string(
            $payload['id'] ?? null
        )
    ) {
        throw new InvalidArgumentException(
            'The cursor structure is invalid.'
        );
    }

    return $payload;
}

The example demonstrates length checks, strict decoding, cursor versioning, and structural validation. A production application can additionally sign or encrypt cursors when required by the threat model.

PHP Database Query

<?php

declare(strict_types=1);

$pageSize =
    readPageSize(
        $_GET
    );

$cursor =
    isset($_GET['after'])
        ? decodeCursor(
            (string)$_GET['after']
        )
        : null;

$sql = '
    SELECT
        public_id,
        status,
        total_amount,
        currency,
        created_at
    FROM orders
    WHERE
        tenant_id = :tenant_id
';

$parameters = [
    'tenant_id' => $tenantId
];

if ($cursor !== null) {
    $sql .= '
        AND (
            created_at < :cursor_created_at
            OR (
                created_at = :cursor_created_at
                AND public_id < :cursor_id
            )
        )
    ';

    $parameters['cursor_created_at'] =
        $cursor['createdAt'];

    $parameters['cursor_id'] =
        $cursor['id'];
}

$sql .= '
    ORDER BY
        created_at DESC,
        public_id DESC
    LIMIT :query_limit
';

$statement =
    $pdo->prepare(
        $sql
    );

foreach (
    $parameters as
    $name => $value
) {
    $statement->bindValue(
        ':' . $name,
        $value,
        PDO::PARAM_STR
    );
}

$statement->bindValue(
    ':query_limit',
    $pageSize + 1,
    PDO::PARAM_INT
);

$statement->execute();

$rows =
    $statement->fetchAll(
        PDO::FETCH_ASSOC
    );

$hasMore =
    count($rows) > $pageSize;

if ($hasMore) {
    array_pop($rows);
}

$nextCursor = null;

if ($hasMore && $rows !== []) {
    $lastRow =
        $rows[
            array_key_last($rows)
        ];

    $nextCursor =
        encodeCursor(
            $lastRow['created_at'],
            $lastRow['public_id']
        );
}

$response = [
    'items' => $rows,
    'page' => [
        'limit' => $pageSize,
        'nextCursor' => $nextCursor,
        'hasMore' => $hasMore
    ]
];

header(
    'Content-Type: application/json; charset=utf-8'
);

echo json_encode(
    $response,
    JSON_THROW_ON_ERROR
);

Authorization and tenant restrictions must be applied inside the database query before pagination. Filtering unauthorized rows after retrieving a page can produce incomplete pages and can disclose collection information.

Supporting Database Index

CREATE INDEX ix_orders_tenant_created_id
ON orders
(
    tenant_id,
    created_at DESC,
    public_id DESC
);

The most appropriate index depends on the database platform, data distribution, filters, selected fields, and query plan. Validate the actual query plan rather than assuming that an index will always be selected.

GraphQL Connection Pagination

type OrderConnection {
  edges: [OrderEdge!]!
  nodes: [Order!]!
  pageInfo: PageInfo!
}

type OrderEdge {
  cursor: String!
  node: Order!
}

type PageInfo {
  startCursor: String
  endCursor: String
  hasPreviousPage: Boolean!
  hasNextPage: Boolean!
}

type Query {
  orders(
    first: Int = 20
    after: String
  ): OrderConnection!
}

GraphQL Query

query GetOrders(
  $first: Int!
  $after: String
) {
  orders(
    first: $first
    after: $after
  ) {
    nodes {
      id
      status
      createdAt
    }
    pageInfo {
      endCursor
      hasNextPage
    }
  }
}

gRPC Pagination

message ListOrdersRequest {
  int32 page_size = 1;
  string page_token = 2;
  OrderStatus status = 3;
}

message ListOrdersResponse {
  repeated Order orders = 1;
  string next_page_token = 2;
}

The page token should be treated as an opaque continuation value. The service should enforce its own default and maximum page sizes even when the client requests a larger value.

Authorization and Pagination

Authorization filtering must occur before page boundaries are applied.

1. Retrieve 20 orders.
2. Remove unauthorized orders.
3. Return the remaining 7 orders.

1. Establish caller identity.
2. Apply tenant and authorization conditions.
3. Apply collection filters.
4. Apply stable ordering.
5. Retrieve the first 20 authorized resources.

The cursor should not permit a caller to escape the caller's authorized tenant or data scope.

Pagination Security Controls

• Enforce a maximum page size.
• Validate all cursor structures.
• Reject unsupported sort fields.
• Allowlist filtering operators.
• Apply authorization before pagination.
• Prevent arbitrary SQL field injection.
• Limit cursor and query-string length.
• Apply request-rate limits.
• Use finite database and request deadlines.
• Avoid placing sensitive values in exposed cursors.

Pagination Observability

Useful pagination metrics include:

• Requested and effective page size
• Returned item count
• Pagination strategy
• Database query duration
• Rows examined where available
• Deep offset frequency
• Invalid cursor rate
• Page traversal depth
• Total-count query duration
• Response payload size
• Query timeout rate
• Maximum-page-size violations

Cursor values can contain encoded internal information. Logs should redact or hash them when the data-handling policy requires it.

Test Pagination with curl

Offset Request

curl -i \
  'https://api.example.com/orders?limit=20&offset=40'

Cursor Request

curl -i \
  'https://api.example.com/orders?limit=20&after=OPAQUE_CURSOR'

Filtered Cursor Request

curl -i \
  'https://api.example.com/orders?status=confirmed&limit=20&after=OPAQUE_CURSOR'

Replace the example endpoint and cursor with approved test values. Avoid including production credentials or sensitive cursors in shell history.

Pagination Troubleshooting Workflow

1. Confirm the endpoint, filters, sorting, and page parameters.
2. Confirm the effective default and maximum page size.
3. Verify that ordering is explicit and deterministic.
4. Verify that a unique tiebreaker is included.
5. Decode cursors only in an authorized diagnostic environment.
6. Confirm cursor compatibility with the current filters and sorting.
7. Inspect the generated database query.
8. Inspect the query plan and supporting indexes.
9. Test concurrent inserts and deletes between page requests.
10. Verify authorization filtering before pagination.
11. Check whether exact total counts are adding significant cost.
12. Compare runtime responses with the published contract.

Common Pagination Mistakes

Recommended Test Cases

Pagination Best Practices

Practice Exercise

Implement offset and cursor pagination for an order collection, then compare correctness and database performance under concurrent changes.

Requirements

1. Create an order collection containing test records.
2. Define ordering by creation time and public ID.
3. Implement offset pagination.
4. Implement cursor pagination.
5. Use a default page size of 20.
6. Enforce a maximum page size of 100.
7. Use an opaque, versioned cursor.
8. Validate cursor structure and length.
9. Bind the cursor to the filter and sort contract.
10. Apply tenant authorization before pagination.
11. Request one extra row to calculate hasMore.
12. Create a supporting composite index.
13. Insert a new order between page requests.
14. Delete an order between page requests.
15. Compare duplicates, omissions, query plans, and response times.

Comparison Template

Frequently Asked Questions