Voltar para o Blog

Paginação keyset no PostgreSQL: fim do OFFSET lento

Como implementar paginação por cursor (keyset) com índice composto no PostgreSQL e no Aurora PostgreSQL, e comprovar o ganho com planos de execução.

27 de Outubro, 2026Felipe Fadoni
Paginação keyset no PostgreSQL: fim do OFFSET lento

A paginação keyset no PostgreSQL resolve um problema clássico: listagens que ficam cada vez mais lentas conforme o usuário avança de página. A resposta curta é trocar LIMIT 20 OFFSET 100000 por uma condição do tipo WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20, apoiada por um índice composto nas mesmas colunas e na mesma ordem. Assim, o banco salta direto para o ponto certo do índice em vez de ler e descartar milhares de linhas.

Neste tutorial vamos montar um ambiente reproduzível com dados sintéticos, comparar os planos de execução das duas abordagens e implementar a técnica com segurança. Tudo o que for mostrado vale para o PostgreSQL comunitário e também para o Amazon Aurora PostgreSQL, que é compatível com o PostgreSQL no nível de SQL. Não confunda com o Aurora MySQL, que é outro motor e tem sintaxe e otimizador diferentes; nada aqui se aplica diretamente a ele.

O problema: por que o OFFSET fica lento

O OFFSET n não é um "pulo" mágico. Para devolver as linhas da posição 100.001 em diante, o executor precisa produzir as 100.000 linhas anteriores na ordem correta e descartá-las. Mesmo com um índice que atenda ao ORDER BY, o custo cresce de forma aproximadamente linear com o deslocamento, porque cada linha pulada é visitada no índice e, muitas vezes, também na tabela.

Esse custo aparece de forma traiçoeira em produção. As primeiras páginas respondem rápido nos testes, mas crawlers, exportações e usuários que filtram pouco acabam pedindo páginas profundas. Em um cluster Aurora, isso se traduz em mais leituras de páginas, mais CPU na instância de escrita ou leitura e, dependendo da configuração de armazenamento, mais custo de I/O.

Há ainda um problema de consistência. Se novas linhas forem inseridas enquanto o usuário navega, o OFFSET desloca a janela e o usuário vê itens repetidos ou deixa de ver alguns. A paginação keyset ancora a próxima página no último item visto, o que elimina esse efeito para inserções que ocorrem antes do cursor.

Resultado esperado

Ao final, você terá uma tabela orders com um milhão de linhas sintéticas, um índice composto adequado, uma consulta de primeira página e uma consulta de páginas seguintes baseada em cursor. Também terá um roteiro para comparar planos com EXPLAIN (ANALYZE, BUFFERS) e interpretar os números no seu próprio ambiente, sem depender de benchmarks de terceiros.

Pré-requisitos e versões

Você precisa de um PostgreSQL suportado e do cliente psql. Na data de consulta, a política oficial lista as versões 14 a 18 como suportadas, sendo a 18 a mais nova major; a 14 tem fim de suporte previsto para novembro de 2026, então prefira 16, 17 ou 18 para projetos novos. Em Aurora PostgreSQL, verifique no console ou na documentação da AWS quais versões estão disponíveis na sua região antes de criar o cluster.

  • PostgreSQL 16, 17 ou 18 (local, Docker ou Aurora PostgreSQL de testes)
  • psql compatível
  • Permissão para criar tabela e índice em um banco descartável

Use sempre um banco de testes. Os scripts criam e populam tabelas, e o EXPLAIN ANALYZE executa de fato a instrução analisada, o que tem consequências se a instrução modificar dados.

Arquitetura e fluxo de dados

O fluxo é simples. A API recebe opcionalmente um cursor opaco, que codifica o par (created_at, id) do último item da página anterior. Sem cursor, ela executa a consulta da primeira página. Com cursor, ela decodifica, valida e passa os valores como parâmetros para a consulta keyset. A resposta devolve os itens e o novo cursor, gerado a partir do último item retornado.

