Truques de PostgreSQL que todo desenvolvedor deve saber

1. Otimização de Consultas com Índices Avançados

Índices são a base da performance em PostgreSQL, mas poucos desenvolvedores exploram todo seu potencial. Índices parciais permitem indexar apenas um subconjunto de linhas, economizando espaço e acelerando consultas específicas:

CREATE INDEX idx_pedidos_ativos ON pedidos (data_criacao) 
WHERE status = 'ativo';

Índices funcionais resolvem consultas que usam expressões ou funções em colunas. Se você busca por LOWER(email), crie um índice que já aplique a função:

CREATE INDEX idx_email_lower ON usuarios (LOWER(email));

Para índices multicoluna, a ordem das colunas importa: coloque primeiro aquela com maior seletividade. Um índice em (categoria, data_criacao) será eficiente para consultas que filtram por categoria e data, mas não para consultas apenas por data.

2. CTEs e Consultas Recursivas Poderosas

Common Table Expressions (WITH) tornam consultas complexas mais legíveis e reutilizáveis. Exemplo clássico de hierarquia de funcionários:

WITH RECURSIVE hierarquia AS (
    SELECT id, nome, gerente_id, 1 AS nivel
    FROM funcionarios
    WHERE gerente_id IS NULL
    UNION ALL
    SELECT f.id, f.nome, f.gerente_id, h.nivel + 1
    FROM funcionarios f
    JOIN hierarquia h ON f.gerente_id = h.id
)
SELECT * FROM hierarquia ORDER BY nivel, nome;

CTEs também podem modificar dados. Para arquivar pedidos antigos e removê-los da tabela principal:

WITH pedidos_antigos AS (
    DELETE FROM pedidos
    WHERE data_criacao < '2023-01-01'
    RETURNING *
)
INSERT INTO pedidos_arquivados SELECT * FROM pedidos_antigos;

3. Funções de Janela (Window Functions) na Prática

Window functions resolvem problemas de ranking, totais acumulados e comparações entre linhas sem subconsultas complexas. Para calcular a média móvel de vendas nos últimos 7 dias:

SELECT data, valor,
       AVG(valor) OVER (ORDER BY data ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_movel
FROM vendas_diarias;

LAG e LEAD permitem comparar uma linha com a anterior ou posterior. Para identificar crescimento de vendas mês a mês:

SELECT mes, total,
       total - LAG(total) OVER (ORDER BY mes) AS diferenca_mes_anterior
FROM vendas_mensais;

Particionamento com PARTITION BY permite reiniciar cálculos para cada grupo, como ranking dentro de cada departamento:

SELECT nome, departamento, salario,
       RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS ranking
FROM funcionarios;

4. Tipos de Dados Especiais e JSONB

JSONB transformou o PostgreSQL em um banco NoSQL híbrido. Operadores como ->, ->>, @> e ? permitem consultas eficientes:

SELECT dados->>'nome' AS nome, dados->'endereco'->>'cidade' AS cidade
FROM clientes
WHERE dados @> '{"ativo": true}';

Arrays e enumerações são ideais para dados estruturados sem criar tabelas extras:

CREATE TYPE status_pedido AS ENUM ('pendente', 'processando', 'enviado', 'entregue');
CREATE TABLE pedidos (
    id SERIAL PRIMARY KEY,
    itens TEXT[],
    status status_pedido DEFAULT 'pendente'
);

Para dados geográficos, a extensão PostGIS oferece tipos como GEOMETRY e GEOGRAPHY. Intervalos de tempo (daterange, tsrange) permitem consultas eficientes de sobreposição:

SELECT * FROM reservas
WHERE daterange(checkin, checkout, '[]') @> CURRENT_DATE;

5. Full-Text Search (FTS) sem Elasticsearch

PostgreSQL oferece busca textual completa sem dependências externas. Configure o dicionário português e crie índices GIN:

CREATE INDEX idx_busca_artigos ON artigos USING GIN(to_tsvector('portuguese', titulo || ' ' || conteudo));

Para buscar e ranquear resultados:

SELECT titulo, ts_rank(to_tsvector('portuguese', titulo || ' ' || conteudo), plainto_tsquery('portuguese', 'banco de dados')) AS relevancia
FROM artigos
WHERE to_tsvector('portuguese', titulo || ' ' || conteudo) @@ plainto_tsquery('portuguese', 'banco de dados')
ORDER BY relevancia DESC;

A função ts_headline destaca os termos encontrados no resultado:

SELECT ts_headline('portuguese', conteudo, plainto_tsquery('portuguese', 'otimização índices'), 'StartSel=<b>, StopSel=</b>, MaxWords=50, MinWords=20') AS destaque
FROM artigos
WHERE to_tsvector('portuguese', conteudo) @@ plainto_tsquery('portuguese', 'otimização índices');

6. Transações, Locking e Controle de Concorrência

O nível de isolamento SERIALIZABLE garante que transações concorrentes se comportem como se fossem executadas sequencialmente. Use-o quando a consistência for crítica:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

Para filas de processamento, SKIP LOCKED evita que múltiplos workers processem o mesmo registro:

BEGIN;
SELECT * FROM fila_tarefas
WHERE status = 'pendente'
ORDER BY prioridade DESC
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- processa a tarefa
UPDATE fila_tarefas SET status = 'processado' WHERE id = ?;
COMMIT;

Locks de aplicação com pg_try_advisory_lock permitem implementar filas customizadas sem depender de locks de tabela:

SELECT pg_try_advisory_lock(12345);
-- executa operação exclusiva
SELECT pg_advisory_unlock(12345);

7. Ferramentas de Diagnóstico e Monitoramento

EXPLAIN ANALYZE mostra o plano de execução real e os tempos de cada etapa:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pedidos WHERE status = 'ativo' AND data_criacao > '2024-01-01';

A extensão pg_stat_statements identifica consultas lentas em produção:

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Para forçar planos de execução específicos em consultas problemáticas, use a extensão pg_hint_plan:

CREATE EXTENSION pg_hint_plan;
/*+ SeqScan(pedidos) */
SELECT * FROM pedidos WHERE status = 'ativo';

Conclusão

PostgreSQL é um banco de dados extremamente poderoso quando exploramos seus recursos avançados. Índices inteligentes, CTEs recursivas, window functions, JSONB, full-text search e controle de concorrência são ferramentas que todo desenvolvedor deveria dominar. Combinadas com boas práticas de monitoramento, essas técnicas transformam consultas lentas em operações eficientes e sistemas mais robustos.

Referências