Як використовувати обмеження JSON при роботі з PostgreSQL



Раніше у блозі на Хабре ми розповідали про розвиток нашого продукту — білінгу для операторів зв'язку «Гідра», а також розглядали питання роботи з інфраструктурою та використання нових технологій. Приміром, ми розглянули плюси Clojure і ситуації, коли варто і не варто використовувати MongoDB.

Сьогодні мова піде про роботу з JSON, і зокрема, про застосування обмежень. Цікавий матеріал на цю тему опублікував у своєму блозі розробник Магнус Хагандер (Magnus Hagander) — ми представляємо вашій увазі головні думки цього матеріалу.

Хагандер пише, що в ході спілкування в кулуарах одній з конференцій задумався про те, чи можна одночасно використовувати плюси SQL та NoSQL баз даних. Зокрема, співрозмовники запитували розробника про можливості застосування додаткових обмежень СУБД PostgreSQL. «Якщо думаєш, що це може спрацювати в конкретному випадку, швидше за все, так і буде», — переконаний Хагандер.

Проте якщо у випадку унікальних ключів UNIQUE або перевіряючих обмежень все досить просто, то при роботі з виключають обмеженнями все далеко не так просто.

Крім технічної сторони безумовно стоїть питання, чи варто взагалі цим займатися? Чим більше обмежень додано в JSON-дані, тим більш структурованими вони стають. З іншого боку є бази даних, у яких головною перевагою є наявність динамічних схем, однак у них все ще потрібні ключові індекси й обмеження (на відміну від PostgreSQL, де бінарний JSONB є неструктурованим навіть після індексації).

В PostgreSQL ключі і обмеження можна визначати як для колонок, так і безпосередньо для будь-яких виразів, при умови що вони можна буде змінити — результат залежить тільки від вхідних значень, але не від зовнішніх по відношенню до вираження ознак. Це так само стосується до JSONB.

Розглянемо стандартну таблицю, що містить JSON:

postgres=# CREATE TABLE jsontable (j jsonb NOT NULL);
CREATE TABLE

postgres=# CREATE INDEX j_idx ON jsontable USING gin(j jsonb_path_ops);
CREATE INDEX

Звичайно таке оголошення таблиці тільки з JSONB-полем на практиці дуже рідко виявляється хорошою ідей. Найчастіше в реальності існує більше інформації, і потрібно не одне поле JSONB — але для прикладу використаємо саме цей варіант.

Створюючи стандартні зворотні індекси, використовуючи jsonb_path_ops, можна максимально ефективно отримати повністю неструктуроване індексування в JSONB. Цей індекс не буде використовуватися в поточному прикладі, проте, в реальному розробки — це одна з головних причин застосування JSONB. Додамо деяку інформацію у вигляді реєстраційних записів для демонстрації обмежень. Для прикладу скористаємося полуфиксированной структурою (semi-fixed schema). Крім того, в якості ключа сортування тут використовується ідентифікатор користувача UUID — зазвичай так і роблять:

postgres=# INSERT INTO jsontable (j) VALUES ($${
"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f41",
"start": "2015-03-08 10:00",
"end": "2015-03-08 11:00",
"title": "test"
}$$);
INSERT 0 1

Перше, на що слід поглянути, чи є можливість перевірити унікальність поля uuid. Цей ідентифікатор повинен бути унікальним у всій таблиці. Однак, як показує практика, ця вимога не завжди дотримується, а значить, щоб переконатися у відсутності дублікатів, необхідно використовувати обмеження. Все досить просто:

postgres=# CREATE UNIQUE INDEX j_uuid_idx ON jsontable(((j->>'job')::uuid));
CREATE INDEX

Тут извлеченному значенню поля UUID створюється індекс (за допомогою унікального індексу, заснованого на B-дереві). Цей індекс можна використовувати для пошуку по ключу, так і для усунення дублювання ключів. З допомогою команди
j->>'job'
витягується текстове значення поля uuid, потім за допомогою команди
::uuid
здійснюється перетворення у вбудований тип унікальних ідентифікаторів.

В принципі, можна напряму створити обмеження на текстове поле, але набагато ефективніше використати для цього UUID, оскільки він обробляється як 128-бітове ціле число.

postgres=# INSERT INTO jsontable (j) VALUES ($${"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f41", "start": "2015-03-08 11:00", "end": "2015-03-08 12:00", "title": "test2"}$$);
ERROR: duplicate key value violates unique constraint "j_uuid_idx"
DETAIL: Key (((j ->> 'job'::text)::uuid))=(4e9cf085-09a5-4b4f-bc99-bde2d2d51f41) already exists.

Залишається ще одна проблема — відсутня перевірка існування даного поля. У таблицю можна вставляти записи, в яких просто не буде поля UUID. Це відбувається із-за того що оператор ->> за замовчуванням повертає NULL, що не викликає порушення унікальності (оскільки один NULL не дорівнює іншому NULL). Якщо потрібно усунути цей недолік, то можна реалізувати перевірочне обмеження CHECK:

postgres=# ALTER TABLE jsontable ADD CONSTRAINT uuid_must_exist CHECK (j ? 'job');
ALTER TABLE

