Массивы в PostgreSQL: синтаксис, операции, индексы и практические примеры запросов

Массив в PostgreSQL это встроенный тип данных, который хранит упорядоченный набор значений одного типа в одной колонке. Колонка объявляется как tags text[] или tags text ARRAY, элементы доступны по индексу начиная с 1, а поиск по вхождению ускоряет GIN-индекс в связке с операторами @>, <@ и &&.

Коротко о главном: массивы оправданы для небольших списков, которые читаются целиком, таких как теги статьи, набор ID, список флагов или несколько значений метрик. Большие коллекции с частыми точечными правками и требованием ссылочной целостности лучше держать в отдельной таблице связей. Массивы есть в PostgreSQL с первых версий, а удобные функции вокруг них добавляли постепенно: array_position, array_remove и array_replace появились в 9.3, cardinality и WITH ORDINALITY в 9.4. Всё описанное ниже работает на версиях 12 и новее без дополнительных расширений, кроме опционального intarray для целочисленных массивов.

Длина массива не фиксируется при создании колонки: в text[] можно записать и пустой массив, и набор из сотни элементов. Верхняя граница задана лимитом поля в 1 ГБ, а значения крупнее примерно 2 КБ сжимаются и уходят в TOAST-таблицу, что стоит учитывать при чтении больших наборов.

Что такое массивы в PostgreSQL и зачем они нужны

Массив хранит элементы одного типа вместе с информацией о размерности. Значение массива занимает одну ячейку в строке, поэтому SELECT возвращает весь набор сразу, без JOIN и без второго запроса в приложение. Типовые сценарии: теги статьи, список ID связанных сущностей, история статусов заказа, семь значений недельных метрик, набор ролей пользователя.

Массивы бывают многомерными, но фиксированной размерности у типа нет: колонка int[] примет и одномерное значение {1,2,3}, и матрицу {{1,2},{3,4}}. Индексация начинается с 1, а не с 0, как в большинстве языков программирования, и это самая частая причина ошибок при переносе логики из кода в SQL.

Массивы не отменяют нормализацию. Они упрощают схему и убирают JOIN там, где список читается вместе с родительской строкой и редко меняется по частям. Ссылочной целостности внутри массива нет: PostgreSQL не помешает записать в tags значение, которого нет в таблице тегов, и не удалит тег из массива при удалении записи в справочнике.

Когда использовать массивы, а когда нормализацию

Ориентируйтесь на два параметра: размер списка и частоту точечных правок. Если список небольшой и обновляется целиком вместе со строкой, массив экономит таблицу и JOIN. Если элементы добавляются, удаляются и пересчитываются поштучно, отдельная таблица связей предсказуемее: один INSERT вместо перезаписи всего значения.

Если в вашем окружении массивы уже не первый инструмент, полезно сравнить подходы с другими СУБД: хранение массивов в MySQL через JSON, сериализацию и таблицу-связку и массивы внутри BSON-документов в MongoDB дают другую картину компромиссов, и это помогает выбрать модель осознанно.

Синтаксис объявления и вставки массивов

Тип колонки-массива записывают двумя способами: text[] или text ARRAY. Первый вариант короче и встречается чаще.

