Indexando e consultando JSONB no PostgreSQL com GIN + pg_trgm e Prisma

Motivacao e trade-offs

Problema prático: consultas lentas em campos JSONB

Armazenar objetos flexíveis em colunas JSONB é prático: modelagem ágil, evoluções sem migração e suporte nativo do PostgreSQL. O trade-off aparece quando esses objetos precisam ser pesquisados por conteúdo — filtros por campos aninhados, buscas por substring em textos e consultas por estruturas parciais. Sem índices adequados, operações @> (containment), ->> (extrair texto) e buscas por padrões podem provocar varreduras completas (sequential scans) e latência alta.

Quais são as alternativas e quando escolher cada uma

Alternativas comuns são: extrair campos para colunas relacionais (normalização), usar colunas geradas (stored generated columns) e criar índices especializados sobre JSONB. Normalizar é ideal quando o campo é consultado frequentemente com semântica relacional. Colunas geradas são um meio-termo: mantém JSONB mas expõe partes para indexação. Índices GIN e pg_trgm servem quando a flexibilidade do JSONB deve ser preservada e as buscas são sobre conteúdo textual ou estrutura de objetos.

Dica: escolha normalização quando a consistência de modelo e integridade referencial importam; escolha JSONB+índice quando a flexibilidade é prioridade e as consultas são textuais ou por contenção.

Fundamentos técnicos

Como o PostgreSQL indexa JSONB

PostgreSQL oferece suporte nativo a JSONB e cria dois tipos relevantes de índices para esse tipo: índices GIN com jsonb_ops/jsonb_path_ops para acelerar operações de @> (containment) e índices de expressão (expression indexes) sobre valores extraídos do JSONB. jsonb_path_ops é mais compacto e rápido para consultas de contenção, mas não suporta todas as operações. Para buscas textuais (substring, LIKE), precisamos de pg_trgm, que opera sobre texto — logo, aplicamos trigram em expressões que extraem strings do JSONB.

pg_trgm e operator classes

A extensão pg_trgm introduz operadores e funções como similarity() e classes de índice gin_trgm_ops. Para usar trigrams em campos JSONB, criamos índices sobre expressões que convertem partes do JSONB em texto: por exemplo (data->>'title') gin_trgm_ops. Sem especificar a classe de operador certa, o índice GIN não será usado para operações LIKE/ILIKE ou funções de similaridade.

Exemplo 1: caso simples

Contexto do exemplo

Vamos modelar uma tabela articles com uma coluna meta JSONB que guarda título, tags e descrição. Objetivo: pesquisar por substring no título e filtrar por tag usando @>. O exemplo mostra criação de extensão, tabela, índices e consultas com EXPLAIN ANALYZE.

Criação da tabela e índices (SQL)

O bloco abaixo cria a extensão, tabela, popula alguns registros, e adiciona dois índices: um GIN padrão para contenção JSONB e um índice GIN de trigram sobre o título extraído.

-- Cria extensão pg_trgm (precisa de superuser ou owner com privilégio adequado)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Tabela com coluna JSONB
CREATE TABLE articles (
  id serial PRIMARY KEY,
  meta jsonb NOT NULL
);

-- Índice GIN para consultas por contenção (@>)
CREATE INDEX idx_articles_meta_gin ON articles USING gin (meta jsonb_path_ops);

-- Índice trigram sobre expressão que extrai title como texto
CREATE INDEX idx_articles_meta_title_trgm ON articles USING gin ((meta ->> 'title') gin_trgm_ops);

-- Insere alguns dados
INSERT INTO articles (meta) VALUES
  ('{"title":"Performance tuning in PostgreSQL","tags":["postgres","perf"],"body":"..."}'),
  ('{"title":"JSONB indexing patterns","tags":["postgres","jsonb"],"body":"..."}'),
  ('{"title":"Prisma and PostgreSQL integrations","tags":["prisma","nodejs"],"body":"..."}');

Depois de criar os índices, as consultas por contenção e por título com ILIKE podem usar índices. Veja o exemplo de consulta com explicação.

-- Busca por tag usando contenção (usa idx_articles_meta_gin)
EXPLAIN ANALYZE
SELECT id FROM articles WHERE meta @> '{"tags": ["postgres"]}';

-- Busca por substring no title (deve usar idx_articles_meta_title_trgm)
EXPLAIN ANALYZE
SELECT id FROM articles WHERE (meta ->> 'title') ILIKE '%postgres%';

O primeiro EXPLAIN deve mostrar uso de GIN Index Scan com jsonb_path_ops; o segundo mostrará Bitmap Index Scan ou Index Scan usando o índice trigram se o plano estimar que ele é seletivo.

Exemplo 2: caso avancado do mundo real

Requisitos reais e abordagem

Suponha um sistema de buscas para um marketplace onde anúncios têm um JSONB com campos dinâmicos: título, descrição, atributos (array de pares), localização e histórico. Requisitos: (1) pesquisa por texto parcial no título/description, (2) filtragem por atributos (containment), (3) ordenação por relevância (similarity), (4) compatibilidade com Prisma como ORM.

Modelagem no Prisma e migração com SQL manual

Prisma não expõe todos os tipos de índices complexos no schema. Solução prática: declarar o campo JSON na model, gerar migrations e, em seguida, adicionar um migration SQL manual que cria extensões/índices GIN/pg_trgm. Abaixo um exemplo de schema.prisma e o SQL a ser inserido na migration.

// schema.prisma (fragmento)
model Listing {
  id    Int    @id @default(autoincrement())
  data  Json   @db.JsonB
}

Depois de gerar a migration (prisma migrate dev --name init), edite o arquivo SQL gerado e adicione:

-- SQL na migration: cria ext e índices especializados
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_listing_data_gin
  ON "Listing" USING gin (data jsonb_path_ops);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_listing_title_trgm
  ON "Listing" USING gin ((data ->> 'title') gin_trgm_ops);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_listing_description_trgm
  ON "Listing" USING gin ((data ->> 'description') gin_trgm_ops);

Usar CONCURRENTLY evita locking que bloqueia writes em tabelas grandes. Após a migration, consultas complexas podem ser executadas por Prisma via raw queries ou combinando filtros JSON se simples.

Consultas com Prisma usando $queryRaw e parâmetros

Para aproveitar similaridade e evitar injeção, use parâmetros preparados com Prisma.$queryRaw. Exemplo de busca por texto que combina containment por atributo e similaridade no título:

// Node.js com Prisma Client
const search = '%bike%';
const attributeFilter = '{"attributes": [{"key": "condition", "value": "new"}]}'

const rows = await prisma.$queryRaw(
  `SELECT id, data,
          similarity(data ->> 'title', $1) AS sim
   FROM "Listing"
   WHERE data @> $2::jsonb
     AND (data ->> 'title') ILIKE $1
   ORDER BY sim DESC
   LIMIT 20`,
  search,
  attributeFilter
);

Esse padrão usa o índice GIN para contenção e o índice trigram para a busca por título. A ordenação por similaridade usa a função do pg_trgm.

Debugging e troubleshooting

EXPLAIN ANALYZE: interpretar planos e identificar ausência de índice

Quando uma consulta não usa o índice esperado, rode EXPLAIN (ANALYZE, BUFFERS, VERBOSE) para entender o plano. Procure por Sequential Scan ou Filter que utiliza operadores não sargable (não indexáveis). Se o filtro usa funções não indexáveis ou casting implícito, o índice pode ser ignorado.

Erros comuns e armadilhas

Listo abaixo problemas práticos que impedem uso eficiente de índices:

• Extensão não instalada: sem pg_trgm, índices trigram não existem.
• Operador incorreto: usar jsonb_ops quando você precisa de jsonb_path_ops para containment específico, ou esquecer gin_trgm_ops na expressão trigram.
• Comparações de tipo: usar casting inadequado faz o planner evitar o índice; por exemplo, comparar JSONB com string sem cast explícito.
• Tabelas pequenas: planner pode preferir seq scan se estimação de custo achata; índices só ajudam com seletividade adequada.
• Concorrência no CREATE INDEX: esquecer CONCURRENTLY em produção pode bloquear writes longamente.

Se o índice não é usado, verifique a operação exata (ILIKE vs LIKE vs similarity) e a operator class usada no índice; às vezes é necessário recriar o índice com a classe correta.

Soluções práticas para problemas específicos

Se a consulta usa funções no lado esquerdo (por ex. lower(meta->>'title') ILIKE ...), crie um índice sobre a mesma expressão (lower(meta->>'title') gin_trgm_ops). Para ordens por similaridade, crie índices trigram; para filtros @>, crie GIN com jsonb_path_ops. Se a consulta continua lenta, atualize estatísticas com ANALYZE e considere REINDEX ou aumentar work_mem temporariamente para construir índices.

Conclusao

Indexar JSONB requer escolher o índice certo para o tipo de consulta: GIN jsonb_path_ops para contenção e GIN + pg_trgm sobre expressões de texto para buscas por substring e similaridade. No ecossistema Prisma, o caminho prático é criar o campo JSONB na model e adicionar índices via migrations SQL manuais (ou criar via runtime com cuidado). Sempre valide com EXPLAIN ANALYZE, ative pg_trgm quando necessário e prefira CREATE INDEX CONCURRENTLY em produção.

Resumo das ações imediatas recomendadas:

1. Identifique padrões de consulta (containment vs busca textual).
2. Crie jsonb_path_ops para @> e gin_trgm_ops para textos extraídos.
3. Adicione índices via migration SQL para manter controle de versão.
4. Use Prisma.$queryRaw para consultas complexas e parametrizadas quando necessário.

Essas práticas evitam varreduras inteiras na tabela, reduzem latência e mantêm a flexibilidade do JSONB. Em sistemas com alto volume, combine normalização e JSONB corretamente: normalize o que precisa de integridade, mantenha JSONB para campos semi-estruturados e ofereça índices para as consultas críticas.

Se precisar, posso fornecer um script completo de migration pronto para aplicar em um banco existente, com checks e fallback para ambientes que não suportam CONCURRENTLY.