English

Cambia la paginación por offset por la de keyset

En una tabla grande y cambiante, la paginación por offset es un bug esperando a ocurrir. LIMIT 20 OFFSET 100 parece inofensivo en una revisión de código. No lo es. En una tabla grande y mutable hace dos cosas que nadie quiere: se vuelve más lenta con cada página y salta y repite filas en silencio mientras la gente sigue insertando y borrando debajo de ti. La paginación por keyset arregla ambas — y es el patrón que los revisores esperan una vez que tu tabla deja de ser pequeña.

Por qué el offset se desfasa

OFFSET N le dice a la base de datos: trae las filas en orden y luego descarta las primeras N. De esa sola frase salen dos consecuencias.

• Escanea lo que descarta. Para responder OFFSET 100000, el motor todavía tiene que leer y ordenar 100.000 filas antes de poder devolver las siguientes 20. El trabajo crece linealmente con el offset, así que la página 5.000 es mucho más lenta que la página 1 para el mismo tamaño de página.
• Se desfasa con las escrituras. El offset cuenta posiciones, no filas. Inserta una fila que ordena antes de tu página actual entre dos peticiones y cada fila posterior se corre una posición hacia abajo — así que la primera fila de tu siguiente página es una que ya mostraste. Borra una fila y pasa lo contrario: una fila se desliza hacia arriba cruzando el límite y nunca aparece. En una tabla activa, los usuarios ven duplicados y huecos que ningún reintento va a arreglar.

Avanza por clave, no por posición

La paginación por keyset (también llamada seek o cursor) nunca cuenta posiciones. Recuerda la clave de orden de la última fila de la página y pide las filas posteriores a esa clave:

-- Page 1
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's (created_at, id) back in
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

La cláusula WHERE se ancla a un valor, no a un conteo, así que las inserciones y borrados en otras partes de la tabla no pueden mover tu límite. Y con un índice sobre (created_at, id), la base de datos salta directo al ancla en lugar de recorrer todo lo anterior — la página 5.000 cuesta lo mismo que la página 1.

La única regla que lo hace funcionar

El keyset necesita un orden de clasificación explícito, único y estable. Nunca dependas del orden implícito — una consulta sin ORDER BY devuelve las filas en el orden físico que el motor prefiera, y ese orden puede cambiar debajo de ti.

Toda la garantía descansa en que el cursor apunte a exactamente una fila. Cualquier columna de orden no única necesita que se le agregue la clave primaria para que la tupla (sort_key, id) sea única.

Notas para usuarios avanzados

• Comparación de tuplas, no ANDs encadenados. WHERE (created_at, id) < (:c, :id) es la forma correcta y amigable con el índice. Expandirla a mano como created_at < :c OR (created_at = :c AND id < :id) es equivalente pero fácil de equivocar — la mayoría de las bases de datos modernas soportan la sintaxis de tuplas directamente.
• El tradeoff es real. El keyset no puede saltar a un número de página arbitrario — no hay "ir a la página 500", solo siguiente/anterior. Encaja con scroll infinito y APIs, menos con un paginador numerado. Si de verdad necesitas números de página en una tabla enorme, esa es una decisión de producto, no un valor por defecto.
• El índice debe coincidir con el orden. Un índice compuesto con el orden exacto (created_at, id) es lo que convierte el seek en un solo descenso por el índice. Sin él conservas la corrección pero pierdes la velocidad.
• Codifica el cursor. Envía la última clave como un token opaco (base64 de la tupla) en lugar de los valores crudos de las columnas, para que los clientes lo traten como un identificador y puedas cambiar los internos más adelante.

Recursos

• No Offset — Use The Index, Luke — por qué OFFSET es lento y se desfasa, con el SQL del método seek.
• Keyset Cursors, Not Offsets, for Postgres Pagination — Sequin — el desempate y la comparación de tuplas en Postgres.
• Offset and Keyset Pagination with Spring Data JPA — Thorben Janssen — el mismo patrón aplicado en un ORM.