Проектирование БД рекомендательной системы по подходу DB-first

В данной статье мы рассмотрим проектирование системы по подходу DB-first и то, какие проблемы он помогает не просто решить, а устранить как явление.

DB-first — это принцип первичности БД перед приложением. Не приложение, а БД владеет контрактом взаимодействия с ней.

Мы смотрим на БД как на сервис с таким же API, как REST, только средством коммуникации служит не JSON, а SQL, а контрактом является не OpenAPI/Swagger, а схема БД в виде DDL. Если БД — это сервис, значит приложения, взаимодействующие с ней, являются клиентами. Клиентов может быть множество. Сервис определяет контракт, который он способен обеспечить, а не клиент.

Приступим.

Постановка

Давайте спроектируем прототип рекомендательной системы музыкального сервиса в духе Яндекс-Музыки или Spotify.

Подобного рода системы опираются на сопоставление объектов по численным критериям. Критерии определяются в процессе профилирования/разметки, реализацию которого мы вынесем за скобки нашей задачи. Зафиксируем лишь, что система профилирования размечает композиции в пространстве из 5 координат, каждая из которых равномерно распределена в диапазоне от 0.0 до 1.0. Похожие композиции имеют близкие координаты, а непохожие — далёкие. Что значат сами координаты для нас неважно.

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

Нашу задачу сфокусируем на проектировании системы подбора рекомендаций. У нас есть список функциональных требований, и мы уже выбрали в качестве хранилища Postgres.

Функциональные требования

• Поиск похожих композиций по набору композиций

  • Набор композиций определяется списком идентификаторов

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

• Добавление/обновление профиля композиции по параметрам:

  • Идентификатор композиции

  • Разметка (embedding): координаты в пятимерном пространстве

• Удаление профиля композиции по идентификатору

Проектирование БД

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

Поиск похожих композиций по набору композиций

Исполнение любого запроса приводит к путешествию по сети. Это задержки и трафик. Поэтому всегда имеет смысл искать возможность выразить требуемую операцию с помощью одного запроса.

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

Сделаем набросок данного запроса:

with   reference as (     select       avg(f1) as f1,       avg(f2) as f2,       avg(f3) as f3,       avg(f4) as f4,       avg(f5) as f5     from composition     where       id = any($composition_ids)   ) select   match.id,   (     abs(match.f1 - reference.f1) +     abs(match.f2 - reference.f2) +     abs(match.f3 - reference.f3) +     abs(match.f4 - reference.f4) +     abs(match.f5 - reference.f5)    ) / 5 as distance from reference right join composition as match on   match.id <> all($composition_ids) and   match.f1 >= reference.f1 - $margin and match.f1 <= reference.f1 + $margin and   match.f2 >= reference.f2 - $margin and match.f2 <= reference.f2 + $margin and   match.f3 >= reference.f3 - $margin and match.f3 <= reference.f3 + $margin and   match.f4 >= reference.f4 - $margin and match.f4 <= reference.f4 + $margin and   match.f5 >= reference.f5 - $margin and match.f5 <= reference.f5 + $margin order by distance limit 100 

К сведению, параметрам запроса мы дали названия по формату: $имя_параметра.

В данном запросе мы подразумеваем следующую схему:

create table composition (   id uuid not null primary key,   f1 float4 not null,   f2 float4 not null,   f3 float4 not null,   f4 float4 not null,   f5 float4 not null, ); 

Не забываем про индекс:

create index composition_features on composition (   f1, f2, f3, f4, f5 ); 

Добавление/обновление профиля композиции по параметрам

Легко реализуется с помощью одного запроса с теми же требованиями к БД:

insert into composition (id, f1, f2, f3, f4, f5) values ($id, $f1, $f2, $f3, $f4, $f5) on conflict (id) do update set   f1 = $f1,   f2 = $f2,   f3 = $f3,   f4 = $f4,   f5 = $f5 

Удаление профиля композиции по идентификатору

Это совсем просто:

delete from composition where id = $id 

Подключаем инструментарий

Итак, у нас на выходе из стадии проектирования появился артефакт: схема БД и конечный набор запросов к ней. Ничего из этого мы, правда, ещё не проверяли. Так что, если бы мы хотели передать этот артефакт коллегам, скажем, для осуществления задач по интегрированию с данной БД из кода, стоило бы осуществить проверки. И вот настал момент для знакомства с инструментарием dbfirst.

На сайте dbfirst.ru запросим доступ по программе Preview и получим утилиту dbfirst для командной строки.

