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

← назад к разделу

У товара пять меток, и под них заводят таблицу связи с лишним JOIN в каждом запросе; бронь номера хранят двумя колонками и проверяют пересечения в коде, и однажды две брони всё-таки пересекаются. Обе задачи PostgreSQL закрывает типами, которых нет в большинстве баз: массивами и range-типами. В правильном месте они убирают лишние таблицы или десятки строк кода; разберём, когда они нужны и как с ними работать.

Массивы: когда список — часть записи

Обычно, если у статьи есть теги, создают отдельную таблицу article_tag с внешним ключом. Это правильно в общем случае. Но иногда это лишняя сложность ради формальности.

PostgreSQL позволяет хранить массив прямо в колонке:

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', 'архитектура']);

По этому массиву фильтруют тремя операторами. Запрос ниже берёт три набора тегов и печатает вердикт каждого оператора:

живой пример

WITH article AS (
    SELECT * FROM (VALUES
        ('Агрегаты',      ARRAY['ddd', 'pg']),
        ('Планы запроса', ARRAY['pg', 'explain']),
        ('События',       ARRAY['ddd', 'kafka'])
    ) AS v(title, tags)
)
SELECT title,
       'pg' = ANY(tags)                  AS any_pg,       -- есть хотя бы этот тег
       tags @> ARRAY['ddd']              AS contains_ddd, -- содержит все указанные
       tags && ARRAY['kafka', 'explain'] AS overlaps      -- пересекается с набором
FROM article
ORDER BY title;

В боевом запросе те же операторы стоят в WHERE: WHERE tags @> ARRAY['ddd'] вернёт только строки с этим тегом. @> (содержит) и && (пересекается) заменяют многострочный JOIN с фильтрацией.

Когда массив уместен

Массив хорошо работает, если выполняются все условия:

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

Когда лучше создать таблицу

Если хотя бы одно из условий не выполняется — массив становится проблемой:

• У элемента свои поля — например, {name, value, quantity}. Это таблица, не массив.
• Нужно ссылаться на конкретный элемент — позиция в массиве нестабильна при удалении.
• Тысячи элементов — массив не создан для больших объёмов.
• Элементы обновляются независимо из разных запросов или транзакций.

Типичная ошибка — хранить позиции заказа в массиве:

-- Частая ошибка: позиции заказа в массиве
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
);

Индекс для поиска по массиву

Обычный B-tree индекс не помогает для операторов @> и &&. Нужен GIN-индекс:

CREATE INDEX article_tags_gin ON article USING gin (tags);

После этого запросы с @> и && будут использовать индекс вместо полного сканирования таблицы.

С <@ («массив целиком помещается в этот набор») история другая: индекс сюда тоже подключается, но выигрыша почти нет. По такому условию GIN сначала берёт все строки, где встретился хоть один из перечисленных элементов, а потом перепроверяет каждую по самой таблице. На 200 000 строк это 5 795 кандидатов ради 11 подходящих и чтение почти всей таблицы — то же самое, что и без индекса. Настоящая польза GIN по массиву — в @> и &&.

А вот привычная запись 'pg' = ANY(tags) через такой индекс не пойдёт: GIN по массиву не знает оператора = в этой форме и запрос честно прочитает всю таблицу. Тот же смысл индексируемо записывается как tags @> ARRAY['pg'] — «массив содержит этот элемент». Разница только в форме записи, а в плане — полное сканирование против индекса.

Как читать массив в коде

Драйверы разных языков маппируют PostgreSQL-массив по-разному. Ниже — чтение колонки tags text[].

// jOOQ генерирует String[] для text[]-колонок.
List<String> tags = dsl
    .select(ARTICLE.TAGS)
    .from(ARTICLE)
    .where(ARTICLE.ID.eq(articleId))
    .fetchOne(r -> Arrays.asList(r.get(ARTICLE.TAGS)));

// pgx маппирует text[] в []string напрямую.
var tags []string
err := pool.QueryRow(ctx,
    "SELECT tags FROM article WHERE id = $1", articleID,
).Scan(&tags)

// node-postgres (pg) возвращает text[] как string[].
const { rows } = await pool.query<{ tags: string[] }>(
    'SELECT tags FROM article WHERE id = $1',
    [articleId],
);
const tags: string[] = rows[0].tags;

