Administração, desempenho e disponibilidade de Banco de Dados no contexto do DETRAN

Operação de banco de dados no DETRAN: foco em desempenho e disponibilidade

No contexto do DETRAN, bancos de dados sustentam serviços críticos e de alta demanda (ex.: consultas de situação de veículo, emissão de taxas, agendamentos, registro de infrações, integração com órgãos externos). Administração aqui significa manter o banco previsível sob picos, com baixa latência, integridade e capacidade de recuperação. Os pilares operacionais são: (1) desempenho (tempo de resposta e throughput), (2) disponibilidade (continuidade do serviço), (3) segurança e auditoria (rastreabilidade e conformidade) e (4) capacidade (crescimento controlado).

Métricas essenciais para operação

• Latência: tempo médio e p95/p99 de consultas e transações (ex.: p95 < 200 ms em endpoints críticos).
• Throughput: transações por segundo (TPS) e consultas por segundo (QPS).
• Contenção: locks, deadlocks, waits (I/O, CPU, rede, log).
• Uso de recursos: CPU, memória (cache/buffer), IOPS, throughput de disco, espaço.
• Saúde de replicação: atraso (lag), falhas de apply, divergências.
• Backup: duração, taxa de transferência, janela de backup, sucesso/falha.
• RPO/RTO: perda máxima aceitável de dados e tempo máximo de indisponibilidade.

Índices: B-tree, compostos e boas práticas

Conceito e impacto

Índices aceleram buscas e junções ao reduzir leituras. Em bancos relacionais, o índice B-tree é o padrão para igualdade e intervalos (=>, BETWEEN, ORDER BY). O ganho vem de evitar varreduras completas (full scan) e reduzir I/O. O custo é maior escrita (INSERT/UPDATE/DELETE) e consumo de espaço.

Índice B-tree: quando usar

• Filtros por chave (ex.: id_veiculo, cpf, placa).
• Consultas por intervalo (ex.: data_infracao entre datas).
• Ordenações frequentes (ex.: listagens paginadas por data).

Índices compostos: regra do prefixo e seletividade

Índices compostos (multi-coluna) são úteis quando a consulta filtra por mais de uma coluna. A ordem das colunas é decisiva: o otimizador aproveita melhor o índice seguindo a regra do prefixo (as primeiras colunas do índice precisam aparecer no predicado para máximo benefício). Priorize colunas com alta seletividade (muitos valores distintos) e que aparecem com frequência nos filtros.

Exemplo prático: consulta típica de infrações por veículo e período.

-- Consulta frequente (exemplo genérico de SQL relacional)  SELECT *  FROM infracao  WHERE id_veiculo = :id    AND data_infracao >= :ini    AND data_infracao < :fim  ORDER BY data_infracao DESC;

Índice recomendado: (id_veiculo, data_infracao) para filtrar por veículo e percorrer o intervalo de datas já ordenado.

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

Passo a passo: identificar e criar índice com segurança

• 1) Capturar a consulta real: use logs de consultas lentas, APM ou views de estatísticas para obter o SQL e frequência.
• 2) Medir baseline: tempo médio/p95, leituras lógicas/físicas, plano de execução.
• 3) Verificar seletividade: cardinalidade das colunas e distribuição (ex.: muitos veículos, muitas datas).
• 4) Propor índice: B-tree simples ou composto, evitando redundância com índices existentes.
• 5) Validar em homologação: comparar plano e métricas; avaliar impacto em escrita.
• 6) Implantar em janela controlada: preferir criação online quando suportado; monitorar locks e I/O.
• 7) Acompanhar pós-implantação: queda de latência e redução de I/O; observar aumento de tempo de escrita.

Armadilhas comuns

• Índice demais: degrada escrita e aumenta manutenção (rebuild/vacuum).
• Colunas com baixa seletividade: ex.: status com poucos valores pode não ajudar isoladamente; pode ajudar como segunda coluna em índice composto.
• Funções no predicado: WHERE date(data_infracao)=... pode impedir uso do índice; prefira intervalos.
• Paginação ineficiente: OFFSET alto pode ser caro; prefira paginação por chave (seek method) quando possível.

Particionamento: escala por volume e manutenção

Conceito

Particionamento divide uma tabela grande em partes menores (partições) por critério (geralmente data). Benefícios: consultas por intervalo leem menos dados (pruning), manutenção mais simples (arquivar/expurgar por partição), e melhor gerenciamento de índices por partição.

Quando particionar

• Tabelas com crescimento contínuo e consultas por período (ex.: logs, eventos, infrações, transações financeiras).
• Necessidade de retenção por tempo (ex.: manter 5 anos online e arquivar o restante).
• Manutenção pesada em tabela monolítica (reindex, vacuum, estatísticas) afetando janelas operacionais.

