PostgreSQL: Ecossistema de Extensões, Foreign Data Wrappers…

Q: P: CREATE EXTENSION precisa de superusuário?

R: A maioria das extensões precisa de superusuário porque carrega uma biblioteca dinâmica C. PG 14+ permite algumas extensões via GRANT CREATE ON DATABASE + o mecanismo de extensão confiável.

Q: P: Qual é a dimensão máxima que a coluna vetorial do pgvector suporta?

R: O pgvector suporta até 16.000 dimensões (0.7.0+), mas escolha com base na saída do modelo (OpenAI 1536, Cohere 1024, etc.). Dimensões mais altas tornam os índices mais lentos.

Q: P: Como é o desempenho da consulta com postgres_fdw?

R: FDW tem sobrecarga de rede — consultas simples adicionam cerca de 2-5ms de latência. Definir use_remote_estimate=on permite que o remoto gere estimativas de custo e melhora a qualidade do plano. Para migração em massa, prefira COPY em vez de FDW.

Q: P: Qual devo escolher, HNSW ou IVFFlat?

R: Para 100K com necessidade de baixa latência, escolha HNSW (consulta mais rápida). HNSW constrói lentamente mas consulta muito mais rápido que IVFFlat.

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

Entender o mecanismo de extensão do PostgreSQL (CREATE EXTENSION)
Dominar extensões comuns: uuid-ossp, pgcrypto, pg_trgm, pg_stat_statements
Aprender Foreign Data Wrappers (FDW) para consultas entre bancos de dados
Praticar a configuração e uso de postgres_fdw e file_fdw
Dominar o armazenamento vetorial e busca por similaridade com pgvector
Entender as melhores práticas de gestão de extensões

2. A História

A empresa de e-commerce da Alice precisa fazer três coisas: (1) integrar recomendações de produtos por IA, exigindo busca vetorial; (2) migrar dados legados do MySQL para o PG, exigindo consultas entre bancos de dados; (3) acelerar a busca aproximada lenta, exigindo pg_trgm. Ela descobriu que o ecossistema de extensões do PostgreSQL resolve tudo em um só lugar — pgvector armazena embeddings de produtos, postgres_fdw conecta ao MySQL para a migração e pg_trgm aumenta o desempenho da busca aproximada. Um banco de dados substitui Elasticsearch + middleware + um serviço de recomendação próprio.

3. Conceito: Visão Geral do Mecanismo de Extensão

(1) O Que É uma Extensão do PostgreSQL

Uma extensão é o sistema de plugins do PostgreSQL — ela empacota objetos SQL relacionados (funções, tipos, operadores, métodos de índice) como uma única unidade que pode ser instalada e desinstalada com um comando.

SQL

-- Listar extensões disponíveis
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

(2) Comandos de Gestão de Extensões

Comando	Finalidade
`CREATE EXTENSION ext_name`	Instalar uma extensão
`CREATE EXTENSION IF NOT EXISTS ext_name`	Instalação idempotente
`CREATE EXTENSION ext_name VERSION '1.2'`	Instalar uma versão específica
`DROP EXTENSION ext_name`	Desinstalar uma extensão (CASCADE para manter config)
`ALTER EXTENSION ext_name UPDATE TO '2.0'`	Atualizar uma extensão
`\dx` (psql)	Listar extensões instaladas

(3) Pré-requisitos para Instalar Extensões

Condição	Descrição
Arquivo de biblioteca compartilhada	`.so` / `.dll` deve estar em `shared_preload_libraries` ou `dynamic_library_path`
Arquivo de controle	`extension_name.control` em `SHAREDIR/extension/`
Script SQL	`extension_name--version.sql` define os objetos
Privilégios	Precisa de `CREATE` no banco de dados atual + superusuário (para algumas extensões)

4. Operação: Extensões Comuns

▶ Exemplo: uuid-ossp Gera UUIDs

