O ecossistema de extensões do PostgreSQL representa uma das maiores forças deste sistema de gerenciamento de banco de dados. Essas extensões transformam o PostgreSQL de um banco de dados relacional tradicional em uma plataforma versátil capaz de lidar com dados geoespaciais, análises estatísticas avançadas, inteligência artificial e integração com praticamente qualquer fonte de dados externa. A arquitetura extensível do PostgreSQL permite que desenvolvedores e administradores adicionem funcionalidades específicas sem modificar o código-fonte principal do sistema.
Este guia apresenta de forma detalhada todas as principais extensões disponíveis para sistemas baseados em RPM, como Red Hat, CentOS, Rocky Linux e AlmaLinux. Cada extensão será explorada com sua finalidade, instalação, ativação e exemplos práticos de uso. O conteúdo está organizado por categorias funcionais, facilitando a localização da ferramenta adequada para cada necessidade específica de desenvolvimento ou administração.
Foreign Data Wrappers: Conectando Fontes Externas
Os Foreign Data Wrappers (FDW) são uma tecnologia fundamental que transforma o PostgreSQL em um verdadeiro hub de integração de dados. Essas extensões permitem que o banco de dados se conecte a fontes externas como Oracle, MySQL, SQL Server, Hadoop e até arquivos simples, tratando-os como tabelas locais. A consulta ocorre usando SQL padrão, sem necessidade de ferramentas externas de ETL ou scripts complexos de importação.
O mecanismo FDW funciona através de uma camada de abstração que traduz comandos SQL do PostgreSQL para o formato nativo do sistema remoto. Muitas implementações suportam push-down, técnica que envia filtros e agregações diretamente para o servidor remoto, reduzindo drasticamente o volume de dados transferidos pela rede. Esta capacidade é essencial em ambientes híbridos onde diferentes bancos de dados coexistem durante processos de migração ou integração permanente.
oracle_fdw: Ponte para o Mundo Oracle
A extensão oracle_fdw é considerada o padrão da indústria para acesso eficiente entre PostgreSQL e bancos de dados Oracle. Desenvolvida com foco em performance, ela suporta operações de leitura e escrita completas, incluindo INSERT, UPDATE e DELETE. A implementação inclui suporte avançado para push-down de predicados, funções e até ordenações, minimizando o tráfego de rede e aproveitando o poder de processamento do servidor Oracle remoto.
Para instalar a extensão em sistemas baseados em RPM, o comando é direto. Após a instalação do pacote no sistema operacional, a extensão precisa ser ativada no banco de dados específico onde será utilizada. A configuração envolve a criação de um servidor externo que define os parâmetros de conexão, seguida pelo mapeamento de usuários e criação de tabelas estrangeiras que referenciam as tabelas Oracle.
# Instalacao do pacote no sistema operacional
dnf install oracle-fdw_18-- Ativacao da extensao no banco de dados
CREATE EXTENSION oracle_fdw;
-- Criacao do servidor externo
CREATE SERVER servidor_oracle
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//oracle-host:1521/ORCL');
-- Mapeamento de usuario
CREATE USER MAPPING FOR usuario_postgres
SERVER servidor_oracle
OPTIONS (user 'usuario_oracle', password 'senha_oracle');
-- Criacao de tabela estrangeira
CREATE FOREIGN TABLE clientes_oracle (
id INTEGER,
nome TEXT,
email TEXT
) SERVER servidor_oracle
OPTIONS (schema 'VENDAS', table 'CLIENTES');
-- Consulta funciona como tabela local
SELECT * FROM clientes_oracle WHERE id > 1000;mysql_fdw: Integrando Bancos MySQL e MariaDB
O mysql_fdw estabelece conectividade completa com servidores MySQL, MariaDB e Percona Server. Esta extensão é particularmente relevante em ambientes que utilizam múltiplos sistemas de código aberto, permitindo consultas federadas que combinam dados de PostgreSQL e MySQL em uma única query. Além de leitura, a extensão suporta operações de modificação de dados, possibilitando atualizações bidirecionais entre os sistemas.
A implementação do mysql_fdw inclui mapeamento automático de tipos de dados entre os dois sistemas, tratando diferenças como conversão de TINYINT do MySQL para SMALLINT do PostgreSQL. A extensão também gerencia transações distribuídas de forma transparente, garantindo consistência mesmo quando dados são modificados em ambos os lados. O processo de instalação e configuração segue padrão similar ao oracle_fdw, com criação de servidor, mapeamento de usuário e definição de tabelas estrangeiras.
# Instalacao da extensao MySQL FDW
dnf install mysql-fdw_18-- Ativacao no banco de dados
CREATE EXTENSION mysql_fdw;
-- Configuracao do servidor MySQL remoto
CREATE SERVER servidor_mysql
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'mysql-host', port '3306');
-- Mapeamento de credenciais
CREATE USER MAPPING FOR postgres
SERVER servidor_mysql
OPTIONS (username 'root', password 'mysql_password');
-- Importacao automatica de schema completo
IMPORT FOREIGN SCHEMA loja
FROM SERVER servidor_mysql
INTO public;
-- Join entre tabela local PostgreSQL e remota MySQL
SELECT p.nome, m.quantidade
FROM produtos_locais p
JOIN estoque_mysql m ON p.id = m.produto_id;tds_fdw: Acesso a SQL Server e Sybase
A extensão tds_fdw utiliza o protocolo Tabular Data Stream para conectar PostgreSQL a bancos de dados da Microsoft, especificamente SQL Server e Sybase. Esta ponte é fundamental em organizações corporativas onde sistemas legados Windows coexistem com infraestrutura Linux moderna. O protocolo TDS é maduro e estável, garantindo transferência confiável de dados entre plataformas completamente diferentes.
A extensão trata automaticamente diferenças de implementação SQL entre os sistemas, como funções de string e operadores de data. Suporta tipos complexos incluindo XML, JSON (disponível em versões recentes do SQL Server) e tipos geográficos. A configuração requer especificação da versão do protocolo TDS, que varia entre versões antigas e modernas do SQL Server, garantindo compatibilidade máxima.
# Instalacao do wrapper TDS
dnf install tds-fdw_18-- Ativacao da extensao
CREATE EXTENSION tds_fdw;
-- Servidor SQL Server
CREATE SERVER servidor_sqlserver
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (
servername 'sqlserver-host',
port '1433',
database 'ERP',
tds_version '7.4'
);
-- Mapeamento de usuario Windows
CREATE USER MAPPING FOR postgres
SERVER servidor_sqlserver
OPTIONS (username 'dominio\usuario', password 'senha');
-- Tabela estrangeira com tipos SQL Server
CREATE FOREIGN TABLE pedidos_sqlserver (
id INTEGER,
data_pedido DATE,
valor_total DECIMAL(10,2),
xml_detalhes XML
) SERVER servidor_sqlserver
OPTIONS (schema_name 'dbo', table_name 'Pedidos');pgbouncer_fdw: Monitorando Connection Pooler
O pgbouncer_fdw oferece uma interface SQL para consultar estatísticas internas do PgBouncer, o gerenciador de pool de conexões mais utilizado com PostgreSQL. Tradicionalmente, administradores precisam conectar-se à porta administrativa do PgBouncer e executar comandos especiais como SHOW POOLS ou SHOW CLIENTS. Esta extensão elimina essa necessidade, permitindo que consultas SQL diretas retornem essas informações.
A integração é especialmente valiosa para ferramentas de monitoramento automatizado e dashboards que podem agora incluir métricas do PgBouncer ao lado de métricas do banco de dados. Informações como número de conexões ativas por pool, tempo de espera de clientes e estatísticas de transações ficam acessíveis através de JOINs com tabelas do próprio PostgreSQL, criando relatórios unificados de performance.
# Instalacao do wrapper PgBouncer
dnf install pgbouncer-fdw_18-- Ativacao da extensao
CREATE EXTENSION pgbouncer_fdw;
-- Servidor apontando para porta administrativa do PgBouncer
CREATE SERVER pgbouncer_admin
FOREIGN DATA WRAPPER pgbouncer_fdw
OPTIONS (
address '127.0.0.1',
port '6432'
);
-- Mapeamento para usuario admin do PgBouncer
CREATE USER MAPPING FOR postgres
SERVER pgbouncer_admin
OPTIONS (user 'pgbouncer', password 'admin_password');
-- Criacao de views para comandos SHOW
CREATE FOREIGN TABLE pgbouncer_pools (
database TEXT,
usuario TEXT,
cl_active INTEGER,
cl_waiting INTEGER,
sv_active INTEGER,
sv_idle INTEGER
) SERVER pgbouncer_admin OPTIONS (table 'pools');
-- Consulta estatisticas de pool
SELECT database, cl_active, sv_idle
FROM pgbouncer_pools
ORDER BY cl_active DESC;sqlite_fdw: Acessando Bancos SQLite Locais
A extensão sqlite_fdw permite que o PostgreSQL leia e escreva em arquivos de banco de dados SQLite armazenados no sistema de arquivos do servidor. Esta capacidade é extremamente útil para importar dados de aplicações móveis, dispositivos embarcados ou sistemas desktop que usam SQLite como armazenamento local. O acesso é direto ao arquivo, sem necessidade de servidor SQLite em execução.
Casos de uso comuns incluem análise de logs de aplicativos móveis coletados de dispositivos, consolidação de dados de múltiplos arquivos SQLite em um data warehouse central, e migração de sistemas pequenos para PostgreSQL. A extensão gerencia automaticamente bloqueios de arquivo e transações, garantindo integridade mesmo quando o arquivo SQLite é acessado por outros processos simultaneamente.
# Instalacao do wrapper SQLite
dnf install sqlite-fdw_18-- Ativacao da extensao
CREATE EXTENSION sqlite_fdw;
-- Servidor apontando para arquivo SQLite local
CREATE SERVER sqlite_local
FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/var/lib/pgsql/dados/aplicacao.db');
-- Importacao automatica de todas as tabelas
IMPORT FOREIGN SCHEMA main
FROM SERVER sqlite_local
INTO public;
-- Leitura de tabela SQLite
SELECT * FROM logs_aplicacao
WHERE timestamp > current_date - interval '7 days';
-- Agregacao de multiplos arquivos SQLite
CREATE FOREIGN TABLE logs_dispositivo1 (
id INTEGER,
mensagem TEXT,
timestamp TIMESTAMP
) SERVER sqlite_local
OPTIONS (table 'logs');
-- Union de varios dispositivos
SELECT 'dispositivo1' as origem, * FROM logs_dispositivo1
UNION ALL
SELECT 'dispositivo2' as origem, * FROM logs_dispositivo2;ogr_fdw: Lendo Dados Geoespaciais em Diversos Formatos
O ogr_fdw é uma extensão poderosa que aproveita a biblioteca OGR (parte do projeto GDAL) para ler dezenas de formatos de dados vetoriais geoespaciais. Isso inclui Shapefiles, KML, GeoJSON, GPX, arquivos Excel com coordenadas e até dados armazenados em serviços web WFS. A extensão transforma esses arquivos em tabelas PostgreSQL consultáveis, eliminando a necessidade de conversão prévia.
A capacidade de ler diretamente formatos geoespaciais proprietários e abertos torna o ogr_fdw essencial para profissionais de GIS (Sistemas de Informação Geográfica). É possível combinar dados de diferentes fontes em uma única consulta, por exemplo, cruzar shapefiles governamentais com dados JSON de APIs públicas e tabelas PostGIS locais. A extensão detecta automaticamente o sistema de coordenadas e tipos de geometria, facilitando a integração.
# Instalacao da extensao OGR
dnf install ogr-fdw_18-- Ativacao da extensao
CREATE EXTENSION ogr_fdw;
-- Servidor para ler Shapefile local
CREATE SERVER shapefile_server
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/dados/geo/municipios.shp',
format 'ESRI Shapefile'
);
-- Tabela estrangeira do shapefile
CREATE FOREIGN TABLE municipios_shapefile (
fid INTEGER,
nome TEXT,
populacao INTEGER,
geom GEOMETRY(MULTIPOLYGON, 4326)
) SERVER shapefile_server
OPTIONS (layer 'municipios');
-- Servidor para ler GeoJSON de URL
CREATE SERVER geojson_web
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'https://api.exemplo.com/pontos.geojson',
format 'GeoJSON'
);
-- Combinacao de shapefile local com GeoJSON remoto
SELECT m.nome, COUNT(p.id) as total_pontos
FROM municipios_shapefile m
LEFT JOIN pontos_geojson p ON ST_Contains(m.geom, p.geom)
GROUP BY m.nome;hdfs_fdw: Conectando ao Ecossistema Hadoop
A extensão hdfs_fdw estabelece uma ponte entre PostgreSQL e o Hadoop File System, permitindo consultas SQL diretas sobre dados armazenados em clusters Hadoop. Isso é revolucionário para ambientes de Big Data, pois elimina a necessidade de exportar petabytes de dados do HDFS para o PostgreSQL antes de analisá-los. A extensão se conecta ao Hive ou diretamente ao HDFS, executando consultas distribuídas no cluster.
O processamento ocorre no lado do Hadoop, aproveitando sua capacidade de processamento paralelo massivo. O PostgreSQL recebe apenas os resultados agregados, tornando viável a análise de datasets que seriam impossíveis de processar localmente. Casos de uso incluem relatórios executivos sobre dados históricos armazenados em data lakes, auditorias de conformidade em logs distribuídos e integração de dados operacionais (PostgreSQL) com dados analíticos (Hadoop).
# Instalacao do wrapper HDFS
dnf install hdfs-fdw_18-- Ativacao da extensao
CREATE EXTENSION hdfs_fdw;
-- Servidor Hive no cluster Hadoop
CREATE SERVER hadoop_cluster
FOREIGN DATA WRAPPER hdfs_fdw
OPTIONS (
host 'hadoop-namenode',
port '10000',
client_type 'hiveserver2',
auth_type 'NOSASL'
);
-- Mapeamento de usuario
CREATE USER MAPPING FOR postgres
SERVER hadoop_cluster
OPTIONS (username 'hdfs_user');
-- Tabela particionada no Hive
CREATE FOREIGN TABLE logs_acesso_hdfs (
timestamp_acesso TIMESTAMP,
usuario TEXT,
pagina TEXT,
tempo_resposta INTEGER
) SERVER hadoop_cluster
OPTIONS (
dbname 'logs',
table_name 'acessos_web'
);
-- Agregacao executada no Hadoop
SELECT DATE(timestamp_acesso) as data,
AVG(tempo_resposta) as tempo_medio
FROM logs_acesso_hdfs
WHERE timestamp_acesso > current_date - interval '30 days'
GROUP BY DATE(timestamp_acesso);multicorn2: Criando Wrappers Personalizados em Python
O multicorn2 é uma ferramenta meta que permite desenvolver Foreign Data Wrappers customizados usando Python ao invés de C. Isso democratiza a criação de integrações, pois Python é muito mais acessível que programação em C de baixo nível. Com multicorn2, é possível criar wrappers para APIs REST, planilhas Google Sheets, bancos NoSQL não suportados oficialmente ou qualquer fonte de dados imaginável.
A implementação envolve criar uma classe Python que herda de ForeignDataWrapper e implementa métodos como execute para leitura e opcionalmente insert, update, delete para escrita. O código Python tem acesso completo a qualquer biblioteca disponível no ambiente, desde requests para HTTP até pandas para manipulação de dados. Esta flexibilidade torna multicorn2 a escolha preferida para prototipagem rápida e integrações não convencionais.
# Instalacao do Multicorn2
dnf install multicorn2_18-- Ativacao da extensao
CREATE EXTENSION multicorn;
-- Wrapper customizado para API REST (arquivo Python deve existir)
CREATE SERVER api_rest_server
FOREIGN DATA WRAPPER multicorn
OPTIONS (
wrapper 'multicorn.requests.RequestsFdw',
url 'https://api.exemplo.com/usuarios'
);
-- Tabela que consulta API automaticamente
CREATE FOREIGN TABLE usuarios_api (
id INTEGER,
nome TEXT,
email TEXT,
ativo BOOLEAN
) SERVER api_rest_server;
-- Consulta transparente executa requisicao HTTP
SELECT nome, email FROM usuarios_api WHERE ativo = true;# Exemplo de wrapper Python customizado
# Arquivo: /usr/local/lib/python3.9/site-packages/meu_wrapper.py
from multicorn import ForeignDataWrapper
class CsvHttpFdw(ForeignDataWrapper):
"""Wrapper que le CSV de URL HTTP"""
def __init__(self, options, columns):
super(CsvHttpFdw, self).__init__(options, columns)
self.url = options['url']
self.columns = columns
def execute(self, quals, columns):
"""Executa requisicao e retorna linhas"""
import requests
import csv
response = requests.get(self.url)
lines = response.text.split('\n')
reader = csv.DictReader(lines)
for row in reader:
yield row # Retorna cada linha como dicionarioFerramentas Essenciais para Administradores de Banco de Dados
Administradores de banco de dados enfrentam desafios diários que vão desde otimização de performance até manutenção preventiva e recuperação de desastres. As extensões de ferramentas de DBA automatizam tarefas repetitivas, fornecem insights profundos sobre o comportamento do sistema e permitem intervenções cirúrgicas sem interrupção de serviço. Essas ferramentas são a diferença entre um ambiente reativo que apaga incêndios constantemente e um ambiente proativo que previne problemas antes que afetem usuários.
A categoria abrange desde reorganização física de dados até agendamento de tarefas automáticas e monitoramento granular de recursos. Muitas dessas extensões foram desenvolvidas por empresas que operam PostgreSQL em escala massiva, como Cybertec, 2ndQuadrant e contribuidores individuais da comunidade. Elas representam anos de experiência condensada em código reutilizável que resolve problemas reais encontrados em produção.
pg_repack: Reorganização Online Sem Bloqueios
A extensão pg_repack é considerada salvadora de produção por administradores experientes. Ela remove bloat (espaço desperdiçado por tuplas mortas) de tabelas e índices, restaurando a ordem física dos dados sem necessitar de bloqueios exclusivos longos. Ao contrário do comando VACUUM FULL nativo, que trava completamente a tabela durante sua execução, o pg_repack permite que aplicações continuem lendo e escrevendo normalmente.
O processo funciona criando uma cópia da tabela em segundo plano, aplicando todas as modificações que ocorrem durante a cópia através de triggers temporários, e finalmente trocando a tabela antiga pela nova em uma operação atômica rápida. Isso torna viável reorganizar tabelas de terabytes em sistemas que não podem parar. A ferramenta também reordena fisicamente os dados segundo um índice específico, melhorando drasticamente a performance de consultas sequenciais comuns.
# Instalacao da extensao
dnf install pg_repack_18
# Uso via linha de comando (nao via SQL)
# Reorganizar tabela especifica
pg_repack -t usuarios -d producao
# Reorganizar ordenando por indice
pg_repack -t vendas -o indice_data -d producao
# Reorganizar apenas indices da tabela
pg_repack -t logs --only-indexes -d producao
# Reorganizar banco inteiro (cuidado)
pg_repack -d producao-- A extensao precisa ser instalada no banco
CREATE EXTENSION pg_repack;
-- Verificar bloat antes da reorganizacao
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as tamanho,
round(100 * (pg_total_relation_size(schemaname||'.'||tablename) -
pg_relation_size(schemaname||'.'||tablename)) /
NULLIF(pg_total_relation_size(schemaname||'.'||tablename), 0), 2) as pct_bloat
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;pg_cron: Agendador de Tarefas Integrado
A extensão pg_cron traz o poder do agendador cron do Linux para dentro do PostgreSQL. Ela permite agendar comandos SQL para execução automática em horários específicos, sem necessidade de scripts externos ou ferramentas de orquestração. Isso simplifica dramaticamente a automação de tarefas como limpeza de dados antigos, geração de relatórios noturnos, refresh de views materializadas e execução de procedimentos de manutenção.
A sintaxe utiliza o formato tradicional do cron (minuto hora dia mês dia-da-semana), familiar para administradores de sistemas Unix. Os jobs são persistidos no banco de dados e sobrevivem a reinicializações. O histórico de execuções fica registrado, permitindo auditoria e troubleshooting. A extensão requer configuração no arquivo postgresql.conf incluindo pg_cron na lista shared_preload_libraries, pois funciona como um background worker.
# Instalacao da extensao
dnf install pg_cron_18-- Configuracao necessaria no postgresql.conf:
-- shared_preload_libraries = 'pg_cron'
-- cron.database_name = 'postgres'
-- Reiniciar PostgreSQL apos mudanca
-- Ativacao da extensao
CREATE EXTENSION pg_cron;
-- Limpar tabela de logs todo dia as 3h da manha
SELECT cron.schedule(
'limpeza-logs-diaria',
'0 3 * * *',
'DELETE FROM logs WHERE timestamp < current_date - interval ''90 days'''
);
-- Refresh de view materializada a cada 15 minutos
SELECT cron.schedule(
'refresh-dashboard',
'*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_vendas'
);
-- Backup automatico semanal (domingos as 2h)
SELECT cron.schedule(
'backup-semanal',
'0 2 * * 0',
'CALL realizar_backup_completo()'
);
-- Listar jobs agendados
SELECT * FROM cron.job;
-- Ver historico de execucoes
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;
-- Remover job agendado
SELECT cron.unschedule('limpeza-logs-diaria');pg_partman: Gerenciamento Automático de Partições
O pg_partman é a solução padrão para gerenciamento automatizado de partições em PostgreSQL. Tabelas particionadas são essenciais para lidar com grandes volumes de dados temporais, como logs, métricas de IoT ou transações financeiras. Porém, criar e remover partições manualmente é trabalhoso e propenso a erros. Esta extensão automatiza todo o ciclo de vida das partições, desde criação preventiva até remoção de dados antigos.
A extensão suporta particionamento por tempo (diário, semanal, mensal, anual) e por intervalo de valores seriais. Ela cria partições futuras antecipadamente, garantindo que nunca falte uma partição quando novos dados chegarem. Também gerencia a remoção ou arquivamento de partições antigas, com opções para desanexar partições (mantendo dados) ou removê-las completamente. O pg_partman integra-se perfeitamente com pg_cron para execução automática das tarefas de manutenção.
# Instalacao da extensao
dnf install pg_partman_18-- Ativacao da extensao
CREATE EXTENSION pg_partman;
-- Criar tabela pai para particionar
CREATE TABLE metricas (
id BIGSERIAL,
timestamp_coleta TIMESTAMP NOT NULL,
sensor_id INTEGER,
valor DECIMAL,
PRIMARY KEY (id, timestamp_coleta)
) PARTITION BY RANGE (timestamp_coleta);
-- Configurar particionamento automatico mensal
SELECT partman.create_parent(
p_parent_table := 'public.metricas',
p_control := 'timestamp_coleta',
p_type := 'native',
p_interval := 'monthly',
p_premake := 3 -- Criar 3 meses futuros antecipadamente
);
-- Configurar retencao (manter apenas 12 meses)
UPDATE partman.part_config
SET retention = '12 months',
retention_keep_table = false
WHERE parent_table = 'public.metricas';
-- Criar partições iniciais
SELECT partman.run_maintenance('public.metricas');
-- Agendar manutencao automatica diaria
SELECT cron.schedule(
'partman-maintenance',
'30 4 * * *',
'CALL partman.run_maintenance_proc()'
);
-- Verificar configuracao
SELECT * FROM partman.part_config;
-- Listar particoes criadas
SELECT inhrelid::regclass as particao,
pg_size_pretty(pg_total_relation_size(inhrelid)) as tamanho
FROM pg_inherits
WHERE inhparent = 'metricas'::regclass;pg_stat_kcache: Monitorando I/O e CPU por Query
A extensão pg_stat_kcache complementa o pg_stat_statements fornecendo estatísticas sobre uso real de recursos de sistema por query. Enquanto pg_stat_statements mostra apenas tempo de execução e número de chamadas, o pg_stat_kcache adiciona métricas críticas como leituras físicas de disco, escritas em disco e tempo de CPU. Isso permite identificar queries que são lentas não por má otimização SQL, mas por saturação de recursos de hardware.
A extensão é invaluável para análise de performance em ambientes de produção. Ela responde perguntas como qual query está causando mais I/O no storage, qual está consumindo mais ciclos de CPU ou qual está forçando leituras de disco ao invés de usar cache. Esses dados direcionam esforços de otimização de forma precisa, seja adicionando índices, ajustando configurações de memória ou redesenhando queries problemáticas.
# Instalacao da extensao
dnf install pg_stat_kcache_18-- Configuracao no postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'
-- Reiniciar PostgreSQL
-- Ativacao das extensoes
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;
-- Queries com maior consumo de I/O (leituras)
SELECT query,
calls,
reads,
pg_size_pretty(reads * 8192) as total_lido,
ROUND(reads::NUMERIC / calls, 2) as leituras_por_chamada
FROM pg_stat_kcache
JOIN pg_stat_statements USING (queryid)
ORDER BY reads DESC
LIMIT 10;
-- Queries com maior uso de CPU
SELECT query,
calls,
user_time + system_time as cpu_time_total,
ROUND((user_time + system_time) / calls, 4) as cpu_por_chamada
FROM pg_stat_kcache
JOIN pg_stat_statements USING (queryid)
ORDER BY cpu_time_total DESC
LIMIT 10;
-- Queries que mais escrevem em disco
SELECT query,
writes,
pg_size_pretty(writes * 8192) as total_escrito
FROM pg_stat_kcache
JOIN pg_stat_statements USING (queryid)
WHERE writes > 0
ORDER BY writes DESC
LIMIT 10;
-- Resetar estatisticas
SELECT pg_stat_statements_reset();
SELECT pg_stat_kcache_reset();pg_wait_sampling: Diagnosticando Gargalos de Espera
A extensão pg_wait_sampling revela o que as queries estão esperando quando não estão executando ativamente. Queries lentas nem sempre são causadas por planos de execução ruins. Muitas vezes, elas passam a maior parte do tempo aguardando recursos como bloqueios de linha, leitura de disco ou sincronização de checkpoints. Esta extensão coleta amostras dos eventos de espera de cada processo, criando um perfil estatístico detalhado.
A análise de eventos de espera é metodologia consagrada em bancos de dados Oracle, e o pg_wait_sampling traz essa capacidade para PostgreSQL. Os dados coletados mostram se a lentidão está relacionada a Lock (contenção por concorrência), IO (disco lento), CPU (processamento pesado) ou Network (latência de rede em ambientes distribuídos). Isso direciona ações corretivas precisas, como ajustar configurações de checkpoint, adicionar índices ou redesenhar lógica de transações.
# Instalacao da extensao
dnf install pg_wait_sampling_18-- Configuracao no postgresql.conf:
-- shared_preload_libraries = 'pg_wait_sampling'
-- Reiniciar PostgreSQL
-- Ativacao da extensao
CREATE EXTENSION pg_wait_sampling;
-- Ver eventos de espera em tempo real
SELECT pid,
wait_event_type,
wait_event,
query
FROM pg_wait_sampling_current
WHERE wait_event IS NOT NULL;
-- Historico de eventos de espera mais frequentes
SELECT wait_event_type,
wait_event,
count(*) as ocorrencias,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) as percentual
FROM pg_wait_sampling_history
GROUP BY wait_event_type, wait_event
ORDER BY ocorrencias DESC
LIMIT 15;
-- Queries que mais esperaram por locks
SELECT queryid,
count(*) as vezes_esperando_lock
FROM pg_wait_sampling_history
WHERE wait_event_type = 'Lock'
GROUP BY queryid
ORDER BY vezes_esperando_lock DESC;
-- Perfil de espera de query especifica
SELECT wait_event_type,
wait_event,
count(*) as amostras
FROM pg_wait_sampling_history
WHERE queryid = 1234567890
GROUP BY wait_event_type, wait_event;pg_hint_plan: Forçando Planos de Execução Específicos
O pg_hint_plan traz para PostgreSQL o conceito de hints do Oracle, permitindo que desenvolvedores forcem o otimizador a usar estratégias específicas de execução. Normalmente, o planejador de queries do PostgreSQL escolhe automaticamente o melhor plano baseado em estatísticas. Porém, ocasionalmente suas estatísticas estão desatualizadas ou o otimizador não consegue prever padrões de dados complexos, gerando planos subótimos.
A extensão permite inserir hints através de comentários SQL especiais que instruem o planejador a usar índice específico, método de join preferencial ou ordem de tabelas. Isso é útil para queries críticas de produção onde performance previsível é mais importante que otimização automática. Os hints são especificados como comentários de bloco iniciados com caracteres especiais, sendo ignorados quando a extensão não está instalada, mantendo compatibilidade.
# Instalacao da extensao
dnf install pg_hint_plan_18-- Ativacao da extensao
CREATE EXTENSION pg_hint_plan;
-- Forcar uso de indice especifico
/*+ IndexScan(clientes idx_clientes_email) */
SELECT * FROM clientes WHERE email LIKE '%@gmail.com';
-- Forcar Nested Loop Join
/*+ NestLoop(pedidos produtos) */
SELECT p.nome, pr.descricao
FROM pedidos p
JOIN produtos pr ON p.produto_id = pr.id;
-- Forcar ordem de join de 3 tabelas
/*+ Leading((clientes pedidos) itens) */
SELECT c.nome, COUNT(i.id)
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN itens_pedido i ON p.id = i.pedido_id
GROUP BY c.nome;
-- Desabilitar Seq Scan em tabela grande
/*+ NoSeqScan(vendas) */
SELECT * FROM vendas WHERE data > '2025-01-01';
-- Multiplos hints na mesma query
/*+
HashJoin(a b)
IndexScan(a idx_a_data)
SeqScan(c)
*/
SELECT * FROM tabela_a a
JOIN tabela_b b ON a.id = b.a_id
JOIN tabela_c c ON b.id = c.b_id;
-- Ver plano com hint aplicado
EXPLAIN /*+ IndexScan(usuarios idx_usuarios_cidade) */
SELECT * FROM usuarios WHERE cidade = 'São Paulo';hypopg: Testando Índices Hipotéticos Sem Criá-los
A extensão hypopg permite criar índices hipotéticos que existem apenas na memória do planejador de queries. Isso resolve um problema comum: antes de criar um índice grande (que pode levar horas e consumir dezenas de gigabytes), administradores querem ter certeza de que ele realmente melhorará a performance. Com hypopg, é possível testar o índice usando EXPLAIN sem gastar tempo ou espaço em disco construindo-o fisicamente.
O processo é simples: cria-se um índice hipotético, executa-se EXPLAIN em queries relevantes para ver se o planejador escolheria usar esse índice, e depois remove-se o índice hipotético. Se os testes mostram melhoria significativa, o índice real pode ser criado com confiança. Se não mostram benefício, economiza-se horas de construção de índice inútil. A extensão é especialmente valiosa em ambientes de produção onde janelas de manutenção são limitadas.
# Instalacao da extensao
dnf install hypopg_18-- Ativacao da extensao
CREATE EXTENSION hypopg;
-- Criar indice hipotetico em coluna especifica
SELECT hypopg_create_index('CREATE INDEX ON pedidos (data_pedido)');
-- Listar indices hipoteticos criados
SELECT * FROM hypopg_list_indexes();
-- Testar se query usaria o indice hipotetico
EXPLAIN SELECT * FROM pedidos
WHERE data_pedido BETWEEN '2025-01-01' AND '2025-01-31';
-- Criar indice composto hipotetico
SELECT hypopg_create_index(
'CREATE INDEX ON vendas (cliente_id, data_venda)'
);
-- Testar indice parcial hipotetico
SELECT hypopg_create_index(
'CREATE INDEX ON usuarios (email) WHERE ativo = true'
);
EXPLAIN SELECT * FROM usuarios WHERE email = 'teste@email.com' AND ativo = true;
-- Remover indice hipotetico especifico
SELECT hypopg_drop_index(12345); -- ID retornado ao criar
-- Remover todos indices hipoteticos
SELECT hypopg_reset();
-- Exemplo completo de teste
-- 1. Criar indice hipotetico
SELECT hypopg_create_index('CREATE INDEX ON logs (timestamp, nivel)');
-- 2. Ver plano antes (sem indice real)
EXPLAIN ANALYZE
SELECT COUNT(*) FROM logs
WHERE timestamp > current_date - interval '7 days'
AND nivel = 'ERROR';
-- 3. Se beneficio confirmado, criar indice real
CREATE INDEX idx_logs_timestamp_nivel ON logs (timestamp, nivel);
-- 4. Limpar hipoteticos
SELECT hypopg_reset();Ferramentas Essenciais para Desenvolvedores de Aplicações
Desenvolvedores modernos esperam que bancos de dados sejam mais que simples repositórios de dados. Eles precisam de capacidades como fazer requisições HTTP para APIs externas, gerar dados de teste realistas e processar filas de mensagens diretamente em SQL. As extensões desta categoria transformam PostgreSQL em uma plataforma de desenvolvimento completa, reduzindo a necessidade de código externo para tarefas comuns e mantendo a lógica de negócio próxima aos dados.
Essas ferramentas são particularmente valiosas em arquiteturas serverless e edge computing, onde minimizar dependências externas reduz latência e complexidade. Um trigger que chama webhook HTTP diretamente do banco de dados é mais simples e confiável que um sistema de mensageria intermediário. Dados de teste gerados por funções SQL são mais consistentes que scripts externos que podem ficar desatualizados.
pg_net: Requisições HTTP Assíncronas do Banco de Dados
A extensão pg_net representa a evolução moderna das capacidades HTTP no PostgreSQL. Diferente de outras extensões HTTP que bloqueiam a transação enquanto aguardam resposta, o pg_net executa requisições de forma assíncrona. Isso significa que a função SQL inicia a requisição HTTP e retorna imediatamente, sem esperar pela resposta. O resultado fica disponível posteriormente através de uma tabela de status.
Este comportamento assíncrono é crucial para integração com webhooks, APIs de pagamento e serviços externos que podem ter alta latência. Um trigger que notifica serviço externo sobre nova venda não deve bloquear o INSERT por segundos aguardando confirmação HTTP. Com pg_net, o INSERT completa instantaneamente e a notificação HTTP processa em background. A extensão também suporta HTTPS, autenticação e headers customizados, cobrindo cenários reais de integração de APIs.
# Instalacao da extensao
dnf install pg_net_18-- Ativacao da extensao
CREATE EXTENSION pg_net;
-- Fazer requisicao GET simples assincrona
SELECT net.http_get(
url := 'https://api.exemplo.com/status'
) as request_id;
-- Requisicao POST com JSON
SELECT net.http_post(
url := 'https://api.exemplo.com/webhook',
headers := '{"Content-Type": "application/json"}'::jsonb,
body := '{"evento": "nova_venda", "valor": 150.00}'::jsonb
) as request_id;
-- Verificar status de requisicao
SELECT * FROM net.http_request_queue
WHERE id = 'uuid-da-requisicao';
-- Trigger que notifica API externa em nova venda
CREATE OR REPLACE FUNCTION notificar_venda()
RETURNS TRIGGER AS $$
BEGIN
PERFORM net.http_post(
url := 'https://webhook.site/token',
headers := '{"Content-Type": "application/json"}'::jsonb,
body := jsonb_build_object(
'venda_id', NEW.id,
'cliente', NEW.cliente_nome,
'valor', NEW.valor_total,
'timestamp', NEW.criado_em
)
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_notificar_venda
AFTER INSERT ON vendas
FOR EACH ROW
EXECUTE FUNCTION notificar_venda();
-- Requisicao com autenticacao Bearer
SELECT net.http_get(
url := 'https://api.protegida.com/dados',
headers := jsonb_build_object(
'Authorization', 'Bearer ' || 'seu-token-jwt-aqui'
)
);
-- Ver historico de requisicoes
SELECT id,
url,
method,
created_at,
status_code,
content_type
FROM net.http_request_queue
ORDER BY created_at DESC
LIMIT 20;pgsql_http: Cliente HTTP Síncrono Clássico
O pgsql_http é a extensão HTTP original para PostgreSQL, funcionando de forma síncrona. Quando uma função SQL executa uma requisição HTTP, a query espera até receber a resposta completa antes de continuar. Isso é apropriado para casos onde o resultado da requisição é necessário imediatamente na mesma transação, como validar cupom de desconto em API externa antes de finalizar pedido.
A extensão suporta todos os métodos HTTP padrão (GET, POST, PUT, DELETE, PATCH) e retorna a resposta completa incluindo código de status, headers e corpo. É possível fazer scraping de páginas web, consultar APIs RESTful públicas ou integrar com serviços internos da empresa. A simplicidade do modelo síncrono facilita o desenvolvimento, pois o fluxo de execução é linear e previsível, sem necessidade de gerenciar callbacks ou polling de status.
# Instalacao da extensao
dnf install pgsql_http_18-- Ativacao da extensao
CREATE EXTENSION http;
-- Requisicao GET simples
SELECT content::json
FROM http_get('https://api.exchangerate-api.com/v4/latest/USD');
-- Extrair taxa de cambio especifica
SELECT (content::json -> 'rates' ->> 'BRL')::numeric as taxa_usd_brl
FROM http_get('https://api.exchangerate-api.com/v4/latest/USD');
-- POST com dados JSON
SELECT status, content
FROM http_post(
'https://httpbin.org/post',
'{"nome": "Paulo", "idade": 30}',
'application/json'
);
-- Requisicao com headers customizados
SELECT content
FROM http((
'GET',
'https://api.github.com/users/postgres',
ARRAY[http_header('User-Agent', 'PostgreSQL http client')],
NULL,
NULL
)::http_request);
-- Funcao que valida CEP usando API ViaCEP
CREATE OR REPLACE FUNCTION buscar_endereco_cep(cep_input TEXT)
RETURNS TABLE(logradouro TEXT, bairro TEXT, cidade TEXT, uf TEXT) AS $$
DECLARE
resposta http_response;
dados json;
BEGIN
-- Fazer requisicao para API ViaCEP
SELECT * INTO resposta
FROM http_get('https://viacep.com.br/ws/' || cep_input || '/json/');
-- Verificar sucesso
IF resposta.status != 200 THEN
RAISE EXCEPTION 'Erro ao buscar CEP: status %', resposta.status;
END IF;
-- Parsear JSON
dados := resposta.content::json;
-- Retornar dados
RETURN QUERY SELECT
dados->>'logradouro',
dados->>'bairro',
dados->>'localidade',
dados->>'uf';
END;
$$ LANGUAGE plpgsql;
-- Usar funcao de validacao de CEP
SELECT * FROM buscar_endereco_cep('01310-100');
-- Trigger que enriquece endereco com dados de API
CREATE OR REPLACE FUNCTION preencher_endereco_automatico()
RETURNS TRIGGER AS $$
DECLARE
dados_endereco RECORD;
BEGIN
IF NEW.cep IS NOT NULL AND NEW.logradouro IS NULL THEN
SELECT * INTO dados_endereco FROM buscar_endereco_cep(NEW.cep);
NEW.logradouro := dados_endereco.logradouro;
NEW.bairro := dados_endereco.bairro;
NEW.cidade := dados_endereco.cidade;
NEW.uf := dados_endereco.uf;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_preencher_endereco
BEFORE INSERT OR UPDATE ON clientes
FOR EACH ROW
EXECUTE FUNCTION preencher_endereco_automatico();postgresql_faker: Gerando Dados de Teste Realistas
A extensão postgresql_faker gera dados fictícios mas realistas para testes, desenvolvimento e demonstrações. Ela fornece funções que retornam nomes próprios, endereços, emails, números de telefone, textos lorem ipsum e dezenas de outros tipos de dados. Os valores gerados seguem padrões convincentes, como nomes brasileiros reais, CEPs válidos e emails com domínios comuns.
Dados de teste realistas são cruciais para identificar bugs que só aparecem com dados do mundo real, treinar equipes em ambientes seguros sem expor informações sensíveis e criar demonstrações convincentes para clientes. A extensão suporta localização, gerando dados apropriados para diferentes países e culturas. É possível popular rapidamente bancos de desenvolvimento com milhões de registros que se parecem com produção, permitindo testes de performance realistas.
# Instalacao da extensao
dnf install postgresql_faker_18-- Ativacao da extensao
CREATE EXTENSION faker;
-- Gerar nomes de pessoas
SELECT faker.first_name() as nome,
faker.last_name() as sobrenome,
faker.email() as email;
-- Gerar enderecos completos
SELECT faker.street_address() as rua,
faker.city() as cidade,
faker.state_abbr() as uf,
faker.zipcode() as cep;
-- Gerar dados de empresa
SELECT faker.company() as empresa,
faker.catch_phrase() as slogan,
faker.phone_number() as telefone;
-- Popular tabela de clientes com dados ficticios
INSERT INTO clientes_teste (nome, email, telefone, cidade)
SELECT
faker.first_name() || ' ' || faker.last_name(),
faker.email(),
faker.phone_number(),
faker.city()
FROM generate_series(1, 1000);
-- Gerar textos lorem ipsum
SELECT faker.paragraph(5) as descricao_produto;
-- Gerar datas aleatorias
SELECT faker.date_between(
'2024-01-01'::date,
'2025-12-31'::date
) as data_venda;
-- Criar conjunto completo de dados de teste
CREATE TABLE usuarios_teste AS
SELECT
generate_series(1, 10000) as id,
faker.first_name() as nome,
faker.last_name() as sobrenome,
faker.email() as email,
faker.phone_number() as telefone,
faker.street_address() as endereco,
faker.city() as cidade,
faker.state_abbr() as estado,
faker.zipcode() as cep,
faker.date_between('2020-01-01', current_date) as cadastrado_em,
(random() > 0.3) as ativo;
-- Gerar dados financeiros
SELECT
faker.credit_card_number() as numero_cartao,
faker.currency_code() as moeda,
(random() * 10000)::decimal(10,2) as valor;PgQ: Sistema de Filas para Processamento em Lote
A extensão PgQ implementa um sistema de filas genérico e de alta performance diretamente no PostgreSQL. Desenvolvida originalmente pelo Skype para processar bilhões de eventos, ela fornece uma API baseada em funções SQL para produzir e consumir mensagens de forma confiável. A arquitetura é lockless, permitindo múltiplos produtores e consumidores operarem simultaneamente sem bloqueios significativos.
Casos de uso típicos incluem processamento assíncrono de tarefas pesadas, integração entre sistemas através de eventos e implementação de padrões Event Sourcing. A fila garante entrega at-least-once, onde cada mensagem é processada pelo menos uma vez, mas consumidores devem implementar idempotência para lidar com possíveis reprocessamentos. O PgQ mantém histórico de eventos processados, facilitando auditoria e reprocessamento quando necessário.
# Instalacao da extensao
dnf install pgq_18-- Ativacao da extensao
CREATE EXTENSION pgq;
-- Criar fila de eventos
SELECT pgq.create_queue('fila_emails');
-- Registrar consumidor na fila
SELECT pgq.register_consumer('fila_emails', 'processador_emails');
-- Funcao produtora: adicionar evento na fila
CREATE OR REPLACE FUNCTION enfileirar_email(
destinatario TEXT,
assunto TEXT,
corpo TEXT
) RETURNS BIGINT AS $$
BEGIN
RETURN pgq.insert_event(
'fila_emails',
'enviar_email',
jsonb_build_object(
'destinatario', destinatario,
'assunto', assunto,
'corpo', corpo,
'timestamp', now()
)::text
);
END;
$$ LANGUAGE plpgsql;
-- Adicionar eventos na fila
SELECT enfileirar_email(
'cliente@exemplo.com',
'Bem-vindo!',
'Obrigado por se cadastrar em nossa plataforma.'
);
-- Funcao consumidora: processar batch de eventos
CREATE OR REPLACE FUNCTION processar_batch_emails()
RETURNS INTEGER AS $$
DECLARE
batch_id BIGINT;
evento RECORD;
eventos_processados INTEGER := 0;
dados JSONB;
BEGIN
-- Obter proximo batch de eventos
SELECT pgq.next_batch('fila_emails', 'processador_emails') INTO batch_id;
IF batch_id IS NULL THEN
RETURN 0; -- Sem eventos pendentes
END IF;
-- Processar cada evento do batch
FOR evento IN
SELECT * FROM pgq.get_batch_events(batch_id)
LOOP
dados := evento.ev_data::jsonb;
-- Logica de processamento (enviar email real aqui)
RAISE NOTICE 'Enviando email para: %', dados->>'destinatario';
eventos_processados := eventos_processados + 1;
END LOOP;
-- Confirmar processamento do batch
PERFORM pgq.finish_batch(batch_id);
RETURN eventos_processados;
END;
$$ LANGUAGE plpgsql;
-- Executar processamento (pode ser agendado com pg_cron)
SELECT processar_batch_emails();
-- Monitorar status das filas
SELECT queue_name,
queue_cur_table,
queue_ntables,
queue_ticker_max_count
FROM pgq.queue;
-- Ver consumidores registrados
SELECT * FROM pgq.consumer;
-- Estatisticas de consumo
SELECT * FROM pgq.get_consumer_info('fila_emails');pgmemcache: Integração com Memcached
A extensão pgmemcache conecta PostgreSQL ao Memcached, sistema de cache distribuído em memória amplamente utilizado. Ela fornece funções SQL para definir, obter e deletar chaves no Memcached, permitindo que triggers do banco de dados invalidem caches automaticamente quando dados mudam. Isso garante consistência entre banco de dados e camada de cache sem necessidade de lógica complexa na aplicação.
A integração direta com Memcached dentro do banco de dados simplifica arquiteturas de sistemas. Um trigger pode invalidar cache de produto imediatamente após atualização de preço, garantindo que aplicações sempre busquem dados atualizados. Stored procedures podem verificar cache antes de executar queries pesadas, implementando estratégias de cache em nível de banco de dados. A extensão suporta múltiplos servidores Memcached para redundância e distribuição de carga.
# Instalacao da extensao
dnf install pgmemcache_18-- Ativacao da extensao
CREATE EXTENSION pgmemcache;
-- Configurar servidores Memcached
SELECT memcache_server_add('localhost:11211');
-- Adicionar multiplos servidores para distribuicao
SELECT memcache_server_add('cache1.exemplo.com:11211');
SELECT memcache_server_add('cache2.exemplo.com:11211');
-- Armazenar valor no cache (chave, valor, expiracao em segundos)
SELECT memcache_set('produto:123', 'Notebook Dell XPS', 3600);
-- Recuperar valor do cache
SELECT memcache_get('produto:123');
-- Deletar chave do cache
SELECT memcache_delete('produto:123');
-- Funcao que busca com cache
CREATE OR REPLACE FUNCTION buscar_produto_com_cache(produto_id INTEGER)
RETURNS TEXT AS $$
DECLARE
chave_cache TEXT := 'produto:' || produto_id;
valor_cache TEXT;
nome_produto TEXT;
BEGIN
-- Tentar buscar do cache primeiro
valor_cache := memcache_get(chave_cache);
IF valor_cache IS NOT NULL THEN
RETURN valor_cache; -- Cache hit
END IF;
-- Cache miss: buscar do banco
SELECT nome INTO nome_produto
FROM produtos
WHERE id = produto_id;
-- Armazenar no cache por 1 hora
IF nome_produto IS NOT NULL THEN
PERFORM memcache_set(chave_cache, nome_produto, 3600);
END IF;
RETURN nome_produto;
END;
$$ LANGUAGE plpgsql;
-- Trigger para invalidar cache quando produto muda
CREATE OR REPLACE FUNCTION invalidar_cache_produto()
RETURNS TRIGGER AS $$
BEGIN
PERFORM memcache_delete('produto:' || NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_invalidar_cache
AFTER UPDATE ON produtos
FOR EACH ROW
EXECUTE FUNCTION invalidar_cache_produto();
-- Armazenar objetos JSON complexos
SELECT memcache_set(
'carrinho:usuario:456',
jsonb_build_object(
'usuario_id', 456,
'itens', jsonb_build_array(
jsonb_build_object('produto_id', 123, 'quantidade', 2),
jsonb_build_object('produto_id', 789, 'quantidade', 1)
),
'total', 350.00
)::text,
1800 -- 30 minutos
);
-- Incrementar contador no cache
SELECT memcache_incr('visualizacoes:produto:123', 1);Tipos de Dados Especializados e Funções Extras
Os tipos de dados nativos do PostgreSQL cobrem a maioria das necessidades, mas cenários especializados exigem tipos customizados. Armazenar endereços IP como texto desperdiça espaço e torna buscas de ranges ineficientes. UUIDs padrão causam fragmentação de índices. Números de versão armazenados como texto não ordenam corretamente. As extensões desta categoria adicionam tipos especializados que resolvem esses problemas com validação nativa e operadores otimizados.
Tipos de dados customizados não apenas economizam espaço de armazenamento, mas também previnem erros de validação e melhoram drasticamente a performance de consultas. Um tipo inet otimizado com operadores de range permite consultas como "encontre todos IPs nesta subnet" usando índices eficientemente. Um tipo semver garante que versões sejam comparadas corretamente, evitando bugs onde "1.10.0" seria considerado menor que "1.2.0" em comparação alfabética.
ip4r: Tipos Otimizados para Endereços IP
A extensão ip4r fornece tipos de dados especializados para armazenar e consultar endereços IPv4 e IPv6 e ranges de IPs. Enquanto o PostgreSQL possui o tipo nativo inet, o ip4r oferece performance superior especialmente para consultas de pertencimento a ranges, como "este IP está nesta subnet". Os tipos são armazenados de forma compacta e suportam índices GiST que aceleram dramaticamente buscas de ranges.
Casos de uso incluem sistemas de firewall que precisam consultar rapidamente se IP está em lista de bloqueio, análise de logs web identificando origem geográfica de acessos por faixas de IP e gerenciamento de alocação de IPs em provedores de internet. A extensão fornece operadores intuitivos para testar contenção, sobreposição e adjacência de ranges, tornando queries complexas de redes simples de escrever.
# Instalacao da extensao
dnf install ip4r_18-- Ativacao da extensao
CREATE EXTENSION ip4r;
-- Criar tabela com tipos ip4r
CREATE TABLE bloqueios_firewall (
id SERIAL PRIMARY KEY,
ip_range ip4r NOT NULL,
motivo TEXT,
bloqueado_em TIMESTAMP DEFAULT now()
);
-- Inserir ranges de IPs bloqueados
INSERT INTO bloqueios_firewall (ip_range, motivo) VALUES
('192.168.1.0/24'::ip4r, 'Rede interna de teste'),
('10.0.0.0-10.255.255.255'::ip4r, 'Range privado completo'),
('203.0.113.0/24'::ip4r, 'Bloqueio de spam detectado');
-- Criar indice GiST para buscas rapidas
CREATE INDEX idx_bloqueios_ip ON bloqueios_firewall USING gist(ip_range);
-- Verificar se IP especifico esta bloqueado
SELECT motivo, bloqueado_em
FROM bloqueios_firewall
WHERE '192.168.1.50'::ip4 <<= ip_range;
-- Funcao para validar se IP pode acessar
CREATE OR REPLACE FUNCTION ip_permitido(ip_teste TEXT)
RETURNS BOOLEAN AS $$
BEGIN
RETURN NOT EXISTS (
SELECT 1 FROM bloqueios_firewall
WHERE ip_teste::ip4 <<= ip_range
);
END;
$$ LANGUAGE plpgsql;
-- Testar funcao
SELECT ip_permitido('8.8.8.8'); -- true (Google DNS nao bloqueado)
SELECT ip_permitido('192.168.1.100'); -- false (rede interna bloqueada)
-- Encontrar ranges sobrepostos
SELECT a.ip_range as range1, b.ip_range as range2
FROM bloqueios_firewall a
JOIN bloqueios_firewall b ON a.id < b.id
WHERE a.ip_range && b.ip_range; -- Operador de sobreposicao
-- Contar IPs em um range
SELECT ip_range,
upper(ip_range) - lower(ip_range) as total_ips
FROM bloqueios_firewall;
-- Trabalhar com IPv6
CREATE TABLE alocacoes_ipv6 (
cliente_id INTEGER,
ip_range ip6r NOT NULL,
alocado_em TIMESTAMP DEFAULT now()
);
INSERT INTO alocacoes_ipv6 (cliente_id, ip_range) VALUES
(1, '2001:db8::/32'::ip6r),
(2, '2001:db8:1::/48'::ip6r);
-- Buscar alocacao de IPv6 especifico
SELECT cliente_id, ip_range
FROM alocacoes_ipv6
WHERE '2001:db8:1::1'::ip6 <<= ip_range;pg_uuidv7: UUIDs Ordenáveis por Tempo
A extensão pg_uuidv7 gera identificadores únicos universais versão 7, que incorporam timestamp em sua estrutura. Diferente do UUID v4 padrão que é completamente aleatório, o UUID v7 tem seus primeiros bytes baseados em timestamp, fazendo com que UUIDs gerados sequencialmente sejam ordenáveis. Isso resolve um problema crítico de performance: UUIDs aleatórios causam fragmentação severa em índices B-Tree.
Quando UUIDs aleatórios são usados como chave primária, cada inserção acontece em posição aleatória do índice, causando splits de página frequentes e degradação de performance. UUID v7 garante que novas inserções aconteçam sempre no final do índice, mantendo a performance similar a chaves seriais incrementais. Ao mesmo tempo, preserva as vantagens dos UUIDs: são globalmente únicos, não revelam informações sobre volume de dados e funcionam perfeitamente em sistemas distribuídos.
# Instalacao da extensao
dnf install postgresql16-pg-uuidv7_18-- Ativacao da extensao
CREATE EXTENSION pg_uuidv7;
-- Gerar UUID v7 ordenavel
SELECT uuid_generate_v7();
-- Criar tabela usando UUID v7 como chave primaria
CREATE TABLE pedidos (
id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
cliente_id INTEGER NOT NULL,
valor_total DECIMAL(10,2),
criado_em TIMESTAMP DEFAULT now()
);
-- Inserir registros (IDs serao ordenados por tempo de criacao)
INSERT INTO pedidos (cliente_id, valor_total) VALUES
(123, 150.00),
(456, 280.50),
(789, 95.00);
-- Verificar que UUIDs sao ordenados cronologicamente
SELECT id, cliente_id, criado_em
FROM pedidos
ORDER BY id; -- IDs ordenam cronologicamente
-- Comparacao: UUID v4 aleatorio vs UUID v7 ordenavel
SELECT
gen_random_uuid() as uuid_v4_aleatorio,
uuid_generate_v7() as uuid_v7_ordenavel;
-- Extrair timestamp de UUID v7
CREATE OR REPLACE FUNCTION uuid_v7_to_timestamp(uuid_v7 UUID)
RETURNS TIMESTAMP AS $$
BEGIN
-- UUID v7 armazena timestamp Unix em milissegundos nos primeiros 48 bits
RETURN to_timestamp(
(('x' || substring(uuid_v7::text, 1, 8) ||
substring(uuid_v7::text, 10, 4))::bit(48)::bigint / 1000.0)
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Usar funcao para extrair timestamp
SELECT id,
uuid_v7_to_timestamp(id) as timestamp_criacao,
criado_em
FROM pedidos;
-- Indice mantem ordem sequencial (sem fragmentacao)
CREATE INDEX idx_pedidos_id ON pedidos(id);
-- Analisar estatisticas de bloat (deve ser minimo)
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as tamanho_total,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as tamanho_tabela,
pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as tamanho_indices
FROM pg_tables
WHERE tablename = 'pedidos';semver: Versionamento Semântico Nativo
A extensão semver implementa o padrão Semantic Versioning 2.0.0 como tipo de dado nativo. Ela permite armazenar números de versão (como "2.1.3-beta.1") e compará-los corretamente seguindo as regras semânticas. Armazenar versões como texto causa ordenação incorreta, onde "1.10.0" seria considerado menor que "1.2.0" alfabeticamente. O tipo semver resolve isso implementando comparação correta.
Casos de uso incluem gerenciamento de releases de software, versionamento de APIs e controle de dependências em sistemas de pacotes. A extensão valida automaticamente que versões seguem o formato correto (major.minor.patch) e suporta sufixos de pre-release e metadados. Operadores permitem verificar compatibilidade (major.minor iguais indicam compatibilidade de API) e ordenar versões cronologicamente de forma precisa.
# Instalacao da extensao
dnf install postgresql16-semver_18-- Ativacao da extensao
CREATE EXTENSION semver;
-- Criar tabela de releases de software
CREATE TABLE releases_aplicacao (
id SERIAL PRIMARY KEY,
versao semver NOT NULL UNIQUE,
descricao TEXT,
lancado_em DATE DEFAULT current_date,
estavel BOOLEAN DEFAULT true
);
-- Inserir versoes (validacao automatica do formato)
INSERT INTO releases_aplicacao (versao, descricao, estavel) VALUES
('1.0.0'::semver, 'Versao inicial', true),
('1.1.0'::semver, 'Adicao de novos recursos', true),
('1.1.1'::semver, 'Correcao de bugs', true),
('1.2.0-beta.1'::semver, 'Versao beta', false),
('1.2.0'::semver, 'Release estavel', true),
('2.0.0'::semver, 'Breaking changes', true);
-- Ordenacao correta de versoes
SELECT versao, descricao
FROM releases_aplicacao
ORDER BY versao;
-- Resultado: 1.0.0, 1.1.0, 1.1.1, 1.2.0-beta.1, 1.2.0, 2.0.0
-- Comparacao de versoes
SELECT '2.1.0'::semver > '1.10.5'::semver; -- true
SELECT '1.0.0-beta'::semver < '1.0.0'::semver; -- true (pre-release < release)
-- Encontrar versao mais recente
SELECT versao
FROM releases_aplicacao
WHERE estavel = true
ORDER BY versao DESC
LIMIT 1;
-- Verificar se versao e compativel (mesmo major.minor)
CREATE OR REPLACE FUNCTION versoes_compativeis(v1 semver, v2 semver)
RETURNS BOOLEAN AS $$
BEGIN
-- Compativel se major e minor iguais (patch pode diferir)
RETURN (v1::text ~ '^(\d+)\.(\d+)') AND
(v2::text ~ '^(\d+)\.(\d+)') AND
(substring(v1::text from '^(\d+)') = substring(v2::text from '^(\d+)')) AND
(substring(v1::text from '^\d+\.(\d+)') = substring(v2::text from '^\d+\.(\d+)'));
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Testar compatibilidade
SELECT versoes_compativeis('1.2.0'::semver, '1.2.5'::semver); -- true
SELECT versoes_compativeis('1.2.0'::semver, '1.3.0'::semver); -- false
SELECT versoes_compativeis('1.2.0'::semver, '2.0.0'::semver); -- false
-- Tabela de dependencias com versoes
CREATE TABLE dependencias_pacote (
pacote TEXT NOT NULL,
depende_de TEXT NOT NULL,
versao_minima semver NOT NULL,
versao_maxima semver
);
INSERT INTO dependencias_pacote VALUES
('meu-app', 'postgresql', '12.0.0'::semver, '16.99.99'::semver),
('meu-app', 'python', '3.9.0'::semver, '3.12.99'::semver);
-- Verificar se versao instalada satisfaz dependencia
SELECT pacote, depende_de
FROM dependencias_pacote
WHERE '14.5.0'::semver >= versao_minima
AND ('14.5.0'::semver <= versao_maxima OR versao_maxima IS NULL);Replicação e Alta Disponibilidade
A replicação é fundamental para garantir alta disponibilidade e distribuir carga de leitura em ambientes de produção. O PostgreSQL oferece replicação física nativa, mas extensões como pglogical expandem dramaticamente as capacidades, permitindo replicação lógica seletiva, transformação de dados durante replicação e até topologias multi-master em casos específicos.
pglogical: Replicação Lógica Avançada
A extensão pglogical foi pioneira em trazer replicação lógica para PostgreSQL, antes mesmo do recurso ser incorporado ao core na versão 10. Ela oferece capacidades além da replicação nativa, como replicação seletiva de tabelas específicas, transformação de dados em trânsito e resolução de conflitos. A replicação lógica replica mudanças a nível de linha, não blocos de disco, permitindo que subscriber e publisher tenham esquemas diferentes.
Casos de uso incluem migração gradual entre versões maiores do PostgreSQL, consolidação de dados de múltiplas filiais em data warehouse central e distribuição de subconjuntos de dados para diferentes regiões geográficas. A extensão suporta replicação bidirecional, onde duas instâncias replicam mudanças uma para outra, útil para arquiteturas ativo-ativo com resolução automática de conflitos.
# Instalacao da extensao
dnf install pglogical_18-- Configuracao no postgresql.conf:
-- wal_level = logical
-- max_worker_processes = 10
-- max_replication_slots = 10
-- max_wal_senders = 10
-- shared_preload_libraries = 'pglogical'
-- Reiniciar PostgreSQL
-- NO SERVIDOR ORIGEM (publisher):
CREATE EXTENSION pglogical;
-- Criar node publisher
SELECT pglogical.create_node(
node_name := 'provider_principal',
dsn := 'host=192.168.1.10 port=5432 dbname=producao'
);
-- Adicionar tabelas para replicacao
SELECT pglogical.replication_set_add_table(
set_name := 'default',
relation := 'public.vendas',
synchronize_data := true
);
SELECT pglogical.replication_set_add_table(
set_name := 'default',
relation := 'public.clientes',
synchronize_data := true
);
-- NO SERVIDOR DESTINO (subscriber):
CREATE EXTENSION pglogical;
-- Criar node subscriber
SELECT pglogical.create_node(
node_name := 'subscriber_replica',
dsn := 'host=192.168.1.20 port=5432 dbname=replica'
);
-- Criar subscription conectando ao publisher
SELECT pglogical.create_subscription(
subscription_name := 'subscription_producao',
provider_dsn := 'host=192.168.1.10 port=5432 dbname=producao user=replicator password=senha',
replication_sets := ARRAY['default'],
synchronize_structure := false,
synchronize_data := true
);
-- Monitorar status da replicacao
SELECT subscription_name,
status,
slot_name,
replication_sets
FROM pglogical.show_subscription_status();
-- Ver lag de replicacao
SELECT subscription_name,
received_lsn,
last_msg_send_time,
last_msg_receipt_time,
latest_end_lsn,
latest_end_time
FROM pglogical.show_subscription_status();
-- Replicacao seletiva: apenas registros especificos
SELECT pglogical.replication_set_add_table(
set_name := 'default',
relation := 'public.logs',
synchronize_data := true,
row_filter := 'nivel = ''ERROR''' -- Replica apenas erros
);
-- Pausar e retomar replicacao
SELECT pglogical.alter_subscription_disable('subscription_producao');
SELECT pglogical.alter_subscription_enable('subscription_producao');
-- Remover subscription
SELECT pglogical.drop_subscription('subscription_producao');Linguagens Procedurais Alternativas
O PostgreSQL permite escrever funções armazenadas em diversas linguagens além do PL/pgSQL nativo. Extensões de linguagens procedurais trazem capacidades especializadas: R para análise estatística avançada, Lua para lógica leve e rápida, e até Shell scripts para integração com sistema operacional. Cada linguagem tem forças únicas que, quando combinadas com SQL, criam soluções impossíveis de implementar apenas com queries tradicionais.
A escolha da linguagem procedural depende do caso de uso. R é imbatível para ciência de dados e machine learning, com milhares de pacotes estatísticos disponíveis. Lua oferece performance excepcional para lógica de negócio complexa que seria verbosa em PL/pgSQL. Shell permite automação de tarefas de sistema diretamente de triggers, como mover arquivos ou executar backups. Ferramentas de depuração e profiling ajudam desenvolvedores a otimizar código complexo.
plpgsql_check: Linter para Código PL/pgSQL
A extensão plpgsql_check analisa estaticamente funções escritas em PL/pgSQL antes da execução, identificando erros que só apareceriam em runtime. Ela detecta problemas como variáveis não inicializadas, tipos incompatíveis em atribuições, queries SQL com erros de sintaxe, código inalcançável após RETURN e possíveis vulnerabilidades de SQL injection. É essencialmente um compilador que valida código sem executá-lo.
Usar plpgsql_check em processo de CI/CD previne deploy de código com bugs óbvios. A extensão identifica problemas que testes unitários podem perder, como branches de código que nunca são alcançadas ou variáveis declaradas mas nunca usadas. Ela também fornece warnings sobre práticas ruins, como concatenação de strings em queries dinâmicas ao invés de usar parâmetros. A análise é rápida e não modifica dados, permitindo validação segura de milhares de funções.
# Instalacao da extensao
dnf install postgresql16-plpgsql-check-- Ativacao da extensao
CREATE EXTENSION plpgsql_check;
-- Criar funcao com erros intencionais para demonstracao
CREATE OR REPLACE FUNCTION exemplo_com_erros(valor INTEGER)
RETURNS TEXT AS $$
DECLARE
resultado TEXT;
variavel_nao_usada INTEGER;
BEGIN
-- Erro: variavel resultado nao inicializada antes de concatenacao
resultado := resultado || ' teste';
-- Erro: tipo incompativel
resultado := valor;
-- Codigo inalcancavel
RETURN resultado;
RAISE NOTICE 'Isso nunca executa';
END;
$$ LANGUAGE plpgsql;
-- Verificar funcao (modo passivo, apenas verifica)
SELECT * FROM plpgsql_check_function('exemplo_com_erros(integer)');
-- Resultado mostra warnings e erros:
-- - "never read variable variavel_nao_usada"
-- - "uninitialized variable resultado"
-- - "unreachable code" (RAISE apos RETURN)
-- Funcao correta para validar
CREATE OR REPLACE FUNCTION calcular_desconto(
valor_original DECIMAL,
percentual_desconto INTEGER
) RETURNS DECIMAL AS $$
DECLARE
valor_desconto DECIMAL;
valor_final DECIMAL;
BEGIN
-- Validacao de entrada
IF percentual_desconto < 0 OR percentual_desconto > 100 THEN
RAISE EXCEPTION 'Percentual deve estar entre 0 e 100';
END IF;
-- Calculo
valor_desconto := valor_original * (percentual_desconto / 100.0);
valor_final := valor_original - valor_desconto;
RETURN valor_final;
END;
$$ LANGUAGE plpgsql;
-- Verificar funcao correta (nenhum erro deve aparecer)
SELECT * FROM plpgsql_check_function('calcular_desconto(decimal, integer)');
-- Verificar todas as funcoes do schema
SELECT p.proname as funcao,
plpgsql_check_function(p.oid) as problemas
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
AND p.prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql');
-- Modo ativo: gera erro em CREATE FUNCTION se houver problemas
-- (Configurar em postgresql.conf: plpgsql.extra_errors ou extra_warnings)
-- Verificar funcao com modo detalhado
SELECT * FROM plpgsql_check_function(
'exemplo_com_erros(integer)',
performance_warnings := true,
extra_warnings := true,
security_warnings := true
);
-- Detectar SQL injection potencial
CREATE OR REPLACE FUNCTION buscar_usuario_inseguro(nome_usuario TEXT)
RETURNS TABLE(id INTEGER, nome TEXT) AS $$
BEGIN
-- PERIGOSO: concatenacao direta (SQL injection)
RETURN QUERY EXECUTE 'SELECT id, nome FROM usuarios WHERE nome = ''' || nome_usuario || '''';
END;
$$ LANGUAGE plpgsql;
-- plpgsql_check detecta o problema
SELECT * FROM plpgsql_check_function(
'buscar_usuario_inseguro(text)',
security_warnings := true
);
-- Versao correta usando parametros
CREATE OR REPLACE FUNCTION buscar_usuario_seguro(nome_usuario TEXT)
RETURNS TABLE(id INTEGER, nome TEXT) AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT id, nome FROM usuarios WHERE nome = $1'
USING nome_usuario;
END;
$$ LANGUAGE plpgsql;plprofiler: Analisando Performance de Código
O plprofiler é um profiler de performance para funções PL/pgSQL que mostra exatamente onde o tempo de execução está sendo gasto. Ele instrumenta código linha por linha, registrando quantas vezes cada linha foi executada e quanto tempo consumiu. Os dados são visualizados em gráficos tipo flame graph, revelando instantaneamente gargalos de performance que seriam invisíveis olhando apenas tempo total de execução.
A ferramenta é essencial para otimizar stored procedures complexas. Ela identifica loops que executam queries N+1, funções chamadas excessivamente dentro de loops e branches de código que consomem tempo inesperado. O profiler funciona sem modificar código fonte, usando instrumentação dinâmica. Resultados podem ser exportados para HTML interativo, facilitando compartilhamento de análises com equipe de desenvolvimento.
# Instalacao da extensao e ferramentas
dnf install postgresql16-plprofiler_18
# Instalar ferramenta Python para visualizacao
pip3 install plprofiler-- Ativacao da extensao
CREATE EXTENSION plprofiler;
-- Criar funcao para analisar
CREATE OR REPLACE FUNCTION processar_pedidos_lote(data_inicio DATE, data_fim DATE)
RETURNS INTEGER AS $$
DECLARE
pedido RECORD;
total_processado INTEGER := 0;
valor_total DECIMAL;
BEGIN
-- Loop que pode ter problema de performance
FOR pedido IN
SELECT id, cliente_id, valor FROM pedidos
WHERE data_pedido BETWEEN data_inicio AND data_fim
LOOP
-- Query individual por pedido (potencial N+1)
SELECT SUM(quantidade * preco_unitario) INTO valor_total
FROM itens_pedido
WHERE pedido_id = pedido.id;
-- Atualizar total do pedido
UPDATE pedidos
SET valor_total = valor_total
WHERE id = pedido.id;
total_processado := total_processado + 1;
-- Simulacao de logica complexa
PERFORM pg_sleep(0.001); -- 1ms por pedido
END LOOP;
RETURN total_processado;
END;
$$ LANGUAGE plpgsql;
-- Habilitar profiling
SELECT plprofiler.enable(true);
-- Executar funcao sendo perfilada
SELECT processar_pedidos_lote('2025-01-01', '2025-01-31');
-- Desabilitar profiling
SELECT plprofiler.enable(false);
-- Ver estatisticas coletadas
SELECT * FROM plprofiler.pl_profiler_functions_src
WHERE funcname = 'processar_pedidos_lote';
-- Resetar estatisticas
SELECT plprofiler.reset();# Gerar relatorio HTML com flame graph (linha de comando)
plprofiler --database=producao --title="Analise processar_pedidos_lote" --output=relatorio.html
# O HTML mostra:
# - Tempo total por linha de codigo
# - Numero de execucoes de cada linha
# - Flame graph interativo
# - Identificacao de hot spots (linhas mais lentas)PL/R: Análise Estatística com Linguagem R
A extensão PL/R permite escrever funções usando a linguagem R, a ferramenta preferida de cientistas de dados e estatísticos. Ela disponibiliza milhares de pacotes R para análise estatística, machine learning, visualização de dados e processamento de séries temporais diretamente dentro do banco de dados. Dados não precisam ser exportados para análise externa, mantendo segurança e eliminando movimento desnecessário de grandes volumes.
Casos de uso incluem calcular regressões lineares sobre dados transacionais, detectar anomalias usando algoritmos estatísticos avançados, gerar previsões de séries temporais e treinar modelos de machine learning sobre dados históricos. Funções R podem acessar qualquer pacote instalado no servidor, como ggplot2 para gráficos, forecast para previsões ou randomForest para classificação. Resultados retornam diretamente para SQL, podendo ser armazenados ou usados em queries complexas.
# Instalacao da extensao
dnf install postgresql16-plr_18
# Instalar R e pacotes necessarios
dnf install R
R
install.packages(c("forecast", "ggplot2", "jsonlite"))
quit()-- Ativacao da extensao
CREATE EXTENSION plr;
-- Funcao simples em R: calcula media
CREATE OR REPLACE FUNCTION media_r(valores NUMERIC[])
RETURNS NUMERIC AS $$
return(mean(valores))
$$ LANGUAGE plr;
-- Usar funcao
SELECT media_r(ARRAY[10, 20, 30, 40, 50]); -- Retorna 30
-- Regressao linear em R sobre dados da tabela
CREATE OR REPLACE FUNCTION regressao_vendas()
RETURNS TABLE(coeficiente TEXT, valor NUMERIC) AS $$
# Buscar dados da tabela
query <- "SELECT dias_desde_lancamento, vendas_diarias FROM produtos_vendas"
dados <- pg.spi.exec(query)
# Executar regressao linear
modelo <- lm(vendas_diarias ~ dias_desde_lancamento, data=dados)
# Retornar coeficientes
coefs <- coefficients(modelo)
return(data.frame(
coeficiente = names(coefs),
valor = as.numeric(coefs)
))
$$ LANGUAGE plr;
-- Executar regressao
SELECT * FROM regressao_vendas();
-- Resultado:
-- coeficiente | valor
-- ------------------------|--------
-- (Intercept) | 1500.5
-- dias_desde_lancamento | -12.3
-- Detectar outliers usando desvio padrao
CREATE OR REPLACE FUNCTION detectar_outliers_r(
tabela TEXT,
coluna TEXT,
limite_desvios NUMERIC DEFAULT 3
) RETURNS TABLE(valor NUMERIC, z_score NUMERIC) AS $$
# Buscar dados
query <- paste("SELECT", coluna, "FROM", tabela)
dados <- pg.spi.exec(query)
valores <- dados[[coluna]]
# Calcular Z-scores
media <- mean(valores, na.rm=TRUE)
desvio <- sd(valores, na.rm=TRUE)
z_scores <- abs(valores - media) / desvio
# Filtrar outliers
outliers <- valores[z_scores > limite_desvios]
z_outliers <- z_scores[z_scores > limite_desvios]
return(data.frame(valor=outliers, z_score=z_outliers))
$$ LANGUAGE plr;
-- Detectar valores anomalos em vendas
SELECT * FROM detectar_outliers_r('vendas_diarias', 'valor', 2.5);
-- Previsao de serie temporal usando pacote forecast
CREATE OR REPLACE FUNCTION prever_vendas_futuras(dias_futuro INTEGER)
RETURNS TABLE(dia INTEGER, previsao NUMERIC, limite_inferior NUMERIC, limite_superior NUMERIC) AS $$
library(forecast)
# Buscar historico de vendas
query <- "SELECT valor FROM vendas_diarias ORDER BY data"
dados <- pg.spi.exec(query)
# Criar serie temporal
serie <- ts(dados$valor, frequency=7) # Frequencia semanal
# Ajustar modelo ARIMA automatico
modelo <- auto.arima(serie)
# Fazer previsao
previsao <- forecast(modelo, h=dias_futuro)
# Retornar resultados
return(data.frame(
dia = 1:dias_futuro,
previsao = as.numeric(previsao$mean),
limite_inferior = as.numeric(previsao$lower[,2]),
limite_superior = as.numeric(previsao$upper[,2])
))
$$ LANGUAGE plr;
-- Prever proximos 30 dias
SELECT * FROM prever_vendas_futuras(30);
-- Correlacao entre multiplas variaveis
CREATE OR REPLACE FUNCTION matriz_correlacao()
RETURNS TABLE(variavel1 TEXT, variavel2 TEXT, correlacao NUMERIC) AS $$
# Buscar dados multivariados
query <- "SELECT views_pagina, tempo_site, itens_carrinho, conversao FROM metricas_usuario"
dados <- pg.spi.exec(query)
# Calcular matriz de correlacao
matriz <- cor(dados)
# Converter para formato tabular
resultados <- data.frame()
for(i in 1:ncol(matriz)) {
for(j in 1:ncol(matriz)) {
resultados <- rbind(resultados, data.frame(
variavel1 = colnames(matriz)[i],
variavel2 = colnames(matriz)[j],
correlacao = matriz[i,j]
))
}
}
return(resultados)
$$ LANGUAGE plr;
SELECT * FROM matriz_correlacao()
WHERE variavel1 != variavel2
ORDER BY abs(correlacao) DESC;PL/Lua: Lógica de Negócio Leve e Rápida
A extensão PL/Lua embute a linguagem Lua no PostgreSQL, oferecendo alternativa leve e extremamente rápida ao PL/pgSQL para lógica complexa. Lua é famosa por ser uma das linguagens de script mais rápidas disponíveis, sendo usada em jogos (World of Warcraft, Angry Birds), servidores web (Nginx, OpenResty) e sistemas embarcados. Sua sintaxe simples e execução eficiente tornam-na ideal para funções que executam milhões de vezes.
Lua consome muito menos memória que linguagens como Python ou R, permitindo execução concorrente de muitas funções sem sobrecarregar servidor. A linguagem tem recursos poderosos como coroutines para programação assíncrona e metatables para orientação a objetos. Funções Lua no PostgreSQL podem acessar todas as facilidades SQL através de API dedicada, executar queries, preparar statements e gerenciar transações.
# Instalacao da extensao
dnf install postgresql16-pllua_18-- Ativacao da extensao
CREATE EXTENSION pllua;
-- Funcao simples em Lua: calcular fatorial
CREATE OR REPLACE FUNCTION fatorial_lua(n INTEGER)
RETURNS BIGINT AS $$
if n <= 1 then
return 1
else
return n * fatorial_lua(n - 1)
end
$$ LANGUAGE pllua;
-- Usar funcao
SELECT fatorial_lua(10); -- Retorna 3628800
-- Funcao que processa JSON complexo
CREATE OR REPLACE FUNCTION processar_pedido_json(pedido_json TEXT)
RETURNS TABLE(item TEXT, quantidade INTEGER, subtotal NUMERIC) AS $$
-- Parsear JSON em Lua
local json = require("json")
local pedido = json.decode(pedido_json)
local resultados = {}
-- Processar cada item
for _, item in ipairs(pedido.itens) do
-- Buscar preco do produto
local stmt = spi.prepare("SELECT preco FROM produtos WHERE id = $1", {"INTEGER"})
local result = stmt:execute(item.produto_id)
local preco = result[1].preco
-- Calcular subtotal
local subtotal = item.quantidade * preco
table.insert(resultados, {
item = item.nome,
quantidade = item.quantidade,
subtotal = subtotal
})
end
return resultados
$$ LANGUAGE pllua;
-- Usar funcao com JSON
SELECT * FROM processar_pedido_json('{
"itens": [
{"produto_id": 123, "nome": "Notebook", "quantidade": 2},
{"produto_id": 456, "nome": "Mouse", "quantidade": 5}
]
}');
-- Validacao complexa com logica de negocios
CREATE OR REPLACE FUNCTION validar_promocao_lua(
cliente_id INTEGER,
produtos INTEGER[],
cupom TEXT
) RETURNS TABLE(valido BOOLEAN, mensagem TEXT, desconto NUMERIC) AS $$
-- Buscar nivel do cliente
local stmt = spi.prepare("SELECT nivel, total_compras FROM clientes WHERE id = $1", {"INTEGER"})
local cliente = stmt:execute(cliente_id)[1]
local desconto = 0
local mensagem = ""
-- Logica de validacao de cupom
if cupom == "PRIMEIRACOMPRA" then
if cliente.total_compras > 0 then
return {{valido=false, mensagem="Cupom apenas para primeira compra", desconto=0}}
end
desconto = 15
mensagem = "Cupom de primeira compra aplicado"
elseif cupom == "FIDELIDADE" then
if cliente.nivel ~= "PREMIUM" then
return {{valido=false, mensagem="Cupom exclusivo para clientes premium", desconto=0}}
end
desconto = 20
mensagem = "Cupom de fidelidade aplicado"
elseif cupom:sub(1, 5) == "DESC-" then
-- Cupom dinamico: DESC-10 = 10% desconto
desconto = tonumber(cupom:sub(6))
if desconto > 30 then
return {{valido=false, mensagem="Desconto maximo de 30%", desconto=0}}
end
mensagem = "Desconto de " .. desconto .. "% aplicado"
else
return {{valido=false, mensagem="Cupom invalido", desconto=0}}
end
return {{valido=true, mensagem=mensagem, desconto=desconto}}
$$ LANGUAGE pllua;
-- Testar validacao
SELECT * FROM validar_promocao_lua(123, ARRAY[1,2,3], 'PRIMEIRACOMPRA');
-- Trigger em Lua para auditoria
CREATE OR REPLACE FUNCTION audit_trigger_lua()
RETURNS TRIGGER AS $$
local mudancas = {}
-- Comparar OLD e NEW
if TG_OP == "UPDATE" then
for coluna, valor_novo in pairs(NEW) do
local valor_antigo = OLD[coluna]
if valor_novo ~= valor_antigo then
table.insert(mudancas, string.format(
"%s: %s -> %s", coluna, tostring(valor_antigo), tostring(valor_novo)
))
end
end
-- Registrar mudancas
if #mudancas > 0 then
local stmt = spi.prepare([[
INSERT INTO auditoria (tabela, registro_id, operacao, mudancas, usuario, timestamp)
VALUES ($1, $2, $3, $4, $5, now())
]], {"TEXT", "INTEGER", "TEXT", "TEXT", "TEXT"})
stmt:execute(
TG_TABLE_NAME,
NEW.id,
TG_OP,
table.concat(mudancas, "; "),
current_user()
)
end
end
return NEW
$$ LANGUAGE pllua;
CREATE TRIGGER trigger_audit_produtos
AFTER UPDATE ON produtos
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_lua();Monitoramento Avançado de Banco de Dados
Monitoramento efetivo diferencia ambientes estáveis de ambientes problemáticos. Extensões de monitoramento fornecem visibilidade profunda sobre comportamento do banco de dados, desde tracking de queries individuais até análise de tentativas de autenticação e status de replicação. Essas ferramentas permitem resposta proativa a problemas antes que afetem usuários finais.
pg_stat_monitor: Estatísticas de Queries com Contexto Temporal
A extensão pg_stat_monitor evolui o conceito do pg_stat_statements adicionando dimensão temporal às estatísticas. Ao invés de acumular dados desde última reinicialização, ela agrupa queries em buckets de tempo configuráveis (exemplo: intervalos de 15 minutos). Isso permite análises como "quais queries estavam lentas durante o incidente das 14h?" ou "como a carga mudou entre ontem e hoje no mesmo horário?"
A capacidade de análise temporal é crucial para troubleshooting de problemas intermitentes. Picos de carga, degradação gradual de performance e anomalias pontuais ficam visíveis quando queries podem ser comparadas entre períodos. A extensão também captura mais metadados que pg_stat_statements padrão, incluindo queries dentro de blocos de transação e contexto de execução completo.
# Instalacao da extensao
dnf install postgresql16-pg-stat-monitor_18-- Configuracao no postgresql.conf:
-- shared_preload_libraries = 'pg_stat_monitor'
-- pg_stat_monitor.pgsm_max = 10000
-- pg_stat_monitor.pgsm_query_max_len = 2048
-- pg_stat_monitor.pgsm_normalized_query = yes
-- pg_stat_monitor.pgsm_bucket_time = 300 -- Buckets de 5 minutos
-- Reiniciar PostgreSQL
-- Ativacao da extensao
CREATE EXTENSION pg_stat_monitor;
-- Ver queries mais lentas no bucket atual
SELECT query,
calls,
total_exec_time,
mean_exec_time,
stddev_exec_time,
rows
FROM pg_stat_monitor
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Comparar performance entre buckets temporais
SELECT bucket,
bucket_start_time,
query,
calls,
mean_exec_time
FROM pg_stat_monitor
WHERE query LIKE '%SELECT%FROM usuarios%'
ORDER BY bucket_start_time DESC;
-- Queries que mais usaram CPU em periodo especifico
SELECT bucket_start_time,
query,
total_exec_time,
calls,
(total_exec_time / calls) as tempo_medio_ms
FROM pg_stat_monitor
WHERE bucket_start_time >= now() - interval '1 hour'
ORDER BY total_exec_time DESC
LIMIT 20;
-- Analise de queries por usuario
SELECT userid::regrole as usuario,
count(*) as tipos_queries,
sum(calls) as total_execucoes,
pg_size_pretty(sum(shared_blks_hit * 8192)::bigint) as cache_hit
FROM pg_stat_monitor
GROUP BY userid
ORDER BY total_execucoes DESC;
-- Queries com maior variacao de tempo (inconsistentes)
SELECT query,
calls,
mean_exec_time,
stddev_exec_time,
round(stddev_exec_time / NULLIF(mean_exec_time, 0) * 100, 2) as coef_variacao_pct
FROM pg_stat_monitor
WHERE calls > 100
ORDER BY coef_variacao_pct DESC
LIMIT 15;
-- Resetar estatisticas
SELECT pg_stat_monitor_reset();
-- Ver configuracoes ativas
SHOW pg_stat_monitor.pgsm_bucket_time;
SHOW pg_stat_monitor.pgsm_max;Extensões para Analytics e Processamento Estatístico
Analytics e processamento estatístico transformam dados brutos em insights acionáveis. Extensões especializadas implementam algoritmos que seriam extremamente ineficientes em SQL puro, como contagem probabilística de elementos distintos, cálculo de percentis em streams de dados e agregações top-N sem ordenação completa. Essas ferramentas são essenciais para análise de grandes volumes onde precisão absoluta pode ser trocada por velocidade astronômica.
Estruturas de dados probabilísticas como HyperLogLog e T-Digest permitem análises que seriam impossíveis de outra forma. Contar usuários únicos diários em bilhões de eventos usando COUNT DISTINCT levaria horas e consumiria toda a memória disponível. Com HyperLogLog, a mesma operação completa em segundos usando kilobytes de RAM, com erro inferior a meio por cento. Essas técnicas são fundamentais para dashboards em tempo real e análises de big data.
hll: HyperLogLog para Contagem Probabilística
A extensão hll implementa o algoritmo HyperLogLog, que conta elementos distintos usando espaço logarítmico ao invés de linear. Ao invés de armazenar cada elemento visto (como COUNT DISTINCT faz internamente), o HyperLogLog mantém uma estrutura compacta de cerca de 1280 bytes que estima a cardinalidade com erro típico de 0,2%. Isso permite calcular métricas como usuários ativos diários, visitantes únicos mensais ou IPs distintos em petabytes de logs.
A verdadeira magia do HyperLogLog é que estruturas podem ser mescladas com operação de união. Se cada servidor web mantém HLL local de visitantes, esses HLLs podem ser combinados para obter total global sem reprocessar dados originais. Isso habilita agregações distribuídas eficientes e cálculo incremental, onde novos dados são adicionados ao HLL existente sem recalcular tudo. A extensão é usada por empresas como Aggregate Knowledge (criadora original) para análises em escala web.
# Instalacao da extensao
dnf install postgresql16-hll_18-- Ativacao da extensao
CREATE EXTENSION hll;
-- Criar tabela com coluna HLL para usuarios unicos diarios
CREATE TABLE metricas_diarias (
data DATE PRIMARY KEY,
usuarios_unicos hll,
pageviews BIGINT DEFAULT 0
);
-- Funcao para registrar visita de usuario
CREATE OR REPLACE FUNCTION registrar_visita(usuario_id INTEGER, data_visita DATE)
RETURNS VOID AS $$
BEGIN
INSERT INTO metricas_diarias (data, usuarios_unicos, pageviews)
VALUES (data_visita, hll_empty() || hll_hash_integer(usuario_id), 1)
ON CONFLICT (data) DO UPDATE SET
usuarios_unicos = metricas_diarias.usuarios_unicos || hll_hash_integer(usuario_id),
pageviews = metricas_diarias.pageviews + 1;
END;
$$ LANGUAGE plpgsql;
-- Simular visitas
SELECT registrar_visita(123, '2025-01-29');
SELECT registrar_visita(456, '2025-01-29');
SELECT registrar_visita(123, '2025-01-29'); -- Usuario repetido
SELECT registrar_visita(789, '2025-01-29');
-- Consultar usuarios unicos (estimativa)
SELECT data,
hll_cardinality(usuarios_unicos)::INTEGER as usuarios_unicos,
pageviews
FROM metricas_diarias;
-- Resultado: 3 usuarios unicos, 4 pageviews
-- Calcular usuarios unicos na ultima semana (uniao de HLLs)
SELECT hll_cardinality(hll_union_agg(usuarios_unicos))::INTEGER as usuarios_semana
FROM metricas_diarias
WHERE data >= current_date - interval '7 days';
-- Calcular MAU (Monthly Active Users) eficientemente
SELECT hll_cardinality(hll_union_agg(usuarios_unicos))::INTEGER as mau
FROM metricas_diarias
WHERE data >= current_date - interval '30 days';
-- Exemplo realista: logs de acesso web
CREATE TABLE logs_acesso (
timestamp TIMESTAMP,
usuario_id INTEGER,
pagina TEXT,
ip_address INET
);
-- Criar agregacao HLL para analise rapida
CREATE TABLE resumo_horario (
hora TIMESTAMP,
usuarios_unicos hll,
ips_unicos hll,
total_acessos BIGINT
);
-- Popular resumo (executado periodicamente)
INSERT INTO resumo_horario
SELECT
date_trunc('hour', timestamp) as hora,
hll_add_agg(hll_hash_integer(usuario_id)) as usuarios_unicos,
hll_add_agg(hll_hash_text(host(ip_address))) as ips_unicos,
count(*) as total_acessos
FROM logs_acesso
WHERE timestamp >= current_timestamp - interval '1 hour'
GROUP BY date_trunc('hour', timestamp)
ON CONFLICT (hora) DO UPDATE SET
usuarios_unicos = EXCLUDED.usuarios_unicos,
ips_unicos = EXCLUDED.ips_unicos,
total_acessos = EXCLUDED.total_acessos;
-- Dashboard em tempo real
SELECT hora,
hll_cardinality(usuarios_unicos)::INTEGER as usuarios,
hll_cardinality(ips_unicos)::INTEGER as ips,
total_acessos,
(total_acessos::FLOAT / NULLIF(hll_cardinality(usuarios_unicos), 0)) as acessos_por_usuario
FROM resumo_horario
WHERE hora >= current_timestamp - interval '24 hours'
ORDER BY hora DESC;
-- Comparar periodos (usuarios novos vs recorrentes)
WITH semana_atual AS (
SELECT hll_union_agg(usuarios_unicos) as hll_atual
FROM metricas_diarias
WHERE data >= current_date - interval '7 days'
),
semana_anterior AS (
SELECT hll_union_agg(usuarios_unicos) as hll_anterior
FROM metricas_diarias
WHERE data BETWEEN current_date - interval '14 days' AND current_date - interval '8 days'
)
SELECT
hll_cardinality((SELECT hll_atual FROM semana_atual))::INTEGER as usuarios_semana_atual,
hll_cardinality((SELECT hll_anterior FROM semana_anterior))::INTEGER as usuarios_semana_anterior,
hll_cardinality(
(SELECT hll_atual FROM semana_atual) - (SELECT hll_anterior FROM semana_anterior)
)::INTEGER as usuarios_novos;TopN: Agregação Top-N Sem Ordenação Completa
A extensão TopN implementa estrutura de dados especializada para manter top N elementos sem necessidade de ordenar dataset completo. Ao invés de executar ORDER BY seguido de LIMIT (que ordena todos os registros), a estrutura TopN mantém apenas os N maiores valores vistos até momento. Isso reduz dramaticamente uso de memória e tempo de processamento, especialmente em agregações de bilhões de registros.
A técnica é fundamental para dashboards que mostram produtos mais vendidos, páginas mais acessadas ou erros mais frequentes. A estrutura TopN pode ser incrementalmente atualizada, permitindo que novos dados sejam adicionados sem recalcular tudo. Múltiplas estruturas TopN podem ser mescladas para agregações distribuídas, similar ao HyperLogLog. A implementação usa heap min para garantir eficiência mesmo com Ns grandes.
# Instalacao da extensao
dnf install postgresql16-topn_18-- Ativacao da extensao
CREATE EXTENSION topn;
-- Criar tipo TopN para produtos mais vendidos
CREATE TABLE vendas_resumo (
data DATE PRIMARY KEY,
top_produtos topn
);
-- Funcao para registrar venda
CREATE OR REPLACE FUNCTION registrar_venda_produto(
produto_id INTEGER,
quantidade INTEGER,
data_venda DATE
) RETURNS VOID AS $$
BEGIN
INSERT INTO vendas_resumo (data, top_produtos)
VALUES (data_venda, topn_empty(10))
ON CONFLICT (data) DO UPDATE SET
top_produtos = topn_add(
vendas_resumo.top_produtos,
produto_id::text,
quantidade
);
END;
$$ LANGUAGE plpgsql;
-- Simular vendas
SELECT registrar_venda_produto(101, 5, current_date);
SELECT registrar_venda_produto(102, 8, current_date);
SELECT registrar_venda_produto(101, 3, current_date);
SELECT registrar_venda_produto(103, 12, current_date);
SELECT registrar_venda_produto(104, 2, current_date);
-- Consultar top 5 produtos do dia
SELECT data,
(topn(top_produtos, 5)).*
FROM vendas_resumo
WHERE data = current_date;
-- Criar agregacao para logs de erro
CREATE TABLE logs_erro_resumo (
hora TIMESTAMP PRIMARY KEY,
top_erros topn
);
-- Popular com dados de logs
INSERT INTO logs_erro_resumo
SELECT
date_trunc('hour', timestamp) as hora,
topn_agg(mensagem_erro, 20) as top_erros
FROM logs_erros
WHERE timestamp >= current_timestamp - interval '24 hours'
GROUP BY date_trunc('hour', timestamp);
-- Ver top 10 erros mais frequentes na ultima hora
SELECT (topn(top_erros, 10)).*
FROM logs_erro_resumo
WHERE hora = date_trunc('hour', current_timestamp);
-- Mesclar TopNs de varios periodos
WITH top_semanal AS (
SELECT topn_union_agg(top_produtos) as produtos_semana
FROM vendas_resumo
WHERE data >= current_date - interval '7 days'
)
SELECT (topn(produtos_semana, 10)).*
FROM top_semanal;
-- Exemplo: paginas mais acessadas por hora
CREATE TABLE acessos_web_resumo (
hora TIMESTAMP,
top_paginas topn,
top_usuarios topn
);
INSERT INTO acessos_web_resumo
SELECT
date_trunc('hour', timestamp) as hora,
topn_agg(pagina, 50) as top_paginas,
topn_agg(usuario_id::text, 100) as top_usuarios
FROM logs_acesso
GROUP BY date_trunc('hour', timestamp);
-- Dashboard: paginas mais visitadas agora
SELECT
hora,
(topn(top_paginas, 15)).*
FROM acessos_web_resumo
WHERE hora = date_trunc('hour', current_timestamp);tdigest: Percentis Precisos em Streams de Dados
A extensão tdigest implementa estrutura de dados para cálculo de percentis e quantis de forma aproximada mas precisa em streams de dados. Calcular percentil 99 de latência tradicionalmente requer ordenar todos os valores e pegar elemento na posição correta. Com milhões de medições, isso é proibitivamente caro. O T-Digest mantém resumo compacto que permite calcular qualquer percentil com alta precisão e espaço constante.
A técnica é essencial para monitoramento de SLAs (Service Level Agreements) que especificam latências de percentil 95 ou 99. Métricas como "99% das requisições completam em menos de 200ms" requerem T-Digest para serem calculadas eficientemente em produção. A estrutura pode ser mesclada para agregações distribuídas, permitindo que cada servidor mantenha T-Digest local que depois são combinados para visão global.
# Instalacao da extensao
dnf install postgresql16-tdigest_18-- Ativacao da extensao
CREATE EXTENSION tdigest;
-- Criar tabela para metricas de latencia
CREATE TABLE metricas_latencia (
hora TIMESTAMP PRIMARY KEY,
latencias tdigest,
total_requisicoes BIGINT
);
-- Registrar latencias em tempo real
INSERT INTO metricas_latencia (hora, latencias, total_requisicoes)
SELECT
date_trunc('hour', timestamp) as hora,
tdigest_agg(latencia_ms) as latencias,
count(*) as total_requisicoes
FROM logs_requisicoes
WHERE timestamp >= current_timestamp - interval '1 hour'
GROUP BY date_trunc('hour', timestamp)
ON CONFLICT (hora) DO UPDATE SET
latencias = EXCLUDED.latencias,
total_requisicoes = EXCLUDED.total_requisicoes;
-- Calcular percentis de latencia
SELECT
hora,
tdigest_percentile(latencias, 0.50) as p50_mediana,
tdigest_percentile(latencias, 0.95) as p95,
tdigest_percentile(latencias, 0.99) as p99,
tdigest_percentile(latencias, 0.999) as p999,
total_requisicoes
FROM metricas_latencia
WHERE hora >= current_timestamp - interval '24 hours'
ORDER BY hora DESC;
-- Verificar se SLA esta sendo cumprido (99% < 200ms)
SELECT
hora,
tdigest_percentile(latencias, 0.99) as p99_latencia,
CASE
WHEN tdigest_percentile(latencias, 0.99) < 200 THEN 'SLA OK'
ELSE 'SLA VIOLADO'
END as status_sla
FROM metricas_latencia
WHERE hora >= current_date
ORDER BY hora DESC;
-- Distribuicao de latencias (varios percentis)
SELECT
data,
tdigest_percentile(latencias_dia, 0.10) as p10,
tdigest_percentile(latencias_dia, 0.25) as p25,
tdigest_percentile(latencias_dia, 0.50) as p50,
tdigest_percentile(latencias_dia, 0.75) as p75,
tdigest_percentile(latencias_dia, 0.90) as p90,
tdigest_percentile(latencias_dia, 0.99) as p99
FROM (
SELECT
date_trunc('day', hora) as data,
tdigest_union_agg(latencias) as latencias_dia
FROM metricas_latencia
GROUP BY date_trunc('day', hora)
) sub
ORDER BY data DESC;
-- Exemplo: tamanhos de arquivos upload
CREATE TABLE uploads_metricas (
data DATE PRIMARY KEY,
tamanhos_bytes tdigest,
total_uploads BIGINT
);
-- Analisar distribuicao de tamanhos
SELECT
data,
pg_size_pretty(tdigest_percentile(tamanhos_bytes, 0.50)::BIGINT) as tamanho_mediano,
pg_size_pretty(tdigest_percentile(tamanhos_bytes, 0.95)::BIGINT) as p95,
pg_size_pretty(tdigest_percentile(tamanhos_bytes, 0.99)::BIGINT) as p99,
total_uploads
FROM uploads_metricas
ORDER BY data DESC
LIMIT 7;
-- Comparar latencias entre endpoints
CREATE TABLE latencias_por_endpoint (
endpoint TEXT,
periodo TIMESTAMP,
latencias tdigest
);
SELECT
endpoint,
tdigest_percentile(tdigest_union_agg(latencias), 0.95) as p95_latencia
FROM latencias_por_endpoint
WHERE periodo >= current_date
GROUP BY endpoint
ORDER BY p95_latencia DESC;Compatibilidade com Oracle Database
Migrar aplicações legadas de Oracle para PostgreSQL é desafio comum em modernização de infraestrutura. Reescrever milhares de linhas de PL/SQL manualmente é inviável na maioria dos projetos. Extensões de compatibilidade Oracle implementam funções, pacotes e comportamentos específicos do Oracle, permitindo que código existente rode com modificações mínimas. Isso reduz drasticamente tempo e custo de migração.
orafce: Funções e Pacotes Oracle no PostgreSQL
A extensão orafce é indispensável para migrações Oracle. Ela implementa centenas de funções Oracle padrão como NVL, DECODE, TO_CHAR com formato Oracle, ADD_MONTHS, TRUNC de datas e pacotes completos como DBMS_OUTPUT, DBMS_RANDOM, DBMS_UTILITY e UTL_FILE. Código PL/SQL que usa essas funções funciona imediatamente após instalar orafce.
A extensão também emula comportamentos Oracle como concatenação com NULL (Oracle trata como string vazia), conversões implícitas de tipos e tratamento de VARCHAR2. Pacotes DBMS permitem que stored procedures Oracle façam operações de sistema como ler/escrever arquivos ou gerar números aleatórios exatamente como no Oracle. Isso preserva funcionalidade completa de aplicações durante migração.
# Instalacao da extensao
dnf install postgresql16-orafce_18-- Ativacao da extensao
CREATE EXTENSION orafce;
-- Funcoes Oracle agora disponiveis
-- NVL: substitui NULL por valor default
SELECT nvl(NULL, 'valor padrao'); -- Retorna 'valor padrao'
SELECT nvl(nome, 'SEM NOME') FROM clientes;
-- NVL2: valor diferente se NULL ou nao NULL
SELECT nvl2(email, 'TEM EMAIL', 'SEM EMAIL') FROM usuarios;
-- DECODE: equivalente a CASE mas sintaxe Oracle
SELECT
nome,
decode(status,
'A', 'Ativo',
'I', 'Inativo',
'S', 'Suspenso',
'Desconhecido'
) as status_descricao
FROM contas;
-- TO_CHAR com formato Oracle
SELECT to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as data_hora;
SELECT to_char(1234567.89, '999G999G999D99') as valor_formatado;
-- Funcoes de data Oracle
SELECT add_months(current_date, 6) as daqui_6_meses;
SELECT last_day(current_date) as ultimo_dia_mes;
SELECT next_day(current_date, 'SUNDAY') as proximo_domingo;
SELECT months_between('2025-12-31', '2025-01-01') as meses_diferenca;
-- TRUNC para datas (comportamento Oracle)
SELECT trunc(current_timestamp, 'MM') as primeiro_dia_mes;
SELECT trunc(current_timestamp, 'YYYY') as primeiro_dia_ano;
-- Pacote DBMS_OUTPUT (equivalente a RAISE NOTICE)
DO $$
BEGIN
dbms_output.enable();
dbms_output.put_line('Mensagem de debug');
dbms_output.put_line('Valor: ' || 123);
END $$;
-- Pacote DBMS_RANDOM
SELECT dbms_random.value() as numero_aleatorio; -- Entre 0 e 1
SELECT dbms_random.value(1, 100) as aleatorio_1_a_100;
SELECT dbms_random.string('U', 10) as string_aleatoria;
-- Pacote UTL_FILE (leitura/escrita de arquivos)
DO $$
DECLARE
arquivo utl_file.file_type;
linha TEXT;
BEGIN
-- Abrir arquivo para escrita
arquivo := utl_file.fopen('/tmp', 'teste.txt', 'w');
utl_file.put_line(arquivo, 'Linha 1 do arquivo');
utl_file.put_line(arquivo, 'Linha 2 do arquivo');
utl_file.fclose(arquivo);
-- Ler arquivo
arquivo := utl_file.fopen('/tmp', 'teste.txt', 'r');
LOOP
BEGIN
utl_file.get_line(arquivo, linha);
RAISE NOTICE 'Lido: %', linha;
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
END LOOP;
utl_file.fclose(arquivo);
END $$;
-- Funcao GREATEST/LEAST com comportamento Oracle
SELECT greatest(10, 20, 5, 30) as maior; -- 30
SELECT least(10, 20, 5, 30) as menor; -- 5
-- SUBSTR com indices base-1 (Oracle) ao inves de base-0
SELECT substr('PostgreSQL', 1, 6); -- 'Postgr'
-- INSTR: encontrar posicao de substring (Oracle style)
SELECT instr('PostgreSQL', 'SQL') as posicao; -- 7
-- LPAD/RPAD com comportamento Oracle
SELECT lpad('123', 10, '0') as codigo; -- '0000000123'
SELECT rpad('Produto', 20, '.') as nome_preenchido;
-- Conversao de tipos estilo Oracle
SELECT to_number('1234.56', '9999.99') as numero;
SELECT to_date('31/12/2025', 'DD/MM/YYYY') as data;
-- Procedimento usando sintaxe Oracle
CREATE OR REPLACE PROCEDURE processar_vendas_oracle(p_data DATE) AS
$$
DECLARE
v_total NUMERIC := 0;
v_contador INTEGER := 0;
BEGIN
dbms_output.put_line('Processando vendas de: ' || to_char(p_data, 'DD/MM/YYYY'));
SELECT count(*), nvl(sum(valor), 0)
INTO v_contador, v_total
FROM vendas
WHERE trunc(data_venda) = trunc(p_data);
dbms_output.put_line('Total de vendas: ' || v_contador);
dbms_output.put_line('Valor total: ' || to_char(v_total, '999G999D99'));
END;
$$ LANGUAGE plpgsql;
-- Executar procedure
CALL processar_vendas_oracle(current_date);Indexação Avançada para Casos Especializados
Índices B-Tree padrão são eficientes para maioria dos casos, mas cenários especializados exigem estruturas dedicadas. Buscas de texto completo com wildcard inicial, ordenação por relevância em full-text search e pesquisas fuzzy de strings similares são impossíveis ou extremamente lentas com índices tradicionais. Extensões de indexação avançada implementam estruturas que resolvem esses problemas específicos com performance ótima.
pg_bigm: Índices para Busca de Substring Eficiente
A extensão pg_bigm cria índices baseados em bigramas (pares de caracteres consecutivos) que aceleram buscas com LIKE e ILIKE mesmo quando padrão começa com wildcard. Busca como LIKE '%texto%' normalmente força varredura sequencial de tabela inteira, pois índices B-Tree não ajudam com wildcard inicial. Índices bigram resolvem isso indexando todas as combinações de dois caracteres.
A técnica é fundamental para sistemas de busca que precisam encontrar ocorrências em qualquer posição do texto. Casos de uso incluem busca de produtos por descrição parcial, localização de documentos contendo fragmentos de texto e sistemas de autocompleção que sugerem resultados enquanto usuário digita. A extensão também suporta busca de similaridade de strings, encontrando textos parecidos mesmo com pequenas diferenças ortográficas.
# Instalacao da extensao
dnf install postgresql16-pg_bigm_18-- Ativacao da extensao
CREATE EXTENSION pg_bigm;
-- Criar tabela de produtos
CREATE TABLE produtos (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
descricao TEXT,
codigo_barras TEXT
);
-- Inserir dados de exemplo
INSERT INTO produtos (nome, descricao) VALUES
('Notebook Dell Inspiron', 'Notebook 15 polegadas Intel i5 8GB RAM'),
('Mouse Logitech MX Master', 'Mouse ergonomico sem fio recarregavel'),
('Teclado Mecânico Keychron', 'Teclado mecanico RGB switches blue'),
('Monitor LG UltraWide', 'Monitor 34 polegadas curvo resolucao 2K');
-- Criar indice bigram para busca de texto
CREATE INDEX idx_produtos_nome_bigm ON produtos USING gin (nome gin_bigm_ops);
CREATE INDEX idx_produtos_descricao_bigm ON produtos USING gin (descricao gin_bigm_ops);
-- Busca com wildcard inicial (agora usa indice!)
EXPLAIN ANALYZE
SELECT * FROM produtos WHERE nome LIKE '%Logitech%';
-- Busca case-insensitive
SELECT * FROM produtos WHERE nome ILIKE '%keychron%';
-- Busca por similaridade (encontra textos parecidos)
SELECT nome,
similarity(nome, 'Notebok Del') as similaridade
FROM produtos
WHERE nome % 'Notebok Del' -- Operador % = similar
ORDER BY similaridade DESC;
-- Configurar limite de similaridade (0.0 a 1.0)
SET pg_bigm.similarity_limit = 0.3;
-- Buscar produtos similares a query com typo
SELECT nome FROM produtos WHERE nome % 'Mose Logitec';
-- Encontra "Mouse Logitech MX Master" mesmo com erros
-- Criar indice para codigo de barras (busca parcial)
CREATE INDEX idx_produtos_codigo_bigm ON produtos USING gin (codigo_barras gin_bigm_ops);
-- Buscar produto por fragmento de codigo
SELECT * FROM produtos WHERE codigo_barras LIKE '%7891234%';
-- Funcao de busca avancada com ranking
CREATE OR REPLACE FUNCTION buscar_produtos(termo TEXT)
RETURNS TABLE(
id INTEGER,
nome TEXT,
relevancia REAL
) AS $$
BEGIN
RETURN QUERY
SELECT
p.id,
p.nome,
greatest(
similarity(p.nome, termo),
similarity(p.descricao, termo)
) as relevancia
FROM produtos p
WHERE p.nome % termo OR p.descricao % termo
ORDER BY relevancia DESC
LIMIT 20;
END;
$$ LANGUAGE plpgsql;
-- Usar funcao de busca
SELECT * FROM buscar_produtos('teclado mecanico');
-- Busca em multiplas colunas
SELECT
id,
nome,
CASE
WHEN nome ILIKE '%notebook%' THEN 'nome'
WHEN descricao ILIKE '%notebook%' THEN 'descricao'
END as encontrado_em
FROM produtos
WHERE nome ILIKE '%notebook%' OR descricao ILIKE '%notebook%';
-- Performance: comparar sem e com indice
-- Criar tabela grande para teste
CREATE TABLE documentos_teste AS
SELECT
id,
'Documento ' || id || ' com texto aleatorio ' || md5(random()::text) as conteudo
FROM generate_series(1, 100000) id;
-- Busca sem indice (lenta)
EXPLAIN ANALYZE
SELECT * FROM documentos_teste WHERE conteudo LIKE '%texto aleatorio%';
-- Criar indice bigram
CREATE INDEX idx_documentos_conteudo_bigm ON documentos_teste
USING gin (conteudo gin_bigm_ops);
-- Mesma busca com indice (rapida)
EXPLAIN ANALYZE
SELECT * FROM documentos_teste WHERE conteudo LIKE '%texto aleatorio%';Segurança e Auditoria Detalhada
Segurança é requisito crítico para bancos de dados de produção, especialmente em ambientes regulados por leis como LGPD, GDPR, HIPAA ou SOC2. Extensões de segurança fornecem auditoria granular de todas as operações, criptografia nativa de dados sensíveis e políticas de senha robustas. Elas registram quem acessou quais dados e quando, permitindo resposta rápida a incidentes e demonstração de conformidade em auditorias.
pgaudit: Auditoria Detalhada para Conformidade
A extensão pgaudit é o padrão da indústria para auditoria de bancos de dados PostgreSQL. Ela registra logs detalhados de operações no banco através do sistema de logging nativo, capturando informações como qual usuário executou qual comando em quais tabelas e quando. O nível de detalhe é configurável, desde audit de apenas comandos DDL até logging completo de cada SELECT, INSERT, UPDATE e DELETE executado.
A auditoria é essencial para detectar acessos não autorizados, investigar vazamentos de dados e provar conformidade com regulamentações. Logs pgaudit podem ser integrados com SIEMs (Security Information and Event Management) como Splunk ou ELK Stack para correlação com eventos de segurança de outros sistemas. A extensão adiciona overhead mínimo mesmo com auditoria completa ativada, sendo viável em produção de alta carga.
# Instalacao da extensao
dnf install postgresql16-pgaudit_18-- Configuracao no postgresql.conf:
-- shared_preload_libraries = 'pgaudit'
-- pgaudit.log = 'read,write,ddl' -- Tipos de operacao a auditar
-- pgaudit.log_catalog = off -- Nao auditar tabelas de sistema
-- pgaudit.log_parameter = on -- Incluir valores de parametros
-- pgaudit.log_relation = on -- Incluir nomes de tabelas
-- pgaudit.log_statement_once = off -- Log de cada statement
-- Reiniciar PostgreSQL
-- Ativacao da extensao
CREATE EXTENSION pgaudit;
-- Configurar auditoria em nivel de sessao
-- 'read' = SELECT, COPY FROM
-- 'write' = INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
-- 'ddl' = CREATE, ALTER, DROP
-- 'function' = execucao de funcoes
-- 'role' = operacoes de usuario/role
-- 'misc' = operacoes diversas
SET pgaudit.log = 'read,write,ddl';
-- Executar operacoes que serao auditadas
SELECT * FROM clientes WHERE id = 123; -- Auditado como READ
INSERT INTO pedidos (cliente_id, valor) VALUES (123, 150.00); -- Auditado como WRITE
CREATE TABLE teste_auditoria (id INTEGER); -- Auditado como DDL
-- Logs aparecem no PostgreSQL log file:
-- AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM clientes WHERE id = 123
-- AUDIT: SESSION,2,1,WRITE,INSERT,,,INSERT INTO pedidos (cliente_id, valor) VALUES (123, 150.00)
-- AUDIT: SESSION,3,1,DDL,CREATE TABLE,,,CREATE TABLE teste_auditoria (id INTEGER)
-- Auditoria especifica por objeto (role-based)
-- Criar role para auditoria personalizada
CREATE ROLE usuario_auditado;
-- Configurar auditoria apenas para esse role
ALTER ROLE usuario_auditado SET pgaudit.log = 'read,write';
-- Auditar apenas tabelas especificas
CREATE ROLE auditor;
GRANT SELECT ON dados_sensiveis TO auditor;
ALTER ROLE auditor SET pgaudit.log = 'read';
-- Desabilitar auditoria temporariamente (para operacoes batch)
SET pgaudit.log = 'none';
-- Executar carga de dados sem auditoria
COPY grande_tabela FROM '/tmp/dados.csv';
-- Reabilitar auditoria
RESET pgaudit.log;
-- Ver configuracao atual
SHOW pgaudit.log;
SHOW pgaudit.log_catalog;
SHOW pgaudit.log_parameter;
-- Exemplo de entrada de log pgaudit:
-- 2025-01-29 10:15:30.123 UTC [12345]: [1-1] user=app_user,db=producao,app=myapp
-- AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.usuarios,
-- UPDATE usuarios SET ultimo_acesso = now() WHERE id = 456Extensões Geoespaciais para Sistemas de Informação Geográfica
Dados geoespaciais estão presentes em praticamente todas as aplicações modernas, desde aplicativos de entrega que calculam rotas até sistemas de análise urbana que processam dados de satélite. O PostgreSQL, através de extensões especializadas, torna-se o banco de dados geoespacial mais poderoso do mundo open source. A capacidade de armazenar geometrias complexas, calcular distâncias geodésicas precisas e executar análises espaciais avançadas coloca PostgreSQL no mesmo patamar de soluções comerciais caras.
O ecossistema geoespacial do PostgreSQL vai além de simples armazenamento de coordenadas. Ele oferece roteamento em redes viárias, análise de dados LIDAR tridimensionais, indexação hexagonal para análises de densidade e suporte a centenas de sistemas de coordenadas diferentes. Essas capacidades são usadas por governos para planejamento urbano, empresas de logística para otimização de rotas e cientistas para análise de mudanças climáticas.
PostGIS: O Gigante dos Dados Geoespaciais
O PostGIS transforma PostgreSQL no banco de dados espacial mais avançado disponível. Ele adiciona tipos de dados geométricos e geográficos completos, permitindo armazenar pontos, linhas, polígonos e geometrias tridimensionais. Mais importante, fornece centenas de funções para análise espacial: calcular área e perímetro, testar contenção e interseção, simplificar geometrias, gerar buffers e transformar entre sistemas de coordenadas.
O PostGIS implementa padrões internacionais como OGC Simple Features e SQL/MM, garantindo interoperabilidade com ferramentas GIS como QGIS, ArcGIS e MapBox. Suporta indexação espacial através de índices GiST e SP-GiST, tornando consultas espaciais em milhões de geometrias extremamente rápidas. Casos de uso vão desde aplicativos simples de localização até sistemas complexos de análise ambiental processando terabytes de dados satelitais.
# Instalacao da extensao PostGIS
dnf install postgis34_18-- Ativacao da extensao
CREATE EXTENSION postgis;
-- Verificar versao instalada
SELECT PostGIS_Full_Version();
-- Criar tabela com geometrias
CREATE TABLE cidades (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
populacao INTEGER,
localizacao GEOMETRY(POINT, 4326) -- SRID 4326 = WGS84 (GPS)
);
-- Inserir cidades com coordenadas (longitude, latitude)
INSERT INTO cidades (nome, populacao, localizacao) VALUES
('São Paulo', 12300000, ST_SetSRID(ST_MakePoint(-46.6333, -23.5505), 4326)),
('Rio de Janeiro', 6748000, ST_SetSRID(ST_MakePoint(-43.1729, -22.9068), 4326)),
('Brasília', 3055000, ST_SetSRID(ST_MakePoint(-47.8825, -15.7942), 4326)),
('Salvador', 2900000, ST_SetSRID(ST_MakePoint(-38.5016, -12.9714), 4326));
-- Criar indice espacial para buscas rapidas
CREATE INDEX idx_cidades_localizacao ON cidades USING GIST(localizacao);
-- Calcular distancia entre cidades (em metros)
SELECT
c1.nome as origem,
c2.nome as destino,
round(ST_Distance(c1.localizacao::geography, c2.localizacao::geography) / 1000) as distancia_km
FROM cidades c1
CROSS JOIN cidades c2
WHERE c1.id < c2.id
ORDER BY distancia_km;
-- Encontrar cidades dentro de 500km de Brasilia
SELECT nome, populacao,
round(ST_Distance(
localizacao::geography,
(SELECT localizacao::geography FROM cidades WHERE nome = 'Brasília')
) / 1000) as distancia_km
FROM cidades
WHERE ST_DWithin(
localizacao::geography,
(SELECT localizacao::geography FROM cidades WHERE nome = 'Brasília'),
500000 -- 500km em metros
)
AND nome != 'Brasília'
ORDER BY distancia_km;
-- Criar tabela de regioes (poligonos)
CREATE TABLE estados (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
sigla CHAR(2),
area_territorial GEOMETRY(MULTIPOLYGON, 4326)
);
-- Inserir estado (exemplo simplificado - poligono de Sao Paulo)
INSERT INTO estados (nome, sigla, area_territorial) VALUES
('São Paulo', 'SP', ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((
-44.0 -25.3, -53.1 -22.0, -50.0 -20.0, -44.0 -20.0, -44.0 -25.3
)))'), 4326));
-- Verificar se cidade esta dentro do estado
SELECT c.nome as cidade, e.nome as estado
FROM cidades c
JOIN estados e ON ST_Contains(e.area_territorial, c.localizacao)
WHERE e.sigla = 'SP';
-- Calcular area de um estado (em km²)
SELECT nome,
round(ST_Area(area_territorial::geography) / 1000000) as area_km2
FROM estados;
-- Criar buffer (area de influencia) ao redor de ponto
SELECT ST_AsText(
ST_Buffer(localizacao::geography, 50000)::geometry
) as buffer_50km
FROM cidades
WHERE nome = 'São Paulo';
-- Rotas e linhas
CREATE TABLE rodovias (
id SERIAL PRIMARY KEY,
nome TEXT,
numero TEXT,
geometria GEOMETRY(LINESTRING, 4326)
);
-- Inserir rodovia
INSERT INTO rodovias (nome, numero, geometria) VALUES
('Rodovia Presidente Dutra', 'BR-116',
ST_SetSRID(ST_MakeLine(ARRAY[
ST_MakePoint(-46.6333, -23.5505), -- Sao Paulo
ST_MakePoint(-43.1729, -22.9068) -- Rio de Janeiro
]), 4326));
-- Calcular comprimento de rodovia
SELECT nome,
round(ST_Length(geometria::geography) / 1000) as comprimento_km
FROM rodovias;
-- Encontrar ponto mais proximo em uma linha
SELECT ST_AsText(
ST_ClosestPoint(
geometria,
ST_SetSRID(ST_MakePoint(-45.0, -23.0), 4326)
)
) as ponto_mais_proximo
FROM rodovias
WHERE numero = 'BR-116';
-- Simplificar geometria complexa (reduzir vertices)
SELECT ST_AsText(ST_Simplify(geometria, 0.01)) as geometria_simplificada
FROM estados
WHERE sigla = 'SP';
-- Criar grade de hexagonos para analise espacial
SELECT ST_AsText(geom) as hexagono
FROM ST_HexagonGrid(
0.5, -- Tamanho do lado do hexagono
ST_SetSRID(ST_MakeEnvelope(-47.0, -24.0, -46.0, -23.0), 4326) -- Bounding box
) geom
LIMIT 10;
-- Analise de pontos de interesse
CREATE TABLE estabelecimentos (
id SERIAL PRIMARY KEY,
nome TEXT,
tipo TEXT,
localizacao GEOMETRY(POINT, 4326)
);
-- Encontrar 5 estabelecimentos mais proximos de uma localizacao
SELECT nome, tipo,
round(ST_Distance(
localizacao::geography,
ST_SetSRID(ST_MakePoint(-46.6500, -23.5600), 4326)::geography
)) as distancia_metros
FROM estabelecimentos
ORDER BY localizacao <-> ST_SetSRID(ST_MakePoint(-46.6500, -23.5600), 4326)
LIMIT 5;
-- Converter entre formatos (WKT, GeoJSON, KML)
SELECT
ST_AsText(localizacao) as wkt,
ST_AsGeoJSON(localizacao) as geojson,
ST_AsKML(localizacao) as kml
FROM cidades
WHERE nome = 'São Paulo';
-- Transformar entre sistemas de coordenadas
-- De WGS84 (4326) para UTM Zone 23S (31983)
SELECT ST_AsText(
ST_Transform(localizacao, 31983)
) as coordenadas_utm
FROM cidades
WHERE nome = 'São Paulo';pgrouting: Roteamento em Redes de Transporte
A extensão pgrouting adiciona capacidades de roteamento em grafos ao PostGIS, permitindo calcular rotas ótimas em redes de transporte. Ela implementa algoritmos clássicos como Dijkstra para menor caminho, A* (A-Star) para roteamento com heurística, Traveling Salesman Problem para otimização de múltiplas paradas e análise de alcançabilidade para determinar áreas acessíveis dentro de tempo/distância limite.
Aplicações práticas incluem sistemas de navegação GPS, otimização de rotas de entrega, planejamento de transporte público e análise de acessibilidade urbana. A extensão trabalha com dados de redes viárias reais obtidos de fontes como OpenStreetMap, considerando restrições como sentido único de vias, velocidades máximas e impedimentos temporários. O resultado são rotas práticas que respeitam regras do mundo real.
# Instalacao da extensao
dnf install pgrouting_18-- Ativacao das extensoes necessarias
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
-- Estrutura de rede viaria (simplificada)
CREATE TABLE rede_viaria (
id SERIAL PRIMARY KEY,
source_id INTEGER NOT NULL, -- Vertice inicial
target_id INTEGER NOT NULL, -- Vertice final
custo DOUBLE PRECISION, -- Custo (tempo, distancia, etc)
custo_reverso DOUBLE PRECISION, -- Custo no sentido oposto
geometria GEOMETRY(LINESTRING, 4326)
);
-- Inserir segmentos de rua
INSERT INTO rede_viaria (source_id, target_id, custo, custo_reverso, geometria) VALUES
(1, 2, 5.0, 5.0, ST_MakeLine(ST_MakePoint(-46.650, -23.550), ST_MakePoint(-46.651, -23.551))),
(2, 3, 3.0, 3.0, ST_MakeLine(ST_MakePoint(-46.651, -23.551), ST_MakePoint(-46.652, -23.552))),
(1, 3, 8.0, 8.0, ST_MakeLine(ST_MakePoint(-46.650, -23.550), ST_MakePoint(-46.652, -23.552))),
(3, 4, 4.0, 4.0, ST_MakeLine(ST_MakePoint(-46.652, -23.552), ST_MakePoint(-46.653, -23.553))),
(2, 4, 6.0, 6.0, ST_MakeLine(ST_MakePoint(-46.651, -23.551), ST_MakePoint(-46.653, -23.553)));
-- Criar indices para performance
CREATE INDEX idx_rede_source ON rede_viaria(source_id);
CREATE INDEX idx_rede_target ON rede_viaria(target_id);
CREATE INDEX idx_rede_geom ON rede_viaria USING GIST(geometria);
-- Calcular menor caminho entre dois pontos (Dijkstra)
SELECT
seq,
node,
edge,
cost,
agg_cost
FROM pgr_dijkstra(
'SELECT id, source_id as source, target_id as target, custo as cost FROM rede_viaria',
1, -- Vertice origem
4, -- Vertice destino
directed := false
) ORDER BY seq;
-- Obter geometria da rota calculada
WITH rota AS (
SELECT edge, seq
FROM pgr_dijkstra(
'SELECT id, source_id as source, target_id as target, custo as cost FROM rede_viaria',
1, 4, directed := false
)
WHERE edge IS NOT NULL
)
SELECT
r.seq,
rv.id as segmento_id,
ST_AsText(rv.geometria) as geometria,
rv.custo
FROM rota r
JOIN rede_viaria rv ON r.edge = rv.id
ORDER BY r.seq;
-- Roteamento A* (mais rapido com heuristica)
SELECT
seq,
node,
cost,
agg_cost
FROM pgr_aStar(
'SELECT id, source_id as source, target_id as target,
custo as cost, custo_reverso as reverse_cost,
ST_X(ST_StartPoint(geometria)) as x1,
ST_Y(ST_StartPoint(geometria)) as y1,
ST_X(ST_EndPoint(geometria)) as x2,
ST_Y(ST_EndPoint(geometria)) as y2
FROM rede_viaria',
1, 4, directed := false
) ORDER BY seq;
-- Calcular area de alcancabilidade (driving distance)
-- Todos os pontos alcancaveis com custo maximo de 10
SELECT
node,
edge,
cost,
agg_cost
FROM pgr_drivingDistance(
'SELECT id, source_id as source, target_id as target, custo as cost FROM rede_viaria',
1, -- Vertice origem
10.0 -- Custo maximo
) ORDER BY agg_cost;
-- Problema do Caixeiro Viajante (TSP)
-- Encontrar ordem otima de visitar pontos
SELECT
seq,
node,
cost,
agg_cost
FROM pgr_TSP(
$$SELECT id as source,
ST_X(ST_Centroid(geometria)) as x,
ST_Y(ST_Centroid(geometria)) as y
FROM rede_viaria$$
) ORDER BY seq;
-- Rota com multiplas paradas (via points)
SELECT
seq,
path_seq,
node,
edge,
cost,
agg_cost
FROM pgr_dijkstraVia(
'SELECT id, source_id as source, target_id as target, custo as cost FROM rede_viaria',
ARRAY[1, 2, 4], -- Sequencia de vertices a visitar
directed := false
) ORDER BY seq, path_seq;
-- Exemplo realistico: importar dados do OpenStreetMap
-- (Requer ferramenta osm2pgrouting para processar arquivo OSM)
-- Estrutura compativel com osm2pgrouting
CREATE TABLE ways (
gid SERIAL PRIMARY KEY,
osm_id BIGINT,
name TEXT,
source INTEGER,
target INTEGER,
length_m DOUBLE PRECISION,
cost DOUBLE PRECISION,
reverse_cost DOUBLE PRECISION,
maxspeed_forward INTEGER,
maxspeed_backward INTEGER,
the_geom GEOMETRY(LINESTRING, 4326)
);
-- Calcular custo baseado em velocidade maxima
UPDATE ways SET
cost = length_m / (maxspeed_forward * 1000.0 / 3600.0), -- Tempo em segundos
reverse_cost = length_m / (maxspeed_backward * 1000.0 / 3600.0)
WHERE maxspeed_forward > 0;
-- Roteamento real de endereco A para endereco B
-- 1. Encontrar no mais proximo ao ponto de origem
WITH ponto_origem AS (
SELECT id, source, target
FROM ways
ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(-46.650, -23.550), 4326)
LIMIT 1
),
ponto_destino AS (
SELECT id, source, target
FROM ways
ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(-46.700, -23.600), 4326)
LIMIT 1
)
-- 2. Calcular rota entre vertices mais proximos
SELECT
w.name as nome_rua,
round(w.length_m) as comprimento_metros,
r.cost as tempo_segundos,
ST_AsGeoJSON(w.the_geom) as geometria
FROM pgr_dijkstra(
'SELECT gid as id, source, target, cost, reverse_cost FROM ways',
(SELECT source FROM ponto_origem),
(SELECT target FROM ponto_destino),
directed := true
) r
JOIN ways w ON r.edge = w.gid
ORDER BY r.seq;Recursos Principais: Extensões que Transformam PostgreSQL
Algumas extensões são tão revolucionárias que transformam completamente o comportamento do PostgreSQL. Elas não apenas adicionam funcionalidades, mas reimplementam aspectos fundamentais do banco de dados para casos de uso específicos. Citus transforma PostgreSQL em cluster distribuído, TimescaleDB otimiza para séries temporais e pgvector adiciona busca de similaridade para inteligência artificial. Essas extensões representam anos de pesquisa e desenvolvimento de empresas especializadas.
Citus: PostgreSQL Distribuído para Escala Horizontal
A extensão Citus transforma PostgreSQL single-node em sistema distribuído que escala horizontalmente adicionando servidores. Ela implementa sharding transparente, onde dados de tabelas grandes são automaticamente distribuídos entre múltiplos nós workers. Queries são paralelizadas executando em todos os nós simultaneamente, agregando resultados no coordenador. Isso permite processar terabytes de dados com latências de milissegundos.
Citus é ideal para aplicações multi-tenant (SaaS) onde cada cliente tem seus próprios dados, análises em tempo real sobre grandes volumes e dashboards que agregam bilhões de eventos. A extensão mantém transações ACID completas mesmo em operações distribuídas, suporta JOINs entre dados sharded e mantém compatibilidade com aplicações existentes. Empresas como Microsoft (Azure Cosmos DB for PostgreSQL) e Cloudflare usam Citus em produção.
# Instalacao da extensao
dnf install citus_18-- Configuracao no postgresql.conf do coordenador:
-- shared_preload_libraries = 'citus'
-- Reiniciar PostgreSQL
-- Ativacao da extensao (no coordenador)
CREATE EXTENSION citus;
-- Adicionar workers ao cluster
SELECT citus_add_node('worker1.exemplo.com', 5432);
SELECT citus_add_node('worker2.exemplo.com', 5432);
SELECT citus_add_node('worker3.exemplo.com', 5432);
-- Ver nodes do cluster
SELECT * FROM citus_get_active_worker_nodes();
-- Criar tabela distribuida (sharded)
CREATE TABLE eventos (
usuario_id BIGINT NOT NULL,
evento_tipo TEXT,
timestamp TIMESTAMP,
dados JSONB
);
-- Marcar tabela como distribuida por usuario_id
SELECT create_distributed_table('eventos', 'usuario_id');
-- Citus automaticamente cria shards em todos os workers
-- Inserir dados (distribuidos automaticamente)
INSERT INTO eventos (usuario_id, evento_tipo, timestamp, dados) VALUES
(123, 'login', now(), '{"ip": "192.168.1.1"}'::jsonb),
(456, 'compra', now(), '{"valor": 150.00}'::jsonb),
(123, 'logout', now(), '{"duracao": 3600}'::jsonb);
-- Query executa em paralelo em todos os workers
SELECT usuario_id, count(*) as total_eventos
FROM eventos
WHERE timestamp > current_date - interval '7 days'
GROUP BY usuario_id
ORDER BY total_eventos DESC
LIMIT 10;
-- Tabela de referencia (replicada em todos os workers)
CREATE TABLE produtos (
id INTEGER PRIMARY KEY,
nome TEXT,
preco DECIMAL
);
SELECT create_reference_table('produtos');
-- JOIN entre tabela distribuida e referencia funciona normalmente
SELECT
e.usuario_id,
p.nome as produto,
count(*) as compras
FROM eventos e
JOIN produtos p ON (e.dados->>'produto_id')::INTEGER = p.id
WHERE e.evento_tipo = 'compra'
GROUP BY e.usuario_id, p.nome;
-- Tabela local (apenas no coordenador)
CREATE TABLE configuracoes (
chave TEXT PRIMARY KEY,
valor TEXT
);
-- Colocacao (co-location) para JOINs eficientes
CREATE TABLE usuarios (
id BIGINT PRIMARY KEY,
nome TEXT,
email TEXT
);
CREATE TABLE pedidos (
id BIGSERIAL,
usuario_id BIGINT,
valor DECIMAL,
data TIMESTAMP
);
-- Distribuir ambas por usuario_id (mesmo sharding)
SELECT create_distributed_table('usuarios', 'id');
SELECT create_distributed_table('pedidos', 'usuario_id');
-- JOIN entre tabelas co-localizadas executa localmente em cada worker
SELECT u.nome, sum(p.valor) as total_gasto
FROM usuarios u
JOIN pedidos p ON u.id = p.usuario_id
GROUP BY u.nome;
-- Rebalancear shards entre workers
SELECT citus_rebalance_start();
-- Monitorar rebalanceamento
SELECT * FROM citus_rebalance_status();
-- Estatisticas de distribuicao
SELECT * FROM citus_shards;
SELECT * FROM citus_shard_sizes ORDER BY size DESC;
-- Exemplo multi-tenant (isolamento por tenant_id)
CREATE TABLE tenant_dados (
tenant_id INTEGER NOT NULL,
recurso_id BIGINT,
dados JSONB,
criado_em TIMESTAMP DEFAULT now()
);
SELECT create_distributed_table('tenant_dados', 'tenant_id');
-- Todos os dados de um tenant ficam no mesmo shard
-- Queries de um tenant sao extremamente rapidas
SELECT * FROM tenant_dados
WHERE tenant_id = 42 -- Executa em um unico worker
AND criado_em > current_date - interval '30 days';
-- Politicas de isolamento por tenant
SET citus.multi_shard_commit_protocol = '2pc'; -- Two-phase commit
-- Adicionar capacidade adicionando worker dinamicamente
SELECT citus_add_node('worker4.exemplo.com', 5432);
-- Remover worker gracefully
SELECT citus_drain_node('worker1.exemplo.com', 5432);TimescaleDB: Otimização para Séries Temporais
A extensão TimescaleDB otimiza PostgreSQL especificamente para dados de séries temporais, comuns em IoT, monitoramento, métricas de aplicações e dados financeiros. Ela introduz hypertables, que automaticamente particiona dados por tempo mantendo interface de tabela única. Consultas em janelas de tempo são 10-100x mais rápidas que PostgreSQL puro, e compressão nativa reduz armazenamento em 90% sem perda de dados.
A extensão adiciona funções especializadas para análise temporal como time_bucket para agregações por intervalo, first e last para valores em extremos de série e suporte nativo para continuous aggregates (views materializadas incrementais). Políticas de retenção automatizam exclusão de dados antigos. TimescaleDB é usado por empresas como Coinbase para métricas de trading e Schneider Electric para dados de sensores industriais.
# Instalacao da extensao
dnf install timescaledb_18
# Executar ferramenta de configuracao (ajusta postgresql.conf)
timescaledb-tune-- Configuracao no postgresql.conf:
-- shared_preload_libraries = 'timescaledb'
-- Reiniciar PostgreSQL
-- Ativacao da extensao
CREATE EXTENSION timescaledb;
-- Criar tabela normal de metricas
CREATE TABLE metricas_sensor (
timestamp TIMESTAMPTZ NOT NULL,
sensor_id INTEGER NOT NULL,
temperatura DOUBLE PRECISION,
umidade DOUBLE PRECISION,
pressao DOUBLE PRECISION
);
-- Converter em hypertable (particao automatica por tempo)
SELECT create_hypertable('metricas_sensor', 'timestamp');
-- Inserir dados (particionamento transparente)
INSERT INTO metricas_sensor VALUES
(now(), 1, 22.5, 65.0, 1013.25),
(now() - interval '1 hour', 1, 23.1, 63.5, 1012.80),
(now() - interval '2 hours', 2, 21.8, 67.2, 1014.10);
-- Time bucket: agregar dados em intervalos de 1 hora
SELECT
time_bucket('1 hour', timestamp) as hora,
sensor_id,
avg(temperatura) as temp_media,
max(umidade) as umidade_maxima
FROM metricas_sensor
WHERE timestamp > now() - interval '24 hours'
GROUP BY hora, sensor_id
ORDER BY hora DESC;
-- Funcoes especializadas para series temporais
SELECT
sensor_id,
first(temperatura, timestamp) as primeira_leitura,
last(temperatura, timestamp) as ultima_leitura,
avg(temperatura) as media
FROM metricas_sensor
WHERE timestamp > now() - interval '7 days'
GROUP BY sensor_id;
-- Continuous Aggregates (materialized views incrementais)
CREATE MATERIALIZED VIEW metricas_horarias
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', timestamp) as hora,
sensor_id,
avg(temperatura) as temp_media,
min(temperatura) as temp_minima,
max(temperatura) as temp_maxima,
stddev(temperatura) as temp_desvio
FROM metricas_sensor
GROUP BY hora, sensor_id;
-- Atualizar continuous aggregate automaticamente
SELECT add_continuous_aggregate_policy('metricas_horarias',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour'
);
-- Consultar aggregate (super rapido)
SELECT * FROM metricas_horarias
WHERE hora > now() - interval '30 days'
ORDER BY hora DESC;
-- Compressao automatica (reduz 90% do espaco)
ALTER TABLE metricas_sensor SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'sensor_id'
);
-- Politica de compressao (comprimir dados com mais de 7 dias)
SELECT add_compression_policy('metricas_sensor', INTERVAL '7 days');
-- Politica de retencao (deletar dados com mais de 90 dias)
SELECT add_retention_policy('metricas_sensor', INTERVAL '90 days');
-- Ver chunks (particoes internas)
SELECT * FROM timescaledb_information.chunks
WHERE hypertable_name = 'metricas_sensor';
-- Ver estatisticas de compressao
SELECT * FROM timescaledb_information.compressed_chunk_stats;
-- Downsampling: agregar dados antigos em resolucao menor
CREATE MATERIALIZED VIEW metricas_diarias
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 day', timestamp) as dia,
sensor_id,
avg(temperatura) as temp_media_dia
FROM metricas_sensor
GROUP BY dia, sensor_id;
-- Gap filling: preencher lacunas em series temporais
SELECT
time_bucket_gapfill('15 minutes', timestamp) as intervalo,
sensor_id,
avg(temperatura) as temp_media,
interpolate(avg(temperatura)) as temp_interpolada
FROM metricas_sensor
WHERE timestamp > now() - interval '6 hours'
AND sensor_id = 1
GROUP BY intervalo, sensor_id
ORDER BY intervalo;
-- Exemplo IoT completo
CREATE TABLE dispositivos_iot (
timestamp TIMESTAMPTZ NOT NULL,
dispositivo_id TEXT NOT NULL,
localizacao GEOMETRY(POINT, 4326),
bateria INTEGER,
status JSONB
);
SELECT create_hypertable('dispositivos_iot', 'timestamp');
-- Indice composto para queries comuns
CREATE INDEX idx_dispositivo_tempo
ON dispositivos_iot (dispositivo_id, timestamp DESC);
-- Query otimizada
SELECT
time_bucket('5 minutes', timestamp) as intervalo,
avg((status->>'cpu')::NUMERIC) as cpu_media,
max((status->>'memoria')::NUMERIC) as memoria_maxima
FROM dispositivos_iot
WHERE dispositivo_id = 'sensor-001'
AND timestamp > now() - interval '1 hour'
GROUP BY intervalo
ORDER BY intervalo DESC;pgvector: Busca de Similaridade para IA
A extensão pgvector adiciona suporte para vetores de alta dimensionalidade e busca de similaridade vetorial, tecnologia fundamental para aplicações de inteligência artificial e machine learning. Ela permite armazenar embeddings gerados por modelos como OpenAI, HuggingFace ou Google Vertex AI e realizar buscas por nearest neighbor (vizinhos mais próximos) eficientemente usando distâncias como cosseno, euclidiana ou produto interno.
Casos de uso explodiram com ascensão de RAG (Retrieval-Augmented Generation), onde sistemas de IA buscam documentos relevantes antes de gerar respostas. Pgvector permite sistemas de recomendação (encontrar produtos similares), busca semântica (encontrar textos com significado similar mesmo sem palavras iguais), análise de sentimento e detecção de duplicatas. A extensão suporta índices IVFFlat e HNSW que aceleram buscas em milhões de vetores para tempo de resposta de milissegundos.
# Instalacao da extensao
dnf install postgresql16-pgvector_18-- Ativacao da extensao
CREATE EXTENSION vector;
-- Criar tabela com embeddings
CREATE TABLE documentos (
id SERIAL PRIMARY KEY,
titulo TEXT,
conteudo TEXT,
embedding vector(1536) -- Dimensao OpenAI text-embedding-3-small
);
-- Inserir documento com embedding
INSERT INTO documentos (titulo, conteudo, embedding) VALUES
('Introducao ao PostgreSQL',
'PostgreSQL e um banco de dados relacional open source...',
'[0.1, 0.2, 0.3, ...]'::vector); -- 1536 dimensoes
-- Criar indice para busca rapida (IVFFlat)
CREATE INDEX idx_documentos_embedding
ON documentos USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- Ou usar HNSW (geralmente mais rapido)
CREATE INDEX idx_documentos_embedding_hnsw
ON documentos USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- Buscar documentos similares (nearest neighbors)
-- Assumindo que temos embedding da query
SELECT
titulo,
conteudo,
1 - (embedding <=> '[0.15, 0.25, 0.33, ...]'::vector) as similaridade
FROM documentos
ORDER BY embedding <=> '[0.15, 0.25, 0.33, ...]'::vector
LIMIT 5;
-- Operadores de distancia:
-- <-> = Distancia euclidiana (L2)
-- <#> = Produto interno negativo
-- <=> = Distancia de cosseno
-- Exemplo completo: sistema RAG
CREATE TABLE conhecimento_base (
id SERIAL PRIMARY KEY,
documento TEXT,
chunk_texto TEXT, -- Pedaco do documento
metadata JSONB,
embedding vector(1536)
);
CREATE INDEX idx_conhecimento_embedding
ON conhecimento_base USING hnsw (embedding vector_cosine_ops);
-- Funcao de busca semantica
CREATE OR REPLACE FUNCTION buscar_contexto_relevante(
query_embedding vector(1536),
limite INTEGER DEFAULT 5
)
RETURNS TABLE(
chunk TEXT,
similaridade FLOAT,
metadata JSONB
) AS $$
BEGIN
RETURN QUERY
SELECT
chunk_texto,
1 - (embedding <=> query_embedding) as sim,
metadata
FROM conhecimento_base
ORDER BY embedding <=> query_embedding
LIMIT limite;
END;
$$ LANGUAGE plpgsql;
-- Usar em aplicacao RAG
-- 1. Gerar embedding da pergunta do usuario (via OpenAI API)
-- 2. Buscar contexto relevante
SELECT * FROM buscar_contexto_relevante('[embedding da pergunta]'::vector);
-- 3. Enviar contexto + pergunta para LLM gerar resposta
-- Recomendacao de produtos similares
CREATE TABLE produtos_ecommerce (
id SERIAL PRIMARY KEY,
nome TEXT,
descricao TEXT,
categoria TEXT,
embedding_descricao vector(768) -- BERT embeddings
);
-- Encontrar produtos similares
SELECT
p2.nome,
p2.categoria,
1 - (p2.embedding_descricao <=> p1.embedding_descricao) as similaridade
FROM produtos_ecommerce p1
CROSS JOIN LATERAL (
SELECT * FROM produtos_ecommerce p
WHERE p.id != p1.id
ORDER BY p.embedding_descricao <=> p1.embedding_descricao
LIMIT 10
) p2
WHERE p1.id = 123;
-- Deteccao de duplicatas (embeddings muito similares)
SELECT
d1.id as doc1_id,
d2.id as doc2_id,
d1.titulo,
d2.titulo,
1 - (d1.embedding <=> d2.embedding) as similaridade
FROM documentos d1
JOIN documentos d2 ON d1.id < d2.id
WHERE 1 - (d1.embedding <=> d2.embedding) > 0.95 -- >95% similar
ORDER BY similaridade DESC;
-- Clustering de vetores
-- Encontrar K centroides usando KMeans
-- (Requer implementacao externa, mas pgvector armazena resultado)
CREATE TABLE clusters_documentos (
cluster_id INTEGER PRIMARY KEY,
centroid vector(1536),
total_documentos INTEGER
);
-- Atribuir documentos aos clusters mais proximos
UPDATE documentos d
SET cluster_id = (
SELECT cluster_id
FROM clusters_documentos c
ORDER BY c.centroid <=> d.embedding
LIMIT 1
);
-- Estatisticas de vetores
SELECT
avg(vector_dims(embedding)) as dimensoes_media,
count(*) as total_vetores
FROM documentos;
-- Distancia media entre vetores
SELECT avg(
embedding <=> lead(embedding) OVER (ORDER BY id)
) as distancia_media
FROM documentos;Outras Extensões Importantes e Ferramentas Especializadas
Além das grandes categorias já exploradas, existem extensões que resolvem problemas muito específicos mas críticos em seus nichos. Ferramentas de versionamento de dados, frameworks de testes unitários para banco de dados, sistemas de automação de failover e extensões para criptografia avançada completam o ecossistema. Essas ferramentas são menos conhecidas mas igualmente valiosas para casos de uso especializados.
pgTap: Framework de Testes Unitários para Banco de Dados
A extensão pgTap é o framework padrão para escrever testes unitários de esquemas, funções e lógica de banco de dados PostgreSQL. Ela implementa protocolo TAP (Test Anything Protocol), permitindo que testes sejam executados e reportados de forma padronizada. Com pgTap, desenvolvedores podem validar que tabelas têm colunas corretas, índices existem, funções retornam valores esperados e triggers executam conforme planejado.
Testes de banco de dados são frequentemente negligenciados, mas são cruciais para prevenir regressões quando esquemas evoluem. O pgTap permite testar desde estrutura básica (esta coluna é NOT NULL?) até lógica complexa (esta função calcula corretamente impostos?). Testes podem ser integrados em pipelines de CI/CD, garantindo que mudanças não quebrem funcionalidade existente. A extensão inclui centenas de funções de asserção cobrindo praticamente todos os aspectos de banco de dados.
# Instalacao da extensao
dnf install postgresql16-pgtap_18-- Ativacao da extensao
CREATE EXTENSION pgtap;
-- Iniciar suite de testes
BEGIN;
SELECT plan(15); -- Numero de testes a executar
-- Testar existencia de tabelas
SELECT has_table('public', 'usuarios', 'Tabela usuarios deve existir');
SELECT has_table('public', 'pedidos', 'Tabela pedidos deve existir');
-- Testar estrutura de colunas
SELECT has_column('usuarios', 'id', 'Coluna id deve existir');
SELECT has_column('usuarios', 'email', 'Coluna email deve existir');
-- Testar tipos de dados
SELECT col_type_is('usuarios', 'id', 'integer', 'ID deve ser integer');
SELECT col_type_is('usuarios', 'email', 'text', 'Email deve ser text');
-- Testar constraints
SELECT col_not_null('usuarios', 'email', 'Email nao pode ser null');
SELECT col_is_unique('usuarios', ARRAY['email'], 'Email deve ser unico');
-- Testar chaves primarias
SELECT has_pk('usuarios', 'Usuarios deve ter chave primaria');
SELECT col_is_pk('usuarios', 'id', 'ID deve ser a chave primaria');
-- Testar chaves estrangeiras
SELECT has_fk('pedidos', 'Pedidos deve ter foreign key');
SELECT fk_ok('pedidos', 'usuario_id', 'usuarios', 'id',
'FK de pedidos para usuarios deve existir');
-- Testar indices
SELECT has_index('usuarios', 'idx_usuarios_email', 'Indice de email deve existir');
-- Testar funcoes
SELECT has_function('calcular_total_pedido', 'Funcao calcular_total_pedido deve existir');
-- Testar resultado de funcao
SELECT is(
calcular_total_pedido(1),
150.00::DECIMAL,
'Total do pedido 1 deve ser 150.00'
);
-- Finalizar testes
SELECT * FROM finish();
ROLLBACK;
-- Suite de testes completa
CREATE OR REPLACE FUNCTION test_suite_usuarios()
RETURNS SETOF TEXT AS $$
BEGIN
-- Testar insercao
INSERT INTO usuarios (nome, email) VALUES ('Teste', 'teste@example.com');
RETURN NEXT is(
(SELECT count(*) FROM usuarios WHERE email = 'teste@example.com'),
1::BIGINT,
'Usuario deve ser inserido corretamente'
);
-- Testar validacao de email duplicado
RETURN NEXT throws_ok(
'INSERT INTO usuarios (nome, email) VALUES (''Outro'', ''teste@example.com'')',
'23505', -- Codigo de erro de unique violation
NULL,
'Nao deve permitir email duplicado'
);
-- Testar trigger
UPDATE usuarios SET nome = 'Atualizado' WHERE email = 'teste@example.com';
RETURN NEXT is(
(SELECT atualizado_em FROM usuarios WHERE email = 'teste@example.com') > now() - interval '1 second',
true,
'Trigger deve atualizar timestamp'
);
END;
$$ LANGUAGE plpgsql;
-- Executar suite de testes
BEGIN;
SELECT * FROM runtests('test_suite_usuarios'::name);
ROLLBACK;
-- Testar performance (tempo de execucao)
SELECT cmp_ok(
(SELECT extract(milliseconds from now() - query_start)
FROM pg_stat_activity WHERE pid = pg_backend_pid()),
'<',
100.0,
'Query deve executar em menos de 100ms'
);
-- Testar dados de tabela
SELECT results_eq(
'SELECT id, nome FROM usuarios ORDER BY id',
$$VALUES (1, 'Usuario 1'), (2, 'Usuario 2')$$,
'Usuarios devem ter dados corretos'
);
-- Testar contagem de registros
SELECT is(
(SELECT count(*) FROM pedidos WHERE status = 'pendente'),
5::BIGINT,
'Deve haver 5 pedidos pendentes'
);
-- Testar views
SELECT has_view('vw_pedidos_resumo', 'View de resumo deve existir');
SELECT view_owner_is('vw_pedidos_resumo', 'postgres', 'Owner da view deve ser postgres');
-- Executar todos os testes de um schema
SELECT * FROM runtests('public'::name);temporal_tables: Versionamento Automático de Dados
A extensão temporal_tables implementa tabelas versionadas segundo padrão SQL:2011, mantendo histórico completo de todas as mudanças em dados. Cada linha tem período de validade temporal indicando quando aquela versão era atual. Ao atualizar ou deletar registro, a versão antiga é automaticamente movida para tabela de histórico com timestamps de início e fim. Isso permite queries como "como estava esta tabela em 15 de janeiro?" ou "mostre todas as versões deste registro".
Tabelas temporais são fundamentais para auditoria, análise de mudanças ao longo do tempo e recuperação de dados acidentalmente modificados. Diferente de triggers manuais de auditoria, temporal tables são padronizadas e otimizadas. O versionamento é completamente transparente para aplicação, que continua fazendo INSERT/UPDATE/DELETE normalmente. Apenas queries que precisam acessar histórico usam sintaxe especial FOR SYSTEM_TIME.
# Instalacao da extensao
dnf install postgresql16-temporal_tables_18-- Ativacao da extensao
CREATE EXTENSION temporal_tables;
-- Criar tabela principal
CREATE TABLE produtos_precos (
id SERIAL PRIMARY KEY,
produto_id INTEGER NOT NULL,
preco DECIMAL(10,2) NOT NULL,
sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
);
-- Criar tabela de historico (mesma estrutura)
CREATE TABLE produtos_precos_history (LIKE produtos_precos);
-- Criar trigger para versionamento automatico
CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON produtos_precos
FOR EACH ROW EXECUTE FUNCTION versioning(
'sys_period',
'produtos_precos_history',
true -- Incluir timestamp de fim na tabela de historico
);
-- Inserir preco inicial
INSERT INTO produtos_precos (produto_id, preco) VALUES (101, 50.00);
-- Aguardar um momento e atualizar preco
SELECT pg_sleep(2);
UPDATE produtos_precos SET preco = 55.00 WHERE produto_id = 101;
-- Aguardar e atualizar novamente
SELECT pg_sleep(2);
UPDATE produtos_precos SET preco = 52.00 WHERE produto_id = 101;
-- Ver preco atual
SELECT * FROM produtos_precos WHERE produto_id = 101;
-- Resultado: preco = 52.00
-- Ver historico completo de precos
SELECT
produto_id,
preco,
lower(sys_period) as valido_de,
upper(sys_period) as valido_ate
FROM produtos_precos_history
WHERE produto_id = 101
UNION ALL
SELECT
produto_id,
preco,
lower(sys_period) as valido_de,
upper(sys_period) as valido_ate
FROM produtos_precos
WHERE produto_id = 101
ORDER BY valido_de;
-- Query temporal: qual era o preco em determinado momento?
CREATE OR REPLACE FUNCTION preco_em_data(
p_produto_id INTEGER,
p_data TIMESTAMP
) RETURNS DECIMAL AS $$
DECLARE
v_preco DECIMAL;
BEGIN
-- Buscar na tabela de historico
SELECT preco INTO v_preco
FROM produtos_precos_history
WHERE produto_id = p_produto_id
AND sys_period @> p_data::timestamptz;
-- Se nao achou no historico, buscar na tabela atual
IF v_preco IS NULL THEN
SELECT preco INTO v_preco
FROM produtos_precos
WHERE produto_id = p_produto_id
AND sys_period @> p_data::timestamptz;
END IF;
RETURN v_preco;
END;
$$ LANGUAGE plpgsql;
-- Usar funcao temporal
SELECT preco_em_data(101, '2025-01-29 10:00:00'); -- Preco historico
-- Exemplo: rastrear mudancas de usuario
CREATE TABLE usuarios (
id SERIAL PRIMARY KEY,
nome TEXT,
email TEXT,
nivel TEXT,
sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
);
CREATE TABLE usuarios_history (LIKE usuarios);
CREATE TRIGGER usuarios_versioning
BEFORE INSERT OR UPDATE OR DELETE ON usuarios
FOR EACH ROW EXECUTE FUNCTION versioning(
'sys_period',
'usuarios_history',
true
);
-- Simular mudancas ao longo do tempo
INSERT INTO usuarios (nome, email, nivel)
VALUES ('João Silva', 'joao@email.com', 'BASICO');
SELECT pg_sleep(1);
UPDATE usuarios SET nivel = 'PREMIUM' WHERE email = 'joao@email.com';
SELECT pg_sleep(1);
UPDATE usuarios SET nome = 'João Pedro Silva' WHERE email = 'joao@email.com';
-- Auditoria: ver todas as mudancas de um usuario
SELECT
nome,
nivel,
lower(sys_period) as mudanca_em,
CASE
WHEN upper(sys_period) IS NULL THEN 'ATUAL'
ELSE 'HISTORICO'
END as status
FROM (
SELECT * FROM usuarios WHERE email = 'joao@email.com'
UNION ALL
SELECT * FROM usuarios_history WHERE email = 'joao@email.com'
) historico_completo
ORDER BY lower(sys_period);
-- Politica de retencao: deletar historico antigo
DELETE FROM produtos_precos_history
WHERE upper(sys_period) < current_timestamp - interval '2 years';
-- Restaurar versao antiga de registro
-- 1. Encontrar versao desejada no historico
SELECT * FROM usuarios_history
WHERE id = 1
AND lower(sys_period) <= '2025-01-29 10:00:00'::timestamptz
AND upper(sys_period) > '2025-01-29 10:00:00'::timestamptz;
-- 2. Atualizar tabela atual com dados historicos
UPDATE usuarios u
SET nome = h.nome,
nivel = h.nivel
FROM usuarios_history h
WHERE u.id = h.id
AND h.id = 1
AND lower(h.sys_period) <= '2025-01-29 10:00:00'::timestamptz
AND upper(h.sys_period) > '2025-01-29 10:00:00'::timestamptz;pg_auto_failover: Alta Disponibilidade Automatizada
A extensão pg_auto_failover fornece sistema completo de alta disponibilidade com failover automático para PostgreSQL. Ela monitora continuamente saúde de nós primário e standby, detectando falhas em segundos. Quando primário falha, um standby é promovido automaticamente e aplicações são redirecionadas através de monitor node central. O sistema é mais simples de configurar que Patroni mas oferece recursos semelhantes para ambientes de produção.
A arquitetura consiste em monitor node (coordenador), um nó primary e um ou mais standbys. O monitor usa health checks periódicos e implementa algoritmo de consenso para evitar split-brain. Quando primário retorna após falha, pode ser reintegrado automaticamente como standby. A ferramenta inclui CLI completo para gerenciar cluster, realizar switchover planejado e visualizar topologia atual.
# Instalacao do pg_auto_failover
dnf install pg_auto_failover_18
# Inicializar monitor node (servidor separado)
pg_autoctl create monitor \
--hostname monitor.exemplo.com \
--pgdata /var/lib/pgsql/monitor \
--auth trust \
--ssl-self-signed
# Inicializar primary node
pg_autoctl create postgres \
--hostname primary.exemplo.com \
--pgdata /var/lib/pgsql/data \
--auth trust \
--ssl-self-signed \
--monitor postgres://autoctl_node@monitor.exemplo.com/pg_auto_failover
# Inicializar standby node
pg_autoctl create postgres \
--hostname standby1.exemplo.com \
--pgdata /var/lib/pgsql/data \
--auth trust \
--ssl-self-signed \
--monitor postgres://autoctl_node@monitor.exemplo.com/pg_auto_failover
# Executar como servico (systemd)
pg_autoctl run
# Comandos de gerenciamento:
# Ver status do cluster
pg_autoctl show state
# Resultado exemplo:
# Name | Node | Host:Port | LSN | Reachable | Current State | Assigned State
# --------------+-------+----------------------+-----------+-----------+---------------------+----------------
# node_1 | 1 | primary.exemplo.com | 0/3000060 | yes | primary | primary
# node_2 | 2 | standby1.exemplo.com | 0/3000060 | yes | secondary | secondary
# Executar switchover planejado (trocar primary e standby)
pg_autoctl perform switchover
# Executar failover manual
pg_autoctl perform failover
# Ver configuracao
pg_autoctl show settings
# Habilitar maintenance mode (impede failover automatico)
pg_autoctl enable maintenance
# Desabilitar maintenance mode
pg_autoctl disable maintenance
# Ver eventos do cluster
pg_autoctl show events-- Extensao instalada automaticamente no monitor node
-- Conectar ao monitor para ver estado
-- Ver nodes registrados
SELECT * FROM pgautofailover.node;
-- Ver eventos de failover
SELECT * FROM pgautofailover.event
ORDER BY event_time DESC
LIMIT 20;
-- Ver health checks
SELECT nodename,
health,
goalstate,
reportedstate
FROM pgautofailover.node;
-- Configuracoes de failover
SELECT * FROM pgautofailover.formation;Conclusão: O Poder das Extensões PostgreSQL
O ecossistema de extensões do PostgreSQL demonstra a força do modelo de desenvolvimento aberto e extensível. O que começou como um banco de dados relacional tradicional evoluiu para plataforma universal capaz de lidar com dados geoespaciais através do PostGIS, séries temporais com TimescaleDB, busca vetorial para inteligência artificial usando pgvector e distribuição horizontal através do Citus. Cada extensão resolve problemas reais enfrentados por empresas em produção, desde startups até corporações que processam petabytes diariamente.
A diversidade de extensões cobertas neste guia revela a amplitude de casos de uso suportados. Foreign Data Wrappers conectam PostgreSQL a praticamente qualquer fonte de dados externa, eliminando silos de informação. Ferramentas de DBA automatizam manutenção e otimização, liberando administradores para trabalho estratégico. Extensões de analytics implementam algoritmos probabilísticos que tornam possível análises antes inimagináveis em SQL tradicional. Recursos de compatibilidade Oracle facilitam migrações, reduzindo custos de licenciamento enquanto mantêm funcionalidade completa.
Segurança e conformidade são endereçadas através de extensões como pgaudit para auditoria detalhada e pgsodium para criptografia moderna. Sistemas de alta disponibilidade como pg_auto_failover garantem uptime contínuo com failover automático em segundos. Linguagens procedurais alternativas como PL/R trazem capacidades estatísticas avançadas para dentro do banco de dados, eliminando necessidade de exportar dados para análise externa. Ferramentas de teste como pgTap permitem desenvolvimento rigoroso com testes unitários abrangentes.
A instalação de extensões em sistemas baseados em RPM é padronizada e confiável, com pacotes mantidos pela comunidade PostgreSQL disponíveis através de repositórios oficiais. O comando dnf install seguido de CREATE EXTENSION é tudo que separa um PostgreSQL básico de um sistema especializado. Esta simplicidade de deployment, combinada com estabilidade do PostgreSQL e maturidade das extensões, torna a solução adequada para qualquer escala de operação.
O futuro do ecossistema PostgreSQL é promissor. Extensões continuam evoluindo com novos recursos, enquanto extensões completamente novas surgem para resolver desafios emergentes. A integração com tecnologias modernas como containerização através de Docker, orquestração com Kubernetes e plataformas de nuvem torna PostgreSQL ainda mais versátil. A comunidade ativa garante que bugs são corrigidos rapidamente, documentação é mantida atualizada e melhores práticas são compartilhadas globalmente.
Para profissionais de tecnologia, dominar extensões PostgreSQL representa vantagem competitiva significativa. Conhecer quando usar pg_bigm para buscas de substring, HyperLogLog para contagem de distintos em escala ou pglogical para replicação seletiva permite arquitetar soluções elegantes para problemas complexos. A capacidade de escolher ferramenta certa para cada situação, ao invés de forçar soluções genéricas, resulta em sistemas mais eficientes, manuteníveis e econômicos.
Este guia completo serve como referência permanente para desenvolvedores, administradores de banco de dados, engenheiros de dados e arquitetos de sistemas. Cada extensão foi apresentada com contexto de uso, exemplos práticos de código e casos reais de aplicação. A organização por categorias facilita localização rápida da ferramenta necessária quando surge um desafio específico. Os comandos de instalação e exemplos SQL são prontos para uso, permitindo experimentação imediata e aprendizado prático.
O PostgreSQL, potencializado por seu rico ecossistema de extensões, prova que software open source pode não apenas competir, mas superar alternativas proprietárias em funcionalidade, performance e confiabilidade. A ausência de custos de licenciamento, combinada com liberdade de modificar e estender conforme necessário, democratiza acesso a tecnologia de ponta. Organizações de todos os tamanhos podem implementar soluções de classe mundial sem comprometer qualidade ou capacidades avançadas.
A jornada de aprendizado com PostgreSQL e suas extensões é contínua. Novas versões trazem melhorias, otimizações de performance e recursos adicionais. Extensões são atualizadas para suportar últimas versões do PostgreSQL e incorporar feedback da comunidade. Participar de fóruns, conferências como PGConf e contribuir para projetos open source fortalece tanto habilidades individuais quanto o ecossistema como um todo. O conhecimento compartilhado neste guia é apenas o ponto de partida para exploração mais profunda deste universo técnico fascinante.
Ao implementar extensões PostgreSQL em produção, sempre consulte documentação oficial específica da versão utilizada, realize testes abrangentes em ambientes não produtivos e planeje janelas de manutenção adequadas para instalação e configuração. Monitore performance antes e depois da ativação de extensões para validar benefícios esperados. Mantenha backups atualizados e tenha planos de rollback caso algo não funcione conforme esperado. Com preparação adequada e conhecimento sólido, as extensões PostgreSQL transformam desafios técnicos em soluções elegantes e eficientes.
Referências e Recursos Adicionais
A documentação oficial do PostgreSQL está disponível em https://www.postgresql.org/docs/ e é constantemente atualizada com cada nova versão. O repositório oficial de pacotes RPM mantido pela comunidade pode ser acessado em https://yum.postgresql.org/, oferecendo extensões compiladas e testadas para diversas distribuições Linux baseadas em Red Hat. O catálogo completo de extensões disponíveis pode ser explorado em https://pgxn.org/, a rede de distribuição de extensões PostgreSQL.
Comunidades ativas fornecem suporte através de listas de email, fóruns e canais de chat. O Stack Overflow possui tag dedicada para PostgreSQL com milhares de questões respondidas. Conferências anuais como PGConf US, PGConf Europe e eventos regionais oferecem oportunidades de aprendizado e networking com especialistas. Blogs técnicos de empresas especializadas como 2ndQuadrant, Crunchy Data, Percona e EnterpriseDB publicam regularmente artigos aprofundados sobre casos de uso avançados e otimizações de performance.
Para quem busca certificação profissional, o programa PostgreSQL Professional Certification oferece validação formal de conhecimentos. Cursos online em plataformas como Coursera, Udemy e Linux Academy cobrem desde fundamentos até tópicos avançados. Livros como "PostgreSQL: Up and Running" e "The Art of PostgreSQL" são referências valiosas para estudo sistemático. A combinação de documentação oficial, prática hands-on e participação na comunidade constrói expertise sólida ao longo do tempo.
O código fonte de todas as extensões citadas está disponível publicamente, permitindo que desenvolvedores estudem implementações, contribuam melhorias e adaptem soluções para necessidades específicas. A licença permissiva do PostgreSQL e da maioria das extensões garante liberdade total de uso em ambientes comerciais. Esta transparência e abertura são pilares fundamentais que sustentam a confiança depositada por milhões de usuários globalmente no ecossistema PostgreSQL.