No universo da otimização de bancos de dados, os índices são celebrados como os heróis da performance. São a primeira ferramenta que sacamos para resolver uma query de SELECT lenta, e com razão. No entanto, existe um lado sombrio e raramente discutido: cada índice que você cria impõe uma taxa oculta e perpétua sobre a performance de escrita do seu sistema. Um índice que não é utilizado para acelerar nenhuma leitura, mas que ainda precisa ser meticulosamente mantido a cada INSERT, UPDATE e DELETE, não é um ativo de performance; é um “peso morto” disfarçado. É um débito técnico que se acumula silenciosamente, tornando suas transações mais lentas, aumentando a contenção de locks e inflando seus custos de armazenamento.
A prática de “indexar por precaução” ou de esquecer índices criados para queries que não existem mais leva a um fenômeno de “index sprawl”: uma proliferação de índices inúteis que ativamente prejudicam a saúde do seu banco de dados. Este artigo é um guia prático e de alto impacto para identificar, validar e remover com segurança esses índices não utilizados, um “quick win” que pode liberar uma performance de escrita que você nem sabia que estava perdendo.
Por Que Índices Inúteis São Tão Caros
Para entender o impacto, é crucial lembrar que um índice não é uma entidade passiva. É uma estrutura de dados viva que deve ser mantida em perfeita sincronia com a tabela principal. Essa manutenção tem um custo real e mensurável.
1. Amplificação de Escrita (Write Amplification)
Este é o custo mais direto. Uma única operação de escrita na sua tabela é amplificada em múltiplas operações de escrita no disco.
- Exemplo Prático: Imagine uma tabela audit_logs com 8 índices para suportar vários tipos de consulta. Quando sua aplicação executa um único INSERT nesta tabela, o banco de dados precisa realizar, no mínimo, nove operações de escrita distintas: uma para escrever a linha na própria tabela (no heap) e mais oito para inserir a nova entrada em cada uma das oito estruturas de índice B-Tree.
- O Impacto: Em um sistema com alta taxa de ingestão de dados, essa amplificação de escrita se torna um gargalo massivo. Ela aumenta drasticamente a demanda por IOPS (Operações de Entrada/Saída por Segundo) no seu disco, o que se traduz diretamente em uma fatura de nuvem mais alta para armazenamento de alta performance.
2. Aumento da Contenção de Locks
A manutenção dos índices não acontece em um vácuo; ela participa do mesmo sistema de controle de concorrência que suas transações.
- Exemplo Prático: Quando uma linha é atualizada (UPDATE), o banco de dados precisa adquirir locks não apenas na linha da tabela, mas também nas entradas correspondentes nos índices. Mais índices significam mais recursos a serem bloqueados. Um UPDATE que modifica uma coluna indexada geralmente resulta em uma operação de “delete + insert” na árvore do índice, um processo que pode exigir locks em múltiplas páginas do índice.
- O Impacto: Em um ambiente de alta concorrência, essa necessidade de bloquear mais recursos por mais tempo aumenta drasticamente a probabilidade de contenção de locks e até mesmo de deadlocks. Outras transações são forçadas a esperar, resultando em uma queda na vazão (throughput) total do sistema.
3. Custos de Armazenamento e Manutenção
Índices não são apenas metadados; eles são estruturas de dados que consomem espaço em disco.
- Exemplo Prático: Em tabelas largas com muitos índices compostos, não é incomum que o tamanho total dos índices exceda o tamanho da própria tabela.
- O Impacto: Isso não apenas aumenta seus custos diretos de armazenamento, mas também prolonga operações de manutenção críticas. Backups demoram mais para serem concluídos e restaurados. Operações de VACUUM (no PostgreSQL) ou de manutenção de estatísticas têm mais trabalho a fazer, consumindo mais recursos do sistema.
O Passo a Passo para Identificar Índices Não Utilizados
Identificar esses índices “write-only” requer uma análise do seu uso real em produção. Felizmente, os principais bancos de dados relacionais coletam estatísticas que nos permitem fazer exatamente isso.
Para PostgreSQL
O PostgreSQL rastreia o uso de índices na visão de sistema pg_stat_user_indexes. A coluna chave aqui é idx_scan, que conta quantas vezes o índice foi usado para uma varredura.
A Query:
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
s.idx_scan AS times_used
FROM
pg_stat_user_indexes s
JOIN
pg_indexes i ON s.indexrelname = i.indexname
WHERE
s.idx_scan = 0
AND i.indexdef NOT LIKE '%UNIQUE%'; -- Exclui índices de constraints UNIQUE
ORDER BY
pg_relation_size(s.indexrelid) DESC;
Esta query lista todos os índices que nunca foram usados para uma leitura (idx_scan = 0), ordenados pelo seu tamanho. Os maiores no topo são os seus principais candidatos para remoção.
Para SQL Server
O SQL Server usa uma Dynamic Management View (DMV) chamada sys.dm_db_index_usage_stats. Esta DMV é ainda mais rica, pois rastreia leituras (user_seeks, user_scans, user_lookups) e escritas (user_updates) separadamente.
A Query:
SELECT
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
s.user_updates AS total_writes,
s.user_seeks + s.user_scans + s.user_lookups AS total_reads,
(SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) as number_of_rows
FROM
sys.dm_db_index_usage_stats AS s
JOIN
sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE
OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_seeks + s.user_scans + s.user_lookups = 0 -- Condição chave: nenhuma leitura
AND s.user_updates > 0 -- Pelo menos uma escrita
AND i.is_unique = 0; -- Exclui índices de constraints UNIQUE
ORDER BY
s.user_updates DESC;
Esta query é extremamente poderosa. Ela encontra todos os índices que não tiveram nenhuma operação de leitura, mas tiveram pelo menos uma operação de escrita. Estes são, por definição, os índices “peso morto”.
Caveats Críticos da Abordagem Manual
Essas queries são um ótimo ponto de partida, mas vêm com um aviso importante: as estatísticas de uso de índice são reiniciadas toda vez que o serviço do banco de dados é reiniciado. Se você executar a query um dia após uma reinicialização, os resultados não serão confiáveis. É crucial que você colete esses dados ao longo de um ciclo de negócios completo (pelo menos um mês, ou um trimestre, se houver relatórios trimestrais) para ter certeza de que não está descartando um índice que é raramente usado, mas crítico.
Análise Contínua com Observabilidade
O risco e a natureza manual da abordagem acima são exatamente os problemas que uma plataforma de observabilidade como a dbsnOOp foi projetada para resolver.
- Monitoramento Persistente: A dbsnOOp coleta e armazena estatísticas de uso de índice ao longo do tempo, independentemente dos reinícios do servidor. Ela constrói uma imagem precisa e de longo prazo do que é e do que não é usado em seu ambiente.
- Contextualização: A plataforma não apenas diz que um índice não é usado; ela pode correlacionar o uso de outros índices com as queries mais críticas do seu sistema. Isso dá a confiança de que remover um índice não irá impactar negativamente uma query importante.
- Recomendações Seguras e Acionáveis: Com base em sua análise histórica e contínua, a dbsnOOp pode gerar uma lista de alta confiança de índices não utilizados que são candidatos seguros para remoção. Isso transforma um projeto de investigação arriscado em um processo de otimização de rotina, baseado em dados.
O Processo de Remoção Segura: Um Checklist para SREs
Uma vez que você identificou um índice candidato para remoção, nunca o descarte imediatamente em produção. Siga um processo seguro:
- Identificar e Questionar: Use a query ou o relatório da dbsnOOp para encontrar um candidato. Questione sua origem: ele pertence a uma feature antiga? Foi criado para uma query que foi refatorada? Há algum job de BI trimestral que possa usá-lo?
- Desabilitar ou Renomear (Ambientes de Staging/QA): Teste o impacto da remoção em um ambiente de pré-produção. Execute seus testes de regressão e de performance para garantir que nenhuma query essencial foi prejudicada.
- Renomear em Produção: Em vez de um DROP INDEX imediato, uma tática segura é primeiro renomear o índice em produção (ex: ALTER INDEX idx_old RENAME TO idx_old_todelete;). Isso o torna invisível para o otimizador de consultas, efetivamente o desabilitando.
- Monitorar de Perto: Após o rename, monitore de perto seus dashboards de performance e logs de erro por um período (ex: uma ou duas semanas). Se nenhuma nova query lenta ou erro aparecer, você pode prosseguir com confiança.
- Descartar (DROP): Finalmente, agende uma janela de manutenção para executar o comando DROP INDEX.
A Higiene Contínua da Indexação
A gestão de índices não é um evento único de criação; é um ciclo de vida contínuo de criação, monitoramento e remoção. Os índices não utilizados são uma forma insidiosa de débito técnico que impõe uma taxa oculta em cada transação de escrita que seu sistema processa. Ao adotar uma abordagem proativa para encontrar e remover esse peso morto, você não está apenas limpando seu banco de dados; você está liberando a performance de escrita, reduzindo a contenção e diminuindo seus custos operacionais. Em um mundo de engenharia orientada por dados, não há mais desculpa para pagar o imposto da ineficiência.
Quer encontrar e eliminar o “peso morto” que está desacelerando suas escritas? Marque uma reunião com nosso especialista ou assista a uma demonstração na prática!
Para agendar uma conversa com um de nossos especialistas, acesse nosso site. Se preferir ver a ferramenta em ação, assista a uma demonstração gratuita. Mantenha-se atualizado com nossas dicas e novidades seguindo nosso canal no YouTube e nossa página no LinkedIn.
Saiba mais sobre o dbsnOOp!
Visite nosso canal no youtube e aprenda sobre a plataforma e veja tutoriais
Aprenda sobre monitoramento de banco de dados com ferramentas avançadas aqui.
Leitura Recomendada
- Por Que Meu Banco de Dados na Nuvem Está Tão Caro?: Uma análise prática dos principais fatores que inflam a fatura do AWS RDS, Azure SQL e Google Cloud SQL, conectando o custo diretamente a workloads não otimizados, superdimensionamento e a falta de visibilidade sobre o consumo real.
- Locks, Contenção e Performance: Um Estudo Técnico Sobre a Recuperação de Bancos de Dados em Situação Crítica: Este artigo oferece um mergulho técnico em um dos problemas mais complexos da engenharia de dados. Ele detalha como a contenção por bloqueios pode paralisar um sistema e como uma análise precisa da árvore de locks é crucial para a recuperação de bancos de dados em situações críticas.
- 3 falhas que só aparecem de madrugada (e como evitá-las): Focado em um dos momentos mais críticos para as equipes de SRE, este artigo discute os problemas de performance e estabilidade que se manifestam durante processos batch e picos de baixa latência, e como a análise proativa pode prevenir crises noturnas.