SQL

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Gerar UUID v4 (aleatório)
SELECT uuid_generate_v4();

-- Gerar UUID v1 (baseado em tempo)
SELECT uuid_generate_v1();

-- Usar como valor padrão de coluna
CREATE TABLE api_keys (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    key_name    TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: pgcrypto Criptografia de Dados

SQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash de senha com salt
SELECT crypt('MySecret123', gen_salt('bf'));

-- Verificar senha
SELECT crypt('MySecret123', stored_hash) = stored_hash AS is_match;

-- Criptografia AES
SELECT encode(encrypt('dados do cartão de crédito'::bytea,
    'secret_key_16bytes'::bytea, 'aes'), 'hex');

-- Gerar token aleatório
SELECT encode(gen_random_bytes(32), 'hex');

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: pg_trgm Busca Aproximada

SQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Mostrar pontuação de similaridade
SELECT similarity('PostgreSQL', 'Postgres');

-- Índice GIN trigram para busca aproximada rápida
CREATE INDEX idx_products_name_trgm ON products
    USING GIN (name gin_trgm_ops);

-- Busca aproximada com limite
SELECT name, similarity(name, 'iphon') AS score
FROM products
WHERE name % 'iphon'
ORDER BY score DESC;

TEXT 📖 Somente leitura

     name      | score
---------------+-------
 iPhone 15 Pro |  0.42
 iPhone 14     |  0.38
(2 rows)

▶ Exemplo: pg_stat_statements Estatísticas de Consultas Lentas

SQL

-- Deve estar em shared_preload_libraries primeiro
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 consultas por tempo total de execução
SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Redefinir estatísticas
SELECT pg_stat_statements_reset();

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

▶ Exemplo: Introdução ao PostGIS Dados Espaciais

SQL

CREATE EXTENSION IF NOT EXISTS postgis;

-- Armazenar geometria de ponto
CREATE TABLE stores (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    location GEOMETRY(POINT, 4326)
);

-- Inserir coordenadas (longitude, latitude)
INSERT INTO stores (name, location)
VALUES ('Dubai Mall', ST_SetSRID(ST_MakePoint(55.2796, 25.1972), 4326));

-- Encontrar lojas em um raio de 5 km
SELECT name,
    ST_Distance(location::geography,
        ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography
    ) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography, 5000);

TEXT 📖 Somente leitura

INSERT 0 1

5. Conceito: Foreign Data Wrappers

(1) Arquitetura FDW

flowchart LR
    A["PG Local<br/>postgres_fdw"] -->|"CREATE SERVER"| B["PG Remoto<br/>(ou MySQL/Oracle)"]
    A -->|"CREATE SERVER"| C["file_fdw<br/>(arquivos CSV/Log)"]
    A -->|"CREATE SERVER"| D["Outros FDW<br/>(Redis/MongoDB...)"]
    B --> E["IMPORT FOREIGN SCHEMA"]
    C --> F["CREATE FOREIGN TABLE"]
    D --> G["JOIN entre sistemas"]

Nome FDW	Fonte de dados alvo	Caso de uso
postgres_fdw	PostgreSQL remoto	Consultas entre bancos de dados, migração de dados
mysql_fdw	MySQL remoto	Migração MySQL→PG
file_fdw	Arquivos CSV locais	Análise de logs, importação de dados
redis_fdw	Redis	Consultas de cache
mongo_fdw	MongoDB	Consultas de documentos

(2) Etapas de Configuração do FDW

Etapa	Comando
1. Instalar extensão	`CREATE EXTENSION postgres_fdw`
2. Criar SERVER	`CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (...)`
3. Criar USER MAPPING	`CREATE USER MAPPING FOR local_user SERVER remote OPTIONS (...)`
4. Criar FOREIGN TABLE	`CREATE FOREIGN TABLE ft_xxx SERVER remote OPTIONS (...)`
5. Ou importar SCHEMA inteiro	`IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER remote INTO remote_schema`

