Храним данные в JSONB, как это влияет на скорость запросов?

Добрый день, меня зовут Павел Поляков, я 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, мне есть чем поделиться. Все это нужно разработчику, чтобы делать свою работу хорошо, быть востребованным на рынке и получать высокую компенсацию.

Спасибо 🤗