B-tree vs GIN vs GiST: um guia prático para PostgreSQL

O que você realmente está escolhendo ao selecionar um índice

A maioria dos problemas de índice no PostgreSQL começa do mesmo jeito: uma lista parece rápida com 1.000 linhas e fica lenta com 1.000.000. Ou uma caixa de busca que funcionou em testes vira uma pausa de segundos em produção. Quando isso acontece, é tentador perguntar “Qual índice é o melhor?” Uma pergunta mais útil é: “O que essa tela pede ao banco de dados?”

A mesma tabela pode precisar de tipos de índice diferentes porque telas diferentes a leem de maneiras distintas. Uma view filtra por um único status e ordena por created_at. Outra faz busca full-text. Outra verifica se um campo JSON contém uma chave. Outra encontra itens próximos a um ponto no mapa. São padrões de acesso diferentes, então um tipo de índice não vence em tudo.

Isso é o que você está escolhendo ao definir um índice: como a app acessa os dados. Você faz principalmente buscas exatas, ranges e ordenações? Você busca dentro de documentos ou arrays? Você pergunta “o que está perto desta localização” ou “o que se sobrepõe a este intervalo”? A resposta indica se B-tree, GIN ou GiST é o mais adequado.

B-tree, GIN e GiST em linguagem simples

Escolher um índice tem menos a ver com o tipo da coluna e mais com o que suas consultas fazem com ela. O PostgreSQL escolhe índices com base em operadores como =, <, @>, ou @@, não em se a coluna é “text” ou “json”. Por isso o mesmo campo pode precisar de índices diferentes em telas distintas.

B-tree: rápido para buscas ordenadas

B-tree é o padrão e a escolha mais comum. Brilha quando você filtra por valor exato, por intervalo ou precisa de resultados em uma ordem específica.

Um exemplo típico é uma lista administrativa filtrada por status e ordenada por created_at. Um índice B-tree em (status, created_at) pode ajudar tanto no filtro quanto na ordenação. B-tree também é a ferramenta usual para unicidade (unique constraints).

GIN: rápido quando cada linha contém muitas chaves pesquisáveis

GIN foi feito para perguntas do tipo “essa linha contém este termo/valor?”, onde uma linha pode corresponder a muitas chaves. Exemplos comuns são busca full-text (um documento contém palavras) e pertencimento em JSONB/arrays (JSON contém uma chave/valor).

Pense num registro de cliente com um objeto JSONB preferences, e numa tela que filtra usuários onde preferences contém { "newsletter": true }. Isso é um lookup no estilo GIN.

GiST: flexível para ranges, geo e similaridade

GiST é uma estrutura geral usada por tipos de dados que não cabem numa ordenação simples. É natural para ranges (sobreposição, contém), consultas geométricas/geográficas (perto, dentro) e algumas buscas por similaridade.

Ao decidir entre B-tree vs GIN vs GiST, comece listando os operadores que suas telas mais usadas usam. O índice certo normalmente fica claro depois disso.

Tabela de decisão para telas comuns (filtros, busca, JSON, geo)

A maioria das apps precisa de poucos padrões de índice. O truque é casar o comportamento da tela com os operadores que suas consultas usam.

Dois índices podem coexistir quando os endpoints diferem. Por exemplo, uma lista admin pode precisar de um B-tree em (tenant_id, created_at) para ordenação rápida, enquanto uma página de busca precisa de um GIN para @@. Mantenha ambos só se ambos os padrões de consulta forem comuns.

Se estiver em dúvida, olhe o operador primeiro. Índices ajudam quando o banco pode pular grandes partes da tabela.

Filtros e ordenação: onde B-tree costuma vencer

Para as telas do dia a dia, B-tree é a escolha monótona que funciona. Se sua consulta é “pegar linhas onde uma coluna iguala um valor, talvez ordenar, depois mostrar a página 1”, B-tree costuma ser a primeira tentativa.

Filtros de igualdade são o caso clássico. Colunas como status, user_id, account_id, type ou tenant_id aparecem constantemente em dashboards e painéis admin. Um índice B-tree pode pular direto para os valores que batem.

Filtros por range também cabem bem em B-tree. Quando você filtra por tempo ou intervalos numéricos, a estrutura ordenada ajuda: created_at >= ..., price BETWEEN ..., id > .... Se sua UI oferece “Últimos 7 dias” ou “$50 a $100”, B-tree faz exatamente o que você precisa.

