SQL Avançado: 200 Comandos de Nível Sênior/DBA

Aqui o SQL encontra a infraestrutura. Estes 200 itens são o que se usa quando o banco já está em produção, com volume, concorrência e alguém no telefone perguntando por que o relatório está lento.

Plano de execução, índices avançados, isolamento, particionamento, replicação, backup, segurança e diagnóstico — com a sintaxe de cada banco onde ela diverge.

Aviso: vários comandos daqui alteram o comportamento do servidor. Teste em homologação antes, e entenda o que cada um faz antes de rodar em produção.

Índice

• 1–18 · Funções de janela avançadas
• 19–30 · Consultas analíticas e recursão
• 31–54 · Plano de execução e otimização
• 55–74 · Índices avançados
• 75–90 · Concorrência, locks e MVCC
• 91–105 · Particionamento
• 106–119 · Replicação e alta disponibilidade
• 120–133 · Backup e recuperação
• 134–146 · Manutenção, vacuum e estatísticas
• 147–159 · Segurança avançada
• 160–170 · Extensões e recursos modernos
• 171–190 · Diagnóstico em produção
• 191–200 · Boas práticas de produção

Funções de janela avançadas

Frames, distribuição e o padrão gaps-and-islands — análise séria sem sair do SQL.

1. Frame ROWS

Define a janela em número de linhas físicas antes/depois da atual.

