Cursor-Based Pagination in PostgreSQL (Keyset vs OFFSET)

Offset-based pagination (LIMIT 20 OFFSET 10000) degrades linearly as offsets grow because PostgreSQL must scan and discard all preceding rows. Cursor-based (keyset) pagination uses indexed columns to seek directly to the starting point, maintaining constant-time performance regardless of dataset size. This implementation gives you cursor pagination with PostgreSQL, including cursor encoding, bidirectional navigation, and edge cases with duplicate sort keys. For background on why OFFSET degrades, see the PostgreSQL LIMIT documentation.

When to Use This

• API feeds with millions of items where deep page navigation is common
• Real-time data where rows are inserted continuously, making offset counts unstable
• You need consistent page results even when underlying data changes between requests
• GraphQL APIs using Relay-style connections that require cursor-based pagination

Solution

1. Database Schema and Index

-- migrations/001_create_posts.sql
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  title TEXT NOT NULL,
  score INTEGER NOT NULL DEFAULT 0
);

-- Composite index for cursor pagination by created_at
CREATE INDEX idx_posts_created_at_id ON posts (created_at DESC, id DESC);

-- Index for score-based pagination
CREATE INDEX idx_posts_score_id ON posts (score DESC, id DESC);

2. Cursor Encoding and Decoding

// pagination/Cursor.ts
import { Buffer } from 'buffer';

interface CursorData {
  createdAt: string;
  id: string;
}

function encodeCursor(data: CursorData): string {
  const json = JSON.stringify(data);
  return Buffer.from(json).toString('base64url');
}

function decodeCursor(cursor: string): CursorData {
  const json = Buffer.from(cursor, 'base64url').toString('utf8');
  return JSON.parse(json);
}

3. Query with Keyset Pagination

// pagination/PostRepository.ts
import { Pool } from 'pg';

interface PageResult<T> {
  data: T[];
  nextCursor: string | null;
  prevCursor: string | null;
  hasMore: boolean;
}

class PostRepository {
  constructor(private pool: Pool) {}

  async findPage(
    limit: number = 20,
    afterCursor?: string,
    beforeCursor?: string
  ): Promise<PageResult<Post>> {
    const client = await this.pool.connect();

    try {
      let query: string;
      let params: unknown[];

      if (afterCursor) {
        // Forward pagination: get rows after cursor
        const { createdAt, id } = decodeCursor(afterCursor);
        query = `
          SELECT * FROM posts
          WHERE (created_at, id) < ($1, $2)
          ORDER BY created_at DESC, id DESC
          LIMIT $3
        `;
        params = [createdAt, id, limit + 1];
      } else if (beforeCursor) {
        // Backward pagination: get rows before cursor
        const { createdAt, id } = decodeCursor(beforeCursor);
        query = `
          SELECT * FROM (
            SELECT * FROM posts
            WHERE (created_at, id) > ($1, $2)
            ORDER BY created_at ASC, id ASC
            LIMIT $3
          ) sub
          ORDER BY created_at DESC, id DESC
        `;
        params = [createdAt, id, limit + 1];
      } else {
        // First page
        query = `
          SELECT * FROM posts
          ORDER BY created_at DESC, id DESC
          LIMIT $1
        `;
        params = [limit + 1];
      }

      const result = await client.query(query, params);
      const rows = result.rows;
      const hasMore = rows.length > limit;
      const data = hasMore ? rows.slice(0, limit) : rows;

      // Generate cursors
      const nextCursor = hasMore && data.length > 0
        ? encodeCursor({ createdAt: data[data.length - 1].created_at, id: data[data.length - 1].id })
        : null;

      const prevCursor = data.length > 0
        ? encodeCursor({ createdAt: data[0].created_at, id: data[0].id })
        : null;

      return {
        data,
        nextCursor,
        prevCursor: afterCursor || (!beforeCursor && data.length > 0) ? prevCursor : null,
        hasMore,
      };
    } finally {
      client.release();
    }
  }
}

4. Express API Endpoint

// routes/posts.ts
app.get('/api/posts', async (req, res) => {
  const limit = Math.min(Number(req.query.limit) || 20, 100);
  const after = req.query.after as string | undefined;
  const before = req.query.before as string | undefined;

  const page = await postRepo.findPage(limit, after, before);

  res.json({
    data: page.data,
    pagination: {
      nextCursor: page.nextCursor,
      prevCursor: page.prevCursor,
      hasMore: page.hasMore,
    },
  });
});

5. Client-Side Navigation

// client/PaginatedFeed.ts
class PaginatedFeed {
  private nextCursor: string | null = null;
  private prevCursor: string | null = null;

  async loadNext(): Promise<Post[]> {
    const params = new URLSearchParams();
    params.set('limit', '20');
    if (this.nextCursor) params.set('after', this.nextCursor);

    const res = await fetch(`/api/posts?${params}`);
    const page = await res.json();

    this.nextCursor = page.pagination.nextCursor;
    this.prevCursor = page.pagination.prevCursor;

    return page.data;
  }

  async loadPrevious(): Promise<Post[]> {
    if (!this.prevCursor) return [];

    const params = new URLSearchParams();
    params.set('limit', '20');
    params.set('before', this.prevCursor);

    const res = await fetch(`/api/posts?${params}`);
    const page = await res.json();

    this.nextCursor = page.pagination.nextCursor;
    this.prevCursor = page.pagination.prevCursor;

    return page.data;
  }
}

How It Works

• Keyset pagination uses indexed composite keys instead of OFFSET, enabling O(log n) seeks
• Cursor encoding hides implementation details and prevents clients from manipulating query parameters
• Bidirectional cursors support both forward and backward navigation through the same dataset
• Over-fetching by 1 row determines if more pages exist without a separate COUNT query

Production Considerations

• Always create composite indexes matching the exact sort order used in pagination queries. See SQL Performance Tuning Guide for indexing strategies.
• Use timestamptz (not timestamp) to avoid timezone ambiguity in cursors
• Validate cursor structure to prevent injection via malformed base64 payloads. See Input Validation for validation patterns.

Common Mistakes

• Paginating by a non-unique column without a tiebreaker (e.g., created_at alone), causing skipped or duplicated rows
• Using large LIMIT values, which still requires major index scanning
• Not handling the case where the cursor row is deleted, which breaks forward navigation

Troubleshooting

• 5xx errors under load: check rate limits, connection pools, and downstream timeouts.
• CORS errors in the browser: confirm allowed origins, methods, and headers. Preflight requests must return the right headers before the actual request.
• Unexpected 404s: verify route definitions, path parameters, and base paths. Watch for trailing slashes and URL encoding differences.
• Authentication failures: validate token expiry, signature algorithms, and clock skew. Log rejected tokens without exposing secrets.
• Slow response times: profile the slowest percentiles.

See Also

• PostgreSQL LIMIT and OFFSET documentation — official reference on why OFFSET degrades.
• Relay Cursor Connections Specification — the canonical spec for GraphQL cursor pagination.
• Keyset pagination in PostgreSQL — Markus Winand’s deep dive on keyset vs OFFSET performance.
• pg_stat_statements — track query performance and identify slow pagination queries.
• /recipes/go-rest-api-gin — building REST APIs with Go and Gin.
• /recipes/api-documentation-openapi — documenting pagination in OpenAPI specs.
• /recipes/graphql-pagination-relay-connections — implementing Relay-style cursor pagination in GraphQL.
• /recipes/database-replication — scaling reads across replicas for high-traffic paginated APIs.