Добрый день, меня зовут Павел Поляков, я 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
.
Сначала я создал обе схемы, просто три таблицы в одной базе данных:
![Схема базы данных Схема базы данных](https://habrastorage.org/getpro/habr/upload_files/e75/d7c/759/e75d7c7595647cfd1f042a45c1a66527.png)
Написал два генератора данных, для 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
В виде графика
![Сравнение времени запросов к обоим схемам Сравнение времени запросов к обоим схемам](https://habrastorage.org/getpro/habr/upload_files/8de/fad/ce6/8defadce68ef6443313b27fd3890b06c.png)
Детали про измерение времени
Я не использовал никакой benchmark движок и не писал свой. Все запросы просто выполнял пару раз на MacBook Pro (15-inch, 2019)
. Результаты всегда были более менее одинаковые, смысл здесь не в миллисекундах, а в разнице которую видно невооруженным глазом и она не меняется.
Выводы
Результат меня удивил.
С одной стороны, если делать запрос по всему объему данных — миллионам записей, то "обычная" схема выигрывает. Но не разгромно. Разница во времени — секунда, для нашего случая это не критично.
Но если делать запрос приближенный к реальной жизни, то вариант с JSONB
выигрывает! Изначально, я предполагал что результат с JSONB
будет незначительно медленнее, но произшло обратное. Я рад, что я ошибался.
В результате мы уже год используем схему с JSONB
и все работает отлично. Очевидно что ситуация с JSONB
колонками будет только улучшаться. И это отлично, что в PostgreSQL
можно совмещать реляционную и документоориентированную модель базы данных.
A еще...
В конце еще раз приглашу вас в свой Telegram-канал. На канале Хороший разработчик знает я минимум три раза в неделю простым языком рассказываю про свой опыт, хард скиллы и софт скиллы. Я 15+ лет в IT, мне есть чем поделиться. Все это нужно разработчику, чтобы делать свою работу хорошо, быть востребованным на рынке и получать высокую компенсацию.
Спасибо ?