CREATE TABLE articles (
    id    bigserial PRIMARY KEY,
    title text NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

ALTER TABLE articles ADD COLUMN scores integer ARRAY;

DEFAULT '{}' избавляет от NULL в колонке тегов и упрощает запросы: пустой массив ведёт себя предсказуемее, чем NULL, при разворачивании и поиске по вхождению.

Использование ARRAY и литералов

Два равнозначных способа задать значение: конструктор ARRAY[1,2,3] и строковый литерал с приведением типа '{1,2,3}'::int[].

SELECT ARRAY[1, 2, 3];              -- {1,2,3}
SELECT '{1,2,3}'::int[];           -- {1,2,3}
SELECT ARRAY['a', 'b']::text[];    -- {a,b}

INSERT INTO articles (title, tags)
VALUES ('Индексы в PostgreSQL', ARRAY['postgresql', 'index', 'performance']);

UPDATE articles
SET tags = '{postgresql,sql}'::text[]
WHERE id = 1;

Литерал с кавычками и запятыми внутри элементов требует двойных кавычек и удвоения внутренних кавычек: '{"it, infra","dba's notes"}'. Строка с пробелом или запятой без кавычек распарсится неверно или вызовет ошибку формата. Конструктор ARRAY[] таких сложностей не создаёт, поэтому в приложениях удобнее передавать массив параметром и не собирать литерал вручную.

Массив из подзапроса собирают через ARRAY(SELECT ...), а из набора строк через array_agg:

SELECT ARRAY(SELECT id FROM departments WHERE active);

INSERT INTO article_tags (article_id, tags)
SELECT a.id, array_agg(t.name ORDER BY t.name)
FROM articles a JOIN tags t ON t.article_id = a.id
GROUP BY a.id;

Многомерный массив задают вложенными списками: ARRAY[[1,2],[3,4]] или '{1,2;3,4}'::int[][]. Все элементы обязаны иметь один тип, иначе PostgreSQL попробует неявное приведение или вернёт ошибку. NULL внутри массива допустим и хранится как отдельный элемент.

Доступ к элементам массива и их изменение

Элемент берут по индексу в квадратных скобках, для многомерного массива индексы указывают каскадом.

SELECT tags[1] FROM articles WHERE id = 1;             -- первый тег
SELECT matrix[1][2] FROM t;                            -- элемент первой строки, второго столбца
SELECT tags[2:3] FROM articles WHERE id = 1;           -- срез, снова массив

UPDATE articles SET tags[2] = 'sql' WHERE id = 1;      -- правка одного элемента

Выход за границы не ошибка: tags[99] вернёт NULL, а срез вне диапазона вернёт пустой массив. Это удобно для необязательных полей, но опасно для логики, где NULL трактуется как отсутствие данных.

Обновление одного элемента переписывает строку целиком: PostgreSQL не хранит элементы массива отдельными версиями. На колонках с TOAST-значениями каждая правка тега приводит к перезаписи всего набора и росту WAL. При тысячах обновлений в секунду таблица связей окажется дешевле.

Функции array_append, array_prepend, array_cat, array_remove

Для добавления и удаления элементов есть отдельные функции, каждая возвращает новый массив:

SELECT array_append(ARRAY['a','b'], 'c');        -- {a,b,c}
SELECT array_prepend('z', ARRAY['a','b']);       -- {z,a,b}
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);        -- {1,2,3,4}
SELECT array_remove(ARRAY['a','b','a'], 'a');    -- {b}
SELECT array_replace(ARRAY['html','js'], 'js', 'javascript');
SELECT array_position(ARRAY['a','b','c'], 'b');  -- 2

Оператор || делает то же, что array_cat, и читается короче: tags || ARRAY['new']. Функция array_remove убирает все вхождения элемента, а не первое. Ни одна из этих функций не меняет исходный массив, поэтому результат нужно присваивать:

UPDATE articles SET tags = array_append(tags, 'tutorial') WHERE id = 1;

Для накопления списка в цикле такой подход плох: каждая итерация создаёт новый массив. Собирайте значения через array_agg в одном запросе или через array_append на уровне приложения.

Агрегация и разворачивание массивов: array_agg и unnest

array_agg собирает значения группы в массив, unnest делает обратную операцию и превращает массив в набор строк. Вместе они закрывают большинство задач с коллекциями без промежуточных таблиц.

SELECT customer_id, array_agg(product ORDER BY product) AS products
FROM orders
GROUP BY customer_id;

Порядок элементов в результате без ORDER BY внутри агрегата не гарантирован: планировщик может читать строки в любом порядке. NULL-элементы array_agg включает в массив, в отличие от string_agg, который их пропускает. Чтобы исключить NULL, добавьте фильтр:

SELECT department, array_agg(email ORDER BY email)
       FILTER (WHERE email IS NOT NULL) AS emails
FROM users GROUP BY department;

SELECT array_agg(DISTINCT region ORDER BY region) FROM clients;

Примеры использования array_agg с GROUP BY

Агрегация списка email по отделам убирает десятки строк из результата и переносит сборку в базу. Дополнительно можно завернуть массив в функцию проверки размера, чтобы отчёт не разросся:

SELECT department,
       cardinality(array_agg(email)) AS total_emails,
       array_agg(email ORDER BY created_at DESC) AS recent_emails
FROM users
WHERE created_at > now() - interval '90 days'
GROUP BY department
HAVING cardinality(array_agg(email)) > 5;

Массив в качестве результата удобен, когда приложение всё равно обрабатывает список целиком: один round-trip вместо потока строк. Для выгрузок в CSV, где нужен плоский результат, применяйте unnest или string_agg.

