CursorPagination [pattern] v1.0.0

Cursor-based pagination uses an opaque cursor (typically encoding a sort key + tiebreaker id) to mark a position in a sorted collection. Unlike offset pagination, cursors remain stable under concurrent inserts and deletes, scale to billion-row tables, and never produce duplicate or skipped rows.

Body

    # Cursor (Keyset) Pagination

    ## The Problem with Offset Pagination

    `SELECT * FROM users ORDER BY created_at DESC LIMIT 50 OFFSET 1000` requires the database to scan and discard the first 1000 rows. At offset 1,000,000 this becomes prohibitive. Worse, if rows are inserted/deleted between requests:
    - INSERT at top → page 2 shows duplicates from page 1
    - DELETE at top → page 2 skips rows
    - The same `?page=N` URL returns different content over time (non-stable)

    ## Cursor Pagination (a.k.a. Keyset Pagination)

    Sort by a stable, indexed column (e.g. `created_at DESC, id DESC` — id breaks ties for identical timestamps). The cursor encodes the last-seen row's sort key tuple.

    ```sql
    -- Page 1
    SELECT * FROM users
    ORDER BY created_at DESC, id DESC
    LIMIT 51;  -- fetch one extra to know if there's a next page

    -- Cursor for page 2 = base64({ created_at: '...', id: '...' }) of the LAST row on page 1.

    -- Page 2
    SELECT * FROM users
    WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
    ORDER BY created_at DESC, id DESC
    LIMIT 51;
    ```

    ## API Surface

    ```http
    GET /v1/users?limit=50
    →
    {
      "data": [...50 users...],
      "has_more": true,
      "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wNyIsImlkIjoidV9hYmMifQ=="
    }

    GET /v1/users?limit=50&cursor=eyJjcm...
    →
    {
      "data": [...next 50 users...],
      "has_more": true,
      "next_cursor": "..."
    }
    ```

    ## Cursor Encoding

    Cursors MUST be opaque to clients (base64 of a JSON object signed with HMAC, or a row-id hashed via app secret). Clients should never construct or modify cursors. This:
    - Allows server to evolve cursor schema without breaking clients
    - Prevents enumeration attacks
    - Allows clients to treat cursor as a black-box token

    ```typescript
    function encodeCursor(row: { created_at: Date; id: string }): string {
      const json = JSON.stringify({ ts: row.created_at.toISOString(), id: row.id });
      const signed = hmac('SHA256', SECRET, json);
      return base64url(json + '.' + signed);
    }

    function decodeCursor(cursor: string): { ts: Date; id: string } {
      const [json, sig] = base64url.decode(cursor).split('.');
      if (hmac('SHA256', SECRET, json) !== sig) throw new InvalidCursorError();
      const { ts, id } = JSON.parse(json);
      return { ts: new Date(ts), id };
    }
    ```

    ## Bidirectional Cursors (next + prev)

    Stripe API uses `starting_after=id` and `ending_before=id`. Linear/Relay GraphQL uses `after`/`before` cursors with `first`/`last` counts. Both work; pick one and document.

    ## When Offset Is Acceptable

    Offset pagination is acceptable for: (1) admin tools where the dataset is < 10K rows, (2) UIs that show 'page N of M' with a known total. For both, accept that `?page=N` means 'slice at this offset' — not 'this fixed item'.
  

Use When

• Collection has > 10K rows OR is expected to grow unbounded
• Inserts/deletes happen concurrently with browsing (timeline, feed, message log)
• Public API where stable cursor semantics matter for client retries
• Time-series data (events, logs, metrics) — natural sort order
• GraphQL endpoints implementing the Relay cursor connection spec

Avoid When

• Static dataset < 1000 rows — overhead not justified
• Need 'jump to page N' UI with a known total — use offset and accept the cost
• Sort order depends on user-supplied filter that changes between requests — cursors lose stability

Counter Examples

• Cursor exposed as raw row id — client can construct arbitrary cursors, bypassing intended ordering, leaking enumeration of private rows.
• Cursor includes only created_at (no tiebreaker id) — rows with identical timestamps cause duplicates or skips on page boundaries.
• Server returns total: N alongside cursor — total count requires a full table scan; defeats the performance benefit.

CursorPagination [pattern] v1.0.0

