Проектирование БД рекомендательной системы по подходу DB-first
В данной статье мы рассмотрим проектирование системы по подходу DB-first и то, какие проблемы он помогает не просто решить, а устранить как явление.
DB-first — это принцип первичности БД перед приложением. Не приложение, а БД владеет контрактом взаимодействия с ней.
Мы смотрим на БД как на сервис с таким же API, как REST, только средством коммуникации служит не JSON, а SQL, а контрактом является не OpenAPI/Swagger, а схема БД в виде DDL. Если БД — это сервис, значит приложения, взаимодействующие с ней, являются клиентами. Клиентов может быть множество. Сервис определяет контракт, который он способен обеспечить, а не клиент.
Приступим.
Постановка
Давайте спроектируем прототип рекомендательной системы музыкального сервиса в духе Яндекс-Музыки или Spotify.
Подобного рода системы опираются на сопоставление объектов по численным критериям. Критерии определяются в процессе профилирования/разметки, реализацию которого мы вынесем за скобки нашей задачи. Зафиксируем лишь, что система профилирования размечает композиции в пространстве из 5 координат, каждая из которых равномерно распределена в диапазоне от 0.0 до 1.0. Похожие композиции имеют близкие координаты, а непохожие — далёкие. Что значат сами координаты для нас неважно.
К сведению, такого рода разметку, как правило, осуществляют с помощью нейросетей, тренируя их на больших данных о поведении людей. Такими данными может послужить множество плейлистов, составленных людьми самостоятельно, а доступ к ним можно получить на большинстве сервисов онлайн-музыки.
Нашу задачу сфокусируем на проектировании системы подбора рекомендаций. У нас есть список функциональных требований, и мы уже выбрали в качестве хранилища Postgres.
Функциональные требования
Поиск похожих композиций по набору композиций
Набор композиций определяется списком идентификаторов
Поиск должен выдавать набор композиций с координатами близкими к среднему значению координат композиций, предоставленных на входе
Добавление/обновление профиля композиции по параметрам:
Удаление профиля композиции по идентификатору
Проектирование БД
Начнём с определения перекоса нагрузки, так как это предопределяет принципиальное устройство БД. Здравый смысл подсказывает, что операций поиска будет осуществляться на порядки больше, чем операций добавления композиций, а потому систему нужно оптимизировать под поиск. Значит и дизайн БД имеет смысл начать именно с этой операции, и уже под неё подстроить схему и остальные операции.
Поиск похожих композиций по набору композиций
Исполнение любого запроса приводит к путешествию по сети. Это задержки и трафик. Поэтому всегда имеет смысл искать возможность выразить требуемую операцию с помощью одного запроса.
Можем ли мы реализовать требование поиска похожих композиций через один запрос? Да, мы можем осуществить выборку по следующему алгоритму: по представленным идентификаторам композиций вывести их среднюю разметку и по диапазону вокруг этой разметки найти другие композиции, отсортировав по увеличению дистанции. Дистанция — это среднее значение разницы по всем измерениям.
Сделаем набросок данного запроса:
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 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 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.
