Скрытая цена JSONB в PostgreSQL: что происходит при обновлении больших документов

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

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

В этой статье мы не собираемся доказывать, что jsonb плохой тип данных. Наоборот: jsonb – один из самых полезных инструментов PostgreSQL. Хотелось бы разобраться в более узком вопросе:

что именно происходит, когда мы обновляем один ключ внутри большого JSONB-документа, и чем это отличается от обновления обычной колонки рядом с таким же большим документом?

При первом рассмотрении кажется, что на операцию не потребуется много ресурсов: меняется всего один ключ, например status. В голове разработчика это выглядит примерно так:

• нашли нужный кусочек документа;

• заменили «new» на «done»;

• записали обратно.

Но PostgreSQL работает не так. И если документ уже большой, у этой разницы появляется вполне измеримая цена.

Что именно мы хотели проверить

Мы взяли четыре сценария:

1. Маленький документ, а часто меняющееся поле хранится в обычной колонке;

2. Маленький документ, а то же поле хранится внутри jsonb;

3. Большой документ, а часто меняющееся поле хранится в обычной колонке;

4. Большой документ, а то же поле хранится внутри jsonb.

Смысл такого сравнения простой. Мы хотели отделить два фактора:

• влияет ли сам размер документа;

• влияет ли то, что изменяемый атрибут лежит внутри jsonb, а не рядом с ним.

Если в обоих “больших” сценариях рядом лежит один и тот же крупный JSON-документ, но в одном случае мы обновляем обычную колонку status, а в другом — делаем jsonb_set(payload, '{status}', ...), то разница между ними уже очень показательна.

Почему здесь вообще может быть цена

На SQL-уровне функция jsonb_set возвращает новое значение jsonb, а не “чинит пару байт на месте”. Поэтому логическая операция “поменять один ключ” для PostgreSQL превращается в запись нового значения столбца. А дальше вступает обычная физика UPDATE в PostgreSQL: создаётся новая версия строки. Если значение большое, к картине добавляется TOAST – механизм хранения крупных значений вне основной heap-страницы. Иными словами, удобство jsonb не отменяет того, что база всё равно работает с версиями строк и физическим хранением данных.

Из-за этого была гипотеза:

чем больше документ и чем чаще изменяемое поле спрятано внутри него, тем заметнее будет разница между “обновить обычную колонку” и “обновить один ключ внутри jsonb».

Отдельно было интересно посмотреть на HOT update. Если никакие индексируемые столбцы не меняются и на странице хватает места, PostgreSQL может сделать HOT update и избежать создания новых записей в индексах. Но если мы индексируем выражение вроде (payload->>'status'), то для такого обновления этот бонус уже исчезает.

Стенд

Все примеры тестировали локально на PostgreSQL 16.4.

Чтобы не спорить о “магии окружения”, специально сделали стенд максимально простым:

• одна отдельная схема для эксперимента;

• одинаковое количество строк;

• одинаковая логика данных;

• отличие только в размере документа и в том, где именно лежит часто обновляемое поле.

У этого эксперимента есть ограничения: локальный стенд, PostgreSQL 16.4, 5000 строк, обновление половины таблицы, fillfactor = 70, без конкурентной нагрузки. Поэтому наши цифры – не универсальный бенчмарк, а иллюстрация механики и относительной разницы между сценариями.

Вот полный DDL и генерация данных.

