Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps

Implementando Estratégias de Indexação JSONB no PostgreSQL para Consultas de Alto Desempenho no Estilo NoSQL

Escolha índices JSONB do PostgreSQL para consultas de contenção, busca por chave, filtros de intervalo e designs relacionais híbridos.

Implementando Estratégias de Indexação JSONB no PostgreSQL para Consultas de Alto Desempenho no Estilo NoSQL

A indexação JSONB no PostgreSQL ajuda quando sua tabela possui atributos flexíveis, mas suas consultas ainda precisam ser rápidas. JSONB funciona bem para metadados de produtos, payloads de eventos, configurações de perfil e outros dados semiestruturados, desde que você escolha índices que correspondam aos operadores que realmente utiliza.

JSONB vs JSON

JSON (Armazenamento de Texto)

  • Armazena a representação exata do texto
  • Mais rápido para inserir (sem processamento)
  • Mais lento para consultar
  • Preserva espaços em branco e ordem das chaves
  • Não possui classe de operador GIN nativa no estilo JSONB; índices de expressão em valores extraídos ainda são possíveis

JSONB (Armazenamento Binário)

  • Armazenado como binário decomposto
  • Pequena sobrecarga na inserção
  • Geralmente mais rápido para consultar do que json, especialmente quando indexado
  • Não preserva espaços em branco
  • Suporta várias estratégias de indexação úteis
  • Recomendado para a maioria dos dados JSON consultáveis

Operações Básicas com JSONB

Configuração da Tabela

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Inserir dados de exemplo
INSERT INTO products (name, metadata) VALUES
    ('Laptop', '{"brand": "Dell", "price": 999, "specs": {"ram": 16, "cpu": "i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 899, "specs": {"ram": 6, "storage": 128}}'),
    ('Tablet', '{"brand": "Samsung", "price": 599, "specs": {"ram": 8, "storage": 256}}');

Operadores de Consulta

-- Acessar por chave: ->
SELECT name, metadata->'brand' AS brand FROM products;

-- Acessar como texto: ->>
SELECT name, metadata->>'brand' AS brand_text FROM products;

-- Acesso aninhado
SELECT name, metadata->'specs'->>'ram' AS ram FROM products;

-- Verificar existência de chave: ?
SELECT * FROM products WHERE metadata ? 'brand';

-- Verificar se alguma chave existe: ?|
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'manufacturer'];

-- Verificar se todas as chaves existem: ?&
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'price'];

-- Contenção: @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- Contido por: <@
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ metadata;

Estratégias de Indexação JSONB

1. Índice GIN (Índice Invertido Generalizado)

Método de indexação padrão e mais versátil.

-- Criar índice GIN em toda a coluna JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Suporta operadores: @>, ?, ?|, ?&
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata ? 'brand';

Tamanho do índice vs desempenho da consulta:

  • Maior tamanho do índice
  • Excelente para consultas de contenção
  • Bom para verificações de existência de chave
  • Atualizações mais lentas (JSON inteiro reindexado)

2. Índice GIN com jsonb_path_ops

Otimizado apenas para consultas de contenção.

CREATE INDEX idx_products_metadata_ops ON products USING GIN (metadata jsonb_path_ops);

-- Suporta apenas: @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata @> '{"specs": {"ram": 16}}';

-- NÃO suporta: ?, ?|, ?&
-- SELECT * FROM products WHERE metadata ? 'brand';  -- Não usará o índice

Vantagens:

  • Frequentemente menor que o índice GIN JSONB padrão
  • Frequentemente mais rápido para consultas de contenção
  • Melhor para cargas de trabalho com muitas escritas

Quando usar:

  • Usando principalmente o operador @>
  • Necessidade de índices menores
  • Altas taxas de inserção/atualização

3. Índices de Expressão em Chaves Específicas

Indexar caminhos JSONB específicos para máximo desempenho.

-- Indexar um valor de texto específico
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

-- Indexar um valor numérico específico
CREATE INDEX idx_products_price ON products (((metadata->>'price')::NUMERIC));

-- Indexar valor aninhado
CREATE INDEX idx_products_ram ON products (((metadata->'specs'->>'ram')::INTEGER));

-- Consultas usam esses índices eficientemente
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';
SELECT * FROM products WHERE (metadata->>'price')::NUMERIC > 500;

Vantagens:

  • Menor tamanho do índice
  • Desempenho de consulta mais rápido para campos específicos
  • Benefícios do índice B-tree padrão (consultas de intervalo, ordenação)

Quando usar:

  • Campos específicos consultados com frequência
  • Necessidade de consultas de intervalo ou ordenação
  • Desejo de sobrecarga mínima de índice

4. Índices Parciais

Indexar apenas subconjunto relevante de dados.

-- Indexar apenas produtos com chave 'brand'
CREATE INDEX idx_products_branded ON products USING GIN (metadata)
    WHERE metadata ? 'brand';

-- Indexar apenas produtos caros
CREATE INDEX idx_expensive_products ON products USING GIN (metadata)
    WHERE (metadata->>'price')::NUMERIC > 500;

-- Indexar especificações de uma marca específica
CREATE INDEX idx_apple_specs ON products USING GIN (metadata->'specs')
    WHERE metadata->>'brand' = 'Apple';

Vantagens:

  • Índices muito menores
  • Atualizações mais rápidas para linhas não indexadas
  • Requisitos de armazenamento reduzidos

5. Índices Compostos

Combinar JSONB com colunas regulares.

-- Indexar categoria + metadados JSONB
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    metadata JSONB
);

CREATE INDEX idx_products_v2_category ON products_v2 (category);
CREATE INDEX idx_products_v2_metadata_gin ON products_v2 USING GIN (metadata jsonb_path_ops);

-- Consulta eficiente combinando ambos
SELECT * FROM products_v2 
WHERE category = 'electronics' 
  AND metadata @> '{"brand": "Apple"}';

Teste de Desempenho

Configuração do Conjunto de Dados de Teste

CREATE TABLE test_jsonb (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Inserir 1 milhão de registros
INSERT INTO test_jsonb (data)
SELECT jsonb_build_object(
    'user_id', generate_series(1, 1000000),
    'name', 'User ' || generate_series(1, 1000000),
    'age', (random() * 60 + 18)::INTEGER,
    'city', (ARRAY['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix'])[floor(random() * 5 + 1)],
    'active', random() > 0.5,
    'metadata', jsonb_build_object(
        'score', (random() * 1000)::INTEGER,
        'level', floor(random() * 10 + 1)
    )
);

Comparar Diferentes Tipos de Índice

-- Sem índice
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Espere uma varredura sequencial em uma tabela grande.

-- Índice GIN
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Espere uma varredura de índice bitmap quando o planejador estimar que é útil.

-- GIN jsonb_path_ops
DROP INDEX idx_gin;
CREATE INDEX idx_gin_ops ON test_jsonb USING GIN (data jsonb_path_ops);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Frequentemente usa um índice menor que a classe de operador GIN JSONB padrão.

-- Índice de expressão
DROP INDEX idx_gin_ops;
CREATE INDEX idx_city ON test_jsonb ((data->>'city'));
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data->>'city' = 'NYC';
-- Frequentemente a opção mais enxuta para uma chave frequentemente filtrada.

Os tempos exatos dependem do hardware, configurações do PostgreSQL, largura da linha, estado do cache e distribuição dos dados. Trate EXPLAIN (ANALYZE, BUFFERS) da sua própria carga de trabalho como a fonte da verdade.

Padrões Avançados de Consulta

Operações com Arrays

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES
    ('{"name": "Alice", "tags": ["developer", "golang", "postgresql"]}'),
    ('{"name": "Bob", "tags": ["designer", "figma", "ui"]}');

-- Verificar se array contém elemento
SELECT * FROM users WHERE profile->'tags' ? 'golang';

-- Verificar se array contém algum dos elementos
SELECT * FROM users WHERE profile->'tags' ?| ARRAY['golang', 'python'];

-- Verificar se array contém todos os elementos
SELECT * FROM users WHERE profile->'tags' ?& ARRAY['developer', 'golang'];

-- Expandir elementos do array
SELECT 
    id,
    profile->>'name' AS name,
    jsonb_array_elements_text(profile->'tags') AS tag
FROM users;

Agregação e Agrupamento

-- Contar por campo JSONB
SELECT 
    data->>'city' AS city,
    COUNT(*) AS user_count
FROM test_jsonb
GROUP BY data->>'city'
ORDER BY user_count DESC;

-- Média de campo numérico aninhado
SELECT 
    data->>'city' AS city,
    AVG((data->'metadata'->>'score')::INTEGER) AS avg_score
FROM test_jsonb
GROUP BY data->>'city';

-- Agregar objetos JSONB
SELECT 
    data->>'city' AS city,
    jsonb_agg(data->'name') AS user_names
FROM test_jsonb
WHERE (data->>'age')::INTEGER > 30
GROUP BY data->>'city';

Pesquisa de Texto Completo em JSONB

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB
);

INSERT INTO documents (content) VALUES
    ('{"title": "PostgreSQL Guide", "body": "Learn PostgreSQL JSONB indexing"}'),
    ('{"title": "NoSQL vs SQL", "body": "Comparison of database paradigms"}');

-- Criar índice de pesquisa de texto
CREATE INDEX idx_documents_fts ON documents 
    USING GIN (to_tsvector('english', content->>'body'));

-- Pesquisa de texto completo
SELECT * FROM documents
WHERE to_tsvector('english', content->>'body') @@ to_tsquery('PostgreSQL & indexing');

Atualizar Campos JSONB

-- Atualizar JSONB inteiro
UPDATE products SET metadata = '{"brand": "HP", "price": 1099}'::JSONB
WHERE id = 1;

-- Atualizar chave específica usando jsonb_set
UPDATE products 
SET metadata = jsonb_set(metadata, '{price}', '1199')
WHERE id = 1;

-- Atualizar valor aninhado
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram}', '32')
WHERE id = 1;

