Brew решает развесить теги на напитки: coffee, hot, limited, classic. Возникает вечный вопрос моделирования: сложить теги массивом прямо в строку напитка (tags text[]) — или завести отдельную таблицу-связку «одна строка на пару (напиток, тег)»? Оба способа в Postgres первоклассны: у массивов есть свои операторы и GIN-индекс, а junction — это классическая нормализация. Выбор не про «правильно/неправильно», а про то, какие вопросы ты будешь задавать.

Цель юнита — пощупать обе модели на одних и тех же данных: операторы массива (@> «содержит», = ANY «принадлежит») против обычных join'ов и GROUP BY на junction. Увидим, где они эквивалентны, а где цена вопроса резко расходится.

Массив: @> и = ANY

Массив (text[]) хранит список прямо в колонке — одна строка на напиток, теги под рукой без join'а. Два рабочих оператора: @> («массив содержит подмассив») — tags @> ARRAY['coffee'] находит напитки с тегом coffee; и = ANY («значение принадлежит массиву») — 'cold' = ANY(tags) проверяет один тег. Оба ускоряет GIN-индекс по колонке (USING gin (tags), см. 06-05): на большой таблице это Bitmap Index Scan, а не Seq Scan. Массив идеален, когда теги — простые ярлыки: их немного, они без собственных атрибутов, и ты в основном спрашиваешь «есть ли тег X».

Junction: нормализация в строки

Таблица-связка drink_tags(drink_sku, tag) хранит то же самое строками — по одной на пару. Та же выборка «напитки с тегом coffee» — обычный WHERE tag = 'coffee', и результат совпадает с @> по массиву: по данным модели эквивалентны. Зато junction открывает то, чего массив не умеет: составной PRIMARY KEY (drink_sku, tag) гарантирует уникальность пары; на tag можно повесить внешний ключ к справочнику тегов (и БД не даст вписать опечатку); тегу легко добавить свои колонки (цвет, приоритет); а «сколько напитков у каждого тега» — это тривиальный GROUP BY tag. На массиве тот же подсчёт требует развернуть unnest(tags) и только потом группировать — лишний шаг.

Мост между моделями

Модели не враждуют — между ними есть мост. array_agg(tag ORDER BY tag) сворачивает строки junction обратно в массив (как в выдаче API), а unnest(tags) разворачивает массив в строки (чтобы посчитать или сджойнить). Поэтому нормальный приём — хранить нормализованно (junction), а отдавать массивом: запросы-аналитика идут по строкам, а наружу клиент получает компактный text[]/json.

Когда массив, когда junction

Обе модели хранят одно и то же; расходится цена разных вопросов:

Что показывает наш код

Одни и те же теги в двух столах: drink_tags_arr (массив + GIN) и drink_tags (junction). Пять запросов:

SELECT drink_sku FROM drink_tags_arr WHERE tags @> ARRAY['coffee'];   -- ArrayTaggedCoffee
SELECT drink_sku FROM drink_tags_arr WHERE $1::text = ANY(tags);      -- ArrayHasTag ('cold')
SELECT drink_sku FROM drink_tags     WHERE tag = 'coffee';            -- JunctionTaggedCoffee (тот же ответ)
SELECT tag, count(*) FROM drink_tags GROUP BY tag ORDER BY count(*) DESC, tag;          -- TagPopularity
SELECT drink_sku, array_agg(tag ORDER BY tag) FROM drink_tags GROUP BY drink_sku;       -- TagsFromJunction

sqlc типизирует элемент text[] как string (параметр $1 для = ANY), array_agg(...)::text[] — как []string, count(*)::bigint — как int64. Юнит добавляет свои столы → make db-reset накатывает их через brew.Apply.

Запуск

docker compose up -d
make lecture L=07-jsonb-arrays-and-search/07-04-arrays-vs-junction-table T=db-reset
make lecture L=07-jsonb-arrays-and-search/07-04-arrays-vs-junction-table

Вывод:

1) Массив text[] — операторы поиска:
   tags @> ARRAY['coffee']  → CAP-01, CLD-01, ESP-01
   'cold' = ANY(tags)       → CLD-01
 
2) Junction — те же напитки с тегом coffee (WHERE tag = 'coffee'):
   → CAP-01, CLD-01, ESP-01   (совпало с @> по массиву)
 
3) Частота тегов (GROUP BY на junction — тривиально):
ТЕГ      НАПИТКОВ
coffee   3
hot      3
classic  1
cold     1
limited  1
milk     1
tea      1
 
4) array_agg(tag ORDER BY tag) — junction свёрнут обратно в массив:
   CAP-01 = {coffee, hot, milk}
   CLD-01 = {coffee, cold, limited}
   ESP-01 = {classic, coffee, hot}
   TEA-01 = {hot, tea}

@> по массиву и WHERE tag = 'coffee' по junction дали один и тот же список (CAP/CLD/ESP) — данные эквивалентны. А вот «частота тегов» на junction — это один GROUP BY (coffee и hot по 3, остальные по 1), тогда как на массиве пришлось бы сначала unnest. array_agg показал обратный путь: junction свернулся в те же массивы, что лежат в drink_tags_arr.

Заборчик

Массив соблазняет компактностью — и мстит, когда тег перестаёт быть просто ярлыком. Сигналы выбора:

• тегу нужны свои атрибуты (когда повешен, кем, с каким весом), справочник с проверкой опечаток (FK) или регулярный счёт/джойн по тегам — бери junction;
• массив не даст ни внешнего ключа на элемент, ни уникальности внутри ({coffee, coffee} пройдёт), а аналитика по нему всегда идёт через unnest;
• обратная крайность тоже вредна: junction на простые неизменные ярлыки с единственным вопросом «есть ли тег» — лишний join на ровном месте.

Практичное правило: junction по умолчанию для всего, что имеет связи и атрибуты; массив — для коротких простых списков, где единственная операция — @>/= ANY. В проде «массив, по которому теперь джойним и считаем» мигрируют в junction — и DBA попросит сделать это раньше, чем он распухнет.

Что забрать с собой

• Массив text[]: операторы @> («содержит») и = ANY («принадлежит»), ускоряются GIN; компактно, без join'а.
• Junction (entity, value): нормализация в строки — FK на справочник, уникальность пары (PK), свои колонки тега, частота через GROUP BY.
• Модели эквивалентны по данным («напитки с coffee» совпали); расходится цена разных вопросов (частота тривиальна на junction).
• Мост: array_agg (строки → массив, для выдачи), unnest (массив → строки, для аналитики); приём «хранить нормализованно, отдавать массивом».

Дальше — 07-05 «Полнотекстовый поиск»: от точечных тегов и containment перейдём к поиску по словам внутри текста — tsvector/tsquery, ранжирование ts_rank, веса setweight и генерируемый tsvector-столбец с GIN на блоге и меню Brew.