Estratégias comuns

• Range por data: mensal/semanal (ex.: data_evento).
• Hash por chave: distribuir carga de escrita/leitura quando não há filtro por data (menos comum para retenção).
• Subparticionamento: por data e por região/unidade, se houver padrão de acesso.

Passo a passo: particionar uma tabela de transações por mês

• 1) Escolher chave de particionamento: data de ocorrência/registro que aparece nos filtros.
• 2) Definir granularidade: mensal costuma equilibrar quantidade de partições e tamanho.
• 3) Criar partições futuras: automatizar criação (job) para evitar falhas em inserções.
• 4) Ajustar índices: criar índices locais por partição (quando suportado) e revisar índices globais.
• 5) Migrar dados: estratégia online (dual-write/trigger) ou janela de manutenção, conforme criticidade.
• 6) Validar pruning: conferir plano de execução para garantir que apenas partições necessárias são lidas.
• 7) Operação de retenção: expurgar/arquivar removendo partições inteiras (mais rápido que DELETE massivo).

Estatísticas e otimizador: base para bons planos

Conceito

O otimizador escolhe planos com base em estatísticas (cardinalidade, distribuição, correlação). Estatísticas desatualizadas levam a escolhas ruins (ex.: nested loop onde deveria ser hash join, ou full scan em vez de index scan).

Boas práticas

• Atualizar estatísticas após cargas grandes, mudanças de padrão (ex.: novo serviço aumentando volume).
• Histograms (quando suportado) para colunas com distribuição desigual (ex.: poucos valores muito frequentes).
• Autovacuum/analyze (ou equivalente) bem calibrado para tabelas com alta rotatividade.
• Monitorar regressões após deploy: mudanças no SQL podem exigir novas estatísticas.

Passo a passo: tratar regressão por estatísticas

• 1) Detectar: aumento de p95/p99 e mudança no plano de execução.
• 2) Verificar estatísticas: data da última coleta e estimativas vs. linhas reais.
• 3) Recoletar: executar coleta/atualização de estatísticas na tabela/índices afetados.
• 4) Revalidar: comparar plano e métricas; confirmar redução de leituras e tempo.

Tuning e análise de gargalos

Abordagem sistemática

Tuning eficaz começa pela observabilidade: identificar onde o tempo é gasto (CPU, I/O, locks, rede, log). Evite “otimizações no escuro”. Em sistemas do DETRAN, gargalos comuns aparecem em períodos de pico (abertura de agenda, vencimentos, grandes lotes de integrações).

Checklist de diagnóstico

• 1) Top SQL: consultas mais lentas e mais frequentes (custo total).
• 2) Plano de execução: scans, joins, estimativas erradas, sort/hash spill para disco.
• 3) I/O: leituras físicas altas, cache insuficiente, storage saturado.
• 4) Locks: bloqueios longos, deadlocks, transações abertas.
• 5) Log/redo: saturação de escrita de log em picos de transação.
• 6) Conexões: excesso de conexões, pool mal dimensionado, filas.

Intervenções típicas

• SQL: reescrever filtros, evitar funções em colunas indexadas, reduzir colunas retornadas, paginação por chave.
• Índices: criar/ajustar compostos, remover redundantes, considerar índices parciais (quando suportado) para subconjuntos (ex.: status='ABERTA').
• Particionamento: reduzir varreduras e facilitar retenção.
• Configuração: memória para cache/sort, paralelismo, checkpoint, autovacuum/analyze.
• Concorrência: reduzir tempo de transação, ordem consistente de atualização para evitar deadlocks.

Backup e restore: garantia de recuperação

Conceitos: tipos e objetivos

• Backup completo: cópia integral; base para restores simples, porém pesado.
• Incremental/diferencial: copia mudanças; reduz janela de backup.
• Backup de logs (PITR): permite recuperar até um ponto no tempo (Point-in-Time Recovery), essencial para erros humanos (DELETE acidental) e corrupção lógica.

RPO e RTO na prática

• RPO (Recovery Point Objective): quanto de dado pode ser perdido. Ex.: RPO de 5 minutos implica replicação/log shipping frequente.
• RTO (Recovery Time Objective): tempo para restaurar e voltar a operar. Ex.: RTO de 30 minutos exige automação, infraestrutura pronta e procedimentos testados.

Estratégias de retenção

Retenção define por quanto tempo manter backups e logs, equilibrando custo e conformidade. Um modelo comum:

• Diários: manter 7 a 14 dias.
• Semanais: manter 4 a 8 semanas.
• Mensais: manter 6 a 12 meses (ou conforme norma interna).
• Logs para PITR: manter janela compatível com auditoria e necessidade de rollback (ex.: 7 a 30 dias).

