Добрый день, меня зовут Павел Поляков, я Principal Engineer в каршеринг компании SHARE NOW, в Гамбурге в ???????? Германии. А еще я автор Telegram-канала Хороший разработчик знает, где рассказываю обо всем, что должен знать хороший разработчик.
Сегодня хочу поговорить о том стоит ли хранить данные в JSONB полях в PostgreSQL. Как это влияет на производительность?
Почему это важно
Новый сервис
В прошлом году наша команда должна была разработать новый сервис. Ничего необычного. Он слушает сообщения из RabbitMQ, делает полезное действие и записывает результат в базу данных. Сервис был новый, и базу данных для него нужно было спроектировать с нуля.
Обычно мы используем PostgreSQL, а именно Amazon Aurora PostgreSQL. В этот раз решили тоже использовать ее. Я начал проектировать базу данных. Чтобы реализовать бизнес требования, нам нужно было записывать результат в табличку, назовем ее history. Каждый раз, когда наш сервис триггер он будет что-то делать и получать результат. На одно действие результатов может быть как 0 так и несколько, но обычно до пяти. Я решил, что можно сохранять их как массив в поле типа JSONB.
У команды есть вопросы
Я предложил это решение команде. В целом команда была не против, но среди нас не было и экспертов масштабном использовании JSONB полей. Мы знали, что результатов будет сотни тысяч. И что по ним, иногда, нужно будет искать. Мне задали вопрос — а такое решение точно не будет сильно медленнее чем обычная схема, где каждый результат сохранялся бы в отдельной записи? Я сказал, что скорее всего не будет, ведь JSONB уже давно на рынке и по полям в объекте, если надо, можно создать индекс.
Этот ответ устроил всех и мы продолжили работать с предложенной схемой, но во мне поселилось зерно сомнения. Я подозревал, что если сделать схему такой как я предложил, то работать будет, но будет проигрыш по скорости. Для бизнеса точно не критично, у нас не терабайты информации. Но какой именно будет проигрыш? Я решил проверить.
Проверяем
Для того чтобы проверить я решил создать две схемы, похожие на наш случай:
Схема c JSONB. Одна таблица —
history_a, где результаты для каждого запроса будут сохраняться как массив вJSONBколонкеСхема "обычная". Две таблицы — одна для запросов —
history_b, а другая для результатов —history_b_results. Каждый результат записывается отдельно.
Чтобы было интереснее, предположим, что результаты содержат информацию про начисленные очки для какого-то пользователя. Эти очки истекают где-то в будущем. Поэтому каждый результат имеет три свойства: type, points и expiration_date.
Сначала я создал обе схемы, просто три таблицы в одной базе данных:
Написал два генератора данных, для JSONB схемы и для "обычной", и залил эти данные в базу данных. В результате в таблице history_a получилось 1.000.000 записей и по три записи результатов в JSONB колонке для каждой записи. В таблице history_b тоже 1.000.000 записей, на каждую из которых в таблице history_b_results приходится по 3 записи результатов, то есть 3.000.000.
Пришло время выполнять SQL запросы и измерять скорость. Я решил проверить два варианта — выборка по всем данным и выборка с фильтром по customer_uuid, что более приближенно к реальности. Чтобы усложнить, будем еще и фильтровать по expiration_date.
Если хотите повторить эксперимент, то репозиторий найдете здесь.
Выборка по всем данным
Схема c
JSONB
explain analyze
select
count(*)
from
history_a,
jsonb_to_recordset(results) as results(type text,
points int,
"expirationDate" text)
where
TO_DATE("expirationDate", 'YYYY-MM-DD') > CURRENT_DATE - 10
Execution Time: 1393.279 ms
"Обычная" схема
explain analyze
select
count(*)
from
history_b hb
join history_b_results hbr on
hb.id = hbr.history_b_id
where
expiration_date > CURRENT_DATE - 10
Execution Time: 208.923 ms
Выборка с фильтром по customer_uuid
Схема с
JSONB
explain analyze
select
count(*)
from
history_a,
jsonb_to_recordset(results) as results(type text,
points int,
"expirationDate" text)
where
customer_uuid = 'dfce725c-f88e-411f-aa21-4e97a311a25a' AND
TO_DATE("expirationDate", 'YYYY-MM-DD') > CURRENT_DATE - 10
Execution Time: 103.321 ms
"Обычная" схема
explain analyze
select
count(*)
from
history_b hb
join history_b_results hbr on
hb.id = hbr.history_b_id
where
customer_uuid = '04f7ebcd-04f1-4213-ab3f-43986e33c176' and
expiration_date > CURRENT_DATE - 10
Execution Time: 196.466 ms
В виде графика
Детали про измерение времени
Я не использовал никакой benchmark движок и не писал свой. Все запросы просто выполнял пару раз на MacBook Pro (15-inch, 2019). Результаты всегда были более менее одинаковые, смысл здесь не в миллисекундах, а в разнице которую видно невооруженным глазом и она не меняется.
Выводы
Результат меня удивил.
С одной стороны, если делать запрос по всему объему данных — миллионам записей, то "обычная" схема выигрывает. Но не разгромно. Разница во времени — секунда, для нашего случая это не критично.
Но если делать запрос приближенный к реальной жизни, то вариант с JSONB выигрывает! Изначально, я предполагал что результат с JSONB будет незначительно медленнее, но произшло обратное. Я рад, что я ошибался.
В результате мы уже год используем схему с JSONB и все работает отлично. Очевидно что ситуация с JSONB колонками будет только улучшаться. И это отлично, что в PostgreSQL можно совмещать реляционную и документоориентированную модель базы данных.
A еще...
В конце еще раз приглашу вас в свой Telegram-канал. На канале Хороший разработчик знает я минимум три раза в неделю простым языком рассказываю про свой опыт, хард скиллы и софт скиллы. Я 15+ лет в IT, мне есть чем поделиться. Все это нужно разработчику, чтобы делать свою работу хорошо, быть востребованным на рынке и получать высокую компенсацию.
Спасибо ????
Комментарии (25)
akhkmed
21.10.2021 13:17+3Давайте индексы добавим. И данных побольше.
PavloPoliakov Автор
21.10.2021 14:20+1В моем случае "побольше данных" это был бы тест ради теста, не коррелирует с нашей бизнес задачей. А про индексы снизу написала @Insolita- будет еще лучше.
akhkmed
21.10.2021 14:28Не пытаюсь вас отговорить от вашего выбора, но когда данных станет чуть больше (раз в 10-100), то ожидание результата уже не оправдает ваши ожидания. А если сразу не прикинуть, как именно индексировать, то есть риск переделывать. Данные ведь только добавляются?
PavloPoliakov Автор
21.10.2021 14:32Все верно, как я говорил, в нашем кейсе данные не будут расти и по всем данным выборка тоже не будет применяться. Только с фильтром по
customer_uuid(на котором есть индекс), а у каждого конкретного пользователя количество данных маленькое.
Insolita
21.10.2021 13:40+3Да, если индексы добавить - можно и еще большей эффективности добиться, я тоже около года назад проверяла https://donnainsolita.medium.com/postgres-jsonb-vs-property-value-table-5f5b9f183a9d
LborV
21.10.2021 14:26Разница во времени — секунда, для нашего случая это не критично.
Секунда на самом деле это колоссально много, пока условный "воркер" крутит эту секунду запрос, остальные запросы стакаются. Как итог - вермя ожидания на выполнение будет расти коскадно
PavloPoliakov Автор
21.10.2021 14:30Это верное предположение, если это OLTP вариант использования. В нашем случае - нет, такие запросы только для аналитики бы вызывались. Поэтому секунда это мало.
Kazzman
21.10.2021 14:32+1Павел, секундочку, а какой план запроса в случае реляционной схемы? Ответы на вопросы производительности стоит начинать искать с плана и только с него.
PavloPoliakov Автор
21.10.2021 14:35Спасибо за комментарий, хочу уточнить, в моем эксперименте я проверял работу по умолчанию. Задачи оптимизировать запросы не было, потому что они не являются сейчас бутылочным горлышком.
Вот полный
EXPLAINреляционного запроса:Finalize Aggregate (cost=45949.77..45949.78 rows=1 width=8) (actual time=191.621..191.621 rows=1 loops=1) | -> Gather (cost=45949.56..45949.77 rows=2 width=8) (actual time=191.490..196.400 rows=3 loops=1) | Workers Planned: 2 | Workers Launched: 2 | -> Partial Aggregate (cost=44949.56..44949.57 rows=1 width=8) (actual time=171.319..171.319 rows=1 loops=3) | -> Nested Loop (cost=0.42..44949.56 rows=1 width=0) (actual time=171.314..171.314 rows=0 loops=3) | -> Parallel Seq Scan on history_b_results hbr (cost=0.00..43934.00 rows=124 width=4) (actual time=171.313..171.313 rows=0 loops=3)| Filter: (expiration_date > (CURRENT_DATE - 10)) | Rows Removed by Filter: 1000000 | -> Index Scan using history_b_pkey on history_b hb (cost=0.42..8.19 rows=1 width=4) (never executed) | Index Cond: (id = hbr.history_b_id) | Filter: ((customer_uuid)::text = '7e240a94-261b-4eeb-b0d3-8c6de47d9557'::text) | Planning Time: 0.148 ms | Execution Time: 196.466 ms |Kazzman
21.10.2021 14:55+4Тогда не понятно что вы хотели оценить. В случае с одной (индексированной) таблицей это явно оверхед на работу с jsonb, связанный также с полным извлечением данных jsonb. В случае с двумя таблицами всё упирается в просмотр бОльшей таблицы сканом. У вас даже время выполнения сопоставимое получилось для всех записей и по одному клиенту. Без индекса такой подзапрос не делают же. Без индекса просто ожидаемый результат.
Вывод с выигрышность-невыигрышностью мне кажется не верный.
Сам делал денормализацию, в том числе с коллекциями в оракле. Очень от сценария использования зависит. Если обработка в бд, то лучше уж ухитриться кластеризовать данные.
PavloPoliakov Автор
21.10.2021 15:16-1Я отвечал на такой вопрос - если мы данные храним эстетически красиво и компактно (для меня это вариант с
JSONB), будет ли это драматически влиять на производительность? Ответ оказался - нет, не будет.
Kilor
22.10.2021 09:11+3Чтобы хоть как-то сравнить эффективность запросов, надо смотреть их планы при решении одной и той же задачи - иначе вот так и может получиться, что там спрятался Seq Scan, и сравнение JSON-варианта идет с не самой эффективной альтернативой. И на ее фоне результаты могут выглядеть приемлемо, хотя "на самом деле" могут проигрывать кратно.
RajaKajiev
21.10.2021 16:53WTF moments:
1) зачем 100500 раз вызыватьTO_DATE()для данных вместо одного вызова для параметра (CURRENT_DATE - 10) ?
2) если требуется дописывать значения вhistory_b, то проблемой будет не сколько формат поляresults, а сколько конфликты приupdate=> нужно ещё полеversion(хотя на времяselectэто не должно драматически повлиять)P.S. 3) если нужен только
Array- мы уверены, что хотим JSONB не из желания поэкспериментировать с JSONB?PavloPoliakov Автор
21.10.2021 16:58Спасибо за фидбэк. Отвечая на ваши вопросы:
Какая альтернатива? Это может быть частью
jsonb_to_recordset?Наверное вы имеете в виду
history_a(гдеJSONB). Верно дописывать было бы не слишком удобно, но у нас данные immutable.
novoselov
21.10.2021 17:45+1Если я правильно понял алтернатива поменять правую часть на константу
expirationDate > TO_CHAR(CURRENT_DATE - 10, 'YYYY-MM-DD')Вы не конвертируете каждое поле, формат строки позволяет корректно сравнивать значения.
vagon333
21.10.2021 17:36+3Не могу сказать что тест аккуратный: JSON payload мелкий.
Увеличьте размер JSON payload и посмотрите на реакцию DBMS на парсинг более крупных JSON полей.Результаты логичны:
- в первом случае (Выборка по всем данным) вы парсите JSON и за это платите временем.
- во втором случае (Выборка с фильтром по customer_uuid) вы добавляете поиск по GUID, что сужает поиск, и начитываете из одной таблицы, а в нормальной форме читаете из 2х таблиц с JOIN. Небольшая задержка логична.
Повозившись с XML и JSON в базе данных с неконтролиуемой структурой XML/JSON, я вернулся к классической реляционной модели т.к. DBMS лучше заточены на них.
Asazio
21.10.2021 22:04+2Совсем недавно вышло видео о JSON в Postgres, так хорошо рассказаны текущие проблемы и как и решили или собераются решить.
edo1h
31.10.2021 16:53а причём тут вообще jsonb? я не вижу, чтобы вы в запросах с ним работали, хотя изначально про это речь шла:
Я сказал, что скорее всего не будет, ведь JSONB уже давно на рынке и по полям в объекте, если надо, можно создать индекс.
PavloPoliakov Автор
01.11.2021 11:19Я разворачиваю
jsonbв рекордсет при запросеjsonb_to_recordset, согласно документации (https://www.postgresql.org/docs/9.4/functions-json.html) это JSON Processing Function.
censor2005
Всё-таки интересен план проведения эксперимента. Возможно ли, что PostgreSQL кэширует результаты предыдущих запросов? На обе схемы каждый раз выполнялся один и тот же запрос, или разные? Данные в обеих таблицах одинаковые или разные? Может, распределение данных оказалось неудачным
PavloPoliakov Автор
Я пробовал с разными входными данными, не только теми что записал. Разное количество дней и разные
customer_uuid. Результаты были такие же. Я думаю этот эксперимент позволяет посмотреть на картину "в целом".