PostgreSQL jsonb: операторы, GIN-индексы и ограничения

Используйте jsonb, а не json, и только для данных, чья форма действительно меняется: payload вебхуков, настройки отдельных клиентов, атрибуты товаров, которые различаются по категориям. Достаёте поле как текст через ->>, проверяете, содержит ли документ нужный фрагмент, через @>, а индекс строите как GIN с jsonb_path_ops, если в основном пользуетесь @>. Остальная часть руководства - это детали, от которых зависит, хорошо ли это решение переживёт время, и случаи, когда обычная колонка оказалась бы лучше.

Предупреждение в двух словах: jsonb - хороший второй инструмент и плохой первый. Всё, по чему вы фильтруете, делаете join, сортируете или что должно быть уникальным, должно лежать в настоящей колонке. Колонка с документом нужна для длинного хвоста полей, которые вы читаете целиком и редко ищете.

json и jsonb: в чём разница#

В PostgreSQL два типа JSON, и ведут они себя по-разному. json хранит точный текст, который вы передали, и разбирает его заново при каждом обращении. jsonb разбирает один раз, хранит разложенную двоичную форму и поддерживает индексы. Почти во всех случаях вам нужен jsonb.

Чем вы платите за jsonb:

• Пробелы и порядок ключей не сохраняются. {"b": 1, "a": 2} вернётся как {"a": 2, "b": 1}: ключи упорядочены сначала по длине, затем по алфавиту.
• Повторяющиеся ключи схлопываются. Если ключ встречается дважды, побеждает последнее значение.
• Вставка чуть медленнее из-за преобразования.

Выбирать json стоит только тогда, когда нужен исходный текст байт в байт (журнал аудита с точным содержимым того, что прислала третья сторона) или когда значение вообще не читается. Но и тогда сырое тело чаще понятнее хранить в колонке text: так яснее намерение.

Отдельно о числах. jsonb хранит числа как PostgreSQL numeric, поэтому большие целые и десятичные дроби сохраняются без потерь - лучше, чем справляется JavaScript. Подвох на выходе: клиент, который разбирает результат в double, теряет точность выше 2^53, и это вина клиента, а не базы.

Колонка объявляется как любая другая:

CREATE TABLE app.events (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  tenant_id   bigint      NOT NULL,  kind        text        NOT NULL,  created_at  timestamptz NOT NULL DEFAULT now(),  payload     jsonb       NOT NULL DEFAULT '{}');INSERT INTO app.events (tenant_id, kind, payload) VALUES  (7, 'signup',  '{"plan":"pro","source":"ad","tags":["beta","eu"],"user":{"id":41,"email":"a@example.com"}}'),  (7, 'invoice', '{"total":19.90,"currency":"USD","paid":false}');

Обратите внимание, как устроена таблица: tenant_id, kind и created_at - колонки, потому что их использует каждый запрос, а в payload лежит только переменная часть. Весь дизайн держится на этом разделении.

Операторы на каждый день#

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

Стрелка с одним остриём оставляет значение в виде jsonb, так что цепочку можно продолжать. Стрелка с двумя остриями отдаёт обычный text: он нужен в конце цепочки и в условии WHERE. Целое число справа выбирает элемент массива, отсчёт идёт с нуля, а отрицательное считает с конца.