Разворачивание массивов с помощью unnest и LATERAL

unnest вызывают в списке выборки, во FROM и в условиях. При вызове во FROM функция может ссылаться на столбцы предыдущих таблиц, LATERAL в простом случае не требуется, но с ним запрос читается яснее:

SELECT a.id, t.tag
FROM articles a, unnest(a.tags) AS t(tag)
WHERE t.tag = 'postgresql';

SELECT a.id, t.tag
FROM articles a
CROSS JOIN LATERAL unnest(a.tags) WITH ORDINALITY AS t(tag, pos)
ORDER BY a.id, t.pos;

WITH ORDINALITY добавляет номер элемента в развёрнутом наборе и сохраняет исходный порядок, что полезно при выводе списков в интерфейсе. Несколько массивов можно развернуть одним вызовом unnest(a, b): строки пойдут параллельно по позициям, а короткий массив добьётся NULL до длины длинного.

Помните про кратность: строка с пятью тегами даст пять строк результата. Дальнейшая группировка, LIMIT и подсчёты работают уже по развёрнутому набору, поэтому для агрегатов по статьям такой JOIN применяют внутри подзапроса, а не на верхнем уровне.

Операторы ANY и ALL для сравнения с массивами

ANY проверяет, что условие верно хотя бы для одного элемента, ALL требует выполнения для всех. Обе конструкции работают и с массивом, и с подзапросом.

SELECT * FROM users WHERE id = ANY(ARRAY[1, 2, 3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);
SELECT * FROM events WHERE status <> ALL(ARRAY['failed', 'canceled']);

Синтаксис = ANY(array) эквивалентен IN по смыслу, но свободнее: слева может быть выражение, справа массив из параметра приложения. Для b-tree индексов такой запрос превращается в обычный поиск по списку значений, поэтому = ANY на индексированной колонке работает быстро.

Пустые массивы дают неочевидный результат: условие = ANY('{}') вернёт false, а = ALL('{}') вернёт true. Запрос вида "удалить всё, что не входит в список" с пустым списком сработает как удаление всей таблицы, если не добавить проверку на пустоту. NULL внутри массива делает сравнение неопределённым: x = ANY(ARRAY[NULL, 1]) вернёт NULL, а не true или false, и строка выпадет из WHERE. Используйте IS NOT NULL или функцию array_position, если NULL-элементы возможны.

Сравнение с подзапросами

Подзапрос справа работает так же, как массив, и выполняется один раз на весь внешний запрос:

SELECT * FROM employees
WHERE department_id = ANY(
    SELECT id FROM departments WHERE active = true
);

SELECT * FROM clients c
WHERE NOT EXISTS (
    SELECT 1 FROM contracts ct
    WHERE ct.client_id = c.id AND ct.status <> ALL(ARRAY['signed', 'archived'])
);

Разница между = ANY(SELECT ...) и IN(SELECT ...) минимальна, а вот NOT IN с подзапросом, возвращающим NULL, молча теряет строки. Конструкция <> ALL ведёт себя так же и требует тех же осторожностей.

Операторы @>, <@, && и GIN-индексы для ускорения поиска

Для поиска по содержимому массива есть три оператора: @> проверяет, что левый массив содержит все элементы правого, <@ проверяет вложенность в обратную сторону, && ищет хотя бы один общий элемент.

SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'index'];
SELECT * FROM articles WHERE tags <@ ARRAY['sql', 'nosql', 'postgresql'];
SELECT * FROM articles WHERE tags && ARRAY['sql', 'nosql'];

Два первых запроса отличаются строгостью: одиночный тег найдёт все статьи с ним, пара тегов только те, где встречаются оба. Оператор && полезен для блока "похожие материалы": он находит пересечение по любому тегу и хорошо ложится на GIN-индекс. Сравнение элементов по индексу, например tags[1] = 'sql', через GIN не ускоряется, для него нужен отдельный индекс по выражению.

Создание GIN-индекса и его влияние на производительность

GIN хранит по одной записи на каждый элемент массива и поддерживает операторы @>, <@, && и равенство:

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
CREATE INDEX idx_articles_tags_fast ON articles USING GIN (tags) WITH (fastupdate = off);

