Postgres jsonb vs property-value table

Задача дать пользователям возможность создавать свои формы для ввода или дополнять кастомными полями какие-нибудь сущности встречатся довольно часто. И это часто вызывает вопрос — как потом хранить и использовать данные этих заполненных форм.

Наиболее распространенные варианты

  • таблица типа (entity_id, property_name, value),
  • более продвинутый вариант — для корректности сортировок и снижения преобразования типов — сделать отдельные столбцы значений на каждый тип (entity_id, property_name, value_bool, value_str, value_int, value_float… etc)
  • ну и конечно NoSql

Первой мыслью пришло в голову просто взять jsonb, но учитывая что в моём кейсе точно известно, что пользователи смогут задавать свои фильтры/сортировки/группировки по введенным данным, закрался тревожный червячок сомнения — не подведет ли json? Поэтому решила наколбасить на коленке проверялку.

Сделала вот такую структурку (ориентируясь на реальный кейс — описания полей в json по умолчанию, не полноценный EAV)

Так как хотелось проверить на достаточно существенном объёме данных, решила подключить yii-шку и наваяла генератор рандомно-фейковой забивки. + одну предзаданную форму с разными типами данных

$fields = [
['name' => 'age', 'type' => 'int'],
['name' => 'gender', 'type' => 'str'],
['name' => 'department', 'type' => 'json'],
['name' => 'deadline', 'type' => 'date'],
['name' => 'active', 'type' => 'bool'],
['name' => 'partner', 'type' => 'userfk']
];

Забивка:

if ($form['name'] === 'test_form') {
$values = [
'age' => [
'value' => $this->faker->numberBetween(10, 75),
'type' => 'int'
],
'gender' => [
'value' => $this->faker->randomElement(['male','female']),
'type' => 'str'
],
'department' => [
'value' => $this->faker
->randomElements(
['A', 'B', 'C', 'D', 'E'],
$this->faker->randomElement([1, 2, 3])
),
'type' => 'json',
],
'deadline' => [
'value' => $this->faker->dateTimeBetween('-3month', '+3month')->format('Y-m-d'),
'type' => 'date',
],
'active' => [
'value' => $this->faker->boolean,
'type' => 'bool'
],
'partner' => [
'value' => $this->faker->randomElement($this->getUserIds()),
'type' => 'userfk'
],
];
}

, на которую и делала проверочные запросы.

Как работать с такими данными?

В случае таблицы с полями и значениями, чтобы произвести сортировки/фильтрации нам необходимо приджойнить эту таблицу с каждым полем, над которым нужно произвести операцию. Например — для выбора пользователей мужчин запрос будет выглядеть как

SELECT data.* 
FROM form_data_raw data
INNER JOIN form_data_values field_gender
on data.id = field_gender.data_id
AND (
field_gender.field ='gender'
AND field_gender.value ='male'
)
WHERE data.form_id=:id

Для выбора всех женщин с просроченным дедлайном и сортировкой по возрасту запрос будет выглядеть как

SELECT data.* 
FROM form_data_raw data
INNER JOIN form_data_values field_gender
on data.id = field_gender.data_id
AND (
field_gender.field ='gender'
AND field_gender.value ='female'
)
INNER JOIN form_data_values field_deadline
on data.id = field_deadline.data_id
AND (
field_deadline.field ='deadline'
AND field_deadline.value::date < now()
)
INNER JOIN form_data_values field_age
on data.id = field_age.data_id
AND field_age.field ='age'

Те же запросы в случае хранения данных в jsonb будут выглядеть как

SELECT data.* 
FROM form_data_json data
WHERE data.values->'gender'->>'value' = 'male'
AND data.form_id=:id

Чтобы добавить новые поля формы для jsonb запрос будет выглядеть как

UPDATE form_data_json data
SET values = values || '{"float": {"type": "numeric", "value": null}, "state": {"type": "str", "value": "open"}}'
WHERE data.form_id=:id

Для изменения значения поля можно воспользоваться функцией jsonb_set

UPDATE form_data_json data
SET values = jsonb_set(values, '{float, type}', '"float"')
WHERE data.form_id=:id

Для удаления поля

UPDATE form_data_json data
SET values = values - 'float'
WHERE data.form_id=:id

Для теста я использовала как совсем простые запросы, так и более сложные — подсчитать кол-во участников разного пола в зависимости от активности, выбрать участников не входящих в заданные отделы, с возрастом выше чем средний по всем анкетам формы и т.п. Все можно посмотреть тут

В процессе, решила добавить еще баз с версиями — от 10 до текущей 13. Собственно репа всего этого безобразия и табличка с результатами — https://github.com/Insolita/pgjsonb_test

И результаты меня весьма удивили — потому как по этим тестам Jsonb не просто справляется, а справляется гораздо круче.

Причем без всяких индексов — я пробовала btree и hash — никаких преимуществ не дает… ну а gin по типу запросов в принципе не походит. (GIN эффективен при активном использовании проверок на вхождение c операторами @> ?&, ?|<@)А простая табличка field-value порой показывает результаты лучше, или как минимум не так уж сильно хуже в моём кейсе, чем со значениями разбитыми по типам. (Хотя на этот счет можно было бы поиграть с индексацией). Ну и базы из докера “как есть” с дефолтным конфигом.

Добавление и удаление полей — единственные потенциально “дорогостоящие” операции для варианта хранения в jsonb. Если в случае таблицы полей-значений мы просто добавляем или удаляем строки, при хранении данных в jsonb поле эти операции производятся через UPDATE таблицы и скорость напрямую зависит от количества записей, но так как это обычно гораздо более редкие операции — вполне приемлемо.

Так же можно заметить как по-немножечку улучшается результат с новыми версиями postgres

Вот… как-то так. Червячок успокоился.

#php,#yii,#laravel,#javascript, #python,#linux,#archlinux,#vue

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store