Índices PostgreSQL para apps SaaS: btree vs GIN vs GiST

Qual problema os índices resolvem em telas reais de SaaS

Um índice altera como o PostgreSQL encontra linhas. Sem índice, o banco frequentemente precisa ler grande parte da tabela (uma varredura sequencial) e então descartar a maior parte. Com o índice certo, ele pode pular direto para as linhas que batem (uma busca no índice) e buscar apenas o que precisa.

Você percebe isso cedo em SaaS porque telas do dia a dia fazem muitas consultas. Um único clique pode disparar várias leituras: a página de listagem, uma contagem total, alguns cards do dashboard e uma caixa de busca. Quando uma tabela cresce de milhares para milhões de linhas, a mesma consulta que parecia instantânea começa a ficar lenta.

Um exemplo típico é uma página de Orders filtrada por status e data, ordenada pelos mais recentes, com paginação. Se o PostgreSQL tem que varrer toda a tabela de orders para achar orders pagos dos últimos 30 dias, cada carregamento de página faz trabalho extra. Um índice bom transforma isso em um pulo rápido para a fatia certa dos dados.

Índices não são de graça. Cada um acelera leituras para consultas específicas, mas também deixa gravações mais lentas (INSERT/UPDATE/DELETE precisam atualizar índices) e usa mais armazenamento (além de pressionar mais o cache). Esse trade-off é o motivo pelo qual você deve partir de padrões reais de consulta, não de tipos de índice.

Uma regra simples que evita trabalho desnecessário: adicione um índice apenas quando você conseguir apontar para uma consulta específica e frequente que ele vai acelerar. Se você constrói telas com um gerador guiado por chat como o Koder.ai, é útil capturar o SQL por trás das suas páginas de listagem e dashboards e usar isso como sua lista de desejos de índices.

B-tree vs GIN vs GiST em termos simples

A maior parte da confusão sobre índices desaparece quando você para de pensar em recursos (JSON, busca, arrays) e começa a pensar na forma da consulta: o que a cláusula WHERE faz, e como você espera que os resultados sejam ordenados?

B-tree: rápido para ordenação e comparações

Use B-tree quando sua consulta se parece com comparações normais e você se importa com a ordenação. É o cavalo-de-trabalho para igualdade, intervalos e joins.

Padrões de exemplo: filtrar por tenant_id = ?, status = 'active', created_at >= ?, fazer join users.id = orders.user_id, ou mostrar “mais recentes primeiro” com ORDER BY created_at DESC.

GIN e GiST: quando uma linha contém muitos valores pesquisáveis

GIN (Generalized Inverted Index) é apropriado quando uma coluna contém muitos membros e você pergunta “isso contém X?”. Isso é comum com chaves JSONB, elementos de array e vetores de full-text.

Padrões de exemplo: metadata @> {'plan':'pro'} em JSONB, tags @> ARRAY['urgent'], ou to_tsvector(body) @@ plainto_tsquery('reset password').

GiST (Generalized Search Tree) serve para questões sobre distância ou sobreposição, onde valores se comportam como ranges ou formas. É usado com tipos range, dados geométricos e buscas de “match mais próximo”.

Padrões de exemplo: janelas de tempo sobrepostas com colunas range, buscas por similaridade (por exemplo, com operadores trigram), ou consultas espaciais (se você usa PostGIS).

Uma maneira prática de escolher:

• Se você filtra ou ordena por colunas normais, comece com B-tree.
• Se você verifica contenção ou associação, olhe para GIN.
• Se você pergunta “quão perto” ou “isso se sobrepõe?”, GiST costuma ser a opção.
• Se a consulta é rara ou a tabela é pequena, talvez você não precise de um novo índice.
• Se você não consegue descrever a forma da consulta, meça primeiro (EXPLAIN) antes de adicionar qualquer coisa.

Índices aceleram leituras, mas custam tempo em gravações e disco. Em SaaS, esse trade-off é mais importante em tabelas quentes como events, sessions e activity logs.