З цим обмеженням більше не можна буде вставити в таблицю запису без поля UUID, а створення унікального індексу на попередньому етапі забезпечує відсутність дублікатів. Перетворення до типу UUID дозволяє забезпечувати коректність формату даних. Цей набір індексів і обмежень повторює функціональність класичної колонки певної
uuid NOT NULL UNIQUE
.

Однак існують і більш складні що виключають обмеження — саме тому в якості прикладу була обрана реєстраційна запис.
Основна ідея тут полягає в неможливості існування перекривають записів в таблиці при реалізованих обмеження. Для звичайних реляційних баз даних реалізувати такий механізм просто — потрібно лише створити обычное виключає обмеження.

Для JSON-даних можна застосувати схожий на викладений вище метод реалізації обмежень на основі функції вилучення. Однак, існує одна проблема — можна створювати обмеження для виразів, тільки якщо вони незмінні, а перетворення тексту до тимчасових міток изменяемо.

Причиною змінності перетворення тексту до тимчасових міток є перетворення величин, які залежать від зовнішніх значень, наприклад:

postgres=# SELECT 'today'::timestamp;
timestamp 
---------------------
2016-03-08 00:00:00
(1 row)

Це хороший приклад змінності такого перетворення, оскільки значення буде змінюватися щодня. А значення, яке використовується в якості індексу, не повинна бути змінено (до явного оновлення значення звичайно ж).

Якщо точно відомо, що в базі не міститься таких типів даних, то використання функції швидше за все буде незмінним. Якщо це точно відомо, то можна створити невелику інтерфейсну функцію, яка буде перетворювати вище вказане вираз в незмінне:

postgres=# CREATE FUNCTION immutable_tstamp(t text) RETURNS timestamp LANGUAGE sql IMMUTABLE AS $$SELECT t::timestamptz AT TIME ZONE 'UTC'$$;
CREATE FUNCTION

Ця функція реалізує незмінне перетворення до часової мітки, яка жорстко записана у тимчасовій зоні UTC. Крім того, має сенс реалізувати ще й обмеження на перевірки, що дозволить переконатися в тому, що в даних містяться лише фіксовані значення,

На наступному кроці функція поєднується з функцією tsrange() — це дозволяє створити засноване на вираженні виключає обмеження, яке забезпечує відсутність перекривають записів. Дана задача вирішується шляхом покрокового вилучення записів часу початку і закінчення з допомогою функції незмінного перетворення. Потім для створення дійсних тимчасових міток і передачі їх в виключає обмеження з допомогою оператора перекриття (&&) викликається функція
tsrange
.

postgres=# ALTER TABLE jsontable
ADD CONSTRAINT overlapping_times
EXCLUDE USING gist(
tsrange(
immutable_tstamp(j->>'start'),
immutable_tstamp(j->>'end')
) WITH &&
);
ALTER TABLE

В результаті ми заборонили додавання записів з перекриваються полями часу:

postgres=# insert into jsontable (j) values ($${
"uuid": "4e9cf085-09a5-4b4f-bc99-bde2d2d51f43",
"start": "2015-03-08 10:30",
"end": "2015-03-08 11:30",
"title": "test"
}$$);
ERROR: conflicting key value violates exclusion constraint "overlapping_times"
DETAIL: Key (tsrange(immutable_tstamp(j ->> 'start'::text), immutable_tstamp(j ->> 'end'::text)))=(["2015-03-08 10:30:00","2015-03-08 11:30:00")) conflicts with existing key (tsrange(immutable_tstamp(j ->> 'start'::text), immutable_tstamp(j ->> 'end'::text)))=(["2015-03-08 11:00:00","2015-03-08 12:00:00")).

За допомогою функції і обмеження ми реалізували функціональність звичайних виключають обмежень, обумовлених як
EXCLUDE USING gist(r WITH &&)
у разі, якщо в колонці r доступний діапазон.

Так що відповідь на початкове питання «чи можна використовувати переваги одночасно SQL та NoSQL СУБД?» — так. Принаймні поки що використовується СУБД, що володіє можливостями обох типів, а саме PostgreSQL.

Важливий момент — якщо, як у нашому прикладі, схема відома, система буде працювати швидше і ефективніше завдяки зберіганню полів в реляційній формі. Безумовно, для роботи необхідно використовувати правильний інструмент, тому застосовувати JSON необхідно тільки, якщо схема є, принаймні полудинамической. При цьому можливість оголошення обмежень для частини схеми дуже корисна навіть у тому випадку, якщо дані не реляційні, і все працює не так швидко. Зрештою вся суть динамічної схеми — це її гнучкість.

Наш досвід

Ми використовуємо JSON в PostgreSQL в декількох проектах. Зокрема, в проекті для управління бізнес-процесами ми зберігаємо в таких полях значення змінних процесу, структура якого визначається в момент впровадження продукту, а не під час його розробки.

Робота з полями таблиці здійснюється через адаптер фреймворку Ruby On Rails для PostgreSQL. Читання і запис працюють в нативному для Ruby режимі — через хеші та списки. Таким чином, ви можете працювати з даними з поля без додаткових перетворень.

Стаття описує важливий аспект цілісності збережених даних, але найчастіше крім простого зберігання потрібно здійснювати пошук по них, для чого в PostgreSQL також присутні функціональні індекси, які можуть істотно підвищити операції доступу до даних для частих запитів.

Інші технічні статті в нашому блозі:



Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.