6. Operação: Consulta entre Bancos de Dados com postgres_fdw

▶ Exemplo: Configurar uma Conexão com PostgreSQL Remoto

SQL

-- Etapa 1: Instalar extensão
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Etapa 2: Criar conexão de servidor
CREATE SERVER legacy_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.100', port '5432', dbname 'legacy');

-- Etapa 3: Mapear usuário local para credenciais remotas
CREATE USER MAPPING FOR current_user
    SERVER legacy_db
    OPTIONS (user 'admin', password 'secret123');

-- Etapa 4: Importar schema estrangeiro
IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, products, orders)
    FROM SERVER legacy_db
    INTO legacy_schema;

-- Agora consulte tabelas remotas como se fossem locais
SELECT u.name, COUNT(o.id) AS order_count
FROM legacy_schema.users u
JOIN legacy_schema.orders o ON u.id = o.user_id
GROUP BY u.name;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: JOIN entre Bancos de Dados Local e Remoto

SQL

-- Local: novo catálogo de produtos PG
-- Remoto: dados legados de pedidos MySQL via postgres_fdw

SELECT p.name,
       SUM(loi.quantity) AS total_sold,
       SUM(loi.quantity * loi.unit_price) AS revenue
FROM products p
JOIN legacy_schema.order_items loi ON p.id = loi.product_id
WHERE p.category = 'Electronics'
GROUP BY p.name
ORDER BY revenue DESC;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

▶ Exemplo: file_fdw Lê CSV Externo

SQL

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_server
    FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE access_logs_csv (
    ip_address    TEXT,
    request_time  TIMESTAMP,
    method        TEXT,
    path          TEXT,
    status_code   INT,
    response_time NUMERIC
) SERVER csv_server
OPTIONS (filename '/var/log/nginx/access.csv', format 'csv', header 'true');

-- Analisar logs nginx com SQL
SELECT path,
       COUNT(*) AS hits,
       AVG(response_time) AS avg_ms
FROM access_logs_csv
WHERE status_code = 200
GROUP BY path
ORDER BY hits DESC
LIMIT 20;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: Migração de Dados com FDW na Prática

SQL

-- Migrar usuários do legado para o local
INSERT INTO users (name, email, created_at)
SELECT name, email, created_at
FROM legacy_schema.users
WHERE id NOT IN (SELECT legacy_id FROM users);

-- Usar dblink para sincronização incremental
CREATE EXTENSION IF NOT EXISTS dblink;

SELECT dblink_connect('legacy', 'host=192.168.1.100 dbname=legacy user=admin password=secret123');

SELECT * FROM dblink('legacy',
    'SELECT id, name, email FROM users WHERE created_at > now() - interval ''1 day'''
) AS t(id BIGINT, name TEXT, email TEXT);

TEXT 📖 Somente leitura

INSERT 0 1

7. Conceito: Busca Vetorial com pgvector

(1) Como a Busca Vetorial Funciona

Modelos de IA codificam texto/imagens em vetores de alta dimensão (embeddings), e a distância entre vetores representa similaridade semântica. O pgvector permite que o PostgreSQL armazene e recupere vetores nativamente.

