Paginação por offset em tabela grande: quando a página 500 derruba o banco

OFFSET não é um salto. O banco não tem como saber onde começa a linha de número 24.951 de um resultado ordenado sem percorrer as 24.950 anteriores, porque a posição de uma linha no resultado depende do filtro, da ordenação e do estado da tabela naquele instante. Mesmo com o índice perfeito para a consulta, o executor lê as entradas do índice em ordem, busca cada linha correspondente na tabela, conta e descarta até atingir o deslocamento pedido, e só então começa a devolver o que o cliente queria. O trabalho da página N é proporcional a N vezes o tamanho da página.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, criado_em, status, total
FROM pedidos
WHERE loja_id = 42
ORDER BY criado_em DESC, id DESC
LIMIT 50 OFFSET 24950;

 Limit  (cost=21418.52..21461.44 rows=50 width=32)
        (actual time=1874.221..1874.402 rows=50 loops=1)
   Buffers: shared hit=3121 read=21877
   ->  Index Scan using pedidos_loja_criado_id_idx on pedidos
         (cost=0.56..412936.11 rows=481120 width=32)
         (actual time=0.041..1872.930 rows=25000 loops=1)
         Index Cond: (loja_id = 42)
         Buffers: shared hit=3121 read=21877
 Planning Time: 0.162 ms
 Execution Time: 1874.455 ms

O plano mostra o problema em duas linhas. O nó de índice produziu vinte e cinco mil linhas para que o Limit devolvesse cinquenta, e para isso tocou quase vinte e cinco mil páginas de dados, a maior parte lida do disco porque linhas antigas de uma loja raramente estão em cache. O índice está sendo usado, o plano é o melhor possível para essa forma de consulta, e mesmo assim a execução levou quase dois segundos. Nenhum ajuste de índice resolve isso, porque o custo não vem de um plano ruim: vem de pedir ao banco uma posição que ele só consegue encontrar contando.

É por isso que o problema demora a aparecer. Pessoas navegando pela interface quase nunca passam da página cinco, e em desenvolvimento a tabela tem poucos milhares de linhas. O custo só se manifesta quando alguém automatiza a navegação: um script de exportação, uma integração que sincroniza o histórico inteiro, um robô de busca seguindo o link de próxima página, ou um usuário que descobriu que pode trocar o número na URL. E como o custo total de percorrer N páginas é quadrático, uma exportação completa que parecia inofensiva lê, no total, dezenas de bilhões de linhas.

A lentidão é o sintoma que derruba o banco, mas não é o único defeito da paginação por deslocamento. Existem outros três, e eles afetam a correção dos dados e a saúde do banco mesmo quando ninguém chega à página 500.

Os dois primeiros itens são os mais traiçoeiros, porque não geram erro nem alerta. Uma sincronização que pagina por deslocamento sobre uma tabela que recebe escrita o tempo todo vai, com certeza estatística, perder e duplicar registros, e o time de dados vai descobrir isso meses depois comparando totais que não batem. O quarto item explica por que o incidente do início afetou o checkout, que não tinha nada a ver com a listagem: a exportação expulsou do cache as páginas que o checkout usava.

A alternativa é parar de pedir uma posição e passar a pedir uma continuação. Em vez de "pule 24.950 linhas", a consulta diz "me dê as próximas 50 depois deste item", usando os valores da ordenação do último item da página anterior como ponto de partida. Com um índice na mesma ordem, o banco desce direto na árvore até esse ponto e lê só as cinquenta linhas seguintes. O custo da página 4.000 passa a ser igual ao da página 1.

-- Indice na mesma ordem da listagem: filtro de igualdade primeiro,
-- depois a ordenacao, depois o desempate unico.
CREATE INDEX CONCURRENTLY pedidos_loja_criado_id_idx
  ON pedidos (loja_id, criado_em DESC, id DESC);

-- Primeira pagina: sem cursor.
SELECT id, criado_em::text AS criado_em_cursor, status, total
FROM pedidos
WHERE loja_id = $1
ORDER BY criado_em DESC, id DESC
LIMIT 51;  -- um a mais que o tamanho da pagina, para saber se existe proxima

-- Paginas seguintes: continua depois do ultimo item devolvido.
SELECT id, criado_em::text AS criado_em_cursor, status, total
FROM pedidos
WHERE loja_id = $1
  AND (criado_em, id) < ($3::timestamptz, $4::bigint)
ORDER BY criado_em DESC, id DESC
LIMIT $2;