drop schema if exists jsonb_price cascade;create schema jsonb_price;set search_path = jsonb_price, public;create or replace function make_noise(seed int, chunks int)returns textlanguage sqlimmutableas $$  select string_agg(md5(seed::text || ':' || g::text), '')  from generate_series(1, chunks) as g$$;create table docs_rel_small (    id bigint generated always as identity primary key,    status text not null,    payload jsonb not null) with (fillfactor = 70);create table docs_json_small (    id bigint generated always as identity primary key,    payload jsonb not null) with (fillfactor = 70);create table docs_rel_big (    id bigint generated always as identity primary key,    status text not null,    payload jsonb not null) with (fillfactor = 70);create table docs_json_big (    id bigint generated always as identity primary key,    payload jsonb not null) with (fillfactor = 70);create table docs_json_big_idx (    id bigint generated always as identity primary key,    payload jsonb not null) with (fillfactor = 70);insert into docs_rel_small (status, payload)select    'new',    jsonb_build_object(        'customer_id', gs,        'source', 'api',        'flags', jsonb_build_array('web', 'promo'),        'amount', gs % 1000    )from generate_series(1, 5000) as gs;insert into docs_json_small (payload)select    jsonb_build_object(        'status', 'new',        'customer_id', gs,        'source', 'api',        'flags', jsonb_build_array('web', 'promo'),        'amount', gs % 1000    )from generate_series(1, 5000) as gs;insert into docs_rel_big (status, payload)select    'new',    jsonb_build_object(        'customer_id', gs,        'source', 'api',        'flags', jsonb_build_array('web', 'promo'),        'amount', gs % 1000,        'description', make_noise(gs, 220),        'audit', jsonb_build_object(            'created_by', 'system',            'request_id', md5(gs::text),            'trace', make_noise(gs + 100000, 120)        )    )from generate_series(1, 5000) as gs;insert into docs_json_big (payload)select    jsonb_build_object(        'status', 'new',        'customer_id', gs,        'source', 'api',        'flags', jsonb_build_array('web', 'promo'),        'amount', gs % 1000,        'description', make_noise(gs, 220),        'audit', jsonb_build_object(            'created_by', 'system',            'request_id', md5(gs::text),            'trace', make_noise(gs + 100000, 120)        )    )from generate_series(1, 5000) as gs;insert into docs_json_big_idx (payload)select    jsonb_build_object(        'status', 'new',        'customer_id', gs,        'source', 'api',        'flags', jsonb_build_array('web', 'promo'),        'amount', gs % 1000,        'description', make_noise(gs, 220),        'audit', jsonb_build_object(            'created_by', 'system',            'request_id', md5(gs::text),            'trace', make_noise(gs + 100000, 120)        )    )from generate_series(1, 5000) as gs;vacuum analyze docs_rel_small;vacuum analyze docs_json_small;vacuum analyze docs_rel_big;vacuum analyze docs_json_big;vacuum analyze docs_json_big_idx;

Мы специально задали fillfactor = 70, чтобы у PostgreSQL было больше шансов сделать HOT update там, где он вообще возможен.

Сначала – быстрый sanity check

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

select 'docs_rel_small' as table_name, avg(pg_column_size(payload))::bigint as avg_payload_bytes from jsonb_price.docs_rel_smallunion allselect 'docs_json_small', avg(pg_column_size(payload))::bigint from jsonb_price.docs_json_smallunion allselect 'docs_rel_big', avg(pg_column_size(payload))::bigint from jsonb_price.docs_rel_bigunion allselect 'docs_json_big', avg(pg_column_size(payload))::bigint from jsonb_price.docs_json_bigunion allselect 'docs_json_big_idx', avg(pg_column_size(payload))::bigint from jsonb_price.docs_json_big_idxorder by 1;

У нас получилось примерно так:

То есть стенд действительно разделил сценарии на “маленький JSONB” и “большой JSONB”, а не просто дал две почти одинаковые таблицы с разными именами.

После этого сняли размеры таблиц и связанных с ними TOAST-отношений:

select    c.relname as table_name,    pg_size_pretty(pg_relation_size(c.oid)) as heap,    pg_size_pretty(pg_table_size(c.oid)) as table_with_toast,    pg_size_pretty(pg_indexes_size(c.oid)) as indexes,    pg_size_pretty(pg_total_relation_size(c.oid)) as total,    pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) as toast_totalfrom pg_class cwhere c.oid in (    'jsonb_price.docs_rel_small'::regclass,    'jsonb_price.docs_json_small'::regclass,    'jsonb_price.docs_rel_big'::regclass,    'jsonb_price.docs_json_big'::regclass,    'jsonb_price.docs_json_big_idx'::regclass)order by c.relname;

На этом шаге уже видно важную вещь: у больших сценариев заметная часть объёма живёт не в основном heap, а в TOAST.

Основной эксперимент

Теперь – самое интересное. Мы обновляли половину строк в каждой таблице.

1. Маленький документ, статус в отдельной колонке

explain (analyze, buffers)update jsonb_price.docs_rel_smallset status = 'done'where id <= 2500;

Результат: 9 мс.

После этого посмотрели статистику таблицы:

analyze jsonb_price.docs_rel_small;select    relname,    n_tup_upd,    n_tup_hot_upd,    n_dead_tupfrom pg_stat_user_tableswhere schemaname = 'jsonb_price'  and relname = 'docs_rel_small';

У нас получилось:

• n_tup_upd = 2500

• n_tup_hot_upd = 1122

• n_dead_tup = 0

Это хороший базовый сценарий: маленький документ, меняется короткое поле, и никаких сюрпризов тут нет.

Сразу оговоримся: n_dead_tup в pg_stat_user_tables – это оценочный счётчик, а не точное физическое число мёртвых строк. Поэтому используем его только как вспомогательный сигнал и не делаем из него главный вывод статьи.

