NOT VALID + VALIDATE: adicionar restricoes sem locks longos

Adicionar uma chave estrangeira ou um CHECK a uma tabela vazia leva um segundo. Numa tabela com dezenas de milhoes de linhas, esse mesmo ALTER TABLE varre todas as linhas sob um lock e pode congelar as escritas por minutos. O PostgreSQL divide essa operacao em dois passos: um NOT VALID instantaneo e um VALIDATE CONSTRAINT em segundo plano.

Por que um ALTER comum e perigoso

Quando voce roda ALTER TABLE orders ADD CONSTRAINT ..., o PostgreSQL precisa provar que TODAS as linhas existentes satisfazem a restricao. Para isso ele pega um lock ACCESS EXCLUSIVE (ou quase equivalente) e le a tabela inteira.

• Quanto maior a tabela, mais demora a varredura.
• Enquanto ela roda, os INSERT, UPDATE e DELETE nessa tabela ficam na fila atras do lock.
• Para uma chave estrangeira, a tabela pai tambem fica travada.

Numa tabela orders em producao isso significa downtime real de escrita. A ideia do NOT VALID e separar "comecar a aplicar a regra nas linhas novas" de "verificar as linhas antigas".

Passo 1: ADD CONSTRAINT ... NOT VALID

A opcao NOT VALID diz: registre a restricao no catalogo e aplique-a a toda linha nova ou modificada, mas nao varra os dados existentes.

-- Foreign key: instant, no full-table scan
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- CHECK works the same way
ALTER TABLE orders
  ADD CONSTRAINT chk_amount_positive
  CHECK (amount > 0)
  NOT VALID;

O comando pega um lock curto, somente de metadados, e retorna na hora. A partir daqui, um pedido novo com um user_id inexistente ou com amount <= 0 e recusado. As linhas "sujas" antigas permanecem por ora, e a restricao fica marcada como nao validada.

Passo 2: VALIDATE CONSTRAINT

O segundo passo termina de varrer as linhas existentes, mas sem o lock agressivo.

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;

O VALIDATE CONSTRAINT pega apenas um lock SHARE UPDATE EXCLUSIVE: leituras e escritas concorrentes continuam fluindo, e somente outras operacoes de esquema ficam bloqueadas. Se uma unica linha quebrar a regra, o comando falha e a restricao continua no estado NOT VALID, entao voce pode limpar os dados e tentar de novo.

Vale localizar as linhas problematicas antes de validar:

-- Orphan orders that would fail the FK
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Onde se aplica e uma pegadinha

O padrao funciona para FOREIGN KEY e CHECK (inclusive CHECK (col IS NOT NULL) como forma de adicionar NOT NULL sem uma varredura longa). Para UNIQUE e PRIMARY KEY nao existe a sintaxe NOT VALID; use CREATE INDEX CONCURRENTLY mais ADD CONSTRAINT ... USING INDEX.

• Pegadinha: enquanto uma restricao esta em NOT VALID, o planejador nao se apoia nela para otimizacoes (por exemplo, constraint exclusion em particoes). Sempre conclua com o VALIDATE.
• Rode os dois passos em transacoes separadas. Se envolver ambos numa unica transacao, voce volta a varredura longa sob um lock pesado.
• Verifique o estado com \d+ orders, que mostra NOT VALID ao lado das restricoes nao validadas, ou consulte pg_constraint.convalidated.

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

E no MySQL e no ClickHouse

A historia muda fora do PostgreSQL. No MySQL/InnoDB, as chaves estrangeiras sao validadas na criacao e nao ha um NOT VALID separado; o CHECK chegou na 8.0.16 e tambem e validado de imediato. Para CHECK existe um contorno: cria-lo como NOT ENFORCED e depois mudar para ENFORCED, mas isso nao torna a varredura uma tarefa de fundo. O InnoDB moderno roda muitos ALTER de forma online (ALGORITHM=INPLACE, LOCK=NONE), porem adicionar uma FK ainda pede cuidado em tabelas grandes.

No ClickHouse nao ha chaves estrangeiras; as restricoes CHECK sao declaradas no CREATE TABLE e verificadas na insercao, nao retroativamente em toda a tabela. Entao esse truque de dois passos e uma especialidade do PostgreSQL, e e justamente por isso que ele e tao valioso para migracoes sem downtime.