# psycopg3 автоматически маппирует text[] в list[str].
async with await psycopg.AsyncConnection.connect(dsn) as conn:
    row = await conn.execute(
        "SELECT tags FROM article WHERE id = %s", (article_id,)
    )
    tags: list[str] = (await row.fetchone())[0]

Что с массивом можно делать

Одного оператора «содержит» мало — вот минимальный набор, который закрывает обычные задачи.

-- развернуть массив в строки: теги как обычная выдача
SELECT p.id, tag FROM products p, unnest(p.tags) AS tag WHERE tag LIKE 'к%';

-- собрать строки обратно в массив
SELECT seller_id, array_agg(DISTINCT status ORDER BY status) AS statuses
FROM orders GROUP BY seller_id;

-- добавить и убрать один элемент
UPDATE products SET tags = array_append(tags, 'новинка') WHERE id = 1;
UPDATE products SET tags = array_remove(tags, 'распродажа') WHERE id = 1;

-- сколько элементов и на каком месте лежит значение
SELECT cardinality(tags) AS n, array_position(tags, 'кофе') AS pos FROM products WHERE id = 1;

unnest и array_agg — пара: первая разворачивает, вторая собирает, и вместе они дают почти любую правку («убрать все теги, начинающиеся с tmp_» — это array_agg по отфильтрованному unnest). array_append и array_remove переписывают колонку целиком, как любое обновление, — экономии на «изменили один элемент» здесь нет.

Две вещи, о которые спотыкаются с непривычки. Элементы нумеруются с единицы: tags[1] — первый, tags[0] — пусто, без всякой ошибки. И NULL внутри массива ломает проверки: 'кофе' = ANY(tags) вернёт не false, а «неизвестно», если в массиве есть NULL; оператор @> на NULL-элементах ведёт себя так же непредсказуемо, а cardinality их честно считает. Поэтому колонку-массив объявляют NOT NULL DEFAULT '{}', а сам массив держат без пустых элементов — проверкой CHECK (array_position(tags, NULL) IS NULL).

Range-типы: интервал как первоклассный тип

Когда нужно хранить период — обычно заводят две колонки: valid_from и valid_to. Это работает, но плодит многословный код и оставляет место для ошибок.

PostgreSQL предлагает другой путь: range-тип — тип данных, который сам по себе является интервалом.

Встроенные range-типы:

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

Одна колонка вместо двух

-- Два поля — много кода, легко ошибиться в граничных условиях
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', '[)')
);

Запрос «что действует прямо сейчас» выглядит по-разному:

-- Два поля — длинный WHERE с обработкой NULL
SELECT * FROM tariff_v1
WHERE valid_from <= now()
  AND (valid_to IS NULL OR valid_to > now());

-- Range — кратко
SELECT * FROM tariff_v2
WHERE validity @> now();

Оператор @> читается как «содержит». PostgreSQL сам разбирается с границами.

Операторы range-типов

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

У range-типа можно задать, включает ли он свои границы. Нотация:

• [a, b) — a включён, b исключён.
• [a, b] — оба включены.
• (a, b) — оба исключены.
• [a, ) — верхней границы нет вовсе: подходит любое значение больше a.

Пустая граница и значение 'infinity' — не одно и то же, хотя на первый взгляд означают одно. [a, ) — «границы нет», и upper_inf(...) отвечает true. А tstzrange(now(), 'infinity', '[)') из примера выше — граница есть, просто её значение бесконечно далеко: upper_inf(...) отвечает false, а upper(...) возвращает саму infinity. Проверке @> now() всё равно, а вот запрос, который ищет незакрытые периоды по пустой границе, строки со значением 'infinity' не найдёт.

Для дат и времени стандартом считается [) — левая граница включена, правая исключена. Иначе две соседние брони начинают считаться пересекающимися. Спросим об этом сам PostgreSQL:

живой пример

SELECT tsrange('2026-05-07 14:00', '2026-05-07 16:00', '[)')
    && tsrange('2026-05-07 16:00', '2026-05-07 18:00', '[)') AS overlap_half_open,
       tsrange('2026-05-07 14:00', '2026-05-07 16:00', '[]')
    && tsrange('2026-05-07 16:00', '2026-05-07 18:00', '[]') AS overlap_closed;

