ALTER TABLE que para a produção: a fila de locks atrás de uma migração inocente

O deploy rodava a migração antes de subir a nova versão, como manda o roteiro. Naquele dia, um analista tinha disparado um relatório de BI que abre uma transação, lê a tabela orders e passa minutos agregando. Essa transação segura um ACCESS SHARE em orders, um lock fraco que ninguém percebe. Quando a migração pediu o ACCESS EXCLUSIVE que o ALTER TABLE exige, ela não pôde ser atendida e entrou na fila de espera do lock.

Até aqui o dano seria só um deploy lento. O problema é o que acontece com as consultas seguintes: um SELECT comum da API pede ACCESS SHARE, que não conflita com o ACCESS SHARE do relatório, mas conflita com o ACCESS EXCLUSIVE que está esperando na frente. O Postgres respeita a ordem da fila, então a API passa a esperar atrás da migração. Em 12 segundos as 50 conexões do pool estavam ocupadas esperando, e o serviço ficou mudo até o relatório terminar.

t=0      relatorio de BI abre transacao e le orders (ACCESS SHARE), vai durar 5 min 40 s
t=10 s   migracao: ALTER TABLE orders ADD COLUMN ... pede ACCESS EXCLUSIVE -> espera o relatorio
t=10 s   API: SELECT em orders pede ACCESS SHARE -> entra na fila ATRAS do ALTER
t=12 s   pool de 50 conexoes inteiro preso na fila, health check ainda responde 200
t=15 s   usuarios veem timeout; novas requisicoes seguem chegando
t=5m40   relatorio termina -> ALTER pega o lock, roda em 40 ms, solta -> a fila drena

Com lock_timeout = 2 s:
t=12 s   ALTER desiste (55P03), libera a fila; API volta em milissegundos
t=14 s   migracao tenta de novo com backoff, ate o relatorio terminar

Dois sinais enganaram o time. O health check não toca a tabela orders e continuou verde, então o balanceador manteve a instância no ar. E o gráfico de CPU do banco estava quase zerado, porque nada estava executando: tudo estava esperando. Um incidente de lock não parece um incidente de banco nos painéis comuns.

O ALTER TABLE costuma pedir o modo mais forte, o ACCESS EXCLUSIVE, que conflita com todos os outros modos, inclusive o ACCESS SHARE de um SELECT. A regra de enfileiramento é simples: um pedido de lock espera por todos os locks já concedidos que conflitam com ele e também pelos pedidos conflitantes que chegaram antes. Isso evita que um pedido forte morra de inanição, mas dá a ele o poder de bloquear qualquer coisa que venha depois.

O resultado é uma assimetria perigosa. O custo do ALTER, uma vez com o lock, é de milissegundos. O custo de esperar o lock é ilimitado e é pago por todos os outros. O tempo da migração não é o tempo do comando, é o tempo da transação mais longa que estiver tocando a tabela naquele momento, mais o tempo que a fila leva para drenar depois que o lock é liberado.

A tabela mostra por que a pergunta certa nunca é "quanto o comando demora", e sim "que lock ele pede e por quanto tempo vai esperar para consegui-lo". Mesmo as operações rápidas dependem de conseguir o ACCESS EXCLUSIVE, e é essa espera que precisa de um teto.

Durante o travamento, três consultas contam a história inteira. A primeira lista as sessões em espera de lock e quem está na frente da fila, usando pg_blocking_pids. A segunda lista os pedidos de lock não concedidos em pg_locks, onde o ALTER aparece com o modo AccessExclusiveLock. A terceira, que vale rodar antes de qualquer migração, lista transações abertas há mais de 30 segundos, as candidatas a segurar a tabela.

-- Quem esta esperando lock e quem esta na frente da fila.
SELECT a.pid,
       a.state,
       a.wait_event_type,
       now() - a.query_start     AS running_for,
       pg_blocking_pids(a.pid)   AS blocked_by,
       left(a.query, 70)         AS query
  FROM pg_stat_activity a
 WHERE a.datname = current_database()
   AND a.backend_type = 'client backend'
   AND (a.wait_event_type = 'Lock'
        OR a.pid IN (SELECT unnest(pg_blocking_pids(x.pid)) FROM pg_stat_activity x))
 ORDER BY a.query_start;

-- Pedidos de lock ainda nao concedidos: o ALTER aparece aqui, esperando.
SELECT pid, mode, relation::regclass AS table_name
  FROM pg_locks
 WHERE NOT granted;

-- Antes de migrar: transacoes abertas ha mais de 30 s que podem segurar a tabela.
SELECT pid, now() - xact_start AS open_for, state, left(query, 70) AS query
  FROM pg_stat_activity
 WHERE xact_start < now() - interval '30 seconds'
   AND pid <> pg_backend_pid();

A leitura típica mostra uma cadeia: o relatório de BI no começo, sem esperar nada e com a transação mais antiga; o ALTER logo depois, esperando por ele; e dezenas de SELECT da API esperando pelo ALTER, todos com wait_event_type igual a Lock. A tentação é matar o ALTER, e isso funciona na hora, porque libera a fila. Mas a causa que volta amanhã é o relatório, não a migração.

A defesa principal é fazer a migração perder a paciência rápido. Com lock_timeout de 2 segundos, o ALTER espera no máximo 2 segundos na fila; se não conseguir, falha com o erro 55P03 e libera todo mundo que estava atrás dele. O custo para a aplicação cai de minutos para no máximo 2 segundos de lentidão. A migração então tenta de novo, com espera crescente e um pouco de aleatoriedade, até encontrar uma janela em que o relatório não esteja segurando a tabela.

