Хабрахабр

[Перевод] Замена EAV на JSONB в PostgreSQL

TL; DR: JSONB может значительно упростить разработку схемы БД без ущерба производительности в запросах.

Введение

Приведем классический пример, наверное, одного из старейших вариантов использования в мире реляционных БД (база данных): у нас есть сущность, и необходимо сохранить определенные свойства (атрибуты) этой сущности. Но не все экземпляры могут имеют одинаковый набор свойств, к тому же в будущем, возможное добавление ещё свойств.

Отлично! Самый простой путь решения этой проблемы – это создание столбца в таблице БД для каждого значение свойства, и просто заполнять те, которые нужны для определенного экземпляра сущности. Проблема решена… до того момента, пока ваша таблица не содержит миллионы записей и у вас не возникнет необходимость добавить новую запись.

Одна таблица содержит сущности (записи), другая таблица содержит имена свойств (атрибутов), а третья таблица связывает сущности с их атрибутами и содержит значение этих атрибутов для текущей сущности. Рассмотрим паттерн EAV (Entity-Attribute-Value), он встречается достаточно часто. Это дает вам возможность иметь разные наборы свойств для разных объектов, а также добавлять свойства “на лету”, не изменяя структуры БД.

Так, например, для получения одной или нескольких сущностей, которые имеют по 1 атрибуту требуется 2 join-на (объединения) в запросе: первый – объединение с таблицей атрибутов, второй – объединение с таблицей значений.
Тем не менее, я бы не писал эту заметку, если бы не было недостатков в подходе с использованием EVA. Кроме того, все атрибуты обычно хранятся в виде строк, что приводит к приведению типов, как для результата, так и для условия WHERE. Если сущность имеют 2 атрибуты, то нужно уже 4 join-на! Это были неизбежное недостатки, и лучшей альтернативы просто не было.
Но затем в PostgreSQL появилась новая “технология”… Если вы пишете много запросов, то это достаточно расточительно, с точки зрения использования ресурсов.
Несмотря на эти очевидные недостатки, EAV уже давно используется для решения такого рода проблем.

4, был добавлен тип данных JSONB для хранения двоичных данных JSON. Начиная с PostgreSQL 9. Также JSONB поддерживает индексирование, что делает запросы к ним еще быстрее. Хотя хранение JSON в этом формате обычно занимает немного больше места и времени, чем простой текстовый JSON, выполнение операций с ним происходит намного быстрее.

Но многие утверждают, что это должно сопровождаться снижением производительности… Вот по этой причине я и появилась эта статья. Тип данных JSONB позволяет нам заменить громоздкий паттерн EAV путем добавления всего лишь одного столбца JSONB в нашу таблицу сущностей, что значительно упрощает проектирование базы данных.

Настройка тестовой базы данных

Для этого сравнения я создал базу данных на новой установке PostgreSQL 9.5 на 80-долларовой сборке DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил этот скрипт с помощью psql. Для представления данных в виде EAV были созданы следующие таблицы:

CREATE TABLE entity ( id SERIAL PRIMARY KEY, name TEXT, description TEXT
);
CREATE TABLE entity_attribute ( id SERIAL PRIMARY KEY, name TEXT
);
CREATE TABLE entity_attribute_value ( id SERIAL PRIMARY KEY, entity_id INT REFERENCES entity(id), entity_attribute_id INT REFERENCES entity_attribute(id), value TEXT
);

Ниже представлена таблица где будут хранится те же данные, но с атрибутами в столбце типа JSONB – properties.

CREATE TABLE entity_jsonb ( id SERIAL PRIMARY KEY, name TEXT, description TEXT, properties JSONB
);

Затем было добавлено в таблицы сущностей (entity & entity_jsonb) 10 миллионов записей, и соответственно, было заполнено одинаковыми данными таблицы где используется EAV паттерн и подход с JSONB столбцом – entity_jsonb.properties. Выглядит намного проще, не так ли? Пример данных: Таким образом, мы получили несколько разных типов данных среди всего набора свойств.

}

Давайте начнем сравнивать реализации в работе! Итак, теперь у нас есть одинаковые данные, для двух вариантов.

Упрощение дизайна

Ранее уже было сказано, что дизайн БД был значительно упрощен: одна таблица, за счет использования столбца JSONB для свойств, вместо использования трех таблиц для EAV. Но как же это отражается в запросах? Обновление одного свойства сущности выглядит следующим образом:

-- EAV
UPDATE entity_attribute_value SET value = 'blue' WHERE entity_attribute_id = 1 AND entity_id = 120; -- JSONB
UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"color"}', '"blue"') WHERE id = 120;

Чтобы обновить значение свойства в объекте JSONB, мы должны использовать функцию jsonb_set(), и должны передать наше новое значение как объект JSONB. Как видим, последний запрос не выглядит проще. Посмотрев на пример с EAV, нам нужно знать и entity_id, и entity_attribute_id, чтобы выполнить обновление. Тем не менее, нам не нужно знать какой-либо идентификатор заранее. Если вы хотите обновить свойство в столбце JSONB на основе имени объекта, – то это все делается одной простой строкой.

