Добрый день, меня зовут Павел Поляков, я Principal Engineer в каршеринг компании SHARE NOW, в Гамбурге в 
Германии. А еще я автор Telegram-канала Хороший разработчик знает, где рассказываю обо всем, что должен знать хороший разработчик.
Сегодня хочу поговорить о том стоит ли хранить данные в JSONB полях в PostgreSQL. Как это влияет на производительность?
Обычно мы используем PostgreSQL, а именно Amazon Aurora PostgreSQL. В этот раз решили тоже использовать ее. Я начал проектировать базу данных. Чтобы реализовать бизнес требования, нам нужно было записывать результат в табличку, назовем ее history. Каждый раз, когда наш сервис триггер он будет что-то делать и получать результат. На одно действие результатов может быть как 0 так и несколько, но обычно до пяти. Я решил, что можно сохранять их как массив в поле типа JSONB.
Этот ответ устроил всех и мы продолжили работать с предложенной схемой, но во мне поселилось зерно сомнения. Я подозревал, что если сделать схему такой как я предложил, то работать будет, но будет проигрыш по скорости. Для бизнеса точно не критично, у нас не терабайты информации. Но какой именно будет проигрыш? Я решил проверить.
Сначала я создал обе схемы, просто три таблицы в одной базе данных:
Схема базы данных
Написал два генератора данных, для JSONB схемы и для "обычной", и залил эти данные в базу данных. В результате в таблице history_a получилось 1.000.000 записей и по три записи результатов в JSONB колонке для каждой записи. В таблице history_b тоже 1.000.000 записей, на каждую из которых в таблице history_b_results приходится по 3 записи результатов, то есть 3.000.000.
Пришло время выполнять SQL запросы и измерять скорость. Я решил проверить два варианта — выборка по всем данным и выборка с фильтром по customer_uuid, что более приближенно к реальности. Чтобы усложнить, будем еще и фильтровать по expiration_date.
Если хотите повторить эксперимент, то репозиторий найдете здесь.
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
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
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
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
Сравнение времени запросов к обоим схемам
С одной стороны, если делать запрос по всему объему данных — миллионам записей, то "обычная" схема выигрывает. Но не разгромно. Разница во времени — секунда, для нашего случая это не критично.
Но если делать запрос приближенный к реальной жизни, то вариант с JSONB выигрывает! Изначально, я предполагал что результат с JSONB будет незначительно медленнее, но произшло обратное. Я рад, что я ошибался.
В результате мы уже год используем схему с JSONB и все работает отлично. Очевидно что ситуация с JSONB колонками будет только улучшаться. И это отлично, что в PostgreSQL можно совмещать реляционную и документоориентированную модель базы данных.
Спасибо
habr.com
Германии. А еще я автор Telegram-канала Хороший разработчик знает, где рассказываю обо всем, что должен знать хороший разработчик.Сегодня хочу поговорить о том стоит ли хранить данные в JSONB полях в PostgreSQL. Как это влияет на производительность?
Почему это важно
Новый сервис
В прошлом году наша команда должна была разработать новый сервис. Ничего необычного. Он слушает сообщения из RabbitMQ, делает полезное действие и записывает результат в базу данных. Сервис был новый, и базу данных для него нужно было спроектировать с нуля.Обычно мы используем PostgreSQL, а именно Amazon Aurora PostgreSQL. В этот раз решили тоже использовать ее. Я начал проектировать базу данных. Чтобы реализовать бизнес требования, нам нужно было записывать результат в табличку, назовем ее history. Каждый раз, когда наш сервис триггер он будет что-то делать и получать результат. На одно действие результатов может быть как 0 так и несколько, но обычно до пяти. Я решил, что можно сохранять их как массив в поле типа JSONB.
У команды есть вопросы
Я предложил это решение команде. В целом команда была не против, но среди нас не было и экспертов масштабном использовании JSONB полей. Мы знали, что результатов будет сотни тысяч. И что по ним, иногда, нужно будет искать. Мне задали вопрос — а такое решение точно не будет сильно медленнее чем обычная схема, где каждый результат сохранялся бы в отдельной записи? Я сказал, что скорее всего не будет, ведь JSONB уже давно на рынке и по полям в объекте, если надо, можно создать индекс.Этот ответ устроил всех и мы продолжили работать с предложенной схемой, но во мне поселилось зерно сомнения. Я подозревал, что если сделать схему такой как я предложил, то работать будет, но будет проигрыш по скорости. Для бизнеса точно не критично, у нас не терабайты информации. Но какой именно будет проигрыш? Я решил проверить.
Проверяем
Для того чтобы проверить я решил создать две схемы, похожие на наш случай:- Схема c JSONB. Одна таблица — history_a, где результаты для каждого запроса будут сохраняться как массив в JSONB колонке
- Схема "обычная". Две таблицы — одна для запросов — history_b, а другая для результатов — history_b_results. Каждый результат записывается отдельно.
Сначала я создал обе схемы, просто три таблицы в одной базе данных:
Написал два генератора данных, для 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
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
- "Обычная" схема
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
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
- "Обычная" схема
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, мне есть чем поделиться. Все это нужно разработчику, чтобы делать свою работу хорошо, быть востребованным на рынке и получать высокую компенсацию.Спасибо
Храним данные в JSONB, как это влияет на скорость запросов?
Добрый день, меня зовут Павел Поляков, я Principal Engineer в каршеринг компании SHARE NOW, в Гамбурге в ?? Германии. А еще я автор Telegram-канала Хороший разработчик знает , где...
habr.com