// migrate.js
const LOCK_TIMEOUT_MS = 2000;
const STATEMENT_TIMEOUT_MS = 30000;
const MAX_ATTEMPTS = 8;

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

export async function applyDdl(pool, { name, sql, transactional = true }) {
  for (let attempt = 1; attempt <= MAX_ATTEMPTS; attempt += 1) {
    const client = await pool.connect();
    try {
      if (transactional) {
        // Cada tentativa e uma transacao curta: ou pega o lock em ate 2 s, ou desiste e libera a fila.
        await client.query('BEGIN');
        await client.query("SELECT set_config('lock_timeout', $1, true)", [LOCK_TIMEOUT_MS + 'ms']);
        await client.query("SELECT set_config('statement_timeout', $1, true)", [STATEMENT_TIMEOUT_MS + 'ms']);
        await client.query(sql);
        await client.query('COMMIT');
      } else {
        // CREATE INDEX CONCURRENTLY nao roda em transacao; o limite vai na sessao e e desfeito no fim.
        await client.query('SET lock_timeout = ' + LOCK_TIMEOUT_MS);
        await client.query(sql);
      }
      return { name, attempt };
    } catch (error) {
      await client.query('ROLLBACK').catch(() => {});
      // Erro 55P03 (lock_not_available) e esperado: espera com backoff e jitter e tenta de novo.
      if (error.code !== '55P03' || attempt === MAX_ATTEMPTS) throw error;
      const wait = Math.min(500 * 2 ** attempt, 15000) + Math.floor(Math.random() * 500);
      console.warn(name + ': lock not acquired (attempt ' + attempt + '), retrying in ' + wait + ' ms');
      await sleep(wait);
    } finally {
      if (!transactional) await client.query('RESET lock_timeout').catch(() => {});
      client.release();
    }
  }
}

• Use set_config com o terceiro argumento verdadeiro (equivalente a SET LOCAL) dentro da transação: o limite some no COMMIT ou ROLLBACK e não contamina a conexão do pool.
• Em operações fora de transação, como CREATE INDEX CONCURRENTLY, o limite é de sessão: restaure com RESET antes de devolver a conexão ao pool.
• Trate somente 55P03 como erro retentável. Qualquer outro erro de DDL, como coluna duplicada, deve parar a migração.
• Defina um número máximo de tentativas e falhe com mensagem clara. Uma migração que tenta para sempre é só outra forma de espera sem teto.
• Rode o statement_timeout junto, para que um DDL que escolheu varrer a tabela não segure o lock por minutos.

O timeout limita o dano da espera, mas o desenho da migração decide quanto tempo o lock fica com você depois de conseguido. A regra é separar o que é mudança de catálogo, que é instantânea, do que exige olhar todas as linhas, que deve rodar em passos com lock fraco ou em lotes fora do DDL.

-- 1. Coluna anulavel sem default: so catalogo, o ACCESS EXCLUSIVE dura milissegundos.
ALTER TABLE orders ADD COLUMN channel text;

-- 2. Preenchimento fora do DDL, em lotes pequenos, repetido ate nao restar linha nula.
UPDATE orders SET channel = 'web'
 WHERE id BETWEEN 1 AND 50000 AND channel IS NULL;

-- 3. NOT NULL sem varredura sob lock (Postgres 12+): a constraint validada dispensa o scan.
ALTER TABLE orders ADD CONSTRAINT orders_channel_nn CHECK (channel IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_nn;
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_channel_nn;

-- 4. Chave estrangeira em duas etapas: criar sem validar e validar sem bloquear escrita.
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

-- 5. Indice sem bloquear escrita. Se falhar, sobra um indice INVALID: remover e repetir.
CREATE INDEX CONCURRENTLY idx_orders_channel ON orders (channel);
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_channel;

No passo 3, o SET NOT NULL ainda pede ACCESS EXCLUSIVE, por isso deve passar pelo mesmo executor com lock_timeout. A diferença é que, com a constraint já validada, o Postgres 12 ou superior reconhece que nenhuma linha é nula e não varre a tabela. Para troca de tipo, como int para bigint, a abordagem é criar uma coluna nova, preencher em lotes, sincronizar com gatilho e fazer a troca em uma transação curta, o mesmo princípio de expandir, migrar e contrair.

Migração segura é um processo, não um comando. Quatro proteções custam pouco e pegam a maioria dos casos antes da produção.

1. Ensaio com carga e transação longa: no ambiente de homologação, abra uma transação de leitura em orders por um minuto e rode a migração durante o tráfego sintético. Se a API engasgar, o executor ainda não tem timeout ou o DDL pede mais lock do que o previsto.
2. Análise automática no CI: rejeite migrações que contenham ADD COLUMN com default volátil, SET NOT NULL direto, CREATE INDEX sem CONCURRENTLY, ADD FOREIGN KEY sem NOT VALID ou ALTER COLUMN TYPE. Uma verificação por texto já pega o erro comum.
3. Consulta de pré-voo: antes de aplicar, rode a lista de transações abertas há mais de 30 segundos. Se houver uma que toque a tabela, espere ou avise o dono, em vez de apostar na retentativa.
4. Janela com alerta: alerte quando houver sessão em wait_event_type igual a Lock por mais de 5 segundos. Esse alerta teria avisado o incidente aos 15 segundos, não aos 6 minutos.

Rodar de madrugada ajuda a reduzir o número de usuários afetados, mas não resolve a causa: o relatório noturno de BI e os jobs agendados costumam estar justamente ali. O que protege é o teto de espera, não o horário.