Índices parciais no PostgreSQL: indexando apenas as linhas quentes

A maioria dos índices cobre a tabela inteira: cada linha, cada valor. Mas as consultas raramente se distribuem de forma uniforme pelos seus dados. Você acessa o tempo todo pedidos pending, usuários ativos, tarefas abertas, enquanto uma cauda histórica de milhões de linhas finalizadas fica ali parada como peso morto. Um índice parcial indexa apenas o subconjunto com que você de fato se importa. O ganho: um índice menor, que se atualiza mais rápido e tem muito mais chances de permanecer em cache.

O que é um índice parcial

Um índice parcial é um índice comum com um predicado WHERE. Só as linhas que satisfazem a condição entram na árvore.

-- Full index: every row of orders
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index: only the "hot" orders
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Se a tabela guarda 50M de pedidos mas apenas alguns milhares estão em pending, o segundo índice é ordens de magnitude menor. Inserir um pedido concluído nunca o toca: a linha não passa no predicado. Isso economiza tanto espaço em disco quanto tempo de escrita.

Para que o planejador o use, a condição da consulta precisa corresponder logicamente ao predicado do índice (ou ser um subconjunto dele):

-- Uses idx_orders_pending
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Por que é mais rápido e mais barato

As vantagens vêm de pura aritmética: o índice contém fisicamente menos entradas:

• Menor tamanho. Menos páginas em disco, mais chances de o índice inteiro caber em shared_buffers.
• Escritas mais rápidas. Os INSERT/UPDATE de linhas fora do predicado não atualizam o índice de forma alguma.
• Estatísticas mais limpas. O planejador estima a cardinalidade sobre um subconjunto estreito e erra com menos frequência.
• Manutenção mais barata. VACUUM e reconstruções operam sobre um conjunto de dados muito menor.

O caso clássico é uma fila de jobs. Os workers leem apenas as linhas não finalizadas:

CREATE INDEX idx_jobs_queue
  ON jobs (priority DESC, created_at)
  WHERE state IN ('queued', 'running');

Mesmo com centenas de milhões de jobs concluídos na tabela, o índice da fila permanece minúsculo e quente.

Pegadinha: o predicado precisa ser determinístico e imutável. Você não pode escrever WHERE created_at > now() - interval '7 days': now() muda, então o índice de hoje cobriria as linhas erradas amanhã. Use comparações contra valores estáticos: WHERE status = 'pending', WHERE deleted_at IS NULL.

UNIQUE parcial para exclusões lógicas

O uso mais poderoso é uma restrição UNIQUE parcial. Suponha que os usuários precisem ter um e-mail único, mas você nunca exclui linhas de forma física: em vez disso, você carimba deleted_at. Um UNIQUE (email) comum impediria registrar uma nova conta com o e-mail de um usuário excluído.

-- Won't work: an old deleted row blocks the email forever
CREATE UNIQUE INDEX ON users (email);

-- Uniqueness only among live rows
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

Agora você pode manter qualquer quantidade de linhas excluídas compartilhando um e-mail, enquanto entre as linhas ativas (deleted_at IS NULL) o e-mail permanece único. O mesmo truque impõe "um único padrão por usuário":

-- At most one primary address per user
CREATE UNIQUE INDEX idx_one_primary_address
  ON addresses (user_id)
  WHERE is_primary = true;

Essa é uma regra de integridade quase impossível de expressar com uma restrição UNIQUE comum sem recorrer a triggers.

Quando não ajuda, e diferenças entre motores

Um índice parcial não é uma bala de prata. Ele é inútil quando:

• as consultas percorrem todo o intervalo de uma coluna em vez de um subconjunto;
• o predicado cobre a maior parte da tabela (o benefício "parcial" evapora);
• o predicado da consulta não corresponde ao do índice: o planejador simplesmente o ignora.

Diferenças entre motores:

• PostgreSQL — suporte completo a índices parciais e UNIQUE parcial via CREATE INDEX ... WHERE. A implementação de referência.
• SQLite — suporta a mesma sintaxe CREATE INDEX ... WHERE, incluindo o UNIQUE parcial.
• MySQL/InnoDB — sem índices parciais. Soluções de contorno: uma coluna gerada mais um índice sobre ela, ou uma tabela separada de "linhas quentes". Observação: KEY (col(10)) no MySQL é um índice de prefixo (parte do valor), não um parcial.
• SQL Server — tem um equivalente chamado índice filtrado: CREATE INDEX ... WHERE.
• ClickHouse — um motor de outra natureza; em vez de índices com predicado você usa particionamento (PARTITION BY) e índices de salto de dados.

Uma receita prática: encontre uma consulta que filtra repetidamente sobre a mesma condição estreita (status, deleted_at, is_active) e leve essa condição para o WHERE do índice. Confirme com EXPLAIN (ANALYZE) que o planejador escolhe o índice e que o tamanho em disco caiu. Costuma ser a aceleração mais barata disponível.