SQL Academy · Aula

Manutenção de índices e fragmentação

Diagnostique a fragmentação dos índices, reconstrua-os com REINDEX CONCURRENTLY e remova índices não utilizados com segurança.

Manutenção de índices e fragmentação é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.

Por que os índices aumentam de tamanho

O PostgreSQL usa MVCC: UPDATE grava uma nova versão da linha, mantendo a antiga visível para as transações existentes. Existem entradas de índice para as duas versões. Com o tempo:

Tabelas com muitas atualizações acumulam entradas de índice obsoletas
Os índices ficam maiores do que o necessário
As buscas ficam mais lentas à medida que a árvore B fica mais profunda

Diagnóstico do inchaço

As consultas para verificar o inchaço não são triviais. Ferramentas comuns:

Extensão pgstattuple
Relatórios do pg_repack
Consultas de diagnóstico do inchaço do check_postgres e de ferramentas de monitoramento

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

Reconstrói o índice. A forma clássica adquire um bloqueio ACCESS EXCLUSIVE — uma escolha ruim em produção:

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12+)

Variante que não bloqueia — leitores e escritores continuam durante a reconstrução:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Índices não utilizados

Um índice não utilizado torna toda escrita mais lenta, mas não acelera nenhuma leitura. Encontre-os:

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Remova os índices não utilizados

Remova-os — mas primeiro verifique todos os ambientes e períodos. Índices usados em relatórios ocasionais parecem não utilizados na maior parte do tempo.

DROP INDEX CONCURRENTLY old_unused_idx;

Índices duplicados

Às vezes, o mesmo índice é criado por uma restrição e por um CREATE INDEX manual. Verifique se há duplicatas em pg_indexes e remova a redundante:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Amplificação de escrita dos índices

Cada INSERT/UPDATE/DELETE atualiza todos os índices relevantes. Três índices em uma tabela muito acessada = custo de escrita 3 vezes maior. Adicione apenas os índices que compensam.

Lista pendente do GIN

Os índices GIN agrupam as atualizações em uma lista pendente. Limpe-a manualmente ou dependa do autovacuum:

SELECT gin_clean_pending_list('events_data_gin');

VACUUM limpa as entradas dos índices

VACUUM (abordado na lição sobre MVCC) remove as entradas de índice obsoletas das páginas do heap. Sem o autovacuum, os índices crescem indefinidamente.

Monitoramento do tamanho dos índices

Acompanhe o tamanho dos índices ao longo do tempo:

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: reescreva tabelas online

Para um inchaço grave, pg_repack reescreve a tabela e os índices online — sem bloquear a tabela inteira. Instale-o como um pacote do OS e uma extensão do PostgreSQL.

Recapitulação

Os índices precisam de cuidados.

O inchaço causado pelo MVCC é normal — gerencie-o com VACUUM e REINDEX CONCURRENTLY
Remova os índices não utilizados
Evite duplicatas
Cada novo índice torna as escritas mais lentas — planeje com cuidado

Verificação rápida

Qual comando do PostgreSQL reconstrói um índice WITHOUT bloquear as escritas?

Perguntas Frequentes

A aula “Manutenção de índices e fragmentação” é grátis?

Sim — o texto completo de “Manutenção de índices e fragmentação” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.

O que vou aprender em “Manutenção de índices e fragmentação”?

Diagnostique a fragmentação dos índices, reconstrua-os com REINDEX CONCURRENTLY e remova índices não utilizados com segurança. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar SQL Academy?

Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Manutenção de índices e fragmentação”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de SQL Academy?

Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para SQL Academy