PostgreSQL Avançado para Desenvolvedores Backend: Índices, Performance, Particionamento e Otimização de Queries em 2025

Sumário

1. Por que PostgreSQL Domina o Mercado de Bancos Relacionais
2. Anatomia de uma Query: Como o Planner Funciona
3. Índices: Muito Além do B-Tree Básico
4. EXPLAIN ANALYZE: A Arte de Ler Planos de Execução
5. Tipos Avançados do PostgreSQL
6. CTEs e Window Functions: SQL Poderoso
7. Full-Text Search Nativo do PostgreSQL
8. Particionamento de Tabelas: Escalando para Bilhões de Linhas
9. Replicação e Alta Disponibilidade
10. Vacuum, Autovacuum e Manutenção
11. Tuning de Configuração para Produção
12. Extensões Poderosas: PostGIS, pg_stat_statements e mais
13. Padrões de Schema Avançados
14. Monitoramento e Diagnóstico
15. Migrações Seguras em Produção
16. Conclusão e Próximos Passos

1. Por que PostgreSQL Domina o Mercado de Bancos Relacionais {#introducao}

Em 2025, o PostgreSQL é consistentemente o banco de dados mais amado e mais adotado entre desenvolvedores profissionais. Não é por acaso: após 35 anos de desenvolvimento contínuo, o PostgreSQL oferece uma combinação única de robustez ACID, extensibilidade, conformidade com padrões SQL e recursos avançados que rivalizam com bancos comerciais como Oracle e SQL Server.

O que Diferencia o PostgreSQL

MVCC (Multi-Version Concurrency Control)

O PostgreSQL usa MVCC para gerenciar concorrência sem precisar bloquear leituras. Quando uma transação modifica uma linha, o PostgreSQL cria uma nova versão dela (a versão “morta” fica visível para transações que começaram antes da modificação). Isso elimina o problema de leitores bloqueando escritores e escritores bloqueando leitores.

Implicação prática: queries SELECT nunca bloqueiam UPDATE/DELETE e vice-versa. Isso é fundamental para aplicações de alta concorrência.

Tipos de Dados Ricos

O PostgreSQL não se limita aos tipos básicos. Ele oferece:

• JSONB — JSON binário com indexação completa
• ARRAY — Arrays nativos de qualquer tipo
• HSTORE — Key-value store
• UUID — Identificadores universais únicos
• INTERVAL — Intervalos de tempo
• TSTZRANGE — Ranges de timestamp com fuso horário
• GEOMETRY — Tipos geoespaciais via PostGIS
• Tipos customizados via CREATE TYPE

Extensibilidade

PostgreSQL permite criar:

• Tipos de dados customizados
• Funções em múltiplas linguagens (PL/pgSQL, PL/Python, PL/V8 para JavaScript)
• Operadores customizados
• Métodos de acesso a índices (access methods)
• Agregações customizadas
• Procedimentos armazenados

Conformidade com SQL Padrão

PostgreSQL tem a melhor conformidade com o padrão SQL entre todos os bancos open source. Isso significa portabilidade de queries e redução de comportamentos surpreendentes.

Configuração de Ambiente para Seguir Este Guia

# Docker com PostgreSQL 16 (versão mais recente LTS)
docker run -d 
  --name postgres-lab 
  -e POSTGRES_PASSWORD=postgres 
  -e POSTGRES_DB=learning 
  -p 5432:5432 
  -v postgres_data:/var/lib/postgresql/data 
  postgres:16-alpine

# Conectar
psql -h localhost -U postgres -d learning

# Habilitar timing de queries
timing on

# Ver tamanho de objetos
l+       # databases
dt+      # tabelas
di+      # índices

2. Anatomia de uma Query: Como o Planner Funciona {#query-planner}

Para otimizar queries, é essencial entender como o PostgreSQL processa uma consulta. O ciclo completo é:

SQL Query
    ↓
Parser (análise sintática → Parse Tree)
    ↓
Analyzer/Rewriter (análise semântica → Query Tree)
    ↓
Planner/Optimizer (gera planos candidatos → escolhe o mais barato)
    ↓
Executor (executa o plano escolhido)
    ↓
Resultado

O Planner e as Estatísticas

O planner do PostgreSQL usa estatísticas coletadas pelo ANALYZE para estimar o custo de diferentes planos de execução. As estatísticas ficam na tabela pg_statistic e incluem:

• n_distinct: número estimado de valores únicos
• correlation: correlação entre a ordem dos dados e os valores (relevante para range queries)
• most_common_values: valores mais frequentes (histograma)
• most_common_freqs: frequências dos valores mais comuns

-- Ver estatísticas de uma coluna
SELECT 
  attname,
  n_distinct,
  correlation,
  most_common_vals::text,
  most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Quando o Planner Erra

O planner pode fazer escolhas ruins quando:

1. Estatísticas desatualizadas: ANALYZE não rodou recentemente
2. Alta correlação não capturada: o planner assume independência entre colunas por padrão
3. Queries complexas: CTEs, subqueries aninhadas podem confundir o otimizador
4. Dados muito skewed: muitos valores de um tipo específico

-- Forçar análise estatística mais detalhada para uma coluna crítica
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; -- padrão é 100
ANALYZE orders;

-- Estatísticas de múltiplas colunas correlacionadas (PostgreSQL 10+)
CREATE STATISTICS order_status_user_stats
ON status, user_id
FROM orders;

ANALYZE orders;

3. Índices: Muito Além do B-Tree Básico {#indices}

Tipos de Índices no PostgreSQL

B-Tree (padrão) — Para a maioria dos casos

-- Índice simples
CREATE INDEX idx_users_email ON users(email);

-- Índice composto: ordem importa!
-- Eficaz para: WHERE status = 'ACTIVE' AND created_at > '2024-01-01'
-- Eficaz para: WHERE status = 'ACTIVE' (apenas primeiro campo)
-- NÃO eficaz para: WHERE created_at > '2024-01-01' (apenas segundo campo sem o primeiro)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Índice único
CREATE UNIQUE INDEX idx_users_username ON users(username);

GIN (Generalized Inverted Index) — Para arrays, JSONB e full-text

-- Para queries em JSONB
CREATE INDEX idx_products_metadata_gin ON products USING gin(metadata);

-- Para arrays
CREATE INDEX idx_products_tags_gin ON products USING gin(tags);

-- Para full-text search
CREATE INDEX idx_products_fts_gin ON products 
USING gin(to_tsvector('portuguese', name || ' ' || COALESCE(description, '')));

-- Uso:
SELECT * FROM products 
WHERE metadata @> '{"brand": "Apple"}';  -- Usa o índice GIN

SELECT * FROM products 
WHERE to_tsvector('portuguese', name || ' ' || COALESCE(description, '')) 
@@ plainto_tsquery('portuguese', 'smartphone camera');

GiST (Generalized Search Tree) — Para tipos geométricos e ranges

-- Para dados geoespaciais (PostGIS)
CREATE INDEX idx_locations_gist ON locations USING gist(coordinates);

-- Para date ranges
CREATE INDEX idx_reservations_period_gist ON reservations USING gist(period);

-- Uso com GiST:
SELECT * FROM reservations
WHERE period && '[2024-01-01, 2024-01-15]'::daterange; -- Overlap

BRIN (Block Range INdex) — Para tabelas enormes com dados naturalmente ordenados

-- Ideal para colunas de timestamp em tabelas de log/eventos
-- onde dados são inseridos em ordem cronológica
CREATE INDEX idx_events_created_brin ON events USING brin(created_at);
-- BRIN ocupa ~100x menos espaço que B-Tree para este caso

Hash — Apenas para igualdade exata

CREATE INDEX idx_sessions_token_hash ON sessions USING hash(token);
-- Mais rápido que B-Tree para igualdade exata, mas não para ORDER BY

Índices Parciais: O Segredo dos Experts

Índices parciais indexam apenas uma parte das linhas de uma tabela. São extremamente eficientes quando você consulta frequentemente um subconjunto dos dados:

-- Só indexar pedidos pendentes (assumindo que 95% dos pedidos estão COMPLETED)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'PENDING';

-- Benefício: o índice é muito menor e cabe facilmente em memória
-- Tamanho: ~5% do tamanho de um índice completo

-- Uso: esta query vai usar o índice parcial automaticamente
SELECT * FROM orders 
WHERE status = 'PENDING' 
AND created_at > NOW() - INTERVAL '24 hours';

-- Índice parcial para emails não verificados
CREATE INDEX idx_users_unverified ON users(created_at)
WHERE email_verified = false;

-- Índice parcial excluindo soft deletes
CREATE INDEX idx_products_active ON products(name, price)
WHERE deleted_at IS NULL;

Índices Funcionais (Expression Indexes)

-- Queries case-insensitive sem LOWER() no dado
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Agora esta query é eficiente:
SELECT * FROM users WHERE LOWER(email) = LOWER('Test@Example.com');

-- Índice em extração de JSON
CREATE INDEX idx_users_json_city ON users((metadata->>'city'));

-- Uso:
SELECT * FROM users WHERE metadata->>'city' = 'São Paulo';

-- Índice para buscas de prefix em LIKE
CREATE INDEX idx_products_name_prefix ON products(name text_pattern_ops);
-- Eficaz para: WHERE name LIKE 'Sams%'
-- NÃO eficaz para: WHERE name LIKE '%sung'

Índices Compostos: A Regra do Prefixo

Um índice composto em (a, b, c) é eficaz para queries que filtram por:

• a
• a, b
• a, b, c

Mas não para:

• b sozinho
• c sozinho
• b, c

-- Exemplo prático: tabela de pedidos
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at DESC);

-- ✅ Usa o índice (prefixo completo)
SELECT * FROM orders 
WHERE user_id = 'uuid' AND status = 'PENDING' 
ORDER BY created_at DESC;

-- ✅ Usa o índice (prefixo parcial)
SELECT * FROM orders WHERE user_id = 'uuid';

-- ✅ Usa o índice (prefixo parcial com status)
SELECT * FROM orders WHERE user_id = 'uuid' AND status = 'PENDING';

-- ❌ NÃO usa o índice eficientemente (pula o primeiro campo)
SELECT * FROM orders WHERE status = 'PENDING';

Índices Covering (INCLUDE)

-- Índice cobrindo: a query pode ser satisfeita apenas pelo índice
-- sem acessar a tabela (Index Only Scan)
CREATE INDEX idx_products_name_including_price
ON products(name)
INCLUDE (price, stock); -- Colunas adicionais não indexadas, apenas armazenadas

-- Esta query será um Index Only Scan (não acessa a tabela):
SELECT name, price, stock FROM products WHERE name = 'iPhone 15';

Monitoramento de Uso de Índices

-- Ver índices não utilizados (candidatos a remoção)
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan AS index_scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND tablename NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Ver tabelas com sequential scans frequentes (candidatos a novos índices)
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_scan - idx_scan AS too_much_seq,
  pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

4. EXPLAIN ANALYZE: A Arte de Ler Planos de Execução {#explain}

EXPLAIN ANALYZE é a ferramenta mais importante para diagnóstico de performance. Entender sua saída é essencial.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT 
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
HAVING SUM(o.total) > 1000
ORDER BY total_spent DESC
LIMIT 10;

Interpretando o Output do EXPLAIN

Limit  (cost=12847.52..12847.55 rows=10 width=48) (actual time=234.123..234.127 rows=10 loops=1)
  ->  Sort  (cost=12847.52..12875.52 rows=11200 width=48) (actual time=234.122..234.124 rows=10 loops=1)
        Sort Key: (sum(o.total)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=12543.00..12655.00 rows=11200 width=48) (actual time=231.234..232.891 rows=8421 loops=1)
              Group Key: u.id, u.name
              Filter: (sum(o.total) > 1000.00)
              Rows Removed by Filter: 2341
              ->  Hash Join  (cost=2847.00..11234.00 rows=85600 width=40) (actual time=45.234..198.456 rows=85234 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Index Scan using idx_orders_created on orders o  (cost=0.43..7234.12 rows=85600 width=24) (actual time=0.123..134.567 rows=85234 loops=1)
                          Index Cond: (created_at > (now() - '30 days'::interval))
                    ->  Hash  (cost=1847.00..1847.00 rows=80000 width=24) (actual time=43.456..43.456 rows=80000 loops=1)
                          Buckets: 65536  Batches: 2  Memory Usage: 4096kB
                          ->  Seq Scan on users u  (cost=0.00..1847.00 rows=80000 width=24) (actual time=0.012..21.234 rows=80000 loops=1)

Decodificando o formato:

• cost=X..Y: custo estimado (startup..total) em unidades arbitrárias
• actual time=A..B: tempo real em ms (startup..total)
• rows=N: linhas retornadas
• loops=N: quantas vezes este nó foi executado

Nós comuns e seus significados:

• Seq Scan: leitura sequencial completa da tabela (geralmente ruim para tabelas grandes)
• Index Scan: usa um índice (bom, mas acessa a tabela para buscar dados)
• Index Only Scan: usa apenas o índice (ótimo!)
• Bitmap Heap Scan: usa índice para criar um bitmap, depois acessa a tabela
• Hash Join: constrói hash table de um lado, sonda com o outro
• Nested Loop: loop aninhado (bom para small inner results)
• Merge Join: ordena ambos os lados e faz merge
• Sort: ordenação (ruim se aparecer “external merge” = faltou memória)

Usando pg_explain.depesz.com e auto_explain

-- Habilitar auto_explain para capturar queries lentas automaticamente
-- Adicionar ao postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = 1000   -- Logar queries > 1 segundo
-- auto_explain.log_analyze = true
-- auto_explain.log_buffers = true
-- auto_explain.log_format = json

-- Verificar queries lentas recentes
SELECT 
  query,
  calls,
  total_exec_time / calls AS avg_ms,
  rows / calls AS avg_rows,
  100.0 * total_exec_time / sum(total_exec_time) OVER () AS percentage
FROM pg_stat_statements
WHERE calls > 100
ORDER BY avg_ms DESC
LIMIT 20;

Dicas para Análise de Planos

-- 1. Verificar se estimativas batem com a realidade
-- Grande diferença entre "rows=100 (estimated)" e "rows=100000 (actual)" = estatísticas ruins
ANALYZE tabela_problema;

-- 2. Identificar nós mais lentos
-- O nó com maior "actual time" total é o gargalo
-- Para nós com loops > 1: actual time é POR LOOP, multiplique

-- 3. Verificar uso de memória
-- "Sort Method: external merge Disk: 234MB" = adicione work_mem para esta sessão
SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT ...;

-- 4. Verificar cache hit ratio
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_ratio
FROM pg_statio_user_tables;
-- Meta: cache_hit_ratio > 95%

5. Tipos Avançados do PostgreSQL {#tipos}

JSONB: O Melhor dos Dois Mundos

-- Criar tabela com JSONB
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(200) NOT NULL,
  metadata JSONB,
  specs JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Inserir dados
INSERT INTO products (name, metadata, specs) VALUES
('iPhone 15 Pro', 
 '{"brand": "Apple", "category": "smartphone", "tags": ["premium", "ios"]}',
 '{"ram": 8, "storage": 256, "camera_mp": 48, "battery_mah": 3274}'),
('Samsung Galaxy S24',
 '{"brand": "Samsung", "category": "smartphone", "tags": ["android", "flagship"]}', 
 '{"ram": 12, "storage": 256, "camera_mp": 50, "battery_mah": 4000}');

-- Queries em JSONB
-- Acessar campo
SELECT name, metadata->>'brand' AS brand FROM products;

-- Filtrar por campo JSONB
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';

-- Filtrar por sub-objeto (containment)
SELECT * FROM products WHERE metadata @> '{"category": "smartphone"}';

-- Filtrar por elemento de array
SELECT * FROM products WHERE metadata->'tags' @> '["premium"]';

-- Spec numérica
SELECT name FROM products WHERE (specs->>'ram')::int >= 12;

-- JSONB com índice GIN para containment
CREATE INDEX idx_products_metadata ON products USING gin(metadata);
CREATE INDEX idx_products_specs ON products USING gin(specs);

-- Path containment - verificar se uma chave existe
SELECT * FROM products WHERE metadata ? 'brand';
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'category'];  -- todas
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'sku'];       -- alguma

-- Construir JSONB dinamicamente
SELECT 
  id,
  jsonb_build_object(
    'id', id,
    'name', name,
    'brand', metadata->>'brand',
    'specs', specs
  ) AS product_json
FROM products;

-- Atualizar campo específico sem reescrever todo o JSON
UPDATE products 
SET specs = jsonb_set(specs, '{storage}', '512')
WHERE id = 'uuid-aqui';

-- Remover campo
UPDATE products 
SET metadata = metadata - 'old_field'
WHERE id = 'uuid-aqui';

-- Merge de objetos JSONB (PostgreSQL 17+)
UPDATE products
SET metadata = metadata || '{"verified": true, "updated_at": "2025-01-01"}'::jsonb
WHERE id = 'uuid-aqui';

Arrays Nativos

-- Tabela com arrays
CREATE TABLE articles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  tags TEXT[],
  related_ids UUID[],
  scores INT[]
);

INSERT INTO articles (title, tags, scores) VALUES
('Intro to PostgreSQL', ARRAY['database', 'tutorial', 'beginner'], ARRAY[95, 87, 92]),
('Advanced Indexing', ARRAY['database', 'performance', 'advanced'], ARRAY[88, 91, 95]);

-- Queries com arrays
-- Elemento em array
SELECT * FROM articles WHERE 'database' = ANY(tags);

-- Array contém todos os elementos
SELECT * FROM articles WHERE tags @> ARRAY['database', 'performance'];

-- Array tem algum elemento em comum
SELECT * FROM articles WHERE tags && ARRAY['tutorial', 'advanced'];

-- Acessar elemento (1-indexed!)
SELECT title, tags[1] AS first_tag FROM articles;

-- Slicing
SELECT tags[1:2] FROM articles;  -- Primeiros 2 elementos

-- Agregar em arrays
SELECT array_agg(id) AS all_ids FROM articles;
SELECT array_agg(DISTINCT category ORDER BY category) FROM products;

-- Unnest: transformar array em linhas
SELECT title, unnest(tags) AS tag FROM articles;

-- Combinando com GROUP BY para contar tags
SELECT tag, COUNT(*) 
FROM articles, unnest(tags) AS tag 
GROUP BY tag 
ORDER BY COUNT(*) DESC;

Date/Time e Range Types

-- Ranges: extremamente úteis para reservas, agendamentos, planos
CREATE TABLE reservations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id UUID NOT NULL,
  guest_name VARCHAR(100) NOT NULL,
  period DATERANGE NOT NULL,
  
  -- Constraint de exclusão: prevenir sobreposição de reservas
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

CREATE INDEX idx_reservations_period ON reservations USING gist(period);

-- Verificar disponibilidade
SELECT room_id FROM reservations
WHERE room_id = 'uuid-quarto'
AND period && '[2025-06-01, 2025-06-07)'::daterange;

-- Encontrar quartos disponíveis para um período
SELECT r.id, r.name FROM rooms r
WHERE NOT EXISTS (
  SELECT 1 FROM reservations res
  WHERE res.room_id = r.id
  AND res.period && '[2025-06-01, 2025-06-07)'::daterange
);

-- Funções de range
SELECT 
  lower(period) AS check_in,
  upper(period) AS check_out,
  upper(period) - lower(period) AS nights,
  period @> '2025-06-03'::date AS includes_june_3
FROM reservations;

-- TSTZRANGE para eventos com hora e timezone
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT,
  duration TSTZRANGE NOT NULL,
  EXCLUDE USING gist (duration WITH &&)
);

6. CTEs e Window Functions: SQL Poderoso {#ctes}

Common Table Expressions (CTEs)

-- CTE básica - legibilidade
WITH 
user_orders AS (
  SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(total) as total_spent,
    MAX(created_at) as last_order_date
  FROM orders
  WHERE status = 'COMPLETED'
  GROUP BY user_id
),
user_categories AS (
  SELECT 
    o.user_id,
    COUNT(DISTINCT oi.product_id) as distinct_products,
    MODE() WITHIN GROUP (ORDER BY p.category_id) as favorite_category
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status = 'COMPLETED'
  GROUP BY o.user_id
)
SELECT 
  u.id,
  u.name,
  u.email,
  uo.total_orders,
  uo.total_spent,
  uo.last_order_date,
  uc.distinct_products,
  uc.favorite_category
FROM users u
JOIN user_orders uo ON uo.user_id = u.id
JOIN user_categories uc ON uc.user_id = u.id
WHERE uo.total_spent > 1000
ORDER BY uo.total_spent DESC;

-- CTEs Recursivas: para dados hierárquicos
-- Exemplo: organograma / categoria pai-filho
WITH RECURSIVE category_tree AS (
  -- Caso base: categorias raiz
  SELECT 
    id,
    name,
    parent_id,
    0 AS depth,
    ARRAY[id] AS path,
    name::TEXT AS full_path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Caso recursivo
  SELECT 
    c.id,
    c.name,
    c.parent_id,
    ct.depth + 1,
    ct.path || c.id,
    ct.full_path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
  WHERE ct.depth < 10  -- Limite para evitar loops infinitos
)
SELECT 
  repeat('  ', depth) || name AS tree_display,
  full_path,
  depth
FROM category_tree
ORDER BY path;

-- CTE com dados de escrita (RETURNING)
WITH inserted_order AS (
  INSERT INTO orders (user_id, total, status)
  VALUES ('uuid-user', 299.99, 'PENDING')
  RETURNING id, user_id, total
),
inserted_items AS (
  INSERT INTO order_items (order_id, product_id, quantity, price)
  SELECT id, 'uuid-product', 2, 149.99
  FROM inserted_order
  RETURNING order_id, product_id, quantity
)
SELECT o.id AS order_id, i.product_id, i.quantity
FROM inserted_order o
JOIN inserted_items i ON i.order_id = o.id;

Window Functions: Análise sem GROUP BY

Window functions são uma das features mais poderosas do SQL moderno. Elas calculam valores sobre um conjunto de linhas relacionadas sem colapsar os resultados em grupos.

-- Ranking de clientes por gasto
SELECT 
  u.name,
  u.email,
  SUM(o.total) AS total_spent,
  RANK() OVER (ORDER BY SUM(o.total) DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY SUM(o.total) DESC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY SUM(o.total) DESC) AS row_num,
  NTILE(4) OVER (ORDER BY SUM(o.total) DESC) AS quartile
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'COMPLETED'
GROUP BY u.id, u.name, u.email;

-- Running totals e médias móveis
SELECT 
  DATE_TRUNC('day', created_at) AS day,
  SUM(total) AS daily_revenue,
  SUM(SUM(total)) OVER (ORDER BY DATE_TRUNC('day', created_at)) AS cumulative_revenue,
  AVG(SUM(total)) OVER (
    ORDER BY DATE_TRUNC('day', created_at)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_days,
  LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('day', created_at)) AS previous_day,
  SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('day', created_at)) AS day_over_day_change
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
  AND status = 'COMPLETED'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

-- Percentil por categoria
SELECT 
  p.category_id,
  p.name,
  p.price,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p2.price) OVER (PARTITION BY p.category_id) AS median_price,
  (p.price - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p2.price) OVER (PARTITION BY p.category_id)) 
    / NULLIF(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p2.price) OVER (PARTITION BY p.category_id), 0) * 100 
    AS pct_above_median,
  MIN(p2.price) OVER (PARTITION BY p.category_id) AS min_in_category,
  MAX(p2.price) OVER (PARTITION BY p.category_id) AS max_in_category