SELECT dia, valor,
  AVG(valor) OVER (ORDER BY dia ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7d
FROM metricas;

2. Frame RANGE

Define a janela por valor, não por posição: empates entram juntos.

SELECT valor, SUM(valor) OVER (ORDER BY valor RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM vendas;

3. Frame GROUPS

Conta grupos de valores iguais como uma unidade (PostgreSQL 11+).

SELECT dia, SUM(valor) OVER (ORDER BY dia GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM metricas;

4. EXCLUDE no frame

Remove a linha atual (ou o grupo dela) do cálculo — média dos outros, não a sua.

SELECT id, AVG(nota) OVER (PARTITION BY turma ORDER BY id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS media_dos_outros
FROM provas;

5. PERCENT_RANK

Posição relativa de 0 a 1 dentro da partição.

SELECT nome, total, ROUND(PERCENT_RANK() OVER (ORDER BY total)::numeric, 3) AS percentil
FROM vendedores;

6. CUME_DIST

Distribuição acumulada: proporção de linhas com valor menor ou igual.

SELECT nome, CUME_DIST() OVER (ORDER BY salario) AS acumulada FROM funcionarios;

7. PERCENTILE_CONT

Percentil interpolado — a mediana verdadeira, insensível a outliers.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS mediana FROM pedidos;

8. PERCENTILE_DISC

Percentil discreto: devolve um valor que existe de fato no conjunto.

SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY duracao_ms) AS p90 FROM requisicoes;

9. MODE

Valor mais frequente do grupo.

SELECT MODE() WITHIN GROUP (ORDER BY categoria) AS mais_vendida FROM pedidos;

10. NTH_VALUE

Pega o n-ésimo valor da janela.

SELECT cliente_id, NTH_VALUE(total, 2) OVER (PARTITION BY cliente_id ORDER BY criado_em) AS segundo_pedido
FROM pedidos;

11. Gaps and islands

Detecta sequências contínuas: a diferença entre a data e o ROW_NUMBER é constante dentro de uma ilha.

SELECT usuario_id, MIN(dia) AS inicio, MAX(dia) AS fim, COUNT(*) AS dias_seguidos
FROM (
  SELECT usuario_id, dia,
         dia - (ROW_NUMBER() OVER (PARTITION BY usuario_id ORDER BY dia))::int AS grupo
  FROM acessos
) ace_num
GROUP BY usuario_id, grupo;

12. Sessionização

Agrupa eventos em sessões quando o intervalo entre eles passa de um limite.

SELECT *, SUM(nova_sessao) OVER (PARTITION BY usuario_id ORDER BY quando) AS sessao
FROM (
  SELECT *, CASE WHEN quando - LAG(quando) OVER (PARTITION BY usuario_id ORDER BY quando)
                 > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS nova_sessao
  FROM eventos
) eve_marcado;

13. Retenção mês a mês

Compara o conjunto de usuários entre períodos com window + join.

WITH usu_mes AS (
  SELECT DISTINCT usuario_id, DATE_TRUNC('month', quando) AS mes
  FROM eventos
)
SELECT atual.mes,
       COUNT(*) FILTER (WHERE seg.usuario_id IS NOT NULL) AS retidos
FROM usu_mes atual
LEFT JOIN usu_mes seg
ON seg.usuario_id = atual.usuario_id
AND seg.mes = atual.mes + INTERVAL '1 month'
GROUP BY atual.mes
ORDER BY atual.mes;

14. Top N por grupo com empate

RANK em vez de ROW_NUMBER quando empates devem entrar juntos.

SELECT *
FROM (
  SELECT prod.*,
         RANK() OVER (PARTITION BY prod.categoria ORDER BY prod.vendas DESC) AS posicao
  FROM produtos prod
) prod_rank
WHERE posicao <= 3;

15. Diferença para o líder do grupo

Compara cada linha com o melhor da sua partição.

SELECT nome, categoria, vendas,
  MAX(vendas) OVER (PARTITION BY categoria) - vendas AS distancia_do_topo
FROM produtos;

16. Total móvel por tempo

RANGE com INTERVAL cria janelas por tempo real, não por número de linhas.

SELECT quando, valor,
  SUM(valor) OVER (ORDER BY quando RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS ultimos_7d
FROM transacoes;

17. Preencher valor ausente

Repete o último valor conhecido (last value carried forward).

SELECT dia, cotacao,
  COALESCE(cotacao, (array_agg(cotacao) FILTER (WHERE cotacao IS NOT NULL)
    OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING))[COUNT(cotacao) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING)]) AS preenchida
FROM cotacoes;

18. Custo da janela

Cada janela distinta pode gerar um sort. Reaproveite a mesma cláusula OVER sempre que possível.

-- Um sort só: mesma janela nomeada
SELECT SUM(v) OVER w, AVG(v) OVER w, COUNT(*) OVER w
FROM t WINDOW w AS (PARTITION BY g ORDER BY d);

Consultas analíticas e recursão

Agregações multidimensionais e travessia de grafos direto no banco.

19. GROUPING SETS

Vários GROUP BY em uma consulta só, sem UNION ALL.

SELECT regiao, produto, SUM(valor)
FROM vendas
GROUP BY GROUPING SETS ((regiao, produto), (regiao), ());

20. ROLLUP

Subtotais hierárquicos + total geral.

SELECT ano, mes, SUM(valor) FROM vendas GROUP BY ROLLUP (ano, mes);

21. CUBE

Todas as combinações possíveis de subtotais.

SELECT regiao, canal, SUM(valor) FROM vendas GROUP BY CUBE (regiao, canal);

22. GROUPING()

Diz se a linha é um subtotal — evita confundir com NULL de dado.

SELECT regiao, GROUPING(regiao) AS eh_total, SUM(valor)
FROM vendas GROUP BY ROLLUP (regiao);

23. PIVOT

Linhas viram colunas. Nativo no SQL Server e Oracle; no PostgreSQL use CASE ou crosstab.

SELECT *
FROM vendas ven
PIVOT (SUM(ven.valor) FOR ven.mes IN ([1],[2],[3])) AS ven_pivot;   -- SQL Server

24. UNPIVOT

Colunas viram linhas — normaliza planilha importada.

SELECT produto, mes, valor
FROM vendas_larga ven_larga
UNPIVOT (valor FOR mes IN (jan, fev, mar)) AS ven_normalizada;   -- SQL Server / Oracle

25. crosstab (PostgreSQL)

Pivô dinâmico via extensão tablefunc.

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab('SELECT produto, mes, valor FROM vendas ORDER BY 1,2')
  AS t(produto TEXT, jan NUMERIC, fev NUMERIC);

26. Travessia de grafo

CTE recursiva percorre relacionamentos em profundidade arbitrária.

WITH RECURSIVE caminho AS (
  SELECT origem, destino, 1 AS saltos FROM rotas WHERE origem = 'GRU'
  UNION ALL
  SELECT cam.origem, rot.destino, cam.saltos + 1
  FROM caminho cam
  INNER JOIN rotas rot
  ON rot.origem = cam.destino
  WHERE cam.saltos < 4
)
SELECT DISTINCT destino,
                MIN(saltos)
FROM caminho
GROUP BY destino;

27. Detectar ciclo na recursão

Carregue o caminho percorrido e pare quando repetir um nó.

WITH RECURSIVE arvore AS (
  SELECT id, pai_id, ARRAY[id] AS caminho
  FROM nos
  WHERE pai_id IS NULL
  UNION ALL
  SELECT nos.id, nos.pai_id, arv.caminho || nos.id
  FROM nos
  INNER JOIN arvore arv
  ON nos.pai_id = arv.id
  WHERE NOT nos.id = ANY(arv.caminho)
)
SELECT *
FROM arvore;

28. CYCLE — detecção nativa

PostgreSQL 14+ marca ciclos sem você montar o array na mão.

WITH RECURSIVE arvore AS (
  SELECT id, pai_id
  FROM nos
  WHERE pai_id IS NULL
  UNION ALL
  SELECT nos.id, nos.pai_id
  FROM nos
  INNER JOIN arvore arv
  ON nos.pai_id = arv.id
) CYCLE id SET tem_ciclo USING caminho
SELECT *
FROM arvore;

29. SEARCH — ordem da travessia

Controla se a recursão é em largura ou profundidade.

WITH RECURSIVE t AS (...) SEARCH DEPTH FIRST BY id SET ordem
SELECT * FROM t ORDER BY ordem;

30. Explosão de estrutura (BOM)

Lista de materiais: componentes de componentes, com quantidade acumulada.

WITH RECURSIVE bom AS (
  SELECT peca_id, componente_id, qtd FROM estrutura WHERE peca_id = 100
  UNION ALL
  SELECT bom.peca_id, estr.componente_id, bom.qtd * estr.qtd
  FROM bom
  INNER JOIN estrutura estr
  ON estr.peca_id = bom.componente_id
)
SELECT componente_id,
       SUM(qtd)
FROM bom
GROUP BY componente_id;

Plano de execução e otimização

Parar de adivinhar. O plano diz exatamente por onde o tempo está indo.

31. EXPLAIN

Mostra o plano que o otimizador pretende usar, sem executar.

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

32. EXPLAIN ANALYZE

Executa de verdade e compara estimativa com realidade. É o comando que resolve a maioria dos casos.

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 42;

33. EXPLAIN com buffers

Mostra leitura de cache e de disco — separa problema de I/O de problema de CPU.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM pedidos WHERE total > 1000;

34. Plano em formato JSON

Formato ideal para ferramentas de visualização de plano.

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM pedidos;

35. Seq Scan

Varredura completa. Ruim para pouca linha; ótimo quando você lê quase tudo mesmo.

-- Se aparece Seq Scan em filtro seletivo, falta índice (ou a estatística está velha).

36. Index Scan × Index Only Scan

Index Only Scan nem toca na tabela: todas as colunas vieram do índice.

CREATE INDEX idx_cob ON pedidos (cliente_id) INCLUDE (total);
EXPLAIN SELECT cliente_id, total FROM pedidos WHERE cliente_id = 1;

37. Bitmap Scan

Combina vários índices ou lê muitas linhas espalhadas de forma ordenada por página.

EXPLAIN SELECT * FROM pedidos WHERE status = 'pago' AND cliente_id < 500;

38. Nested Loop

Bom quando um lado é pequeno e o outro tem índice. Péssimo quando os dois são grandes.

-- Nested Loop com milhões de linhas dos dois lados = query travada

39. Hash Join

Monta uma tabela hash do lado menor. Rápido para volumes grandes, gasta memória.

SET work_mem = '128MB';  -- hash cabendo em memória evita ida ao disco

40. Merge Join

Junta dois conjuntos já ordenados. Ótimo quando os índices já entregam a ordem.

EXPLAIN
SELECT *
FROM pedidos ped
INNER JOIN itens ite
ON ite.pedido_id = ped.id;   -- procure Merge Join no plano

41. Estimativa × realidade

rows=1000 estimado contra actual rows=2000000 é o sintoma clássico de estatística desatualizada.

ANALYZE pedidos;   -- e rode o EXPLAIN ANALYZE de novo

42. Custo do plano

Custo é uma unidade relativa do otimizador, não segundos. Serve para comparar planos entre si.

EXPLAIN SELECT * FROM pedidos;  -- cost=0.00..1234.56

43. Sort e spill em disco

external merge Disk no plano significa que faltou work_mem.

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos ORDER BY total;

44. SARGable

Filtro que preserva o índice. Função aplicada na coluna quebra isso.

-- Não usa índice
WHERE UPPER(nome) = 'ANA'
-- Usa (com índice por expressão)
CREATE INDEX ON clientes (UPPER(nome));

45. Evite SELECT *

Colunas a mais impedem Index Only Scan e trafegam dado inútil.

SELECT id, total FROM pedidos WHERE cliente_id = 1;   -- e não SELECT *

46. OR que mata o índice

OR entre colunas diferentes costuma virar Seq Scan. UNION ALL resolve.

SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a <> 1;

47. Paginação com OFFSET grande

OFFSET 100000 lê e descarta 100 mil linhas. Pagine por chave (keyset pagination).

-- Lento
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 100000;

-- Rápido
SELECT * FROM pedidos WHERE id > 100000 ORDER BY id LIMIT 20;

48. EXISTS em vez de COUNT

Para saber se existe, pare na primeira linha.

-- Ruim
SELECT COUNT(*) FROM pedidos WHERE cliente_id = 1;
-- Bom
SELECT EXISTS (SELECT 1 FROM pedidos WHERE cliente_id = 1);

49. Problema N+1

Mil consultas de uma linha custam muito mais que uma consulta de mil linhas.

SELECT * FROM pedidos WHERE cliente_id = ANY($1);   -- um round-trip só

50. Materializar resultado intermediário

Cálculo pesado reaproveitado várias vezes merece tabela temporária ou view materializada.

CREATE TEMP TABLE base AS SELECT ... ;
ANALYZE base;

51. Forçar ou desabilitar operador

Para diagnóstico: desabilite um método e veja se o plano alternativo é melhor.

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

52. Hints (Oracle e MySQL)

Alguns bancos permitem instruir o otimizador diretamente. Último recurso.

SELECT /*+ INDEX(p idx_pedidos_cliente) */ * FROM pedidos ped WHERE cliente_id = 1;

53. Timeout de consulta

Impede que uma query ruim monopolize o servidor.

SET statement_timeout = '30s';

54. Plano em produção × homologação

Volume e estatísticas diferentes geram planos diferentes. Sempre valide com dados realistas.

-- Compare EXPLAIN dos dois ambientes antes de concluir que 'está rápido aqui'.

Índices avançados

Além do B-tree: estruturas certas para cada tipo de busca.

55. B-tree

O padrão: igualdade, intervalo e ordenação. Resolve 90% dos casos.

CREATE INDEX idx_padrao ON pedidos (criado_em);

56. Hash

Só igualdade, sem ordenação. Nicho estreito no PostgreSQL moderno.

CREATE INDEX idx_hash ON sessoes USING HASH (token);

57. GIN

Para valores compostos: arrays, JSONB e busca textual.

CREATE INDEX idx_gin ON eventos USING GIN (payload);

58. GiST

Estruturas geométricas, intervalos e vizinhança.

CREATE INDEX idx_gist ON reservas USING GIST (periodo);

59. BRIN

Minúsculo, para tabelas gigantes com dado naturalmente ordenado (séries temporais).

CREATE INDEX idx_brin ON logs USING BRIN (criado_em);

60. SP-GiST

Estruturas particionadas: dados não balanceados, prefixos, quadtrees.

CREATE INDEX idx_spgist ON pontos USING SPGIST (coord);

61. Índice de texto completo

Busca por palavras com ranking, não por LIKE.

CREATE INDEX idx_busca ON artigos USING GIN (to_tsvector('portuguese', titulo || ' ' || corpo));
SELECT * FROM artigos WHERE to_tsvector('portuguese', corpo) @@ plainto_tsquery('portuguese', 'banco de dados');

62. Trigramas

Acelera LIKE '%meio%' e busca por similaridade.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_trgm ON clientes USING GIN (nome gin_trgm_ops);
SELECT * FROM clientes WHERE nome ILIKE '%silva%';

63. Índice de similaridade

Ordena por proximidade textual — busca tolerante a erro de digitação.

SELECT cli.nome,
       similarity(cli.nome, 'jonatan') AS similaridade
FROM clientes cli
ORDER BY similaridade DESC
LIMIT 10;

64. Índice clusterizado

No SQL Server e MySQL/InnoDB, a tabela é fisicamente ordenada pela chave primária.

CREATE CLUSTERED INDEX ix_pedidos ON pedidos (criado_em);   -- SQL Server

65. CLUSTER (PostgreSQL)

Reordena fisicamente a tabela segundo um índice. Melhora leitura por faixa; precisa refazer periodicamente.

CLUSTER pedidos USING idx_pedidos_data;

66. Fator de preenchimento

Deixa espaço livre na página para updates, reduzindo fragmentação.

CREATE INDEX idx_x ON t (col) WITH (fillfactor = 80);

67. Índice em coluna com pouca variedade

Coluna com 2 valores raramente compensa — a não ser em índice parcial sobre o valor raro.

CREATE INDEX idx_erro ON logs (criado_em) WHERE nivel = 'ERROR';

68. Detectar índices não usados

Zero varredura em produção = candidato a remoção.

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;

69. Detectar índices duplicados

Mesma coluna inicial em vários índices costuma indicar redundância.

SELECT indrelid::regclass, array_agg(indexrelid::regclass)
FROM pg_index GROUP BY indrelid, indkey HAVING COUNT(*) > 1;

70. Inchaço de índice

Índice muito atualizado incha e perde eficiência. REINDEX CONCURRENTLY resolve sem downtime.

REINDEX INDEX CONCURRENTLY idx_pedidos_cliente;

71. Índice para ORDER BY + LIMIT

Se o índice já entrega a ordem, o banco lê só as primeiras linhas.

CREATE INDEX idx_top ON pedidos (criado_em DESC);
SELECT * FROM pedidos ORDER BY criado_em DESC LIMIT 10;

72. Índice composto na ordem certa

Igualdade primeiro, intervalo depois. (status, criado_em) serve status = X AND criado_em > Y.

CREATE INDEX idx_ordem ON pedidos (status, criado_em);

73. Índice em coluna nula

Se você filtra IS NULL com frequência, um índice parcial fica muito menor.

CREATE INDEX idx_sem_processar ON pedidos (id) WHERE processado_em IS NULL;

74. Custo de manutenção

Meça o impacto na escrita: índice que acelera um relatório e atrasa 10 mil INSERTs por minuto pode não valer.

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;

Concorrência, locks e MVCC

O que acontece quando mil sessões querem a mesma linha.

75. MVCC

Cada transação vê um retrato consistente do banco; leitura não bloqueia escrita.

-- PostgreSQL e Oracle usam MVCC por padrão:
-- leitores não travam escritores e vice-versa.

76. Anomalia: leitura não repetível

A mesma consulta devolve valores diferentes dentro da transação. Some em REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

77. Anomalia: leitura fantasma

Novas linhas aparecem no meio da transação. Some em SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

78. Write skew

Duas transações leem, validam e gravam — cada uma correta sozinha, juntas violam a regra. Só SERIALIZABLE evita.

-- Dois médicos de plantão saindo ao mesmo tempo: cada transação vê o outro no plantão.

79. Lock de linha

FOR UPDATE trava as linhas até o fim da transação.

SELECT * FROM contas WHERE id = 1 FOR UPDATE;

80. FOR NO KEY UPDATE

Lock mais fraco: permite outras operações que não alteram a chave.

SELECT * FROM pedidos WHERE id = 1 FOR NO KEY UPDATE;

81. FOR SHARE

Impede alteração, mas permite outras leituras travadas.

SELECT * FROM produtos WHERE id = 1 FOR SHARE;

82. LOCK TABLE

Trava a tabela inteira. Use com muita parcimônia e transação curtíssima.

BEGIN;
LOCK TABLE inventario IN EXCLUSIVE MODE;
-- operação crítica
COMMIT;

83. Advisory lock

Lock nomeado pela aplicação, sem tabela envolvida. Garante que só uma instância roda a rotina.

SELECT pg_try_advisory_lock(12345);
-- rotina exclusiva
SELECT pg_advisory_unlock(12345);

84. Ver locks ativos

Diagnóstico de travamento: quem segura o quê.

SELECT loc.pid,
       loc.mode,
       loc.granted,
       cla.relname
FROM pg_locks loc
LEFT JOIN pg_class cla
ON cla.oid = loc.relation
WHERE NOT loc.granted;

85. Descobrir quem bloqueia quem

Mostra a cadeia de bloqueio direto.

SELECT pid, pg_blocking_pids(pid) AS bloqueado_por, query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

86. lock_timeout

Desiste de esperar o lock em vez de acumular fila.

SET lock_timeout = '5s';

87. idle_in_transaction_timeout

Mata transação aberta e esquecida — a maior causa de lock e de inchaço.

SET idle_in_transaction_session_timeout = '60s';

88. Fila de trabalho concorrente

SKIP LOCKED deixa vários workers consumirem a mesma tabela sem colidir.

UPDATE fila SET status = 'processando'
WHERE id = (SELECT id FROM fila WHERE status = 'pendente'
            ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING *;

89. Retry em serialization failure

Em SERIALIZABLE, a aplicação precisa repetir a transação abortada. Não é bug, é o contrato.

-- Código da aplicação: capture SQLSTATE 40001 e tente de novo (com backoff).

90. Ordem consistente de acesso

Deadlock quase sempre vem de ordens diferentes. Padronize a ordem das tabelas e das chaves.

-- Sempre: contas (menor id primeiro) -> lancamentos

Particionamento

Quando a tabela fica grande demais para ser tratada como uma coisa só.

91. Particionar por intervalo

O caso mais comum: uma partição por mês de dados temporais.

CREATE TABLE eventos (id BIGSERIAL, criado_em DATE NOT NULL, dados JSONB)
PARTITION BY RANGE (criado_em);

92. Criar a partição

Cada faixa vira uma tabela física própria.

CREATE TABLE eventos_2026_01 PARTITION OF eventos
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

93. Particionar por lista

Uma partição por valor discreto: região, país, tenant.

CREATE TABLE clientes (id BIGINT, regiao TEXT) PARTITION BY LIST (regiao);
CREATE TABLE clientes_sul PARTITION OF clientes FOR VALUES IN ('PR','SC','RS');

94. Particionar por hash

Distribui uniformemente quando não há critério natural.

CREATE TABLE sessoes (id BIGINT) PARTITION BY HASH (id);
CREATE TABLE sessoes_0 PARTITION OF sessoes FOR VALUES WITH (MODULUS 4, REMAINDER 0);

95. Partição padrão

Recebe o que não casa com nenhuma faixa — evita erro de inserção.

CREATE TABLE eventos_outros PARTITION OF eventos DEFAULT;

96. Partition pruning

O ganho real: o banco lê só as partições que o filtro alcança. Confirme no EXPLAIN.

EXPLAIN SELECT * FROM eventos WHERE criado_em >= DATE '2026-01-01';

97. Índice em tabela particionada

Criado no pai, é propagado para todas as partições.

CREATE INDEX idx_eventos_data ON eventos (criado_em);

98. DETACH — arquivar

Desanexa a partição antiga sem apagar: vira uma tabela normal para exportar.

ALTER TABLE eventos DETACH PARTITION eventos_2025_01;

99. ATTACH — anexar

Traz uma tabela existente para dentro da partição (valida a faixa).

ALTER TABLE eventos ATTACH PARTITION eventos_2026_02
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

100. DROP de partição

Apagar dado antigo vira operação instantânea, sem DELETE de milhões de linhas.

DROP TABLE eventos_2024_01;

101. Criar partições automaticamente

Agende a criação com antecedência: partição faltando derruba a inserção.

-- Rotina mensal que cria os próximos 3 meses.
-- Ou use pg_partman.

102. Chave de partição na PK

A chave de partição precisa fazer parte da chave primária e das constraints únicas.

CREATE TABLE eventos (id BIGINT, criado_em DATE, PRIMARY KEY (id, criado_em))
PARTITION BY RANGE (criado_em);

103. Quando não particionar

Abaixo de dezenas de milhões de linhas, um bom índice costuma resolver melhor e sem complexidade.

-- Particione por necessidade de manutenção (purga, arquivamento), não por moda.

104. Sharding

Particionamento entre servidores diferentes. Ganha escala de escrita, perde JOIN e transação global.

-- Escolha a chave de shard com muito cuidado: mudá-la depois é reescrever o sistema.

105. Tabela estrangeira

Com postgres_fdw, uma partição pode viver em outro servidor (sharding declarativo).

CREATE EXTENSION postgres_fdw;
CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db2', dbname 'loja');

Replicação e alta disponibilidade

Como o banco sobrevive a uma máquina que morre — e como distribuir leitura.

106. WAL — o log de transações

Toda alteração vira registro no WAL antes de ir para a tabela. É a base de durabilidade, replicação e PITR.

SHOW wal_level;   -- 'replica' ou 'logical' para replicar

107. Replicação física

A réplica aplica o WAL byte a byte: cópia idêntica do cluster inteiro.

-- Na réplica
pg_basebackup -h primario -U replicador -D /var/lib/postgresql/data -R -P

108. Replicação lógica

Replica por tabela, entre versões diferentes e com transformação. Base de migração sem downtime.

-- Origem
CREATE PUBLICATION pub_vendas FOR TABLE pedidos, itens;

-- Destino
CREATE SUBSCRIPTION sub_vendas
  CONNECTION 'host=origem dbname=loja user=repl'
  PUBLICATION pub_vendas;

109. Replicação síncrona

O COMMIT só volta depois que a réplica confirmou. Zero perda, mais latência.

ALTER SYSTEM SET synchronous_standby_names = 'replica1';
SELECT pg_reload_conf();

110. Replicação assíncrona

Commit rápido, com risco de perder as últimas transações num failover.

ALTER SYSTEM SET synchronous_commit = 'off';   -- avalie o risco antes

111. Medir o atraso da réplica

Lag alto significa leitura desatualizada e failover mais arriscado.

SELECT now() - pg_last_xact_replay_timestamp() AS atraso;

112. Réplica de leitura

Direcione relatórios para a réplica e alivie o primário.

-- A aplicação usa duas conexões: escrita no primário, leitura na réplica.
SELECT pg_is_in_recovery();   -- true = é réplica

113. Promover réplica

Failover: a réplica vira primário.

SELECT pg_promote();

114. Slot de replicação

Garante que o primário não descarte WAL que a réplica ainda não consumiu — e enche o disco se a réplica sumir.

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot('slot_orfao');

115. Failover automático

Ferramentas como Patroni, repmgr e pg_auto_failover cuidam da eleição e do redirecionamento.

-- Sem orquestrador, failover é manual: alguém precisa promover e reapontar a aplicação.

116. Split-brain

Dois primários aceitando escrita ao mesmo tempo. Fencing e quórum existem para evitar isso.

-- Nunca promova manualmente sem garantir que o antigo primário está isolado.

117. AlwaysOn e Data Guard

Equivalentes no SQL Server (Availability Groups) e Oracle (Data Guard).

-- SQL Server
ALTER AVAILABILITY GROUP ag1 FAILOVER;

118. Replicação no MySQL

Baseada em binlog, com GTID para posicionamento confiável.

CHANGE REPLICATION SOURCE TO SOURCE_HOST='primario', SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

119. Connection pooling

Banco não gosta de milhares de conexões. PgBouncer economiza memória e estabiliza a latência.

-- pgbouncer.ini
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 20

Backup e recuperação

Backup que nunca foi restaurado não é backup — é esperança.

120. Dump lógico

Exporta o banco como comandos SQL. Portátil entre versões e arquiteturas.

pg_dump -h localhost -U app -Fc loja > loja.dump

121. Restaurar dump

Restaura o arquivo gerado, com paralelismo quando o formato permite.

pg_restore -h localhost -U app -d loja -j 4 loja.dump

122. Dump de todo o cluster

Inclui usuários, roles e permissões — o que o pg_dump de um banco não traz.

pg_dumpall -h localhost -U postgres > cluster.sql

123. Dump só do schema

Útil para comparar estrutura entre ambientes.

pg_dump --schema-only -U app loja > schema.sql

124. Backup físico

Cópia dos arquivos do cluster; muito mais rápido para restaurar bases grandes.

pg_basebackup -h localhost -U replicador -D /backup/base -Fp -Xs -P

125. Archive de WAL

Guardar os segmentos de WAL é o que permite recuperar em qualquer ponto no tempo.

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'cp %p /backup/wal/%f';

126. PITR — recuperar a um instante

Restaura o backup base e reaplica o WAL até o segundo anterior ao incidente.

-- postgresql.conf da restauração
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-08-10 14:29:00'

127. Backup no MySQL

mysqldump para lógico; Percona XtraBackup para físico a quente.

mysqldump --single-transaction --routines --triggers loja > loja.sql

128. Backup no SQL Server

Full, diferencial e de log formam a cadeia de recuperação.

BACKUP DATABASE loja TO DISK = 'D:\bkp\loja.bak' WITH COMPRESSION;
BACKUP LOG loja TO DISK = 'D:\bkp\loja_log.trn';

129. Backup no Oracle

RMAN gerencia backup, catálogo e recuperação.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

130. Backup no Firebird

gbak faz backup lógico; nbackup faz incremental físico.

gbak -b -user SYSDBA -password senha /dados/loja.fdb /backup/loja.fbk

131. Testar a restauração

Agende um restore periódico em máquina separada. É o único teste que conta.

# cron mensal: restaura o último backup e roda uma consulta de sanidade
pg_restore -d loja_teste ultimo.dump && psql -d loja_teste -c 'SELECT COUNT(*) FROM pedidos;'

132. Retenção 3-2-1

Três cópias, em dois tipos de mídia, uma fora do site. Vale para banco também.

-- Diário local (7 dias) + semanal em object storage (8 semanas) + mensal offsite (12 meses)

133. Backup antes de migração

Toda migração estrutural começa com um backup verificado e um plano de rollback escrito.

pg_dump -Fc loja > pre-migracao-$(date +%F).dump

Manutenção, vacuum e estatísticas

O banco precisa de faxina. Ignorar isso é acumular dívida até a parada.

134. VACUUM

Marca como reutilizável o espaço das versões antigas de linha (MVCC).

VACUUM pedidos;

135. VACUUM FULL

Reescreve a tabela e devolve espaço ao sistema — com lock exclusivo. Só em janela de manutenção.

VACUUM FULL pedidos;

136. Autovacuum

Roda sozinho. O erro comum é deixá-lo conservador demais em tabelas de alta escrita.

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.02);

137. Table bloat

Espaço morto acumulado. Sintoma: tabela cresce e a consulta desacelera sem aumento de dado útil.

SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

138. Transaction ID wraparound

Risco real do PostgreSQL: sem vacuum, o contador de transações estoura e o banco entra em modo protegido.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

139. ANALYZE

Recalcula estatísticas de distribuição usadas pelo otimizador.

ANALYZE VERBOSE pedidos;

140. Estatísticas estendidas

Ensina ao otimizador que duas colunas são correlacionadas (cidade e estado, por exemplo).

CREATE STATISTICS st_cidade_estado (dependencies) ON cidade, estado FROM clientes;
ANALYZE clientes;

141. Aumentar a amostragem

Coluna com distribuição irregular merece histograma mais detalhado.

ALTER TABLE pedidos ALTER COLUMN status SET STATISTICS 1000;
ANALYZE pedidos;

142. REINDEX CONCURRENTLY

Reconstrói índices inchados sem bloquear escrita.

REINDEX TABLE CONCURRENTLY pedidos;

143. Manutenção no MySQL

OPTIMIZE TABLE reorganiza; ANALYZE atualiza estatísticas.

OPTIMIZE TABLE pedidos;
ANALYZE TABLE pedidos;

144. Manutenção no SQL Server

Rebuild/reorganize de índice e atualização de estatísticas.

ALTER INDEX ALL ON pedidos REBUILD;
UPDATE STATISTICS pedidos;

145. Manutenção no Firebird

Sweep remove versões antigas; backup/restore compacta o arquivo.

gfix -sweep -user SYSDBA -password senha /dados/loja.fdb

146. Janela de manutenção

Agende para o vale de uso, monitore o tempo de execução e tenha plano de aborto.

SET statement_timeout = '2h';   -- limite explícito para a rotina pesada

Segurança avançada

Dado sensível exige mais que usuário e senha.

147. Row Level Security

Filtra linhas por política, dentro do próprio banco.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON pedidos USING (tenant_id = current_setting('app.tenant')::int);

148. Política por operação

Regras diferentes para leitura e escrita.

CREATE POLICY p_insert ON pedidos FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant')::int);

149. FORCE ROW LEVEL SECURITY

Aplica a política até para o dono da tabela.

ALTER TABLE pedidos FORCE ROW LEVEL SECURITY;

150. Criptografia em repouso

TDE no SQL Server/Oracle, LUKS/dm-crypt no disco, ou criptografia por campo na aplicação.

-- SQL Server
ALTER DATABASE loja SET ENCRYPTION ON;

151. Criptografia por campo

Só quem tem a chave lê o dado — protege até de um dump vazado.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
INSERT INTO clientes (cpf_cifrado) VALUES (pgp_sym_encrypt('123.456.789-00', 'chave'));
SELECT pgp_sym_decrypt(cpf_cifrado, 'chave') FROM clientes;

152. Hash de senha

Senha nunca é criptografia reversível: é hash com sal e custo alto (bcrypt, argon2).

SELECT crypt('senha-do-usuario', gen_salt('bf', 12));

153. Conexão TLS obrigatória

Sem TLS, credencial e dado trafegam legíveis na rede.

-- pg_hba.conf
hostssl  all  all  0.0.0.0/0  scram-sha-256

154. SCRAM em vez de MD5

Método de autenticação moderno do PostgreSQL.

ALTER SYSTEM SET password_encryption = 'scram-sha-256';

155. Auditoria

Registrar quem leu e alterou dado sensível — exigência de LGPD em muitos cenários.

-- pgaudit
ALTER SYSTEM SET pgaudit.log = 'write, ddl';

156. Mascaramento de dados

Ambiente de teste não deve ter PII real. Mascare na cópia.

UPDATE clientes SET email = 'user' || id || '@exemplo.com', cpf = NULL;

157. SQL injection

A defesa é consulta parametrizada. Concatenar string com entrada do usuário é a origem de quase todo vazamento.

-- Vulnerável
-- 'SELECT * FROM users WHERE email = ''' + entrada + ''''

-- Seguro
SELECT * FROM users WHERE email = $1;

158. SQL injection em código do banco

Function com SQL dinâmico também é vulnerável. Use quote_ident e quote_literal.

EXECUTE format('SELECT * FROM %I WHERE nome = %L', tabela, valor);

159. Princípio do menor privilégio

A aplicação não cria tabela, não dropa nada e não lê o que não precisa.

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_web;

Extensões e recursos modernos

O que o banco ganhou nos últimos anos e muita gente ainda não usa.

160. pg_stat_statements

A extensão mais valiosa para performance: agrega tempo por consulta normalizada.

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

161. PostGIS

Dados geográficos com índice espacial e cálculo de distância real.

CREATE EXTENSION postgis;
SELECT nome FROM lojas
ORDER BY geom <-> ST_MakePoint(-47.06, -22.90)::geography LIMIT 5;

162. TimescaleDB

Séries temporais em escala: hipertabelas, compressão e agregação contínua.

SELECT create_hypertable('metricas', 'coletado_em');

163. pgvector

Busca por similaridade vetorial — base de RAG e recomendação.

CREATE EXTENSION vector;
CREATE TABLE docs (id BIGSERIAL, embedding vector(1536));
SELECT * FROM docs ORDER BY embedding <-> $1 LIMIT 5;

164. Foreign Data Wrapper

Consulta outro banco (ou CSV, ou API) como se fosse tabela local.

CREATE EXTENSION postgres_fdw;
IMPORT FOREIGN SCHEMA public FROM SERVER outro INTO externo;

165. Colunas geradas

Coluna calculada e armazenada pelo banco, sempre coerente.

ALTER TABLE itens ADD COLUMN total NUMERIC GENERATED ALWAYS AS (qtd * preco) STORED;

166. IDENTITY padrão

Substituto moderno do SERIAL, conforme o padrão SQL.

CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

167. MERGE

Comando padrão para inserir/atualizar/apagar em uma passada (PostgreSQL 15+, Oracle, SQL Server).

MERGE INTO estoque est
USING recebimento rec ON est.produto_id = rec.produto_id
WHEN MATCHED THEN UPDATE SET qtd = est.qtd + rec.qtd
WHEN NOT MATCHED THEN INSERT (produto_id, qtd) VALUES (rec.produto_id, rec.qtd);

168. JSON_TABLE

Converte JSON em tabela relacional dentro da consulta (SQL:2016).

SELECT * FROM JSON_TABLE(
  '[{"id":1,"nome":"Ana"}]', '$[*]'
  COLUMNS (id INT PATH '$.id', nome TEXT PATH '$.nome')
) AS cli_json;

169. Índice paralelo e consulta paralela

O banco usa vários núcleos numa consulta só — ajuste os limites conforme a máquina.

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM eventos;

170. Compressão de coluna

TOAST comprime valores grandes; LZ4 é mais rápido que o padrão em muitos casos.

ALTER TABLE documentos ALTER COLUMN corpo SET COMPRESSION lz4;

Diagnóstico em produção

O banco está lento. Estes são os comandos que respondem 'por quê' em minutos.

171. O que está rodando agora

Primeiro comando de qualquer incidente.

SELECT pid, now() - query_start AS duracao, state, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duracao DESC;

172. Consultas há mais de 1 minuto

Isola os suspeitos imediatos.

SELECT pid, now() - query_start AS duracao, query FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '1 minute';

173. Transações abertas e ociosas

'idle in transaction' segura lock e impede vacuum. Grande causadora de incidente.

SELECT pid, now() - state_change AS parado, query FROM pg_stat_activity
WHERE state = 'idle in transaction' ORDER BY parado DESC;

174. Cancelar e matar

Cancelar tenta encerrar a consulta; terminate derruba a conexão.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 12345;
SELECT pg_terminate_backend(12345);

175. Cadeia de bloqueio

Mostra quem está travando quem, com a consulta de cada lado.

SELECT bloqueada.pid AS vitima,
       bloqueadora.pid AS culpada,
       bloqueadora.query
FROM pg_stat_activity bloqueada
INNER JOIN pg_stat_activity bloqueadora
ON bloqueadora.pid = ANY(pg_blocking_pids(bloqueada.pid));

176. Top consultas por tempo total

Otimizar a consulta de 5 ms chamada 1 milhão de vezes rende mais que a de 2 s chamada 3 vezes.

SELECT query, calls, round(total_exec_time::numeric, 0) AS ms_total
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

177. Taxa de acerto do cache

Abaixo de ~99% em OLTP costuma indicar memória insuficiente.

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2) AS cache_pct
FROM pg_stat_database;

178. Tabelas mais lidas do disco

Aponta onde a memória não está dando conta.

SELECT relname, heap_blks_read, heap_blks_hit
FROM pg_statio_user_tables ORDER BY heap_blks_read DESC LIMIT 10;

179. Sequential scans frequentes

Tabela grande com muitos seq scans grita por índice.

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10;

180. Conexões por estado

Descobre se o problema é excesso de conexão em vez de consulta lenta.

SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

181. Tamanho do banco e das tabelas

Crescimento inesperado costuma ser bloat, índice novo ou log esquecido.

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

182. Espaço de WAL

WAL acumulado significa archive falhando ou slot de replicação órfão — e disco cheio à frente.

SELECT COUNT(*) * 16 AS mb_wal FROM pg_ls_waldir();

183. Deadlocks registrados

Contador crescente indica padrão de acesso conflitante na aplicação.

SELECT datname, deadlocks, conflicts FROM pg_stat_database;

184. Temp files

Arquivo temporário em volume alto significa work_mem baixo para as consultas atuais.

SELECT datname, temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database ORDER BY temp_bytes DESC;

185. Logar consultas lentas

Coleta contínua vale mais que investigação pontual.

ALTER SYSTEM SET log_min_duration_statement = '1000';   -- 1 s
SELECT pg_reload_conf();

186. Auto explain

Registra o plano das consultas lentas automaticamente, sem reproduzir na mão.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '3s';
SET auto_explain.log_analyze = on;

187. Ver e alterar parâmetros

Confira o valor efetivo antes de teorizar sobre a causa.

SHOW work_mem;
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%';

188. Recarregar configuração

Muitos parâmetros aplicam sem reiniciar o servidor.

ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

189. Diagnóstico no MySQL

Equivalentes: lista de processos e schema de performance.

SHOW FULL PROCESSLIST;
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

190. Diagnóstico no SQL Server

DMVs entregam as consultas mais caras e as esperas dominantes.

SELECT TOP 10 total_worker_time/execution_count AS media_cpu,
       text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY media_cpu DESC;

Boas práticas de produção

O que evita o incidente antes de ele existir.

191. Migração versionada

Estrutura muda por script versionado no repositório, nunca por alteração manual no servidor.

-- migrations/0042_add_index_pedidos.sql
CREATE INDEX CONCURRENTLY idx_pedidos_status ON pedidos (status);

192. Migração reversível

Todo script tem o caminho de volta escrito antes de subir.

-- up
ALTER TABLE pedidos ADD COLUMN canal VARCHAR(20);
-- down
ALTER TABLE pedidos DROP COLUMN canal;

193. Deploy sem downtime

Adicione coluna nullable, popule em lote, só então torne obrigatória.

ALTER TABLE pedidos ADD COLUMN canal VARCHAR(20);           -- 1
UPDATE pedidos SET canal = 'web' WHERE canal IS NULL;       -- 2 (em lotes)
ALTER TABLE pedidos ALTER COLUMN canal SET NOT NULL;        -- 3

194. Cuidado com lock em ALTER

Em tabela grande, um ALTER descuidado trava a aplicação inteira. Verifique o comportamento da sua versão.

SET lock_timeout = '3s';   -- falha rápido em vez de enfileirar a produção
ALTER TABLE pedidos ADD COLUMN x INT;

195. Nunca teste em produção

UPDATE e DELETE começam como SELECT, e rodam primeiro em homologação com volume parecido.

BEGIN;
UPDATE pedidos SET status = 'x' WHERE ...;
-- confira o número de linhas afetadas
ROLLBACK;   -- ou COMMIT

196. Monitoramento contínuo

Acompanhe conexões, cache hit, replicação, tamanho e consultas lentas — com alerta, não com dashboard que ninguém olha.

-- Alertas mínimos: lag de réplica, disco > 80%, conexões > 80% do limite,
-- transação aberta > 5 min, falha de backup.

197. Limite de conexões

Dimensione o pool: mais conexões que núcleos costuma piorar, não melhorar.

SHOW max_connections;
SELECT COUNT(*) FROM pg_stat_activity;

198. Timeouts em toda camada

Consulta, transação e conexão precisam de limite. Sem isso, uma query ruim derruba tudo.

SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';
SET lock_timeout = '5s';

199. Documentar o modelo

COMMENT vive junto do schema e aparece nas ferramentas — melhor que wiki desatualizada.

COMMENT ON TABLE pedidos IS 'Pedidos de venda; uma linha por checkout concluído.';
COMMENT ON COLUMN pedidos.total IS 'Valor final em BRL, já com desconto.';

200. Revisar antes de aplicar

Script de banco passa por revisão como qualquer código — com EXPLAIN do que muda e plano de rollback.

-- Checklist: backup ok? rollback escrito? lock estimado? janela definida? monitoramento ligado?