Generated Columns + JSONB: как навести порядок в гибкой схеме PostgreSQL

Вы добавляете в таблицу поле payload jsonb — и жизнь становится проще. События, логи, метаданные, ответы внешних API — всё летит в один столбец без жёсткой структуры. А через месяц коллега просит: «Сделай выборку по полю event_type». Вы пишете запрос с payload->>'type', и он работает. Через полгода таких полей уже десяток, а фильтрации тормозят. Вы вешаете GIN-индекс, но хочется более привычного btree, да и читать payload->>'status' повсюду надоедает. Тогда на сцену выходят генерируемые столбцы.

Почему JSONB — не полноценная схема

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

Когда вы фильтруете по вложенному ключу, PostgreSQL читает JSONB-поле из TOAST или из основной таблицы, достаёт значение через оператор ->> и только потом сравнивает. Если таких запросов много и они идут по разным ключам, рано или поздно вы замечаете, что база перестала справляться. Хранить в JSONB — удобно, запрашивать «как попало» — накладно.

Управляемая semi‑structured схема как раз об этом: оставить гибкость там, где она нужна, но для горячих ключей «вытащить» предсказуемую структуру наружу — так, чтобы оптимизатор видел её как родную.

Что такое generated column и как он помогает

Генерируемый столбец — это столбец, значение которого всегда вычисляется на основе других столбцов той же строки. В отличие от виртуальных вычислений на уровне приложения, база делает это сама и только в момент вставки или обновления (для типа STORED). Выглядит как обычный столбец, но вставить в него значение напрямую нельзя.

Базовый синтаксис для стабильных версий PostgreSQL (12–17) и российского форка Postgres Pro — только STORED:

column_name type GENERATED ALWAYS AS (expression) STORED

После записи строка хранится в таблице физически, занимает место и может быть проиндексирована btree. В версии PostgreSQL 12–17 слово STORED обязательно, потому что виртуальных generated columns в upstream не было. Начиная с PostgreSQL 18 появилась поддержка VIRTUAL generated columns, которые не хранятся на диске и вычисляются при каждом чтении. Свежее нововведение делает тему ещё интереснее, но на момент написания основной массив продакшен-систем работает именно с хранимыми столбцами.

Ключевое ограничение: выражение может использовать только immutable‑функции. Никаких random(), now() и обращений к другим строкам. Генерируемый столбец не может ссылаться на другой generated column, и его нельзя сделать частью ключа секционирования.

Связка JSONB + Generated Columns: «вытягиваем» порядок из документа

Представьте таблицу событий, куда сыплются разнородные JSON-документы. На старте она выглядит так:

CREATE TABLE events (
    id bigserial primary key,
    payload jsonb,
    created_at timestamptz default now()
);

Через несколько месяцев вы точно знаете, что у каждого события есть ключ type со строковым значением, и по нему постоянно фильтруют. Вместо того чтобы каждый раз писать WHERE payload->>'type' = 'purchase', можно добавить генерируемый столбец:

ALTER TABLE events
ADD COLUMN event_type text
GENERATED ALWAYS AS (payload->>'type') STORED;

PostgreSQL пробежит по всем существующим строкам, вычислит значения и зафиксирует их. Дальше при вставках и обновлениях payload столбец будет пересчитываться автоматически. Теперь запрос выглядит привычно:

SELECT * FROM events
WHERE event_type = 'purchase'
ORDER BY created_at;

Читаемость растёт, оптимизатор видит обычную колонку и может строить планы на основе статистики. Дальше на event_type можно спокойно вешать btree-индекс, который для операций равенства и диапазонов часто эффективнее GIN.

Аналогично стоит поступать с числовыми и временными ключами: вытянуть price как numeric, event_time как timestamptz, если дата лежит в JSON отдельным полем. Приведение типа делается прямо в выражении:

ALTER TABLE events
ADD COLUMN order_amount numeric
GENERATED ALWAYS AS ((payload->>'amount')::numeric) STORED;

Так один столбец jsonb постепенно окружается «отвердевшими» ключами, которые живут по законам реляционной схемы.

Управление слабоструктурированной схемой на практике

Ключевая идея подхода — не пытаться вытащить из JSONB вообще всё, а добавлять generated columns только для тех полей, которые стали по‑настоящему горячими.

Как это выглядит в жизни