Cursor-based pagination uses an opaque cursor (typically encoding a sort key + tiebreaker id) to mark a position in a sorted collection. Unlike offset pagination, cursors remain stable under concurrent inserts and deletes, scale to billion-row tables, and never produce duplicate or skipped rows.

Body

    # Cursor (Keyset) Pagination

    ## The Problem with Offset Pagination

    `SELECT * FROM users ORDER BY created_at DESC LIMIT 50 OFFSET 1000` requires the database to scan and discard the first 1000 rows. At offset 1,000,000 this becomes prohibitive. Worse, if rows are inserted/deleted between requests:
    - INSERT at top → page 2 shows duplicates from page 1
    - DELETE at top → page 2 skips rows
    - The same `?page=N` URL returns different content over time (non-stable)

    ## Cursor Pagination (a.k.a. Keyset Pagination)

    Sort by a stable, indexed column (e.g. `created_at DESC, id DESC` — id breaks ties for identical timestamps). The cursor encodes the last-seen row's sort key tuple.

    ```sql
    -- Page 1
    SELECT * FROM users
    ORDER BY created_at DESC, id DESC
    LIMIT 51;  -- fetch one extra to know if there's a next page

    -- Cursor for page 2 = base64({ created_at: '...', id: '...' }) of the LAST row on page 1.

    -- Page 2
    SELECT * FROM users
    WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
    ORDER BY created_at DESC, id DESC
    LIMIT 51;
    ```

    ## API Surface

    ```http
    GET /v1/users?limit=50
    →
    {
      "data": [...50 users...],
      "has_more": true,
      "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wNyIsImlkIjoidV9hYmMifQ=="
    }

    GET /v1/users?limit=50&cursor=eyJjcm...
    →
    {
      "data": [...next 50 users...],
      "has_more": true,
      "next_cursor": "..."
    }
    ```

    ## Cursor Encoding

    Cursors MUST be opaque to clients (base64 of a JSON object signed with HMAC, or a row-id hashed via app secret). Clients should never construct or modify cursors. This:
    - Allows server to evolve cursor schema without breaking clients
    - Prevents enumeration attacks
    - Allows clients to treat cursor as a black-box token

    ```typescript
    function encodeCursor(row: { created_at: Date; id: string }): string {
      const json = JSON.stringify({ ts: row.created_at.toISOString(), id: row.id });
      const signed = hmac('SHA256', SECRET, json);
      return base64url(json + '.' + signed);
    }

    function decodeCursor(cursor: string): { ts: Date; id: string } {
      const [json, sig] = base64url.decode(cursor).split('.');
      if (hmac('SHA256', SECRET, json) !== sig) throw new InvalidCursorError();
      const { ts, id } = JSON.parse(json);
      return { ts: new Date(ts), id };
    }
    ```

    ## Bidirectional Cursors (next + prev)

    Stripe API uses `starting_after=id` and `ending_before=id`. Linear/Relay GraphQL uses `after`/`before` cursors with `first`/`last` counts. Both work; pick one and document.

    ## When Offset Is Acceptable

    Offset pagination is acceptable for: (1) admin tools where the dataset is < 10K rows, (2) UIs that show 'page N of M' with a known total. For both, accept that `?page=N` means 'slice at this offset' — not 'this fixed item'.
  

Use When

• Collection has > 10K rows OR is expected to grow unbounded
• Inserts/deletes happen concurrently with browsing (timeline, feed, message log)
• Public API where stable cursor semantics matter for client retries
• Time-series data (events, logs, metrics) — natural sort order
• GraphQL endpoints implementing the Relay cursor connection spec

Avoid When

• Static dataset < 1000 rows — overhead not justified
• Need 'jump to page N' UI with a known total — use offset and accept the cost
• Sort order depends on user-supplied filter that changes between requests — cursors lose stability

Counter Examples

• Cursor exposed as raw row id — client can construct arbitrary cursors, bypassing intended ordering, leaking enumeration of private rows.
• Cursor includes only created_at (no tiebreaker id) — rows with identical timestamps cause duplicates or skips on page boundaries.
• Server returns total: N alongside cursor — total count requires a full table scan; defeats the performance benefit.

Examples

