Paginación por Cursor en PostgreSQL (Keyset vs OFFSET)

La paginación basada en offset (LIMIT 20 OFFSET 10000) se degrada linealmente a medida que los offsets crecen porque PostgreSQL debe escanear y descartar todas las filas precedentes. La paginación por cursor (keyset) usa columnas indexadas para buscar directamente el punto de inicio, manteniendo rendimiento de tiempo constante independientemente del tamaño del conjunto de datos. Esta receta implementa paginación por cursor con PostgreSQL, incluyendo codificación de cursor, navegación bidireccional y casos límite con claves de ordenamiento duplicadas. Para más contexto sobre por qué OFFSET se degrada, consulta la documentación de LIMIT de PostgreSQL.

Cuándo Usar Esto

• Fuentes de API con millones de elementos donde la navegación a páginas profundas es común
• Datos en tiempo real donde las filas se insertan continuamente, haciendo los conteos de offset inestables
• Necesitas resultados de página consistentes incluso cuando los datos subyacentes cambian entre peticiones
• APIs de GraphQL que usan conexiones estilo Relay y requieren paginación por cursor

Solución

1. Esquema de Base de Datos e Índice

-- 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
);

-- Índice compuesto para paginación por cursor por created_at
CREATE INDEX idx_posts_created_at_id ON posts (created_at DESC, id DESC);

-- Índice para paginación por score
CREATE INDEX idx_posts_score_id ON posts (score DESC, id DESC);

2. Codificación y Decodificación de Cursor

// 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. Consulta con 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) {
        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) {
        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 {
        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;

      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. Endpoint de API Express

// 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. Navegación del Cliente

// 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;
  }
}

Cómo Funciona

• Paginación keyset usa claves compuestas indexadas en lugar de OFFSET, habilitando búsquedas O(log n)
• Codificación de cursor oculta detalles de implementación y previene la manipulación de parámetros de consulta por los clientes
• Cursores bidireccionales soportan navegación hacia adelante y hacia atrás a través del mismo conjunto de datos
• Sobreobtención de 1 fila determina si existen más páginas sin una consulta COUNT separada

Consideraciones de Producción

• Crea siempre índices compuestos que coincidan con el orden exacto de ordenamiento usado en las consultas de paginación. Consulta la Guía de Optimización de SQL para estrategias de indexación.
• Usa timestamptz (no timestamp) para evitar ambigüedad de zona horaria en los cursores
• Valida la estructura del cursor para prevenir inyección mediante payloads base64 malformados. Consulta Validación de Entrada para patrones de validación.

Errores Comunes

• Paginar por una columna no única sin un desempate (ej. created_at solo), causando filas saltadas o duplicadas
• Usar valores LIMIT grandes, que aún requieren un escaneo significativo del índice
• No manejar el caso donde la fila del cursor es borrada, rompiendo la navegación hacia adelante

Solución de Problemas

• Errores 5xx bajo carga: verifica límites de tasa, pools de conexión y tiempos de espera del backend.
• Errores CORS en el navegador: confirma orígenes, métodos y cabeceras permitidos. Las peticiones de pre-vuelo deben devolver las cabeceras correctas antes de la petición real.
• 404 inesperados: verifica definiciones de rutas, parámetros de ruta y rutas base. Vigila las barras finales y las diferencias de codificación de URL.
• Fallos de autenticación: valida expiración de tokens, algoritmos de firma y desviación de reloj. Registra tokens rechazados sin exponer secretos.
• Tiempos de respuesta lentos: perfila los percentiles más lentos.

Véase También

• Documentación de LIMIT y OFFSET de PostgreSQL — referencia oficial sobre por qué OFFSET se degrada.
• Especificación de Relay Cursor Connections — la especificación canónica para paginación por cursor en GraphQL.
• Paginación keyset en PostgreSQL — análisis profundo de Markus Winand sobre el rendimiento de keyset vs OFFSET.
• pg_stat_statements — seguimiento del rendimiento de consultas para identificar paginación lenta.
• /recipes/go-rest-api-gin — construcción de APIs REST con Go y Gin.
• /recipes/api-documentation-openapi — documentación de paginación en especificaciones OpenAPI.
• /recipes/graphql-pagination-relay-connections — implementación de paginación por cursor estilo Relay en GraphQL.
• /recipes/database-replication — escalado de lecturas entre réplicas para APIs paginadas de alto tráfico.