-- Adicionar nova chave
UPDATE products
SET metadata = metadata || '{"warranty": "2 years"}'::JSONB
WHERE id = 1;

-- Remover chave
UPDATE products
SET metadata = metadata - 'warranty'
WHERE id = 1;

-- Remover chave aninhada
UPDATE products
SET metadata = metadata #- '{specs,storage}'
WHERE id = 1;

Melhores Práticas de Design de Esquema

1. Extrair Campos Frequentemente Consultados

Ruim: Tudo em JSONB

CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    data JSONB  -- Contém: order_date, customer_id, status, total, items...
);

-- Consulta lenta
SELECT * FROM orders_bad 
WHERE data->>'status' = 'pending'
  AND (data->>'order_date')::DATE > '2024-01-01';

Bom: Abordagem híbrida

CREATE TABLE orders_good (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    metadata JSONB,  -- Campos consultados com menos frequência
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_status ON orders_good(status);
CREATE INDEX idx_orders_date ON orders_good(order_date);

-- Consulta rápida
SELECT * FROM orders_good
WHERE status = 'pending' AND order_date > '2024-01-01';

2. Normalizar Dados Repetidos

Ruim: Desnormalizado em JSONB

CREATE TABLE logs_bad (
    id SERIAL PRIMARY KEY,
    log_data JSONB
);

-- Informações de usuário repetidas em cada log
INSERT INTO logs_bad (log_data) VALUES
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "login"}'),
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "view_page"}');

Bom: Referenciar com ID

CREATE TABLE logs_good (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    action VARCHAR(50),
    metadata JSONB,  -- Apenas dados específicos da ação
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. Usar Restrições em JSONB

CREATE TABLE products_constrained (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    
    -- Garantir que chaves específicas existam
    CONSTRAINT metadata_has_brand CHECK (metadata ? 'brand'),
    
    -- Garantir que o preço seja positivo
    CONSTRAINT positive_price CHECK ((metadata->>'price')::NUMERIC > 0),
    
    -- Garantir que a estrutura aninhada exista
    CONSTRAINT has_specs CHECK (metadata ? 'specs')
);

Monitoramento e Otimização

Analisar Desempenho de Consultas JSONB

-- Habilitar temporização
\timing on

-- Analisar consulta
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- Verificar uso do índice
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products';

Identificar Índices Ausentes

-- Encontrar tabelas com varreduras sequenciais em colunas JSONB
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND tablename IN (
      SELECT tablename 
      FROM information_schema.columns 
      WHERE data_type = 'jsonb'
  )
ORDER BY seq_scan DESC;

Verificar Tamanho do Índice

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

Para índices GIN, comece com o tamanho do índice, contagens de varredura, custo de escrita e planos de consulta antes de decidir se deve reconstruir.

Migração de NoSQL para PostgreSQL JSONB

MongoDB para PostgreSQL

// Documento MongoDB
db.products.find({
    "brand": "Apple",
    "specs.ram": { $gte: 8 }
})

-- Equivalente PostgreSQL
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}'
  AND (metadata->'specs'->>'ram')::INTEGER >= 8;

Criar Script de Migração

import psycopg2
from pymongo import MongoClient
import json

# Conectar ao MongoDB
mongo_client = MongoClient('mongodb://localhost:27017/')
mongo_db = mongo_client['mydb']
mongo_collection = mongo_db['products']

# Conectar ao PostgreSQL
pg_conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)
pg_cursor = pg_conn.cursor()

# Migrar dados
for doc in mongo_collection.find():
    # Remover _id do MongoDB
    doc.pop('_id', None)
    
    # Inserir no PostgreSQL
    pg_cursor.execute(
        "INSERT INTO products (name, metadata) VALUES (%s, %s)",
        (doc.get('name'), json.dumps(doc))
    )

pg_conn.commit()
pg_cursor.close()
pg_conn.close()

Resumo de Melhores Práticas

  1. Use JSONB, não JSON: O formato binário permite indexação
  2. Escolha o tipo de índice certo: GIN para flexibilidade, índices de expressão para campos específicos
  3. Design de esquema híbrido: Extraia campos consultados com frequência para colunas
  4. Evite aninhamento profundo: Mantenha a estrutura JSONB razoavelmente plana
  5. Use restrições: Valide a estrutura e os valores do JSONB
  6. Monitore padrões de consulta: Crie índices com base no uso real
  7. Manutenção regular: VACUUM e REINDEX para desempenho ideal
  8. Benchmark antes da produção: Teste com volumes de dados realistas
  9. Considere particionamento: Para tabelas JSONB muito grandes
  10. Documente seu esquema: Mantenha documentação da estrutura JSONB esperada

Conclusão

Comece com um índice GIN padrão quando precisar de consultas flexíveis de contenção e existência de chave. Use jsonb_path_ops para cargas de trabalho pesadas em contenção e use índices de expressão quando uma chave JSONB se tornar um filtro ou campo de ordenação frequente. Se um campo for central para sua aplicação, promova-o para uma coluna real.