Параметр fastupdate включён по умолчанию: новые записи попадают в pending list и переносятся в основной индекс позже, что ускоряет вставку и замедляет первые чтения. Размер pending list ограничен параметром gin_pending_list_limit (по умолчанию 4 МБ). Когда нагрузка на чтение важнее скорости записи, fastupdate = off даёт стабильные планы без внезапной очистки списка.

Для целочисленных массивов расширение intarray добавляет класс операторов gin__int_ops, который хранит не отдельные значения, а сжатые диапазоны, и индекс получается заметно компактнее. Цена GIN в любом случае выше, чем у b-tree: индекс на 200 тысячах строк с двумя-тремя тегами занимает около 14 МБ, а каждая вставка или обновление массива обновляет все его элементы в индексе. Индексируйте те колонки, по которым действительно ищете, и не дублируйте GIN на колонке, которую читают только целиком.

Ограничения и особенности многомерных массивов

Главное ограничение многомерных массивов: они прямоугольные. Внутри одного значения каждый вложенный массив обязан иметь одинаковую длину.

SELECT ARRAY[[1,2],[3,4]];   -- корректно: 2x2
SELECT ARRAY[[1,2],[3]];     -- ошибка: multidimеnsional arrays must have matching dimensions

Ошибка возникает при вычислении выражения, поэтому данные из приложения стоит проверять до вставки. При обновлении элемента многомерного массива указывают все индексы: matrix[1][2] = 5. Частичное обновление строки матрицы невозможно, значение перезаписывается целиком.

Массив не имеет фиксированной длины, но и не хранит её как ограничение: колонка int[] примет значение любой длины, а NOT NULL на уровне колонки не запрещает NULL внутри массива. Проверку на отсутствие NULL-элементов делают отдельным условием:

ALTER TABLE articles ADD CONSTRAINT tags_no_nulls
    CHECK (array_position(tags, NULL) IS NULL);

Пустой массив и NULL это разные значения. Для поиска по вхождению разница принципиальна: tags && '{}' всегда false, а tags IS NULL вернёт строки, где значение не задано вовсе. Перед миграцией проверьте, нет ли в колонке NULL, иначе часть запросов начнёт терять строки.

Проверка размерности и длины массива

Для инспекции значений есть несколько функций:

SELECT array_dims(ARRAY[[1,2],[3,4]]);        -- [1:2][1:2]
SELECT array_ndims(ARRAY[[1,2],[3,4]]);       -- 2
SELECT array_length(ARRAY[1,2,3], 1);         -- 3
SELECT cardinality(ARRAY[1,2,3]);             -- 3
SELECT array_lower(ARRAY[1,2,3], 1);          -- 1
SELECT cardinality('{}'::int[]);              -- 0

array_length для пустого массива возвращает NULL, а cardinality для него же возвращает 0. Для условий "список непустой" применяйте cardinality(tags) > 0: результат всегда число, и логика не ломается о NULL. Функция array_dims показывает границы срезов, что важно после операций с array_cat и срезами, где нижняя граница может отличаться от 1.

Практические примеры запросов с оценкой планов выполнения

Замеры ниже сделаны на стенде с PostgreSQL 16, 2 vCPU, shared_buffers 512 МБ и таблицей на 200 000 строк. Абсолютные значения у вас будут другими, важна разница между планами. Для чтения планов используйте EXPLAIN (ANALYZE, BUFFERS): он показывает фактические строки, попадания в кеш и время. Логика разбора планов похожа во всех СУБД, полезные приёмы собраны в материале про анализ планов и работу с индексами.

Пример 1: Поиск по тегам с GIN-индексом