SELECT payload -> 'user' ->> 'email'      AS email,       payload -> 'tags' ->> 0            AS first_tag,       (payload #>> '{user,id}')::bigint  AS user_idFROM app.eventsWHERE kind = 'signup';

Два поведения ставят в тупик. Во-первых, отсутствующий ключ даёт SQL NULL, а не ошибку: это удобно, но скрывает опечатки - payload ->> 'plna' молча ничего не вернёт. Во-вторых, JSON null и отсутствующий ключ - разные вещи. '{"a": null}'::jsonb -> 'a' возвращает значение jsonb null, а это не SQL NULL; ->> на нём возвращает SQL NULL. Если ваша логика различает эти случаи, проверяйте оператором ? из таблицы ниже.

В PostgreSQL 14 появились подскрипты, поэтому работает и payload['user']['email'], и в UPDATE это читается естественнее. Результат - jsonb, как у ->.

Операторы проверки

Они возвращают boolean и используются в WHERE.

Включение (containment) стоит выучить как следует, потому что индексы построены вокруг этого оператора:

-- Events whose payload includes this fragmentSELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';-- Nested: the fragment must have the same structureSELECT id FROM app.events WHERE payload @> '{"user":{"id":41}}';-- Arrays: contains the element, in any positionSELECT id FROM app.events WHERE payload @> '{"tags":["beta"]}';-- A key exists, whatever its valueSELECT id FROM app.events WHERE payload ? 'currency';

Включение сравнивает структуру, а не текст. Фрагмент {"tags":["beta"]} подойдёт документу с ["eu","beta","x"], потому что порядок массива и лишние элементы не важны. Но он не подойдёт документу, где "beta" вложен на уровень глубже. А оператор ? смотрит только на ключи верхнего уровня: email внутри user он не найдёт. Знак вопроса может также конфликтовать с плейсхолдерами параметров в некоторых драйверах - известный случай это JDBC-драйвер для Java; тогда выручают функции jsonb_exists, jsonb_exists_any и jsonb_exists_all.

Изменение документа#

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

-- Add or overwrite top-level keys (right side wins)UPDATE app.events SET payload = payload || '{"paid":true}' WHERE id = 2;-- Set a nested path; the last argument creates the key if it is missingUPDATE app.eventsSET payload = jsonb_set(payload, '{user,email}', '"new@example.com"', true)WHERE id = 1;-- Remove a key, or a pathUPDATE app.events SET payload = payload - 'source' WHERE id = 1;UPDATE app.events SET payload = payload #- '{user,email}' WHERE id = 1;

Новое значение в jsonb_set само должно быть корректным jsonb, поэтому в строке выше два слоя кавычек: внутренние делают её JSON-строкой. Это самая частая ошибка с данной функцией. || работает поверхностно - он заменяет вложенный объект целиком, а не сливает с ним, - поэтому для глубокого слияния нужны jsonb_set или код приложения.

Документы собирают из строк через jsonb_build_object и jsonb_agg, а разбирают обратно функциями, возвращающими наборы строк:

-- One row per tagSELECT e.id, t.tagFROM app.events e,     jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)WHERE e.kind = 'signup';-- One row per top-level keySELECT key, value FROM app.events e, jsonb_each(e.payload) WHERE e.id = 2;-- Build a document from a querySELECT jsonb_build_object('tenant', tenant_id, 'events', jsonb_agg(kind))FROM app.events GROUP BY tenant_id;

Для более сложной структуры jsonpath (jsonb_path_query, @?, @@) позволяет записать фильтр вроде $.items[*] ? (@.qty > 5) одним выражением. Это мощная вещь, и знать о ней стоит, но читать её могут только те, кто с ней работал, поэтому берите её, когда цепочки -> становятся громоздкими, а не по умолчанию. В PostgreSQL 17 добавили и стандартный для SQL JSON_TABLE, который превращает документ в строки одной конструкцией; проверьте версию сервера, прежде чем на него полагаться.

Индексирование jsonb через GIN#

Без индекса каждый фильтр payload @> ... читает всю таблицу. GIN-индекс (generalised inverted) решает это, индексируя ключи и значения внутри документа - так же, как предметный указатель в книге сопоставляет слова и страницы. Если нужна общая картина, PostgreSQL indexes: types, order and cost показывает, где GIN находится среди типов индексов.

Есть два класса операторов, и выбор имеет значение:

-- Default: also answers key-existence queriesCREATE INDEX events_payload_gin ON app.events USING gin (payload);-- Smaller and quicker, containment and jsonpath onlyCREATE INDEX events_payload_path ON app.events USING gin (payload jsonb_path_ops);

Практическое правило: если все ваши запросы построены на @>, берите jsonb_path_ops. Он хеширует весь путь к значению, а не индексирует ключи и значения по отдельности, поэтому заметно меньше и лучше справляется с включением по частому ключу. Если нужен ?, чтобы узнать, существует ли ключ, нужен класс по умолчанию. Можно держать оба, но два GIN-индекса на одной колонке - дорогая цена за удобство.

