Хранение массивов в базах данных: нормализация или вложенные массивы

Нормализация или вложенные массивы: что выбрать для вашей задачи

Короткий ответ: элементы, которые меняются по одному и требуют ссылочной целостности, храните в отдельной таблице со связью один ко многим. Массив, который читается целиком, обновляется редко и укладывается в сотни элементов, держите внутри строки: text[] или jsonb в PostgreSQL, embedded array в MongoDB.

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

Разница проявляется в трёх местах: индексы (B-tree по внешнему ключу против GIN или multikey по массиву), стоимость записи (одна строка против целой версии строки или документа) и целостность (внешние ключи против проверок в приложении). Ниже критерии выбора, таблица сравнения и быстрые правила.

Ключевые критерии выбора

• Частота обновлений. UPDATE строки в таблице-связке переписывает одну небольшую строку. UPDATE элемента массива создаёт новую версию всей строки, а когда массив вынесен в TOAST, перезаписывается и внешнее значение.
• Паттерн чтения. Если массив нужен только целиком вместе с родителем, вложенная структура экономит JOIN и второй индексный проход. Если искать надо по отдельным элементам, нормализация даёт обычный B-tree, а массивам нужен GIN или multikey-индекс, который дороже в обновлении и крупнее по размеру.
• Целостность. Внешние ключи, UNIQUE и CHECK на каждый элемент доступны только в нормализованной схеме. Внутри массива или документа ссылок между элементами нет: список тегов спокойно примет несуществующий тег.
• Размер массива. До десятков элементов колонка работает предсказуемо. Сотни и тысячи элементов приводят к выносу значения в TOAST, росту GIN-индекса или приближению к лимиту документа 16 МБ в MongoDB.
• Смена схемы. Новый атрибут элемента в дочерней таблице - это ALTER TABLE плюс миграция данных. В jsonb и документах MongoDB поле добавляется новым записям, а старые документы приходится обновлять скриптом.

Сравнительная таблица подходов

Пять способов хранения массивов в реляционных СУБД, включая сериализацию и денормализованные колонки, с готовыми запросами и типичными ошибками миграции мы собрали в обзоре как хранить массивы в реляционной базе данных.

Быстрые правила.

• Элементы меняются ежедневно и поштучно - нормализация.
• Массив читается целиком, меняется раз в месяц - вложенный массив.
• Нужны отчёты и соединения по элементам - нормализация.
• Нужен поиск по вхождению элемента и по атрибутам - jsonb с GIN или text[] с GIN.
• Документ уже собирается целиком в приложении и живёт в MongoDB - embedded array с multikey-индексом.

Нормализация: связь один ко многим на практике

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

CREATE TABLE users (
    id     bigserial PRIMARY KEY,
    email  text NOT NULL UNIQUE
);

CREATE TABLE roles (
    id   smallint PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE user_roles (
    user_id    bigint   NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id    smallint NOT NULL REFERENCES roles(id),
    granted_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, role_id)
);

CREATE INDEX idx_user_roles_role ON user_roles (role_id);

Первичный ключ из двух колонок запрещает дубликаты ролей у одного пользователя, внешние ключи не дают появиться ссылке на несуществующую роль, ON DELETE CASCADE убирает связки вместе с пользователем.

Пример схемы и запросов

Чтение пользователя вместе с ролями собирается через JOIN и array_agg. Порядок задавайте явно, иначе роли вернутся в произвольной последовательности.

SELECT u.email, array_agg(r.name ORDER BY r.name) AS roles
FROM users u
LEFT JOIN user_roles ur ON ur.user_id = u.id
LEFT JOIN roles r       ON r.id = ur.role_id
WHERE u.id = 42
GROUP BY u.id, u.email;

Добавление и удаление элемента - одна инструкция:

INSERT INTO user_roles (user_id, role_id) VALUES (42, 3)
ON CONFLICT DO NOTHING;

DELETE FROM user_roles WHERE user_id = 42 AND role_id = 3;

Обратный поиск «все пользователи с ролью admin» уходит в индекс idx_user_roles_role и остаётся обычным B-tree scan. Для связи один ко многим индекс по внешнему ключу в дочерней таблице нужен обязательно: без него каждое чтение ролей пользователя превращается в seq scan по всей связке.

Плюсы и минусы нормализации

• Ссылочная целостность. FK, UNIQUE, NOT NULL и CHECK работают на уровне отдельного элемента, а не всего списка.
• Точечные обновления. Десять изменений - это десять небольших UPDATE вместо десяти перезаписей целого массива или документа.
• Гибкие выборки. Соединения с другими таблицами, агрегации, оконные функции и пагинация по элементам доступны без разбора JSON.
• Предсказуемый размер строки. Таблица не растёт из-за одного значения и не трогает TOAST.

