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-шку и наваяла генератор рандомно-фейковой забивки. + одну предзаданную форму с разными типами данных

Забивка:

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

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

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

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

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

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

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

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

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

В процессе, решила добавить еще баз с версиями — от 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

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