Канал с кабанчиком on Telegram: “В PostgreSQL есть одна интересная фича под назва…”

В 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. Теперь у нас и уникальность проверяется, и поиск записей по тексту стал чуточку быстрее, но самое главное - индекс занимает в разы меньше места. Все счастливы.