CREATE INDEX CONCURRENTLY: indexar sem downtime no PostgreSQL

Um CREATE INDEX comum pega um lock que impede escritas na tabela durante toda a construcao. Em uma tabela minuscula voce nem percebe; em uma tabela quente com milhoes de linhas, congelar cada INSERT e UPDATE por minutos e um incidente. O CREATE INDEX CONCURRENTLY constroi o indice sem bloquear as escritas, e e o burro de carga das migracoes sem downtime.

O que um CREATE INDEX comum bloqueia

Um CREATE INDEX comum pega um lock SHARE na tabela: as leituras continuam fluindo, mas qualquer escrita entra na fila ate a construcao terminar.

-- locks out writes to orders until the index is built
CREATE INDEX idx_orders_user_id ON orders (user_id);

Em uma tabela orders com dezenas de milhoes de linhas a construcao leva minutos, e a aplicacao nao consegue inserir pedidos durante todo esse tempo. A variante com CONCURRENTLY resolve exatamente isso:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

O Postgres faz duas passagens pela tabela e espera as transacoes em andamento, entao a construcao e mais lenta e mais pesada para o disco, mas as escritas nunca travam. A sintaxe e identica no resto, incluindo indices compostos:

CREATE INDEX CONCURRENTLY idx_orders_user_status
    ON orders (user_id, status);

Por que nao roda dentro de uma transacao

O CREATE INDEX CONCURRENTLY nao pode rodar dentro de um bloco de transacao. O comando gerencia varias transacoes internas e espera por outras sessoes, entao envolve-lo em BEGIN ... COMMIT e um erro:

BEGIN;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);  -- ERROR
COMMIT;

A consequencia pratica para migracoes: esse comando precisa rodar fora de uma transacao. Muitas ferramentas de migracao envolvem cada passo em uma transacao por padrao, entao para um passo com CONCURRENTLY voce precisa desativar isso (disable_ddl_transaction! no Rails, atomic = False no Django, um passo nao transacional dedicado em outras).

• Um indice por passo de migracao.
• Nao misture CONCURRENTLY com outro DDL no mesmo passo.
• Reserve tempo: em tabelas grandes sao dezenas de minutos.

O indice INVALID que uma falha deixa

Essa e a parte mais traicoeira. Se uma construcao CONCURRENTLY falha (linhas duplicadas em um indice UNIQUE, um cancelamento, uma conexao caida), o indice nao some; ele fica no catalogo marcado como INVALID. O planejador o ignora, mas ele ainda assim deixa as escritas mais lentas, ocupa espaco e impede recriar um indice com o mesmo nome.

Para encontra-los:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

Tambem ajuda acompanhar o progresso de uma construcao em andamento:

SELECT phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;

Corrigir um indice INVALID e simples: apague-o e reconstrua-o.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Pegadinha: nao "conserte" um indice INVALID cegamente com REINDEX. Antes do Postgres 12 um REINDEX em um indice comum pega um lock pesado; a partir do 12 existe o REINDEX INDEX CONCURRENTLY, feito justamente para uma reconstrucao sem downtime.

DROP INDEX CONCURRENTLY e idempotencia

Apagar um indice tambem pega um lock: um DROP INDEX comum em uma tabela quente congela leituras e escritas no momento da remocao. O DROP INDEX CONCURRENTLY remove esse lock do mesmo jeito que a construcao:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_status;

Ele tem as mesmas restricoes: nada de bloco de transacao, e voce nao pode apagar varios indices em um unico comando. Mas IF EXISTS torna o passo idempotente, entao uma reexecucao nao vai falhar.

O padrao seguro completo para "recriar um indice sem downtime":

DROP INDEX CONCURRENTLY IF EXISTS idx_employees_dept;
CREATE INDEX CONCURRENTLY idx_employees_dept ON employees (dept, salary);

Como outros bancos diferem

• MySQL/InnoDB: nao existe a palavra-chave CONCURRENTLY, mas o DDL online e nativo. CREATE INDEX ... ALGORITHM=INPLACE, LOCK=NONE (ou ALTER TABLE ... ADD INDEX) normalmente constroi o indice enquanto as escritas continuam. O motor decide se LOCK=NONE e possivel e da erro se nao for.
• ClickHouse: indices secundarios de skip sao adicionados com ALTER TABLE ... ADD INDEX, o que e barato, mas o indice so se aplica aos dados existentes apos MATERIALIZE INDEX, que faz o trabalho em segundo plano.

No PostgreSQL a regra e simples: em producao, construa e apague cada indice com CONCURRENTLY, como seu proprio passo nao transacional, e sempre verifique pg_index.indisvalid depois da migracao.