guia prático: reconstruir índices no PostgreSQL em produção usando CREATE INDEX CONCURRENTLY e índices parciais (passo a passo seguro)

Por Kevin Marcondes · Publicado em 28/10/2026 · 10 min de leitura

Este guia prático: reconstruir índices no PostgreSQL em produção usando CREATE INDEX CONCURRENTLY e índices parciais mostra quando aplicar cada técnica, como preparar o ambiente, executar com segurança operacional e validar o resultado. Você verá pré‑requisitos, passos defensivos, monitoramento, rollback e cuidados com UNIQUE/PK e réplicas, reduzindo bloqueios e evitando regressões de plano em tabelas ativas.

Quando reconstruir um índice e sinais de que vale a pena

Reconstruir índices pode recuperar desempenho perdido por bloat, corrigir definições inadequadas e reduzir I/O. Em produção, o desafio é fazer isso com impacto mínimo.

Procure estes sinais antes de agir: consultas degradando, mais varreduras sequenciais do que o esperado, índices maiores que a própria tabela e aumento de tempo de VACUUM.

Se houver suspeita de corrupção ou estatísticas quebradas, priorize a integridade antes da troca. Veja o guia de recuperação específico em como recuperar índices e stats.

Mudanças de filtros ao longo do tempo geram índices subaproveitados. Um índice parcial bem desenhado pode reduzir bloat e melhorar a seletividade. Coordene alterações de esquema e deploys com o seu processo de migrations e rollbacks em PostgreSQL.

Para manter a densidade ideal de palavras‑chave neste conteúdo: guia prático: reconstruir índices no PostgreSQL em produção usando CREATE INDEX CONCURRENTLY e índices parciais é exatamente o que você vai aplicar aqui.

• Consultas mudaram e o índice atual não cobre os filtros principais.
• Índice cresceu demais após muitas atualizações/deletes (bloat).
• Planner evita o índice por baixa seletividade.
• Necessidade de tornar UNIQUE uma coluna já populada.

Como o CREATE INDEX CONCURRENTLY reduz impacto (e quando não usar)

CREATE INDEX CONCURRENTLY (CIC) evita bloquear leituras e escritas por longos períodos. Ele faz múltiplas passagens na tabela para capturar alterações concorrentes e só toma pequenos locks na fase final.

Limitações importantes: CIC é mais lento que o CREATE INDEX tradicional, exige espaço extra em disco e não pode rodar dentro de um bloco de transação. Não use em janelas curtíssimas sem margem, pois a duração varia conforme a carga.

Para constraints existentes (PK/UNIQUE) ou para reindexar sem trocar nomes, considere REINDEX CONCURRENTLY. Em cenários com relatórios pesados e carga mista, combine com práticas do serviço de hardening e tuning de PostgreSQL para ERPs.

• Evita bloqueio prolongado de SELECT/INSERT/UPDATE/DELETE.
• Consome mais I/O e tempo por múltiplas varreduras.
• Exige espaço extra equivalente, em geral, ao tamanho do índice alvo.
• Falhas deixam um índice inválido que precisa ser removido.

Checklist pré-execução: segurança, espaço e concorrência

Antes de criar um índice concorrente, valide saúde do autovacuum, espaço livre e sessões longas. CIC não roda dentro de TRANSACTION e pode gerar muito WAL, aumentando lag em réplicas.

Defina limites de tempo razoáveis para sua sessão, prepare janela para a troca e garanta que não há migrations conflitantes pendentes. Veja também como organizar migrations e rollbacks.

Se sua base sofre picos de conexões, alinhe timeouts e janela de manutenção para evitar impacto durante o rebuild; veja o guia sobre Too many connections no PostgreSQL.

Use consultas abaixo como checklist. Substitua nomes de tabela/colunas conforme seu caso.

• Confirme espaço livre para o novo índice e WAL extra.
• Verifique sessões longas e travas potenciais.
• Procure duplicados antes de criar UNIQUE.
• Ajuste maintenance_work_mem para acelerar a construção.

-- Ajustes temporários de tempo (opcional)
SET lock_timeout = '3s';
SET statement_timeout = '1h';

-- 1) Tamanho da tabela e de índices
SELECT 
  pg_size_pretty(pg_relation_size('public.my_table')) AS table_size,
  pg_size_pretty(pg_indexes_size('public.my_table'))  AS indexes_size;