Passo a passo: desenhar um plano de backup/restore

• 1) Classificar bases: críticas (produção transacional), analíticas, integrações, logs.
• 2) Definir RPO/RTO por sistema: serviços externos e atendimento ao cidadão tendem a exigir RTO menor.
• 3) Escolher estratégia: full + incremental + logs (PITR) para críticas; full semanal + diferencial diário para menos críticas.
• 4) Definir armazenamento: cópia em mídia/conta separada, com imutabilidade (quando possível) e criptografia.
• 5) Automatizar e monitorar: alertas de falha, verificação de integridade, tempo de execução.
• 6) Documentar runbook: passos de restore, credenciais, dependências, ordem de serviços.

Testes de restauração (obrigatórios)

Backup sem teste é suposição. Testes devem comprovar: (1) integridade, (2) tempo real de recuperação, (3) capacidade de recuperar até um ponto no tempo.

• Teste mensal: restore completo em ambiente isolado, validação de checks e consultas de amostragem.
• Teste trimestral: simulação de desastre com RTO cronometrado e failover de aplicação.
• Teste de PITR: restaurar para um timestamp anterior a um “erro simulado”.

Replicação e alta disponibilidade

Replicação: objetivos e cuidados

Replicação mantém cópias sincronizadas para leitura, contingência e HA. Pode ser síncrona (menor perda, maior latência) ou assíncrona (menor latência, risco de perda dentro do lag). Em serviços críticos do DETRAN, a decisão deve ser guiada por RPO/RTO.

• Leitura escalável: direcionar relatórios/consultas pesadas para réplicas (evitando impactar OLTP).
• Contingência: promover réplica em falha do primário.
• Proteção contra falhas: exige monitoramento de lag e consistência.

Alta disponibilidade (HA)

• Failover automático: orquestrador/cluster decide e promove nó saudável.
• Quórum e split-brain: evitar dois primários simultâneos (risco de divergência).
• VIP/DNS: mecanismo de redirecionamento rápido para o novo primário.
• Runbook de incidentes: critérios para failover, rollback e comunicação.

Passo a passo: definir arquitetura para RPO 0–5 min e RTO 15–30 min

• 1) Escolher modo de replicação: síncrona para RPO ~0 (se latência permitir) ou assíncrona com monitoramento e log shipping para RPO em minutos.
• 2) Dimensionar nós: primário + pelo menos 1 réplica em zona distinta; storage e rede compatíveis com pico.
• 3) Definir mecanismo de failover: automático para serviços críticos; manual assistido para reduzir risco operacional em sistemas menos críticos.
• 4) Testar failover: simular queda, medir tempo até estabilização e validar integridade.
• 5) Integrar com aplicação: pool de conexões com reconexão, timeouts e idempotência em operações sensíveis.

Segurança: controle de acesso, segregação de funções, auditoria e criptografia

Controle de acesso (princípio do menor privilégio)

O banco deve expor apenas o necessário para cada perfil. Em ambientes do DETRAN, é comum separar perfis: aplicação, suporte, DBA, auditoria e integração. Evite contas compartilhadas e privilégios amplos em produção.

• RBAC: roles por função (leitura, escrita, manutenção).
• Contas de aplicação: permissões restritas a schemas/tabelas necessárias.
• Operações perigosas: DROP, ALTER, TRUNCATE e acesso a dados sensíveis devem ser controlados e justificados.

Segregação de funções (SoD)

Separar quem desenvolve, quem implanta e quem administra reduz risco de fraude e erro. Exemplo prático:

• Dev: sem acesso direto a produção; usa dados mascarados em homologação.
• DBA: executa mudanças via change management, com scripts versionados.
• Auditoria: acesso somente leitura a trilhas e relatórios.

Trilhas de auditoria

Auditoria registra quem acessou/alterou dados e quando. Para o DETRAN, isso é crucial em dados pessoais e atos administrativos (ex.: alteração de cadastro, baixa de débitos, cancelamentos).

• Auditar autenticação: logins, falhas, origem.
• Auditar DDL: criação/alteração de objetos.
• Auditar DML sensível: alterações em tabelas críticas (com usuário, timestamp, chave do registro, operação).
• Imutabilidade: logs enviados para repositório central com retenção e proteção contra alteração.

Criptografia em repouso

Criptografia em repouso protege dados em discos, backups e snapshots. Pode ser feita em nível de storage/volume ou nativamente no banco (TDE/criptografia de tablespace/arquivo). Também é recomendável criptografar backups e controlar chaves (KMS/HSM quando disponível).

