Resolvendo Índices Quebrados: Como Reconstruir e Reparar Índices no PostgreSQL

Os índices são geralmente a razão pela qual o PostgreSQL pode responder a uma consulta em milissegundos em vez de ler uma tabela inteira. Eles também são fáceis de esquecer até que um fique inchado, inválido ou sob suspeita de corrupção. Então, os sintomas parecem problemas normais de desempenho no início: uma consulta fica mais lenta, as leituras de disco aumentam, uma tabela que costumava ser silenciosa se torna cara, ou um plano de consulta para de fazer sentido.

Reconstruir um índice não é difícil. Saber quando reconstruí-lo é a parte mais difícil. Um índice inchado pode ser corrigido com REINDEX, mas a causa raiz pode ser configurações fracas de autovacuum ou uma carga de trabalho que atualiza as mesmas linhas o dia todo. Um índice corrompido pode precisar de reparo urgente, mas você também deve perguntar por que a corrupção ocorreu: armazenamento, memória, erros de kernel, configurações de hardware inseguras ou um bug de software raro.

Este guia foca em comandos práticos do PostgreSQL: como identificar índices suspeitos, como reconstruí-los com e sem tempo de inatividade, e o que verificar antes de executar manutenção em um banco de dados de produção.

Entendendo os Índices do PostgreSQL e Seus Problemas Comuns

Os índices do PostgreSQL, mais comumente índices B-tree, são estruturas de busca que ajudam o planejador a evitar escanear cada linha. Quando um índice é saudável e seletivo, o PostgreSQL pode pular para a pequena parte da tabela que precisa. Quando o índice está inchado ou inválido, o planejador ainda pode usá-lo, mas o banco de dados faz trabalho extra para obter o mesmo resultado.

Os índices podem se tornar problemáticos principalmente devido a duas razões: inchaço e corrupção.

Inchaço do Índice

Inchaço do índice refere-se ao acúmulo de "tuplas mortas" (versões de dados obsoletas) dentro de uma estrutura de índice. No PostgreSQL, quando linhas são atualizadas ou excluídas, as versões antigas dos dados (e suas entradas de índice correspondentes) não são removidas imediatamente. Em vez disso, elas são marcadas como "mortas" e eventualmente recuperadas pelo processo VACUUM. Se o VACUUM não for executado com frequência ou eficácia suficiente, ou se houver uma alta taxa de atualizações/exclusões, essas tuplas mortas podem se acumular, tornando o índice maior do que o necessário. Um índice inchado ocupa mais espaço em disco, requer mais operações de E/S para escanear e pode até se tornar menos eficaz em acelerar consultas.

Corrupção do Índice

Corrupção do índice é um problema mais grave onde a estrutura interna de um índice se torna logicamente inconsistente ou fisicamente danificada. Isso pode ser causado por vários fatores, incluindo:

Falhas de hardware: Erros de disco, problemas de memória ou quedas de energia.
Bugs de software: Defeitos raros, mas possíveis, no próprio PostgreSQL ou em componentes subjacentes do sistema operacional.
Falhas repentinas do sistema: Término abrupto do servidor PostgreSQL sem procedimentos adequados de desligamento.

Índices corrompidos podem levar a resultados de consulta incorretos, erros como "índice contém dados inesperados" ou até impedir que consultas sejam concluídas. Identificar e corrigir a corrupção é crítico para a integridade dos dados e a estabilidade do banco de dados.

Os sintomas de índices problemáticos geralmente incluem uma desaceleração repentina de consultas específicas, aumento da atividade de E/S sem motivo aparente ou mensagens de erro relacionadas à varredura de índices.

Identificando Índices Problemáticos

Antes de reparar um índice, você precisa identificar quais estão causando problemas. O PostgreSQL fornece várias maneiras de fazer isso.

Verificando Índices Não Utilizados ou Ineficientes

A visão pg_stat_user_indexes fornece estatísticas sobre o uso de índices. Você pode consultá-la para encontrar índices que são raramente ou nunca usados, que podem ser candidatos para remoção ou reavaliação.

SELECT
    relname AS nome_tabela,
    indexrelname AS nome_indice,
    idx_scan AS varreduras_indice,
    idx_tup_read AS tuplas_lidas,
    idx_tup_fetch AS tuplas_buscadas
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- Índices que nunca foram escaneados
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

Embora um idx_scan de 0 possa indicar um índice não utilizado, é crucial considerar que alguns índices são usados para restrições (por exemplo, UNIQUE, PRIMARY KEY) ou relatórios acessados com pouca frequência. Sempre investigue antes de descartar.

