Índices parciais no Postgres: quando menos índice é mais

A tabela orders guarda oito anos de histórico. Noventa e oito por cento das linhas têm status = 'completed'. A query do dashboard filtra status = 'pending' e ordena por created_at. Um índice completo em (status, created_at) funciona — e cresce com cada pedido completed para sempre. Vacuum demora mais. Inserts no checkout pagam manutenção de índice em linhas que a query nunca lerá. Índices parciais no Postgres resolvem isso indexando só a fatia que a aplicação consulta de fato: mesmo access path do planner, fração do tamanho, fração da amplificação de escrita.

Um índice parcial é um btree padrão (ou outro tipo de índice) com cláusula WHERE anexada na criação. Só linhas que satisfazem o predicado aparecem no índice. O planner usa o índice parcial só quando condições da query implicam o mesmo predicado — correspondência que deve ser deliberada, não acidental. Índices parciais complementam Postgres composite index patterns; não substituem disciplina de ordem de colunas. Respondem outra pergunta: quais linhas merecem ser indexadas.

Índices parciais encolhem o working set que o planner busca

Índices completos em colunas status de baixa cardinalidade costumam degenerar em estruturas largas onde a maioria das entradas aponta para linhas que a query quente nunca toca. Índices parciais trocam generalidade por precisão.

-- Full index: grows with every order
CREATE INDEX orders_status_created_idx
  ON orders (status, created_at);
 
-- Partial index: only pending rows
CREATE INDEX orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

O índice parcial armazena só pedidos pending. Linhas completed e cancelled nunca entram na estrutura. Inserir pedido completed não toca orders_pending_created_idx. Promover linha de pending a completed a remove do índice parcial — ainda é escrita, mas limitada ao pequeno conjunto pending.

Redução de tamanho é proporcional à selectividade. Se um por cento das linhas bate com o predicado, esperar roughly duas ordens de magnitude menos páginas de índice — menos evicções de buffer cache, index-only scans mais rápidos quando visibility map coopera, ciclos de autovacuum mais curtos no próprio índice.

Índice parcial com predicado que a aplicação esquece de repetir é índice invisível — presente em \di, ausente de EXPLAIN.

Design de predicado deve espelhar formas reais de query

O predicado do índice parcial deve estar implied pela cláusula WHERE da query para o planner escolhê-lo. Postgres não adivinha.

Works — query repeats the predicate or a stricter subset:

-- Index
CREATE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;
 
-- Query: predicate matches
SELECT id FROM users
WHERE deleted_at IS NULL AND email = $1;

Fails — query omits the indexed predicate:

-- Same index
SELECT id FROM users WHERE email = $1;
-- Planner may seq scan: index does not guarantee deleted rows are excluded

Works with stable sentinels — soft-delete and archive patterns:

CREATE INDEX documents_published_slug_idx
  ON documents (slug)
  WHERE published = true;
 
CREATE INDEX events_upcoming_idx
  ON events (starts_at)
  WHERE starts_at > '1970-01-01'::timestamptz AND cancelled = false;

Evitar funções voláteis em predicados (WHERE expires_at > now()). O conjunto indexado precisaria de manutenção constante; o planner não consegue raciocinar sobre limite móvel em plan time. Usar colunas status mantidas pela aplicação ou particionar por tempo.

Padrões de índice parcial de alto valor comuns:

Para unicidade entre subconjunto, índice unique parcial substitui checagens frágeis na aplicação:

CREATE UNIQUE INDEX one_active_sub_per_user
  ON subscriptions (user_id)
  WHERE status = 'active';

Só uma assinatura ativa por user_id — linhas cancelled não participam. Tentativas de inserir segunda linha ativa falham no banco com violação de constraint clara.

Índices parciais interagem com Row-Level Security e rotas de escrita

Políticas Row-Level Security acrescentam filtros implícitos. Predicado de índice parcial deve alinhar com como queries seguras rodam de fato — não como leem em código de aplicação sem RLS ativo.

Se toda query via PostgREST ou cliente Supabase aplica tenant_id = current_setting('app.tenant_id')::uuid, índice parcial só em WHERE status = 'open' pode ser invisível a planos que também precisam provar isolamento de tenant. Forma mais segura:

CREATE INDEX tasks_open_tenant_idx
  ON tasks (tenant_id, updated_at)
  WHERE status = 'open';