• O que criptografar: dados pessoais, documentos, chaves de integração, backups.
• Gestão de chaves: rotação, segregação de acesso, procedimentos de recuperação.
• Impacto: pequena sobrecarga; deve ser medida em homologação.

Estudo de caso: base transacional crescendo por volume de transações

Cenário

Um sistema transacional do DETRAN registra eventos (pagamentos, agendamentos e atualizações de situação). A tabela transacao cresce rapidamente: 2 milhões de linhas/dia. Sintomas em pico:

• p95 de consultas de consulta de transações por cidadão subiu de 180 ms para 1,2 s.
• Relatórios operacionais impactam o OLTP.
• Locks aumentaram em atualizações de status.
• Janela de backup completo passou a competir com o horário de maior uso.

Diagnóstico (métricas e evidências)

• Top SQL: consulta por cpf + período faz full scan em tabela grande.
• Plano: estimativa de cardinalidade incorreta (estatísticas desatualizadas).
• I/O: leituras físicas altas; cache não cobre working set.
• Relatórios: consultas sem filtro temporal adequado.
• Backup: full diário com duração de 4h; logs não permitem PITR fino.

Plano de melhoria (ações, métricas e validação)

1) Índices orientados às consultas críticas

Ação: criar índice composto para consultas por cidadão e período e revisar índices redundantes.

-- Exemplo genérico  CREATE INDEX idx_transacao_cpf_data  ON transacao (cpf, data_evento);

• Métrica-alvo: reduzir p95 de 1,2 s para < 250 ms; reduzir leituras físicas em > 70% na consulta.
• Validação: comparar plano antes/depois; medir p95/p99 em horário de pico; monitorar impacto em INSERT (tempo médio de commit).

2) Particionamento por data para escala e retenção

Ação: particionar transacao por mês em data_evento, com criação automática de partições futuras.

• Métrica-alvo: pruning em consultas por período (ler 1–2 partições em vez de todas); reduzir tempo de manutenção (reindex/analyze) por partição.
• Validação: verificar no plano que apenas partições do intervalo são acessadas; medir queda de I/O e tempo.

3) Estatísticas e estabilidade de planos

Ação: ajustar política de coleta de estatísticas para tabelas de alta rotatividade e executar atualização após cargas.

• Métrica-alvo: reduzir variação de planos e regressões; estimativas próximas do real (diferença pequena entre estimado e retornado).
• Validação: auditoria semanal de planos das top queries; alertas para mudança de plano/latência.

4) Separação de carga: réplica para leitura e relatórios

Ação: direcionar relatórios e consultas pesadas para réplica de leitura, mantendo OLTP no primário.

• Métrica-alvo: reduzir CPU do primário em > 20% em horário comercial; manter lag de replicação < 60 s (ou conforme RPO).
• Validação: monitorar lag, erros de replicação e performance do primário; testes de consistência em relatórios.

5) Backup/restore com PITR e retenção adequada

Ação: migrar de full diário pesado para estratégia full semanal + incrementais diários + backups de log frequentes (PITR), com retenção definida e backups criptografados.

• Métrica-alvo: reduzir janela de backup em horário crítico; atingir RPO de 5 min e RTO de 30 min para o sistema.
• Validação: testes mensais de restore completo e testes trimestrais de PITR com cronômetro (RTO real); relatório de sucesso/falha.

6) Contenção e locks: reduzir tempo de transação

Ação: revisar transações longas na aplicação (ex.: manter transação aberta enquanto chama serviços externos), padronizar ordem de atualização e criar índices para reduzir locks por varredura.

• Métrica-alvo: reduzir waits por lock e deadlocks; reduzir tempo médio de transação.
• Validação: monitorar waits, deadlocks e tempo de commit; testes de carga com concorrência.

7) Segurança e auditoria operacional

Ação: aplicar RBAC, separar perfis (aplicação/DBA/auditoria), habilitar auditoria de DDL e DML sensível, e criptografia em repouso para dados e backups.

• Métrica-alvo: 100% de acessos administrativos com identidade individual; trilhas de auditoria com retenção e integridade; backups criptografados.
• Validação: revisão periódica de permissões; testes de restauração incluindo chaves; verificação de logs em repositório central.

Roteiro prático de validação contínua (produção)

• Antes/depois (baseline): registrar p50/p95/p99, I/O, CPU, locks, lag de replicação e duração de backup.
• Testes de carga: simular pico (abertura de agenda/vencimentos) e medir SLOs.
• Canary/implantação gradual: aplicar mudanças (índices/partições/config) com rollback planejado.
• Alertas: latência acima do SLO, crescimento de tabela, falha de backup, lag alto, espaço baixo, deadlocks.
• Revisão mensal: top SQL, índices não usados, bloat/fragmentação, eficácia de partições, sucesso de restores.