Первая пара с краями [) даёт false: брони идут одна за другой. Вторая, закрытая с обеих сторон, даёт true — обе содержат точку 16:00, хотя физически не пересекаются.

Когда range не брать

Диапазон удобен ровно тем, что это одно значение, — и неудобен там, где работают с границами по отдельности. Отчёт «подписки, начавшиеся в мае», сортировка ORDER BY valid_from, соединение «действовало ли правило на дату документа» — всё это на паре обычных колонок пишется прямо и индексируется обычным B-деревом, а на диапазоне требует функций lower(period) и upper(period), то есть индекса по выражению или перебора.

Отсюда практический выбор. Нужны проверки пересечений и запрет двойных броней — берите tstzrange и EXCLUDE, ради них всё и затевалось. Нужны обычные отчёты по датам начала и конца — двух колонок достаточно. Компромисс, который часто выбирают в живых схемах: держать пару колонок как источник правды, а диапазон добавить генерируемой колонкой поверх них (GENERATED ALWAYS AS (tstzrange(valid_from, valid_to, '[)')) STORED) и повесить EXCLUDE уже на неё. Тогда и отчёты простые, и пересечения запрещены — этот же приём и есть готовый путь миграции с двух колонок на диапазон: колонка появляется рядом, ничего не ломая, а код переводится на неё постепенно.

EXCLUDE constraint: защита от пересечений на уровне базы

Бронирования не должны пересекаться. Обычно это проверяют в коде приложения. Но проверка в коде не защищает от одновременных запросов от двух пользователей.

PostgreSQL решает это на уровне базы с помощью EXCLUDE constraint. Это уникальная возможность, которой нет в других популярных СУБД.

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 &&)
);

Constraint читается так: «не допускать две записи, у которых room_id одинаковый И period пересекается». При попытке вставить пересекающуюся бронь PostgreSQL вернёт ошибку:

INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-07 14:00, 2026-05-09 12:00)');
-- OK

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

Расширение btree_gist нужно, чтобы PostgreSQL мог включить в GiST-индекс обычный тип bigint (для room_id). Без него EXCLUDE с несколькими колонками разных типов не работает.

Важно: EXCLUDE защищает даже при одновременных вставках из разных соединений. Проверка на стороне приложения это не гарантирует.

Как читать range-колонку в коде

Ниже — чтение колонки validity tstzrange.

// Диапазонные типы понимает не сам jOOQ, а модуль jooq-postgres-extensions:
// с ним для tstzrange генерируется OffsetDateTimeRange, без него — просто Object.
var row = dsl
    .select(TARIFF_V2.VALIDITY)
    .from(TARIFF_V2)
    .where(TARIFF_V2.ID.eq(tariffId))
    .fetchOne();

var r = row.get(TARIFF_V2.VALIDITY);
var from = r.lower().toInstant();
var to   = r.upper().toInstant();

// pgx читает tstzrange через pgtype.Range[pgtype.Timestamptz].
var validity pgtype.Range[pgtype.Timestamptz]
err := pool.QueryRow(ctx,
    "SELECT validity FROM tariff_v2 WHERE id = $1", tariffID,
).Scan(&validity)

from := validity.Lower.Time
to   := validity.Upper.Time

// node-postgres возвращает tstzrange как строку.
// Удобнее разложить в SQL:
const { rows } = await pool.query<{ lower: Date; upper: Date }>(
    `SELECT lower(validity) AS lower, upper(validity) AS upper
       FROM tariff_v2 WHERE id = $1`,
    [tariffId],
);
const { lower, upper } = rows[0];

# psycopg3 отдаёт любой range-тип одним классом Range.
from datetime import datetime
from psycopg.types.range import Range

async with await psycopg.AsyncConnection.connect(dsn) as conn:
    row = await conn.execute(
        "SELECT validity FROM tariff_v2 WHERE id = %s", (tariff_id,)
    )
    validity: Range[datetime] = (await row.fetchone())[0]
    lower = validity.lower
    upper = validity.upper

Три вещи, без которых EXCLUDE в живой схеме работает не так, как задумано.

