Índices no PostgreSQL: B-Tree, GIN, GiST e BRIN sob a Ótica da Engenharia de Sistemas

Os índices no PostgreSQL convertem varreduras no heap em buscas logarítmicas. Essa escolha define a latência de consultas e a vazão de gravação.

O armazenamento primário reside no heap em páginas de 8 KB desordenadas. Cada índice opera como uma estrutura secundária independente que aponta para tuplas físicas.

Compreender a mecânica de cada método de acesso evita contenção de locks, saturação de I/O e bloat de índices em produção.

Como o PostgreSQL organiza dados no heap e por que índices secundários são necessários?

O PostgreSQL armazena tabelas em arquivos particionados em blocos de 8 KB denominados páginas do heap. As tuplas ocupam o primeiro espaço livre disponível sem ordenação física. Localizar registros sem índice exige varrer todas as páginas da relação no disco.

Cada tupla física recebe um identificador de 6 bytes denominado TID (ItemPointerData). O TID armazena o número do bloco de 8 KB e o deslocamento do ponteiro de linha na página.

Em tabelas com milhões de linhas, uma varredura sequencial satura a memória e o subsistema de I/O. A busca linear percorre gigabytes de blocos irrelevantes.

Os índices secundários eliminam essa ineficiência. Eles organizam cópias ordenadas ou desconstruídas das chaves em arquivos dedicados. O padrão segue a especificação do Index Access Method Interface.

Ao consultar um índice, o motor recupera diretamente os TIDs correspondentes. O acesso ao heap torna-se pontual, reduzindo drasticamente os blocos lidos no pool de buffers compartilhados.

No MaxVision Code e em arquiteturas com agentes autônomos, o isolamento transacional depende dessa eficiência. Consultas lentas em metadados paralisam pipelines inteiros de execução.

-- Exemplo de diagnóstico no planejador com EXPLAIN (ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, criado_em 
FROM contas_usuarios 
WHERE email = 'engenharia@maxvision.com.br';

O que torna a B-Tree o índice padrão e como funciona a variante Lehmann-Yao?

A B-Tree do PostgreSQL implementa a variante de alta concorrência proposta por Lehman e Yao em 1981. Essa estrutura balanceada permite que leitores percorram a árvore sem travas exclusivas nos nós internos, mesmo durante divisões concorrentes de páginas de 8 KB.

Árvores B-Tree tradicionais exigiriam travas ascendentes durante a divisão de nós (page splits). Esse mecanismo paralisaria threads de leitura sob fluxos intensos de inserção concorrente.

O artigo fundacional de Lehman e Yao (1981) resolveu esse gargalo. Os autores introduziram o ponteiro de ligação à direita (right-link) e a chave limite (high key) em cada página.

Quando uma página atinge 8 KB e precisa se dividir, o nó original registra a chave máxima permitida. Metade das tuplas migra para uma nova página irmã alocada à direita.

Se uma leitura concorrente alcançar a página antiga antes da atualização do nó pai, ela identifica a chave limite. O leitor segue imediatamente o ponteiro right-link para a nova página sem reiniciar a busca.

A documentação da B-Tree no PostgreSQL detalha os operadores suportados. O índice atende a comparações escalares: =, <, ≤, >, ≥, BETWEEN e cláusulas ORDER BY.

No PostgreSQL 13+, a B-Tree inclui deduplicação nativa de chaves. Chaves repetidas compartilham uma única entrada física no nó folha. Isso reduz o tamanho do índice em até 40% em colunas com baixa cardinalidade.

Página Interna (8 KB)
[ Bloco 41 ] -> [ Chave 500 ] -> [ Bloco 42 ] -> [ High Key: 1000 ] -> [ Right-Link: Bloco 43 ]
                      |
                      v
Página Folha (8 KB): [ TID (12, 4) ] [ TID (12, 5) ] [ TID (14, 1) ]

Como o índice GIN estrutura dados invertidos para acelerar consultas em JSONB e arrays?

O GIN (Generalized Inverted Index) desconstrói valores compostos em chaves atômicas individuais e mapeia cada chave para uma lista de tuplas físicas. Essa arquitetura invertida viabiliza buscas instantâneas por contenção e pertinência em documentos JSONB, vetores de busca textual e arrays.

Em uma B-Tree convencional, o documento JSONB ou o array é tratado como um escalar único. Comparações internas de propriedades tornam-se inviáveis sem varrer a tabela inteira.

O índice GIN resolve esse problema dividindo o atributo composto. Cada elemento atômico, como uma chave JSONB ou tag de array, ganha uma entrada única na árvore principal de termos (Entry Tree).

Cada entrada na árvore aponta para uma estrutura de tuplas denominada posting list. Se a chave ocorre em milhares de linhas, o GIN converte a lista em uma B-Tree interna (posting tree).

Para evitar que inserções individuais reestruturem árvores a cada transação, o GIN adota uma fila pendente (fastupdate). Novas entradas acumulam-se em buffers e sofrem fusão em lote durante o autovacuum.

Ao consultar colunas JSONB com o operador de contenção @>, a escolha da classe de operadores altera o desempenho e o tamanho do índice:

-- GIN com jsonb_ops (padrão): indexa chaves e valores separadamente
CREATE INDEX idx_pedidos_dados_ops ON pedidos USING gin (payload jsonb_ops);

-- GIN com jsonb_path_ops: indexa hashes dos caminhos completos
CREATE INDEX idx_pedidos_dados_path ON pedidos USING gin (payload jsonb_path_ops);

A classe jsonb_path_ops produz índices até 70% menores do que jsonb_ops. Ela responde com máxima velocidade a consultas de caminho completo. A ressalva é que ela não suporta verificações de chaves soltas com o operador ?.

O que é o GiST e como árvores de predicados resolvem buscas geométricas e temporais?

O GiST (Generalized Search Tree) é um framework de índice balanceado que organiza dados com base em propriedades arbitrárias de contenção hierárquica. Ele agrupa dados espaciais, intervalos temporais e vizinhos mais próximos em envelopes delimitadores que suportam sobreposição e inclusão multidimensional.

Dados geométricos e temporais não possuem uma relação de ordem linear total estrita. Uma B-Tree não consegue responder se duas caixas bidimensionais se intersectam sem percorrer todas as entradas.

O artigo de Hellerstein, Naughton e Pfeffer (1995) definiu o framework. O GiST permite criar predicados customizados de chave.

Em dados PostGIS, cada nó intermediário armazena o retângulo delimitador mínimo (Minimum Bounding Box ou MBR) de seus nós filhos. O motor desce apenas pelos ramos cujos delimitadores intersectam a área pesquisada.

O GiST suporta indexação aproximada (lossy). Ele filtra eventuais falsos-positivos na etapa de rechecagem da tupla no heap.

Além de geometrias, o GiST é essencial para intervalos temporais com restrições de exclusão física. Ele impede sobreposição de agendamentos diretamente no banco de dados:

-- Impede agendamentos concorrentes da mesma sala no mesmo intervalo temporal
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE agendamento_salas 
ADD CONSTRAINT reserva_sem_colisao 
EXCLUDE USING gist (sala_id WITH =, periodo_reserva WITH &&);

O GiST também viabiliza buscas pelo vizinho mais próximo (k-Nearest Neighbors ou k-NN) via operador <->. O algoritmo calcula distâncias euclidianas navegando na árvore sem ordenar a tabela inteira.

Quando o índice BRIN reduz o consumo de disco em 99% em tabelas de séries temporais?

O BRIN (Block Range Index) armazena resumos estatísticos de intervalos contíguos de páginas do heap em vez de indexar tuplas individuais. Essa abordagem reduz o consumo de disco de gigabytes para kilobytes em tabelas massivas append-only cujos registros guardam correlação física natural com sua ordem cronológica.

Índices B-Tree tradicionais criam uma entrada dedicada para cada linha da tabela. Em tabelas com centenas de milhões de registros, a B-Tree consome dezenas de gigabytes e satura a memória.

O índice BRIN agrupa páginas consecutivas do heap. O agrupamento padrão usa o parâmetro pages_per_range com 128 blocos de 8 KB (1 MB).

Para cada intervalo físico de 1 MB, o BRIN armazena apenas os valores mínimo e máximo da coluna.

Ao executar um filtro por data, o planejador lê o índice diminuto. Ele descarta blocos físicos inteiros fora da faixa solicitada. O motor executa então um Bitmap Heap Scan apenas nas páginas selecionadas.

-- Criação de índice BRIN em tabela massiva de telemetria ou auditoria
CREATE INDEX idx_telemetria_data_brin 
ON telemetria_iot USING brin (data_medicao) 
WITH (pages_per_range = 128, autosummarize = on);

Para que o BRIN opere com eficiência, a coluna indexada precisa ter alta correlação física com o armazenamento em disco. Essa métrica é verificada na visão do catálogo pg_stats:

SELECT tablename, attname, correlation 
FROM pg_stats 
WHERE tablename = 'telemetria_iot' AND attname = 'data_medicao';

Valores de correlação próximos a 1.0 ou -1.0 indicam ordenação física perfeita. Se linhas sofrerem mutações e transferências frequentes entre páginas, os limites mínimo e máximo dos intervalos se expandem, degradando a seletividade do BRIN.

Tabela comparativa: B-Tree vs. GIN vs. GiST vs. BRIN

A escolha do método de acesso exige avaliar a estrutura física em disco. Também pesam os operadores aceitos, o consumo de armazenamento e a sobrecarga de escrita.

Como o mecanismo Heap-Only Tuples combate o Write Amplification em tabelas indexadas?

O mecanismo Heap-Only Tuples (HOT) permite que o PostgreSQL atualize registros sem modificar os índices secundários, desde que a alteração não afete colunas indexadas e a nova tupla caiba na mesma página de 8 KB do heap. Essa otimização elimina a amplificação de escrita e reduz o custo de limpeza do autovacuum.

Em bancos relacionais com MVCC, cada comando UPDATE grava uma cópia inteira do registro com novos carimbos de transação. Sem HOT, cada atualização forçaria inserções em todos os índices secundários da tabela.

Conforme detalhado no documento técnico README.HOT do código-fonte do PostgreSQL, a tupla modificada permanece conectada à versão anterior por um ponteiro interno. Os índices secundários continuam apontando apenas para o TID original.

Quando uma consulta percorre o índice e atinge a página do heap, ela segue a cadeia HOT interna até a versão visível. O motor executa a limpeza pontual de versões obsoletas (pruning) durante leituras normais, dispensando um ciclo global de VACUUM.

Para garantir que haja espaço vago em cada página para acomodar atualizações HOT, engenheiros ajustam o parâmetro fillfactor da tabela:

-- Reserva 20% de espaço livre em cada bloco de 8 KB para atualizações HOT
ALTER TABLE contas_usuarios SET (fillfactor = 80);
VACUUM FULL contas_usuarios;

Tabelas com 5 a 10 índices secundários que sofrem frequentes UPDATE sofrem degradação severa se o HOT for quebrado. Monitorar a taxa de sucesso do HOT na visão pg_stat_user_tables é essencial para a saúde operacional:

SELECT relname, 
       n_tup_upd AS total_updates, 
       n_tup_hot_upd AS hot_updates,
       ROUND((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS taxa_hot_pct
FROM pg_stat_user_tables 
WHERE n_tup_upd > 1000;

Taxas de HOT inferiores a 80% em tabelas transacionais indicam que índices desnecessários capturam colunas mutáveis ou que o fillfactor está saturado em 100%.

Como diagnosticar a eficiência de um índice com EXPLAIN ANALYZE e Visibility Map?

O planejador do PostgreSQL decide entre varreduras sequenciais e varreduras por índice avaliando a seletividade estimada da consulta, o custo de I/O e a distribuição estatística das colunas. Diagnosticar essa decisão exige analisar os planos de execução reais com métricas detalhadas de buffers compartilhados de memória.

Um índice nem sempre é a opção mais veloz. Se uma condição retornar mais de 10% a 20% das linhas totais da tabela, leituras aleatórias pelo índice tornam-se mais custosas do que ler o heap sequencialmente.

O comando EXPLAIN (ANALYZE, BUFFERS) revela exatamente como o motor comportou-se durante o processamento da consulta:

EXPLAIN (ANALYZE, BUFFERS)
SELECT nome, saldo 
FROM clientes 
WHERE status = 'ativo' AND criado_em >= '2026-01-01';

Existem três nós principais de varredura por índice no motor de execução:

1. Index Scan: O motor navega pela árvore do índice e visita a página física do heap para cada TID recuperado. Gera I/O aleatório disperso caso a tabela não esteja fisicamente ordenada.
2. Index Only Scan: Se todas as colunas requisitadas residem no próprio índice, o motor consulta o Visibility Map. Se a página do heap estiver marcada como totalmente visível, a leitura física do heap é eliminada.
3. Bitmap Index Scan: O motor lê um ou múltiplos índices e constrói um mapa de bits de páginas em memória (BitmapIndexScan). O BitmapHeapScan lê as páginas em ordem sequencial estrita, transformando I/O aleatório em contíguo e permitindo combinar índices com AND e OR.

-- Exemplo de criação de índice de cobertura com INCLUDE para Index Only Scan
CREATE INDEX idx_clientes_status_saldo 
ON clientes (status) INCLUDE (saldo, nome);

Com cláusulas INCLUDE, os atributos complementares residem exclusivamente nos nós folhas da B-Tree sem compor a chave de ordenação. O índice atende a consultas completas com zero acessos físicos ao heap quando o mapa de visibilidade está atualizado pelo autovacuum.

Perguntas Frequentes

Qual é a principal diferença entre os índices B-Tree e GIN no PostgreSQL?

A B-Tree é uma árvore balanceada de alta concorrência otimizada para valores escalares individuais, provendo buscas rápidas por igualdade, faixas de valores e ordenação. O GIN desconstrói dados compostos como documentos JSONB, arrays e termos textuais em elementos atômicos, mapeando cada chave para uma lista de registros físicos correspondentes.

Por que o índice BRIN ocupa até 99% menos espaço em disco do que a B-Tree?

O BRIN não cria entradas individuais para cada tupla da tabela no disco. Ele agrupa blocos contíguos de páginas do heap e armazena apenas valores de resumo estatístico, como os limites mínimo e máximo de cada intervalo. Esse design enxuto reduz o consumo físico de gigabytes para dezenas de kilobytes em séries temporais.

O que são Heap-Only Tuples e como essa otimização combate o Write Amplification?

O mecanismo Heap-Only Tuples permite que o PostgreSQL atualize registros sem modificar os índices secundários da tabela quando as colunas alteradas não participam de nenhum índice. A nova versão da linha é encadeada internamente na própria página de dados de 8 KB, evitando gravações redundantes nas árvores de índices.

Quando devo preferir a classe de operadores jsonb_path_ops no GIN?

A classe de operadores jsonb_path_ops deve ser preferida quando as consultas à coluna JSONB utilizam exclusivamente o operador de contenção @>. Ela armazena hashes dos caminhos completos dos atributos, gerando arquivos físicos até 70% menores e buscas sensivelmente mais rápidas do que o padrão jsonb_ops.

Em quais cenários um Index Only Scan ainda é forçado a ler dados do heap?

Um Index Only Scan precisa ler o heap quando a página física de destino não está com o bit de visibilidade total ativo no Visibility Map da tabela. Como os índices secundários do PostgreSQL não armazenam carimbos transacionais de MVCC, o motor valida no heap se a tupla está ativa caso o autovacuum ainda não tenha confirmado a visibilidade.