FROM products p
JOIN products p2 ON p2.category_id = p.category_id AND p2.deleted_at IS NULL
WHERE p.deleted_at IS NULL;

-- First/Last value por partição
SELECT 
  user_id,
  order_id,
  created_at,
  total,
  FIRST_VALUE(order_id) OVER (
    PARTITION BY user_id 
    ORDER BY created_at
  ) AS first_order_id,
  LAST_VALUE(order_id) OVER (
    PARTITION BY user_id 
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_id,
  LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_date,
  LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) - created_at AS days_to_next_order
FROM orders
WHERE status = 'COMPLETED'
ORDER BY user_id, created_at;

7. Full-Text Search Nativo do PostgreSQL {#full-text}

O PostgreSQL oferece um motor de full-text search poderoso que elimina a necessidade do Elasticsearch para muitos casos de uso.

-- Configuração para português
SET default_text_search_config = 'portuguese';

-- Criar coluna de busca com trigger para atualização automática
ALTER TABLE products ADD COLUMN search_vector TSVECTOR;

-- Preencher search_vector existente
UPDATE products 
SET search_vector = 
  setweight(to_tsvector('portuguese', COALESCE(name, '')), 'A') ||
  setweight(to_tsvector('portuguese', COALESCE(description, '')), 'B') ||
  setweight(to_tsvector('portuguese', COALESCE(metadata->>'brand', '')), 'C') ||
  setweight(to_tsvector('portuguese', COALESCE(array_to_string(tags, ' '), '')), 'B');

-- Trigger para atualizar automaticamente
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('portuguese', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('portuguese', COALESCE(NEW.description, '')), 'B') ||
    setweight(to_tsvector('portuguese', COALESCE(NEW.metadata->>'brand', '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_search_vector_update
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_search_vector();

-- Índice GIN no search_vector
CREATE INDEX idx_products_search_vector ON products USING gin(search_vector);

-- Busca básica
SELECT name, description,
  ts_rank(search_vector, query) AS rank
FROM products, plainto_tsquery('portuguese', 'smartphone câmera') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- Busca com highlight
SELECT 
  name,
  ts_headline(
    'portuguese',
    description,
    plainto_tsquery('portuguese', 'smartphone câmera'),
    'MaxWords=35, MinWords=15, ShortWord=3, HighlightAll=FALSE, MaxFragments=3, FragmentDelimiter=" ... "'
  ) AS excerpt
FROM products
WHERE search_vector @@ plainto_tsquery('portuguese', 'smartphone câmera')
ORDER BY ts_rank(search_vector, plainto_tsquery('portuguese', 'smartphone câmera')) DESC
LIMIT 10;

-- Busca avançada com operadores
-- AND implícito: plainto_tsquery('gato cachorro') = gato & cachorro
-- OR: query('gato | cachorro')
-- NOT: query('gato & !cachorro')
-- Frase: phraseto_tsquery('smartphone apple')
-- Prefix: to_tsquery('smartphon:*')

SELECT name FROM products
WHERE search_vector @@ to_tsquery('portuguese', 'smartphon:* & câmera:*');

-- Autocomplete com trigramas (extensão pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);

-- Busca fuzzy (tolerante a erros de digitação)
SELECT name, similarity(name, 'iphon') AS sml
FROM products
WHERE similarity(name, 'iphon') > 0.2
ORDER BY sml DESC;

-- LIKE eficiente com trigramas
SELECT * FROM products WHERE name ILIKE '%samsung%';

8. Particionamento de Tabelas {#particionamento}

Para tabelas com dezenas ou centenas de milhões de linhas, o particionamento é essencial para manter a performance.

-- Particionamento por range de data (tabela de eventos/logs)
CREATE TABLE events (
  id UUID DEFAULT gen_random_uuid(),
  event_type VARCHAR(100) NOT NULL,
  user_id UUID,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Criar partições por mês
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatizar criação de partições com função
CREATE OR REPLACE FUNCTION create_monthly_partition(
  parent_table TEXT,
  partition_date DATE
) RETURNS VOID AS $$
DECLARE
  partition_name TEXT;
  start_date TEXT;
  end_date TEXT;
BEGIN
  partition_name := parent_table || '_' || to_char(partition_date, 'YYYY_MM');
  start_date := to_char(date_trunc('month', partition_date), 'YYYY-MM-DD');
  end_date := to_char(date_trunc('month', partition_date) + INTERVAL '1 month', 'YYYY-MM-DD');
  
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
    partition_name, parent_table, start_date, end_date
  );
  
  -- Criar índices na partição
  EXECUTE format(
    'CREATE INDEX ON %I (user_id, created_at DESC)',
    partition_name
  );
  
  RAISE NOTICE 'Partição % criada: % a %', partition_name, start_date, end_date;
END;
$$ LANGUAGE plpgsql;

-- Criar próximos 12 meses de partições
DO $$
DECLARE
  i INT;
BEGIN
  FOR i IN 0..11 LOOP
    PERFORM create_monthly_partition(
      'events', 
      date_trunc('month', NOW() + (i || ' months')::interval)::DATE
    );
  END LOOP;
END $$;

-- Particionamento por hash (distribuição uniforme)
CREATE TABLE user_sessions (
  id UUID DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  token TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Partition pruning (eliminação de partições)
-- O PostgreSQL automaticamente elimina partições irrelevantes
EXPLAIN SELECT * FROM events WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';
-- Vai mostrar apenas events_2024_06 no plano

-- Removendo partições antigas (arquivamento)
-- Sem particionamento: DELETE de 10 milhões de linhas = horas
-- Com particionamento: DROP TABLE events_2023_01 = milissegundos!
ALTER TABLE events DETACH PARTITION events_2023_01;
-- Agora pode arquivar (COPY para arquivo externo) e depois:
DROP TABLE events_2023_01;

9. Tuning de Configuração para Produção {#tuning}

# postgresql.conf — Tuning para servidor de produção 32GB RAM, SSD NVMe

# ==========================================
# Memória
# ==========================================
shared_buffers = 8GB          # 25% da RAM. Cache de páginas do PostgreSQL
effective_cache_size = 24GB   # Estimativa de cache total (RAM - shared_buffers - OS)
work_mem = 64MB               # Memória por operação (sort, hash). CUIDADO: por conexão!
maintenance_work_mem = 2GB    # Para VACUUM, CREATE INDEX, etc.
huge_pages = on               # Huge pages do kernel (TLB miss reduction)

# ==========================================
# Write-Ahead Log (WAL)
# ==========================================
wal_buffers = 64MB            # Padrão (-1) é automático, 64MB é bom para escritas intensas
checkpoint_completion_target = 0.9  # Distribuir writes de checkpoint em 90% do intervalo
checkpoint_timeout = 15min    # Forçar checkpoint a cada 15 minutos
min_wal_size = 1GB
max_wal_size = 4GB

# ==========================================
# Planner
# ==========================================
random_page_cost = 1.1        # Para SSD NVMe (padrão é 4.0 para HDD)
effective_io_concurrency = 200  # Para SSD (padrão é 1 para HDD)
default_statistics_target = 200  # Mais estatísticas (padrão: 100)

# ==========================================
# Conexões e Workers
# ==========================================
max_connections = 200         # Use pgBouncer em produção!
max_worker_processes = 8      # Número de CPUs
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# ==========================================
# Autovacuum
# ==========================================
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.02   # Vacuum após 2% de rows mortas (padrão: 0.2)
autovacuum_analyze_scale_factor = 0.01  # Analyze após 1% de mudanças (padrão: 0.1)
autovacuum_vacuum_cost_delay = 2ms      # Agressividade (menos delay = mais rápido)

# ==========================================
# Logging
# ==========================================
log_min_duration_statement = 1000  # Logar queries > 1 segundo
log_checkpoints = on
log_connections = off          # Em produção com muitas conexões: off
log_lock_waits = on
log_temp_files = 0             # Logar todos os temp files
track_io_timing = on           # Importante para diagnóstico

Connection Pooling com PgBouncer

# pgbouncer.ini
[databases]
learning = host=127.0.0.1 port=5432 dbname=learning

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Transaction mode: mais eficiente, mas sem SET, LISTEN, etc.
pool_mode = transaction

max_client_conn = 1000         # Max conexões dos clientes
default_pool_size = 25         # Conexões reais no PostgreSQL
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Timeouts
connect_timeout = 10
query_timeout = 0              # Sem timeout (configurar no app)
client_idle_timeout = 600
server_idle_timeout = 600

log_pooler_errors = 1
stats_period = 60

10. Padrões de Schema Avançados {#schema}

Temporal Tables: Histórico Completo de Mudanças

-- Implementar auditoria completa sem triggers complexos
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  -- Período de validade do registro
  valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  valid_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity'::timestamptz,
  is_current BOOLEAN GENERATED ALWAYS AS (valid_to = 'infinity'::timestamptz) STORED
);

-- View para versão atual
CREATE VIEW current_products AS
SELECT id, name, price, valid_from
FROM products WHERE is_current = true;

-- Função para atualizar com histórico
CREATE OR REPLACE FUNCTION update_product(
  p_id UUID,
  p_name VARCHAR DEFAULT NULL,
  p_price DECIMAL DEFAULT NULL
) RETURNS VOID AS $$
DECLARE
  v_now TIMESTAMPTZ := NOW();
BEGIN
  -- Fechar registro atual
  UPDATE products
  SET valid_to = v_now
  WHERE id = p_id AND is_current = true;
  
  -- Criar nova versão
  INSERT INTO products (id, name, price, valid_from)
  SELECT 
    p_id,
    COALESCE(p_name, name),
    COALESCE(p_price, price),
    v_now
  FROM products
  WHERE id = p_id AND valid_to = v_now;
END;
$$ LANGUAGE plpgsql;

-- Consultar estado em um ponto no tempo (Time Travel)
SELECT id, name, price
FROM products
WHERE id = 'uuid-produto'
  AND valid_from <= '2024-06-01 12:00:00+00'::timestamptz
  AND valid_to > '2024-06-01 12:00:00+00'::timestamptz;

-- Histórico completo de um produto
SELECT name, price, valid_from, valid_to
FROM products
WHERE id = 'uuid-produto'
ORDER BY valid_from;

Multi-Tenancy com Row Level Security

-- RLS (Row Level Security) para SaaS multi-tenant
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  name TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Habilitar RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Criar política: usuários só veem seus tenant_id
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Para administradores (bypass RLS)
CREATE POLICY admin_all ON projects
  TO app_admin
  USING (true);

-- No código da aplicação, configurar o tenant antes de qualquer query:
-- SET app.current_tenant_id = 'uuid-do-tenant';
-- Ou via connection string parameter

-- Função auxiliar para configurar contexto
CREATE OR REPLACE FUNCTION set_tenant_context(p_tenant_id UUID)
RETURNS VOID AS $$
BEGIN
  PERFORM set_config('app.current_tenant_id', p_tenant_id::text, true);
END;
$$ LANGUAGE plpgsql;

11. Monitoramento e Diagnóstico {#monitoramento}

-- Dashboard de saúde do banco de dados
SELECT 
  -- Cache hit ratio
  ROUND(
    sum(blks_hit)::numeric / NULLIF(sum(blks_hit + blks_read), 0) * 100, 
    2
  ) AS cache_hit_ratio,
  
  -- Transações
  sum(xact_commit) AS commits,
  sum(xact_rollback) AS rollbacks,
  
  -- Tuples
  sum(tup_fetched) AS fetched,
  sum(tup_inserted) AS inserted,
  sum(tup_updated) AS updated,
  sum(tup_deleted) AS deleted,
  
  -- Conflitos e deadlocks
  sum(conflicts) AS conflicts,
  sum(deadlocks) AS deadlocks,
  sum(temp_bytes) / 1024 / 1024 AS temp_mb_used
FROM pg_stat_database
WHERE datname = current_database();

-- Queries ativas agora
SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

-- Locks e deadlocks
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Tamanho de tabelas e índices
SELECT 
  tablename,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

12. Migrações Seguras em Produção {#migracoes}

Migrações em produção são arriscadas. Operações aparentemente simples como ALTER TABLE ADD COLUMN podem bloquear a tabela por segundos em uma tabela pequena, ou minutos em tabelas grandes.

-- ❌ NUNCA fazer em produção em tabelas grandes:
ALTER TABLE orders ADD COLUMN notes TEXT NOT NULL DEFAULT '';  -- Bloqueia!

-- ✅ Forma segura (PostgreSQL 11+):
-- Valores padrão são armazenados nos metadados, não reescrevem a tabela
ALTER TABLE orders ADD COLUMN notes TEXT DEFAULT '';  -- Rápido!
-- Somente atualizar linhas existentes depois (em batches):
UPDATE orders SET notes = '' WHERE notes IS NULL AND id IN (
  SELECT id FROM orders WHERE notes IS NULL LIMIT 10000
);

-- Adicionar NOT NULL sem bloquear:
-- 1. Adicionar a coluna como NULL primeiro
ALTER TABLE orders ADD COLUMN priority INT;

-- 2. Criar constraint NOT VALID (não verifica linhas existentes)
ALTER TABLE orders ADD CONSTRAINT orders_priority_not_null 
  CHECK (priority IS NOT NULL) NOT VALID;

-- 3. Preencher valores em batches (sem lock)
DO $$
DECLARE
  batch_size INT := 10000;
  last_id UUID;
BEGIN
  LOOP
    UPDATE orders 
    SET priority = 1 
    WHERE id IN (
      SELECT id FROM orders 
      WHERE priority IS NULL 
      ORDER BY id 
      LIMIT batch_size
    );
    
    EXIT WHEN NOT FOUND;
    COMMIT;  -- Liberar lock após cada batch
    PERFORM pg_sleep(0.1);  -- Dar respiro para outras transações
  END LOOP;
END $$;

-- 4. Validar constraint (sem bloquear escritas, apenas leituras necessárias)
ALTER TABLE orders VALIDATE CONSTRAINT orders_priority_not_null;

-- Criação de índice sem bloquear:
-- ❌ Bloqueia escrita na tabela:
CREATE INDEX idx_orders_priority ON orders(priority);

-- ✅ Não bloqueia (demora mais, mas não bloqueia):
CREATE INDEX CONCURRENTLY idx_orders_priority ON orders(priority);
-- NUNCA dentro de um bloco de transação (não funciona)

13. Conclusão e Próximos Passos {#conclusao}

O PostgreSQL é um banco de dados extraordinariamente poderoso que continua evoluindo rapidamente. As features cobertas neste artigo representam apenas uma fração do que ele oferece.

Resumo das técnicas mais impactantes:

A combinação de índices adequados (GIN para JSONB, parciais para subconjuntos, covering para index-only scans) com a análise regular via EXPLAIN ANALYZE e pg_stat_statements é o foundation de qualquer PostgreSQL performático em produção.

Window functions e CTEs eliminam a necessidade de múltiplas queries onde uma sofisticada resolve tudo. Particionamento transforma operações de horas em milissegundos para tabelas de bilhões de linhas.

Recursos para continuar aprendendo:

• Documentação oficial do PostgreSQL (postgresql.org/docs) — a melhor documentação de banco de dados existente
• "PostgreSQL: Up and Running" (O'Reilly)
• PGConf.dev — conferência anual dos contribuidores do PostgreSQL
• Planet PostgreSQL (planet.postgresql.org) — agregador de blogs da comunidade
• pgtune.leopard.in.ua — calculadora de configuração baseada no seu hardware

Próximas features do PostgreSQL 17:

• Merge SQL padrão melhorado
• Melhorias significativas no planner para queries paralelas
• Incremental View Maintenance (experimental)
• Melhor suporte a JSON SQL padrão

Publicado em 2025 | Categoria: Banco de Dados | Tags: PostgreSQL, SQL avançado, Performance, Índices, Particionamento, Window Functions, Full-Text Search