-- 2) Sessões e transações longas
SELECT pid, usename, xact_start, now()-xact_start AS age, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY age DESC;

-- 3) Lag de replicação (se houver standbys)
SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 4) Procure duplicados antes de UNIQUE
-- Substitua col1, col2 pelas colunas almejadas
SELECT col1, col2, COUNT(*)
FROM public.my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1
LIMIT 50;

Passo a passo: reconstruir um índice existente com downtime imperceptível

Estratégia geral: crie um novo índice com CREATE INDEX CONCURRENTLY, valide, troque o antigo e, se desejar, renomeie o novo para manter a convenção. Em tabelas muito ativas, priorize horários de menor pico.

Fluxo sugerido: criar novo índice, checar validade, soltar o antigo com DROP INDEX CONCURRENTLY e renomear. Evite IF NOT EXISTS com definições ambíguas; prefira nomes explícitos e revisão prévia.

Após a troca, monitore o uso do novo índice e mantenha o antigo por um curto período apenas se for necessário reverter rápido. Se jobs concorrentes causam bloqueios, avalie seu cron conforme o guia de bloqueios e deadlocks no PostgreSQL.

Reforçando a intenção deste guia prático: reconstruir índices no PostgreSQL em produção usando CREATE INDEX CONCURRENTLY e índices parciais é o caminho para reduzir bloqueios mantendo previsibilidade.

• Nomeie o novo índice com sufixo _new para clareza.
• Valide indisvalid antes de trocar.
• DROP INDEX CONCURRENTLY no antigo para evitar bloqueios.
• Renomeie o novo se o nome padrão for referenciado em queries ou ferramentas.

-- 1) Criar o novo índice (definição equivalente ao antigo)
CREATE INDEX CONCURRENTLY idx_my_table_created_at_new
  ON public.my_table (created_at);

-- 2) Verificar que o índice ficou válido
SELECT i.relname, idx.indisvalid
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
WHERE i.relname = 'idx_my_table_created_at_new';

-- 3) Remover o antigo de forma não bloqueante
DROP INDEX CONCURRENTLY IF EXISTS idx_my_table_created_at;

-- 4) (Opcional) Padronizar o nome
ALTER INDEX IF EXISTS idx_my_table_created_at_new
  RENAME TO idx_my_table_created_at;

UNIQUE e PK: troca segura com bloqueios mínimos

Para tornar UNIQUE uma coluna já populada sem bloquear, crie o índice único com CONCURRENTLY e, em seguida, anexe-o como constraint. A adição/remoção de constraints exige lock rápido da tabela, então planeje a janela.

Se já existe uma PK/UNIQUE problemática, REINDEX CONCURRENTLY pode ser mais direto. Quando for preciso mudar colunas da chave, crie um novo UNIQUE CONCURRENTLY e troque a constraint para usar esse índice.

Antes do UNIQUE, elimine duplicados; se existirem, o comando falhará e deixará um índice inválido que você precisará descartar.

• Crie UNIQUE CONCURRENTLY primeiro.
• Troque a constraint para usar o novo índice.
• Espere um lock curto ao alterar constraints.
• Deduplicate antes para evitar falhas.

-- 1) Criar índice único de forma concorrente
CREATE UNIQUE INDEX CONCURRENTLY idx_my_table_email_uq_new
  ON public.my_table (email);

-- 2) Trocar a constraint para usar o novo índice (lock breve)
ALTER TABLE public.my_table
  DROP CONSTRAINT IF EXISTS my_table_email_key,
  ADD CONSTRAINT my_table_email_key
    UNIQUE USING INDEX idx_my_table_email_uq_new;

-- Observação: se a constraint antiga existia, o DROP pode remover
-- o índice antigo automaticamente. Limpeza manual só se necessário.

Índices parciais: quando brilham e como desenhá-los

Índices parciais cobrem um subconjunto de linhas definido por um predicado WHERE. Eles são excelentes quando a maioria das consultas filtra por um estado específico, reduzindo tamanho e bloat.

Regras práticas: a condição do índice deve refletir o filtro mais comum. O planner só usará o índice se o predicado da consulta implicar o do índice. Prefira expressões simples e funções imutáveis.

Valide o padrão real de filtros no seu workload. Um índice parcial mal escolhido pode virar peso morto. Em cenários de relatórios intensos, combine o desenho do índice com tuning de consultas e hardware — veja o serviço de hardening e tuning de PostgreSQL para ERPs.