Частичное ограничение. Отменённая бронь продолжает занимать комнату, пока не сказано обратное: EXCLUDE … WHERE (status <> 'CANCELLED') исключает отменённые из проверки. Без этой оговорки система отказывается принимать новую бронь на освободившееся время, и выглядит это как ошибка на ровном месте.

Отложенная проверка. DEFERRABLE INITIALLY DEFERRED переносит проверку на конец транзакции — без этого нельзя «подвинуть» две соседние брони одной транзакцией: первая же правка временно создаст пересечение и упадёт, хотя итоговое состояние корректно.

Цена. EXCLUDE — это не запись в каталоге, а GiST-индекс, который обновляется при каждой вставке и правке участвующих колонок. Отсюда и менее очевидное следствие: обновление такой колонки не может быть «лёгким» (HOT), то есть каждая правка периода пишет новую версию строки и обновляет индекс. На таблице броней это незаметно, на таблице с тысячами правок в секунду — уже нет.

Multirange (PostgreSQL 14+)

Начиная с PostgreSQL 14 есть multirange — набор непересекающихся интервалов в одной колонке. Полезен для расписаний с несколькими окнами доступности:

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

Вместо хранения нескольких строк с разными периодами — одна колонка с упорядоченным набором интервалов, и занятость проверяется одним оператором:

живой пример

WITH avail AS (
    SELECT '{[2026-05-07 09:00,2026-05-07 12:00),[2026-05-07 14:00,2026-05-07 18:00)}'::tstzmultirange AS slots
)
SELECT slots @> '2026-05-07 13:00'::timestamptz AS free_at_13,
       slots @> '2026-05-07 15:00'::timestamptz AS free_at_15
FROM avail;

В 13:00 окна нет, в 15:00 есть — обе проверки в одной строке.

Что с ним реально делают. range_agg собирает набор диапазонов в один multirange и сам склеивает соприкасающиеся — так из строк «был на смене с 9 до 13» и «с 13 до 18» получается одно окно с 9 до 18. Операторы те же, что у диапазонов: @> (содержит момент или диапазон), && (пересекается), - и * (разность и пересечение), а unnest разворачивает multirange обратно в отдельные диапазоны.

SELECT employee_id, range_agg(shift) AS worked
FROM shifts
GROUP BY employee_id;

И предупреждение, ради которого это стоит помнить: индекса, который отвечал бы на «в каком из окон внутри multirange-колонки лежит этот момент», нет. GiST по multirange умеет отвечать на вопросы про колонку целиком (@>, &&), а поиск по отдельному внутреннему окну превращается в перебор с разворачиванием. Поэтому multirange хорош как результат вычисления и как компактное хранение «свободных окон», а не как основная колонка, по которой ищут.

Коротко

• Массив подходит для простых наборов скалярных значений (теги, разрешения), когда элементов немного и у каждого нет своих атрибутов. Как только элементу нужны поля или внешние ссылки — это таблица.
• Для поиска по массиву нужен GIN-индекс; операторы @> и && без него делают полное сканирование.
• Range-тип заменяет пару колонок valid_from/valid_to и даёт удобные операторы для работы с интервалами.
• Стандарт для дат и времени — граница [): левый край включён, правый исключён. Так два соседних интервала не пересекаются.
• EXCLUDE constraint запрещает пересечение интервалов прямо в базе — включая защиту при одновременных запросах. Требует расширения btree_gist.
• Multirange (PostgreSQL 14+) — несколько интервалов в одной колонке для расписаний с несколькими окнами.
• Массив разворачивают unnest и собирают array_agg; элементы нумеруются с единицы, а NULL внутри ломает = ANY и @> — колонку держат NOT NULL DEFAULT '{}'.
• EXCLUDE в жизни почти всегда частичный (WHERE status <> 'CANCELLED') и часто DEFERRABLE; платит GiST-индексом и «тяжёлым» обновлением строки.
• Диапазон берут ради пересечений; для отчётов по границам удобнее две колонки, а перейти между ними помогает генерируемая колонка tstzrange(...) поверх них.

Что почитать дальше

• Типы индексов в PostgreSQL — GIN и GiST: когда и какой использовать.
• JSONB в PostgreSQL — когда вместо массива нужен JSONB.
• Время и временные зоны — почему tstzrange, а не tsrange.