JSONB, массивы и когда side table побеждает

Команда добавляет колонку «metadata» JSONB для «случайных дополнительных полей». Через три года в колонке 30 полей, к которым обращается каждый запрос — ни одного индекса, ни одного ограничения, все парсятся при каждом чтении. Схема выросла именно в то, чего пытались избежать.

JSONB vs JSON: всегда JSONB

У Postgres два типа JSON-хранения. JSON хранит текст дословно — парсится заново при каждом чтении, не индексируемый, немного меньше. JSONB хранит разобранную бинарную структуру — индексируемый, запрашиваемый через path-операторы, немного больше. В продакшне всегда JSONB. Единственная причина использовать JSON — сохранение порядка ключей или дублирующихся ключей, чего легитимные схемы никогда не делают.

Когда JSONB является правильным выбором

JSONB хорошо подходит для трёх форм данных:

1. Действительно гетерогенные данные. Логи событий, где каждый тип события имеет разную форму payload. Ответы сторонних API, где схему контролирует кто-то другой. Объекты конфигурации, где ключи различаются у каждого tenant.

2. Метаданные «длинного хвоста». Таблица products, где 80% продуктов имеют 10 общих колонок и 20% имеют 50 дополнительных полей специфических для поставщика. 10 колонок типизированы; хвост из 50 полей — JSONB.

3. Схема первична, запросы редки. Данные, которые вы храните, но редко запрашиваете по полям — метаданные загрузчика файлов, пользовательские настройки на строку.

Неверная форма: любое поле, по которому вы GROUP BY, JOIN, агрегируете, принудительно обеспечиваете уникальность или ссылаетесь через foreign key. Эти поля должны быть типизированными колонками.

Когда side table побеждает

Типизированные массивы (TEXT[], INTEGER[]) и JSONB проигрывают side table когда:

• Нужно запрашивать «все строки с тегом X» в масштабе — GIN индекс помогает, но join-таблица с B-tree индексом по (tag_id, row_id) быстрее и позволяет принудительно соблюдать FK integrity.
• Нужно глобально переименовать тег X — одно UPDATE в таблице тегов vs сканирование каждой строки с тегом.
• Нужно считать строки по тегу или соединять теги с другой таблицей — SQL агрегация по join-таблице на порядок дешевле GIN-indexed JSONB.
• Нужна уникальность на строку (нет дублирующихся тегов в одном элементе) — легко принудительно соблюдается составным PK join-таблицы; невозможно внутри массива без constraint function.

Правило решения: если нужно только читать «теги этой строки», колонка-массив нормальна. В момент, когда запрос идёт со стороны тега, используйте side table.

Стратегии JSONB-индексов

У JSONB два семейства индексов. Выбор неправильного делает запросы в 10-100 раз медленнее.

GIN (Generalized Inverted Index). Индексирует каждый ключ или путь внутри JSONB. Класс операторов по умолчанию индексирует каждый ключ; jsonb_path_ops индексирует целые пути (быстрее для запросов @> containment, больше индекс). Поддерживает операторы @>, ?, ?|, ?&.

-- GIN по умолчанию: поддерживает ?, ?|, ?& и @>
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- jsonb_path_ops: только @>, но быстрее для него
CREATE INDEX idx_events_payload_paths ON events USING GIN (payload jsonb_path_ops);

Expression B-tree. Индексирует один конкретный путь, извлечённый как типизированное значение. Поддерживает равенство и range-запросы по этому пути. Намного меньше GIN.

-- Индексирует поле user_id как текст
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

-- Индексирует user_id как integer
CREATE INDEX idx_events_user_id_int ON events (((payload->>'user_id')::BIGINT));

Используйте GIN для «содержит ли этот JSONB ключ/значение X?». Используйте expression B-tree для «найти строки где data.field равно конкретному значению».

Generated STORED колонки

Postgres поддерживает generated-колонки, значение которых вычисляется из других колонок при записи и хранится:

ALTER TABLE order_items
  ADD COLUMN line_total_cents INTEGER
  GENERATED ALWAYS AS (unit_price_cents * quantity) STORED;

Колонка запрашиваемая, индексируемая и обновляется автоматически при каждой записи. В отличие от триггера, вычисление объявлено в схеме и видимо любому читателю без знания о триггере.

Сценарии использования: производные значения, которые часто запрашиваются (full_name, line_total, is_final из enum статуса), аудит-флаги, вычисленная денормализация. Цена: записи немного медленнее (выражение вычисляется при каждой записи); миграции на generated-колонках могут вызвать перезапись таблицы.

FK при масштабе: паттерн PlanetScale

Некоторые гиперскейл-компании (PlanetScale на Vitess, несколько крупных Postgres-инсталляций) рекомендуют отключать foreign keys. Конкретные условия, где это обосновано:

• Данные шардированы и связь пересекает границы шардов — FK не могут охватывать шарды.
• Каскад создаст транзакцию на несколько миллионов строк, удерживающую блокировки минутами.
• Проход проверки FK при DDL-изменении типа колонки является операционным узким местом.

Ни одно из этих условий не применимо к типичной SaaS-схеме ниже ~100M строк в таблице. Для большинства команд FK-ограничение стоит ~5-50 мкс на строку при записи и навсегда отказывает всем строкам-сиротам. Его отключение перемещает гарантию integrity в код приложения, где она реализуется непоследовательно и ломается при рефакторингах.

Опытные инженеры воспринимают «мы отключили FK» как сигнал конкретных ограничений масштаба — не как общую лучшую практику.