A escolha do par (created_at, id) é proposital. Ordenar apenas por created_at não é determinístico, porque várias linhas podem ter o mesmo instante. Acrescentar a chave primária como desempate torna a ordenação total e única, condição necessária para que o cursor aponte para uma posição sem ambiguidade.

Estrutura de diretórios

keyset-demo/
├── .env.example
├── sql/
│   ├── 01_schema.sql
│   ├── 02_seed.sql
│   ├── 03_offset.sql
│   └── 04_keyset.sql

O arquivo .env.example serve apenas para documentar as variáveis de conexão usadas pelo psql. Nunca versione o .env real.

Arquivo keyset-demo/.env.example:

PGHOST=localhost
PGPORT=5432
PGDATABASE=keyset_demo
PGUSER=SUBSTITUA_PELO_USUARIO
PGPASSWORD=SUBSTITUA_PELA_SENHA

Preparação do ambiente

Se for usar Docker localmente, a imagem oficial postgres permite subir uma instância descartável. Ajuste a tag para a major desejada e use uma senha apenas local.

docker run --name keyset-pg -e POSTGRES_PASSWORD=troque-esta-senha-local \
  -e POSTGRES_DB=keyset_demo -p 5432:5432 -d postgres:18
cp .env.example .env   # edite os valores
set -a && . ./.env && set +a
psql -c "SELECT version();"

O último comando deve imprimir a versão do servidor. Se falhar, confira se o contêiner está ativo com docker ps e se a porta 5432 não está ocupada por outra instância.

Implementação passo a passo

Criando o esquema

A tabela representa pedidos de clientes. O índice composto segue exatamente a ordem da listagem: mais recentes primeiro, com desempate pelo id.

Arquivo sql/01_schema.sql:

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer     NOT NULL,
  status      text        NOT NULL,
  amount      numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL
);

CREATE INDEX orders_created_at_id_idx
  ON orders (created_at DESC, id DESC);

Um B-tree pode ser percorrido nos dois sentidos, então (created_at, id) em ordem ascendente também atenderia ORDER BY created_at DESC, id DESC com uma varredura reversa. Declarar DESC explicita a intenção e evita confusão; o que não pode acontecer é misturar direções, como created_at DESC, id ASC, sem um índice com essa mesma combinação.

Gerando dados sintéticos reproduzíveis

Usamos generate_series e setseed para que qualquer pessoa obtenha a mesma distribuição. Os timestamps são deliberadamente repetidos em alguns casos para exercitar o desempate pelo id.

Arquivo sql/02_seed.sql:

SELECT setseed(0.42);

INSERT INTO orders (customer_id, status, amount, created_at)
SELECT
  (random() * 50000)::int + 1,
  (ARRAY['pending','paid','shipped','canceled'])[1 + floor(random() * 4)::int],
  round((random() * 1000)::numeric, 2),
  timestamptz '2026-01-01 00:00:00+00'
    + make_interval(secs => floor(g / 3.0))
FROM generate_series(1, 1000000) AS g;

VACUUM (ANALYZE) orders;

O VACUUM (ANALYZE) atualiza estatísticas e o mapa de visibilidade. Sem estatísticas, o planejador pode escolher planos ruins; sem mapa de visibilidade atualizado, varreduras index-only são menos eficazes.

A consulta com OFFSET

Arquivo sql/03_offset.sql:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 500000;

Espera-se um plano com Limit sobre Index Scan using orders_created_at_id_idx, em que o nó do índice reporta cerca de 500.020 linhas produzidas para entregar apenas 20. Observe a linha Buffers: shared hit=... read=...: ela mostra quantas páginas foram tocadas e é o melhor indicador do desperdício.

A consulta keyset

A primeira página não tem cursor. As seguintes usam a comparação de row constructors, que o PostgreSQL avalia em ordem lexicográfica e consegue mapear para uma condição de índice composto.

Arquivo sql/04_keyset.sql:

-- Primeira página
SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Página seguinte: use os valores do ÚLTIMO item da página anterior.
PREPARE next_page(timestamptz, bigint) AS
  SELECT id, customer_id, status, amount, created_at
  FROM orders
  WHERE (created_at, id) < ($1, $2)
  ORDER BY created_at DESC, id DESC
  LIMIT 20;

-- Exemplo: substitua pelos valores reais obtidos na página anterior.
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE next_page('2026-01-02 22:17:13+00', 500000);

A expressão (created_at, id) < ($1, $2) significa "created_at menor, ou created_at igual e id menor". Escrever isso com OR manual também é correto logicamente, mas a forma com row constructor é mais legível e é reconhecida pelo planejador como uma condição de índice sobre as duas colunas.

O PREPARE aqui demonstra o uso de parâmetros, o mesmo princípio que sua aplicação deve seguir com o driver. Os valores do cursor nunca devem ser concatenados em SQL; eles chegam do cliente e precisam ser tratados como dados não confiáveis.

Execução

psql -f sql/01_schema.sql
psql -f sql/02_seed.sql
psql -f sql/03_offset.sql
psql -f sql/04_keyset.sql

O seed de um milhão de linhas pode levar de segundos a alguns minutos, dependendo do hardware. Rode cada EXPLAIN duas ou três vezes: a primeira execução tende a ler mais do disco e as seguintes encontram páginas no cache, o que muda a proporção entre read e hit.

Testes e validação

O critério principal não é o tempo isolado, mas a forma do plano. Na consulta keyset, espera-se um Index Cond: (ROW(created_at, id) < ROW(...)) e um nó de índice com cerca de 20 linhas e poucos buffers, independentemente de quão profunda é a página. Na consulta com OFFSET, os buffers crescem conforme o deslocamento aumenta. Teste offsets de 1.000, 100.000 e 500.000 para observar essa tendência no seu ambiente.

Valide também a correção. Uma forma simples é comparar a concatenação de duas páginas keyset consecutivas com o resultado de LIMIT 40 sem cursor: os 40 ids devem coincidir na mesma ordem. Isso confirma que o desempate funciona e que nenhum item é perdido ou duplicado na fronteira entre páginas.

WITH p1 AS (
  SELECT id, created_at FROM orders
  ORDER BY created_at DESC, id DESC LIMIT 20
), last AS (
  SELECT created_at, id FROM p1 ORDER BY created_at, id LIMIT 1
), p2 AS (
  SELECT o.id, o.created_at FROM orders o, last
  WHERE (o.created_at, o.id) < (last.created_at, last.id)
  ORDER BY o.created_at DESC, o.id DESC LIMIT 20
)
SELECT (SELECT array_agg(id ORDER BY created_at DESC, id DESC)
        FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2) u)
     = (SELECT array_agg(id ORDER BY created_at DESC, id DESC)
        FROM (SELECT id, created_at FROM orders
              ORDER BY created_at DESC, id DESC LIMIT 40) t) AS paginas_ok;

O resultado esperado é paginas_ok = t. Se vier f, revise se a direção do ORDER BY e a do operador de comparação estão coerentes.

Erros comuns e soluções

O erro mais frequente é ordenar por uma coluna não única sem desempate. O sintoma é a perda ou repetição de itens quando vários registros compartilham o mesmo created_at. A solução é sempre incluir uma coluna única, normalmente a chave primária, no final da ordenação, no cursor e no índice.

Outro erro comum é misturar direções. Com ORDER BY created_at DESC, id ASC, a comparação de row constructor deixa de expressar a ordem desejada, porque ela é sempre lexicográfica em um único sentido. Nesses casos, mantenha as direções iguais ou escreva a condição com OR explícito e crie um índice com as direções correspondentes, ciente de que o plano pode ficar menos eficiente.

Também é comum adicionar filtros, como status = 'paid', e esquecer o índice. Se a listagem sempre filtra por status, o índice ideal passa a ser (status, created_at DESC, id DESC), com a coluna de igualdade na frente. Sem isso, o banco pode percorrer o índice de data descartando linhas de outros status, reintroduzindo parte do desperdício que queríamos eliminar.