1. Начинаете с чистого JSONB. Первое время всех всё устраивает, а запросов немного.
2. Смотрите, какие ключи чаще всего попадают в WHERE и ORDER BY. Запросы к логам или событиям почти всегда фильтруются по типу события, идентификатору пользователя или временной метке.
3. Добавляете под них generated columns через ALTER TABLE. На живом инстансе такая миграция может занять время и заблокировать запись, поэтому стоит планировать её на окно, либо использовать инструменты типа pg_repack или делать через создание новой таблицы и переключение.
4. Создаёте btree-индексы по новым столбцам. Теперь WHERE user_id = 123 работает как с обычной колонкой.
5. В запросах используете имена столбцов, а не операторы извлечения из JSONB. Код становится чище, а планы запросов — предсказуемее.

Такой подход не отменяет GIN: он остаётся для полнотекстовых поисков или проверок существования ключей (?, @>), которые по‑прежнему живут в jsonb. Но для точных фильтраций по конкретным полям btree на generated column обычно удобнее.

Когда останавливаться

Не стоит вытаскивать все ключи подряд. Если у вас сто разных ключей, которые встречаются в 1% документов, сгенерированные столбцы превратятся в мёртвый груз: каждая строка станет занимать больше места, а пользы от индексов будет мало. Правило простое: структурируем только то, что реально участвует в выборках. Semi‑structured схема на то и semi‑structured, чтобы оставить пространство для нерегулярных данных.

Производительность: что говорят факты, а чего мы не знаем

Подход с generated columns даёт три очевидные выгоды на чтение:

• Меньше данных для фильтрации: при чтении btree-индекса по event_type базе не нужно заходить в TOAST-таблицу и вытаскивать весь JSONB, чтобы достать значение одного ключа.
• Более качественная статистика: для обычных столбцов планировщик собирает гистограммы, самые частые значения и т. д. Для сырого выражения (payload->>'type') статистика либо отсутствует, либо менее точна.
• Привычный инструментарий индексов: btree-индексы легковесны и понятны, упорядоченные данные хорошо подходят для ORDER BY и диапазонных запросов.

Но есть и важные нюансы. Сгенерированный столбец типа STORED занимает место в основной таблице — теперь у вас рядом лежит и сам payload, и его копия в виде отдельного поля. Если JSONB-документ крупный и хранится в TOAST, а generated column маленький, таблица всё равно растёт за счёт нового столбца. При большом количестве таких столбцов общий размер может вырасти значительно.

Чего мы не знаем без собственных бенчмарков:

• На сколько конкретно ускоряется запрос на вашем наборе данных и при вашей нагрузке.
• Как соотносятся по скорости GIN по JSONB и btree по сгенерированному столбцу для конкретных типов запросов.
• Как поведёт себя VIRTUAL‑столбец в PostgreSQL 18 в сравнении с STORED или обычным индексом на выражении.

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

Ограничения и неочевидные грабли

Самые частые сюрпризы, с которыми сталкиваются те, кто впервые пробует generated columns в связке с JSONB:

• Нельзя присвоить значение напрямую. В INSERT или UPDATE вы можете указать DEFAULT, но никак не конкретное число. Если очень хочется «обмануть» систему и вставить своё, придётся обновлять исходный JSONB‑столбец. Это логично, но новичков часто раздражает.
• Нельзя ссылаться на другой generated column. Выражение должно использовать только исходные столбцы, поэтому цепочки вроде «из JSONB вытащили A, на основе A создали B» не сработают.
• Изменение выражения требует перестроения таблицы. Если вы решите, что event_type должен теперь вычисляться как payload->>'eventType', а не payload->>'type', вы не сможете просто поменять выражение. Придётся удалить столбец и создать заново — а это повлечёт полную перезапись всех строк.
• Только immutable‑функции. На выражениях вроде lower() или ::timestamp это работает, а вот now() или функции, зависящие от локали, — нет.
• Не участвует в ключах секционирования. Не получится разбить таблицу на партиции по сгенерированному столбцу. Хотя это ограничение редко бывает проблемой для сценариев с JSONB.
• STORED хранит данные, поэтому у таблицы растёт размер. В PostgreSQL 18 с VIRTUAL‑столбцами эта проблема частично уходит, но зато каждый запрос будет перевычислять значение, что тоже не бесплатно.

Когда этот подход оправдан, а когда лучше выбрать нормализацию

Generated columns + JSONB — это прекрасный компромисс, когда схема ещё не устоялась, но запросы уже предъявляют требования к производительности. Типичные ситуации:

• События и логи. Разные источники присылают похожие, но не идентичные JSON‑документы. У всех есть timestamp, event_type, user_id, но остальные поля могут различаться. Выносим общие ключи в generated columns, остальное оставляем в JSONB.
• Агрегация данных из внешних API. Структура ответа может поменяться в любой момент. Вы сохраняете всё в jsonb, а по мере необходимости добавляете generated columns для фильтрации или сортировки.
• Слабоструктурированные метаданные товаров или контента. Разные категории обладают разным набором атрибутов. Вы делаете универсальную таблицу с attributes jsonb и структурируете только то, что нужно для поиска и каталога.