CREATE TABLE articles (
    id    bigserial PRIMARY KEY,
    title text NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (title, tags)
SELECT 'article ' || g,
       ARRAY['tag' || (g % 5000), 'sql']
       || CASE WHEN g % 33 = 0 THEN ARRAY['postgresql'] ELSE '{}'::text[] END
FROM generate_series(1, 200000) AS g;

Запрос по одному тегу без индекса читает всю таблицу:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles WHERE tags @> ARRAY['postgresql'];

Seq Scan on articles  (cost=0.00..4560.00 rows=6061 width=27) (actual time=0.021..38.900 rows=6061 loops=1)
  Filter: (tags @> '{postgresql}'::text[])
  Rows Removed by Filter: 193939
  Buffers: shared hit=2417
Planning Time: 0.180 ms
Execution Time: 39.412 ms

После создания индекса тот же запрос идёт через bitmap:

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

Bitmap Heap Scan on articles  (cost=73.14..2021.55 rows=6061 width=27) (actual time=1.402..7.950 rows=6061 loops=1)
  Recheck Cond: (tags @> '{postgresql}'::text[])
  Heap Blocks: exact=2417
  ->  Bitmap Index Scan on idx_articles_tags  (cost=0.00..71.62 rows=6061 width=0) (actual time=1.021..1.021 rows=6061 loops=1)
        Index Cond: (tags @> '{postgresql}'::text[])
Planning Time: 0.240 ms
Execution Time: 8.101 ms

Время выполнения упало примерно в пять раз, а число прочитанных буферов осталось тем же: индекс отсеивает строки, но подходящие данные всё равно разбросаны по всей таблице. Recheck Cond в плане не ошибка: GIN возвращает приблизительный набор совпадений, и PostgreSQL перепроверяет условие по строке. Для проверки размера индекса выполните SELECT pg_size_pretty(pg_relation_size('idx_articles_tags')).

Пример 2: Агрегация данных с array_agg

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    customer_id integer NOT NULL,
    product     text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

SELECT customer_id,
       array_agg(product ORDER BY created_at) AS products,
       cardinality(array_agg(product))       AS items
FROM orders
WHERE created_at > now() - interval '30 days'
GROUP BY customer_id;

Результат для клиента с тремя заказами выглядит как {vps-4, backup, monitoring} в одной ячейке. Такой отчёт отдаётся приложению одним запросом, а порядок внутри массива задаёт ORDER BY внутри агрегата. Для подсчёта уникальных товаров применяйте array_agg(DISTINCT product), но помните, что DISTINCT и ORDER BY внутри агрегата требуют, чтобы выражение сортировки совпадало с агрегируемым.

Пример 3: Разворачивание массива и соединение с другой таблицей

CREATE TABLE categories (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE products (
    id           serial PRIMARY KEY,
    name         text NOT NULL,
    category_ids integer[] NOT NULL DEFAULT '{}'
);

SELECT p.name, c.name AS category
FROM products p
CROSS JOIN LATERAL unnest(p.category_ids) AS cid(id)
JOIN categories c ON c.id = cid.id
ORDER BY p.name, c.name;

Nested Loop  (cost=0.29..2841.00 rows=20000 width=40)
  ->  Function Scan on unnest cid  (cost=0.00..200.00 rows=10000 width=36)
  ->  Index Scan using categories_pkey on categories c  (cost=0.29..0.31 rows=1 width=20)
        Index Cond: (id = cid.id)

Function Scan разворачивает массивы, а соединение с справочником идёт по первичному ключу, поэтому стоимость вложенного цикла держится на уровне десятых долей миллисекунды на строку. Если убрать индекс по categories.id, план сменится на Hash Join с полным чтением справочника, что на больших объёмах заметно медленнее. Для проверки на своих данных меняйте состав индексов и смотрите на строку Rows Removed by Filter: рост этого числа означает, что индекс подобран неверно.

Стенд для таких замеров удобно разворачивать в облаке: управляемый PostgreSQL с нужной версией и возможностью включить расширения доступен, например, в Timeweb Cloud, где база поднимается рядом с серверами приложения.

Заключение: когда массивы оправданы

Массивы выигрывают в трёх сценариях: небольшой список читается вместе со строкой, поиск идёт по вхождению через операторы @>, <@, && и GIN-индекс, а данные нужно развернуть или собрать функциями unnest и array_agg. Они проигрывают там, где элементы правят поштучно, где нужна ссылочная целостность и где массив растёт до тысяч значений.

Перед переносом логики в массивы проверьте на своих данных три вещи: сколько строк вернёт типичный запрос с @> и какой план выберет планировщик, во сколько обходится обновление одного элемента на вашем профиле нагрузки, нет ли в колонке NULL и пустых массивов. Решения по схеме на этом этапе определяют стоимость сопровождения позже, о чём подробно говорится в разборе влияния проектирования базы на администрирование.

Если в приложении коллекции уже описаны через ORM, сверьте генерацию SQL с ручными запросами: ArrayField в Django, @ElementCollection в Hibernate и скалярные списки в Prisma ведут себя по-разному при ленивой загрузке и соединениях. Начните с одной колонки-массива, добавьте GIN-индекс и сравните план до и после: разница вроде 39 мс против 8 мс на тестовой таблице обычно видна сразу.