Detectando Inchaço do Índice

O inchaço é mais difícil de detectar diretamente, mas um tamanho de índice desproporcionalmente grande em comparação com sua tabela ou um índice que cresce excessivamente sem crescimento correspondente de dados pode indicar inchaço. Você pode comparar o tamanho das tabelas e seus índices:

SELECT
    relname AS nome_tabela,
    pg_size_pretty(pg_relation_size(relid)) AS tamanho_tabela,
    pg_size_pretty(pg_indexes_size(relid)) AS tamanho_indices,
    pg_size_pretty(pg_total_relation_size(relid)) AS tamanho_total
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

Para detecção de inchaço mais avançada, você pode considerar o uso de scripts contribuídos pela comunidade ou extensões como pg_repack ou pgstattuple (que pode estimar o inchaço observando a densidade de tuplas).

Identificando Consultas Lentas com `EXPLAIN ANALYZE`

Quando uma consulta específica fica lenta, EXPLAIN ANALYZE é seu melhor amigo. Ele mostra o plano de execução da consulta e estatísticas de tempo real, incluindo como os índices são usados (ou não usados).

EXPLAIN ANALYZE
SELECT * FROM sua_tabela WHERE sua_coluna = 'algum_valor';

Se o plano mostrar varreduras sequenciais onde uma varredura de índice era esperada, ou se uma varredura de índice estiver demorando um tempo incomumente longo, isso pode apontar para um índice ineficiente ou problemático.

Verificando Corrupção do Índice

A corrupção do índice geralmente se manifesta como erros nos logs do PostgreSQL ou quando as consultas falham inesperadamente. Procure por mensagens contendo frases como corruption, unexpected data, invalid page ou bad block. Não há um único comando SQL embutido que prove que todos os índices em um banco de dados estão saudáveis. Para verificações mais profundas, as equipes costumam usar a extensão amcheck do PostgreSQL, especialmente bt_index_check e bt_index_parent_check para índices B-tree, durante janelas de manutenção.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_produtos_nome'::regclass);

amcheck é uma ferramenta de diagnóstico, não uma ferramenta de reparo. Se ela relatar um problema, faça um backup se você ainda não tiver um recente, inspecione os logs do PostgreSQL e do sistema e planeje uma reconstrução.

Dica: Monitore regularmente os logs do PostgreSQL em busca de mensagens de erro. A detecção precoce de corrupção pode evitar problemas maiores.

O Comando `REINDEX`: Sua Ferramenta Principal

O comando REINDEX é a ferramenta principal para reconstruir índices do PostgreSQL. Ele reconstrói um índice do zero, corrigindo efetivamente o inchaço removendo tuplas mortas e reparando a corrupção construindo uma estrutura nova e válida com base nos dados atuais da tabela.

Como o `REINDEX` Funciona

Quando REINDEX é executado, o PostgreSQL reconstrói o índice a partir dos dados atuais da tabela. O resultado é uma nova estrutura de índice compacta. Para inchaço, isso significa que o espaço morto dentro do índice é removido. Para muitos casos de corrupção em nível de índice, ele fornece ao PostgreSQL uma estrutura nova construída a partir da tabela.

Sintaxe e Uso do `REINDEX`

REINDEX pode ser aplicado em diferentes granularidades:

Reindexar um índice específico:
```
REINDEX INDEX nome_indice;
```
Este é o caso de uso mais comum, visando um único índice problemático.
Reindexar todos os índices em uma tabela:
```
REINDEX TABLE nome_tabela;
```
Útil quando uma tabela tem vários índices inchados ou corrompidos.
Reindexar todos os índices em um banco de dados:
```
REINDEX DATABASE nome_banco_dados;
```
Esta é uma medida mais drástica, normalmente usada em situações onde se suspeita de corrupção ou inchaço generalizado. Pode causar tempo de inatividade significativo.
Reindexar catálogos do sistema em um banco de dados:
```
REINDEX SYSTEM nome_banco_dados;
```
Isso reconstrói todos os índices nas tabelas do catálogo do sistema dentro de um banco de dados especificado. Deve ser usado com extrema cautela e apenas se você suspeitar de problemas com índices do catálogo do sistema, pois pode impactar toda a funcionalidade do banco de dados e requer acesso exclusivo.

