Prompt y contexto
Una tabla de órdenes permite una referencia externa opcional por tenant: como máximo una fila por tenant puede tener NULL. ¿Cómo aplicarías esto en PostgreSQL 18 manejando duplicados históricos, escrituras concurrentes y rollback?
El índice único por defecto de PostgreSQL trata los valores NULL como distintos, por lo que múltiples NULL no entran en conflicto. PostgreSQL 18 agrega NULLS NOT DISTINCT, haciendo que los valores NULL participen en la unicidad. La pregunta evalúa el diseño de restricciones, la seguridad de la migración y la semántica de concurrencia en lugar de trasladar una verificación de negocio propensa a condiciones de carrera al código de la aplicación.
Lo que el entrevistador está evaluando
- Si explicas con precisión la unicidad por defecto de NULL frente a
NULLS NOT DISTINCT. - Si sabes que la opción se aplica a índices B-tree únicos o restricciones de unicidad, no a comparaciones ordinarias.
- Si encuentras y resuelves primero las combinaciones históricas duplicadas de NULL y no-NULL.
- Si diseñas una migración en línea, un plan de bloqueos, el comportamiento ante escrituras concurrentes y el rollback.
- Si el ORM, la replicación, el particionamiento y los contratos downstream siguen siendo compatibles.
Preguntas para aclarar primero
- ¿El alcance es toda la tabla, o una regla por tenant, región o estado activo?
- ¿NULL significa no asignado, desconocido o una referencia compartida intencional?
- ¿Contienen las filas históricas múltiples NULL, cadenas vacías, variantes de mayúsculas/minúsculas o claves con borrado lógico (soft-deleted)?
- ¿Cuáles son la tasa de escritura, el presupuesto de bloqueo y la ventana de migración?
- ¿Asumen la aplicación, el ORM, CDC y los reportes que NULL puede repetirse?
Respuesta de treinta segundos
“Aclararía el significado de NULL y el alcance, luego auditaría los duplicados históricos. Para una regla con alcance de tenant, colocaría el tenant y la referencia en una clave única compuesta con NULLS NOT DISTINCT; la opción hace que NULL participe en la unicidad de esa clave, pero no cambia la comparación trivaluada de SQL ni hace que la columna sea NOT NULL. Limpiaría o decidiría los conflictos históricos, desplegaría el manejo de conflictos antes del índice, lo construiría con un plan de bloqueos y latencia, y probaría el comportamiento del ORM y CDC. La base de datos se convierte en la autoridad final concurrente; si la semántica de negocio es incorrecta, revierto la restricción y la política de la aplicación en lugar de confiar en una verificación previa propensa a condiciones de carrera.”
Análisis detallado paso a paso
Paso 1: Confirmar el significado de NULL y el alcance
Distingue un valor no asignado de un valor desconocido. Si NULL significa no asignado, un solo NULL puede ser la regla prevista; si significa desconocido y las repeticiones son válidas, la unicidad es incorrecta. Decide si la restricción es global o agrupada por tenant, región y estado activo, luego elige el orden de la clave compuesta y si se necesita un índice parcial.
Paso 2: Elegir la expresión en la base de datos
PostgreSQL 18 soporta NULLS NOT DISTINCT en índices únicos. El valor por defecto trata los NULL como no iguales y permite múltiples NULL. Una restricción de unicidad puede hacer que el modelo sea más claro, mientras que un índice B-tree único puede adaptarse a una migración en línea. La opción cambia únicamente la comparación de unicidad: WHERE value = NULL sigue la lógica trivaluada, y la columna puede permanecer nullable.
Paso 3: Auditar y limpiar datos históricos
Agrupa por la clave propuesta y cuenta múltiples NULL, cadenas vacías, variantes de mayúsculas/minúsculas y filas con borrado lógico que aún ocupan una clave. Decide por cada conflicto si fusionar órdenes, completar la referencia, retener una fila y migrar las otras, o documentar una excepción. Haz que la limpieza sea reproducible y auditable, y valídala en un entorno sombra antes de que la creación del índice exponga conflictos no resueltos.
Paso 4: Diseñar la migración en línea
Lanza primero un manejo compatible en la aplicación para los conflictos de unicidad, luego crea el índice o la restricción durante una ventana controlada. Para una tabla grande, evalúa la creación concurrente, el nivel de bloqueo, el espacio en disco y la latencia de escritura; monitorea los conflictos y las transacciones largas en todo momento. Si los tenants deben escalonarse, crea la regla por lotes y registra una marca de agua de finalización. Mantén la verificación previa antigua hasta que la restricción de la base de datos y el mapeo de errores estén listos.
Paso 5: Manejar la concurrencia y los contratos downstream
El índice único es el árbitro final para inserciones y actualizaciones concurrentes. Un “verificar y luego insertar” en la aplicación puede mejorar el mensaje, pero no puede reemplazar la restricción. Mapea una violación de unicidad a un error de negocio reintentable o visible para el usuario sin reintentos infinitos. Revisa CDC, la replicación, el esquema del ORM, los reportes y las cachés para detectar suposiciones de que NULL puede repetirse, y luego actualiza los contratos y las alertas.
Paso 6: Verificar, monitorear y revertir (rollback)
En staging y con un tenant canary, prueba un NULL, un segundo NULL, valores no-NULL iguales, valores no-NULL diferentes, actualizaciones, eliminar y recrear, y escrituras concurrentes. Monitorea la construcción del índice, las esperas de bloqueo, la tasa de conflictos, los errores de la aplicación y la latencia downstream. Si la semántica o la tasa de conflictos son inaceptables, detén la nueva ruta de escritura, elimina la restricción, restaura el manejo de errores compatible y preserva el registro de auditoría para su análisis.
Respuesta de muestra de alta calidad
Primero confirmaría el significado de NULL y el alcance. Si cada tenant puede tener una referencia opcional, incluiría el tenant y la referencia en una clave única compuesta y usaría NULLS NOT DISTINCT en PostgreSQL 18. Esto hace que NULL participe en la unicidad para esa clave, mientras que la lógica trivaluada ordinaria de SQL y la columna nullable permanecen sin cambios.
Antes del despliegue, auditaría múltiples NULL, cadenas vacías, variantes de mayúsculas/minúsculas y filas con borrado lógico, decidiría cómo se fusiona o completa cada conflicto y registraría la decisión. Desplegaría el manejo de conflictos de unicidad antes de construir el índice, luego monitorearía bloqueos, espacio, transacciones largas y conflictos. Las pruebas cubren uno y dos NULL, no-NULL iguales y diferentes, actualizaciones, eliminar y recrear, y concurrencia. La base de datos es la autoridad final, y el ORM, CDC, los reportes y las cachés deben adoptar el mismo contrato; si el significado de negocio resulta ser incorrecto, elimino la restricción y revierto la política de la aplicación.
Errores comunes
- Asumir que UNIQUE permite solo un NULL por defecto → PostgreSQL trata los NULL como distintos → usa
NULLS NOT DISTINCTexplícitamente. - Tratarlo como NOT NULL → La opción aún permite un NULL → separa el significado de valor faltante de la unicidad.
- Usar solo una verificación previa en la aplicación → Las solicitudes concurrentes aún compiten en condiciones de carrera → deja que el índice único de la base de datos decida.
- Ignorar cadenas vacías y variantes de mayúsculas/minúsculas → Los duplicados de negocio pueden no ser conflictos de NULL → define la normalización y limpieza primero.
- Construir en línea sin limpieza histórica → Los duplicados existentes pueden fallar o bloquear la migración → audita, decide y monitorea transacciones largas.
- Cambiar solo la base de datos → El ORM, CDC y los reportes aún pueden asumir NULL repetibles → actualiza el contrato de datos y el mapeo de errores.
Preguntas de seguimiento
¿Cambia NULLS NOT DISTINCT las comparaciones ordinarias con NULL?
No. Cambia si los NULL colisionan en un índice único. WHERE value = NULL todavía sigue la lógica trivaluada de SQL y debe usar IS NULL. La semántica de consultas, la semántica de índices y la nulabilidad de columnas deben explicarse por separado.
¿Cómo puedes migrar sin tiempo de inactividad cuando ya existen dos NULL?
Elige la fila retenida por tenant y estado de negocio, fusiona o completa las otras, y registra la decisión en una tabla de auditoría. Despliega el manejo de conflictos, crea el índice en lotes mientras monitoreas bloqueos y transacciones largas, y pospón los tenants cuyos conflictos no puedan resolverse dentro de la ventana.
¿Debería una regla multi-tenant usar una clave compuesta o un índice parcial?
Si cada estado debe ser único, usa tenant más referencia en una clave única compuesta. Si solo las filas activas están restringidas, un índice parcial limitado al estado activo puede ser adecuado. La elección depende de los contratos de eliminación, restauración y transición de estados, y debe demostrarse con pruebas concurrentes.