Três detalhes fazem essa consulta funcionar, e errar qualquer um deles produz resultados sutilmente errados. O primeiro é o desempate: criado_em sozinho não é único, e dois pedidos criados no mesmo microssegundo, comuns em importações em lote, fariam o cursor pular ou repetir itens. Acrescentar o id à ordenação e à comparação torna a ordem total e determinística. O segundo é a comparação de linha, (criado_em, id) < (valor, valor), que significa "criado_em menor, ou igual e id menor". Escrever isso à mão com OR costuma impedir o uso do índice, enquanto a forma de linha o PostgreSQL usa diretamente como condição de índice. O terceiro é o índice com as colunas na mesma ordem e direção da cláusula ORDER BY, com a coluna de igualdade do filtro na frente.

 Limit  (cost=0.56..44.21 rows=50 width=32)
        (actual time=0.052..0.198 rows=50 loops=1)
   Buffers: shared hit=54
   ->  Index Scan using pedidos_loja_criado_id_idx on pedidos
         (actual time=0.050..0.187 rows=50 loops=1)
         Index Cond: ((loja_id = 42) AND
           (ROW(criado_em, id) < ROW('2026-03-14 18:22:07.481213-03'::timestamptz, 88123410)))
         Buffers: shared hit=54
 Execution Time: 0.231 ms

A linha Index Cond é a confirmação que importa: a comparação de linha aparece dentro da condição do índice, e não como filtro aplicado depois. Se ela aparecer em uma linha Filter, o banco está lendo o índice desde o início e descartando, e o ganho desaparece. Isso acontece quando a direção de alguma coluna do índice não bate com a ordenação, quando a consulta ordena por uma expressão diferente da indexada, ou quando existe um filtro adicional de igualdade que não está no começo do índice. Cada combinação de filtro e ordenação que a interface oferece precisa de um índice compatível, e esse é o custo real da técnica.

Expor os valores da ordenação diretamente na URL, como ?depois_de_data=...&depois_de_id=..., funciona, mas amarra o contrato da API à implementação. Se a ordenação mudar, se um novo desempate for necessário ou se a listagem passar a aceitar outra ordem, todos os clientes quebram. O padrão mais durável é um cursor opaco: uma string que o servidor produz, o cliente devolve sem interpretar, e que carrega uma versão para permitir mudanças futuras.

// Cursor opaco e assinado para paginacao por chave (Express + node-postgres).
import { createHmac, timingSafeEqual } from 'node:crypto';

const SEGREDO_CURSOR = process.env.SEGREDO_CURSOR;
const LIMITE_PADRAO = 50;
const LIMITE_MAXIMO = 200;

const assinar = (dados) =>
  createHmac('sha256', SEGREDO_CURSOR).update(dados).digest('base64url').slice(0, 22);

export function codificarCursor({ criadoEm, id }) {
  const dados = Buffer.from(JSON.stringify({ v: 1, c: criadoEm, i: id })).toString('base64url');
  return dados + '.' + assinar(dados);
}

export function decodificarCursor(cursor) {
  const [dados, assinatura] = String(cursor).split('.');
  if (!dados || !assinatura) return null;
  const esperada = Buffer.from(assinar(dados));
  const recebida = Buffer.from(assinatura);
  if (esperada.length !== recebida.length || !timingSafeEqual(esperada, recebida)) return null;
  const { v, c, i } = JSON.parse(Buffer.from(dados, 'base64url').toString('utf8'));
  return v === 1 ? { criadoEm: c, id: i } : null;
}

export async function listarPedidos(req, res) {
  const limite = Math.min(Number(req.query.limite) || LIMITE_PADRAO, LIMITE_MAXIMO);
  const cursor = req.query.cursor ? decodificarCursor(req.query.cursor) : null;
  if (req.query.cursor && !cursor) {
    return res.status(400).json({ erro: 'cursor_invalido' });
  }

  const parametros = [req.lojaId, limite + 1];
  let continuacao = '';
  if (cursor) {
    parametros.push(cursor.criadoEm, cursor.id);
    continuacao = 'AND (criado_em, id) < ($3::timestamptz, $4::bigint)';
  }

  const { rows } = await db.query(
    'SELECT id, criado_em::text AS criado_em_cursor, status, total ' +
      'FROM pedidos WHERE loja_id = $1 ' + continuacao +
      ' ORDER BY criado_em DESC, id DESC LIMIT $2',
    parametros,
  );

  const temProxima = rows.length > limite;
  const itens = temProxima ? rows.slice(0, limite) : rows;
  const ultimo = itens[itens.length - 1];

  return res.json({
    itens: itens.map(({ criado_em_cursor, ...pedido }) => ({ ...pedido, criado_em: criado_em_cursor })),
    proximo_cursor: temProxima
      ? codificarCursor({ criadoEm: ultimo.criado_em_cursor, id: ultimo.id })
      : null,
  });
}

O detalhe que mais produz defeito em produção está na coluna criado_em_cursor. O timestamptz do PostgreSQL tem precisão de microssegundos, e o Date do JavaScript só guarda milissegundos. Se o cursor for montado a partir do valor convertido para Date, o ponto de continuação perde os três últimos dígitos, e itens criados dentro do mesmo milissegundo que o último da página são pulados ou repetidos, de forma intermitente e quase impossível de reproduzir. Selecionar a coluna como texto preserva a precisão completa, e o cast de volta para timestamptz na consulta seguinte reconstrói exatamente o mesmo valor. Pela mesma razão o id vai como texto: o node-postgres devolve bigint como string para não perder precisão acima de dois elevado a cinquenta e três.

