- Часть 1: проектируем каркас базы
- Часть 2: секционируем «наживую»
Наша база будет не такой масштабной и распределенной, как у ВКонтакте или Badoo, а «чтобы было», но было хорошо — функционально, быстро и умещалось на одном сервере PostgreSQL — чтобы можно было развернуть отдельный экземпляр сервиса где-то на стороне, например.
Поэтому не будем затрагивать вопросы шардинга, репликации и геораспределенных систем, а сосредоточимся на схемных решениях внутри БД.
Шаг 1: Немного бизнес-специфики
Наш обмен сообщениями мы будем проектировать не абстрактно, а встраивать в окружение корпоративной соцсети. То есть люди у нас не «просто переписываются», а общаются между собой в контексте решения определенных бизнес-задач.
А какие бывают задачи у бизнеса?.. Посмотрим на примере Василия — руководителя отдела разработки.
- «Николай, вот по этой задаче патч нужен уже сегодня!»
Значит, переписка может вестись в контексте какого-то документа. - «Коля, го вечером в доту?»
То есть даже у одной пары собеседников общение одновременно может вестись по разным темам. - «Петр, Николай, посмотрите в аттаче прайс на новый сервер.»
Так, у одного сообщения может быть несколько адресатов. При этом сообщение может содержать прикрепленные файлы. - «Семен, и ты тоже взгляни.»
И должна быть возможность в уже существующую переписку пригласить нового участника.
Остановимся пока на этом перечне «очевидных» потребностей.
Без понимания прикладной специфики задачи и задаваемых ей ограничений, спроектировать эффективную схему БД для ее решения практически невозможно.
Шаг 2: Минимальная логическая схема
Схемно пока все получается очень похоже на email-переписку — традиционный инструмент ведения бизнеса. Таки да, «алгоритмически» многие задачи бизнеса похожи друг на друга, поэтому и инструменты для их решения будут структурно сходны.
Давайте зафиксируем уже получившуюся логическую схему отношений сущностей. Для простоты понимания нашей модели воспользуемся самым примитивным вариантом отображения ER-модели без усложнений UML или IDEF-нотаций:
В нашем примере персона, документ и бинарное «тело» файла — это «внешние» сущности, которые самостоятельно существуют и без нашего сервиса. Поэтому просто будем воспринимать их в дальнейшем как некоторые ссылки «куда-то» по UUID.
Рисуйте схемы как можно проще — большинство тех, кому вы их будете показывать, не являются экспертами в чтении UML/IDEF. Но — рисуйте обязательно.
Шаг 3: Набрасываем структуру таблиц
Поскольку сообщения у нас пишут много людей сразу, часть из них вообще могут делать это в оффлайн-режиме, то самый простой вариант — использовать UUID в качестве идентификаторов не только для внешних сущностей, но и для всех объектов внутри нашего сервиса. Причем генерировать их можно даже на клиентской стороне — это поможет нам поддержать отправку сообщений при кратковременной недоступности БД, а вероятность коллизии крайне мала.
Черновая структура таблиц в нашей базе примет вот такой вид:
CREATE TABLE "Тема"(
"Тема"
uuid
PRIMARY KEY
, "Документ"
uuid
, "Название"
text
);
CREATE TABLE "Сообщение"(
"Сообщение"
uuid
PRIMARY KEY
, "Тема"
uuid
, "Автор"
uuid
, "ДатаВремя"
timestamp
, "Текст"
text
);
CREATE TABLE "Адресат"(
"Сообщение"
uuid
, "Персона"
uuid
, PRIMARY KEY("Сообщение", "Персона")
);
CREATE TABLE "Файл"(
"Файл"
uuid
PRIMARY KEY
, "Сообщение"
uuid
, "BLOB"
uuid
, "Имя"
text
);CREATE TABLE theme(
theme
uuid
PRIMARY KEY
, document
uuid
, title
text
);
CREATE TABLE message(
message
uuid
PRIMARY KEY
, theme
uuid
, author
uuid
, dt
timestamp
, body
text
);
CREATE TABLE message_addressee(
message
uuid
, person
uuid
, PRIMARY KEY(message, person)
);
CREATE TABLE message_file(
file
uuid
PRIMARY KEY
, message
uuid
, content
uuid
, filename
text
);Самое простое при описании формата — начинать «раскручивать» граф связей от таблиц, которые не ссылаются сами ни на кого.
Шаг 4: Выясняем неочевидные потребности
Все, мы спроектировали базу, в которую можно отлично писать и как-то читать.
Давайте поставим себя на место пользователя нашего сервиса — что мы захотим делать с его помощью?
- Последние сообщения
Это хронологически отсортированный по различным признакам реестр «моих» сообщений. Где я один из адресатов, где я автор, где мне написали, а я не ответил, где не ответили мне, ... - Участники переписки
Кто вообще участвует в этом длинном-длинном чате?
Наша структура позволяет решить обе эти задачи «вообще», но быстро — нет. Проблема в том, что для сортировки в рамках первой задачи невозможно создать индекс, подходящий для каждого из участников (и придется извлекать все записи), а для решения второй необходимо извлекать все-все сообщения по теме.
Непредусмотренные пользовательские задачи могут поставить жирный крест на производительности.
Шаг 5: Разумная денормализация
Обе наши проблемы помогут решить дополнительные таблицы, в которые мы будем дублировать часть данных, необходимых для формирования на них подходящих к нашим задачам индексов.
CREATE TABLE "РеестрСообщений"(
"Владелец"
uuid
, "ТипРеестра"
smallint
, "ДатаВремя"
timestamp
, "Сообщение"
uuid
, PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
);
CREATE INDEX ON "РеестрСообщений"("Владелец", "ТипРеестра", "ДатаВремя" DESC);
CREATE TABLE "УчастникТемы"(
"Тема"
uuid
, "Персона"
uuid
, PRIMARY KEY("Тема", "Персона")
);CREATE TABLE message_registry(
owner
uuid
, registry
smallint
, dt
timestamp
, message
uuid
, PRIMARY KEY(owner, registry, message)
);
CREATE INDEX ON message_registry(owner, registry, dt DESC);
CREATE TABLE theme_participant(
theme
uuid
, person
uuid
, PRIMARY KEY(theme, person)
);Здесь мы применили два типичных подхода, применяемых при создании вспомогательных таблиц:
- Умножение записей
Формируем по одной исходной записи сообщения сразу несколько записей-следствий в разные виды реестров для разных владельцев — как для отправителя, так и для получателя. Зато каждый из реестров теперь ложится на индекс — ведь в типовом случае мы захотим видеть только первую страницу. - Уникализация записей
При каждой отправке сообщения внутри конкретной темы достаточно проверить, существует ли уже такая запись. Если нет — добавляем ее в наш «словарь».
В следующей части статьи речь пойдет про внедрение секционирования в структуру нашей базы.
Комментарии (21)
alexxz
10.01.2020 00:02Это какой-то просто стратегический фейл именовать сущности на русском. Мало того что задолбаешься раскладку переключать, так еще и кучу кавычек расставлять надо. Не смог читать мешанину из русских и английских слов… Варианты разночтения проще решить документацией или комментариями к столбцам.
Kilor Автор
10.01.2020 00:26+1Не смог читать мешанину из русских и английских слов…
Вот так — приятнее для глаз?
Проблема, что всегда есть шанс нарваться на reserved word.alexxz
10.01.2020 00:48Спасибо, так гораздо приятнее. Шанс нарваться, конечно, есть всегда. Но почти всегда это проблема вскрывается при прототипировании или при разработке. Проблема дополнительно еще уменьшается, если в именах слобцов зашито указание на тип. Напрмер message_id или user_id. Трагичное попадание на ключевое слово может случиться при апргейде базы данных, увы. Но это уже довольно редкое событие.
Kilor Автор
10.01.2020 01:00Напрмер message_id или user_id. Трагичное попадание на ключевое слово может случиться при апргейде базы данных, увы.
Или при миграции на другую СУБД — назвать uuid-поле вполне безобидным именем rowid в PostgreSQL, и словить артефакты в Oracle… или ctid в обратную сторону.alexxz
10.01.2020 01:16При миграции на другую СУБД вопросы именования полей будут далеко не самыми сложными и интересными 8)
shurutov
11.01.2020 11:22Кто мешает держать перед глазами табличку ключевых слов диалекта (текущая): https://www.postgresql.org/docs/current/sql-keywords-appendix.html
По моему скромному мнению вероятность использовать такое слово вполне себе устремляется к нулю с таким подходом.
Лично я вполне себе такое практикую. И горя не знаю. И да, при использовании ключевых слов без кавычек ПГ ругается страшной руганью и не позволяет создать объект, который либо сам называется ключевым словом, либо в своём составе содержит ключевое слово в качестве названия подобъекта (например, наименование столбца в таблице).
Обновление? А вы аннотации к новой версии не читаете? Правда?Kilor Автор
11.01.2020 11:40Даже не «внезапно нарваться на reserved при обновлении», а нарваться при переносе бизнес-сущностей в конкретные названия таблиц. Вроде и хочешь «честно» назвать таблицу user или group или столбец copy — а нет, и начинаются костыли…
Обновление? А вы аннотации к новой версии не читаете? Правда?
А как чтение release notice поможет застраховаться от необходимости переназвать объекты и переписать запросы, если вдруг возникнет конфликт?shurutov
11.01.2020 11:58Вроде и хочешь «честно» назвать таблицу user или group или столбец copy — а нет, и начинаются костыли…
Вот именно для этого я держу перед глазами указанную ссылку. Костыли — более, чем согласен, но ключевые слова — это ключевые слова. Увы нам.
А как чтение release notice поможет застраховаться от необходимости переназвать объекты и переписать запросы, если вдруг возникнет конфликт?
Вот смотрите: прочитали аннотацию, прослезились, обматерили разработчиков, переименовали то, что нужно, после этого обновились. Хотя бы от косяков от именования не будет после обновления.
Когда аннотация не читается, вероятность ошибки, связанной с именованием объектов, при запуске на обновлённой СУБД существенно выше.
darknefrit
10.01.2020 07:56+1Вот это вы развели дискуссию не по теме статьи, пунто отлично справляется с раскладкой за частую я забываю в принципе что её нужно переключать. А писать комменты просто чтобы показать какие тут все англоговорящие… ой ну правда вы как малолетки. Реально намного проще воспринимать таблицы с русскими полями. Во вторых зачем планировать миграцию на другую бд когда нибудь в далёком далёкой будущем в далёкой далёкой галактике/ <L Уже выбрана и софт пишем под неё… высасывать проблему из пальца и пытаться тролить этим автора, а не писать объективные комменты по тексту статью… это ребяечество
alexxz
10.01.2020 10:52Статья про проектирование каркаса базы данных. И для описания используется язык SQL. И вот оформление этого самого каркаса тут обсуждается. Предложение называть столбец PK таблицы так же как и саму таблицу уже приводит к некоторому замешательству. Дополнительно в такой БД запросы будут выглядеть как каша из русских и английских слов.
Как пример типичного запроса. При попытке его прочитать у меня вскипает мозг от частоты пререключения языка.
SELECT "Пользователь"."Пол" , CAST("ДатаВремя" AS DATE) "Дата" , COUNT(1) "КоличествоПользователей" FROM "Пользователь" JOIN "Страна" ON "Пользователь"."Страна" = "Страна"."Страна" WHERE UPPER("Страна"."Название") = 'РОССИЯ' AND "Пользователь"."Пол" IS NOT NULL GROUP BY "Пользователь"."Пол", CAST("ДатаВремя" AS DATE)Kilor Автор
10.01.2020 11:11Так-то нет большой беды, если такой запрос аккуратно оформлен:
А нечитаемо-то написать запрос можно независимо от языка и названий:
SELECT "user".sex, dt::date, COUNT(1) FROM "user" JOIN country ON "user".country = country.country_id WHERE UPPER(country.title) = 'РОССИЯ' AND "user".sex IS NOT NULL GROUP BY 1,2
gudvinr
Жму руку тому человеку, которому понравится переключать раскладку по сто раз во время написания запросов.
Kilor Автор
Это вопрос, скорее, религиозного характера, поэтому объяснение в тексте под спойлером. Но для заведомо однозначного понимания «что это за ...» я привел названия именно «по-русски».
gudvinr
Это объективно снижает эффективность. Потому что:
Kilor Автор
Мы же говорим о примере моделирования БД. Для целей статьи — то есть усвоения информации человеком — лучше уж кириллицей.
Ведь даже в разговорном общении разработчики скажут «прочитай такие-то сообщения по такому-то индексу», а не «произведи SELECT записей из таблицы messages».
Никто ведь не заставляет использовать такие названия полей и таблиц при разработке в конкретных условиях, тем более в условиях ограничений ЯП.
Sazonov
С таким подходом очень сложно в интернациональных компаниях. Доминирующее большинство разработчиков прекрасно читают и воспринимают на слух английские названия таблиц и т.п. А вот кириллица в коде у меня стойко ассоциируется с 1С.
Kilor Автор
Согласен. Но для интенациональной аудитории вся статья должна быть на другом языке, не только названия сущностей/таблиц.
gudvinr
Вы говорите
А потом
Никто и абстрактные мессенджеры в принципе не разрабатывает. Статью написать и забыть, конечно, и правда можно с такими описаниями сущностей, но раз уж большинство разработчиков таким не занимаются, то идти поперёк того, что используется и понимается всеми — довольно странно и непривычно.
Да и в разговоре зачастую кастомные сущности естественным образом обрастают слегновыми "юзерами" и "месседжами". Чистота русского языка — это хорошо, но с этим можно поупражняться в документации, а работать с переменными на латинице привычнее.
Kilor Автор
Между моделью решения конкретной прикладной задачи и конкретной ее реализацией есть определенная разница. В статье я писал про первое.
В реляционной алгебре, из которой весь SQL вышел, «всеми используются и понимаются» совсем другие операции для решения тех же задач — но таки мы пользуемся не ими, а SQL, и между собой говорим «по-человечьи».Но непривычно — да, возможно. Но это «на вкус и цвет» — для меня, например, Crow's Foot/IDEF1X кажутся чрезмерно сложными для описания простых вещей.