Gerenciamento de Banco de Dados

1. Contexto e Ambiente Técnico

O sistema de gestão era uma aplicação PHP/Laravel conectada a um PostgreSQL 12 hospedado em VPS próprio. O banco havia começado pequeno e crescido organicamente: tabelas novas eram adicionadas conforme a necessidade, migrations eram raramente escritas com cuidado no que diz respeito a índices, e ninguém tinha feito uma revisão de performance desde o lançamento.

Ao momento do nosso engajamento, o banco tinha:

• 87 tabelas, das quais 14 com mais de 500 mil registros
• Nenhum índice composto em colunas frequentemente usadas em filtros conjuntos (WHERE status = ? AND created_at > ?)
• 23 foreign keys sem índice correspondente na coluna referenciada
• Queries N+1 em 8 das 12 telas mais acessadas (identificadas via Laravel Debugbar)
• Backup manual: pg_dump executado manualmente, armazenado na mesma VPS do banco

2. Diagnóstico: Análise de Performance

2.1 Identificação das queries problemáticas

Ativamos o módulo pg_stat_statements do PostgreSQL para coletar estatísticas reais de execução por 72 horas sem alterar nada no sistema. Isso nos deu uma visão baseada em dados reais — não suposições — de quais queries custavam mais tempo de CPU e quais tabelas eram mais acessadas.

As 10 queries mais lentas respondiam por 73% do tempo total de CPU do banco. Todas tinham em comum: sequential scan em tabelas grandes sem índice adequado. A query mais pesada — um relatório de faturamento mensal com 4 JOINs — levava 34 segundos e era executada 40 vezes por dia.

2.2 Análise de EXPLAIN ANALYZE

Para cada query crítica, executamos EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) para entender o plano de execução real. O padrão se repetia: Seq Scan em tabelas de 300k+ linhas com filtros que poderiam ser indexados, e Nested Loop Joins onde Hash Joins seriam mais eficientes.

3. Plano de Ação e Priorização

Dividimos o trabalho em três trilhas paralelas, ordenadas por impacto e risco:

1. Trilha 1 — Quick wins de indexação (semana 1): criação de índices que não requerem alteração de schema, apenas CREATE INDEX CONCURRENTLY — seguro para produção, sem lock de tabela.
2. Trilha 2 — Reestruturação de schema (semanas 2–3): normalização de tabelas desnormalizadas, remoção de colunas obsoletas, criação de índices compostos, ajuste de tipos de dados inadequados.
3. Trilha 3 — Backup e recuperação (paralela): implantação de backup automatizado antes de qualquer mudança estrutural, garantindo rollback em qualquer cenário.

A estratégia de executar a trilha de backup antes das alterações de schema foi deliberada: sem backup confiável e testado, nenhuma alteração estrutural deveria ser executada em produção.

4. Implementação

4.1 Indexação estratégica

Criamos 31 índices novos usando CREATE INDEX CONCURRENTLY, que permite criação sem bloquear leituras e escritas na tabela. Os índices foram priorizados pela frequência de uso medida no pg_stat_statements. Para as queries com filtros compostos recorrentes, usamos índices parciais (WHERE status = 'ativo') reduzindo o tamanho do índice e tornando-o mais eficiente para o caso de uso real.

4.2 Reescrita das queries críticas e eliminação de N+1

As 8 telas com problema N+1 foram refatoradas para usar eager loading (with() no Eloquent) ou queries únicas com JOINs adequados. A query de relatório de faturamento — que antes fazia 4 JOINs sequenciais em full scans — foi reescrita usando CTEs (Common Table Expressions) e os novos índices, passando de 34 segundos para 1,1 segundo.

4.3 Migração de schema sem downtime

Para as alterações de schema que exigiam ALTER TABLE (renomeação de colunas, mudança de tipos), usamos a técnica de expand-contract: adicionamos a nova coluna em paralelo com a antiga, rodamos um script de backfill incremental para popular os dados existentes, atualizamos o código da aplicação para escrever nas duas colunas e, após validação completa, removemos a antiga. Nenhum lock de tabela longo, nenhuma janela de manutenção necessária.

4.4 Estratégia de backup automatizado

Implantamos uma estratégia em três camadas: backup incremental a cada hora via WAL archiving (Write-Ahead Log), dump completo diário via pg_dump comprimido com gzip, e replicação síncrona para um segundo servidor. Os backups são armazenados em um bucket cloud separado da infraestrutura principal. Mais importante: configuramos um job diário que restaura o backup mais recente em um servidor de homologação e valida integridade — o backup só é considerado válido se a restauração for bem-sucedida.

5. Resultados Mensurados (após 45 dias em produção)

O uso médio de CPU do servidor de banco caiu de 78% para 21% nos horários de pico, permitindo que o cliente adie um upgrade de infraestrutura que estava planejado para o trimestre seguinte.

6. Lições Aprendidas

• Meça antes de otimizar: pg_stat_statements e EXPLAIN ANALYZE revelam os problemas reais — sem métricas, qualquer otimização é um chute. As queries que os desenvolvedores achavam que eram lentas não eram as mesmas que o banco mostrava como as mais custosas.
• Backup sem teste de restauração não é backup: um dump que ninguém nunca testou restaurar pode estar corrompido, incompleto ou de um formato que não é mais compatível com a versão atual do banco. Testar a restauração é parte obrigatória da estratégia.
• CREATE INDEX CONCURRENTLY é seu melhor amigo: permite adicionar índices em tabelas grandes em produção sem janela de manutenção, ao custo de ser mais lento que um index build padrão — troca totalmente válida.
• Expand-contract é a forma segura de mudar schema em produção: evite ALTER TABLE ... RENAME COLUMN direto em produção com tráfego ativo. A técnica de paralelo + backfill + contrato é mais trabalhosa, mas elimina risco de indisponibilidade.