Por fim, cuidado com fusos horários no cursor. Serialize created_at com precisão completa e offset explícito (ISO 8601 com microssegundos). Arredondar para milissegundos no JavaScript, por exemplo, pode fazer o cursor apontar para um instante ligeiramente diferente e pular registros.

Segurança

Trate o cursor como entrada externa. Codifique-o de forma opaca, por exemplo em base64url de um JSON com os dois campos, e valide tipos e limites ao decodificar. Se o cursor não puder ser adulterado sem consequências, assine-o com HMAC no backend; isso impede que clientes fabriquem posições arbitrárias, embora em listagens públicas a assinatura raramente seja indispensável.

O EXPLAIN ANALYZE merece atenção especial. Ele executa a instrução de verdade; em SELECT isso apenas consome recursos, mas em INSERT, UPDATE ou DELETE os dados são alterados. Para analisar instruções de escrita, envolva-as em BEGIN; ... ROLLBACK; e, ainda assim, evite fazê-lo em produção sem janela e autorização.

Performance, custos e operação

No Aurora PostgreSQL, menos páginas lidas significa menos pressão sobre o cache da instância e, na configuração de armazenamento padrão, menos operações de I/O cobradas. Não é possível prometer uma economia específica, porque ela depende do padrão de acesso, do tamanho do cache e do modelo de cobrança escolhido para o cluster; meça com o Performance Insights ou com pg_stat_statements antes e depois da mudança.

O índice composto tem custo. Cada inserção atualiza mais uma estrutura, e o índice ocupa espaço em disco e em memória. Em tabelas de escrita intensa, avalie se a listagem justifica o índice e evite criar variações redundantes para cada combinação de filtro. Em produção, crie índices com CREATE INDEX CONCURRENTLY para não bloquear escritas, sabendo que a operação é mais lenta e não pode rodar dentro de uma transação.

Para acompanhar o efeito, habilite pg_stat_statements e compare mean_exec_time e shared_blks_hit/shared_blks_read das consultas de listagem. Essa visão agregada é mais confiável que execuções isoladas, porque reflete a mistura real de páginas rasas e profundas pedidas pelos usuários.

Limitações e cuidados para produção

A paginação keyset não permite saltar diretamente para "a página 37". Ela é ideal para rolagem infinita, botões de "próxima" e "anterior" e exportações, mas exige repensar interfaces que exibem números de página. Se o produto realmente precisa de saltos arbitrários, uma alternativa é limitar a profundidade máxima do OFFSET e incentivar filtros mais específicos.

Contar o total de resultados continua caro. Um count(*) em uma tabela grande percorre muitas linhas, com ou sem keyset. Considere exibir "mais resultados disponíveis" buscando LIMIT 21 e verificando se veio o vigésimo primeiro item, ou use estimativas das estatísticas quando a precisão não for crítica.

Por último, lembre que este é um exemplo didático. Em produção, você ainda precisa de testes automatizados na API, limite máximo de itens por página, observabilidade das consultas e revisão do índice à medida que novos filtros aparecem. A técnica é estável e madura, mas o desenho correto depende da ordenação e dos filtros reais da sua aplicação.

Conclusão

Trocar OFFSET por paginação keyset é uma das otimizações com melhor relação entre esforço e resultado no PostgreSQL e no Aurora PostgreSQL. Com uma ordenação total, um índice composto alinhado e um cursor tratado como dado não confiável, o custo de cada página deixa de depender da profundidade da navegação. Valide no seu ambiente com EXPLAIN (ANALYZE, BUFFERS) e métricas agregadas, e só então leve a mudança para produção.

Referências oficiais (consultadas em 2026-10-27)

Precisa implementar isso na sua empresa?

Quer aplicar essas práticas de engenharia, dados ou inteligência artificial no seu produto? Solicite um diagnóstico técnico sem compromisso.

Solicitar orçamento