Extensões do PostgreSQL: pg_trgm, uuid-ossp, pgcrypto

1. Introdução às Extensões do PostgreSQL

O PostgreSQL possui um sistema de extensões que permite adicionar funcionalidades além do núcleo do banco de dados. As extensões são pacotes que podem ser instalados com o comando CREATE EXTENSION e oferecem recursos especializados para diferentes necessidades.

Para habilitar uma extensão, o superusuário do banco deve executar:

CREATE EXTENSION IF NOT EXISTS nome_da_extensao;

Este artigo aborda três extensões amplamente utilizadas:

  • pg_trgm: Fornece funções e operadores para determinar similaridade entre strings usando trigramas, ideal para busca textual aproximada
  • uuid-ossp: Gera identificadores únicos universais (UUIDs), essenciais para chaves primárias distribuídas
  • pgcrypto: Oferece funções criptográficas para hash, criptografia simétrica e geração de números aleatórios seguros

2. pg_trgm: Similaridade e Busca por Trigramas

Trigramas são sequências de três caracteres consecutivos extraídas de uma string. A extensão pg_trgm divide palavras em trigramas e calcula a similaridade entre duas strings contando quantos trigramas elas compartilham.

Principais funções e operadores:

-- Exibir trigramas de uma string
SELECT show_trgm('PostgreSQL');
-- Resultado: {"  p"," po",pos,ost,stg,tgr,gre,res,esq,sql,"ql "}

-- Calcular similaridade (0 a 1)
SELECT similarity('PostgreSQL', 'Postgres');
-- Resultado: 0.5

-- Similaridade de palavra
SELECT word_similarity('PostgreSQL', 'Postgres');
-- Resultado: 0.5833333

-- Operador de similaridade (%)
SELECT 'PostgreSQL' % 'Postgres';
-- Resultado: true (se similaridade > limite padrão de 0.3)

-- Operador de similaridade de palavra (%%)
SELECT 'PostgreSQL' %% 'Postgres';
-- Resultado: true

3. pg_trgm: Índices GIN e Otimização de Consultas

O verdadeiro poder do pg_trgm está nos índices GIN (Generalized Inverted Index) que aceleram consultas de similaridade e buscas LIKE/ILIKE.

-- Criar tabela de exemplo
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome_completo TEXT NOT NULL
);

-- Inserir dados de exemplo
INSERT INTO usuarios (nome_completo) VALUES
    ('Maria Silva Santos'),
    ('João Pedro Oliveira'),
    ('Ana Costa Pereira'),
    ('Carlos Eduardo Lima');

-- Criar índice GIN com operador de trigramas
CREATE INDEX idx_usuarios_nome_trgm ON usuarios
    USING GIN (nome_completo gin_trgm_ops);

-- Consulta com LIKE otimizada pelo índice
SELECT * FROM usuarios
WHERE nome_completo ILIKE '%silva%';

-- Busca aproximada com similaridade
SELECT *, similarity(nome_completo, 'Maria Silvia') AS sim
FROM usuarios
WHERE nome_completo % 'Maria Silvia'
ORDER BY sim DESC;

O índice GIN acelera significativamente consultas LIKE com padrões no início ou meio da string, algo que índices B-tree tradicionais não conseguem fazer eficientemente.

4. uuid-ossp: Geração de UUIDs

UUIDs (Universally Unique Identifiers) são identificadores de 128 bits que garantem unicidade sem necessidade de coordenação centralizada. A extensão uuid-ossp oferece diferentes algoritmos de geração:

-- UUID versão 1 (baseado em timestamp + MAC address)
SELECT uuid_generate_v1();
-- Exemplo: 123e4567-e89b-12d3-a456-426614174000

-- UUID versão 4 (aleatório)
SELECT uuid_generate_v4();
-- Exemplo: 9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d

-- UUID versão 3 (baseado em MD5 de namespace + nome)
SELECT uuid_generate_v3(uuid_ns_url(), 'https://exemplo.com');

-- UUID versão 5 (baseado em SHA-1 de namespace + nome)
SELECT uuid_generate_v5(uuid_ns_dns(), 'exemplo.com');

Vantagens dos UUIDs sobre inteiros seriais:
- Unicidade global, ideal para sistemas distribuídos
- Dificuldade de adivinhação de IDs
- Fusão de bancos de dados sem conflitos

5. uuid-ossp: Integração com Tabelas e Aplicações

