Anadir una clave foranea o un CHECK a una tabla vacia es cosa de un segundo. En una tabla con decenas de millones de filas, ese mismo ALTER TABLE recorre todas las filas bajo un bloqueo y puede congelar las escrituras durante minutos. PostgreSQL parte esa operacion en dos pasos: un NOT VALID instantaneo y un VALIDATE CONSTRAINT en segundo plano.
Por que un ALTER normal es peligroso
Cuando ejecutas ALTER TABLE orders ADD CONSTRAINT ..., PostgreSQL debe demostrar que TODAS las filas existentes cumplen la restriccion. Para ello toma un bloqueo ACCESS EXCLUSIVE (o casi equivalente) y lee la tabla entera.
- Cuanto mas grande es la tabla, mas dura el recorrido.
- Mientras dura, los
INSERT, UPDATE y DELETE sobre esa tabla se encolan tras el bloqueo.
- Para una clave foranea, la tabla padre tambien queda bloqueada.
En una tabla orders en produccion eso significa downtime real de escritura. La idea de NOT VALID es separar "empezar a aplicar la regla a las filas nuevas" de "verificar las filas viejas".
Paso 1: ADD CONSTRAINT ... NOT VALID
La opcion NOT VALID dice: registra la restriccion en el catalogo y aplicala a cada fila nueva o modificada, pero no recorras los datos existentes.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;
ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0)
NOT VALID;
La sentencia toma un bloqueo corto, solo de metadatos, y termina al instante. A partir de aqui, un pedido nuevo con un user_id inexistente o con amount <= 0 se rechaza. Las filas "sucias" antiguas permanecen de momento y la restriccion queda marcada como no validada.
Paso 2: VALIDATE CONSTRAINT
El segundo paso termina de recorrer las filas existentes, pero sin el bloqueo agresivo.
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;
VALIDATE CONSTRAINT toma solo un bloqueo SHARE UPDATE EXCLUSIVE: las lecturas y las escrituras concurrentes siguen fluyendo y solo se bloquean otras operaciones de esquema. Si una sola fila rompe la regla, el comando falla y la restriccion se queda en estado NOT VALID, asi que puedes limpiar los datos y reintentar.
Conviene localizar las filas problematicas antes de validar:
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;
Donde aplica y un detalle traicionero
El patron funciona para FOREIGN KEY y CHECK (incluido CHECK (col IS NOT NULL) como forma de anadir NOT NULL sin un recorrido largo). Para UNIQUE y PRIMARY KEY no existe la sintaxis NOT VALID; en su lugar usa CREATE INDEX CONCURRENTLY mas ADD CONSTRAINT ... USING INDEX.
- Trampa: mientras una restriccion esta en
NOT VALID, el planificador no se apoya en ella para optimizaciones (por ejemplo, constraint exclusion en particiones). Completa siempre el VALIDATE.
- Ejecuta los dos pasos en transacciones distintas. Si envuelves ambos en una sola transaccion, vuelves al recorrido largo bajo un bloqueo pesado.
- Consulta el estado con
\d+ orders, que muestra NOT VALID junto a las restricciones sin validar, o consulta pg_constraint.convalidated.
SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
Y en MySQL y ClickHouse
La historia cambia fuera de PostgreSQL. En MySQL/InnoDB, las claves foraneas se validan al crearse y no hay un NOT VALID aparte; CHECK llego en 8.0.16 y tambien se valida de inmediato. Para CHECK hay un atajo: crearlo como NOT ENFORCED y luego pasarlo a ENFORCED, pero eso no convierte el recorrido en tarea de fondo. El InnoDB moderno ejecuta muchos ALTER online (ALGORITHM=INPLACE, LOCK=NONE), aunque anadir una FK sigue exigiendo cuidado en tablas grandes.
En ClickHouse no hay claves foraneas; las restricciones CHECK se declaran en CREATE TABLE y se verifican en la insercion, no de forma retroactiva sobre toda la tabla. Asi que este truco de dos pasos es una especialidad de PostgreSQL, y por eso es tan valioso para migraciones sin downtime.
Anadir una clave foranea o un
CHECKa una tabla vacia es cosa de un segundo. En una tabla con decenas de millones de filas, ese mismoALTER TABLErecorre todas las filas bajo un bloqueo y puede congelar las escrituras durante minutos. PostgreSQL parte esa operacion en dos pasos: unNOT VALIDinstantaneo y unVALIDATE CONSTRAINTen segundo plano.Por que un ALTER normal es peligroso
Cuando ejecutas
ALTER TABLE orders ADD CONSTRAINT ..., PostgreSQL debe demostrar que TODAS las filas existentes cumplen la restriccion. Para ello toma un bloqueoACCESS EXCLUSIVE(o casi equivalente) y lee la tabla entera.INSERT,UPDATEyDELETEsobre esa tabla se encolan tras el bloqueo.En una tabla
ordersen produccion eso significa downtime real de escritura. La idea deNOT VALIDes separar "empezar a aplicar la regla a las filas nuevas" de "verificar las filas viejas".Paso 1: ADD CONSTRAINT ... NOT VALID
La opcion
NOT VALIDdice: registra la restriccion en el catalogo y aplicala a cada fila nueva o modificada, pero no recorras los datos 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;La sentencia toma un bloqueo corto, solo de metadatos, y termina al instante. A partir de aqui, un pedido nuevo con un
user_idinexistente o conamount <= 0se rechaza. Las filas "sucias" antiguas permanecen de momento y la restriccion queda marcada como no validada.Paso 2: VALIDATE CONSTRAINT
El segundo paso termina de recorrer las filas existentes, pero sin el bloqueo agresivo.
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user; ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;VALIDATE CONSTRAINTtoma solo un bloqueoSHARE UPDATE EXCLUSIVE: las lecturas y las escrituras concurrentes siguen fluyendo y solo se bloquean otras operaciones de esquema. Si una sola fila rompe la regla, el comando falla y la restriccion se queda en estadoNOT VALID, asi que puedes limpiar los datos y reintentar.Conviene localizar las filas 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;Donde aplica y un detalle traicionero
El patron funciona para
FOREIGN KEYyCHECK(incluidoCHECK (col IS NOT NULL)como forma de anadir NOT NULL sin un recorrido largo). ParaUNIQUEyPRIMARY KEYno existe la sintaxisNOT VALID; en su lugar usaCREATE INDEX CONCURRENTLYmasADD CONSTRAINT ... USING INDEX.NOT VALID, el planificador no se apoya en ella para optimizaciones (por ejemplo, constraint exclusion en particiones). Completa siempre elVALIDATE.\d+ orders, que muestraNOT VALIDjunto a las restricciones sin validar, o consultapg_constraint.convalidated.SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'orders'::regclass;Y en MySQL y ClickHouse
La historia cambia fuera de PostgreSQL. En MySQL/InnoDB, las claves foraneas se validan al crearse y no hay un
NOT VALIDaparte;CHECKllego en 8.0.16 y tambien se valida de inmediato. ParaCHECKhay un atajo: crearlo comoNOT ENFORCEDy luego pasarlo aENFORCED, pero eso no convierte el recorrido en tarea de fondo. El InnoDB moderno ejecuta muchosALTERonline (ALGORITHM=INPLACE, LOCK=NONE), aunque anadir una FK sigue exigiendo cuidado en tablas grandes.En ClickHouse no hay claves foraneas; las restricciones
CHECKse declaran enCREATE TABLEy se verifican en la insercion, no de forma retroactiva sobre toda la tabla. Asi que este truco de dos pasos es una especialidad de PostgreSQL, y por eso es tan valioso para migraciones sin downtime.