Массивы и range-типы — теги, бронирования, EXCLUDE constraint

Опирается на правила: PG-T-070 … PG-T-074 из PostgreSQL Style Guide → раздел Массивы и range-типы.

Важно знать

Массив уместен: набор скалярных значений (теги), размер до десятков, нет отдельной идентичности, не нужно ссылаться извне.

Массив неуместен: атрибуты у элементов, ссылки на конкретный элемент, тысячи элементов, independent updates — это таблица.

GIN-индекс для @>, &&, ANY.

Range-типы оправданы когда сущность сама по себе — интервал (тариф, бронь, цена с историей).

@> (содержит) и && (пересекаются) — мощные операторы вместо многострочного WHERE.

EXCLUDE constraint — уникальная возможность PG для гарантии непересечения (брони, расписание).

По умолчанию [) — корректное && без перекрытия концов.

Multirange (PG14+) — для расписаний с несколькими окнами.

Массивы и range — редкие, но иногда правильные инструменты. Не используем «потому что прикольно» — используем когда альтернатива (отдельная таблица) усложняет схему ради формальности.

Массивы

CREATE TABLE article (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text   NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO article (title, tags) VALUES ('Заголовок', ARRAY['ddd', 'pg', 'архитектура']);

SELECT * FROM article WHERE 'pg' = ANY(tags);
SELECT * FROM article WHERE tags @> ARRAY['ddd'];     -- содержит все указанные
SELECT * FROM article WHERE tags && ARRAY['ddd'];     -- пересекается

Когда массив оправдан

PG-T-070:

Простой набор скалярных значений — теги, разрешения, локали поддержки.
Размер ограничен — десятки максимум.
Нет отдельной идентичности у элемента — никто не редактирует «третий тег», только заменяет весь набор.
Не нужно ссылаться на конкретный элемент из других таблиц.

Когда массив — антипаттерн

PG-T-071:

У элемента свои атрибуты — [{name, value, weight}, ...]. Это таблица.
Нужно ссылаться на элемент — article.tags[2] нестабильно при удалении.
Тысячи элементов на запись.
Independent updates разными запросами/транзакциями.

Контр-пример:

-- ✗ — items в массиве без идентичности
CREATE TABLE order_doc (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    items jsonb[]                       -- статусы, валидация, ссылки невозможны
);

-- ✓ — нормальная схема
CREATE TABLE order_doc (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE order_item (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id  bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE,
    sku       varchar(50) NOT NULL,
    quantity  integer NOT NULL CHECK (quantity > 0),
    price     numeric(15, 2) NOT NULL
);

GIN-индекс для массивов

CREATE INDEX article_tags_gin ON article USING gin (tags);

-- ускоряет
SELECT * FROM article WHERE tags @> ARRAY['ddd'];
SELECT * FROM article WHERE tags && ARRAY['ddd', 'pg'];
SELECT * FROM article WHERE 'ddd' = ANY(tags);

Java-сторона

jOOQ генерирует String[] / Integer[] для array-колонок. Удобно прокидывать как List<String> через converter.

Range-типы

Тип	Содержит
`int4range`	интервал `integer`
`int8range`	интервал `bigint`
`numrange`	интервал `numeric`
`daterange`	интервал `date`
`tsrange`	интервал `timestamp` (без TZ)
`tstzrange`	интервал `timestamptz`

Когда range оправдан

PG-T-072: когда сущность сама по себе — интервал.

Период действия тарифа / промокода / роли.
Бронирование (дата заезда — выезда).
Цена с историей (когда стоила эту цену).
Возрастные ограничения (int4range(18, 65)).

-- Две колонки — много кода, легко ошибиться
CREATE TABLE tariff_v1 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    valid_from  timestamptz NOT NULL,
    valid_to    timestamptz                       -- NULL = без ограничения
);

-- Range — атомарная семантика интервала
CREATE TABLE tariff_v2 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    validity    tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)')
);

Что даёт range

1. Атомарная семантика — один тип со встроенными включающими/исключающими краями ([), (], [], ()).

2. Операторы пересечения:

-- Две колонки — длинный WHERE
SELECT * FROM tariff_v1
WHERE valid_from <= '2026-05-07T12:00:00+00'
  AND (valid_to IS NULL OR valid_to > '2026-05-07T12:00:00+00');

-- Range — кратко
SELECT * FROM tariff_v2
WHERE validity @> timestamptz '2026-05-07T12:00:00+00';

Оператор	Что делает
`@>`	range содержит точку или другой range
`&&`	два range пересекаются
`-\|-`	соприкасаются (соседние)
`<<` / `>>`	строго слева / справа

3. EXCLUDE constraint — гарантия непересечения.

EXCLUDE для непересечений

PG-T-073: уникальная возможность PostgreSQL.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id   bigint NOT NULL,
    period    tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-07 14:00, 2026-05-09 12:00)');
INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-08 10:00, 2026-05-10 12:00)');
-- ERROR: conflicting key value violates exclusion constraint

На «двух колонках» сделать «брони не пересекаются» — только триггерами с риском race condition. На range — одной декларацией.

Альтернатива на стороне приложения = двойная бронь при параллельных запросах. EXCLUDE защищает даже при одновременных вставках на уровне БД.

Открытые/закрытые края

PG-T-074: по умолчанию [).

[a, b) — a включён, b исключён (стандарт для дат: [2026-03-01, 2026-04-01) = весь март).
[a, b] — оба включены.
(a, b) — оба исключены.
[a, +infinity) — без верхней границы.

Дефолт [):

Бронь «с 14:00 до 16:00» = [14:00, 16:00).
Бронь «с 16:00 до 18:00» = [16:00, 18:00).
Они не пересекаются — корректно.

С [] они бы пересекались на точке 16:00.

Multirange (PG14+)

CREATE TABLE schedule (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    avail  tstzmultirange NOT NULL
);

Массив непересекающихся range. Полезно для расписаний с несколькими окнами доступности.

Java-сторона

jOOQ генерирует Range<T>. Удобно обернуть в record:

public record ValidityPeriod(Instant from, Instant to) {
    public boolean contains(Instant t) {
        return !t.isBefore(from) && t.isBefore(to);
    }
}

Что запрещено

Антипаттерн	Правило	Что взамен
`items jsonb[]` для order items	`PG-T-071`	отдельная таблица `order_item`
Массив с атрибутами у элементов	`PG-T-071`	нормальная таблица
Массив с тысячами элементов	`PG-T-071`	связанная таблица
`valid_from + valid_to` для интервала	`PG-T-072`	`tstzrange`
Триггер для проверки непересечения	`PG-T-073`	`EXCLUDE` constraint
`[]` для дат-интервалов	`PG-T-074`	`[)`
Поиск по массиву без GIN	`PG-T-070`	`CREATE INDEX ... USING gin`
`daterange` для timestamptz	`PG-T-072`	`tstzrange`
`EXCLUDE` без `btree_gist` extension	`PG-T-073`	`CREATE EXTENSION btree_gist`

Куда дальше

PG → Массивы и range — нормативные формулировки.
JSONB — когда оправдан — JSONB vs array для полиморфных значений.
Время и таймзоны — tstzrange для timestamptz.
Типы индексов в PostgreSQL — GIN, GiST детали.
Расширения, которые часто нужны — btree_gist.
Антипаттерны типов — сводка.