Если массив читается целиком вместе с родительской строкой и меняется редко, денормализованное хранение в одной колонке (ARRAY или JSONB) убирает JOIN и ускоряет выборку. Если элементы меняются по отдельности, имеют собственные атрибуты или участвуют в фильтрах и сортировках, нормализация в отдельной таблице дешевле в поддержке и защищает от аномалий обновления. Практичный старт для нового проекта: нормализованная схема плюс кэш или материализованное представление для горячих читательских запросов.
Разберём обе схемы на одном примере, посчитаем компромиссы по чтению, записи и целостности, пройдём по сценариям с конкретными последствиями и соберём чек-лист, по которому решение принимается за один разговор с командой.
Нормализация и денормализация массивов: в чём принципиальная разница
Нормализация выносит элементы массива в отдельную таблицу и связывает их с родительской записью внешним ключом. Каждый элемент получает собственную строку, которую можно обновить, удалить или дополнить своими полями. Денормализация оставляет массив внутри одной колонки: типизированный ARRAY в PostgreSQL, документ JSONB, строка с разделителями или SET в MySQL. Родительская строка и её массив всегда лежат рядом физически.
Реляционная модель требует устранить избыточность: значение хранится в одном месте и не дублируется. Денормализация сознательно нарушает это правило ради выигрыша в скорости чтения или простоты кода приложения. Дублирование вводится осознанно, и это главный признак, по которому рабочий приём отличается от случайной ошибки проектирования.
Как выглядит нормализованная схема для массива
Для тегов статьи в реляционной СУБД нужны три таблицы: сами статьи, уникальный справочник тегов и связующая таблица для связи many-to-many.
| Таблица | Колонки | Ключи и ограничения |
|---|---|---|
| articles | id, title, created_at | PRIMARY KEY (id) |
| tags | id, name | PRIMARY KEY (id), UNIQUE (name) |
| article_tags | article_id, tag_id | PRIMARY KEY (article_id, tag_id), FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES tags(id) |
Если порядок элементов важен, а справочник не нужен, хватит двух таблиц: articles и article_tags с колонками (article_id, position, tag). Колонка position сохраняет порядок там, где он несёт смысл, например в списке шагов инструкции.
Составной первичный ключ (article_id, tag_id) не даёт привязать один тег к статье дважды. Внешний ключ с ON DELETE CASCADE удаляет связи при удалении статьи, и целостность держит СУБД, а не код приложения. Дополнительный индекс по (tag_id, article_id) ускоряет обратный поиск: все статьи с этим тегом.
Как выглядит денормализованное хранение массива
В PostgreSQL массив тегов удобно хранить в колонке типа ARRAY(text). В MySQL нативного массива нет, поэтому берут JSON, SET для фиксированного перечня или строку с разделителем. Строка таблицы articles приобретает вид: title = 'Настройка репликации', tags = ARRAY['postgresql', 'репликация', 'базы данных'].
Денормализация бывает частичной. Полная убирает нормализованные таблицы совсем. Частичная оставляет нормализованное хранение источником истины, а рядом держит колонку-кэш, счётчик или материализованное представление с готовым массивом. Второй вариант дешевле по последствиям: при ошибке в кэше данные всегда пересобираются из основной таблицы. Пример частичной денормализации: колонка tags_count в articles, которую поддерживает триггер, чтобы не считать COUNT при каждом открытии списка статей.
Ключевые компромиссы: скорость чтения, стоимость записи, целостность
Выбор между схемами сводится к трём осям. Каждая тянет решение в свою сторону, и выигрыш всегда идёт за счёт другого свойства.
| Ось | Нормализация | Денормализация |
|---|---|---|
| Чтение массива целиком | JOIN двух-трёх таблиц, результат зависит от индексов и кэша | Одно чтение колонки из строки родителя |
| Изменение одного элемента | INSERT или DELETE одной короткой строки | UPDATE всей строки и запись целого массива в WAL |
| Поиск по элементу | JOIN с индексом по тегу | Требует GIN-индекса, без него полное сканирование |
| Целостность | Внешние ключи, уникальные индексы, каскады | Проверки в приложении, риск дублей и рассинхрона |
| Изменение структуры элемента | ALTER TABLE дочерней таблицы | Перезапись всех значений в колонке |
| Аналитика по элементам | GROUP BY по связующей таблице, индексы работают | Разворот массива функцией, обход индексов |
Когда чтение становится узким местом
Сценарии, где денормализация реально ускоряет чтение: лента новостей с тегами, карточка товара с характеристиками, профиль пользователя с интересами, публичная страница статьи в базе знаний. Во всех случаях массив нужен целиком вместе с родительской строкой, а фильтрация по элементам либо редкая, либо закрыта отдельным индексом.
Денормализованное чтение стоит одного обращения к странице heap. JOIN собирает строку из двух таблиц, и на больших объёмах к стоимости добавляются случайные чтения страниц дочерней таблицы и работа планировщика с гистограммами. При 10 миллионах связей и трёх тегах на статью JOIN обычно упирается в буферный кэш, а не в CPU. Когда рабочий набор помещается в shared_buffers, разница между планами сокращается до единиц процентов, и преимущество массива исчезает.
Поиск по элементам без JOIN даёт GIN-индекс. В PostgreSQL условие tags @> ARRAY['postgresql'] использует его напрямую, и это единственный способ сохранить денормализацию при частой фильтрации. Без индекса такой поиск читает все строки таблицы, а на миллионе статей разница измеряется секундами.
Когда запись и обновления страдают
Изменение одного элемента массива в колонке означает UPDATE всей строки: PostgreSQL пишет новую версию кортежа, старую помечает мёртвой, генерирует WAL на весь массив и при размере больше 2 КБ выносит значение в TOAST-таблицу отдельными чтениями и записями. Стоимость растёт линейно от размера массива, а не от числа изменённых элементов.
В нормализованной схеме добавление тега это INSERT одной строки в 30-40 байт в article_tags, удаление это DELETE по составному ключу. Родительская строка не перезаписывается, autovacuum разбирается с небольшим объёмом мусора.
Пример: у статьи 1000 тегов, приходит ещё один. Денормализованная схема читает колонку, пересобирает массив в памяти приложения и пишет его целиком, меняя одну позицию из тысячи. Нормализованная вставляет одну короткую строку.
При высокой конкурентности денормализация приводит к потерянным обновлениям. Два процесса читают массив, каждый добавляет свой тег, второй UPDATE затирает результат первого. Защита через SELECT FOR UPDATE или оптимистичную блокировку по версии строки снижает параллелизм и съедает выигрыш от простой схемы.
Целостность данных и аномалии
Нормализация перекладывает контроль на СУБД: внешние ключи не дают сослаться на удалённый тег, уникальный индекс по name не даёт завести дубль в справочнике, составной ключ не даёт дважды привязать тег к статье, каскадное удаление не оставляет висячих связей. Всё это работает и при записи через psql, и через ORM, и при ручной правке данных.
Денормализация переносит проверки в приложение. В JSONB легко записать дубликат тега, если не добавить валидацию, или рассинхронизировать колонку-кэш с нормализованным источником. Чаще всего встречаются три аномалии: вставки (дубли элементов в массиве), удаления (тег лежит в массиве, а в справочнике его уже нет) и обновления (тег переименовали, массив хранит старое имя).
Денормализация выдерживает проверку временем, когда данные меняются редко и читаются часто. Как только появляется поток частых одиночных правок, поддержка согласованности в коде начинает стоить дороже, чем JOIN.
Сложность запросов и миграций: что важнее в долгосрочной перспективе
Схема определяет, сколько кода придётся писать и насколько дорого менять данные через год. Разница проявляется в типовых задачах: выборка, фильтрация, агрегация, разворот массива в строки.
Типовые SQL-запросы для обоих подходов
| Задача | Нормализация | Денормализация |
|---|---|---|
| Статья с тегами | SELECT с JOIN articles, article_tags, tags и GROUP BY a.id, array_agg(t.name) | SELECT id, title, tags FROM articles WHERE id = 42 |
| Поиск статей по тегу postgresql | JOIN article_tags и tags, WHERE t.name = 'postgresql' | WHERE tags @> ARRAY['postgresql'] с GIN-индексом |
| Число тегов у статьи | SELECT count(*) FROM article_tags WHERE article_id = 42 | array_length(tags, 1) для ARRAY, jsonb_array_length(tags) для JSONB |
| Статьи с любым из двух тегов | JOIN и WHERE t.name IN ('postgresql', 'mysql') | WHERE tags && ARRAY['postgresql', 'mysql'] |
| Разворот массива в строки | Обычный SELECT из article_tags | SELECT unnest(tags) FROM articles |
| Топ-10 популярных тегов | SELECT tag_id, count(*) FROM article_tags GROUP BY tag_id ORDER BY 2 DESC | Разворот массива с последующей группировкой |
Запросы на нормализованной схеме многословнее, зато переносятся между СУБД и оптимизируются планировщиком предсказуемо. Денормализованные версии короче, но привязаны к конкретной СУБД: операторы @>, && и unnest работают в PostgreSQL, а в MySQL синтаксис JSON_CONTAINS и JSON_TABLE другой.
Аналитика это слабое место денормализации. Развернуть массив в строки для GROUP BY можно, но такая операция обходит индексы и заставляет читать всю колонку. На нормализованной схеме тот же отчёт строится одним проходом по связующей таблице с индексом, а при больших объёмах под отчёты выделяется реплика только для чтения.
Миграции и изменение структуры
Добавление атрибута к элементу массива в нормализованной схеме это ALTER TABLE article_tags ADD COLUMN weight int и последующее заполнение. Таблица блокируется на короткое время. В PostgreSQL 11 и новее колонку с значением по умолчанию добавляют без перезаписи таблицы, в более старых версиях операцию выносят в окно обслуживания.
В денормализованной колонке новый атрибут означает перезапись всех значений: каждый документ JSONB нужно прочитать, изменить и записать обратно. На таблице в сотни гигабайт это часы работы, рост WAL, раздувание файлов и риск упереться в размер диска. Приходится идти батчами по первичному ключу и следить за отставанием реплик.
Смена формата внутри колонки, например переход от строки с разделителями к JSONB, добавляет поле-версию. Без него приложение не понимает, что лежит в старой строке, и каждый запрос требует ветвления по типу содержимого. Поле schema_version внутри JSONB или отдельная колонка-маркер снимает часть боли, но код чтения остаётся с двумя ветками, пока миграция не завершена.
Практические сценарии: когда денормализация оправдана, а когда ведёт к аномалиям
Ниже четыре типовых случая с конкретными последствиями выбора. По ним удобно проверять собственный проект: совпадение с описанным профилем нагрузки почти всегда ведёт к тому же решению.
Пример: теги статей в базе знаний
Сервис базы знаний: 50 тысяч статей, 800 тегов, читателей в десятки раз больше, чем авторов. Теги меняются редко, поиск по ним частый. Здесь работает денормализация в ARRAY(text) плюс GIN-индекс: выборка статей по тегу идёт условием tags @> ARRAY['postgresql'], карточка статьи отдаёт массив без JOIN.
Обратная картина при переименовании тегов. Если редакторы регулярно объединяют и переименовывают теги, нормализованный справочник позволяет сделать UPDATE одной строки в tags и не трогать статьи. В денормализованной схеме переименование превращается в массовую перезапись массивов, а любая забытая строка остаётся со старым именем. Рабочий компромисс: справочник тегов держать нормализованно, а готовый массив для чтения собирать в материализованном представлении и обновлять по расписанию.
Пример: список товаров в заказе
Заказ это позиции с ценой, количеством и скидкой на момент покупки. Денормализация в JSONB удобна для отображения: один SELECT возвращает весь заказ. Проблемы начинаются при изменении количества или удалении позиции: нужно перезаписать весь документ, а при параллельной правке двумя операторами возникает расхождение.
Нормализованная схема orders плюс order_items позволяет менять одну позицию точечно, считать сумму запросом SELECT sum(price * qty) и строить отчёты по проданным товарам без разворота JSON. Цена и название товара внутри order_items дублируют справочник сознательно: это снимок на момент покупки, без которого история заказов искажается при изменении каталога. Нормализация здесь основа, а денормализация допустима только внутри нормализованной позиции.
Пример: права доступа пользователя
Массив ролей у пользователя. Если набор ролей фиксирован (admin, editor, viewer), денормализация в ARRAY(text) или JSONB удобна: проверка доступа выполняется условием 'admin' = ANY(roles) или roles @> ARRAY['admin'], без JOIN и лишних таблиц.
Как только роли становятся динамическими, появляются требования вида «выдать права на проект», «роль действует до даты», «аудит изменений прав». Массив строк такое не выражает, и его заменяет таблица user_roles с полями user_id, role_id, granted_at, expires_at. Типичная аномалия денормализованного подхода: роль удалили из справочника, а в массивах пользователей строка осталась, и доступ сохраняется.
Где денормализация не оправдана
Корзина интернет-магазина с частым добавлением и удалением товаров: каждое действие перезаписывает весь массив, а при конкурентных правках теряются позиции. Список участников чата при высокой активности: одновременно пишут десятки клиентов, оптимистичная блокировка по версии строки приводит к постоянным конфликтам и ретраям. Финансовые транзакции: нужна атомарность на уровне отдельной операции, а не всего документа. Журнал событий с требованиями к аудиту: правки задним числом в денормализованной колонке невозможно отследить без отдельной таблицы истории.
Чек-лист для выбора схемы на этапе проектирования
Список вопросов рассчитан на обсуждение с командой до создания таблиц. Ответы удобно выписать в две колонки: что поддерживает массив, что поддерживает отдельную таблицу.
Вопросы, которые нужно задать до выбора схемы
- Какой профиль нагрузки: чтение или запись? Если чтений в десятки раз больше, денормализация даёт выигрыш; при сопоставимых объёмах он пропадает.
- Как часто меняются отдельные элементы? Редкие правки (раз в день и реже) терпят массив; частые одиночные правки требуют отдельной таблицы.
- Какой ожидаемый размер массива? До 10-20 элементов колонка работает стабильно, сотни и тысячи элементов раздувают строку, TOAST и WAL.
- Нужен ли поиск по элементам? Если да, оцените GIN-индекс для ARRAY или JSONB; если фильтров много и они разнородные, нормализация проще.
- Есть ли у элемента собственные атрибуты? Дата добавления, автор, вес, комментарий это признак сущности, а не элемента списка, и ведёт к нормализации.
- Нужна ли ссылочная целостность на уровне СУБД? Внешние ключи, уникальные индексы и каскадные удаления доступны только нормализованной схеме.
- Требуется ли аудит изменений? История правок естественно ложится на отдельную таблицу с timestamp и автором.
- Будет ли аналитика по элементам? Отчёты вида «топ тегов» или «выручка по товару» дешевле на нормализованных данных.
- Планируется ли репликация и кэширование? Одну денормализованную таблицу проще реплицировать целиком, а пересобрать кэш из нормализованного источника проще.
- Как часто меняется структура массива? Добавление поля к элементу в дочерней таблице дешевле перезаписи всех документов в колонке.
Как принять решение: алгоритм из трёх шагов
- Если элемент массива это сущность с собственными атрибутами, связями или историей, выбирайте нормализацию: отдельная таблица, внешний ключ, составной первичный ключ.
- Если массив это набор однотипных значений, который читается целиком и меняется редко, рассмотрите денормализацию: ARRAY(text), JSONB или SET, плюс GIN-индекс, если нужен поиск по элементам.
- Если сомневаетесь, начинайте с нормализации и добавьте материализованное представление или кэш для чтения. Пересобрать кэш из источника всегда можно, восстановить потерянную целостность в денормализованной колонке почти никогда.
Пример применения: теги статей в базе знаний. Элемент это просто имя тега без своих атрибутов, массив читается вместе со статьёй, меняется редко, поиск по тегу нужен. Алгоритм даёт денормализацию с GIN-индексом. Как только появляется требование «показывать дату, когда тег добавлен», пункт про собственные атрибуты переводит решение на связующую таблицу.
Решения на этапе проектирования определяют, насколько сложными окажутся мониторинг, бэкапы и масштабирование после релиза. Связь между схемой и эксплуатацией разобрана в статье как проектирование базы данных влияет на администрирование.
Технические особенности хранения массивов в PostgreSQL и других СУБД
ARRAY vs JSONB в PostgreSQL
ARRAY хранит однотипные значения компактно: заголовок с длиной и элементами фиксированного типа, без повторов ключей. Операции с массивами (ANY, @>, &&, array_length, unnest) выполняются напрямую. Ограничение одно: все элементы одного типа. Для тегов, ID и ролей это удобно.
JSONB хранит документ с ключами, поддерживает вложенность и разнородные поля. Цена: на каждый ключ приходятся служебные данные, а валидацию структуры выполняет приложение. Для тегов вида ['postgresql', 'бэкапы'] разницы в удобстве нет, для метаданных вида cpu, ram_gb и вложенного списка дисков JSONB остаётся единственным разумным вариантом.
Поиск по элементам в обоих типах требует GIN-индекса. Для ARRAY применяется класс операторов array_ops, для JSONB jsonb_path_ops или jsonb_ops. Без индекса условие @> превращается в полное сканирование таблицы: на миллионе строк это разница между миллисекундами и десятками секунд.
Ограничения MySQL и других СУБД
В MySQL нативных массивов нет. Есть JSON с ограниченной индексацией: индексировать отдельный элемент можно только через генерируемую колонку и индекс на неё. SET подходит для фиксированного перечня значений с лимитом в 64 элемента и хранит битовую маску, что быстро для поиска, но плохо переносится при изменении справочника.
SQLite обходится без массивов и моделирует их строкой с разделителем, отдельной таблицей или JSON1-функциями. MongoDB хранит массивы нативно и умеет индексировать элементы multikey-индексом, но это документная модель со своими правилами целостности и без внешних ключей между коллекциями.
Практический вывод: если СУБД не поддерживает массивы эффективно, нормализация перестаёт быть более сложным вариантом и становится основным. Как выбрать СУБД под конкретную задачу, разобрано в материале какую СУБД выбрать под хранение данных.
Влияние на производительность и масштабирование
Как размер массива влияет на выбор
До 10-20 элементов на строку денормализованная колонка ведёт себя предсказуемо: строка помещается в одну страницу, UPDATE дешевле лишнего JOIN. Дальше начинается деградация. Строка PostgreSQL размером больше 2 КБ уходит в TOAST, и чтение массива требует дополнительных обращений к внешней таблице. При 5000 ID в JSONB одна строка занимает десятки килобайт, а UPDATE пишет их целиком.
Нормализация растёт иначе: число строк в article_tags увеличивается, но размер каждой остаётся в десятки байт, индексы держат рабочий набор в памяти, вставки идут последовательно по ключу. Для коллекций в тысячи элементов нормализация или вынос поиска в отдельный движок даёт устойчивый результат. Если массив всё же растёт, ограничивайте его размер на уровне приложения: лимит в 20 тегов на статью защищает от случайного попадания тысяч значений в одну строку.
Репликация реагирует на схему по-разному. Денормализованная таблица реплицируется как есть, но каждая правка одного элемента передаёт в WAL весь массив, что увеличивает трафик между узлами и отставание реплик при частых изменениях. Нормализованные точечные изменения переносятся меньшим объёмом.
Если база уже растёт быстрее плана, перенос её на облачные ресурсы с запасом по IOPS снимает часть нагрузки до пересмотра схемы. Такую инфраструктуру предоставляет Timeweb Cloud: серверы, управляемые базы данных и хранилище с гибким изменением ресурсов.
Общие принципы работы с типами хранения и их влияние на нагрузку разобраны в статье хранение данных в 2026: виды, типы и способы организации.
Гибридные схемы: нормализация плюс материализованное представление
Компромисс, который снимает большую часть противоречий: данные хранятся нормализованно, а для чтения рядом живёт денормализованная копия. В PostgreSQL это материализованное представление, которое собирает статью вместе с массивом тегов одним запросом с array_agg. Чтение идёт по представлению, запись по нормализованным таблицам.
Обновление выполняется командой REFRESH MATERIALIZED VIEW CONCURRENTLY, которая не блокирует читателей, но требует уникального индекса на представлении и работает медленнее обычного REFRESH. Расписание зависит от требований к свежести: раз в минуту для ленты, раз в час для каталога. Если данные должны быть актуальны всегда, ту же роль играет кэш в Redis или Memcached с инвалидацией по ключу статьи.
Цена гибрида: два места, где живут данные, и обязательная процедура пересборки, которую нужно мониторить. Платить эту цену разумно там, где нормализованное чтение упирается в JOIN, а денормализованная запись уже показала аномалии. Стратегии инвалидации и синхронизация кэша с источником разобраны в руководстве по кешированию для DevOps и системных администраторов.
При дальнейшем росте нагрузки схема хранения определяет, какие паттерны масштабирования доступны: шардинг по article_id, реплики под отчёты, очереди на запись. Обзор паттернов с примерами собран в статье архитектура масштабирования для высоких нагрузок.
Заключение: как выбрать схему под свой проект
Начинайте с нормализации. Она даёт целостность на уровне СУБД, предсказуемые миграции и не мешает добавить денормализованное чтение позже. Денормализация ускоряет чтение и повышает цену записи, а согласованность массива приходится поддерживать вручную.
Ориентиры для решения. Массив до 20 однотипных элементов, редкие изменения, чтение целиком, поиск по GIN-индексу: денормализация уместна. Частые правки элементов, собственные атрибуты, аудит, аналитика, высокая конкурентность: нормализация. Сомневаетесь, выбирайте гибрид: нормализованное хранение как источник истины плюс материализованное представление или кэш для чтения.
Зафиксируйте выбор в документации схемы: какая таблица источник истины, как обновляется кэш, какой размер массива считается допустимым. Через год этот абзац сэкономит команде часы на разборе, почему колонка tags расходится со справочником.