Замена 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, что делает его еще лучше. Однако этот эталонный тест, конечно, не охватывает все аспекты (например, сущности с очень большим количеством свойств, значительным увеличением числа свойств существующих данных,…), поэтому, если у вас есть какие-либо предложения о том, как их улучшить, пожалуйста, не стесняйтесь оставлять в комментариях!