Массивы в PostgreSQL

Когда несколько значений удобно держать прямо в колонке-массиве, как по ним искать и где этого делать не стоит.

Массив в PostgreSQL — это колонка, хранящая упорядоченный набор значений одного типа: text[], int[] и так далее.

Реляционная классика на «много значений у одной строки» отвечает отдельной таблицей-связкой. Но PostgreSQL умеет хранить набор прямо в колонке — массивом. Для коротких фиксированных списков (теги поста, роли пользователя, дни доставки) это короче и быстрее, чем JOIN. Главное — понимать пределы: у массивов слабее ссылочная целостность.

Зачем это на практике

Массив хорош, когда значения принадлежат строке и почти всегда читаются вместе с ней: метки статьи, выбранные опции заказа, список разрешений. Их не нужно по отдельности связывать с другой таблицей внешним ключом, и их немного. Тогда массив экономит и схему, и запрос: не надо заводить таблицу-связку и джойнить её на каждое чтение.

Создаём и заполняем

Тип массива — это тип элемента со скобками []. Литерал записывают как ARRAY[...] или строкой '{...}'. Нумерация элементов в 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
  ('Индексы в PostgreSQL', ARRAY['db','postgres','performance']),
  ('Гид по JSONB',         ARRAY['db','postgres','json']),
  ('Основы регулярок',     ARRAY['regex','text']);

-- доступ по индексу (с 1!) и срез
SELECT title, tags[1] AS first_tag, tags[1:2] AS first_two
FROM article;

Вывод:

Индексы в PostgreSQL | db | {db,postgres}
Гид по JSONB         | db | {db,postgres}
Основы регулярок     | regex | {regex,text}

ANY и ALL: проверка против набора

Чтобы спросить «есть ли в массиве такое значение», его сравнивают через = ANY(...). Конструкция ANY истинна, если условие выполнено хотя бы для одного элемента; ALL — если для всех.

-- статьи, помеченные тегом postgres
SELECT title FROM article
WHERE 'postgres' = ANY(tags);

-- статьи, где КАЖДЫЙ тег короче 11 символов
SELECT title FROM article
WHERE 11 > ALL(SELECT length(t) FROM unnest(tags) AS t);

Заметьте порядок: значение = ANY(массив). Это идиоматичный способ проверить вхождение скаляра. Кстати, x = ANY(ARRAY[1,2,3]) — это ровно то, во что PostgreSQL разворачивает привычное x IN (1,2,3).

unnest: массив → строки

Часто массив нужно «развернуть» в обычные строки, чтобы агрегировать, джойнить или фильтровать по элементам. Это делает функция unnest — на каждый элемент массива она порождает строку.

-- сколько раз встречается каждый тег по всем статьям
SELECT tag, count(*) AS n
FROM article, unnest(tags) AS tag
GROUP BY tag
ORDER BY n DESC, tag;

Вывод:

db        | 2
postgres  | 2
json      | 1
performance | 1
regex     | 1
text      | 1

Это разворачивание — мост между «массивной» и «реляционной» формой: развернули через unnest, поработали обычными средствами SQL, при желании собрали обратно через array_agg. Обратная функция array_agg(x) схлопывает группу строк в массив.

Операторы и GIN-индекс по массиву

У массивов есть операторы пересечения и содержания: @> (левый содержит правый), <@ (левый содержится в правом), && (пересекаются — есть общий элемент). И, как у JSONB, по массиву можно построить GIN-индекс, чтобы такие проверки не сканировали таблицу.

-- статьи, где есть И postgres, И db
SELECT title FROM article
WHERE tags @> ARRAY['postgres','db'];

-- статьи, пересекающиеся с набором интересов
SELECT title FROM article
WHERE tags && ARRAY['regex','json'];

-- индекс под эти операторы
CREATE INDEX idx_article_tags ON article USING GIN (tags);

GIN по tags ускоряет и @>, и &&, и = ANY-вхождение — то есть весь типовой набор «найди строки с такими метками». Без него каждая такая выборка читает всю таблицу.

Как это работает под капотом

Массив хранится в строке как единое значение: длина плюс сами элементы подряд (для типов переменной длины — со смещениями). Поэтому доступ к tags[1] дёшев, а вот «найти все строки, где есть элемент X» без индекса требует прохода по каждому массиву. GIN решает это так же, как для JSONB: строит обратный индекс, где для каждого элемента-значения хранится список строк, в чьих массивах он встречается. Операторы @>, && сводятся к пересечению/объединению этих списков. Большой массив всё же платит за себя: целиком он переписывается при любом изменении и при разрастании уезжает в TOAST-хранилище.

Когда массив, а когда отдельная таблица

Массив: набор короткий и ограниченный, элементы — простые значения (теги, коды, флаги), их не нужно связывать FOREIGN KEY с другой сущностью, и почти всегда они читаются вместе со строкой.
Отдельная таблица (связка many-to-many): у элемента есть свои атрибуты (когда добавлен, кем, метаданные); набор большой или быстро растёт; нужна ссылочная целостность (FOREIGN KEY на справочник) и контроль уникальности; по элементам идут сложные джойны и агрегаты.

Грубый ориентир: «теги поста» — массив; «товары в заказе с количеством и ценой» — отдельная таблица позиций. Если возникает соблазн положить в массив идентификаторы чужих строк (user_ids int[]), это почти всегда сигнал, что нужна таблица-связка: FOREIGN KEY на массив не повесить.

Частые ошибки

Нумеруют с нуля. В PostgreSQL tags[1] — первый элемент. tags[0] вернёт NULL, а не первый.
Пишут ANY наоборот. Правильно 'x' = ANY(arr), а не arr = ANY('x'). Слева — искомый скаляр, справа — массив.
Ждут ссылочной целостности. Внешний ключ нельзя наложить на элементы массива. Если нужна гарантия, что значения существуют в справочнике, — это таблица-связка.
Кладут в массив тяжёлые/растущие данные. Любое изменение переписывает массив целиком; для больших и часто меняющихся наборов это дорого — берите отдельную таблицу.

Итоги

Массив (text[], int[]) хранит упорядоченный набор одного типа; индексация с 1.
Вхождение скаляра — 'x' = ANY(arr); развернуть в строки — unnest, собрать обратно — array_agg.
Операторы @>, <@, && + GIN-индекс дают быстрый поиск «строки с такими элементами».
Массив — для коротких самодостаточных наборов; нужна целостность, атрибуты элементов или рост — берите отдельную таблицу.