Queries que filtram tenant_id e status = 'open' batem. A orientação de Supabase RLS performance aplica: índices devem incluir colunas que políticas RLS referenciam, e predicados parciais devem refletir interseção de filtros de negócio e de política.

Caveat em write path: atualizar linha cruzando limite do predicado a move para dentro ou fora do índice. pending → completed apaga entrada de índice; completed → pending insere uma. Alto churn na coluna limite concentra carga de escrita — às vezes índice completo com melhor ordem de colunas é mais barato que manutenção constante de índice parcial.

Verificar com EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Esperar Index Scan ou Index Only Scan no nome do índice parcial. Se plano mostra Seq Scan, checar mismatch de predicado, estatísticas stale (ANALYZE orders) ou enable_indexscan desabilitado na sessão (raro). Comparar shared hit e tempo de execução antes e depois — índices parciais ganham em latência de leitura e pressão de cache, não por magia.

Quando o planner deve preferir índice parcial a composto?

Um índice parcial pode servir múltiplas variantes de query?

Só quando toda variante implica o mesmo predicado. WHERE status = 'pending' e WHERE status = 'pending' AND tenant_id = $1 qualificam para índice em (tenant_id, created_at) WHERE status = 'pending'. WHERE status IN ('pending', 'processing') não bate com índice predicado em status = 'pending' sozinho — usar WHERE status IN ('pending', 'processing') no índice ou manter índices parciais separados para rotas quentes disjuntas.

Índices parciais funcionam com colunas INCLUDE?

Sim. Postgres suporta CREATE INDEX ... INCLUDE (col) WHERE predicate para index-only scans que cobrem colunas extras sem ordenação btree nelas. Útil para SELECT id, slug quando predicado é published = true e slug é chave de lookup — INCLUDE (id) evita heap fetches quando visibility map permite.

Quão pequeno deve ser o conjunto filtrado para justificar parcial?

Sem ratio fixo — medir. Abaixo de roughly cinco a dez por cento de selectividade, índices parciais costumam dominar em tamanho e custo de escrita. Entre dez e trinta por cento, comparar pg_relation_size e custos de EXPLAIN contra índice composto completo. Acima de trinta por cento, índices parciais ainda ajudam quando amplificação de escrita em linhas excluídas é gargalo (linhas arquivadas inseridas uma vez, nunca atualizadas). Decisão depende do workload, não de regra fixa.

A common argument runs the other way

Um argumento frequente sustenta que índices parciais adicionam carga cognitiva — todo desenvolvedor precisa conhecer o predicado — e que particionar tabela ou índices compostos simples são mais fáceis de raciocinar em escala.

Particionar por status ou data é poderoso quando classes inteiras de query roteiam para uma partição. Carrega overhead operacional: regras de partition pruning, DDL de attachment, complexidade de backup. Índices parciais são mais leves quando uma fatia quente de tabela única impulsiona a dor — tickets abertos, webhooks pending, entradas de ledger não reconciliadas. Índices compostos sem predicados continuam default; índices parciais são ferramenta de precisão quando selectividade é extrema e predicado é estável em código de aplicação.

Key takeaways

• Índices parciais no Postgres armazenam só linhas que batem com predicado WHERE — índices menores, menos amplificação de escrita em linhas excluídas.
• Cláusulas WHERE de query devem implicar predicado do índice ou planner ignora o índice.
• Índices unique parciais impõem unicidade entre subconjunto sem corridas em nível de aplicação.
• Alinhar predicados com filtros RLS e incluir colunas tenant quando políticas exigem.
• Evitar funções voláteis em predicados; preferir colunas status ou particionamento para limites temporais.
• Confirmar com EXPLAIN (ANALYZE, BUFFERS) — índices parciais são invisíveis quando mal configurados.

Conclusion

A maioria das tabelas acumula linhas que o hot path nunca lê. Indexar histórico inteiro porque um dashboard consulta a cauda é como estruturas btree viram logs append-only multi-gigabyte que penalizam cada insert. Índices parciais admitem que noventa por cento das linhas estão frias — e linhas frias não pertencem à mesma estrutura que serve lookups sub-milissegundo aos dois por cento que não estão.

O próximo índice adicionado a tabela filtrada por status deve justificar por que o predicado não é WHERE status = '<hot value>'. Se a resposta é que ninguém definiu a fatia quente, índice completo provavelmente indexa o passado à custa do presente.