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

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

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

По этому массиву можно фильтровать:

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

Операторы @> (содержит) и && (пересекается) заменяют многострочный 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);

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

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

Драйверы разных языков маппируют 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]

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

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

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

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

Тип	Что хранит
`int4range`	интервал целых чисел (`integer`)
`int8range`	интервал `bigint`
`numrange`	интервал `numeric`
`daterange`	интервал дат
`tsrange`	интервал `timestamp` без временной зоны
`tstzrange`	интервал `timestamptz`

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 содержит точку или другой range
`&&`	два range пересекаются
`-\|-`	два range соприкасаются (следуют друг за другом)
`<<` / `>>`	один range строго левее / правее другого

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

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

[a, b) — a включён, b исключён.
[a, b] — оба включены.
(a, b) — оба исключены.
[a, +infinity) — без верхней границы.

Для дат и времени стандартом считается [) — левая граница включена, правая исключена. Это важно для корректного сравнения:

Бронь A: [14:00, 16:00)
Бронь B: [16:00, 18:00)
→ Они не пересекаются — всё верно.

Если бы обе брони были [] (закрытые с обеих сторон), они бы пересекались на точке 16:00, хотя физически идут одна за другой.

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 генерирует Range<OffsetDateTime> для tstzrange.
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 маппирует tstzrange в TimestamptzRange.
from psycopg.types.range import TimestamptzRange

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

Multirange (PostgreSQL 14+)

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

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

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

Коротко

Массив подходит для простых наборов скалярных значений (теги, разрешения), когда элементов немного и у каждого нет своих атрибутов. Как только элементу нужны поля или внешние ссылки — это таблица.
Для поиска по массиву нужен GIN-индекс; операторы @> и && без него делают полное сканирование.
Range-тип заменяет пару колонок valid_from/valid_to и даёт удобные операторы для работы с интервалами.
Стандарт для дат и времени — граница [): левый край включён, правый исключён. Так два соседних интервала не пересекаются.
EXCLUDE constraint запрещает пересечение интервалов прямо в базе — включая защиту при одновременных запросах. Требует расширения btree_gist.
Multirange (PostgreSQL 14+) — несколько интервалов в одной колонке для расписаний с несколькими окнами.

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

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