Aviso: Executar REINDEX sem CONCURRENTLY trava mais fortemente e pode bloquear o tráfego normal do aplicativo nos objetos afetados. Trate-o como uma operação de tempo de inatividade, a menos que você tenha testado o comando exato e o comportamento de bloqueio para sua versão do PostgreSQL e tipo de objeto.

Minimizando o Tempo de Inatividade com `REINDEX CONCURRENTLY`

Para sistemas de produção onde o tempo de inatividade é inaceitável, REINDEX CONCURRENTLY é uma opção inestimável. Permite que os índices sejam reconstruídos sem bloquear operações de leitura e gravação simultâneas na tabela.

Como funciona o REINDEX CONCURRENTLY:

Ele constrói uma nova definição de índice simultaneamente com as operações normais.
Ele adquire um bloqueio SHARE UPDATE EXCLUSIVE breve na tabela, que bloqueia DDL (como ALTER TABLE) mas permite declarações DML (INSERT, UPDATE, DELETE) e SELECT.
Em seguida, ele escaneia a tabela para construir o novo índice.
Após a construção inicial, ele adquire outro bloqueio SHARE UPDATE EXCLUSIVE muito curto para aplicar as alterações que ocorreram durante o processo de construção.
Finalmente, ele substitui o índice antigo pelo novo e descarta o índice antigo.

Sintaxe:

REINDEX INDEX CONCURRENTLY nome_indice;

Considerações Importantes para REINDEX CONCURRENTLY:

Execução Mais Lenta: Por precisar lidar com alterações simultâneas, REINDEX CONCURRENTLY é geralmente mais lento do que um REINDEX não concorrente.
Espaço em Disco: Requer espaço em disco para ambas as estruturas de índice, antiga e nova, temporariamente.
Sem Suporte a Transações: REINDEX CONCURRENTLY não pode ser executado dentro de um bloco de transação.
Tratamento de Erros: Se REINDEX CONCURRENTLY falhar (por exemplo, devido a uma violação de restrição única em um índice único), ele deixa para trás um índice inválido. Você deve DROP (descartar) este índice inválido e então executar novamente o comando REINDEX CONCURRENTLY.

Exemplos Práticos de Reindexação

Vamos supor que temos uma tabela produtos com um índice idx_produtos_nome.

Reconstruindo um Único Índice (com Tempo de Inatividade)

Se você pode arcar com uma breve interrupção para o índice afetado:

REINDEX INDEX idx_produtos_nome;

Reconstruindo um Único Índice (Concorrentemente, Tempo de Inatividade Mínimo)

Para sistemas de produção onde a tabela produtos precisa permanecer acessível:

-- Para um índice B-tree:
REINDEX INDEX CONCURRENTLY idx_produtos_nome;

-- Para um índice de chave primária ou restrição única (muitas vezes precisa de tratamento especial, embora REINDEX CONCURRENTLY lide com isso):
-- Se você precisa reconstruir uma chave primária ou índice de restrição única, geralmente reconstrói o índice subjacente.
-- Por exemplo, se 'produtos_pkey' é o índice de chave primária:
REINDEX INDEX CONCURRENTLY produtos_pkey;

Reconstruindo Todos os Índices em uma Tabela

Se você suspeitar que vários índices na tabela produtos estão problemáticos:

-- Isso adquirirá um bloqueio ACCESS EXCLUSIVE na tabela 'produtos'.
REINDEX TABLE produtos;

Versões modernas do PostgreSQL suportam reindexação concorrente de tabela:

REINDEX TABLE CONCURRENTLY produtos;

Isso geralmente é mais fácil do que reconstruir manualmente cada índice, mas ainda consome E/S, CPU e espaço em disco temporário. Em versões mais antigas do PostgreSQL que não suportam esta sintaxe, identifique os índices da tabela e reconstrua cada um com REINDEX INDEX CONCURRENTLY.

Primeiro, identifique todos os índices para a tabela:

SELECT indexname FROM pg_indexes WHERE tablename = 'produtos';

Para controle manual, liste os índices primeiro:

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'produtos'
ORDER BY indexname;

Reconstruindo Todos os Índices em um Banco de Dados

Este é um último recurso e requer tempo de inatividade significativo. Deve ser realizado apenas durante janelas de manutenção programadas.

REINDEX DATABASE nome_do_seu_banco_de_dados;

Alternativamente, em versões suportadas do PostgreSQL, você pode usar REINDEX DATABASE CONCURRENTLY nome_do_seu_banco_de_dados;. Isso evita o pior comportamento de bloqueio, mas ainda é uma operação de manutenção importante e não pode ser executada dentro de um bloco de transação.