• A assinatura impede que o cliente fabrique cursores para pular direto para qualquer ponto da tabela, o que reabriria parte do problema e permitiria sondar dados por valor de ordenação.
• O campo de versão permite trocar o formato do cursor no futuro aceitando o antigo por um período, sem quebrar clientes no meio de uma paginação.
• Buscar um item a mais que o limite responde se existe próxima página sem uma segunda consulta e sem COUNT.
• O limite máximo por página é parte da proteção: sem ele, um cliente pede dez mil itens por página e reproduz o custo por outro caminho.

A consulta nova é a parte fácil. A parte difícil é que a API já tem clientes que mandam ?pagina=4000, e a interface tem um paginador com números e um botão de última página. Nenhum dos dois pode ser trocado de um dia para o outro, e a paginação por chave não oferece o que eles pedem: não existe forma barata de pular para a página 4.000 sem percorrer as anteriores, porque essa é justamente a operação cara.

1. Instrumente a rota antiga com a profundidade pedida e o cliente que pediu, para descobrir quem realmente navega além das primeiras páginas. Em quase todos os casos são dois ou três clientes automatizados, e não pessoas.
2. Publique o parâmetro cursor ao lado de pagina, devolvendo proximo_cursor em todas as respostas, inclusive nas que foram pedidas por número de página, para que um cliente possa começar por número e continuar por cursor.
3. Imponha um teto de profundidade para o parâmetro antigo, por exemplo dez mil itens, e acima dele responda 400 com uma mensagem que aponta o parâmetro cursor e a documentação. Esse teto sozinho elimina o incidente, mesmo antes de qualquer cliente migrar.
4. Ofereça uma exportação assíncrona para quem precisa do histórico inteiro: o cliente pede, um job percorre a tabela por chave em ritmo controlado, gera o arquivo e avisa quando está pronto. Scripts de exportação por paginação são o maior consumidor de páginas profundas, e esse é o caminho certo para eles.
5. Na interface, troque o paginador numérico por "carregar mais" ou por anterior e próxima, e substitua o salto para a página N por filtros que as pessoas realmente usam, como intervalo de datas, status e busca.
6. Remova o parâmetro antigo quando a métrica mostrar que ninguém mais passa do teto, com data comunicada aos clientes que ainda o usam.

O terceiro passo merece ênfase porque é o que resolve o risco imediato com uma mudança de poucas linhas. Mecanismos de busca como o Elasticsearch fazem exatamente isso por padrão, recusando deslocamentos acima de dez mil resultados, e a razão é a mesma. O teto transforma um custo ilimitado em um custo conhecido, e o erro com instrução de como migrar faz os clientes automatizados aparecerem sozinhos, em vez de continuarem invisíveis até o próximo incidente.

Para a navegação para trás, a mesma técnica funciona com a comparação invertida: a consulta usa maior que em vez de menor que, ordena em ordem crescente, e o servidor inverte o resultado antes de devolver. O cursor carrega a direção junto com os valores, e a resposta passa a ter anterior_cursor e proximo_cursor. Não é preciso guardar estado no servidor para isso.

OFFSET não é proibido. Ele é a ferramenta errada quando a profundidade não tem limite e a tabela é grande, e continua sendo razoável quando alguma das duas coisas não é verdade. Uma tela administrativa sobre uma tabela de configuração com três mil linhas, uma listagem em que o filtro sempre reduz o resultado a algumas centenas de itens, ou um relatório interno que ninguém automatiza podem usar número de página sem risco, e a simplicidade de implementar e de pular para uma página específica tem valor real nesses casos.

A contagem total é o outro custo que costuma sobreviver à migração. "Mostrando 1 a 50 de 481.120" exige COUNT(*) sobre o filtro inteiro, que no PostgreSQL percorre todas as linhas visíveis, e isso acontece em toda chamada. Na maioria das interfaces, o número exato não é usado para nada além de exibição, e pode ser substituído por uma contagem com teto, que para de contar ao passar de um limite, ou por uma estimativa do planejador quando a ordem de grandeza basta.

-- Contagem com teto: para de contar ao passar de 10.000 linhas.
-- A interface mostra "10.000+" quando o resultado atinge o teto.
SELECT count(*) AS total
FROM (
  SELECT 1
  FROM pedidos
  WHERE loja_id = $1
  LIMIT 10001
) AS amostra;

-- Estimativa da tabela inteira, sem percorrer nada, a partir das
-- estatisticas mantidas pelo ANALYZE e pelo autovacuum.
SELECT reltuples::bigint AS estimativa
FROM pg_class
WHERE oid = 'pedidos'::regclass;

A contagem com teto custa no máximo a leitura de dez mil entradas do índice, independentemente do tamanho da loja, e resolve o caso de uso real da interface, que é dizer ao usuário se o resultado é pequeno ou grande. Quando o produto exige o número exato, por exemplo em um relatório financeiro, o lugar certo para ele é uma contagem mantida por gatilho ou por agregação periódica, e não um COUNT a cada abertura da tela.