• Use quando poucos estados são consultados com frequência.
• Predicados simples, com colunas diretas.
• Evite funções voláteis no predicado.
• Garanta que as consultas repitam o filtro íntegro.

-- Exemplo: foco em pedidos ativos e não deletados
CREATE INDEX CONCURRENTLY idx_orders_active_created_at
  ON public.orders (created_at)
  WHERE status = 'active' AND deleted_at IS NULL;

-- Padrão de consulta que aproveita o índice
EXPLAIN ANALYZE
SELECT id
FROM public.orders
WHERE status = 'active' AND deleted_at IS NULL
  AND created_at >= now() - interval '7 days';

Monitoramento, validação e rollback sem sustos

Monitore o andamento com a visão de progresso e valide o uso após a troca. Se o comando falhar, um índice inválido pode ficar para trás; limpe com DROP INDEX CONCURRENTLY.

Confirme que o novo índice está sendo usado com pg_stat_user_indexes e EXPLAIN. Se a carga mudar, reavalie estatísticas e planos.

Em ambientes com réplicas, observe o lag durante a operação; trocas de índices geram WAL significativo. Se detectar anomalias severas, reveja consistência e estatísticas conforme o guia de recuperação de índices e stats.

Para fechar o ciclo, este guia prático: reconstruir índices no PostgreSQL em produção usando CREATE INDEX CONCURRENTLY e índices parciais prioriza passos auditáveis e reversíveis.

• Acompanhe pg_stat_progress_create_index.
• Cheque idx_scan depois do deploy.
• Remova índices inválidos que sobraram.
• Monitore lag em réplicas durante a janela.

-- Progresso da criação do índice
SELECT pid, phase, relid::regclass AS table,
       index_relid::regclass AS index, tuples_total, tuples_done
FROM pg_stat_progress_create_index;

-- Uso de índices após a troca
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'my_table'
ORDER BY idx_scan DESC;

-- Gerar comandos para limpar índices inválidos
SELECT 'DROP INDEX CONCURRENTLY ' || quote_ident(relname) || ';' AS drop_cmd
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid AND relkind = 'i';

Perguntas frequentes

Posso executar CREATE INDEX CONCURRENTLY dentro de uma transação?

Não. CREATE INDEX CONCURRENTLY não pode rodar dentro de um bloco de transação. Abra uma sessão dedicada para a operação. Se sua ferramenta de migrations inicia transações automaticamente, desative para esse passo específico.

Quanto espaço extra preciso para reconstruir um índice?

Depende do tamanho do índice alvo e do churn de dados durante a construção. Planeje pelo menos espaço equivalente ao novo índice, além de WAL adicional. Em tabelas muito ativas, a necessidade pode aumentar até a conclusão.

E se houver duplicados ao criar um índice UNIQUE CONCURRENTLY?

O comando falha e deixa um índice inválido. Você deve identificar e remover ou consolidar duplicados antes de tentar novamente. Execute uma consulta com GROUP BY e HAVING COUNT(*) > 1 nas colunas-alvo para localizar ocorrências.

Índices parciais funcionam se o WHERE da consulta não for idêntico?

O planner usa o índice parcial quando o predicado da consulta implica o predicado do índice. Se o filtro não equivaler logicamente ao do índice, ele pode ser ignorado. Mantenha os filtros consistentes entre queries e índice.

Quando escolher REINDEX CONCURRENTLY em vez de CREATE INDEX CONCURRENTLY?

Quando você precisa reindexar uma constraint existente sem mudar colunas, REINDEX CONCURRENTLY tende a ser mais simples, mantendo nomes. Se vai alterar definição ou adotar índice parcial, crie um novo índice com CONCURRENTLY e troque.

Veja também

• Guia prático: como configurar failover ativo-passivo entre duas VPS com Keepalived, HAProxy e lsyncd para uploads sincronizados
• Guia: checklist técnico para preparar VPS Debian com Docker Compose para aplicações PHP em produção
• Guia prático para dimensionamento de VPS e tuning de PHP‑FPM para lojas Magento/OpenMage com picos sazonais: do sizing ao teste de carga
• Guia: como planejar e executar testes de carga em aplicações PHP monolito usando k6 e Docker Compose — passo a passo