Проверьте, что планировщик действительно его использует:

EXPLAIN (ANALYZE, BUFFERS)SELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';

Вам нужен Bitmap Index Scan по GIN-индексу, который подаёт данные в Bitmap Heap Scan. GIN всегда даёт bitmap-сканирование, обычного index scan у него не бывает. EXPLAIN ANALYZE on a slow query разбирает такие планы строка за строкой.

Чего GIN-индекс не делает

Именно здесь возникает большая часть разочарований.

• Он не помогает сравнениям через `->>`. WHERE payload ->> 'plan' = 'pro' не может воспользоваться GIN-индексом выше, потому что это не проверка включения. Перепишите как payload @> '{"plan":"pro"}' или постройте индекс по выражению (следующий раздел).
• Он не помогает ни диапазонам, ни сортировке. У GIN-индекса нельзя спросить "total больше 100" или сделать ORDER BY по полю.
• Он медленно пишется. GIN использует отложенный список (fastupdate) для пакетной вставки: в среднем это сглаживает нагрузку, но отдельные вставки время от времени платят за сброс, а у vacuum появляется больше работы. Таблица с интенсивными вставками и большим GIN-индексом это показывает в задержке записи.
• Он может быть большим. Размер в несколько раз больше эквивалентного B-tree - обычное дело. На маленьком сервере он конкурирует за память с горячими данными.
• У планировщика нет статистики о содержимом документа. Для @> он угадывает селективность, поэтому оценки часто неверны, а неверная оценка может выбрать плохой план. Это известное ограничение, а не ошибка настройки.

GIN-индекс по jsonb - универсальный инструмент, который хорошо отвечает на вопрос "найти документы, содержащие X". Для поля, по которому вы постоянно фильтруете или сортируете, нужно что-то точнее.

Индексы по выражению для полей, которые вы действительно запрашиваете#

Когда одно-два поля несут основную нагрузку, индексируйте их напрямую B-tree по выражению. Он меньше, дешевле в поддержке, умеет диапазоны и сортировку и даёт планировщику настоящую статистику.