Создадим папку для нашего проекта и сохраним артефакты в виде следующей структуры файлов:

migrations/1.sql - Файл миграции, содержащий объявления схемы БД queries/select-similar-compositions.sql - Запрос на выборку композиций queries/upsert-composition.sql - Запрос на создание/обновление композиции queries/delete-composition.sql - Запрос на удаление композиции 

Добавим в корень проекта файл project.dbfirst-v1.yaml со следующим содержанием:

space: spookify name: recommendations version: 1.0.0 

Пока не будем вдаваться в детали того, что мы там написали.

Теперь, находясь в директории этого проекта, запустим утилиту dbfirst. И с порога мы получаем сообщение об ошибке:

syntax error at or near ")" SQL state code: 42601.  Context:   migrations/1 

Точно. В файле миграции у нас была лишняя запятая перед скобкой. Исправим:

create table composition (   id uuid not null primary key,   f1 float4 not null,   f2 float4 not null,   f3 float4 not null,   f4 float4 not null,   f5 float4 not null ); 

Прогоняем dbfirst заново и получаем новую ошибку:

relation "compositions" does not exist  Context:   queries/select-similar-compositions 

Правда. Мы же в схеме назвали таблицу composition. Внесём коррективы в запрос:

with   reference as (     select       avg(f1) as f1,       avg(f2) as f2,       avg(f3) as f3,       avg(f4) as f4,       avg(f5) as f5     from composition     where       id = any($composition_ids)   ) select   match.id,   (     abs(match.f1 - reference.f1) +     abs(match.f2 - reference.f2) +     abs(match.f3 - reference.f3) +     abs(match.f4 - reference.f4) +     abs(match.f5 - reference.f5)    ) / 5 as distance from reference right join composition as match on   match.id <> all($composition_ids) and   match.f1 >= reference.f1 - $margin and match.f1 <= reference.f1 + $margin and   match.f2 >= reference.f2 - $margin and match.f2 <= reference.f2 + $margin and   match.f3 >= reference.f3 - $margin and match.f3 <= reference.f3 + $margin and   match.f4 >= reference.f4 - $margin and match.f4 <= reference.f4 + $margin and   match.f5 >= reference.f5 - $margin and match.f5 <= reference.f5 + $margin order by distance limit 100 

Прогоняем утилиту и видим, что результат Ok.

Что это значит? Это значит, что dbfirst проверила нашу схему и наши запросы не только на синтаксическую корректность, но и на совместимость запросов со схемой. Теперь мы можем передать запросы в разработку интеграции с чистой совестью. Однако это отнюдь не всё, что нам может дать утилита dbfirst.

Генерация кода

Дополним файл project.dbfirst-v1.yaml до следующего:

space: spookify name: recommendations version: 1.0.0 artifacts:   - java-jdbc-v1   - haskell-hasql-v1 

И прогоним dbfirst ещё раз. Получаем снова Ok. Прекрасно! Но что это нам дало?

Обратите внимание на появившуюся папку artifacts. В ней вы найдёте SDK для языков Java и Haskell. Это библиотеки, предоставляющие полную интеграцию с предоставленными нами запросами. Структуры и типы данных выведены из схемы и запросов и осуществляется кодирование параметров и декодирование результатов. Всё это гарантированно совместимо со схемой и не требует тестирования.

Для наглядности вот кусок сгенерированного кода Java:

public ArrayList<SelectSimilarCompositionsResultRow> selectSimilarCompositions(UUID[] compositionIdsParam, double marginParam) throws SQLException {   Array compositionIdsParamArray = selectSimilarCompositionsStatement.getConnection().createArrayOf("uuid", compositionIdsParam);   selectSimilarCompositionsStatement.setArray(1, compositionIdsParamArray);   selectSimilarCompositionsStatement.setArray(2, compositionIdsParamArray);   selectSimilarCompositionsStatement.setDouble(3, marginParam);   selectSimilarCompositionsStatement.setDouble(4, marginParam);   selectSimilarCompositionsStatement.setDouble(5, marginParam);   selectSimilarCompositionsStatement.setDouble(6, marginParam);   selectSimilarCompositionsStatement.setDouble(7, marginParam);   selectSimilarCompositionsStatement.setDouble(8, marginParam);   selectSimilarCompositionsStatement.setDouble(9, marginParam);   selectSimilarCompositionsStatement.setDouble(10, marginParam);   selectSimilarCompositionsStatement.setDouble(11, marginParam);   selectSimilarCompositionsStatement.setDouble(12, marginParam);   selectSimilarCompositionsStatement.execute();    ArrayList<SelectSimilarCompositionsResultRow> list = new ArrayList<>();    try (ResultSet resultSet = selectSimilarCompositionsStatement.getResultSet()) {     while (resultSet.next()) {       UUID idCol = (UUID) resultSet.getObject(1);       double distanceColDouble = resultSet.getDouble(2);       OptionalDouble distanceCol = resultSet.wasNull() ? OptionalDouble.empty() : OptionalDouble.of(distanceColDouble);       list.add(new SelectSimilarCompositionsResultRow(idCol, distanceCol));     }   }    return list; } 