-- Criar tabela com UUID como chave primária
CREATE TABLE pedidos (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    cliente_nome TEXT NOT NULL,
    data_criacao TIMESTAMP DEFAULT NOW()
);

-- Inserir registros automaticamente
INSERT INTO pedidos (cliente_nome) VALUES ('João Silva');
INSERT INTO pedidos (cliente_nome) VALUES ('Maria Santos');

-- Consultar dados
SELECT * FROM pedidos;

A partir do PostgreSQL 13, a função nativa gen_random_uuid() está disponível sem necessidade de extensão:

-- Alternativa nativa (PostgreSQL 13+)
CREATE TABLE produtos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nome TEXT NOT NULL
);

Considerações de performance:
- UUIDs ocupam 16 bytes vs 4 bytes de um INTEGER
- Índices em UUIDs podem ser menos eficientes que em inteiros sequenciais
- Para tabelas muito grandes, considere UUIDs ordenados (versão 7) ou particionamento

6. pgcrypto: Criptografia e Hash de Dados

A extensão pgcrypto oferece um conjunto completo de funções criptográficas:

-- Hash com SHA-256
SELECT digest('minha_senha_secreta', 'sha256');
-- Resultado: \x5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8

-- Hash com bcrypt para senhas
SELECT crypt('minha_senha', gen_salt('bf'));
-- Resultado: $2a$06$... (hash bcrypt com salt)

-- Criptografia simétrica com AES
SELECT encrypt(
    'dados_sensiveis',
    'chave_secreta_32_bytes',
    'aes'
);

-- Descriptografia
SELECT decrypt(
    encrypt('dados_sensiveis', 'chave', 'aes'),
    'chave',
    'aes'
);

-- Gerar bytes aleatórios seguros
SELECT gen_random_bytes(16);

-- HMAC (Hash-based Message Authentication Code)
SELECT hmac('mensagem', 'chave_secreta', 'sha256');

7. pgcrypto: Aplicações Práticas em Segurança

Armazenamento seguro de senhas

-- Criar tabela de usuários com senha hashada
CREATE TABLE usuarios_seguros (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    senha_hash TEXT NOT NULL
);

-- Inserir novo usuário (bcrypt com fator de custo 8)
INSERT INTO usuarios_seguros (email, senha_hash)
VALUES (
    'usuario@exemplo.com',
    crypt('senha_forte_123', gen_salt('bf', 8))
);

-- Verificar senha durante login
SELECT id, email
FROM usuarios_seguros
WHERE email = 'usuario@exemplo.com'
  AND senha_hash = crypt('senha_forte_123', senha_hash);

Criptografia de dados sensíveis

-- Criar tabela com coluna criptografada
CREATE TABLE dados_sensiveis (
    id SERIAL PRIMARY KEY,
    documento_criptografado BYTEA,
    chave_cripto TEXT DEFAULT 'minha_chave_aes_32b'
);

-- Inserir dados criptografados
INSERT INTO dados_sensiveis (documento_criptografado)
VALUES (
    encrypt('123.456.789-00', 'chave_32_bytes_segura!!', 'aes-cbc/pad:pkcs')
);

-- Recuperar dados descriptografados
SELECT id,
       decrypt(documento_criptografado, 'chave_32_bytes_segura!!', 'aes-cbc/pad:pkcs')
FROM dados_sensiveis;

8. Considerações Finais e Boas Práticas

Performance:
- Índices GIN do pg_trgm podem aumentar significativamente o tamanho do banco (até 3x o tamanho dos dados indexados)
- Funções criptográficas como bcrypt são intencionalmente lentas para dificultar ataques de força bruta
- UUIDs como chave primária podem causar fragmentação em índices B-tree

Compatibilidade:
- pg_trgm e pgcrypto estão disponíveis desde versões antigas do PostgreSQL
- uuid-ossp é recomendado para versões anteriores ao PostgreSQL 13
- A partir do PostgreSQL 13, gen_random_uuid() nativa substitui uuid_generate_v4()

Resumo comparativo:

Extensão Principal Uso Quando Utilizar
pg_trgm Busca textual aproximada Sistemas de busca, autocomplete, correção ortográfica
uuid-ossp Geração de UUIDs Sistemas distribuídos, chaves primárias globais
pgcrypto Criptografia e hash Armazenamento seguro de senhas, dados sensíveis

Referências