Краткий ответ

Для PostgreSQL это ограничение исключения — EXCLUDE. Оно запрещает существование двух строк, для которых одновременно совпадает помещение и пересекаются интервалы аренды. Такое правило проверяется самой базой данных, включая конкурирующие транзакции, поэтому не зависит от дисциплины приложения.

Исторический контекст

Обычных ограничений PRIMARY KEY, UNIQUE, CHECK и FOREIGN KEY достаточно для многих правил, но они плохо выражают запрет пересечения значений. Например, UNIQUE проверяет равенство целых значений, а не пересечение временных диапазонов.

Ограничения исключения появились как практический способ описывать более общие взаимные несовместимости строк. Важно, что EXCLUDE не является универсально переносимой конструкцией стандартного SQL: это прежде всего возможность PostgreSQL.

Постановка проблемы

Пусть две записи относятся к одной комнате. Если интервал первой записи пересекается с интервалом второй, бронирование должно быть отклонено. Простая проверка в приложении может дать сбой: два параллельных запроса одновременно увидят свободную комнату и оба успешно создадут бронь.

Периодические фоновые проверки также не обеспечивают целостность: между чтением данных и записью может появиться конфликт. Последствиями становятся двойное бронирование, ручное исправление данных и сложная логика блокировок в каждом клиенте базы.

Подробное решение

В PostgreSQL правило задаётся ограничением EXCLUDE. Для каждой пары строк база проверяет указанные операторы; если все они возвращают истину, такая пара запрещена.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
    booking_id bigint PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        period WITH &&
    )
);

Здесь оператор = требует совпадения помещения, а && означает пересечение диапазонов. Поэтому бронирования разных помещений допустимы, как и непересекающиеся интервалы одной комнаты.

Тип tstzrange хранит диапазон времени с часовым поясом. Для соседних интервалов обычно используют полуоткрытые границы: период до 10:00 и период с 10:00 не пересекаются. Это устраняет неоднозначность на границе.

Расширение btree_gist в примере нужно для поддержки сравнения обычного значения room_id внутри GiST-индекса. Оно не превращает любой индекс в ограничение: проверку выполняет именно EXCLUDE, а индекс обеспечивает необходимую структуру и проверку конфликтов.

Участки выражения обычно делают NOT NULL. Если компонент ограничения допускает NULL, результат сравнения может быть неизвестным, и ожидаемое запрещающее условие может не сработать. Для сложных сценариев также нужно явно определить, разрешены ли открытые интервалы и как трактуются отменённые бронирования.

Плюс подхода — целостность сосредоточена в базе и защищена при конкурентных вставках. Минусы — зависимость от PostgreSQL, необходимость разобраться с диапазонами и индексом, а также возможное усложнение миграций и диагностики конфликтов.

В переносимой схеме обычно применяют триггер с блокировками или явную транзакционную логику. Это может работать, но разработчику приходится самостоятельно доказывать корректность блокировок при конкуренции; обычная проверка SELECT перед INSERT сама по себе недостаточна.

Ситуация из практики

В системе коворкинга сначала проверяли свободный интервал в сервисе, а затем добавляли бронь. При высокой нагрузке два экземпляра сервиса иногда одновременно регистрировали один и тот же стол на пересекающиеся периоды.

Рассматривались три варианта. Оставить проверку в приложении было проще, но она не защищала от гонок. Блокировать всю комнату на время операции было надёжнее, однако снижало параллелизм и требовало аккуратно выбирать строки для блокировки. Триггер позволял сохранить правило в базе, но его корректность зависела от сложной реализации блокировок.

Для PostgreSQL выбрали EXCLUDE USING gist по идентификатору комнаты и диапазону времени. В результате база стала единственным источником проверки, параллельные конфликтующие вставки начали отклоняться, а приложение стало обрабатывать конкретную ошибку нарушения ограничения вместо повторной реализации алгоритма проверки.

Что кандидаты часто упускают

Чем EXCLUDE отличается от UNIQUE для временных интервалов?

UNIQUE запрещает одинаковые ключевые значения, но не понимает семантику пересечения. Два разных диапазона могут пересекаться, не будучи равными, поэтому уникальность диапазона не решает задачу бронирования. EXCLUDE позволяет задать комбинацию операторов: например, равенство комнаты и пересечение периода.
Почему проверка пересечений в приложении не становится безопасной только потому, что она выполняется внутри транзакции?

Изоляция транзакции не означает автоматически, что две транзакции не прочитают одно и то же свободное состояние. При обычном сценарии обе могут выполнить проверку до фиксации другой транзакции, после чего обе вставят конфликтующие строки. Нужны ограничение, блокировки или другой механизм сериализации; EXCLUDE предоставляет такой контроль на уровне базы.
Что изменится, если период бронирования сделать допускающим NULL?

NULL означает неизвестное значение, а не пустой или нулевой интервал. Сравнение с неизвестным компонентом не даёт обычного истинного конфликта, поэтому ограничение может разрешить строку, которую разработчик считал конфликтующей. Если каждая бронь обязана иметь определённый период, следует использовать NOT NULL; если неопределённые периоды допустимы, их семантику нужно явно отделить от проверяемых бронирований.