2. Маленький документ, статус внутри JSONB

explain (analyze, buffers)update jsonb_price.docs_json_smallset payload = jsonb_set(payload, '{status}', to_jsonb('done'::text), false)where id <= 2500;

Результат: 10 мс

Статистика после ANALYZE:

• n_tup_upd = 2500

• n_tup_hot_upd = 1110

• n_dead_tup = 0

Вот тут уже начинается интересное. В маленьком документе разница между отдельной колонкой и jsonb_set оказалась небольшой: 9 мс против 10 мс, и на таком размере документа почти не влияла на общее время выполнения.

3. Большой документ, статус в отдельной колонке

explain (analyze, buffers)update jsonb_price.docs_rel_bigset status = 'done'where id <= 2500;

Результат: 8 мс

Статистика:

• n_tup_upd = 2500

• n_tup_hot_upd = 1096

• n_dead_tup = 0

Именно этот сценарий был самым важным контрольным. Большой документ здесь тоже есть, но его не меняем. Меняем только короткую колонку status, которая лежит рядом.

Если смотреть только на логику приложения, то этот сценарий и следующий делают одно и то же – меняют статус. Но физически это разные операции: в docs_rel_big меняется короткая колонка рядом с большим документом, а в docs_json_big меняется сам payload.

4. Большой документ, статус внутри JSONB

explain (analyze, buffers)update jsonb_price.docs_json_bigset payload = jsonb_set(payload, '{status}', to_jsonb('done'::text), false)where id <= 2500;

Результат: 1090 мс

Статистика:

• n_tup_upd = 2500

• n_tup_hot_upd = 1096

• n_dead_tup = 2500

При этом часть обновлений всё равно попала в HOT update: у таблицы не было индекса по payload, а fillfactor = 70 оставлял свободное место на странице. Это важный момент: даже наличие HOT не спасло сценарий, где пришлось переписывать большой payload.

После этого мы снова сняли размеры таблиц и TOAST:

select    c.relname as table_name,    pg_size_pretty(pg_relation_size(c.oid)) as heap,    pg_size_pretty(pg_table_size(c.oid)) as table_with_toast,    pg_size_pretty(pg_indexes_size(c.oid)) as indexes,    pg_size_pretty(pg_total_relation_size(c.oid)) as total,    pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) as toast_totalfrom pg_class cwhere c.oid in (    'jsonb_price.docs_rel_small'::regclass,    'jsonb_price.docs_json_small'::regclass,    'jsonb_price.docs_rel_big'::regclass,    'jsonb_price.docs_json_big'::regclass)order by c.relname;

И вот здесь эксперимент наконец стал действительно показательным.

На нашем стенде:

• docs_rel_big обновлялась за 8 мс;

• docs_json_big обновлялась за 1090 мс;

• размер TOAST у docs_rel_big не изменился;

• размер TOAST у docs_json_big вырос на 30 мб.

То есть сама по себе большая JSON-нагрузка рядом со строкой ещё не делает UPDATE автоматически дорогим. Самым дорогим оказался не факт наличия большого документа, а то, что часто изменяемый атрибут лежал внутри payload, который приходилось пересобирать и записывать заново.

Почему так вышло

Здесь и проявляется скрытая цена jsonb.

Когда большой jsonb лежит рядом с колонкой status, обновление статуса не требует менять сам payload. Это и объясняет, почему docs_rel_big осталась быстрой: большой документ физически присутствует в строке, но само обновление не заставляет PostgreSQL пересобирать и записывать его заново.

Когда же статус лежит внутри payload, операция уже выглядит иначе:

payload = jsonb_set(payload, '{status}', ...)

То есть мы создаём новое значение payload, а затем делаем обычный UPDATE строки. Если документ большой, в картину добавляется TOAST. Старые версии строки и старые данные не исчезают мгновенно: они остаются как минимум до VACUUM. В результате цена одной маленькой бизнес-операции распадается на несколько составляющих:

• создаётся новая версия строки;

• PostgreSQL приходится формировать новое значение payload;

• при большом документе заметно растёт работа с TOAST;

• накапливаются мёртвые версии строк;

• увеличивается будущая нагрузка на VACUUM и повторное использование места.

Снаружи это всё ещё “обновить один статус”. Внутри – уже далеко не один флажок.

А что насчёт HOT update

Чтобы отдельно проверить влияние индекса на часто меняющийся ключ внутри JSONB, мы взяли ещё одну таблицу и добавил индекс на выражение:

create index docs_json_big_idx_status_idx    on jsonb_price.docs_json_big_idx ((payload->>'status'));vacuum analyze jsonb_price.docs_json_big_idx;

После этого повторили тот же UPDATE:

explain (analyze, buffers)update jsonb_price.docs_json_big_idxset payload = jsonb_set(payload, '{status}', to_jsonb('done'::text), false)where id <= 2500;

Результат: 1070 мс.

А затем посмотрели статистику:

analyze jsonb_price.docs_json_big_idx;select    relname,    n_tup_upd,    n_tup_hot_upd,    n_dead_tupfrom pg_stat_user_tableswhere schemaname = 'jsonb_price'  and relname = 'docs_json_big_idx';

Статистика:

• n_tup_upd = 2500

• n_tup_hot_upd = 0

• n_dead_tup = 0

Результат оказался ожидаемым, но от этого не менее полезным: после индекса на (payload->>'status') рассчитывать на HOT для такого UPDATE уже не приходится. На нашем стенде это выразилось в том, что:

• общее время обновления почти не изменилось: 1090 мс против 1070 мс;

• n_tup_hot_upd упал с 1096 до 0;

• PostgreSQL полностью потерял возможность использовать HOT update для такого сценария.

Это важный практический вывод. Сам по себе индекс по jsonb или по выражению из jsonb нормальный инструмент. Но если вы индексируете поле, которое ещё и часто меняете, то нужно помнить: вы не просто ускоряете чтение, вы ещё и лишаете PostgreSQL возможности использовать HOT update. В коротком тесте это не дало заметного прироста времени, но в более длинных сериях обновлений такая потеря оптимизации может оказаться важной.

Что показал эксперимент

Если коротко, то у нас получилась такая картина:

• На маленьких документах разница между “статус отдельной колонкой” и “статус внутри jsonb” есть, но в нашем тесте она оказалась минимальной: 9 мс против 10 мс;

• На больших документах разница становится радикальной: 8 мс против 1090 мс;

• в нашем коротком тесте индекс на (payload->>'status') почти не изменил общее время выполнения: доминирующей всё равно осталась стоимость переписывания большого payload;

• при этом после индекса PostgreSQL полностью потерял возможность использовать HOT update;

• сам факт использования большого jsonb не всегда проблема;

• проблема начинается тогда, когда большой jsonb становится контейнером для часто меняющихся полей.

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

Практические выводы

После этого прогона у нас получились очень приземлённые правила.

1. Часто изменяемые поля лучше держать отдельно от большого JSONB.
Если status, retry_count, updated_at, last_error_code или похожие поля меняются часто, лучше не прятать их внутрь большого документа только ради “красоты схемы”.

2. Большой JSONB хорошо подходит для относительно стабильных данных.
Для редко меняющейся структуры, аудита, сырого ответа внешнего API, “длинного хвоста” редко используемых атрибутов — это отличный вариант.

3. Индекс по выражению из JSONB надо оценивать вместе со стоимостью записи.
Если чтения выигрывают, а обновления редкие – отлично. Если поле меняется часто, индекс может лишить PostgreSQL HOT update и тем самым сделать запись дороже, даже если в коротком тесте это не всегда сразу видно по времени.

4. Смотреть надо не только на время запроса, но и на размеры heap и TOAST.
Если измерять только EXPLAIN ANALYZE, можно упустить главный побочный эффект: рост таблицы и TOAST. Оценочные счётчики вроде n_dead_tup тоже полезны, но использовать их лучше как вспомогательный сигнал, а не как основной аргумент.

5. Не стоит надеяться, что VACUUM “магически всё схлопнет обратно”.
Обычный VACUUM нужен и полезен, но его основная задача – освобождать место для повторного использования, а не немедленно уменьшать размер файлов таблицы и TOAST.

Итоговая таблица результатов

jsonb остаётся очень сильным инструментом. Но за его удобством легко пропустить простую вещь: стоимость обновления определяется не только бизнес-логикой, но и физикой хранения.

Если поле меняется часто, лучше заранее спросить себя:

• это действительно часть документа;

• или это оперативный атрибут строки, который просто удобно было спрятать в JSONB?

Пока документ маленький, разница может быть незаметной. Когда документ становится большим, а обновления регулярными, ответ на этот вопрос начинает стоить вполне реальных миллисекунд, страниц heap, TOAST-чанков и работы VACUUM.

Для нас главный вывод именно в этом:
большой jsonb сам по себе не страшен. Опасным он становится тогда, когда превращается в контейнер для часто изменяемых полей, которые логически кажутся “мелочью”, а физически заставляют PostgreSQL переписывать большой документ заново.