Padrões B-tree para filtros, ordenação e paginação

A maioria das telas de listagem em SaaS compartilha a mesma forma: um limite por tenant, alguns filtros e uma ordenação previsível. Índices B-tree são a escolha padrão aqui, e geralmente os mais baratos de manter.

Um padrão comum é WHERE tenant_id = ? mais filtros como status = ?, user_id = ?, e um intervalo de tempo como created_at >= ?. Para índices B-tree compostos, coloque filtros de igualdade primeiro (colunas que você compara com =), depois adicione a coluna pela qual você ordena.

Regras que funcionam bem na maioria das apps:

• Comece com tenant_id se toda consulta for scapeada por tenant.
• Coloque filtros = em seguida (frequentemente status, user_id).
• Coloque a coluna de ORDER BY por último (frequentemente created_at ou id).
• Use INCLUDE para cobrir páginas de listagem sem alargar a chave.
• Prefira paginação por seek (keyset) em vez de offset quando páginas ficam profundas.

Um exemplo realista: uma página de Tickets mostrando os itens mais novos primeiro, filtrada por status.

-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);

Esse índice suporta tanto o filtro quanto a ordenação, então o Postgres pode evitar ordenar um conjunto grande de resultados. A parte INCLUDE (title) ajuda a página de listagem a acessar menos páginas da tabela, mantendo as chaves do índice focadas em filtro e ordenação.

Para intervalos de tempo, a mesma ideia se aplica:

SELECT id, created_at
FROM events
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at <  $3
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);

Paginações são onde muitas apps SaaS ficam lentas. Paginação por offset (OFFSET 50000) força o banco a andar por muitas linhas. Paginação por seek permanece rápida usando a última chave vista:

SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
  AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;

Com o B-tree certo, isso continua rápido mesmo com o crescimento da tabela.

Indexação amiga do tenant sem excesso de índices

A maioria das apps SaaS é multi-tenant: toda consulta deve ficar dentro de um tenant. Se seus índices não incluem tenant_id, o Postgres ainda pode encontrar linhas rápido, mas frequentemente varre muito mais entradas do índice do que precisava. Índices conscientes do tenant mantêm os dados de cada tenant agrupados no índice para que telas comuns fiquem rápidas e previsíveis.

Uma regra simples: coloque tenant_id primeiro no índice quando a consulta sempre filtra por tenant. Depois adicione a coluna que você mais filtra ou ordena.

Índices de alto impacto e meio “sem glamour” costumam ser:

• (tenant_id, created_at) para listas de itens recentes e paginação por cursor
• (tenant_id, status) para filtros de status (Open, Paid, Failed)
• (tenant_id, user_id) para telas “itens deste usuário”
• (tenant_id, updated_at) para views administrativas de “recém alterados”
• (tenant_id, external_id) para lookup por webhooks ou imports

Over-indexing acontece quando você adiciona um novo índice para cada variação de tela. Antes de criar mais um, verifique se um índice composto existente já cobre as colunas mais à esquerda que você precisa. Por exemplo, se você tem (tenant_id, created_at), geralmente não precisa também de (tenant_id, created_at, id) a menos que realmente filtre por id depois dessas colunas.

Índices parciais podem reduzir tamanho e custo de escrita quando a maioria das linhas não é relevante. Funcionam bem com soft deletes e “somente ativos”, por exemplo: indexar apenas onde deleted_at IS NULL, ou apenas onde status = 'active'.

Cada índice extra torna as gravações mais pesadas. Inserts devem atualizar cada índice, e updates podem tocar múltiplos índices mesmo quando você altera só uma coluna. Se seu app ingere muitos eventos (incluindo apps construídos rapidamente com Koder.ai), mantenha os índices focados nas poucas formas de consulta que os usuários usam todo dia.

Indexando JSONB: GIN e índices de expressão direcionados

