TL; DR: JSONB може спростити розробку схеми БД без шкоди продуктивності в запитах.
Запровадження
Наведемо класичний приклад, напевно, одного з найстаріших варіантів використання у світі реляційних БД (база даних): ми маємо сутність, і необхідно зберегти певні властивості (атрибути) цієї сутності. Але не всі екземпляри можуть мати однаковий набір властивостей, до того ж у майбутньому, можливе додавання ще властивостей.
Найпростіший шлях вирішення цієї проблеми - це створення стовпця в таблиці БД для кожного значення якості, і просто заповнювати ті, які необхідні для певного екземпляра сутності. Чудово! Проблема вирішена до того моменту, поки ваша таблиця не містить мільйони записів і у вас не виникне необхідність додати новий запис.
Розглянемо патерн EAV (), він зустрічається досить часто. Одна таблиця містить сутності (записи), інша таблиця містить імена властивостей (атрибутів), а третя таблиця пов'язує сутності зі своїми атрибутами і містить значення цих атрибутів для поточної сутності. Це дає можливість мати різні набори властивостей для різних об'єктів, а також додавати властивості “на льоту”, не змінюючи структури БД.
Тим не менш, я б не писав цю нотатку, якби не було недоліків у підході з використанням 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-доларовому складанні 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 – властивості.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Виглядає набагато простіше, чи не так? Потім було додано до таблиць сутностей (суб'єкта & 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. Тим не менш, нам не потрібно знати будь-який ідентифікатор заздалегідь. Подивившись приклад з 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 вам знадобиться два об'єднання для кожної властивості, яку ви хочете запросити. На мій погляд, наведені вище запити свідчать про велике спрощення в дизайні бази даних. Подивитися більше прикладів того, як писати запити до JSONB, можливо також у пості.
Тепер настав час поговорити про продуктивність.
Продуктивність
Щоб порівняти продуктивність, я використав у запитах, для підрахунку часу виконання. Кожен запит виконувався щонайменше тричі, тому що вперше планувальнику запитів потрібно більше часу. Спочатку я виконав запити без жодних індексів. Вочевидь, це слугувало перевагою JSONB, оскільки join, необхідних EAV, було неможливо використовувати індекси (поля зовнішнього ключа не індексувалися). Після цього я створив індекс для 2-х стовпців зовнішніх ключів таблиці значень EAV, а також індекс для стовпчика JSONB.
Оновлення даних показало наступні результати за часом (мс). Зверніть увагу, що масштаб є логарифмічним:
Бачимо, що JSONB набагато (> 50000-x) швидше, ніж EAV, якщо не використовувати індекси з причин, зазначених вище. Коли ми індексуємо стовпці з первинними ключами, різниця майже зникає, але 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, що робить його ще кращим. Однак цей еталонний тест, звичайно, не охоплює всі аспекти (наприклад, сутності з дуже великою кількістю властивостей, значним збільшенням числа властивостей існуючих даних…), тому, якщо у вас є якісь пропозиції про те, як їх покращити, будь ласка, не соромтеся залишати у коментарях!
Джерело: habr.com