Использование JSONB-полей вместо EAV в PostgreSQL

[MonkAlex]

Эх, я думал будет сравнение производительности. Заголовок заманил, а внутри как на конференциях - "ну мы не проверяли другие варианты, сделали только такой".

[Bohelene]

Спасибо за комментарий. Планируем продолжить тему, т.к. действительно есть что обсудить и одной статьей все вопросы не закрыть.

[Jack444]

Я обожаю постгрес, когда mvp по быстрому надо сделать, одной таблицы достаточно.

CREATE TABLE IF NOT EXISTS DataObject (
    ids JSONB,
    extra JSONB,
    space VARCHAR(50) NOT NULL,
    id BIGSERIAL,
    PRIMARY KEY (space, id)
) PARTITION BY LIST(space);

Аля NoSql, в ids джейсон на эту же таблицу {space: id} также и связи с внешними сервисами могут хранится, вся логика и описание extra данных на уровне выше в бекенде реализуется, в базе остаётся только тригеры на спейсы настроить для обработки ON DELETE/UPDATE событий.

[silentz]

А можно пример данных?

[PrinceKorwin]

и размер таблицы, пожалуйста.

[Hyperplasia]

Ужас. Никогда не делайте так, пожалуйста. JSON в РСУБД - это плохо. EAV, как принцип хранения всех основных данных в РСУБД - тоже плохо. В JSON хранят данные, которые нативно сохраняются с источника так: кликстримы, показатели с оборудования, конфиги и тд, с последующим преобразованием его в нормальный вид для аналитики, например. EAV используют в основном для хранения: сложных справочников многоуровневых, быстро меняющихся структур или плохо структурированных разряженных данных. Хранение данных по ФЛ/ИП/ЮЛ хорошо решается стандартными способами.

[pankraty]

JSON в РСУБД - это плохо. EAV, как принцип хранения всех основных данных в РСУБД - тоже плохо.

Такая аргументация нормальна при общении с трехлеткой. Для людей постарше желательно давать какое-то обоснование.

Как по мне, так наоборот, разветвленные сущности с переменным составом полей, которые обычно извлекаются целиком, намного удобнее и эффективнее хранить в JSON-е, чем делать 20 left join-ов при сильно нормализованной форме или танцевать с таблицами атрибутов, пытаясь хоть как-то выжать из них перформанс. Ваш опыт может отличаться от моего, но на доводах "это плохо, потому что плохо" дискуссию не построить.

[Hyperplasia]

Я и не планировал дискуссию, но давайте попробуем

Модель данных:

1. Основные данные по ИП/ЮЛ/ФЛ в большинстве случаев не нуждаются в нормализации и могут хранится либо в одной таблице (как по мне самый плохой вариант), так и в нескольких, например: organization, person, person_relation (для связи ФЛ c ЮЛ или с другими сущностями при необходимости). Возможно понадобится несколько сателитных сущностей для хранения данных "один ко многим" - но можно и без них обойтись.

2. Если Вас пугает большое кол-во джойнов - то тут можно воспользоваться view - которые замечательно профилируются и кэшируются в памяти и использовать их, как инфослой.

СУБД:

1. При использовании атрибутов (вместо JSON) - мы можем в БД использовать PK/FK c constraints - для обеспечения целостности данных.

2. Типизация данных позволяет экономить ресурсы при хранении и запросах + дополнительная валидация данных на уровне БД.

3. Масштабируемость - с нативными таблицами проще организовывать партицирование или шардирование.

4. Сортировка и группировка по нативным таблицам явно выигрывает по скорости у варианты с JSON (даже при наличии GIN).

5. С большей вероятностью часть JSON уйдут (при их разрастании) в TOAST - что создаст дополнительные расходы на их чтение и изменение.

[art000109]

О какой версии postgresql (или её форка) вы говорите, касательно скорости и производительности?

[Hyperplasia]

В 14 версии в последний раз тестировал JSONB - но вроде бы и в новых версиях ничего не поменялось. Архитектурно в pg работа с jsonb всегда будет медленнее, чем с нативными таблицами.

[PrinceKorwin]

в каждой версии они улучшают работу с JSONB. но не факт, что именно на ваших сценариях будет заметно улучшение.

в своих сервисах мы решили использовать JSONB для входящих неструктурированных данных + как способ "безболезненного" расширения атрибутивной модели - все то, что не требует ни индексации, ни участия в запросах.

[PPE_RT]

"Поиск произвольного набора клиентов по строчным данным, например, по фамилии или по адресу проживания укладывается в 40 секунд" - погодите, это где такой расслабленный SLA допускается? Обычно поиск клиента это 1-2 секунды

[SIGMA_team]

Добрый день! Речь в статье про 40 секунд шла в контексте наших технических ограничений.
То есть 40 секунд это максимально допустимое время ожидания ответа фронта от бэка.
Фактически запросы выполняются быстрее. Скорость зависит от количества заполненных данных для поиска.

[avvensis]

Что вы будете делать, если вам понадобится атрибут, который ссылается на другую сущность в БД? К примеру, атрибут клиента, который ссылается на услугу или другого клиента. Судя по статье вы храните там только скалярные типы (в перспективе составные), но потребность в ссылках на другие сущности рано или поздно появится. Опять же к примеру, для ЮЛ нужно будет указывать ФЛ лицо руководителя или аффилированные ЮЛ. Если идентификаторы хранить в JSONB, то теряется целостность БД, а если городить таблицы связей для отдельных атрибутов, то теряется динамичность самих атрибутов.

Поиск по атрибутам в 40 сек. это явно не повод для гордости и сущее мучение для пользователей вашей системы. Я, когда столкнулся с подобной задачей, вынес поиск и листинг в отдельный поисковый индекс, а операции CRUD оставил за БД. Такой подход усложняет поддержание целостности данных в системе, но значительно сохраняет время её пользователям.

[SIGMA_team]

У каждого подхода есть свои плюсы и минусы. Кто-то предпочитает использовать классический подход и на каждый атрибут заводить таблицу, мы пошли альтернативным путем, который тоже работает. Ответ про 40 сек. дали выше.