Полный код Java здесь.

Вот Haskell:

selectSimilarCompositions ::   -- | Specification of how to fold the result rows.   Fold Model.SelectSimilarCompositionsResultRow result ->   Statement Model.SelectSimilarCompositionsParams result selectSimilarCompositions (Fold step init extract) =   Statement sql encoder decoder True   where     sql =       "with\n\       \  reference as (\n\       \    select\n\       \      avg(f1) as f1,\n\       \      avg(f2) as f2,\n\       \      avg(f3) as f3,\n\       \      avg(f4) as f4,\n\       \      avg(f5) as f5\n\       \    from composition\n\       \    where\n\       \      id = any($1)\n\       \  )\n\       \select\n\       \  match.id,\n\       \  (\n\       \    abs(match.f1 - reference.f1) +\n\       \    abs(match.f2 - reference.f2) +\n\       \    abs(match.f3 - reference.f3) +\n\       \    abs(match.f4 - reference.f4) +\n\       \    abs(match.f5 - reference.f5) \n\       \  ) / 5 as distance\n\       \from reference\n\       \right join composition as match on\n\       \  match.id <> all($1) and\n\       \  match.f1 >= reference.f1 - $2 and match.f1 <= reference.f1 + $2 and\n\       \  match.f2 >= reference.f2 - $2 and match.f2 <= reference.f2 + $2 and\n\       \  match.f3 >= reference.f3 - $2 and match.f3 <= reference.f3 + $2 and\n\       \  match.f4 >= reference.f4 - $2 and match.f4 <= reference.f4 + $2 and\n\       \  match.f5 >= reference.f5 - $2 and match.f5 <= reference.f5 + $2\n\       \order by distance\n\       \limit 100"     encoder =       (Model.selectSimilarCompositionsParamsCompositionIds >$< (Encoders.param (Encoders.nonNullable (Encoders.array (Encoders.dimension foldl' (Encoders.element (Encoders.nullable Encoders.uuid))))))) <> (Model.selectSimilarCompositionsParamsMargin >$< (Encoders.param (Encoders.nonNullable Encoders.float8)))     decoder =       fmap extract . Decoders.foldlRows step init $         Model.SelectSimilarCompositionsResultRow           <$> (Decoders.column (Decoders.nonNullable Decoders.uuid))           <*> (Decoders.column (Decoders.nullable Decoders.float8)) 

Полный код Haskell здесь.

Здесь весь демонстрационный проект и сгенерированные артефакты.

Разработка сервера

На этом этапе остаётся лишь обернуть сгенерированный SDK в REST API. Как это делать, вы, наверняка, и так знаете.

Рекомендуем лишь обратить внимание на подход Spec-first, который таким же образом отбирая первичность у кода, позволяет вам генерировать код как сервера так и клиента из спецификации контракта в OpenAPI с гарантиями соответствия контракту. На эту тему вы найдёте множество отличных постов. Например, вот, здесь же на Хабре.

В итоге, единственный код, который вам останется написать самому — это конвертация между моделями API и БД. Оставим это за рамками данного поста.

Наблюдения

Интересная практика, не так ли? За буквально часовую работу проектирования мы получили качественные артефакты, которые требуются для любого проекта, интегрирующегося с базой данных, и которые руками программистов реализуются днями или неделями, притом, с нестабильным уровнем качества.

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

Иными словами, перед нами открылась возможность снять с программистов задачи по написанию интеграционного кода, получая его автоматически на выходе из стадии проектирования, а с тестировщиков — его проверку.

О dbfirst

Вы можете запросить доступ к Preview нашей SaaS-платформы на сайте dbfirst.ru. Также доступна опция внедрения ПО в ваши проекты.

Мы молодой проект и всегда рады обратной связи и предложениям. Для прямой связи пишите в Telegram: @wormholio.