Но если вы ловите себя на мысли, что для каждого ключа в JSONB уже создан generated column, а новых непредсказуемых полей давно не появлялось — вероятно, схема стабилизировалась. Тогда честная нормализация с отдельными столбцами или вынос редко используемых атрибутов в отдельные EAV‑подобные таблицы даст лучший баланс между гибкостью и производительностью. JSONB при этом можно оставить как архив исходных данных или для обратной совместимости.

FAQ

Чем generated column по JSONB‑ключу лучше обычного индекса на выражении (payload->>'key')?

Индекс на выражении тоже работает и не занимает лишнее место в строке. Но generated column даёт больше: вы получаете именованный столбец в списке \d, можете явно ссылаться на него в запросах без повторения громоздкого выражения, а главное — планировщик собирает отдельную статистику по этому столбцу. Индекс на выражении статистику по «виртуальному» ключу не хранит, что может ухудшить планы.

Можно ли напрямую вставить значение в generated column при INSERT?

Нет. Можно только указать DEFAULT, что равносильно вычислению из исходных данных. Любая другая попытка вставить конкретное значение приведёт к ошибке. Единственный способ повлиять на значение — обновить исходный JSONB‑столбец.

Как изменить выражение уже существующего generated column?

Напрямую — никак. Придётся удалить столбец (ALTER TABLE ... DROP COLUMN) и добавить новый с новым выражением. Важно помнить, что удаление и повторное создание вызовут перезапись всей таблицы, что на больших объёмах требует тщательного планирования.

Появились ли в PostgreSQL виртуальные (VIRTUAL) generated columns, и когда их ждать?

В PostgreSQL 18, который стал доступен в 2025 году, действительно появилась поддержка VIRTUAL‑столбцов. В этой версии VIRTUAL стало значением по умолчанию для GENERATED. Более старые стабильные версии (12–17) поддерживают только STORED. Если вы работаете с облачными провайдерами, уточняйте доступную мажорную версию.

Что выбрать — STORED или VIRTUAL — если важна экономия места?

VIRTUAL‑столбец вообще не занимает места в таблице и вычисляется на лету при чтении. Это экономит дисковое пространство, но добавляет процессорные затраты при каждом сканировании. STORED, наоборот, хранит вычисленное значение и может быстрее фильтровать ценой дополнительного места. Универсального ответа нет — тестируйте на своём наборе данных. Многие предпочтут VIRTUAL для редко читаемых ключей и STORED для тех, по которым строится основная выборка.

Насколько вырастет размер таблицы при добавлении нескольких generated columns?

Каждый STORED столбец добавляет к строке количество байт, примерно равное его типу данных плюс служебные заголовки (обычно несколько байт выравнивания). Для трёх–пяти столбцов с типами text, numeric или timestamptz прирост размера будет заметен, но редко становится критичным на фоне самого JSONB. О точных цифрах можно судить только по pg_total_relation_size после добавления столбцов на копии данных.

Есть ли ограничения на тип генерируемого столбца?

Можно использовать любой обычный тип данных PostgreSQL: text, integer, numeric, boolean, jsonb, массивы и т.д. Главное — чтобы результат выражения соответствовал заявленному типу. Вы не можете создать generated column с типом, который не соответствует выходу выражения (например, пытаться записать результат ->>'key' в integer без явного приведения).

Вывод

Generated columns в связке с JSONB — это способ не отказываться от удобства schema‑less там, где оно действительно нужно, но при этом навести порядок с горячими ключами. Вы начинаете со свободных документов, а по мере роста запросов вытаскиваете из них чёткие колонки, по которым строите индексы и собираете статистику. Такой подход избавляет от необходимости сразу проектировать идеальную схему и даёт время понять, что в данных действительно важно.

Не пытайтесь структурировать всё подряд — оставьте JSONB для нерегулярных или редко используемых полей. И не забывайте, что каждый добавленный generated column — это компромисс между скоростью чтения и объёмом хранимых данных. Проверяйте решения на своих объёмах и следите за новыми версиями: с появлением VIRTUAL‑столбцов баланс снова сдвинется, и управление полуструктурированной схемой станет ещё гибче.

Источники

• Documentation: 18: 5.4. Generated Columns
• Documentation: 18: 29.6. Generated Column Replication
• Postgres JSONB Best Practice
• JSONB Is Not a Schema: When To Reach For It in Postgres, And When To Stop
• Generated column
• Postgres JSONB Columns and TOAST: A Performance Guide