Comandos de Manutenção Relacionados e Melhores Práticas

A reindexação é frequentemente parte de uma estratégia de manutenção mais ampla. Outros comandos desempenham um papel vital na prevenção de problemas de índice.

`VACUUM` e `VACUUM FULL`

VACUUM: Recupera espaço ocupado por tuplas mortas, tornando-o disponível para reutilização. Não encolhe os arquivos de tabela ou índice no disco, mas é crucial para prevenir o inchaço. O daemon autovacuum geralmente lida com isso automaticamente.
```
VACUUM sua_tabela;
```
VACUUM FULL: Reescreve a tabela inteira e seus índices associados em um novo arquivo de disco, recuperando o máximo de espaço e eliminando o inchaço. No entanto, ele adquire um bloqueio ACCESS EXCLUSIVE na tabela, bloqueando todas as operações, e deve ser usado com extrema cautela. REINDEX é frequentemente preferido para inchaço de índice.
```
VACUUM FULL sua_tabela;
```

`ANALYZE`

O comando ANALYZE coleta estatísticas sobre o conteúdo das tabelas no banco de dados e as armazena em pg_statistic. O planejador de consultas do PostgreSQL usa essas estatísticas para tomar decisões inteligentes sobre como executar consultas, incluindo se deve usar um índice ou não. Executar ANALYZE após mudanças significativas de dados (ou após reindexação) garante que o planejador tenha informações atualizadas.

ANALYZE sua_tabela;
-- Ou analise todo o banco de dados:
ANALYZE;

Monitorando o Auto-Vacuum

Certifique-se de que o daemon autovacuum está em execução e configurado corretamente. Ele é responsável por executar automaticamente as operações VACUUM e ANALYZE, que são críticas para prevenir o inchaço e manter as estatísticas atualizadas. Um autovacuum mal configurado é uma causa comum de degradação de desempenho.

Agendas de Manutenção Regulares

A manutenção proativa de índices é melhor do que a solução de problemas reativa. Estabeleça uma agenda para:

Monitorar o uso e o tamanho dos índices: Identifique potencial inchaço ou índices não utilizados.
Executar REINDEX CONCURRENTLY: Para tabelas frequentemente atualizadas ou excluídas, ou após migrações de dados significativas.
Revisar logs e configurações do autovacuum: Certifique-se de que está acompanhando a atividade do banco de dados.

Teste e Backup

Sempre teste: Antes de realizar qualquer operação de manutenção importante em um banco de dados de produção, teste-as completamente em um ambiente de teste ou desenvolvimento que espelhe sua configuração de produção.
Sempre faça backup: Tenha um backup recente e confiável do seu banco de dados antes de iniciar qualquer operação REINDEX, especialmente as não concorrentes ou aquelas que visam tabelas/banco de dados inteiros. Embora REINDEX seja geralmente seguro, um backup de banco de dados corrompido é inútil.

Dicas de Solução de Problemas e Avisos

Espaço em Disco: As operações REINDEX (especialmente CONCURRENTLY) requerem espaço em disco temporário significativo – potencialmente até o dobro do tamanho do índice que está sendo reconstruído. Certifique-se de que seu servidor de banco de dados tenha espaço livre amplo.
Impacto no Desempenho: Mesmo REINDEX CONCURRENTLY consumirá recursos de CPU e E/S durante sua operação. Monitore cuidadosamente o desempenho do seu sistema enquanto ele é executado.
Identifique Causas Raiz: Não reindexe repetidamente sem entender por que os índices estão ficando inchados ou corrompidos. Investigue problemas subjacentes, como configurações ineficientes de VACUUM, altas taxas de transação ou problemas de hardware.
Criação de Índice vs. Reindexação: CREATE INDEX CONCURRENTLY é o equivalente de REINDEX INDEX CONCURRENTLY para criar novos índices sem bloqueio. Segue princípios semelhantes e tem limitações semelhantes.

Uma boa manutenção de índices é em parte conhecimento de comandos e em parte contenção. REINDEX CONCURRENTLY é uma ferramenta de reparo útil, mas a reindexação repetida sem entender a carga de trabalho geralmente significa que o mesmo inchaço voltará. Use os comandos acima para confirmar o problema, reconstrua o menor objeto afetado que puder e, em seguida, verifique autovacuum, padrões de atualização, saúde do disco e planos de consulta para que você não precise fazer o mesmo reparo de emergência no próximo mês.