[Перевод] Замена EAV на JSONB в PostgreSQL
TL; DR: JSONB может значительно упростить разработку схемы БД без ущерба производительности в запросах.
Введение
Приведем классический пример, наверное, одного из старейших вариантов использования в мире реляционных БД (база данных): у нас есть сущность, и необходимо сохранить определенные свойства (атрибуты) этой сущности. Но не все экземпляры могут имеют одинаковый набор свойств, к тому же в будущем, возможное добавление ещё свойств.
Самый простой путь решения этой проблемы — это создание столбца в таблице БД для каждого значение свойства, и просто заполнять те, которые нужны для определенного экземпляра сущности. Отлично! Проблема решена… до того момента, пока ваша таблица не содержит миллионы записей и у вас не возникнет необходимость добавить новую запись.
Рассмотрим паттерн EAV (Entity-Attribute-Value), он встречается достаточно часто. Одна таблица содержит сущности (записи), другая таблица содержит имена свойств (атрибутов), а третья таблица связывает сущности с их атрибутами и содержит значение этих атрибутов для текущей сущности. Это дает вам возможность иметь разные наборы свойств для разных объектов, а также добавлять свойства «на лету», не изменяя структуры БД.
Тем не менее, я бы не писал эту заметку, если бы не было недостатков в подходе с использованием EVA. Так, например, для получения одной или нескольких сущностей, которые имеют по 1 атрибуту требуется 2 join-на (объединения) в запросе: первый — объединение с таблицей атрибутов, второй — объединение с таблицей значений. Если сущность имеют 2 атрибуты, то нужно уже 4 join-на! Кроме того, все атрибуты обычно хранятся в виде строк, что приводит к приведению типов, как для результата, так и для условия WHERE. Если вы пишете много запросов, то это достаточно расточительно, с точки зрения использования ресурсов.
Несмотря на эти очевидные недостатки, EAV уже давно используется для решения такого рода проблем. Это были неизбежное недостатки, и лучшей альтернативы просто не было.
Но затем в PostgreSQL появилась новая «технология»…
Начиная с PostgreSQL 9.4, был добавлен тип данных JSONB для хранения двоичных данных JSON. Хотя хранение JSON в этом формате обычно занимает немного больше места и времени, чем простой текстовый JSON, выполнение операций с ним происходит намного быстрее. Также JSONB поддерживает индексирование, что делает запросы к ним еще быстрее.
Тип данных 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. Таким образом, мы получили несколько разных типов данных среди всего набора свойств. Пример данных:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Итак, теперь у нас есть одинаковые данные, для двух вариантов. Давайте начнем сравнивать реализации в работе!
Упрощение дизайна
Ранее уже было сказано, что дизайн БД был значительно упрощен: одна таблица, за счет использования столбца 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';
Я думаю, что мы можем согласиться с тем, что второе является более коротким (без join-ов!), и соответственно более читабельным. Здесь победа JSONB! Мы используем оператор JSON →>, чтобы получить цвет как текстовое значение из объекта JSONB. Существует также второй способ достижения того же результата в модели 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.
Обновления данных показало следующие результаты по времени (в мс). Обратите внимание, что масштаб является логарифмическим: 
Видим что JSONB намного (> 50000-x) быстрее, чем EAV, если не использовать индексы, по причине, указанной выше. Когда мы индексируем столбцы c первичными ключами, разница почти пропадает, но JSONB все еще в 1,3 раза быстрее, чем EAV. Обратите внимание, что индекс в столбце JSONB здесь не оказывает никакого влияния, так как мы не используем столбец свойств в критериях оценки.
Для выбора данных на основе значения свойства мы получаем следующие результаты (обычный масштаб):
Можно заметить, что JSONB снова работает быстрее, чем EAV без индексов, но когда EAV с индексами — он все таки работает быстрее чем JSONB. Но потом я увидел, что время для JSONB-запросов было одинаковым, это подтолкнуло меня на тот факт, что GIN-индекс не срабатывают. Видимо, когда вы используете индекс GIN для столбца с заполненными свойствами, он действует только при использовании оператора включения @>. Я использовал это в новом тесте, что оказало огромное влияние на время: всего 0,153 мс! Это в 15000 раз быстрее, чем EAV, и в 25000 раз быстрее, чем оператор →>.
Думаю, это было достаточно быстро!
Размер таблиц БД
Давайте сравним размеры таблиц при обоих подходов. В psql мы можем показать размер всех таблиц и индексов с помощью команды \dti+
Для подхода EAV размеры таблиц составляют около 3068 МБ, а индексы — до 3427 МБ, что в сумме дает 6,43 ГБ. При использовании подхода с JSONB используется 1817 МБ для таблицы и 318 МБ для индексов, что составляет 2,08 ГБ. Получается в 3 раза меньше! Этот факт немного удивил меня, потому что мы храним имена свойств в каждом объекте JSONB.
Но все таки цифры говорят сами за себя: в EAV мы храним 2 целочисленных внешних ключа на значение атрибута, в результате чего получаем 8 байт дополнительных данных. Кроме того, в EAV все значения свойств хранятся в виде текста, в то время как JSONB будет использовать числовые и логические значения внутри, где это возможно, в результате чего получается меньший объем.
Итоги
В целом, я думаю, что сохранение свойств сущностей в формате JSONB может значительно упростить проектирование и обслуживание вашей базы данных. Если вы выполняете много запросов, то все, что хранится в одной таблице с сущностью, действительно будет работать эффективнее. И тот факт, что это упрощает взаимодействие между данными, уже является плюсом, но и результирующая БД в 3 раза меньше по объему.
Также, по сделанным тестом, можно сделать итог, что потери производительности очень незначительные. В некоторых случаях JSONB даже работает быстрее, чем EAV, что делает его еще лучше. Однако этот эталонный тест, конечно, не охватывает все аспекты (например, сущности с очень большим количеством свойств, значительным увеличением числа свойств существующих данных, …), поэтому, если у вас есть какие-либо предложения о том, как их улучшить, пожалуйста, не стесняйтесь оставлять в комментариях!