Ordenação e paginação são onde B-tree pode poupar mais trabalho. Se a ordem do índice casa com seu ORDER BY, o PostgreSQL muitas vezes já retorna linhas ordenadas em vez de ordenar um grande conjunto na memória.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Índices compostos seguem uma regra simples: o PostgreSQL só pode usar eficientemente a parte inicial do índice. Pense “da esquerda para a direita.” Com (user_id, status, created_at), consultas que filtram por user_id (e opcionalmente status) se beneficiam. Uma consulta que filtra apenas por status normalmente não.

Índices parciais são um upgrade forte quando sua tela só se importa com uma fatia dos dados. Fatias comuns são “apenas linhas ativas”, “não soft-deleted” ou “atividade recente”. Eles mantêm o índice menor e mais rápido.

Colunas de alta cardinalidade e o custo de índices extras

Colunas de alta cardinalidade têm muitos valores únicos, como user_id, order_id, email ou created_at com precisão de segundos. Índices geralmente brilham aqui porque um filtro pode reduzir rapidamente os resultados para uma fatia pequena da tabela.

Colunas de baixa cardinalidade são o oposto: booleanos e enums pequenos como is_active, status IN ('open','closed') ou plan IN ('free','pro'). Um índice nessas colunas muitas vezes desaponta porque cada valor casa com uma grande parte das linhas. O PostgreSQL pode escolher corretamente um sequential scan, já que pular pelo índice ainda exige ler muitas páginas da tabela.

Outro custo sutil é buscar linhas. Mesmo que um índice encontre IDs rapidamente, o banco pode ainda precisar visitar a tabela por outras colunas. Se sua consulta precisa só de alguns campos, um índice cobridor (covering index) pode ajudar, mas também torna o índice maior e mais caro de manter.

Cada índice extra tem um preço nas escritas. Inserts precisam gravar em cada índice. Updates que mudam colunas indexadas têm que atualizar essas entradas também. Adicionar índices “só por garantia” pode deixar o app mais lento inteiro, não apenas uma tela.

Orientações práticas:

• Comece com 1–2 índices principais por tabela ocupada, baseados em filtros e ordenações reais.
• Priorize colunas de alta cardinalidade usadas em WHERE e ORDER BY.
• Tenha cautela ao indexar booleanos e enums pequenos, a não ser que se combinem com outra coluna seletiva.
• Adicione um novo índice só depois de nomear a consulta exata que ele vai acelerar.

Exemplo: uma lista de tickets de suporte filtrada por assignee_id (alta cardinalidade) se beneficia de um índice, enquanto is_archived = false sozinho muitas vezes não.

Telas de busca: full-text, prefixos e “contém”

Caixas de busca parecem simples, mas usuários esperam muito: várias palavras, formas diferentes das palavras e ranqueamento razoável. No PostgreSQL, isso geralmente é full-text: você armazena um tsvector (texto preparado) e consulta com um tsquery (o que o usuário digitou, parseado em termos).

Para full-text, GIN é o padrão porque é rápido ao responder “este documento contém estes termos?” em muitas linhas. A troca é escritas mais pesadas: inserir e atualizar linhas tende a custar mais.

GiST também pode servir para full-text. Costuma ser menor e mais barato para atualizar, mas geralmente mais lento nas leituras que GIN. Se seus dados mudam constantemente (por exemplo, tabelas tipo event), esse balanço leitura-escrita pode importar.

Prefix search não é full-text

Prefix search significa “começa com”, como buscar clientes por prefixo de email. Isso não é o que full-text faz. Para padrões de prefixo, um índice B-tree pode ajudar (com a classe de operador certa) porque bate com a forma que strings são ordenadas.

Para buscas “contém” como ILIKE '%error%', B-tree normalmente não ajuda. Aí entram índices trigram ou outra abordagem de busca.

Quando usuários querem filtros + busca de texto

A maioria das telas reais combina busca com filtros: status, assignee, intervalo de data, tenant, etc. Uma configuração prática é:

• Um índice GIN (ou às vezes GiST) para a coluna tsvector.
• Índices B-tree para os filtros mais seletivos (por exemplo, account_id, status, created_at).
• Uma regra estrita de “manter mínimo”, porque muitos índices tornam as escritas mais lentas.

Exemplo: uma tela de tickets que busca “refund delayed” e filtra status = 'open' e um account_id específico. Full-text traz as linhas relevantes, enquanto B-tree ajuda o PostgreSQL a reduzir rapidamente por conta e status.

Campos JSONB: escolher entre GIN e índices B-tree direcionados

JSONB é ótimo para dados flexíveis, mas pode virar consultas lentas se tratado como uma coluna normal. A decisão central é simples: você busca “em qualquer lugar desse JSON” ou filtra por alguns caminhos específicos com frequência?

Para queries de contenção como metadata @> '{"plan":"pro"}', um índice GIN é normalmente a primeira escolha. Ele foi feito para “este documento contém essa forma?” e também suporta checagens de existência de chave como ?, ?| e ?&.

Se sua app filtra principalmente por uma ou duas chaves JSON, um índice de expressão B-tree direcionado costuma ser mais rápido e menor. Também ajuda quando você precisa ordenar ou fazer comparações numéricas em valores extraídos.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Uma boa regra prática:

• Use GIN quando usuários pesquisam por múltiplas chaves, tags ou estruturas aninhadas.
• Use índices de expressão B-tree quando usuários filtram por caminhos específicos repetidamente.
• Indexe o que aparece em telas reais, não tudo.
• Se o desempenho depende de algumas chaves JSON que você usa sempre, considere promovê-las a colunas reais.

Exemplo: uma tela de suporte que filtra tickets por metadata->>'priority' e ordena por created_at. Indexe a path do JSON e a coluna created_at normal. Pule um GIN amplo a não ser que usuários também busquem tags ou atributos aninhados.

Geo e consultas por ranges: onde GiST se encaixa melhor

Telas geo e de ranges são onde GiST muitas vezes se torna óbvio. GiST foi pensado para “isso se sobrepõe, contém, ou está perto daquilo?” em vez de “este valor é igual àquele?”.

Dados geo geralmente significam pontos (local de uma loja), linhas (rota) ou polígonos (zona de entrega). Telas comuns incluem “lojas perto de mim”, “vagas dentro de 10 km”, “mostrar itens dentro desta caixa no mapa” ou “este endereço está dentro da nossa área de serviço?” Um índice GiST (normalmente via PostGIS geometry/geography) acelera esses operadores espaciais para que o banco pule a maioria das linhas em vez de checar cada forma.

Ranges são similares. PostgreSQL tem tipos range como daterange e int4range, e a pergunta típica é overlap: “essa reserva colide com outra?” ou “mostrar assinaturas ativas durante esta semana.” GiST suporta operadores de overlap e contenção de forma eficiente, por isso é comum em calendários, agendamento e checagens de disponibilidade.

B-tree ainda pode importar em telas geo-like. Muitas páginas primeiro filtram por tenant, status ou tempo, depois aplicam uma condição espacial, depois ordenam. Por exemplo: “apenas entregas da minha empresa, dos últimos 7 dias, mais próximas primeiro.” GiST trata da parte espacial, mas B-tree ajuda com filtros seletivos e ordenação.

Como escolher um índice passo a passo

A escolha de índice é mais sobre o operador que sobre o nome da coluna. A mesma coluna pode precisar de índices diferentes dependendo se você usa =, >, LIKE 'prefix%', busca full-text, contenção JSON ou distância geo.

Leia a consulta como uma checklist: WHERE decide quais linhas qualificam, JOIN decide como tabelas se conectam, ORDER BY decide a ordem de saída e LIMIT decide quantas linhas você realmente precisa. O melhor índice costuma ser o que ajuda a achar as primeiras 20 linhas rápido.

Um processo simples que funciona para a maioria das telas:

1. Anote os operadores exatos que sua tela usa (ex.: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Comece com um índice que combine com o filtro mais seletivo ou com a ordenação padrão. Se a tela ordena por created_at DESC, comece por aí.
3. Adicione um índice composto só quando você vir os mesmos filtros juntos o tempo todo. Coloque colunas de igualdade primeiro, depois as de range, depois a chave de ordenação.
4. Use um índice parcial quando você sempre filtra para um subconjunto (ex.: apenas status = 'open'). Use um índice de expressão quando você consulta um valor computado (ex.: lower(email) para buscas case-insensitive).
5. Valide com EXPLAIN ANALYZE. Mantenha se cortar muito o tempo de execução e reduzir substancialmente as linhas lidas.

Exemplo concreto: um dashboard de suporte pode filtrar tickets por status e ordenar pelos mais recentes. Um B-tree em (status, created_at DESC) é uma boa primeira tentativa. Se a mesma tela também filtra por uma flag JSONB meta @> '{"vip": true}', esse é um operador diferente e normalmente precisa de um índice focado em JSON.

Erros comuns que desperdiçam tempo (e deixam escritas lentas)

Uma maneira comum de se frustrar é escolher o “tipo certo” de índice para o operador errado. O PostgreSQL só pode usar um índice quando a consulta bate com o que o índice foi feito para responder. Se sua app usa ILIKE '%term%', um B-tree simples nessa coluna ficará parado, e você ainda fará scan na tabela.

Outra armadilha é construir índices multi-coluna gigantes “só para garantir”. Eles parecem seguros, mas são caros de manter e muitas vezes não combinam com padrões reais de consulta. Se as colunas mais à esquerda não são usadas no filtro, o resto do índice pode não ajudar.

Colunas de baixa seletividade também são fáceis de indexar demais. Um B-tree em um booleano como is_active ou um status com poucos valores pode ser quase inútil, a menos que seja parcial e combine com o slice exato que você consulta.

JSONB tem suas pegadinhas. Um GIN amplo pode ser ótimo para flexibilidade, mas muitos checks de path JSON são mais rápidos com um índice de expressão no valor extraído. Se sua tela sempre filtra por payload->>'customer_id', indexar essa expressão é muitas vezes menor e mais rápido do que indexar o documento inteiro.

Por fim, cada índice extra pesa nas escritas. Em tabelas atualizadas frequentemente (tickets, pedidos), cada insert e update tem que atualizar todos os índices.

Antes de adicionar um índice, pare e cheque:

• O índice corresponde ao operador exato que sua consulta usa?
• Dá para substituir um índice largo multi-coluna por um ou dois focados?
• Esse índice deveria ser parcial para evitar ruído de baixa seletividade?
• Para JSONB, um índice de expressão não serviria melhor?
• A tabela é suficientemente escrita que o custo do índice supera o ganho de leitura?

Checagens rápidas antes de criar (ou manter) um índice

Antes de criar um novo índice, seja específico sobre o que a app realmente faz. Um índice “bom ter” muitas vezes vira escritas mais lentas e mais armazenamento com pouco retorno.

Comece com suas três telas (ou endpoints) principais e escreva a forma exata da consulta: filtros, ordenação e o que o usuário digita. Muitos “problemas de índice” são na verdade “problemas de consulta indefinida”, especialmente quando se debate B-tree vs GIN vs GiST sem nomear o operador.

Um checklist simples:

• Pegue 3 telas reais e liste seus padrões exatos de WHERE e ORDER BY (incluindo direção e tratamento de NULL).
• Confirme o tipo de operador: igualdade (=), range (>, BETWEEN), prefixo, contenção, sobreposição ou distância.
• Escolha um índice por padrão comum de tela, teste e mantenha só os que reduzirem tempo ou leituras de forma mensurável.
• Se a tabela é write-heavy, seja rigoroso: índices extras multiplicam custo de escrita e podem aumentar pressão de vacuum.
• Reavalie após mudanças de feature. Um novo filtro, uma nova ordenação padrão ou trocar de “starts with” para “contains” pode tornar o índice antigo irrelevante.

Exemplo: um dashboard adiciona uma nova ordenação last_activity DESC. Se você só indexou status, o filtro pode continuar rápido, mas a ordenação agora força trabalho extra.

Exemplo: mapear telas reais para o índice certo

Uma tabela de decisão só ajuda se você a mapear para telas reais que entrega. Aqui estão três telas comuns e como elas se alinham com escolhas de índice.

Uma forma prática de aplicar isto é começar pela UI:

• Liste cada controle que reduz resultados (filtros).
• Anote a ordenação padrão.
• Seja específico sobre comportamento de busca (full-text vs starts-with vs contains).
• Indique campos “especiais” (JSONB, geo, ranges).

Próximos passos: torne indexação parte do seu processo de build

Bons índices seguem suas telas: os filtros que as pessoas clicam, a ordenação que esperam e a caixa de busca que realmente usam. Trate indexação como um hábito no desenvolvimento e você evitará a maioria das surpresas de desempenho mais tarde.

Mantenha o processo repetível: identifique as 1–3 queries que uma tela executa, adicione o menor índice que as atenda, teste com dados realistas e remova o que não justificar o custo.

Se você está construindo uma ferramenta interna ou portal de clientes, planeje a necessidade de índices cedo porque essas apps crescem adicionando filtros e mais telas de lista. Se você usa AppMaster (appmaster.io), trate cada configuração de filtro e ordenação de tela como um contrato de consulta concreto, então adicione apenas os índices que batem com esses cliques reais.