Como usar jsonb no PostgreSQL para dados semiestruturados com índices

1. Introdução ao jsonb e dados semiestruturados

O PostgreSQL oferece dois tipos para armazenamento JSON: json e jsonb. A diferença fundamental é que jsonb armazena os dados em formato binário processado, removendo espaços em branco, reordenando chaves e eliminando chaves duplicadas. Isso torna as operações de consulta significativamente mais rápidas, embora a inserção seja ligeiramente mais custosa.

O tipo jsonb é ideal para cenários onde o esquema dos dados é flexível ou imprevisível: logs de eventos com metadados variáveis, configurações de usuário que mudam com o tempo, respostas de APIs externas, ou qualquer situação onde o número de campos e seus tipos podem variar entre registros.

A principal vantagem sobre colunas tradicionais é a eliminação de migrações de esquema. Você pode adicionar novos campos a qualquer registro sem alterar a estrutura da tabela, e consultar esses campos ad-hoc com operadores específicos.

2. Operações básicas com jsonb

Vamos criar uma tabela de exemplo para demonstrar as operações:

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    dados JSONB
);

INSERT INTO eventos (dados) VALUES
('{"usuario": "joao", "acao": "login", "metadados": {"ip": "192.168.1.1", "navegador": "Chrome"}}'),
('{"usuario": "maria", "acao": "compra", "valor": 150.00, "metadados": {"ip": "10.0.0.2"}}'),
('{"usuario": "joao", "acao": "logout", "metadados": {}}');

Para acessar campos, usamos -> (retorna JSON) e ->> (retorna texto):

SELECT dados->>'usuario' AS usuario,
       dados->'metadados'->>'ip' AS ip
FROM eventos
WHERE dados->>'acao' = 'login';

O operador @> verifica se um documento contém outro:

SELECT * FROM eventos
WHERE dados @> '{"usuario": "joao"}';

Para atualizar campos específicos, usamos jsonb_set():

UPDATE eventos
SET dados = jsonb_set(dados, '{metadados,ip}', '"192.168.1.100"')
WHERE dados->>'usuario' = 'joao' AND dados->>'acao' = 'login';

A concatenação com || permite mesclar objetos:

UPDATE eventos
SET dados = dados || '{"timestamp": "2023-01-01T10:00:00"}'
WHERE id = 1;

Para remover chaves, usamos o operador -:

UPDATE eventos
SET dados = dados - 'metadados'
WHERE id = 3;

3. Consultas avançadas com operadores e funções

Para navegar em estruturas aninhadas, #>> e #> usam caminhos textuais:

SELECT dados #>> '{metadados, ip}' AS ip
FROM eventos;

Filtros com ? verificam existência de chave, ?| verifica se alguma chave existe, e ?& verifica se todas existem:

SELECT * FROM eventos
WHERE dados ? 'valor';

SELECT * FROM eventos
WHERE dados ?| ARRAY['valor', 'timestamp'];

SELECT * FROM eventos
WHERE dados ?& ARRAY['usuario', 'acao'];

Funções agregadas são poderosas. jsonb_agg() agrupa valores em array:

SELECT dados->>'acao' AS acao,
       jsonb_agg(dados->>'usuario') AS usuarios
FROM eventos
GROUP BY dados->>'acao';

jsonb_each() desaninha um objeto em pares chave-valor:

SELECT id, chave, valor
FROM eventos, jsonb_each(dados)
WHERE id = 1;

4. Índices GIN para jsonb: fundamentos

O índice GIN (Generalized Inverted Index) é o tipo mais comum para jsonb. Ele funciona indexando cada chave e valor do documento JSON, permitindo buscas rápidas por qualquer combinação.

Para criar um índice GIN padrão:

CREATE INDEX idx_eventos_dados_gin ON eventos USING GIN (dados);

Este índice suporta os operadores @>, ?, ?| e ?&. Consultas que usam esses operadores podem se beneficiar do índice:

EXPLAIN ANALYZE SELECT * FROM eventos
WHERE dados @> '{"usuario": "joao"}';

A limitação principal é que operadores como ->> e -> não são diretamente suportados pelo índice GIN padrão, exigindo índices de expressão separados.

5. Índices GIN com operador de caminho (jsonb_path_ops)

O PostgreSQL oferece uma classe de operadores alternativa: jsonb_path_ops. A diferença é que este índice armazena apenas os caminhos dos valores, não os valores individuais.

CREATE INDEX idx_eventos_dados_path ON eventos USING GIN (dados jsonb_path_ops);

Vantagens do jsonb_path_ops:
- Índices menores (tipicamente 40-50% do tamanho do jsonb_ops)
- Consultas @> mais rápidas, especialmente com documentos grandes

Desvantagens:
- Não suporta os operadores ?, ?|, ?&
- Não funciona para consultas que verificam apenas existência de chave

Use jsonb_path_ops quando suas consultas forem predominantemente do tipo "contém" (@>), e jsonb_ops quando precisar de flexibilidade com todos os operadores.

6. Índices BTREE e expressões para jsonb

Para consultas frequentes em campos específicos, índices BTREE em expressões são mais eficientes que GIN:

CREATE INDEX idx_eventos_usuario ON eventos ((dados->>'usuario'));

Isso permite consultas rápidas com = e <:

SELECT * FROM eventos
WHERE dados->>'usuario' = 'joao';

Índices únicos com jsonb garantem unicidade em valores de chaves:

CREATE UNIQUE INDEX idx_eventos_acao_usuario ON eventos ((dados->>'usuario'), (dados->>'acao'));

Índices parciais são úteis quando apenas um subconjunto dos dados é consultado:

CREATE INDEX idx_eventos_compra_valor ON eventos ((dados->>'valor'))
WHERE dados @> '{"acao": "compra"}';

7. Estratégias de performance e otimização

Sempre use EXPLAIN ANALYZE para verificar se os índices estão sendo usados:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM eventos
WHERE dados @> '{"usuario": "joao"}';

Boas práticas:
- Evite jsonb_each() em tabelas grandes sem filtros prévios
- Prefira operadores indexados (@>, ?) em vez de funções que varrem todo o documento
- Para consultas com ->>, crie índices de expressão BTREE
- Monitore o tamanho dos índices com:

SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE indexname LIKE '%eventos%';

8. Casos de uso reais e armadilhas comuns

Exemplo prático: logs de eventos com metadados variáveis

CREATE TABLE logs_eventos (
    id BIGSERIAL,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    usuario TEXT,
    payload JSONB
);

-- Índices combinados
CREATE INDEX idx_logs_timestamp ON logs_eventos (timestamp);
CREATE INDEX idx_logs_payload_gin ON logs_eventos USING GIN (payload);
CREATE INDEX idx_logs_usuario ON logs_eventos ((payload->>'tipo'));

Armadilha 1: Crescimento excessivo de índices GIN

Índices GIN crescem rapidamente com dados muito aninhados. Para documentos com profundidade > 3 níveis, considere normalizar parte dos dados ou usar jsonb_path_ops.

Armadilha 2: Consultas que não usam índices

-- Esta consulta NÃO usa índice GIN:
SELECT * FROM eventos
WHERE dados->>'usuario' = 'joao';

-- Use índice de expressão ou reescreva com @>:
SELECT * FROM eventos
WHERE dados @> '{"usuario": "joao"}';

Referências