• JOIN в каждом чтении. ORM легко скатываются в N+1, когда роли запрашиваются в цикле.
• Больше строк и ключей. На миллионах элементов связка весит больше, чем массив в колонке.
• Транзакции и блокировки. Обновление родителя и связки требует транзакции; при разном порядке захвата строк появляются взаимные блокировки.
• Жёсткая схема. Новый атрибут элемента - это ALTER TABLE и заполнение исторических данных.

Нормализация обязательна для часто меняющихся списков: роли и права доступа, подписки, участники проекта, позиции заказа. Если у элемента есть собственные атрибуты (кто и когда выдал роль), в text[] их хранить уже неудобно, а в jsonb придётся вручную поддерживать согласованность.

Вложенные массивы в PostgreSQL: array и JSONB

PostgreSQL предлагает два способа держать набор значений в одной колонке: нативный массив с фиксированным типом элемента и jsonb с произвольной структурой.

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

Добавить и убрать тег можно одной инструкцией, без чтения строки в приложение:

UPDATE articles SET tags = array_append(tags, 'postgresql') WHERE id = 7;
UPDATE articles SET tags = array_remove(tags, 'mysql')       WHERE id = 7;
UPDATE articles SET data = jsonb_set(data, '{tags}', (data->'tags') || '"k8s"'::jsonb)
WHERE id = 7;

array_append дубликаты не проверяет. Либо добавляйте CHECK с условием на уникальность элементов, либо пишите выражение вида CASE WHEN tags @> ARRAY['k8s'] THEN tags ELSE tags || 'k8s' END. Ограничений на элементы у массива нет: нельзя объявить, что тег обязан существовать в справочной таблице.

Массив требует одного типа для всех элементов и обычно компактнее jsonb с теми же данными: jsonb хранит разобранное представление и тратит время на разбор при чтении. Зато jsonb допускает вложенные объекты, разные типы в одном массиве и частичное обновление через jsonb_set. Больше операторов, функций и приёмов разбирали в руководстве по массивам в PostgreSQL: синтаксис, операции, индексы и примеры запросов.

Индексация массивов: GIN и другие индексы

CREATE INDEX idx_articles_tags ON articles USING gin (tags);
CREATE INDEX idx_articles_data ON articles USING gin (data jsonb_path_ops);

GIN поддерживает операторы @> (содержит), <@ (содержится в), && (пересечение) и = для массивов; для jsonb добавляются ?, ?|, ?&, @? и @@. Запросы, которые используют такой индекс:

SELECT id, title FROM articles WHERE tags @> ARRAY['postgresql'];
SELECT id, title FROM articles WHERE tags && ARRAY['k8s', 'docker'];
SELECT id, title FROM articles WHERE data @> '{"tags": ["k8s"]}'::jsonb;

Класс операторов выбирают по задаче. jsonb_ops - класс по умолчанию для jsonb: он поддерживает операторы проверки существования ключа, включая ?, а также @> и другие, но индекс получается крупнее. jsonb_path_ops не поддерживает операторы проверки существования ключей, зато поддерживает @>, @? и @@ и обычно заметно компактнее индекса jsonb_ops по тем же данным, а специфичность поиска выше, поэтому поисковые операции обычно выполняются быстрее. Поиск вида data -> 'tags' ? 'k8s' с jsonb_path_ops индекс не использует: этот класс хранит хеши путей, а не сами ключи, и не может ответить на вопрос «есть ли в документе ключ X». Для такого условия нужен jsonb_ops; с jsonb_path_ops работает, например, data @> '{"brand": "Apple"}'.

B-tree по колонке-массиву тоже возможен, но сравнивает массив целиком: равенство и сортировка работают, поиск по отдельному элементу - нет. Для запроса «статьи с тегом X, отсортированные по дате» планировщик чаще выберет Bitmap Heap Scan по GIN с последующей сортировкой; если сортировка критична, добавьте отдельный B-tree по created_at.

Цена GIN в записи. Обновление GIN-индекса может быть медленным из-за вероятности вставки множества ключей для каждого элемента, поэтому при массовой загрузке в таблицу рекомендуется удалить GIN-индекс и пересоздать его после завершения загрузки. Индекс поддерживает технику отложенной вставки (fast update): новые записи сначала помещаются в pending list, а затем переносятся в основную структуру данных массовой вставкой, что значительно ускоряет обновление индекса. Список ожидающих записей очищается, когда его размер превышает gin_pending_list_limit. Индекс не покрывает запрос: он отдаёт ctid, а страницу из heap всё равно приходится читать.

Подробнее о классах операторов и технике fast update — в официальной документации PostgreSQL: GIN Indexes и JSON Types.

Размер строк и TOAST