JSONB é útil quando sua app precisa de campos flexíveis como feature flags, atributos de usuário ou configurações por tenant. O problema é que operadores JSONB diferentes se comportam de forma diferente, então o melhor índice depende de como você consulta.

Duas formas importam mais:

• Contenção: “Esse JSON contém esses pares chave-valor?” usando @>.
• Extração por caminho: “Qual é o valor desse campo específico?” usando -> / ->> (frequentemente comparado com =).

Quando um índice GIN é a escolha certa

Se você filtra frequentemente com @>, um índice GIN na coluna JSONB geralmente compensa.

-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata @> '{"region":"eu","plan":"pro"}';

-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);

Se sua estrutura JSON é previsível e você usa @> em chaves de topo, jsonb_path_ops pode ser menor e mais rápido, mas suporta menos tipos de operadores.

Quando um índice de expressão é melhor

Se sua UI filtra repetidamente por um campo (como plan), extrair esse campo e indexá-lo costuma ser mais rápido e barato do que um GIN amplo.

SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata->> 'plan' = 'pro';

CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));

Uma regra prática: mantenha JSONB para atributos flexíveis e raramente filtrados, mas promova campos estáveis e de alto uso (plan, status, created_at) para colunas reais. Se você está iterando rápido em um app gerado, muitas vezes é um ajuste de esquema simples quando ficar claro quais filtros aparecem em todas as páginas.

Exemplo: se você armazena {"tags":["beta","finance"],"region":"us"} em JSONB, use GIN quando filtrar por conjuntos de atributos (@>), e adicione índices de expressão para as poucas chaves que dirigem a maioria das listas (plan, region).

Indexando arrays: quando GIN brilha

Arrays são tentadores porque são fáceis de armazenar e ler. Uma coluna users.roles text[] ou projects.labels text[] funciona bem quando você pergunta essencialmente: essa linha contém um valor (ou conjunto de valores)? É exatamente aí que GIN ajuda.

GIN é a escolha para queries de associação em arrays. Ele quebra o array em itens individuais e cria uma busca rápida para linhas que os contém.

Formas de consulta em arrays que normalmente se beneficiam:

• Contém um valor ou conjunto: @> (array contains)
• Sobreposição com um conjunto: && (array compartilha qualquer item)
• Às vezes: = ANY(...), mas @> costuma ser mais previsível

Um exemplo típico para filtrar usuários por papel:

-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];

CREATE INDEX users_roles_gin ON users USING GIN (roles);

E filtrando projetos por um conjunto de labels (precisa incluir ambos):

SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];

CREATE INDEX projects_labels_gin ON projects USING GIN (labels);

Onde as pessoas se surpreendem: alguns padrões não usam o índice do jeito que você espera. Se você transformar o array em string (array_to_string(labels, ',')) e então usar LIKE, o índice GIN não ajuda. Também, se você precisa de "começa com" ou correspondências fuzzy dentro das labels, aí é território de busca de texto, não membership de arrays.

Arrays também podem ficar difíceis de manter quando viram um mini-banco de dados: atualizações frequentes, precisar metadados por item (quem adicionou a label, quando, por quê) ou precisar de análises por label. Nesse caso, uma tabela de junção como project_labels(project_id, label) geralmente é mais fácil de validar, consultar e evoluir.

Indexação de busca: full-text e correspondência fuzzy (GIN e GiST)

Para caixas de busca, dois padrões aparecem repetidamente: full-text (encontrar registros sobre um tópico) e correspondência fuzzy (lidar com erros de digitação, nomes parciais e padrões ILIKE). O índice certo é a diferença entre “instantâneo” e “timeouts com 10k usuários”.

Full-text: tsvector + GIN

Use full-text quando usuários digitam palavras reais e você quer resultados ranqueados por relevância, como buscar tickets por assunto e descrição. O setup usual é armazenar um tsvector (muitas vezes em uma coluna gerada) e indexá-lo com GIN. Você busca com @@ e um tsquery.

-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
  to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);

-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');

-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);

SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';

O que guardar no vector: apenas os campos que você realmente pesquisa. Se incluir tudo (notes, logs internos), você paga em tamanho de índice e custo de escrita.

Correspondência fuzzy: trigramas com GIN ou GiST

Use similaridade por trigram quando usuários procuram nomes, emails ou frases curtas e você precisa de correspondência parcial ou tolerância a erros. Trigramas ajudam com ILIKE '%term%' e operadores de similaridade. GIN costuma ser mais rápido para “combina ou não”; GiST pode ser melhor quando você também se importa em ordenar por similaridade.

Regras práticas:

• Use GIN + tsvector para busca por relevância.
• Use trigramas para ILIKE e buscas tolerantes a erros.

Armadilhas a observar:

• Curingas no início sem trigramas (ILIKE '%abc') forçam varreduras.
• Termos muito curtos (1–2 chars) normalmente não funcionam bem com trigramas.
• Stop words e stemming podem surpreender em resultados de full-text, então escolha a configuração que combine com a linguagem do seu produto.

Se você está entregando telas de busca rapidamente, trate o índice como parte da feature: UX de busca e escolha de índices precisam ser desenhados juntos.

Passo a passo: da consulta lenta ao índice certo

Comece com a consulta exata que sua app executa, não com um palpite. Uma “tela lenta” costuma ser uma instrução SQL específica com WHERE e ORDER BY bem definidos. Copie do logs, do debug do ORM ou de onde você captura queries.

Um fluxo que funciona em apps reais:

• Capture o SQL completo, incluindo WHERE, ORDER BY e LIMIT.
• Rode EXPLAIN (ANALYZE, BUFFERS) na mesma query.
• Foque nos operadores que fazem o trabalho (=, >=, LIKE, @>, @@), não só nos nomes das colunas.
• Adicione o menor índice que combina com esses operadores.
• Rode novamente EXPLAIN (ANALYZE, BUFFERS) com volume de dados realista.

Aqui vai um exemplo concreto. Uma página de Customers filtra por tenant e status, ordena pelos mais novos e pagina:

SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Se o EXPLAIN mostrar uma varredura sequencial e uma ordenação, um índice B-tree que combine filtro e ordenação costuma resolver:

CREATE INDEX ON customers (tenant_id, status, created_at DESC);

Se a parte lenta é um filtro JSONB como metadata @> '{"plan":"pro"}', isso aponta para GIN. Se é full-text como to_tsvector(...) @@ plainto_tsquery(...), isso também aponta para um índice de busca com GIN. Se for um operador de “closest match” ou sobreposição, GiST costuma ser a escolha.

Depois de adicionar o índice, meça o trade-off. Verifique tamanho do índice, tempo de insert/update e se ajudou as principais queries lentas ou só um caso de borda. Em projetos que mudam rápido (incluindo os construídos com Koder.ai), essa re-verificação evita acumular índices não usados.

Erros comuns de indexação que desperdiçam tempo e dinheiro

A maioria dos problemas não é sobre escolher B-tree vs GIN vs GiST. É sobre construir um índice que parece certo, mas não combina com a forma como a app consulta a tabela.

Erros que mais atrapalham:

• Índices que nunca são usados. A query usa um operador diferente do que o índice suporta, ou um índice composto tem a ordem errada de colunas. Se seu WHERE começa com tenant_id e created_at, mas o índice começa com created_at, o planner pode ignorá-lo.
• Indexar colunas de baixa cardinalidade sozinhas. Um índice só em status, is_active ou um booleano frequentemente ajuda pouco porque corresponde a muitas linhas. Combine com uma coluna seletiva (como tenant_id ou created_at) ou evite.
• Índices sobrepostos que incham e deixam escritas mais lentas. Índices similares na mesma tabela podem duplicar espaço e tornar inserts/updates mais lentos.
• Pontos cegos de paginação. Indexar só pensando em OFFSET é uma armadilha comum. Se usar keyset pagination, você precisa de um índice que combine ordenação e filtro por “última chave vista”.
• Estatísticas desatualizadas e tabela bagunçada. Se autovacuum não está acompanhando, ou ANALYZE não rodou recentemente, o planner pode escolher planos ruins mesmo quando o índice certo existe.