Métrica de distância	Fórmula	Caso de uso
Distância L2 (<=>)	Distância euclidiana	Distância espacial
Produto interno (<#>)	Produto escalar	Vetores normalizados
Distância cosseno (<=>)	1 - cos(θ)	Similaridade semântica

(2) Seleção de Índice do pgvector

flowchart TD
    A["Coluna vetorial criada"] --> B{"Linhas < 10K?"}
    B -->|Sim| C["Busca exata<br/>(sem necessidade de índice)"]
    B -->|Não| D{"Requisito de recall?"}
    D -->|"Alto recall (> 99%)"| E["IVFFlat<br/>(probes=lists)"]
    D -->|"Rápido + bom recall"| F["HNSW<br/>(ajuste ef_search)"]
    E --> G["CREATE INDEX ... USING ivfflat<br/>(vector_cosine_ops)"]
    F --> H["CREATE INDEX ... USING hnsw<br/>(vector_cosine_ops)"]

Índice	Velocidade de construção	Velocidade de consulta	Recall	Melhor para
Sem índice (força bruta)	N/A	Lento	100%	< 10K
IVFFlat	Médio	Rápido	95-99%	10K-1M
HNSW	Lento	Mais rápido	97-99,5%	100K-10M+

8. Operação: pgvector na Prática

▶ Exemplo: Instalar pgvector e Criar uma Coluna Vetorial

SQL

-- Instalar pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- Tabela de produtos com vetor de embedding (1536 dims para OpenAI)
CREATE TABLE products_vec (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    embedding   vector(1536)
);

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Inserir e Buscar por Similaridade de Cosseno

SQL

-- Inserir produto com embedding
INSERT INTO products_vec (name, category, price, embedding)
VALUES ('Fones de Ouvido Sem Fio', 'Electronics', 79.99,
    '[0.012, -0.034, 0.056, ...]'::vector);

-- Encontrar os 5 produtos mais similares por distância de cosseno
SELECT p.id, p.name, p.category, p.price,
       p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec p
ORDER BY p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

TEXT 📖 Somente leitura

INSERT 0 1

▶ Exemplo: Criar um Índice HNSW

SQL

-- Índice HNSW para busca aproximada rápida
CREATE INDEX idx_products_vec_hnsw ON products_vec
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Ajustar precisão da busca vs velocidade
SET hnsw.ef_search = 100;

-- Consulta com índice (muito mais rápida em grandes conjuntos de dados)
SELECT name,
       embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 10;

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Índice IVFFlat e Ajuste

SQL

-- Índice IVFFlat (construir após carregar dados para melhores centroides)
CREATE INDEX idx_products_vec_ivf ON products_vec
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- Aumentar probes para maior recall
SET ivfflat.probes = 10;

SELECT name, embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS dist
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Fluxo Completo de Recomendação de Produtos por IA

SQL

-- Armazenar embeddings de produtos do modelo de IA
INSERT INTO products_vec (name, category, price, embedding)
VALUES
    ('Tênis de Corrida', 'Sports', 129.99, array_to_vec(ARRAY[0.1,0.2,0.3])::vector),
    ('Tapete de Yoga',  'Sports',  39.99, array_to_vec(ARRAY[0.11,0.19,0.31])::vector),
    ('Caixa de Som Bluetooth', 'Electronics', 49.99, array_to_vec(ARRAY[0.5,0.1,0.2])::vector);

-- Usuário visualizou "Tênis de Corrida", recomendar itens similares
WITH target AS (
    SELECT embedding FROM products_vec WHERE name = 'Tênis de Corrida'
)
SELECT p.name, p.category, p.price,
       p.embedding <=> (SELECT embedding FROM target) AS similarity
FROM products_vec p
WHERE p.name != 'Tênis de Corrida'
ORDER BY similarity
LIMIT 3;

TEXT 📖 Somente leitura

INSERT 0 1

9. Operação: Melhores Práticas de Gestão de Extensões

(1) Gestão de Versões

SQL

-- Verificar versão atual da extensão
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Atualizar extensão
ALTER EXTENSION pgvector UPDATE TO '0.7.0';

-- Atualizar todas as extensões
SELECT extname,
       installed_version,
       default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

(2) Gestão de Privilégios

Cenário	Prática recomendada
Instalar extensão em produção	superusuário executa CREATE EXTENSION
Uso por usuário comum	GRANT USAGE ON SCHEMA / privilégio de execução de função
Conexão FDW	USER MAPPING armazena credenciais, não coloque senhas em hardcode
Atualização de extensão	Teste em staging primeiro, depois execute em produção

(3) Checklist de Auditoria de Produção

Item de verificação	Descrição
shared_preload_libraries	pg_stat_statements/pgvector precisam de pré-carregamento
Origem da extensão	Use apenas fontes oficiais ou de terceiros confiáveis
Bloqueio de versão	Registre versões de extensão de produção, evite atualização automática
Auditoria de segurança	Gestão de chaves pgcrypto, proteção de credenciais FDW
Teste de desinstalação	Confirme o escopo de impacto de DROP EXTENSION

10. Exemplo Abrangente

SQL

-- Configuração completa: extensões + FDW + pgvector para IA de e-commerce

-- Etapa 1: Instalar extensões principais
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgvector;

-- Etapa 2: Catálogo de produtos com busca vetorial
CREATE TABLE products_ai (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    attrs       JSONB DEFAULT '{}',
    embedding   vector(1536)
);

-- Etapa 3: Índice GIN para busca aproximada por nome
CREATE INDEX idx_products_name_trgm ON products_ai
    USING GIN (name gin_trgm_ops);

-- Etapa 4: Índice HNSW para similaridade vetorial
CREATE INDEX idx_products_embedding ON products_ai
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Etapa 5: Conectar banco de dados legado via FDW
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.50', port '5432', dbname 'legacy_shop');
CREATE USER MAPPING FOR current_user SERVER legacy_db
    OPTIONS (user 'migrate_user', password 'secure_pass');
IMPORT FOREIGN SCHEMA public LIMIT TO (old_products, old_users)
    FROM SERVER legacy_db INTO legacy;

-- Etapa 6: Migrar e transformar dados
INSERT INTO products_ai (name, category, price, attrs)
SELECT name, category, price,
       jsonb_build_object('weight_kg', weight, 'color', color)
FROM legacy.old_products
WHERE active = true;

-- Etapa 7: Busca combinada aproximada + vetorial
SELECT p.name, p.price,
       similarity(p.name, 'wireless earbuds') AS text_score,
       p.embedding <=> '[0.015,-0.030,0.050]'::vector AS vec_dist
FROM products_ai p
WHERE p.name % 'wireless earbuds'
ORDER BY vec_dist ASC
LIMIT 5;

❓ Perguntas Frequentes

📖 Resumo

O mecanismo de extensão do PostgreSQL (CREATE EXTENSION) permite gestão em estilo plugin
uuid-ossp (UUIDs), pgcrypto (criptografia), pg_trgm (busca aproximada), pg_stat_statements (análise de consultas lentas) são as quatro extensões mais comuns
Foreign Data Wrappers (FDW) permitem que o PG consulte fontes de dados externas como se fossem tabelas locais
postgres_fdw serve para consultas entre PG e migração; file_fdw serve para análise de logs CSV
pgvector fornece um tipo de dado vetorial + busca por similaridade; o índice HNSW consulta mais rápido
A gestão de extensões precisa de atenção a versão, privilégios, auditoria de segurança e o processo de revisão de produção

📝 Exercícios

⭐ Instale as extensões uuid-ossp e pgcrypto, crie uma tabela api_tokens usando UUID como chave primária e crypt() para armazenar o hash da senha, e escreva uma consulta que verifica uma senha.
⭐⭐ Configure postgres_fdw para conectar a um banco de dados PG remoto (use Docker para simular um), importe a tabela remota products e escreva uma consulta JOIN entre bancos de dados: tabela orders local + tabela products remota.
⭐⭐⭐ Projete uma busca de produtos com mecanismo duplo: pg_trgm trata busca textual aproximada, pgvector trata busca semântica vetorial. Escreva uma função de busca combinada search_products(keyword TEXT, query_vec vector, limit_count INT) que funde a pontuação de texto e a distância vetorial para ranqueamento e teste como os resultados diferem entre diferentes pesos.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós