A trava que um add_index simples pega

Dois cursos que já vieram antes

"SQL: índices e performance" mostrou por que um índice deixa uma consulta rápida: uma B-tree em vez de uma varredura sequencial, com EXPLAIN ANALYZE confirmando a diferença. A aula "add_index numa migration real do Rails", desse curso, mostrou add_index rodando dentro das migrations create_courses e AddPerformanceIndexesToCoreTables deste app. Este curso não repete nenhuma das duas. Ele faz uma pergunta que só importa quando uma tabela tem linhas reais e a aplicação continua escrevendo nela enquanto a migration roda: o que o banco trava, e por quanto tempo, para montar esse índice?

Medido na conexão deste app

Uma tabela de rascunho com 50.000 linhas, a conexão PostgreSQL deste app, bin/rails runner, cronometrado:

conn.add_index :scratch_idx_timing, :n
# add_index comum em 50.000 linhas: 30.8ms

Dezenas de milissegundos; novas execuções ficaram entre uns 20 e 31 ms. Numa tabela desse tamanho, numa máquina de desenvolvimento sem mais nada mexendo nela, um add_index simples não é lento, e não adianta montar uma história assustadora em cima de um número que não a sustenta. As tabelas deste app contam a mesma história: algumas centenas de cursos, alguns milhares de aulas, pequenas o bastante para todo add_index que ele rodou terminar na hora. Essa é a armadilha. O custo nunca foi de milissegundos.

O que add_index trava

Segundo a documentação do PostgreSQL, um CREATE INDEX simples pega um ShareLock na tabela durante toda a construção. Um ShareLock deixa outras transações continuarem rodando SELECT, então as leituras não são afetadas, mas bloqueia todo INSERT, UPDATE e DELETE na tabela até o índice ficar pronto. Numa tabela de rascunho de 50.000 linhas isso dura algumas dezenas de milissegundos, e ninguém percebe. Numa tabela de produção com cem milhões de linhas recebendo escritas, o mesmo ShareLock pode ficar preso por minutos. Toda escrita nessa tabela, de toda requisição e todo job em segundo plano, espera atrás dele o tempo todo.

Esse é o mecanismo: o número de linhas muda quanto tempo a trava dura, e não se ela acontece. Este app nunca sentiu isso, porque nunca teve uma tabela grande o bastante, com escritas simultâneas suficientes, para a duração importar. A migration que este curso monta funciona igual numa tabela vazia ou enorme. Por isso vale aprender aqui, numa tabela pequena o bastante para entender, antes de precisar numa que não é.

Pontos-chave

• "SQL: índices e performance" trata de por que índices aceleram consultas e de como add_index funciona dentro de uma migration. Este curso começa depois dos dois, com o que acontece com as escritas de uma tabela enquanto o índice é montado.
• Na conexão PostgreSQL deste app, um add_index simples sobre 50.000 linhas leva dezenas de milissegundos, o que é invisível nos tamanhos de tabela deste app.
• Segundo a documentação do PostgreSQL, um CREATE INDEX simples segura um ShareLock, bloqueando todo INSERT/UPDATE/DELETE (não SELECT) na tabela durante toda a construção.
• O risco cresce com a tabela, não com a sintaxe: o mesmo add_index invisível numa tabela pequena pode segurar todas as escritas por minutos numa tabela de produção movimentada.