Entrevista de ingeniería de datos: ¿Cómo evitan las restricciones temporales de PostgreSQL la superposición de validez?

Planteamiento y alcance

Los registros de precios, arrendamientos, cronogramas y permisos combinan una clave de negocio con un período de validez. El entrevistador desea que la base de datos rechace períodos superpuestos para un mismo producto o inquilino, incluidas las escrituras concurrentes. La pregunta evalúa el modelado temporal, la semántica de restricciones y un plan de migración seguro en lugar de una consulta que simplemente busque conflictos.

Qué evalúa el entrevistador

• Si distingue una clave primaria o única temporal con WITHOUT OVERLAPS de una clave única B-tree normal.
• Si define extremos de rango, rangos vacíos, comportamiento de NULL, fechas discretas y marcas de tiempo continuas.
• Si comprende que una clave foránea con PERIOD verifica la cobertura temporal, no solo una clave de negocio coincidente.
• Si puede planificar la limpieza histórica, el impacto en bloqueos, la reversión y la validación concurrente.
• Si las restricciones de base de datos, los mensajes de aplicación y las métricas de auditoría tienen responsabilidades separadas.

Semántica y límites

PostgreSQL define restricciones temporales sobre columnas de tipo rango. WITHOUT OVERLAPS se puede utilizar en restricciones de clave primaria y de unicidad; para partes de clave ordinarias iguales, los rangos asociados no deben superponerse. La columna de rango es implícitamente no nula, y los rangos vacíos o multirangos no forman claves temporales válidas. Este invariante de base de datos no se puede reemplazar por una secuencia de aplicación de "verificar y luego insertar".

PERIOD se utiliza para claves foráneas temporales. Una clave de negocio y período secundarios deben estar cubiertos por una o más filas principales; demostrar que existe una fila principal con la misma clave de negocio es insuficiente. La eliminación de un registro principal o el acortamiento de un período cubierto debe seguir la acción de clave foránea y el orden de transacciones.

Pasos de modelado

Elija intervalos semiabiertos o cerrados y use la misma convención en cada ruta de escritura. La validez por fechas suele usar [start, end); la validez por marcas de tiempo debe especificar zona horaria y precisión. Coloque las columnas de clave de negocio ordinarias antes de la columna de rango, eligiendo daterange, tsrange o tstzrange según corresponda.

Cree la restricción única temporal en los registros principales y una clave foránea con PERIOD en los registros cubiertos. La aplicación puede proporcionar un mensaje descriptivo, pero la base de datos decide si el commit tiene éxito. Antes de migrar datos históricos, utilice un informe o una verificación de estilo de exclusión para identificar superposiciones, y luego defina si cada conflicto se fusiona, se divide o se retira.

Ejemplo de SQL

Este ejemplo evita precios superpuestos para un plan_id y exige que las reglas estén totalmente cubiertas por un plan de precios:

CREATE TABLE price_plan (
  plan_id bigint,
  valid_during daterange NOT NULL,
  amount numeric(12, 2) NOT NULL,
  PRIMARY KEY (plan_id, valid_during WITHOUT OVERLAPS)
);

CREATE TABLE plan_rule (
  plan_id bigint,
  valid_during daterange NOT NULL,
  rule_code text NOT NULL,
  CONSTRAINT plan_rule_plan_period_fk
    FOREIGN KEY (plan_id, PERIOD valid_during)
    REFERENCES price_plan (plan_id, PERIOD valid_during)
);

Valide la sintaxis y el comportamiento en una tabla espejo (shadow table) antes de producción, y confirme que el controlador cliente exponga el conflicto de base de datos de manera estable. El ejemplo muestra el invariante principal; la moneda, la precisión de los montos y las columnas de auditoría siguen dependiendo de los requisitos del producto.

Escrituras concurrentes y migración

Antes de agregar la restricción, cuente los rangos en conflicto y ordénelos por clave de negocio; no elimine una fila histórica "aparentemente duplicada" sin una decisión de negocio. Para una tabla grande, estime el tiempo de construcción de índices, las esperas de bloqueo y el retraso de replicación. Utilice limpieza por lotes, una ventana de bajo tráfico y marcadores de progreso observables.

Dos inserciones concurrentes para períodos superpuestos deben coordinarse mediante la base de datos en el momento del commit. La aplicación debe transformar un conflicto de unicidad en un error de negocio reintentable o explicable; una verificación previa exitosa no garantiza una inserción posterior. Mantenga las columnas antiguas y la ruta de escritura para reversión hasta que se superen la validación en tablas espejo, la conciliación de escritura dual y los simulacros de recuperación.

Errores comunes

• Crear únicamente un índice único (plan_id, start_at), lo cual aún permite períodos superpuestos.
• No definir reglas de extremos y mezclar una fecha que termina en 2026-02-01 con el inicio del siguiente período.
• Tratar una clave foránea con PERIOD como una clave foránea ordinaria que solo verifica la clave de negocio.
• Agregar una restricción directamente a una tabla de producción grande sin verificar el historial, los bloqueos o el retraso de replicación.
• Permitir que los reintentos de la aplicación oculten conflictos de restricciones y generen precios duplicados o cobertura parcial.

Preguntas de seguimiento

¿Cómo maneja las superposiciones existentes?

Genere un informe de conflictos agrupado por clave de negocio y ordenado por rango. Haga que el responsable del negocio elija la semántica de fusión, división o retiro; repare las filas, reproduzca las escrituras en una tabla espejo, demuestre que el informe está vacío y solo entonces agregue la restricción.

¿Por qué no usar una restricción de exclusión o un trigger?

Una restricción de exclusión puede expresar la exclusión mutua de intervalos, pero WITHOUT OVERLAPS expresa directamente la semántica de clave primaria o única temporal y se integra con claves foráneas PERIOD. Los triggers pueden pasar por alto la concurrencia, la recursión o las rutas de replicación; utilícelos solo para efectos secundarios adicionales entre tablas mientras mantiene el invariante central en restricciones.

¿Cómo demuestra que la migración preservó el tiempo de negocio?

Compare recuentos de intervalos, muestras de límites, tasas de conflictos rechazados y planes de consulta antes y después. Ejecute comprobaciones de cobertura en una réplica de lectura y en un entorno de recuperación. Durante el despliegue, concilie con la lógica anterior; si se rechazan escrituras válidas, revierta el cambio de restricción sin eliminar el historial.