TOAST срабатывает, когда строка становится «слишком широкой»; порог по умолчанию - 2 КБ. Сначала механизм пытается сжать широкие значения полей, а если это не помогает уложить строку под 2 КБ, разбивает значения на части, которые хранятся в связанной TOAST-таблице. Отделённые значения делятся на порции (после сжатия, если оно применяется) размером не более TOAST_MAX_CHUNK_SIZE байт; по умолчанию значение выбирается так, чтобы на странице помещались четыре строки порций, то есть порядка 2000 байт.

Сжатие настраивается: помимо pglz (по умолчанию) в PostgreSQL 14 появилась LZ4-компрессия TOAST. Метод задаётся опцией столбца COMPRESSION или параметром default_toast_compression; чтобы использовать lz4, сборка должна быть выполнена с --with-lz4.

Пример: массив из 1000 тегов по 20 байт - это около 20 КБ данных. Значение почти наверняка уедет в TOAST и будет сжато, а обновление одного тега перезапишет весь TOAST-набор, увеличив объём WAL. Запрос, который не обращается к колонке tags, TOAST не читает, но SELECT * или выборка самого массива потянет дополнительные страницы.

Практический вывод: держите массивы в пределах сотен элементов. Если список растёт до тысяч значений, переносите его в дочернюю таблицу. Для небольших полей, которые нужны почти в каждой выборке, можно задать ALTER TABLE articles ALTER COLUMN tags SET STORAGE MAIN, но это осмысленно только когда значение помещается в страницу.

Описание механизма TOAST и настройки сжатия — в документации: хранение TOAST и Configurable LZ4 TOAST compression.

Вложенные документы в MongoDB

В MongoDB массив - штатная часть документа BSON, отдельного типа для него не нужно.

{
  "_id": 42,
  "email": "dev@example.com",
  "roles": ["admin", "user"],
  "tags": [
    { "name": "k8s",   "weight": 3 },
    { "name": "nginx", "weight": 1 }
  ]
}

Поиск по элементу массива выглядит коротко: db.users.find({ roles: "admin" }). Для условий по нескольким полям одного элемента нужен $elemMatch, иначе части условия могут выполниться на разных элементах массива.

db.articles.find({ tags: { $elemMatch: { name: "k8s", weight: { $gt: 2 } } } });

Обновление атомарно на уровне документа: $addToSet не создаёт дубликатов, $push добавляет, $pull удаляет, позиционный оператор $ меняет конкретный элемент.

db.users.updateOne(
  { _id: 42 },
  { $addToSet: { roles: "auditor" } }
);

db.articles.updateOne(
  { _id: 1, "tags.name": "k8s" },
  { $set: { "tags.$.weight": 5 } }
);

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

Официальные ограничения MongoDB описаны в MongoDB Limits and Thresholds.

Индексы в MongoDB для массивов

Индекс по полю-массиву становится multikey автоматически: для каждого элемента создаётся отдельная запись индекса.

db.users.createIndex({ roles: 1 });
db.articles.createIndex({ "tags.name": 1, "tags.weight": 1 });

Ограничение: составной индекс может содержать только одно поле-массив, иначе вы получите ошибку о parallel arrays. Размер multikey-индекса растёт пропорционально числу элементов, поэтому массив на тысячи значений резко увеличивает и индекс, и стоимость записи. Для покрытого запроса проекция должна включать только поля из индекса.

Аномалии UPDATE и целостность данных

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

Конкурентные обновления: примеры и решения

Сценарий: два процесса одновременно добавляют теги к одной статье. Процесс A читает tags = {postgresql}, процесс B читает те же данные. A записывает {postgresql, k8s}, B записывает {postgresql, mongodb}. Тег k8s потерян.

• Нормализация. A и B делают INSERT в таблицу-связку. Обе строки сохранятся, дубликаты отсечёт первичный ключ. Потери записи нет.
• PostgreSQL. Одна инструкция UPDATE articles SET tags = array_append(tags, 'k8s') WHERE id = 7 безопасна: выражение считается на актуальной версии строки под блокировкой. Опасна схема из двух запросов - сначала SELECT, потом UPDATE. Для сложной логики берите SELECT ... FOR UPDATE в транзакции.
• MongoDB. $addToSet и $push атомарны на уровне документа, конфликтов нет. Если значение считают в приложении, нужна оптимистичная блокировка по версии или транзакция с несколькими документами; по официальной документации такие транзакции требуют replica set.

ACID сам по себе от логических аномалий не защищает. Транзакция гарантирует атомарность записи, но не спасает от разнесённого по двум запросам «прочитал, посчитал, записал». Тот же риск есть для jsonb, для любой денормализованной колонки и для документов MongoDB.

В MySQL сценарий похож: JSON-колонка перезаписывается целиком, а поиск по элементам требует multi-valued индекса. Как это устроено, разбирали в материале про хранение массивов в MySQL: JSON, сериализация и нормализация.

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

