ADD COLUMN sem DEFAULT só registra a coluna no catálogo e termina em milissegundos, mas precisa de um bloqueio curto. Se houver uma transação longa aberta na tabela, o ALTER fica na fila e todas as consultas que chegam depois esperam atrás dele: é assim que um comando instantâneo derruba a API. O lock_timeout de 2 segundos troca esse risco por uma retentativa. Os gatilhos garantem que tudo que for inserido a partir de agora já nasce com a cópia, e só depois deles o backfill começa.
-- 1. Colunas novas sem DEFAULT: so altera o catalogo, nao reescreve a tabela.
-- O lock_timeout impede que o ALTER fique na fila atras de uma transacao longa
-- travando todas as consultas que chegarem depois dele. Se estourar, tente de novo.
SET lock_timeout = '2s';
ALTER TABLE eventos_rastreio ADD COLUMN id_novo bigint;
ALTER TABLE eventos_anexos ADD COLUMN evento_id_novo bigint;
-- 2. Gatilhos: toda linha inserida daqui em diante ja nasce com a copia.
-- O DEFAULT nextval() e aplicado antes do BEFORE INSERT, entao NEW.id existe.
-- UPDATE OF id dispara so quando o proprio id muda: o backfill, que altera
-- apenas id_novo, nao paga o custo do gatilho.
CREATE FUNCTION eventos_rastreio_copia_id() RETURNS trigger
LANGUAGE plpgsql AS $
BEGIN
NEW.id_novo := NEW.id;
RETURN NEW;
END;
$;
CREATE TRIGGER eventos_rastreio_copia_id
BEFORE INSERT OR UPDATE OF id ON eventos_rastreio
FOR EACH ROW EXECUTE FUNCTION eventos_rastreio_copia_id();
CREATE FUNCTION eventos_anexos_copia_evento_id() RETURNS trigger
LANGUAGE plpgsql AS $
BEGIN
NEW.evento_id_novo := NEW.evento_id;
RETURN NEW;
END;
$;
CREATE TRIGGER eventos_anexos_copia_evento_id
BEFORE INSERT OR UPDATE OF evento_id ON eventos_anexos
FOR EACH ROW EXECUTE FUNCTION eventos_anexos_copia_evento_id();
-- 3. O backfill vai criar uma versao nova de cada linha. Autovacuum agressivo
-- nesta tabela durante a migracao, para o espaco morto ser reaproveitado
-- pelos lotes seguintes em vez de dobrar o tamanho do arquivo.
ALTER TABLE eventos_rastreio SET (
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 5000000,
autovacuum_vacuum_cost_limit = 2000
);
// backfill.js
// Copia a coluna int para a bigint em lotes por faixa de chave, com pausa entre
// lotes e freio por atraso de replica. Pode ser interrompido e retomado: linhas
// ja copiadas nao sao tocadas de novo (destino IS NULL) e o progresso vai para o log.
// Rode DEPOIS de criar os gatilhos: o max lido aqui cobre tudo que existia antes
// deles, e o que entrar depois ja chega copiado.
// Uso: node backfill.js eventos_rastreio [chave_inicial]
import pg from 'pg';
// Lista fechada: os nomes entram no SQL, entao nunca vem de entrada externa.
const ALVOS = {
eventos_rastreio: { chave: 'id', origem: 'id', destino: 'id_novo' },
eventos_anexos: { chave: 'id', origem: 'evento_id', destino: 'evento_id_novo' },
};
const LOTE = 20_000;
const PAUSA_MS = 150;
const ATRASO_MAX_BYTES = 256 * 1024 * 1024;
const [tabela, retomarDe = '0'] = process.argv.slice(2);
const alvo = ALVOS[tabela];
if (!alvo) throw new Error(`tabela fora da lista: ${tabela}`);
const client = new pg.Client({
connectionString: process.env.DATABASE_URL,
application_name: 'backfill-bigint',
});
const esperar = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
async function atrasoReplica() {
const { rows } = await client.query(`
SELECT COALESCE(MAX(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0)::text AS atraso
FROM pg_stat_replication`);
return Number(rows[0].atraso);
}
async function main() {
await client.connect();
// Lote que espera linha travada pela aplicacao desiste em vez de empilhar.
await client.query("SET statement_timeout = '60s'");
const { rows } = await client.query(`SELECT max(${alvo.chave})::text AS max FROM ${tabela}`);
const max = Number(rows[0].max ?? 0);
let inicio = Number(retomarDe);
let lotes = 0;
let copiadas = 0;
const t0 = Date.now();
while (inicio <= max) {
const fim = inicio + LOTE - 1;
const { rowCount } = await client.query(
`UPDATE ${tabela} SET ${alvo.destino} = ${alvo.origem}
WHERE ${alvo.chave} BETWEEN $1 AND $2 AND ${alvo.destino} IS NULL`,
[inicio, fim],
);
copiadas += rowCount;
lotes += 1;
if (lotes % 100 === 0) {
const porSegundo = Math.round(copiadas / ((Date.now() - t0) / 1000));
console.log(JSON.stringify({ tabela, ate: fim, copiadas, porSegundo,
pct: ((fim / max) * 100).toFixed(2) }));
}
// Replica atrasada vira leitura velha para quem le dela: o backfill espera.
while ((await atrasoReplica()) > ATRASO_MAX_BYTES) {
console.log(JSON.stringify({ tabela, ate: fim, freio: 'atraso de replica' }));
await esperar(5000);
}
inicio = fim + 1;
await esperar(PAUSA_MS);
}
console.log(JSON.stringify({ tabela, fim: true, copiadas }));
await client.end();
}
main().catch(async (erro) => {
console.error(erro);
await client.end().catch(() => {});
process.exit(1);
});
Três detalhes fazem o backfill não virar o incidente. Primeiro, lotes por faixa de chave primária, não por LIMIT com OFFSET, para cada UPDATE usar o índice e tocar só páginas vizinhas. Segundo, o índice da coluna nova só é criado depois do backfill: sem índice em id_novo e com espaço livre na página, o Postgres faz HOT update e não precisa gravar entrada nova em nenhum índice. Como as exclusões de retenção deixavam espaço livre em boa parte das páginas, cerca de um terço das atualizações saiu como HOT, e o autovacuum agressivo devolveu espaço para os lotes seguintes. Terceiro, o freio por atraso de réplica: cada lote gera WAL, e uma réplica de leitura 30 segundos atrasada mostra para o usuário um pacote que ainda não saiu. Com lotes de 20 mil linhas e pausa de 150 ms, o pai levou 41 horas e o freio parou o processo 37 vezes, quase sempre durante o pico da tarde.