В PostgreSQL есть одна интересная фича под названием
exclusion constraint. Она позволяет добиться того, чтобы строки в таблице не пересекались по какому-то условию. И чаще всего в примерах описывают сложные кейсы, например защиту от пересечения диапазонов.
Представим, что у нас есть таблица, хранящая информацию о бронировании номеров:
CREATE TABLE room_reservation (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id int NOT NULL, -- идентификатор номера
during tsrange NOT NULL, -- срок бронирования (дата и время с/по)
client_id int NOT NULL -- идентификатор клиента
);
Как нам на уровне БД обеспечить гарантию того, что брони одной и той же комнаты от разных клиентов не пересекутся?
Если упрощать условия и при этом усложнять схему БД, то можно было бы придумать что-то такое:
CREATE TABLE day (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
date date NOT NULL UNIQUE
);
CREATE TABLE room_reservation (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id int NOT NULL,
day_id int NOT NULL REFERENCES day(id),
client_id int NOT NULL,
UNIQUE(room_id, day_id)
);
Во-первых, нам пришлось добавить отдельную таблицу.
Во-вторых, теперь мы оперируем целыми днями.
В-третьих, теперь, если бронь больше чем на день - нам нужно несколько записей в room_reservation.
В общем, минусов хватает.
А exclusion constraint решает её очень просто:
CREATE TABLE room_reservation (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id int NOT NULL,
during tsrange NOT NULL,
client_id int NOT NULL,
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
С помощью выражения EXLUDE мы задаем, используя
GIST-индекс, следующее условие: записи не должны пересекаться, сравнивая room_id по полному совпадению и during по пересечению. Оператор && - один из операторов у range-типов, к числу которых относится и tsrange. Он позволяет проверить пересечение двух диапазонов.
Но как я ранее сказал, такой пример часто приводят в качестве демонстрации мощи exclusion constraint. Но есть еще один интересный юзкейс, о котором многие не знают, и связан он с более простой проверкой уникальности, без каких-то сложных пересечений диапазонов.
Допустим, у нас есть таблица с большим количеством строковых значений, для которых нам нужно обеспечить уникальность (потому что на эти записи по id ссылаются другие таблицы):
CREATE TABLE quotes (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
quote text NOT NULL
);
Самый очевидный способ это сделать - добавить UNIQUE индекс (или UNIQUE constraint, что фактически одно и то же):
CREATE UNIQUE INDEX quotes_unique_idx ON quotes(quote);
Но вот в чем проблема - теперь наш индекс по размеру примерно совпадает с таблицей, поскольку дефолтный B-Tree индекс содержит в себе все индексируемые значения, а строки у нас достаточно крупные.
Хорошо что мы умные и знаем про Hash индекс, размер которого не зависит от размера индексируемых данных, а зависит только от их количества.
CREATE UNIQUE INDEX quotes_unique_idx ON quotes USING HASH(quote);
ERROR: access method "hash" does not support unique indexes
А, ой. Hash-индексы не умеют в проверку уникальности. А как быть? Хочется и рыбку съесть место сэкономить, и уникальность быстро проверять.
И вот тут на помощь снова приходит exclusion constraint. Мы можем сделать вот так:
ALTER TABLE quotes ADD CONSTRAINT quotes_unique_hash_idx EXCLUDE USING HASH (quote WITH =);
Таким простым маневром мы создаем Hash-индекс, поверх которого работает constraint, проверяющий записи на совпадение по равенству quote. Теперь у нас и уникальность проверяется, и поиск записей по тексту стал чуточку быстрее, но самое главное - индекс занимает в разы меньше места. Все счастливы.
