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'
WHERE data.form_id=:id 
ORDER BY cast(field_age.value as int)

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

SELECT data.* 
FROM form_data_json data
WHERE data.values->'gender'->>'value' = 'male'
AND data.form_id=:id
SELECT data.* 
FROM form_data_json data
WHERE data.values->'gender'->>'value' = 'female'
AND cast(data.values->'deadline'->>'value' as date) < now()
AND data.form_id=:id
ORDER BY cast(data.values->'age'->>'value' as int)

Чтобы добавить новые поля формы для 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

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