Mudanças de índice sem tempo de inatividade no PostgreSQL: um playbook seguro

Por que mudanças de índice causam tempo de inatividade (e como evitar)\n\nTrabalhos com índices parecem inofensivos. Você está "apenas" adicionando uma estrutura auxiliar. No PostgreSQL, porém, criar, dropar ou trocar um índice pode exigir locks que bloqueiam outras sessões. Se a tabela estiver ativa, essas esperas se acumulam e a aplicação passa a parecer quebrada.\n\nTempo de inatividade raramente aparece como um banner de queda limpo. Costuma se manifestar como páginas que travam, jobs em background que ficam atrasados e uma fila crescente de requisições presas esperando pelo banco. Alguém clica em "Buscar" e recebe um timeout, enquanto ferramentas de suporte e telas administrativas ficam lentas porque consultas simples não conseguem o lock necessário.\n\n"Só roda à noite" falha por duas razões comuns. Muitos sistemas nunca ficam realmente quietos (usuários globais, jobs em lote, ETL, backups). E operações de índice podem levar mais tempo do que você espera porque leem muito dado e competem por CPU e disco. Se a janela acabar no meio do build, você fica entre esperar mais ou interromper o trabalho.\n\nMudanças de índice sem tempo de inatividade não são mágica. Trata-se de escolher a operação que menos bloqueia, definir guardrails (timeouts e checagens de disco) e monitorar o banco enquanto roda.\n\nEste playbook foca em hábitos práticos de produção:\n\n- Prefira builds concorrentes quando leituras e escritas precisam continuar.\n- Monitore locks e progresso do build para reagir cedo.\n- Tenha um caminho de rollback se a mudança causar regressões ou demorar demais.\n\nO que não cobre: teoria profunda de design de índices, tuning amplo de queries, ou refatores de esquema que reescrevem muitos dados.\n\n## O modelo simples de locks por trás do trabalho com índices\n\nPostgreSQL usa locks para manter os dados corretos quando muitas sessões mexem na mesma tabela. Um lock é só uma regra que diz quem pode ler ou escrever um objeto agora e quem deve esperar.\n\nNa maior parte do tempo você não percebe locks porque o PostgreSQL usa modos leves que permitem consultas normais rodarem. DDL é diferente. Ao criar ou dropar um índice, o PostgreSQL precisa de controle suficiente sobre a tabela para manter catálogo e dados consistentes. Quanto mais controle, mais outras sessões podem ser forçadas a esperar.\n\n### Construir um índice vs usar um índice\n\nUsar um índice costuma ser barato em termos de locks. SELECT, UPDATE e DELETE podem ler ou manter índices enquanto outras sessões fazem o mesmo.\n\nConstruir um índice é diferente. O PostgreSQL precisa escanear a tabela, ordenar ou hashear chaves e escrever uma nova estrutura no disco. Esse trabalho leva tempo, e tempo é o que transforma "pequenos locks" em "grandes problemas" em produção.\n\n### O que CONCURRENTLY muda (e o que não muda)\n\nUm CREATE INDEX normal pega um lock forte que bloqueia escritas pela duração do build. CREATE INDEX CONCURRENTLY foi projetado para manter leituras e escritas normais funcionando enquanto o índice é construído.\n\nMas "concurrente" não significa "sem locks". Ainda há janelas curtas de lock no início e no fim, e o build pode falhar ou esperar se algo estiver mantendo locks incompatíveis.\n\nResultados que importam:\n\n- Builds não-concorrentes podem bloquear inserts, updates e deletes na tabela.\n- Builds concorrentes geralmente permitem leituras e escritas, mas podem ser lentos ou parados por transações longas.\n- As etapas finais ainda precisam de locks breves, então sistemas muito ocupados podem ver esperas curtas.\n\n## Escolha a abordagem certa: concorrente ou não\n\nVocê tem duas opções principais ao mudar índices: criar o índice normalmente (rápido, mas bloqueante) ou criá-lo com CONCURRENTLY (geralmente não bloqueante para o tráfego da aplicação, mas mais lento e sensível a transações longas).\n\n### Quando CONCURRENTLY é a escolha certa\n\nUse CREATE INDEX CONCURRENTLY quando a tabela atende tráfego real e você não pode pausar escritas. É tipicamente a escolha mais segura quando:\n\n- A tabela é grande o suficiente para que um build normal leve minutos ou horas.\n- A tabela tem escrituras constantes, não apenas leituras.\n- Você não consegue agendar uma janela real de manutenção.\n- Você precisa construir primeiro, verificar, e só então dropar um índice antigo.\n\n### Quando um build normal é aceitável\n\nUm CREATE INDEX normal pode ser adequado quando a tabela é pequena, o tráfego é baixo ou você tem uma janela controlada. Ele costuma terminar mais rápido e é mais simples de executar.\n\nConsidere a abordagem normal se o build for consistentemente rápido em staging e você puder parar escritas temporariamente (mesmo que por pouco tempo).\n\nSe você precisa de unicidade, decida cedo. CREATE UNIQUE INDEX CONCURRENTLY funciona, mas falhará se existirem valores duplicados. Em muitos sistemas de produção, encontrar e corrigir duplicatas é o verdadeiro projeto.\n\n## Verificações prévias antes de tocar em produção\n\nA maioria dos problemas acontece antes mesmo do comando começar. Algumas checagens ajudam a evitar duas surpresas grandes: bloqueios inesperados e um build de índice que dura muito mais (ou usa mais espaço) do que você planejou.\n\n1) Tenha certeza de que você não está dentro de uma transação. CREATE INDEX CONCURRENTLY falha se você executá-lo após BEGIN, e algumas ferramentas GUI silenciosamente envolvem statements em uma transação. Se tiver dúvida, abra uma sessão nova e rode só o comando do índice ali.\n\n2) Defina expectativas de tempo e disco. Builds concorrentes geralmente demoram mais que os normais e precisam de espaço extra enquanto rodam. Planeje para o novo índice mais a sobrecarga temporária e confirme que você tem folga de disco confortável.\n\n3) Configure timeouts que batam com seu objetivo. Você quer que o build falhe rápido se não conseguir um lock, mas não quer que a sessão morra no meio do build por causa de um statement_timeout agressivo.\n\n4) Capture uma linha de base. Você quer prova de que a mudança ajudou e uma forma rápida de detectar regressões. Registre um snapshot antes: tempo da query lenta, um EXPLAIN (ANALYZE, BUFFERS) representativo e uma visão rápida de CPU, IO, conexões e espaço livre em disco.\n\nConfigurações de sessão seguras que muitas equipes usam como ponto de partida (ajuste conforme suas regras):\n\n```sql

-- Run in the same session that will build the index SET lock_timeout = '2s'; SET statement_timeout = '0'; \n\n## Passo a passo: criar um índice com CONCURRENTLY\n\nUse `CREATE INDEX CONCURRENTLY` quando precisar que o tráfego da aplicação continue e puder tolerar um tempo de build maior.\n\nPrimeiro, decida exatamente o que vai construir:\n\n- Seja específico sobre a ordem das colunas (isso importa).\n- Considere se um índice parcial é suficiente. Se a maioria das consultas filtra por linhas \"ativas\", um índice parcial pode ser menor, mais rápido e mais barato de manter.\n\nUma execução segura se parece com isto: anote o objetivo e o nome do índice, rode o build fora de qualquer bloco de transação, monitore até completar e então verifique se o planner pode usá-lo antes de remover qualquer outro índice.\n\nsql -- Example: speed up searches by email for active users CREATE INDEX CONCURRENTLY idx_users_active_email ON public.users (email) WHERE status = 'active';

-- Validate it exists SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- Check the plan can use it EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM public.users WHERE status = 'active' AND email = '[email protected]'; \n\nPara notas de progresso (úteis para auditorias), registre hora de início, hora de fim e quaisquer esperas observadas. Enquanto roda, você pode consultar `pg_stat_progress_create_index` de outra sessão.\n\nValidação não é só \"o índice existe.\" Confirme que o planner pode escolhê-lo e então observe o tempo real das queries após o deploy. Se o novo índice não for usado, não corra para dropar o antigo. Corrija a consulta ou a definição do índice primeiro.\n\n## Passo a passo: substituir ou remover índices sem bloquear\n\nO padrão mais seguro é adicionar primeiro, deixar o tráfego beneficiar do novo índice e só então remover o antigo. Assim você mantém um fallback funcionando.\n\n### Trocar um índice antigo por um novo (ordem segura)\n\n1) Crie o novo índice com `CREATE INDEX CONCURRENTLY`.\n\n2) Verifique que ele está sendo usado. Cheque `EXPLAIN` nas queries lentas e observe o uso do índice ao longo do tempo.\n\n3) Só depois disso, drope o índice antigo com `DROP INDEX CONCURRENTLY`. Se o risco for alto, mantenha ambos os índices por um ciclo completo de negócio antes de remover qualquer coisa.\n\n### Dropar índices: quando CONCURRENTLY funciona (e quando não)\n\nPara um índice normal que você criou, `DROP INDEX CONCURRENTLY` é geralmente a escolha certa. Dois pontos de atenção: não pode rodar dentro de um bloco de transação, e ainda precisa de locks curtos no início e no fim, então pode ser atrasado por transações longas.\n\nSe o índice existe por conta de uma `PRIMARY KEY` ou `UNIQUE` constraint, normalmente você não pode dropar direto. É preciso alterar a constraint com `ALTER TABLE`, o que pode tomar locks mais fortes. Trate isso como uma operação de manutenção planejada separada.\n\n### Renomear índices para clareza\n\nRenomear (`ALTER INDEX ... RENAME TO ...`) costuma ser rápido, mas evite se ferramentas ou migrations referenciam o nome do índice. Um hábito mais seguro é escolher um nome claro desde o início.\n\n### Se o índice antigo ainda for necessário\n\nÀs vezes dois padrões de consulta precisam de dois índices diferentes. Se queries importantes ainda dependem do antigo, mantenha-o. Considere ajustar o novo índice (ordem das colunas, condição parcial) em vez de forçar a remoção.\n\n## Monitorar locks e progresso enquanto o índice é construído\n\nMesmo com `CREATE INDEX CONCURRENTLY`, você deve observar o que está acontecendo em tempo real. A maioria dos incidentes-surpresa vem de duas coisas: uma sessão bloqueadora que você não notou ou uma transação longa que mantém o build esperando.\n\n### Detectar sessões bloqueadas (quem está bloqueando quem)\n\nComece encontrando sessões esperando por locks:\n\nsql SELECT a.pid, a.usename, a.application_name, a.state, a.wait_event_type, a.wait_event, now() - a.xact_start AS xact_age, left(a.query, 120) AS query FROM pg_stat_activity a WHERE a.wait_event_type = 'Lock' ORDER BY xact_age DESC; \n\nSe precisar do bloqueador exato, relacione `blocked_pid` a `blocking_pid`:\n\nsql SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, now() - blocked.xact_start AS blocked_xact_age, left(blocked.query, 80) AS blocked_query, left(blocking.query, 80) AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY (pg_blocking_pids(blocked.pid)) WHERE blocked.wait_event_type = 'Lock'; \n\n### Observar progresso do build e sinais de "trava"\n\nPostgreSQL expõe progresso de criação de índice. Se você não vir movimento por muito tempo, procure por uma transação longa (muitas vezes uma sessão ociosa segurando um snapshot antigo).\n\nsql SELECT pid, phase, lockers_total, lockers_done, blocks_total, blocks_done, tuples_total, tuples_done FROM pg_stat_progress_create_index; \n\nTambém monitorize pressão do sistema: IO de disco, lag de replicação e tempos de query subindo. Builds concorrentes são mais amigáveis à disponibilidade, mas ainda leem muitos dados.\n\nRegras simples que funcionam bem em produção:\n\n- Espere se o progresso estiver avançando e o impacto ao usuário for baixo.\n- Cancele e reagende se o build estiver preso atrás de uma transação longa que você não pode encerrar com segurança.\n- Faça uma pausa durante pico de tráfego se o IO estiver prejudicando consultas voltadas ao cliente.\n- Termine apenas como último recurso, e só depois de confirmar o que a sessão está fazendo.\n\nPara comunicação na equipe, mantenha atualizações curtas: hora de início, fase atual, o que está bloqueando (se houver) e quando você vai checar de novo.\n\n## Plano de rollback: como voltar com segurança\n\nMudanças de índice só permanecem de baixo risco se você planejar a saída antes de começar. O rollback mais seguro geralmente não é uma desfazer dramático. É simplesmente parar o novo trabalho e manter o índice antigo no lugar.\n\n### Maneiras comuns pelas quais o trabalho de índice falha\n\nA maioria das falhas em produção é previsível: o build atinge um timeout, alguém cancela durante um incidente, o servidor fica com pouco disco, ou o build compete com o tráfego normal a ponto de a latência para o usuário subir.\n\nCom `CREATE INDEX CONCURRENTLY`, cancelar costuma ser seguro para a aplicação porque as queries continuam rodando. A troca é a limpeza: um build cancelado ou que falhou pode deixar um índice inválido para trás.\n\n### Regras seguras de cancelamento e limpeza\n\nCancelar um build concorrente não faz rollback como uma transação normal. O PostgreSQL pode deixar um índice que existe mas não é válido para o planner.\n\nsql -- Cancel the session building the index (use the PID you identified) SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes DROP INDEX CONCURRENTLY IF EXISTS your_index_name; \n\nAntes de dropar, confirme o que você está vendo:\n\nsql SELECT c.relname AS index_name, i.indisvalid, i.indisready FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = 'your_index_name'; \n\nSe `indisvalid = false`, ele não está sendo usado e é seguro removê-lo.\n\nChecklist prático de rollback ao substituir um índice existente:\n\n- Mantenha o índice antigo até que o novo esteja totalmente construído e válido.\n- Se o novo build falhar ou for cancelado, drope o índice inválido com `DROP INDEX CONCURRENTLY`.\n- Se você já tiver droppado o índice antigo, recrie-o com `CREATE INDEX CONCURRENTLY` para restaurar o comportamento anterior.\n- Se pressão de disco causou a falha, libere espaço primeiro e tente de novo.\n- Se timeouts causaram a falha, agende uma janela mais tranquila em vez de forçar.\n\nExemplo: você inicia um novo índice para uma busca administrativa, ele roda por 20 minutos e então alertas de disco disparam. Cancele o build, drope o índice inválido concorrente e mantenha o índice antigo servindo tráfego. Tente novamente depois de liberar espaço, sem causar indisponibilidade visível ao usuário.\n\n## Erros comuns que criam quedas-surpresa\n\nA maioria das quedas relacionadas a índices não é porque o PostgreSQL está \"lento\". Acontece porque um pequeno detalhe transforma uma mudança segura em algo bloqueante.\n\n### 1) Colocar um build concorrente dentro de uma transação\n\n`CREATE INDEX CONCURRENTLY` não pode rodar dentro de um bloco de transação. Muitas ferramentas de migração envolvem cada mudança em uma única transação por padrão. O resultado é ou um erro claro (melhor caso) ou um deploy confuso com retries.\n\nAntes de rodar a migration, confirme que sua ferramenta consegue executar a instrução fora de uma transação externa, ou separe a migration em um passo não transacional especial.\n\n### 2) Iniciá-lo durante pico de tráfego\n\nBuilds concorrentes reduzem bloqueios, mas ainda adicionam carga: leituras extras, escritas extras e mais pressão no autovacuum. Disparar o build durante um deploy quando o tráfego está em pico é uma forma comum de criar uma lentidão que parece uma queda.\n\nEscolha um período tranquilo e trate como qualquer outra manutenção de produção.\n\n### 3) Ignorar transações longas\n\nUma única transação longa pode segurar a fase de limpeza de um build concorrente. O índice pode parecer progredir e então ficar perto do fim esperando que snapshots antigos desapareçam.\n\nCrie o hábito: cheque por transações longas antes de começar e de novo se o progresso estagnar.\n\n### 4) Dropar a coisa errada (ou quebrar uma constraint)\n\nTimes às vezes droppam um índice pelo nome de cabeça ou removem um índice que sustenta uma regra de unicidade. Se você dropar o objeto errado, pode perder enforcement (constraints únicas) ou regredir performance imediatamente.\n\nChecklist rápido de segurança: verifique o nome do índice no catálogo, confirme se ele suporta uma constraint, confira esquema e tabela, e mantenha \"criar novo\" separado de \"dropar antigo\". Tenha o comando de rollback pronto antes de começar.\n\n## Exemplo realista: acelerar uma busca administrativa\n\nUm ponto de dor comum é uma busca administrativa que parece instantânea em staging, mas arrasta em produção. Suponha que você tem uma grande tabela `tickets` (dezenas de milhões de linhas) por trás de um painel interno, e agentes frequentemente buscam \"tickets abertos de um cliente, mais novos primeiro.\"\n\nA query é assim: \n\nsql SELECT id, customer_id, subject, created_at FROM tickets WHERE customer_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 50; \n\nUm índice completo em `(customer_id, status, created_at)` ajuda, mas adiciona overhead de escrita para cada atualização de ticket, inclusive para os fechados. Se a maioria das linhas não está `open`, um índice parcial costuma ser a vitória mais simples: \n\nsql CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx ON tickets (customer_id, created_at DESC) WHERE status = 'open'; ```\n\nLinha do tempo segura em produção:\n\n- Preflight: confirme que a forma da query é estável e que a tabela tem espaço livre suficiente para construir o novo índice.\n- Build: rode CREATE INDEX CONCURRENTLY em uma sessão separada com timeouts claros.\n- Validação: rode ANALYZE tickets; e confirme que o planner usa o novo índice.\n- Limpeza: quando estiver confiante, drope qualquer índice antigo redundante com DROP INDEX CONCURRENTLY.\n\nComo é o sucesso:\n\n- A busca administrativa cai de segundos para dezenas de milissegundos para clientes típicos.\n- Leituras e escritas normais continuam funcionando durante o build.\n- CPU e IO de disco sobem durante o build, mas ficam dentro dos limites de segurança normais.\n- Você tem números claros antes/depois: tempo de query, linhas varridas e histórico de locks.\n\n## Checklist rápido e próximos passos\n\nTrabalhos com índices são mais seguros quando tratados como um pequeno release de produção: preparar, observar enquanto roda e então verificar o resultado antes de limpar.\n\nAntes de começar:\n\n- Ajuste timeouts para que um lock surpresa não fique pendurado para sempre.\n- Confirme espaço livre em disco suficiente para o novo build.\n- Procure por transações longas que possam desacelerar o build.\n- Escolha uma janela de baixo tráfego e defina o que significa "pronto".\n- Escreva seu plano de rollback agora.\n\nEnquanto roda:\n\n- Observe bloqueios e cadeias de espera.\n- Acompanhe progresso com pg_stat_progress_create_index.\n- Fique de olho em sintomas na aplicação: taxa de erros, timeouts e endpoints lentos ligados à tabela.\n- Esteja pronto para cancelar se esperas por locks aumentarem ou timeouts afetarem usuários.\n- Registre o que aconteceu: hora de início, hora de término e quaisquer alertas.\n\nDepois de terminar, confirme que o índice é válido, rode uma ou duas queries-chave para ver o plano e o tempo melhorarem, e só então remova índices antigos de forma não bloqueante.\n\nSe você fizer isso mais de uma vez, transforme em um passo de entrega repetível: um pequeno runbook, um ensaio em staging com dados parecidos com produção e um responsável claro monitorando o build.\n\nSe você está construindo ferramentas internas ou painéis administrativos com AppMaster (appmaster.io), trate mudanças de banco — como builds de índice — como parte do mesmo checklist de release das suas atualizações de backend: medidas, monitoradas e com um rollback que você possa executar rapidamente.