• Stripe: list charges, customers, invoices — all use starting_after/ending_before opaque cursors. has_more boolean.
• Twitter API v2: pagination_token cursor + max_results (default 10, max 100).
• GitHub GraphQL: Relay pageInfo { hasNextPage, hasPreviousPage, startCursor, endCursor } on every connection.
• Slack API: cursor parameter; response includes response_metadata.next_cursor.

Body

    # Cursor (Keyset) Pagination

    ## The Problem with Offset Pagination

    `SELECT * FROM users ORDER BY created_at DESC LIMIT 50 OFFSET 1000` requires the database to scan and discard the first 1000 rows. At offset 1,000,000 this becomes prohibitive. Worse, if rows are inserted/deleted between requests:
    - INSERT at top → page 2 shows duplicates from page 1
    - DELETE at top → page 2 skips rows
    - The same `?page=N` URL returns different content over time (non-stable)

    ## Cursor Pagination (a.k.a. Keyset Pagination)

    Sort by a stable, indexed column (e.g. `created_at DESC, id DESC` — id breaks ties for identical timestamps). The cursor encodes the last-seen row's sort key tuple.

    ```sql
    -- Page 1
    SELECT * FROM users
    ORDER BY created_at DESC, id DESC
    LIMIT 51;  -- fetch one extra to know if there's a next page

    -- Cursor for page 2 = base64({ created_at: '...', id: '...' }) of the LAST row on page 1.

    -- Page 2
    SELECT * FROM users
    WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
    ORDER BY created_at DESC, id DESC
    LIMIT 51;
    ```

    ## API Surface

    ```http
    GET /v1/users?limit=50
    →
    {
      "data": [...50 users...],
      "has_more": true,
      "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wNyIsImlkIjoidV9hYmMifQ=="
    }

    GET /v1/users?limit=50&cursor=eyJjcm...
    →
    {
      "data": [...next 50 users...],
      "has_more": true,
      "next_cursor": "..."
    }
    ```

    ## Cursor Encoding

    Cursors MUST be opaque to clients (base64 of a JSON object signed with HMAC, or a row-id hashed via app secret). Clients should never construct or modify cursors. This:
    - Allows server to evolve cursor schema without breaking clients
    - Prevents enumeration attacks
    - Allows clients to treat cursor as a black-box token

    ```typescript
    function encodeCursor(row: { created_at: Date; id: string }): string {
      const json = JSON.stringify({ ts: row.created_at.toISOString(), id: row.id });
      const signed = hmac('SHA256', SECRET, json);
      return base64url(json + '.' + signed);
    }

    function decodeCursor(cursor: string): { ts: Date; id: string } {
      const [json, sig] = base64url.decode(cursor).split('.');
      if (hmac('SHA256', SECRET, json) !== sig) throw new InvalidCursorError();
      const { ts, id } = JSON.parse(json);
      return { ts: new Date(ts), id };
    }
    ```

    ## Bidirectional Cursors (next + prev)

    Stripe API uses `starting_after=id` and `ending_before=id`. Linear/Relay GraphQL uses `after`/`before` cursors with `first`/`last` counts. Both work; pick one and document.

    ## When Offset Is Acceptable

    Offset pagination is acceptable for: (1) admin tools where the dataset is < 10K rows, (2) UIs that show 'page N of M' with a known total. For both, accept that `?page=N` means 'slice at this offset' — not 'this fixed item'.
  

Use When

• Collection has > 10K rows OR is expected to grow unbounded
• Inserts/deletes happen concurrently with browsing (timeline, feed, message log)
• Public API where stable cursor semantics matter for client retries
• Time-series data (events, logs, metrics) — natural sort order
• GraphQL endpoints implementing the Relay cursor connection spec

Avoid When

• Static dataset < 1000 rows — overhead not justified
• Need 'jump to page N' UI with a known total — use offset and accept the cost
• Sort order depends on user-supplied filter that changes between requests — cursors lose stability

Counter Examples

• Cursor exposed as raw row id — client can construct arbitrary cursors, bypassing intended ordering, leaking enumeration of private rows.
• Cursor includes only created_at (no tiebreaker id) — rows with identical timestamps cause duplicates or skips on page boundaries.
• Server returns total: N alongside cursor — total count requires a full table scan; defeats the performance benefit.

Derived From

@community/principle-pagination-first