Начать работу с массивами в PostgreSQL проще простого: объявили колонку как integer[], вставили значения — и готово.
Или вообще собрали массив на лету:
SELECT '{1,2,3}'::int[]; SELECT array[1,2,3];
int4 --------- {1,2,3} (1 row) array --------- {1,2,3} (1 row)
Официальная документация дает неплохую базу. Но за этим простым интерфейсом скрывается куда более сложная механика, чем многие привыкли думать. Массивы в PostgreSQL — это не просто «списки, которые можно засунуть в поле таблицы». У них своя стратегия работы с памятью, собственная логика индексации и целый ворох граничных случаев.
В статье подробно разберем аспекты работы с массивами, которые могут неожиданно создать проблемы в продакшене.
Искушение документной моделью данных
Стоп, речь пойдет о JSONB-массивах? Вовсе нет. Дело в том, что сама концепция массивов в реляционных СУБД — это, по сути, докум��нтное хранилище под прикрытием.
При проектировании баз данных принцип локальности ускоряет выборку за счет физической близости связанных данных. И неважно, что вы используете — нативный тип integer[] или JSON-список [1, 2, 3]. Вы принимаете одно и то же архитектурное решение: ставите локальность выше нормализации.
Сохраняя tag_ids в массиве, вы встраиваете связанные данные прямо в строку таблицы — точно так же, как NoSQL база встраивает вложенные документы. Само по себе это не ошибка. Документные БД появились не зря: никаких джойнов (JOIN), простое чтение, естественное соответствие объектам в коде.
Но PostgreSQL — это реляционная СУБД, построенная вокруг реляционной модели, где:
внешние ключи гарантируют ссылочную целостность;
джойны связывают нормализованные таблицы;
обновления затрагивают конкретные строки, а не списки целиком.
Используя массивы, вы получаете удобство документной модели, расплачиваясь реляционными гарантиями. Для элементов массива не работают ни внешние ключи, ни действия вроде ON DELETE CASCADE. Удалите запись из tags — и осиротевший ID так и останется в массиве навечно.
Существует основное правило: если всплыла потребность в ссылочной целостности — вам, скорее всего, нужна таблица-связка (link table), а не массив. Массивы хороши для данных, которые живут и умирают вместе с родительской строкой. Но не для связей, перекинутых между разными таблицами.
Например, связь «автор — пост в блоге» (один автор пишет кучу постов) — это типичная реляционная модель. А вот белый список IP-адресов для сервисного аккаунта — отличный кандидат на массив, так как он нужен только этой конкретной сущности.
Учитывая гибкость JSONB, резонно спросить: зачем вообще возиться с более капризными нативными типами? Ответ кроется в «скучной» стороне СУБД — предсказуемости и эффективности. Колонка integer[] гарантирует, что внутри лежат только целые числа, и ничего кроме.
Плюс массивы эффективнее расходуют место при хранении примитивов, потому что не тащат за собой оверхед метаданных, без которого не обходятся JSON-объекты.
Синтаксические ловушки
Будем считать, что основы по массивам вы и так знаете, так что базовую базу разбирать не будем.
Массивы не обязаны начинаться с единицы
По умолчанию индексация SQL-массивов начинается с 1. И, казалось бы, нет ничего плохого в том, чтобы итерироваться по ним вот так:
FOR i IN 1 .. array_length(fruits, 1) LOOP RAISE NOTICE 'Index % contains: %', i, fruits[i]; END LOOP;
Проблемы начинаются, когда встречается массив с произвольными границами — а PostgreSQL такое допускает:
SELECT '[-5:-3]={10,20,30}'::int[];
Чтобы не стрелять себе в ногу и правильно итерироваться по любому массиву, всегда используйте array_lower() и array_upper() в PL/pgSQL:
SELECT array_lower('[-5:-3]={10,20,30}'::int[], 1);
array_lower ------------- -5 (1 row)
Или generate_subscripts() в чистом SQL:
SELECT generate_subscripts('[-5:-3]={10,20,30}'::int[], 1);
generate_subscripts --------------------- -5 -4 -3 (3 rows)
Потерянные размерности
Создавая таблицу, вы наверняка ожидаете строгой типизации. И это верно для всего... кроме размерности массивов. Казалось бы, integer[][] должен обеспечить двумерную матрицу. Как бы не так. Синтаксис [] — это, по сути, синтаксический сахар. По умолчанию PostgreSQL вообще не контролирует размерность подмассивов на уровне схемы.
CREATE TABLE dimension_test ( matrix integer[][] ); INSERT INTO dimension_test VALUES ('{{1,2}, {3,4}}'); -- ошибки не будет INSERT INTO dimension_test VALUES ('{1,2,3}'); -- 3D-матрица тоже подойдет INSERT INTO dimension_test VALUES ('{{{1,2},{3,4}}, {{5,6},{7,8}}}');
Если хотите железно зафиксировать размерность массива, на определение типа полагаться нельзя. Нужно использовать ограничение CHECK:
CREATE TABLE strict_matrix ( -- dims: убеждаемся, что массив двумерный -- array_length: следим, чтобы размер был ровно 3x3 board integer[] CHECK (array_ndims(board) = 2 AND array_length(board, 1) = 3) );
INSERT INTO strict_matrix VALUES ( ARRAY[ [0, 1, 0], [1, 1, 0], [0, 0, 1] ] );
Единственное, за чем Postgres всё-таки следит, — это однородность массивов на каждом уровне вложенности. То есть вставить подмассивы разной длины он вам все-таки не даст.
INSERT INTO dimension_test VALUES ('{{1,2}, {3}}'); ERROR: malformed array literal: "{{1,2}, {3}}" LINE 1: INSERT INTO dimension_test VALUES ('{{1,2}, {3}}'); ^ DETAIL: Multidimensional arrays must have sub-arrays with matching dimensions.
Срезы массивов
При обращении к элементам массива важно помнить, что синтаксис [1] и [1:1] — это разные вещи. Первый обращается к элементу, второй работает как конструктор среза.
select matrix[1][1] from dimension_test; matrix -------- 1 (1 row)
При нарезке массива, даже если срез состоит из одного элемента, вернется одноэлементный массив, а не скалярное значение.
select matrix[1:1][1:1], matrix[1][1:1], matrix[1:1][1] from dimension_test ; matrix | matrix | matrix --------+--------+-------- {{1}} | {{1}} | {{1}} (1 row)
Самое гадкое
Доступ к элементам массива реализован слишком уж снисходительно по отношению к пользователю, что только мешает отлавливать баги:
-- выход за границы массива возвращает NULL SELECT (ARRAY[1,2,3])[10]; array -------- (null) (1 row)
-- срез за пределами массива возвращает пустой массив SELECT (ARRAY[1,2,3])[5:10]; array ------- {} (1 row)
Но самый взрывающий мозг момент, особенно для тех, кто пришел из других языков, — это то, что PostgreSQL воспринимает многомерные массивы как единую матрицу, а не как «массив массивов».
-- неверная размерность SELECT (ARRAY[[1,2],[3,4]])[1]; array -------- (null) (1 row)
По опыту работы с другими языками, вы ожидаете получить {1,2}, но Postgres так не умеет. Он пытается вытащить первую ячейку матрицы и падает (потому что индекс неполный).
И, перефразируя аналогию Флетчера про «double bubble» (с которой тоже всё пошло не по плану — бонусные баллы тем, кто понял отсылку), нотацией срезов это не лечится.
select ('{{1,2},{3,4}}'::int[])[1:1]; int4 --------- {{1,2}} (1 row)
Единственный способ разрулить эту головоломку — развернуть срез через unnest и собрать обратно через агрегацию.
SELECT array_agg(val) FROM unnest(('{{1,2},{3,4}}'::int[])[1:1]) val; array_agg ----------- {1,2} (1 row)
Только имейте в виду, что array_agg не гарантирует сохранение порядка, если вы явно не пропишете ORDER BY . С простыми запросами unnest это обычно прокатывает, но полагаться на неявную сортировку — так себе идея.
Альтернатива — прогнать туда-обратно через JSONB. Выглядит, конечно, как костыль, с какой стороны ни посмотри. Поэтому, если вам нужно работать со сложными многомерными структурами, где каждый подмассив имеет самостоятельное значение — не мучайтесь, берите JSONB. Он будет работать ровно так, как вы от него ожидаете.
Индексация массивов
Хотя на колонку с массивом технически можно повесить B-tree индекс, толку от него будет мало. Он спасет, только если вы ищете полное совпадение массивов (=) или сортируете их в лексикографическом (словарном) порядке.
-- что больше? Правильный ответ: B -- A: {1, 1000, 1000} -- B: {2, 0}
Поэтому для реальных операций по поиску внутри массива B-tree индекс бесполезен.
Для работы с ними вам нужен GIN (Generalized Inverted Index — обобщенный инвертированный индекс). Если B-tree — это телефонная книга, то GIN — это предметный указатель в конце этой книги. Чтобы найти один или несколько элементов, надо отыскать все возможные места их упоминания, а потом пересечь результаты.
CREATE INDEX posts_by_tags ON posts USING GIN (tags);
GIN заточен под операции с множествами: главное — наличие элемента, порядок не важен. Вот какие операторы GIN поддерживает для массивов:
Вхождение
@>— ищет строки, которые содержат ВСЕ указанные элементы.
-- содержит ВСЕ теги tags @> '{urgent, bug}'
Пересечение
&&— ищет строки, содержащие ХОТЯ БЫ ОДИН из элементов.
-- содержит bug ИЛИ feature tags && '{bug, feature}'
Есть ещё
<@и=(равенство), но с ними всё и так понятно.
Две стороны ANY
А вот тут начинается веселье. Вы наверняка часто слышали совет: «Не используйте динамический SQL для списков IN, используйте ANY». Совет правильный, но со своими нюансами.
Оператор ANY ведет себя радикально по-разному в зависимости от того, с какой стороны сравнения находится массив.
Совет использовать ANY работает, когда вы передаете списки в базу данных как параметры. Вместо того чтобы генерировать простыню из 100 параметров (WHERE id IN ($1, $2, ... $100)), которая раздувает кэш запросов и заставляет базу каждый раз делать жесткий парсинг (hard-parse), вы передаете один параметр-массив.
-- хорошо: один параметр, один план запроса SELECT * FROM users WHERE id = ANY($1::int[]);
Ловушка в том, чтобы полагать, что этот синтаксис так же эффективен при запросах к колонкам. Спойлер: нет. Если вы используете ANY для проверки наличия значения внутри колонки таблицы, вы фактически заставляете базу бежать в цикле.
-- плохо: GIN не поддерживает ANY; запрос свалится в Seq Scan SELECT * FROM tickets WHERE 'feature' = ANY(tags);
Когда вы пишете WHERE 'feature' = ANY(tags), вы не используете магии массивов. Вы применяете скалярный оператор равенства =, обернутый в цикл. А поскольку скалярный = не входит в класс операторов array_ops, планировщик понимает, что индекс тут не помощник, и сваливается в последовательное сканирование (Seq Scan).
Правильный способ переписать запрос:
-- снова хорошо SELECT * FROM tickets WHERE tags @> ARRAY['feature'];
Быстрые обновления и их цена
Поскольку GIN-индекс заточен под работу с множествами, его поддержка обходится дорого. В B-tree одна строка = одна запись в индексе. В GIN одна строка = N записей в индексе, где N — количество элементов в вашем массиве.
Это приводит к размножению записей. Чтобы нивелировать этот эффект, PostgreSQL по умолчанию использует механизм «быстрых обновлений» (fast update). Суть стратегии такая: новые записи сначала попадают в pending list (неотсортированный временный буфер) и сливаются в основную структуру индекса только потом, во время VACUUM.
Учитывая хаотичную природу GIN, это то самое исключение из принципа «VACUUM is a Lie» (доклад о том, что вакуум не освобождает место физически). В этом случае VACUUM действительно занимается структурным обслуживанием индекса.
С одной стороны, это делает INSERT терпимыми, но с другой — может тормозить SELECT. При каждом обращении к индексу Postgres вынужден сканировать и упорядоченный основной индекс, и весь тот беспорядок, что накопился в pending list. Если этот буфер разрастется, производительность запросов начинает ползти вниз.
Если у вас сценарий с интенсивным чтением и редкими записями (read-heavy), эту опцию лучше отключить, чтобы гарантировать стабильную скорость чтения.
CREATE INDEX posts_by_tags ON posts USING GIN (tags) WITH (fastupdate = off);
Хранение и модификация
Пора вернуться к документной модели. В PostgreSQL строки неизменяемы (спасибо MVCC) — никаких «обновлений на месте» не существует, а массивы хранятся как атомарные значения. Отсюда вытекает неприятный факт: чтобы изменить даже один-единственный элемент массива, PostgreSQL придется скопировать и переписать всю строку целиком.
UPDATE user_activity SET event_ids = event_ids || 10001 WHERE user_id = 50;
Каждое добавление элемента переписывает весь массив, что, по сути, означает перезапись всей строки таблицы.
TOAST в действии
Когда массив становится достаточно большим (> 2 КБ — см. ниже), PostgreSQL автоматически переносит его в отдельную область хранения через механизм TOAST. Это позволяет держать основную строку компактной, но обновления массивов при этом превращаются в существенную проблему для производительности.
Разница, казалось бы, незначительная, но последствия ощутимые. При обычном MVCC-обновлении PostgreSQL просто копирует версию строки в основной куче (heap). А вот обновление TOAST-массива заставляет его выкачать все внешние чанки, распаковать весь объект в память, применить изменение, снова сжать и записать новый полноразмерный блоб обратно в TOAST-таблицу. Простая модификация превращается в CPU- и I/O-затратную операцию, которая переписывает весь набор данных, а не только дельту.
Откуда взялась цифра 2 КБ? Порог TOAST рассчитывается так, чтобы на одну 8-килобайтную страницу кучи помещалось минимум четыре кортежа. Это определяется параметром TOAST_TUPLES_PER_PAGE (по умолчанию — 4). Так Postgres ищет баланс между эффективностью и накладными расходами на чтение вынесенных данных (TOAST indirection).
Проблема сжатия
До 14-й версии PostgreSQL полагался исключительно на pglz — алгоритм, который гнался за степенью сжатия в ущерб скорости. Из-за этого цикл «распаковать — изменить — снова сжать» при работе в TOAST превращался в сущую пытку.
В PostgreSQL 14 в качестве альтернативы был представлен LZ4:
ALTER TABLE articles ALTER COLUMN tags SET COMPRESSION lz4;
LZ4 работает заметно быстрее и на сжатие, и на распаковку, при этом уступает в плотности сжатия совсем незначительно. Поэтому, если вы имеете дело с большими массивами, переход на LZ4 — самый простой способ снизить нагрузку на процессор, которую создает TOAST.
Когда большие массивы всё-таки оправданы
У вас могло сложиться впечатление, что массивы — это зло и сплошные проблемы. Но при оценке целесообразности их использования главный вопрос не в том, какого размера массив, а в том, как часто вы его меняете. Массив из 10 000 элементов, который вы записали один раз и дальше используете только в режиме read-only — это абсолютно нормальный, рабочий сценарий. А вот массив всего на 50 элементов, в который вы дописываете данные при каждом входящем запросе — это уже настоящая головная боль.
А если совместить это со сжатием, картина станет ещё интереснее.
DROP TABLE IF EXISTS compression_test; CREATE TABLE compression_test ( id serial PRIMARY KEY, compressed_floats float4[], raw_floats float4[] ); -- не сжимать raw_floats ALTER TABLE compression_test ALTER COLUMN raw_floats SET STORAGE EXTERNAL; -- вставляем полуслучайные данные с низкой кардинальностью INSERT INTO compression_test (compressed_floats, raw_floats) SELECT semi_random_arr, semi_random_arr FROM ( SELECT ARRAY( SELECT floor(random() * 50)::float4 FROM generate_series(1, 10000) ) as semi_random_arr ) as generator; SELECT 'Compressed (EXTENDED)' as strategy, pg_size_pretty(pg_column_size(compressed_floats)::bigint) as size_on_disk FROM compression_test UNION ALL SELECT 'Raw (EXTERNAL)', pg_size_pretty(pg_column_size(raw_floats)::bigint) FROM compression_test;
strategy | size_on_disk -----------------------+-------------- Compressed (EXTENDED) | 15 kB Raw (EXTERNAL) | 39 kB (2 rows)
Массовая загрузка данных с использованием массивов
До сих пор могло показаться, что толку от массивов немного. Да, у них есть свои нюансы с хранением, зато для передачи данных они незаменимы.
Самый быстрый способ залить в базу 5000 строк — это не цикл в коде приложения и уж точно не гигантская строка VALUES (...), (...). Это unnest.
INSERT INTO measurements (sensor_id, value, captured_at) SELECT * FROM unnest( $1::int[], -- массив ID датчиков $2::float[], -- массив значений $3::timestamptz[] -- массив временных меток );
Всё что нужно — один сетевой round-trip и один запрос для парсинга и планирования. PostgreSQL самостоятельно разберет массивы на строки. Работает и для UPSERT, и для MERGE.
Специализированные массивы
Стандартные массивы PostgreSQL относятся к полиморфным типам (anyarray). Это мощная фича, дающая возможность одному определению функции работать с большим количеством разных типов данных. Массивы должны одинаково хорошо справляться с целыми числами, строками, временными метками и пользовательскими типами. Но если вы работаете с одним конкретным типом данных, использование специализированных расширений даст заметный выигрыш в производительности.
Расширение intarray
Если вы работаете исключительно с 4-байтовыми целыми числами (int4/integer), встроенные операции с массивами не используют весь потенциал производительности. Расширение intarray предоставляет специализированные функции и индексные операторы, которые работают значительно быстрее стандартной реализации массивов в PostgreSQL.
Перед использованием расширение нужно явно включить:
CREATE EXTENSION IF NOT EXISTS intarray;
Разница в удобстве для разработчика становится заметна сразу. Чтобы отсортировать массив в стандартном SQL, приходится разворачивать через unnest, сортировать и собирать обратно. С intarray у вас есть нативные функции вроде sort() и uniq().
-- стандартные массивы SELECT array_agg(val ORDER BY val) FROM unnest('{3, 1, 2}'::int[]) val; -- с расширением intarray SELECT sort('{3, 1, 2}'::int[]);
Помимо базовых функций управления, расширение вводит специализированный синтаксис запросов, упрощающий сложную булеву логику. Вместо того чтобы выстраивать цепочки из проверок на пересечение (&&) и вхождение (@>) можно выразить условия в одной «строке запроса», используя оператор @@.
-- стандартные массивы SELECT * FROM staff WHERE available_days @> '{1}' -- должен включать Пн AND available_days && '{6, 7}' -- должен включать Сб ИЛИ Вс AND NOT (available_days @> '{2}'); -- НЕ должен включать Вт -- с расширением intarray SELECT * FROM staff WHERE available_days @@ '1 & (6 | 7) & !2';
Единственный подвох — ограничение по типу. intarray жестко привязан к знаковым 32-битным целым числам. Если ваши значения переваливают за 2 миллиарда, использовать его уже не получится.
ИИ с pgvector
В 2026 году сложно говорить о массивах и не упомянуть pgvector. Хотя он и позиционируется как «векторное хранилище», по сути это просто массив чисел с плавающей точкой, заточенный под другую математику.
Стандартные массивы работают по бинарному принципу: важны точные совпадения (пересечение &&, вхождение @>). Векторы же заточены на «размытое» расстояние (косинусное <=>, евклидово <->).
Если вы строите поиск или рекомендательную систему, pgvector позволяет рассматривать колонку с массивом не как список «фактов» (тег A, тег B), а как координаты в семантическом пространстве.
Тем не менее, архитектурное решение здесь ровно то же самое, что и при использовании стандартного массива: вы жертвуете строгой структурой ради удобства. Поскольку невозможно «сджойнить» две строки на основе того, насколько они похожи, вектор хранится прямо в строке. Вы соглашаетесь на больший размер таблицы в обмен на возможность находить похожие записи.