-- Equality on a text fieldCREATE INDEX events_plan ON app.events ((payload ->> 'plan'));-- Range on a numeric field: cast inside the indexCREATE INDEX events_total ON app.events (((payload ->> 'total')::numeric))  WHERE kind = 'invoice';-- Unique within a tenantCREATE UNIQUE INDEX events_user_once ON app.events (tenant_id, (payload #>> '{user,id}'))  WHERE kind = 'signup';

Двойные скобки обязательны: внешняя пара - это список колонок индекса, внутренняя помечает выражение. Запрос должен повторять выражение в точности, включая приведение типа. Если индекс построен по ((payload ->> 'total')::numeric), то WHERE payload ->> 'total' > '100' сравнивает текст и не использует ничего. Это та же ловушка, что описана для обычных функций в руководстве по индексам, а частичное условие WHERE kind = 'invoice' сокращает индекс до строк, у которых это поле вообще есть.

Индексы по выражению хорошо сочетаются с ограничением CHECK, чтобы тип, к которому вы приводите, был действительно гарантирован:

ALTER TABLE app.events  ADD CONSTRAINT invoice_total_numeric  CHECK (kind <> 'invoice' OR jsonb_typeof(payload -> 'total') = 'number');

Без него одна строка с "total": "n/a" ломает приведение, причём ломает посреди запроса, а не при вставке. Ограничения - самое близкое к схеме внутри документа, что у вас есть, и они дёшевы.

Перенос поля в настоящую колонку#

Когда поле есть в каждом запросе, перестаньте его извлекать. PostgreSQL 12 и новее умеет держать настоящую колонку в согласии с документом:

ALTER TABLE app.events  ADD COLUMN plan text GENERATED ALWAYS AS (payload ->> 'plan') STORED;CREATE INDEX events_plan_col ON app.events (plan);

Колонка вычисляется при записи, хранится на диске, индексируется как любая другая и попадает в SELECT *. Писать в неё напрямую нельзя: вы меняете документ, и она следует за ним. На существующей большой таблице добавление хранимой генерируемой колонки перезаписывает таблицу, так что относитесь к этому как к миграции с блокировкой, а не как к рядовому оператору - безопасный порядок описан в schema migrations without downtime.

Альтернатива - обычная колонка, которую заполняет приложение или backfill, а документ хранит копию, пока вы её не уберёте. Работы больше, зато гибче: можно менять типы, добавлять NOT NULL, добавлять внешний ключ. Генерируемые колонки внешними ключами быть не могут.

Пользователи MySQL узнают этот приём - только так MySQL вообще индексирует JSON, а MySQL JSON columns разбирает его аналог. В PostgreSQL он необязателен, потому что есть индексы по выражению, но генерируемая колонка делает схему честной в том, какие поля важны.

Во что обходится jsonb#

У jsonb есть свойство, которое удивляет при первом обновлении нагруженной таблицы: любое изменение документа переписывает всё значение целиком. PostgreSQL никогда не правит jsonb частично. Изменение одного булева значения в документе на 40 КБ записывает новую версию строки на 40 КБ, а старая ждёт vacuum.

Хранение устроено так:

• Значения больше примерно 2 КБ сжимаются и, если всё ещё велики, выносятся из строки в таблицу TOAST. Запрос, читающий одно поле большого документа, всё равно вынужден получить и распаковать его целиком.
• Обновление значения jsonb в TOAST записывает новое значение в TOAST. Старое остаётся, пока его не уберёт vacuum.
• Обновление любой колонки строки, в том числе не связанной с документом, тоже может переписать строку; heap-only обновления возможны лишь тогда, когда не изменилась ни одна индексированная колонка.

Последствия, в порядке частоты:

1. Горячие счётчики внутри документа - плохая идея. jsonb_set(payload, '{views}', ...) на каждый просмотр страницы порождает раздувание, несоразмерное сохраняемой информации. Держите счётчики в отдельной целочисленной колонке или таблице.
2. Большие документы, которые часто обновляются, лучше разложить на несколько строк. Разбейте блоб на 200 КБ, в котором на запрос меняется одно поле, на строки по подсущностям.
3. Широкие документы вредят чтению, которому нужно одно поле. Узкая таблица, где часто читаемые поля - колонки, а остальное в payload, работает лучше таблицы, состоящей из одного payload.
4. У vacuum больше работы. В vacuum and bloat описано, как увидеть, что мёртвые кортежи копятся быстрее, чем их убирает autovacuum; таблицы jsonb с частыми обновлениями - распространённый источник этого.

Жёсткий предел - 1 ГБ на значение, к которому вам не стоит приближаться. Практический предел намного ниже: после нескольких сотен килобайт документ - это файл, и правильный дом для него - файловое хранилище.

Когда jsonb - неподходящий инструмент#

Честный тест: если вы можете выписать поля, их типы и то, какие из них обязательны, у вас есть схема, а таблицы PostgreSQL - лучший инструмент для схемы из всех, что у вас есть. jsonb не подходит в таких случаях.

• Реляционные данные в маскировке. Массив jsonb со строками заказа внутри orders не может иметь внешний ключ на товары, не соединяется без разворачивания и не гарантирует, что товар существует. Используйте дочернюю таблицу.
• Поля, по которым вы фильтруете, сортируете или агрегируете. У колонки есть статистика, B-tree, тип и ограничения. У пути внутри документа без дополнительной работы нет ничего из этого, а SUM((payload ->> 'total')::numeric) по миллионам строк медленнее, чем SUM(total).
• Всё, что должно быть уникальным или обязательным. NOT NULL, UNIQUE, REFERENCES и CHECK на колонке - одна строка. На поле в документе это индексы по выражению и проверки, написанные вручную, и приложение про одну из них забудет.
• Часто обновляемые мелкие значения. Счётчики, флаги, метки времени и статусы, которые меняются почти при каждом запросе, платят за перезапись, описанную выше, без всякой пользы.
• Крупное двоичное содержимое. Картинки в base64 внутри JSON - это три четверти мегабайта впустую потраченного разбора. Положите файл в объектное хранилище, а URL в строку.
• Целое приложение без схемы. Если в таблице один id и одна колонка data, вы построили документную базу внутри реляционной: с худшими инструментами, чем у документной, и без гарантий реляционной. Это может быть осознанным выбором; в PostgreSQL or MongoDB разобрано, когда лучше подходит другой движок, а MongoDB indexes and schema design показывает, как выглядит альтернатива.

И случаи, когда он - правильный инструмент:

• Payload от третьих сторон, форму которых вы не контролируете: хранится как получено, читается в основном целиком.
• Пользовательские поля или настройки отдельных клиентов, где ключи у каждой строки свои.
• Разреженные атрибуты: каталог, где у каждой категории десяток свойств, и у разных категорий они не совпадают.
• Записи событий и аудита, которые пишутся один раз и читаются при отладке, со стабильными полями (tenant_id, kind, created_at) в виде обычных колонок.
• Прототипы - с планом перенести выжившие поля в колонки.

Привычка, которая работает: начните с колонок для всего, что знаете, добавьте одну колонку jsonb с именем по содержимому (attributes, metadata, raw_payload) и выносите поле из неё в тот день, когда оно дважды появляется в WHERE.

jsonb на небольшом сервере#

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

SELECT pg_size_pretty(pg_table_size('app.events'))   AS table_and_toast,       pg_size_pretty(pg_indexes_size('app.events')) AS indexes;SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scanFROM pg_stat_user_indexes WHERE relid = 'app.events'::regclass;

Индексы на заполненной таблице стройте через CREATE INDEX CONCURRENTLY, чтобы не блокировать запись, и заранее увеличьте maintenance_work_mem для сессии - построение GIN требует много памяти. Если у вас одновременно GIN-индекс по умолчанию и индексы по выражению, через неделю посмотрите idx_scan и удалите тот, который планировщик игнорирует. PostgreSQL tuning for small servers описывает настройки памяти, от которых зависит, останется ли всё это в кеше, а PostgreSQL with pg_stat_statements покажет, какие запросы к jsonb действительно дорогие, прежде чем вы добавите индекс не для того.

На RE:NODE сервер PostgreSQL - это отдельный тариф со своими учётными данными, поэтому построить и проверить индекс на копии стоит небольшого тарифа, а не риска для рабочей базы. Сделайте backup перед большой сборкой индекса или миграцией, которая переписывает таблицу; слоты входят в тариф, а шаги показаны в руководстве по базам данных и backup.

FAQ#

Что выбрать в PostgreSQL: json или jsonb?

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

Почему мой запрос к jsonb не использует GIN-индекс?

Чаще всего потому, что запрос использует ->> или -> с = вместо @>. GIN-индекс отвечает на проверки включения и наличия ключа, а не на сравнение извлечённых значений. Перепишите запрос через @> или создайте B-tree индекс по выражению, в точности совпадающему с выражением в запросе.

jsonb_ops или jsonb_path_ops?

jsonb_path_ops, если вы используете только @> и операторы jsonpath: он меньше и обычно быстрее. Берите jsonb_ops по умолчанию, если вам также нужны ?, ?| или ?& для проверки ключей.

jsonb медленнее обычных колонок?

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

Можно ли поставить внешний ключ или ограничение уникальности на поле внутри jsonb?

Напрямую нельзя. Уникальность даёт уникальный индекс по выражению, а форму документа может проверить ограничение CHECK. Для внешнего ключа нужна настоящая колонка: извлеките поле генерируемой колонкой или перенесите его в саму таблицу.

Каков максимальный размер значения jsonb?

Предел - 1 ГБ, но производительность падает задолго до него. Значения больше примерно 2 КБ сжимаются или уходят в хранилище TOAST, а любое обновление переписывает значение целиком. Держите часто обновляемые документы небольшими, а файлы храните в объектном хранилище.