ROW_NUMBER() no PostgreSQL: paginação, deduplicação e top-N por grupo

Se você já escreveu uma subconsulta para buscar "a linha mais recente de cada grupo" e sentiu que era mais difícil do que deveria, o ROW_NUMBER() é a ferramenta que faltava. É a window function mais usada do PostgreSQL, e quando a ficha cai, toda uma classe de problemas de relatório e limpeza de dados vira uma única consulta legível.

O que o ROW_NUMBER() faz

O ROW_NUMBER() atribui um inteiro único e sem lacunas a cada linha do resultado, na ordem definida pelo ORDER BY dentro do OVER(). A diferença crucial em relação ao GROUP BY: uma window function não agrupa linhas. Agregar com GROUP BY transforma dez pedidos de um cliente em uma linha de resumo. A window function mantém os dez pedidos e adiciona uma coluna — aqui, um número de sequência — calculada sobre uma "janela" de linhas relacionadas. Você fica com o detalhe e com o cálculo.

Sintaxe

ROW_NUMBER() OVER (
  [PARTITION BY coluna_ou_expr]   -- opcional: reinicia a numeração por grupo
  ORDER BY coluna_ou_expr [ASC | DESC]   -- obrigatório: define a ordem
)

Duas cláusulas vivem dentro do OVER():

• ORDER BY — obrigatório para o ROW_NUMBER(). Define a ordem em que as linhas são numeradas. Sem ele, a numeração seria não-determinística.
• PARTITION BY — opcional. Divide as linhas em grupos e reinicia o contador em 1 a cada grupo. Pense nele como um "GROUP BY para a janela", mas as linhas sobrevivem.

Um primeiro exemplo

SELECT
  id,
  customer_id,
  created_at,
  ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
FROM orders;

Cada pedido ganha um número de 1 (mais recente) a N (mais antigo). Uma janela, a tabela inteira.

PARTITION BY: numerando dentro de grupos

O verdadeiro poder aparece com o PARTITION BY. O contador reinicia em 1 no começo de cada partição, então você numera as linhas de forma independente por cliente, por dia, por categoria:

SELECT
  customer_id,
  id AS order_id,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
  ) AS order_rank
FROM orders;

Agora order_rank = 1 é o pedido mais recente de cada cliente. O pedido mais novo do cliente 7 e o do cliente 42 recebem rank 1, cada um na sua partição.

Caso 1: registro mais recente por grupo (o problema "greatest-N-per-group")

"Pegar o pedido mais recente de cada cliente" é um dos pedidos de relatório mais comuns — e historicamente mais chatos. As alternativas clássicas são uma subconsulta correlacionada (lenta) ou um DISTINCT ON (específico do Postgres). O ROW_NUMBER() é a resposta portável e legível: numere as linhas dentro de cada cliente e fique com o número 1.

SELECT *
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders o
) ranked
WHERE rn = 1;

Troque WHERE rn = 1 por WHERE rn <= 3 e você tem "os 3 pedidos mais recentes por cliente" — uma consulta top-N por grupo — sem nenhuma máquina extra.

Caso 2: deduplicação

Encontrou linhas duplicadas que deveriam ser únicas — digamos, dois usuários com o mesmo e-mail? Numere as duplicatas dentro de cada grupo de e-mail e apague tudo menos a primeira:

DELETE FROM users u
USING (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY lower(email)
           ORDER BY created_at   -- mantém a mais antiga; use DESC para a mais nova
         ) AS rn
  FROM users
) d
WHERE u.id = d.id AND d.rn > 1;

O ORDER BY decide qual duplicata sobrevive. Ordenar por created_at mantém a conta original; usar DESC mantém a mais recente. Sempre rode o SELECT interno primeiro e confira as linhas marcadas com rn > 1 antes de confirmar o DELETE.

Caso 3: paginação — e por que o OFFSET dói

O ROW_NUMBER() pode dirigir paginação por página (WHERE rn BETWEEN 41 AND 60) e torna a ordenação explícita. Mas em tabelas grandes, tanto janelas com ROW_NUMBER() quanto o velho OFFSET 100000 LIMIT 20 têm o mesmo defeito: o banco precisa produzir e percorrer as primeiras 100.000 linhas só para descartá-las. A página 5.000 fica muito mais lenta que a página 1.

Para paginação profunda, prefira paginação keyset (cursor) — lembre o último valor exibido e peça a próxima fatia diretamente:

-- Em vez de OFFSET, carregue a última chave de ordenação vista:
SELECT *
FROM orders
WHERE created_at < :ultimo_created_at
ORDER BY created_at DESC
LIMIT 20;

Apoiada por um índice em created_at, toda página custa o mesmo — o banco salta direto para o cursor em vez de contar do topo.

Performance: faça a janela usar um índice

O PARTITION BY + ORDER BY de uma window function exige que as linhas cheguem ordenadas. O PostgreSQL pode satisfazer isso a partir de um índice em vez de ordenar em tempo de execução. Um índice composto que casa com a janela permite pular o Sort inteiro:

CREATE INDEX idx_orders_cust_created
  ON orders (customer_id, created_at DESC);

Agora confirme com EXPLAIN (ANALYZE, BUFFERS): você quer ver um nó WindowAgg alimentado diretamente por um Index Scan, sem um Sort acima dele. Se aparecer um Sort — especialmente um que reporte Sort Method: external merge Disk — a janela está ordenando em disco e a consulta vai ficando mais lenta conforme a tabela cresce.

Da consulta para a produção

Funções de janela como o ROW_NUMBER() são baratas no papel, mas degradam silenciosamente: a tabela cresce, o índice de apoio deixa de ser usado e o WindowAgg começa, em silêncio, a derramar a ordenação em disco. Nada dá erro — o relatório só fica mais lento semana após semana. O PG Monitoring acompanha o plano de execução de cada consulta ao longo do tempo e alerta no exato momento em que aquele Sort vai para o disco ou o plano abandona o índice, para você corrigir antes que os usuários percebam.