Этот текст не про высокие технологии, не про покорение космоса, и не про изобретение нового драйвера или революционного композитного материала. И не про модный вайбкодинг. Не про запуск doom на контроллере. Это скромная история из серии ежедневной рутины, про ненормальное программирование, которое возможно будет полезно веб разработчикам для расширения кругозора.

Пусть НЕ простят меня администраторы БД, если случайно прочитают... Сегодня я расскажу про внутреннюю архитектуру JMatrixPlatform, в которой реализовано эффективное извлечение сложных многоуровневых графов, больших объёмов.

Проблема

Не знаю как вам, а мне становится грустно, когда с помощью JPA требуется извлечь список неких объектов с конкретными свойствами и только конкретными связями чтобы отобразить их в UI-таблице или выгрузить в отчёт. JPA даёт два режима, либо @Entity с фиксированным маппингом, где стратегия загрузки задаётся в трёх местах (FetchType в маппинге, @EntityGraph в запросе, глобальные настройки Hibernate), либо Dto-проекции, которые нужно писать вручную. Ни один из них не позволяет декларативно описать граф с точностью до атрибута на каждом уровне. @EntityGraph управляет только тем, какие связи грузить, но не составом атрибутов внутри них. В итоге в большинстве случаев приходится вручную собирать нативный SQL и также руками собирать граф.

Если вас всё устраивает, проходите дальше, вам будет не интересно :)

Цель

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

Я всего лишь хочу в свободном формате извлекать ровно те свойства и связи графа, которые мне нужны для конкретного случая. Если в текущей UI таблице мне не нужны файлы и комментарии, а в другой нужны, то зачем мне извлекать файлы и комментарии каждый раз?

Граф, пример:

МАРШРУТ
 ├── ШАГ 1
 │    ├── ЗАДАЧА 1.1
 │    │    ├── Замечания
 │    │    └── Пользователь
 │    └── Задача 1.2
 │         └── Замечания
 ├──ШАГ 2
 │    └── ЗАДАЧА 2.1
 │         └── Пользователь
 └── Рассматриваемый объект
      └── Файлы

Целевой класс-проекция для извлечения данных графа маршрута, с шагами, задачами и рассматриваемым объектом:

//В самом JSLObject и JSLRelatedConnection
//уже присутствуют базовые свойства (id, type, currentState и т.д.)

@NoArgsConstructor
@Getter
@Setter
// свойства и связи маршрута
public class JSLRouteAll extends JSLObject {
  @JSelectBasic(JBCode.class) private String code;
  @JSelectBasic(JBRelease.class) private String release;
  @JSelectBasic(JBDescription.class) private String description;

  // прямая связь с рассматриваемым объектом, минуя свойства самой связи
  @JSelectFromRelated(JRSRoute2Target.class) private Target target;

  // связи с шагами
  @JSelectToRelated(JRSRoute2Step.class) private List<StepRel> steps;

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства и связи рассматриваемого объекта
  public static class Target extends JSLObject {
    @JSelectBasic(JBCode.class) private String code;
    @JSelectBasic(JBDescription.class) private String description;
  }

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства связи Маршрут - Шаг
  public static class StepRel extends JSLRelatedConnection<Step> {
    @JSelectAttribute(JTRStepOrder.class) private Long order;
  }

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства и связи шага
  public static class Step extends JSLObject {
    // связи с задачами шага
    @JSelectToRelated(JRSRouteStep2Task.class) private List<TaskRel> tasks;
  }
  
  @NoArgsConstructor
  @Getter
  @Setter
  // свойства связи Шаг - Задача
  public static class TaskRel extends JSLRelatedConnection<Task> {
  }

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства и связи задачи
  public static class Task extends JSLObject {
    @JSelectAttribute(JTRApprover.class) private String approver;
    @JSelectBasic(JBDescription.class) private String description;

    // связи с комментариями
    @JSelectToRelated(JRSRoute2Comment.class) private List<CommentRel> comments;

  }

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства связи Задача - Комментарий
  public static class CommentRel extends JSLRelatedConnection<Comment> {
  }

  @NoArgsConstructor
  @Getter
  @Setter
  // свойства и связи комментария
  public static class Comment extends JSLObject {
    @JSelectBasic(JBOriginated.class) private Instant originated;
    @JSelectBasic(JBDescription.class) private String description;
    @JSelectAttribute(JTRReply.class) private String reply;

    @JSelectBasic(JBOwner.class) private UUID owner;
  }
}

Следует учесть, что в моём случае - связь это отдельная сущность, со своими свойствами. Поэтому между связанным объектом всегда присутствует отдельная "прослойка" JSLRelatedConnection, чтобы можно было извлекать и свойства связи (дата/время создания, автор и т.д.) и кастомные атрибуты.

Ну красота же, согласитесь?! Попробуйте вдумчиво прочитать класс ещё раз! Прямо в одном классе описаны все извлекаемые свойства и все нужные вложенные связи. Нет никаких FetchType, N+1, система эффективно извлечёт ровно то что указано. Если в другом месте понадобится только маршрут и его шаги - формируется отдельный класс:

@NoArgsConstructor
@Getter
@Setter
public class JSLRouteSteps extends JSLObject {
  @JSelectBasic(JBDescription.class) private String description;

  // связи с шагами
  @JSelectToRelated(JRSRoute2Step.class) private List<StepRel> steps;
  //StepRel переиспользуем из JSLRouteAll
}

Решение

Внимание! Всё описанное далее не является архитектурной рекомендацией. Используется только в рамках сложных PLM, MDM сценариев.

Классические @ManyToOne/@OneToMany решают эту задачу плохо, когда коллекций несколько: две и более List дают MultipleBagFetchException. Замена на Set обходит исключение, но оставляет декартово произведение, раздувающее результат. default_batch_fetch_size и @Fetch(SUBSELECT) смягчают N+1, но всё равно требуют ручной настройки и глубоких знаний внутренностей Hibernate - и рано или поздно ошибка всё равно случится и потребует рефакторинга.

Нужен подход, который даёт предсказуемое число запросов независимо от количества коллекций и позволяет описать проекцию графа декларативно. И здесь как раз будет кстати предварительная материализация идентификаторов для последующего извлечения их свойств и связей. Т.е. вместо того, чтобы пытаться вытянуть из БД основной объём, а затем через N+1 извлекать связи, сначала получить список основных id, затем сходить за нужными свойствами и связями подставляя список через IN (:ids). Но что, если таких сущностей будет порядка 5тыс.? Каждый раз передавать коллекцию из 5тыс. идентификаторов по сети выглядит весьма неразумным. Работать конечно будет, но неэффективно.

Есть решение получше? Похоже что да.

А что если не вытягивать в приложение список идентификаторов, а материализовать их непосредственно в отдельной таблице, а затем поочерёдно выполнить join связанных данных? Это даст возможность один раз сохранить требуемый список извлекаемых идентификаторов и затем повторно использовать его для извлечения различных требуемых свойств и связей. Бонусом появляется простая возможность универсальной пагинации, чтобы реализовать «бесконечную» подгрузку любых данных для UI.

--таблица хранения id
CREATE UNLOGGED TABLE temp_query (
  --uuid запроса
  request_id uuid not null,
  --материализованный uuid объекта
  object_id uuid not null,
  --для периодической очистки старых сессий по cron
  originated TIMESTAMP with timezone default now(),
  --для целых чисел
  sort_integer integer, 
  --для decimal
  sort_real decimal,
  --для дат
  sort_timestamp TIMESTAMP with timezone
);

--теперь можно просто сохранить найденные id и значение для сортировки
INSERT INTO temp_query (request_id, object_id, sort_integer)
SELECT '1f6e6faa-0048-41cd-b819-f18da02755f0'::uuid, id, page_count
FROM docs 
WHERE status = 'new';

--и затем поочерёдно извлекаем свойства и связи
--например комментарии
SELECT
  comments.text,
  comments.created_at,
  comments.author
FROM temp_query
JOIN LATERAL (
  SELECT text, created_at, author
  FROM comments
  WHERE temp_query.object_id = comments.id
  ORDER BY created_at DESC
  LIMIT 500
) comments on true
WHERE temp_query.request_id = '1f6e6faa-0048-41cd-b819-f18da02755f0'::uuid
AND (sort_integer, object_id) > (14, '7e806345-baf2-4f54-84ca-9422a621d37d'::uuid)
ORDER BY sort_integer ASC, object_id ASC;

-- где
-- (14, '7e806345-baf2-4f54-84ca-9422a621d37d'::uuid)
-- значение и uuid последней загруженной строки в таблице клиента

-- и т.д., например извлекаем файлы

Спросите, зачем отдельно сохранять идентификаторы, если можно выполнить join непосредственно к требуемой таблице сколько угодно раз? Да, можно!

Если условие поиска весьма простое и нужна всего пара join, то это будет эффективно. А если условие поиска ёмкое (найти все насосы, у которых давление >1МПа, мощность > 10Вт, тип центробежный, система UJA и т.д.) и например требуется 7 join таблиц? Каждый раз выполнять select * from ${name} where станет "узким горлышком". Поиск и insert идентификаторов выполняется "долго" только первый раз (для 50тыс.записей сотни мс), а все последующие запросы на извлечение связей и свойств укладываются буквально в десяток мс на полный круг для каждой следующей порции из 1000 объектов. В моём случае все запросы по умолчанию пагинируемые по дате создания (если не указано другое).

Ну допустим, а что за графом и многоуровневыми связями?

Теперь перейдём к извлечению полноценного графа любого уровня вложенности, с разным составом свойств на каждом уровне. В моём случае я пошёл тем же путём материализации related_id для каждого уровня извлекаемых связей в рамках одного request_id:

CREATE UNLOGGED TABLE temp_related(
  -- uuid запроса
  request_id uuid NOT NULL,
  -- uuid уровня связи (тут можно было указать сис.имя связи,
  -- но на разных уровнях может быть одна и таже связь,
  -- поэтому проще сгенерировать uuid)
  level_id uuid NOT NULL,
  -- uuid для прикрепления related объекта в java коллекции
  parent_id uuid NOT NULL,
  -- uuid связи, для извлечения свойств связи
  connection_id uuid NOT NULL,
  -- uuid объекта, для извлечения свойств
  related_id uuid NOT NULL
);

Касаемо реализации сборки сырых данных на уровне приложения - приводить листинг не вижу смысла, он весьма объёмный, но общая концепция включает классическую рефлексию для чтения аннотаций, затем формирование основного запроса с insert идентификаторов. Далее поочерёдно выполняется join требуемых данных. После каждой итерации создаются соответствующие объекты, проставляются их свойства и добавляются к соответствующим parent объектам. Ничего особенного. Обычная рутина. Что интересно - сам процесс последовательных join запросов оказался максимально простым для понимания и извлечения данных, без магии и без jsonb_agg, jsonb_build_array. На выходе всегда простой плоский список, который легко превращается в коллекцию объектов с свойствами и связями.

А как же чистка temp_query и temp_related?

Да, вы правы. В данном случае настроена автоматическая чистка старых записей cron'у:

DELETE FROM temp_query WHERE originated < now() - interval '8 hours';

Плюс для Vue3 компонента UI таблицы настроено асинхронное onUnmounted событие, которое всегда отправляет соответствующий запрос на чистку temp_query по request_id при закрытии таблицы или открытии другого компонента.

temp_related при этом чистится сразу после успешного построения графа (ну или при возникновении ошибки).

UNLOGGED TABLE не реплицируется же!

Верно. И не переживает падение сервера. В случае падения сессия пагинации потеряется и начнётся заново, что вполне приемлемо для UI таблицы (загрузится заново). А вот с кластеризацией БД сложнее... для пагинации потребуется маршрутизация запросов по request_id на ту ноду, где материализован temp_query. Остальные таблицы реплицируются штатно. Можно конечно сделать ALTER TABLE temp_query SET LOGGED, тогда нужно учесть WAL нагрузку при материализации id, но такой вариант я не рассматривал и не тестировал.

Ради чего всё это

В результате можно легко формировать любые DTO для извлечения данных и платформа под капотом сама прочитает и вернёт готовую коллекцию или конкретный объект:

  JQueryObject myRoutesQuery = JQueryObject.builder(Set.of(JTPRoute.TYPE), false)
      .setWhere(JSearch.builder()
          .and(JBPolicyState.BASIC, JQ.EQ, JLCRoute.Process.STATE)
          .and(JBOwner.ATTRIBUTE, JQ.EQ, ctx.getId())
          .build())
      .setMapper(JSLRouteAll.class)
      .setLimit(0)//все
      .setPageSize(100)//100 шт. на страницу
      .setSort(new JQuerySort(JBOriginated.BASIC, Order.ASC))
      .build();

  JQueryObjectResult result = JDomainObject.query(ctx, myRoutesQuery);
  result.getTotal()//общее количество

  List<JSLRouteAll> routes = result.getFirstPage();//100 шт.
  boolean exists = result.nextPage(ctx);//следующая страница
  result.getResults();// следующие 100 шт.
    
  //или
  
  JDomainObject route = new JDomainObject(routeId);
  
  JSLRouteAll routeInfo = route.select(ctx, JSLRouteAll.class);
  //где JSLRouteAll.class пример есть выше

Ещё есть удобные методы expand и expandWithRoot, которые используют ту же архитектуру материализации - извлекают всё дерево по указанной связи, на указанный уровень глубины (например структуру организации любой вложенности).

  JDomainObject companyDomain new JDomainObject(companyId);
  
  JSEUnit tree = companyDomain.expandWithRoot(
      ctx,
      Set.of(CompanyUnits.RELATIONSHIP),
      Set.of(CompanyUnit.TYPE),
      JDirection.TO,
      1,//recurseTo - только на 1 уровень
      JSEUnit.class,
      null, null,//connectionWhere, objectWhere
      0);//limit - 0 без лимита на каждом уровне
  
  List<JSEUnit> related = companyDomain.expand(
      ctx,
      Set.of(CompanyUnits.RELATIONSHIP),
      Set.of(CompanyUnit.TYPE),
      JDirection.TO,
      0,//recurseTo - 0 означает развернуть все уровни
      JSEUnit.class,
      null, null,//connectionWhere, objectWhere
      0);//limit - 0 без лимита

Заключение

Благодаря внутренней реализации ядра платформы, разработчики больше не думают про SQL и N+1 при извлечении данных. Можно легко сформировать сколько угодно JSL классов под каждую задачу и передать их для извлечения данных. Платформа всё сделает сама. Можно быть уверенным, что если вы запросили 10тыс. документов сразу с комментариями, то платформа извлекла их буквально "парой" точных запросов.

Также платформа предоставляет возможность пагинации из коробки, при этом само приложение не требует sticky session для пагинации и прекрасно кластеризуется.

Писалось под Ray Wilson - You Could Have Been Someone