Примеры для PostgreSQL

Нормализованное чтение одного пользователя с ролями:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, r.name
FROM users u
JOIN user_roles ur ON ur.user_id = u.id
JOIN roles r       ON r.id = ur.role_id
WHERE u.id = 42;

План такой выборки - вложенный цикл из Index Scan по первичным ключам users и по составному ключу user_roles, затем Index Scan по roles_pkey. Число прочитанных строк растёт вместе с числом ролей пользователя, а не вместе с размером таблицы. Запрос «все пользователи с ролью admin» идёт через idx_user_roles_role.

Поиск по массиву и типичный план с GIN:

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

Bitmap Heap Scan on articles
  Recheck Cond: (tags @> '{postgresql}'::text[])
  Rows Removed by Index Recheck: 0
  ->  Bitmap Index Scan on idx_articles_tags
        Index Cond: (tags @> '{postgresql}'::text[])

Строка Rows Removed by Index Recheck показывает перепроверку: если совпадений много, bitmap становится lossy и каждая страница проверяется по условию заново. Когда эта величина сравнима с числом найденных строк, посмотрите на длину массива и подумайте о нормализации.

Поиск по jsonb идёт через jsonb_path_ops тем же способом:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles WHERE data @> '{"tags": ["k8s"]}'::jsonb;

Seq Scan на большой таблице в этом месте означает, что индекс не выбран: либо устарела статистика, либо условие написано так, что GIN неприменим, например tags @> ARRAY[$1] без приведения параметра к ::text[]. После массовой загрузки прогоняйте ANALYZE: планировщик опирается на статистику, а по элементам массивов она приблизительная.

Примеры для MongoDB

Запрос и его разбор:

db.users.find({ roles: "admin" }).explain("executionStats");

С индексом { roles: 1 } план содержит стадию IXSCAN с границами indexBounds по значению, затем FETCH. Без индекса будет COLLSCAN и чтение всей коллекции. В executionStats смотрите три величины: totalKeysExamined (сколько записей индекса просмотрено), totalDocsExamined (сколько документов прочитано), nReturned (сколько отдано). Для multikey-индекса nReturned обычно меньше totalKeysExamined: если у документа два подходящих элемента массива, ключей больше, чем документов.

db.users.find({ roles: "admin" }, { _id: 0, roles: 1 }).explain("executionStats");

Проекция без _id и только по полю из индекса позволяет обойтись без стадии FETCH. Для вложенных объектов применяйте $elemMatch, чтобы условия относились к одному элементу, и держите составной индекс по полям этого объекта.

Когда денормализация оправдана, а когда усложняет сопровождение

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

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

Отдельный приём - гибрид: канонические данные лежат в дочерней таблице, а для поиска поддерживается денормализованная копия (колонка text[] с GIN-индексом, обновляемая триггером, или материализованное представление). Вы получаете целостность на записи и быстрый поиск по элементам на чтении, платя за это синхронизацией копии и дополнительным местом.

Если вы склоняетесь к одной колонке, но не хотите jsonb или нативного массива, есть третий путь: сериализация в JSON, BLOB, MessagePack или Protobuf. Сравнение форматов по размеру и скорости, а также риски десериализации недоверенных данных разбирали в статье про сериализацию массивов в базу данных: JSON, BLOB и бинарные форматы.

Компромиссы по скорости чтения, стоимости записи и сложности миграций мы разбирали отдельно в статье про нормализацию и денормализацию при хранении массивов: там же практические сценарии и готовый чек-лист для проектирования.

Чек-лист для выбора подхода

1. Посчитайте типичный и максимальный размер списка. До 20 элементов колонка почти всегда удобнее; от сотни элементов считайте стоимость TOAST, GIN-индекса или лимита документа.
2. Оцените частоту точечных обновлений. Если элементы меняются ежедневно, выбирайте дочернюю таблицу: там каждое изменение переписывает одну строку.
3. Проверьте требования к целостности. Нужны FK, UNIQUE и CHECK на элемент - только нормализация.
4. Определите паттерн чтения. Массив всегда нужен целиком - вложенная структура экономит JOIN; ищите по элементам - готовьте GIN или multikey.
5. Решите, будут ли отчёты и соединения по элементам. Если да, список лучше разложить в таблицу.
6. Спроектируйте индексы заранее и проверьте EXPLAIN (ANALYZE, BUFFERS) на реальном объёме данных, а в MongoDB - explain("executionStats").
7. Проговорите конкурентные обновления: одна атомарная инструкция (array_append, $addToSet) или транзакция с SELECT ... FOR UPDATE.

Соберите два прототипа схемы на копии реальных данных и сравните план запроса, размер индекса и объём WAL на серии обновлений. Эти три числа снимают вопрос о нормализации быстрее любых рассуждений.