Entrevista de backend: ¿cómo migrarías una restricción de PostgreSQL con NOT VALID?

Pregunta

Una tabla de PostgreSQL en producción necesita una nueva restricción CHECK o de clave foránea. Las filas existentes pueden violarla y el negocio no puede tolerar un bloqueo prolongado de escritura. Diseñe el flujo desde la búsqueda de violaciones, la adición de una restricción NOT VALID, la reparación de filas históricas y la ejecución de VALIDATE CONSTRAINT. Cubra bloqueos, concurrencia, monitoreo y manejo de fallas.

Qué evalúa el entrevistador

• Si distingue el comportamiento de bloqueo al agregar una restricción del escaneo de validación posterior.
• Si sabe que NOT VALID omite el escaneo de filas antiguas mientras que las nuevas inserciones y actualizaciones se siguen verificando.
• Si coordina la reparación, la validación y el despliegue de la aplicación en lugar de emitir un único DDL bloqueante.
• Si el estado de la restricción se mantiene visible y recuperable a través de fallas de validación, transacciones largas y reversiones (rollbacks).

Respuesta modelo

Comience con consultas de solo lectura para estimar las filas infractoras, la disponibilidad de índices y las transacciones largas. Durante una ventana controlada, ejecute ADD CONSTRAINT ... NOT VALID. Esto no escanea las filas existentes, pero verifica de inmediato las inserciones y actualizaciones posteriores; las filas históricas aún pueden violar la regla, por lo que el estado queda explícitamente pendiente de validación.

Repare las filas históricas en lotes delimitados con límites de commit y registros de progreso. La lógica de reparación debe coincidir con la regla de la aplicación, por lo que debe desplegar código compatible primero cuando sea necesario. Luego ejecute VALIDATE CONSTRAINT mientras observa las esperas de bloqueo, la duración del escaneo y la carga de la base de datos. Una validación exitosa marca la restricción en el catálogo como válida y completa la migración.

Para una clave foránea, verifique una restricción de unicidad adecuada en las columnas referenciadas y evalúe las escrituras y eliminaciones concurrentes. Si la validación falla, mantenga la restricción NOT VALID para proteger las nuevas escrituras, repare las filas restantes y vuelva a intentarlo. Elimínela solo cuando el requisito se haya retirado genuinamente.

Flujo de migración

-- 1. Record violations and create repair work
SELECT count(*) FROM orders WHERE total < 0;

-- 2. Add the constraint without scanning historical rows
ALTER TABLE orders
  ADD CONSTRAINT orders_total_nonnegative
  CHECK (total >= 0) NOT VALID;

-- 3. Repair in batches, then validate
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_nonnegative;

La herramienta de migración debe almacenar el nombre de la restricción, el cursor del lote, las horas de inicio y fin, el resultado de la validación y el operador. Antes del despliegue, verifique cada ruta de escritura de la aplicación contra la misma regla para que el trabajo de reparación y la lógica de negocio no se sobrescriban entre sí.

El valor de NOT VALID radica en separar un escaneo histórico costoso del paso de adición de la restricción; la validación aún escanea la tabla y adquiere el bloqueo documentado, por lo que no es gratuita. Verifique las transacciones largas y el retraso de replicación, configure un tiempo de espera de sentencia (statement timeout) y alertas de espera de bloqueo, y ejecute la validación en una ventana controlada.

Las nuevas transacciones se verifican durante la validación, mientras que la reparación histórica debe evitar sobrescribir las actualizaciones del negocio. Utilice rangos de índice estables y transacciones cortas para los lotes. FOR UPDATE SKIP LOCKED puede reclamar trabajo cuando sea apropiado, pero las filas omitidas no deben hacer que las métricas de progreso parezcan completas.

Errores comunes

• Asumir que NOT VALID deshabilita la restricción y permite nuevas escrituras no conformes.
• Ejecutar VALIDATE CONSTRAINT sin verificar transacciones largas, esperas de bloqueo y capacidad de replicación.
• Reparar cada fila histórica en una sola transacción enorme, causando hinchazón (bloat), bloqueos prolongados y una reversión difícil.
• Corregir un CHECK ignorando el índice referenciado y la ruta de eliminación requerida por una clave foránea.
• Eliminar una restricción fallida y perder la protección para nuevas escrituras y la ruta de remediación posterior.

Manejo de fallas y reversión

Utilice estados explícitos como planned, not_valid, backfilling, validating, validated y aborted. Persista cada transición en una tabla de migración y un registro de auditoría. Cuando la validación encuentre violaciones, registre el nombre de la restricción y muestras de claves redactadas, pause la validación y mantenga la restricción; tras la reparación, reanude desde el progreso registrado.

Si un lanzamiento de la aplicación debe revertirse, el código de compatibilidad aún debe manejar tanto las filas antiguas como la nueva restricción. Eliminar una restricción NOT VALID es el último recurso y requiere confirmar que las nuevas escrituras no puedan recrear el problema. Cualquier DROP CONSTRAINT debe contar con aprobación, respaldo y un plan para volver a agregarla.

Observabilidad

Monitoree el conteo de violaciones, la tasa de reparación, el tiempo restante estimado, el progreso de la validación, las esperas de bloqueo, la antigüedad de la transacción más antigua, el crecimiento de WAL y el retraso de replicación. Distinga entre "una nueva escritura violó la restricción" y "las filas históricas no están validadas"; requieren diferentes prioridades de respuesta.

Después de la validación, consulte pg_constraint.convalidated y el nombre de la restricción para confirmar el estado del catálogo. Almacene el resultado, el plan de consulta y la ventana de carga en el registro de migración. Los informes externos deben usar claves redactadas y agregados, nunca datos de negocio en los registros.

• PostgreSQL 17 ALTER TABLE: semántica de bloqueos y concurrencia para NOT VALID y VALIDATE CONSTRAINT.
• Documentación actual de restricciones de PostgreSQL: reglas de validación para CHECK, claves foráneas y filas históricas.
• PostgreSQL 17 pg_constraint: campos de catálogo como convalidated.

Preguntas de seguimiento

¿Por qué se verifican las nuevas escrituras mientras que las filas antiguas pueden permanecer no válidas?

NOT VALID omite el escaneo de las filas que ya existen cuando se agrega la restricción. La definición aún se aplica de inmediato a las operaciones posteriores de INSERT y UPDATE, evitando que el trabajo pendiente histórico crezca.

¿Pueden continuar las escrituras durante la validación?

Pueden, pero la validación lee la tabla y adquiere el bloqueo documentado, por lo que el tiempo de espera y la carga deben controlarse. Las nuevas filas se verifican y los lotes de reparación deben evitar sobrescribir las actualizaciones de la aplicación.

¿Cómo se estima la duración de la validación?

Utilice el tamaño de la tabla, el plan de escaneo, el comportamiento de la caché, la carga de trabajo concurrente y la ventana de mantenimiento para obtener una estimación medida o una muestra. No asuma que el conteo de filas por sí solo es lineal; configure políticas de tiempo de espera y cancelación antes de producción.

¿Qué tiene de especial la migración de una clave foránea?

Verifique la unicidad y la indexación en las columnas referenciadas y defina una semántica de eliminación estable. Durante la validación, observe las filas secundarias (child) existentes junto con las eliminaciones, actualizaciones y conflictos de bloqueo concurrentes.

¿Cuándo se debe abandonar y eliminar la restricción?

Solo cuando el requisito se retira o se diseñó incorrectamente y existe una protección alternativa. La falla de validación por sí sola no es una razón para eliminarla; mantener NOT VALID continúa protegiendo los nuevos datos y preserva la remediación.