Теперь давайте выберем ту сущность, которую мы только что обновили, по условию ее нового цвета:

-- EAV
SELECT e.name FROM entity e INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue'; -- JSONB
SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';

Здесь победа JSONB! Я думаю, что мы можем согласиться с тем, что второе является более коротким (без join-ов!), и соответственно более читабельным. Существует также второй способ достижения того же результата в модели JSONB с использованием оператора @>: Мы используем оператор JSON ->>, чтобы получить цвет как текстовое значение из объекта JSONB.

-- JSONB SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';

Это немного сложнее: мы проверяем, содержит ли объект JSON в столбце свойств объект который находится справа от оператора @>. Менее читаемый, более производительный (см. далее).

Вот где действительно подходит JSONB-подход: мы просто выбираем свойства в качестве дополнительных столбцов в нашем наборе результатов без необходимости объединений: Упростим использования JSONB еще сильнее, когда вам нужно выбрать несколько свойств одновременно.

-- JSONB SELECT name , properties ->> 'color' , properties ->> 'country'
FROM entity_jsonb WHERE id = 120;

На мой взгляд, приведенные выше запросы показывают большое упрощение в дизайне базы данных. С EAV вам понадобится 2 объединения для каждого свойства, которое вы хотите запросить. Посмотреть больше примеров того, как писать запросы к JSONB, возможно также в этом посте.
Теперь пришло время поговорить о производительности.

Производительность

Чтобы сравнить производительность, я использовал EXPLAIN ANALYZE в запросах, для подсчета времени выполнения. Каждый запрос выполнялся как минимум три раза, потому что в первый раз планировщику запросов требуется больше времени. Сначала я выполнил запросы без каких-либо индексов. Очевидно, это служило преимуществом JSONB, так как join-ы, необходимые для EAV, не могли использовать индексы (поля внешнего ключа не индексировались). После этого я создал индекс для 2-х столбцов внешних ключей таблице значений EAV, а также индекс GIN для столбца JSONB.

Обратите внимание, что масштаб является логарифмическим:
image
Видим что JSONB намного (> 50000-x) быстрее, чем EAV, если не использовать индексы, по причине, указанной выше. Обновления данных показало следующие результаты по времени (в мс). Обратите внимание, что индекс в столбце JSONB здесь не оказывает никакого влияния, так как мы не используем столбец свойств в критериях оценки. Когда мы индексируем столбцы c первичными ключами, разница почти пропадает, но JSONB все еще в 1,3 раза быстрее, чем EAV.

Для выбора данных на основе значения свойства мы получаем следующие результаты (обычный масштаб):
image

Но потом я увидел, что время для JSONB-запросов было одинаковым, это подтолкнуло меня на тот факт, что GIN-индекс не срабатывают. Можно заметить, что JSONB снова работает быстрее, чем EAV без индексов, но когда EAV с индексами – он все таки работает быстрее чем JSONB. Я использовал это в новом тесте, что оказало огромное влияние на время: всего 0,153 мс! Видимо, когда вы используете индекс GIN для столбца с заполненными свойствами, он действует только при использовании оператора включения @>. Это в 15000 раз быстрее, чем EAV, и в 25000 раз быстрее, чем оператор ->>.
Думаю, это было достаточно быстро!

Размер таблиц БД

Давайте сравним размеры таблиц при обоих подходов. В psql мы можем показать размер всех таблиц и индексов с помощью команды \dti+

image

При использовании подхода с JSONB используется 1817 МБ для таблицы и 318 МБ для индексов, что составляет 2,08 ГБ. Для подхода EAV размеры таблиц составляют около 3068 МБ, а индексы – до 3427 МБ, что в сумме дает 6,43 ГБ. Этот факт немного удивил меня, потому что мы храним имена свойств в каждом объекте JSONB.
Но все таки цифры говорят сами за себя: в EAV мы храним 2 целочисленных внешних ключа на значение атрибута, в результате чего получаем 8 байт дополнительных данных. Получается в 3 раза меньше! Кроме того, в EAV все значения свойств хранятся в виде текста, в то время как JSONB будет использовать числовые и логические значения внутри, где это возможно, в результате чего получается меньший объем.

Итоги

В целом, я думаю, что сохранение свойств сущностей в формате JSONB может значительно упростить проектирование и обслуживание вашей базы данных. Если вы выполняете много запросов, то все, что хранится в одной таблице с сущностью, действительно будет работать эффективнее. И тот факт, что это упрощает взаимодействие между данными, уже является плюсом, но и результирующая БД в 3 раза меньше по объему.

В некоторых случаях JSONB даже работает быстрее, чем EAV, что делает его еще лучше. Также, по сделанным тестом, можно сделать итог, что потери производительности очень незначительные. Однако этот эталонный тест, конечно, не охватывает все аспекты (например, сущности с очень большим количеством свойств, значительным увеличением числа свойств существующих данных,…), поэтому, если у вас есть какие-либо предложения о том, как их улучшить, пожалуйста, не стесняйтесь оставлять в комментариях!

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть