1. Configuração do Servidor e Arquitetura de Armazenamento

• Planejo de memória: distribuo memória entre shared_buffers, work_mem e maintenance_work_mem. Regra prática: shared_buffers entre ~15–25% da RAM disponível, effective_cache_size estimado com base na memória de OS/cache de paginação para auxiliar o planejador de consultas.
• Conexões e poolers: ajuste de max_connections deve vir acompanhado de um pooler (PgBouncer/Pgbouncer) para evitar overhead de processos, mantendo picos sob controle.
• WAL e durabilidade: use wal_level adequado (repl para standby, logical para replicação lógica), ajuste de checkpoint_completion_target e tamanhos de WAL (min_wal_size / max_wal_size) para amortizar I/O de checkpoints.
• Autovacuum e estatísticas: mantenha o autovacuum ativo e ajuste autovacuum_vacuum_scale_factor e autovacuum_analyze_scale_factor conforme a taxa de modificação de cada tabela; colete estatísticas com ANALYZE periodicamente.
• Tablespaces e particionamento: distribua dados entre discos/volumes apropriados e utilize particionamento por faixa ou hash para tabelas muito grandes, promovendo pruning de partições e melhoria de I/O.

2. Modelagem de Consultas, Índices e Plano de Execução

• Escolha de índices adequada: para igualdade e intervalo use B-tree; para buscas em texto/arrays utilize GIN/GIN com configuração apropriada; BRIN pode ser útil para grandes tabelas append-only com consultas de faixa.
• Indexação inteligente: prefira índices parciais ou expressões quando apenas um subconjunto de linhas é comum (ex.: CREATE INDEX ON table (col) WHERE active = TRUE; ou índices em expressões que refletem filtros frequentes.
• Index covering e INCLUDE: utilize colunas adicionais com INCLUDE para tornar consultas elegíveis a index-only scans e reduzir retornos de leitura de tabelas.
• Observação de planos: utilize EXPLAIN e EXPLAIN ANALYZE para compreender custos, escolhas de join e uso de índices antes de aplicar mudanças.

-- Exemplo: EXPLAIN ANALYZE de uma query com join e agregação
EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.active = TRUE
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

3. Gerenciamento de Dados e Manutenção

• VACUUM e ANALYZE: mantenha a limpeza de tuplas vivas com VACUUM regular e atualização de estatísticas via ANALYZE; o autovacuum deve estar ativo para evitar bloat excessivo.
• Autovacuum tuning: ajuste autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, e limites de workers para equilibrar consumo de I/O com necessidade de limpeza.
• Dados grandes e particionamento: particionamento facilita prune de partições antigas, reduzindo o custo de operações de manutenção e consultas em grandes volumes de dados.
• Backups consistentes: combine snapshots, WAL archiving e verificação regular de restores para garantir recuperação em produção.

4. Observabilidade, Segurança e Operações

• Observabilidade: ative e utilize as visões pg_stat_statements, pg_stat_activity e logs detalhados; considere auto_explain para registrar planos de consultas lentas automaticamente.
• Logging e métricas: configure log_min_duration_statement, log_line_prefix, e métricas de tempo para identificar gargalos sem sobrecarregar o ioutil.
• Conectividade segura: use TLS/SSL para conexões, gerencie papéis e permissões com least privilege e considere rotação de credenciais e políticas de rotação de senhas.
• Operações de produção: adote poolers de conexão, monitore locks com log_lock_waits e prepare-se para rollout controlado de mudanças com testes de performance antes de ir para produção.

Exemplo de prática recomendada

-- Criação de índice parcial para queries frequentes de ativos ativos
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_active ON users (id, name)
WHERE active = TRUE;