Planteamiento y contexto
Un equipo almacena reservas de salas de reuniones. Cada registro tiene una sala, una hora de inicio y una hora de fin; los periodos para una misma sala no deben superponerse, mientras que las reservas adyacentes pueden coincidir en un punto final. La aplicación ya comprueba los conflictos, pero la ocupación duplicada sigue apareciendo bajo concurrencia. Proporciona un diseño de PostgreSQL y explica intervalos semiabiertos, nulos, zonas horarias, escrituras concurrentes, manejo de errores y migración de datos existentes.
Esta pregunta encaja en roles de ingeniería de datos, backend y bases de datos. La clave está en expresar una regla de negocio que abarca múltiples filas como una invariante de la base de datos en lugar de confiar en que cada cliente ejecute la misma consulta. Una respuesta sólida distingue los límites de UNIQUE, CHECK, triggers y restricciones de exclusión, y luego conecta los fallos de restricción con el flujo de trabajo del producto.
Qué evalúa el entrevistador
Una respuesta sólida modela el tiempo de reserva como tstzrange u otro tipo de rango adecuado y utiliza explícitamente [start, end) para que los periodos adyacentes no entren en conflicto. Combina la igualdad de sala y la superposición de tiempo en una restricción de exclusión GiST. Explica cuándo se necesita btree_gist, por qué una comprobación previa en la aplicación no puede eliminar una condición de carrera, cómo mapear una excepción de restricción, cómo manejar límites infinitos y rangos vacíos, y cómo encontrar conflictos existentes antes de la migración.
Preguntas para aclarar primero
- ¿Cuál es la política de zona horaria para el inicio y el fin, y puede una reserva cruzar una transición de horario de verano?
- ¿El fin debe ser posterior al inicio, y las reservas de duración cero tienen significado para el negocio?
- ¿El alcance del conflicto es solo una sala, o también un piso, dispositivo o inquilino (tenant)?
- ¿Pueden tocarse los intervalos adyacentes, y las filas canceladas o eliminadas de forma lógica (soft-deleted) siguen consumiendo el recurso?
- ¿Los datos existentes ya contienen superposiciones, y pueden pausarse brevemente las escrituras durante la migración?
Estructura de respuesta de 30 segundos
“Normalizaría el inicio y el fin en un rango semiabierto consciente de la zona horaria, tstzrange(start_at, end_at, '[)'), y agregaría EXCLUDE USING gist (room_id WITH =, during WITH &&) en la base de datos. Las ventanas superpuestas para una sala se rechazan mientras que las ventanas adyacentes coexisten; btree_gist permite que una clave de sala de tipo entero o UUID participe en la comparación GiST. Intentaría la escritura directamente y mapearía un conflicto de restricción a una respuesta de negocio reintentable en lugar de confiar en verificar y luego insertar. Antes del despliegue, escanearía y repararía conflictos antiguos, habilitaría la restricción gradualmente y monitorearía los fallos.”
Solución paso a paso
Paso 1: elegir la semántica de tiempo
Usa tstzrange para un instante absoluto en lugar de entregar cadenas de hora local a la base de datos. [start, end) incluye el inicio y excluye el final, por lo que [10:00, 11:00) y [11:00, 12:00) no se superponen. PostgreSQL documenta && como el operador de superposición y utiliza restricciones de rango para este tipo de invariante.
Valida start_at < end_at en la escritura y decide si los rangos vacíos tienen sentido. Almacena una representación consistente de la zona horaria y luego dale formato para la zona horaria del espectador; no infieras la duración a partir de la aritmética del reloj local en un día de transición de horario de verano.
Paso 2: expresar la regla como una restricción de exclusión
El rango puede ser una columna generada o construirse en la expresión de restricción. Una columna de rango explícita es conveniente para consultas y auditorías:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_reservations (
reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
during tstzrange NOT NULL,
CHECK (NOT isempty(during)),
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);La restricción requiere que al menos una comparación entre cada par de filas sea falsa o nula. Cuando tanto room_id = como during && son verdaderos, la segunda fila se rechaza. PostgreSQL crea automáticamente un índice del tipo seleccionado para una restricción de exclusión.
Paso 3: entender btree_gist y el costo del índice
Los rangos tienen clases de operadores GiST. Un escalar como un entero, un valor de texto o un UUID generalmente carece de una clase GiST predeterminada para la igualdad, por lo que btree_gist puede proporcionar una clase de operador tipo B-tree que participa en la misma restricción GiST. Trata la extensión como una dependencia del despliegue y verifícala en el entorno de migración.
El índice de la restricción GiST agrega costo de escritura y actualización. Las lecturas deben usar operadores de rango y predicados selectivos. No crees un índice GiST de rango duplicado solo porque un índice sea visible; inspecciona los planes y confirma si el índice de la restricción ya atiende la carga de trabajo de lectura.
Paso 4: manejar concurrencia y transacciones
No ejecutes SELECT para buscar un conflicto y luego INSERT; dos transacciones pueden observar una ventana vacía al mismo tiempo. Deja que la restricción de la base de datos arbitre, captura la restricción con nombre y devuelve “la ventana de tiempo ya está ocupada”, permitiendo al usuario refrescar o elegir otro horario.
Una reserva también puede desencadenar pagos, notificaciones o cuotas. Haz commit de la reserva en una transacción corta, luego publica un outbox o un evento confiable para efectos secundarios externos. Reintenta solo errores de serialización o transitorios que sean seguros de reintentar; un conflicto de restricción es un hecho de negocio, por lo que los reintentos a ciegas no tendrán éxito.
Paso 5: definir cancelación, tenencia múltiple y eliminación
El hecho de que una fila eliminada lógicamente siga ocupando o no una sala debe formar parte del modelo de restricción. Si la cancelación libera la ventana, separa las reservas activas del historial o diseña una transición de estado que se pueda hacer cumplir. Un filtro WHERE status = 'active' en las consultas de la aplicación no hace que una restricción de exclusión normal ignore otras filas.
Para la tenencia múltiple (multi-tenancy), incluye la clave del inquilino cuando el espacio de nombres del recurso sea local para el inquilino, por ejemplo (tenant_id WITH =, room_id WITH =, during WITH &&), y haz cumplir la autorización para que un inquilino no pueda escribir en la sala de otro. La restricción protege contra conflictos; no reemplaza los permisos a nivel de fila ni la máquina de estados del negocio.
Paso 6: migrar datos existentes
Encuentra pares superpuestos por sala con un self-join o una consulta de ventana, y luego registra recuentos y propietarios. Resuelve cada conflicto fusionando, dividiendo, cancelando u obteniendo una decisión de negocio; no trunques datos silenciosamente. Después de que los datos estén limpios, crea la restricción en una ventana de bajo riesgo. Para una tabla grande, evalúa bloqueos, tiempo de construcción de índices, reversión (rollback) y un ensayo de respaldo y restauración.
Paso 7: diseñar errores y observabilidad
Nombra la restricción, por ejemplo room_reservations_no_overlap, para que el nombre de la restricción en el controlador mapee a una respuesta estable orientada al usuario. Registra la sala, el identificador de la solicitud y un resumen del periodo de tiempo sin datos personales innecesarios. Monitorea la tasa de conflictos, los remanentes de migración, la latencia de transacciones y el crecimiento del índice, distinguiendo la contención normal de una tormenta de reintentos.
Paso 8: probar escenarios concurrentes
Prueba una superposición en una sala (fallo), ventanas adyacentes en una sala (éxito), superposición en diferentes salas (éxito), instantes equivalentes en distintas zonas horarias, una actualización que cree un conflicto, cancelación, rangos vacíos y manejo de nulos. Usa dos transacciones concurrentes, no solo scripts secuenciales, y verifica la recuperación, la restauración de respaldos y el comportamiento de reconstrucción de restricciones.
Compensaciones y límites
Una restricción de exclusión se adapta a una regla mantenida continuamente de que ningún par de filas puede satisfacer un conjunto de comparaciones al mismo tiempo. Está más cerca de la fuente de datos que un mutex de aplicación y evita un protocolo de trigger separado propenso a carreras. Los costos son la amplificación de escritura de GiST, una dependencia de extensión y la necesidad de que la aplicación entienda los errores de restricción.
Si la regla abarca tablas, tiene capacidad dinámica o permite una cantidad limitada de superposición, una sola restricción de exclusión puede no ser suficiente. Considera franjas bloqueables, bloqueo a nivel de transacción o un servicio de programación de citas, manteniendo al mismo tiempo las restricciones de la base de datos para las invariantes que puedan expresar. Una restricción CHECK no puede hacer referencia de forma fiable a otras filas para mantener esta regla entre filas.
Plan de despliegue y evidencia
Carga datos de producción en una tabla sombra (shadow table), ejecuta un escaneo de superposiciones y genera una lista de reparación por sala e inquilino. Luego instala la extensión y la restricción, reproduce escrituras concurrentes y verifica el mapeo de errores, el costo del índice, el respaldo y restauración, y las alertas. Habilítalo para una pequeña porción de tráfico, compara los conflictos de restricción con los conflictos observados manualmente y cambia la tabla principal después de que los resultados se estabilicen.
La documentación de rangos de PostgreSQL define operadores como && y muestra una restricción de exclusión GiST que previene reservas superpuestas. Su documentación de restricciones define la semántica de exclusión por pares y señala que agregar la restricción crea el índice especificado. Estas fuentes primarias respaldan las afirmaciones sobre tipos de datos, operadores e índices; los detalles de despliegue aún requieren pruebas contra la versión real de PostgreSQL.
El material público de entrevistas sobre sistemas de reservas también incluye la prevención de reservas dobles concurrentes y las restricciones de exclusión de PostgreSQL como puntos de discusión en entrevistas. Este artículo mantiene ese escenario reconocible pero acota la respuesta a invariantes de datos, migración y verificación de fallos en lugar de repetir un diseño completo de sistema de reservas.
Errores comunes y preguntas de seguimiento
Hacer solo “verificar, luego insertar”
Las transacciones concurrentes pueden pasar ambas la verificación. Mantén la consulta como una sugerencia para la experiencia de usuario si es útil, pero deja que la restricción de la base de datos decida el resultado final.
Almacenar hora local en timestamp
La misma cadena puede representar diferentes instantes en distintas regiones y cambios de horario de verano. Define una política de zona horaria, almacena tiempo absoluto y convierte solo para la visualización.
Usar UNIQUE(room_id, start_at) para prevenir superposiciones
Las restricciones de unicidad bloquean el mismo valor de inicio, no un intervalo largo que cubra varios más cortos. Los operadores de rango expresan la superposición directamente.
Hacer que una restricción normal ignore filas eliminadas lógicamente
La restricción de exclusión normal compara cada fila. Separa los registros activos de los históricos o rediseña el modelo de estados; filtrar solo en las consultas de la aplicación es insuficiente.
¿Por qué no usar solo un trigger?
Un trigger debe implementar su propia semántica de concurrencia, bloqueo y errores, y puede crear casos límite difíciles de respaldo y restauración. Si la regla se puede expresar con rangos y operadores, la restricción de exclusión nativa suele ser más clara; usa un trigger o un programador cuando la regla exceda ese modelo.