Um exemplo concreto: sua tela de Invoices filtra por tenant_id e status, depois ordena por created_at DESC. Um índice só em status vai pouco ajudar. Um ajuste melhor é um índice composto que comece com tenant_id, depois status, e por fim created_at (filtrar primeiro, ordenar por último). Essa mudança simples costuma vencer três índices separados.

Trate cada índice como um custo. Ele tem que justificar seu lugar nas consultas reais, não só na teoria.

Checklist rápido antes de enviar mudanças de índice

Mudanças de índice são fáceis de enviar e irritantes de desfazer se aumentarem custo de escrita ou travarem uma tabela ocupada. Antes de mergear, trate como um pequeno release.

Comece decidindo o que você está otimizando. Puxe dois rankings curtos dos seus logs ou monitoramento: queries que rodam com mais frequência, e queries com maior latência. Para cada um, escreva a forma exata: colunas de filtro, ordem, joins e operadores usados (=, range, IN, ILIKE, operadores JSONB, contains de array). Isso evita adivinhação e te ajuda a escolher o tipo de índice certo.

Checklist antes do deploy:

• Confirme que a query está escopada por tenant quando deveria.
• Case o índice com o operador: B-tree para igualdade/intervalo/ordenação, GIN para membership (arrays, JSONB, full text), GiST para casos de sobreposição ou distância.
• Prefira um índice composto que combine filtro + ordenação comum, em vez de vários índices de coluna única.
• Mantenha o índice estreito: inclua só colunas que a query realmente usa.
• Planeje rollout: a criação do índice bloqueará escritas? Precisa agendar fora do pico?

Depois de adicionar, verifique se ajudou no plano real. Rode EXPLAIN (ANALYZE, BUFFERS) na query exata e compare antes/ depois. Depois monitore produção por um dia:

• A latência de leitura caiu nas telas alvo?
• A latência de escrita subiu (inserts/updates)?
• CPU ou armazenamento subiu por causa do novo índice?
• O índice está realmente sendo usado, ou é peso morto?

Se você está construindo com Koder.ai, vale manter o SQL gerado para uma ou duas telas lentas junto da mudança, assim o índice bate no que a app realmente executa.

Exemplo: indexando um fluxo típico de app SaaS + próximos passos

Imagine uma tela de admin comum: uma lista de Users com escopo por tenant, alguns filtros, ordenação por último ativo e uma caixa de busca. Aqui os índices deixam de ser teoria e começam a poupar tempo de verdade.

Três formas de query que você verá normalmente:

-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;

-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;

-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
  AND metadata @> '{"plan":"pro"}'::jsonb;

Um conjunto pequeno, mas intencional, de índices para essa tela:

• B-tree composto para a listagem: (tenant_id, status, last_active_at DESC).
• GIN para busca: uma coluna tsvector gerada com índice GIN.
• Indexação JSONB conforme uso: GIN (metadata) quando @> for comum, ou uma expressão B-tree como ((metadata->> 'plan')) quando você filtra principalmente por uma chave.

Necessidades mistas são normais. Se uma página faz filtros + busca + JSON, evite tentar enfiar tudo num mega-índice. Mantenha o B-tree para ordenação/paginação e adicione um índice especializado (frequentemente GIN) para a parte cara.

Próximos passos: escolha uma tela lenta, escreva suas 2–3 formas de query principais e revise cada índice por propósito (filtro, ordenação, busca, JSON). Se um índice não casar claramente com uma query real, risque-o do plano. Se você itera rápido com Koder.ai, fazer essa revisão conforme adiciona telas evita espalhamento de índices enquanto o esquema ainda muda.