БД мессенджера (ч.1): проектируем каркас базы

    Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования «с нуля» базы для мессенджера.



    Наша база будет не такой масштабной и распределенной, как у ВКонтакте или Badoo, а «чтобы было», но было хорошо — функционально, быстро и умещалось на одном сервере PostgreSQL — чтобы можно было развернуть отдельный экземпляр сервиса где-то на стороне, например.

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

    Шаг 1: Немного бизнес-специфики


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

    А какие бывают задачи у бизнеса?.. Посмотрим на примере Василия — руководителя отдела разработки.
    • «Николай, вот по этой задаче патч нужен уже сегодня!»
      Значит, переписка может вестись в контексте какого-то документа.
    • «Коля, го вечером в доту?»
      То есть даже у одной пары собеседников общение одновременно может вестись по разным темам.
    • «Петр, Николай, посмотрите в аттаче прайс на новый сервер.»
      Так, у одного сообщения может быть несколько адресатов. При этом сообщение может содержать прикрепленные файлы.
    • «Семен, и ты тоже взгляни.»
      И должна быть возможность в уже существующую переписку пригласить нового участника.

    Остановимся пока на этом перечне «очевидных» потребностей.
    Без понимания прикладной специфики задачи и задаваемых ей ограничений, спроектировать эффективную схему БД для ее решения практически невозможно.

    Шаг 2: Минимальная логическая схема


    Схемно пока все получается очень похоже на email-переписку — традиционный инструмент ведения бизнеса. Таки да, «алгоритмически» многие задачи бизнеса похожи друг на друга, поэтому и инструменты для их решения будут структурно сходны.

    Давайте зафиксируем уже получившуюся логическую схему отношений сущностей. Для простоты понимания нашей модели воспользуемся самым примитивным вариантом отображения ER-модели без усложнений UML или IDEF-нотаций:



    В нашем примере персона, документ и бинарное «тело» файла — это «внешние» сущности, которые самостоятельно существуют и без нашего сервиса. Поэтому просто будем воспринимать их в дальнейшем как некоторые ссылки «куда-то» по UUID.
    Рисуйте схемы как можно проще — большинство тех, кому вы их будете показывать, не являются экспертами в чтении UML/IDEF. Но — рисуйте обязательно.

    Шаг 3: Набрасываем структуру таблиц


    Про имена таблиц и полей
    К «русским» названиям полей и таблиц можно относиться по-разному, но это дело вкуса. Поскольку у нас в «Тензоре» нет разработчиков-иностранцев, а PostgreSQL позволяет нам давать названия хоть иероглифами, если они заключены в кавычки, то мы предпочитаем именовать объекты однозначно-понятно, чтобы не возникало разночтений.

    Поскольку сообщения у нас пишут много людей сразу, часть из них вообще могут делать это в оффлайн-режиме, то самый простой вариант — использовать UUID в качестве идентификаторов не только для внешних сущностей, но и для всех объектов внутри нашего сервиса. Причем генерировать их можно даже на клиентской стороне — это поможет нам поддержать отправку сообщений при кратковременной недоступности БД, а вероятность коллизии крайне мала.

    Черновая структура таблиц в нашей базе примет вот такой вид:
    Таблицы : RU
    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
    );

    Таблицы : EN
    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: Разумная денормализация


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


    Таблицы : RU
    CREATE TABLE "РеестрСообщений"(
      "Владелец"
        uuid
    , "ТипРеестра"
        smallint
    , "ДатаВремя"
        timestamp
    , "Сообщение"
        uuid
    , PRIMARY KEY("Владелец", "ТипРеестра", "Сообщение")
    );
    CREATE INDEX ON "РеестрСообщений"("Владелец", "ТипРеестра", "ДатаВремя" DESC);
    
    CREATE TABLE "УчастникТемы"(
      "Тема"
        uuid
    , "Персона"
        uuid
    , PRIMARY KEY("Тема", "Персона")
    );

    Таблицы : EN
    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

      +6

      Жму руку тому человеку, которому понравится переключать раскладку по сто раз во время написания запросов.

        0
        Это вопрос, скорее, религиозного характера, поэтому объяснение в тексте под спойлером. Но для заведомо однозначного понимания «что это за ...» я привел названия именно «по-русски».
          0

          Это объективно снижает эффективность. Потому что:


          • для написания запроса полностью на латинице вам нужно написать только строку запроса, а для запросов с чем-то не из ASCII вам нужно для каждого поля ввести две кавычки, плюс соблюсти регистр символов, плюс переключить раскладку минимум два раза. Если работа программиста заключается в основном в том, чтобы работать с базой данных — при таком подходе проще сразу застрелиться (это уже субьективно).
          • не все ЯП хорошо относятся к кириллическим символам в названиях переменных/полей/функций. Условный мессенджер — это не табличка в БД, а полноценная программа. Безусловно, если использовать что-то вроде PostgREST и хранимых процедур на pl/pgsql, то с этим проблем не будет. Поэтому при написании кода для самого мессенджера для целей сериализации придётся либо проводить транслитерацию русских переменных, либо ещё как-то извращаться, если целевой язык не слишком хорошо поддерживает кириллицу в коде.
            0
            Мы же говорим о примере моделирования БД. Для целей статьи — то есть усвоения информации человеком — лучше уж кириллицей.
            Ведь даже в разговорном общении разработчики скажут «прочитай такие-то сообщения по такому-то индексу», а не «произведи SELECT записей из таблицы messages».
            Никто ведь не заставляет использовать такие названия полей и таблиц при разработке в конкретных условиях, тем более в условиях ограничений ЯП.
              +2

              С таким подходом очень сложно в интернациональных компаниях. Доминирующее большинство разработчиков прекрасно читают и воспринимают на слух английские названия таблиц и т.п. А вот кириллица в коде у меня стойко ассоциируется с 1С.

                0
                Согласен. Но для интенациональной аудитории вся статья должна быть на другом языке, не только названия сущностей/таблиц.
                +1

                Вы говорите


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

                А потом


                Никто ведь не заставляет использовать такие названия полей и таблиц при разработке в конкретных условиях

                Никто и абстрактные мессенджеры в принципе не разрабатывает. Статью написать и забыть, конечно, и правда можно с такими описаниями сущностей, но раз уж большинство разработчиков таким не занимаются, то идти поперёк того, что используется и понимается всеми — довольно странно и непривычно.


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

                  0
                  Между моделью решения конкретной прикладной задачи и конкретной ее реализацией есть определенная разница. В статье я писал про первое.
                  идти поперёк того, что используется и понимается всеми — довольно странно и непривычно
                  В реляционной алгебре, из которой весь SQL вышел, «всеми используются и понимаются» совсем другие операции для решения тех же задач — но таки мы пользуемся не ими, а SQL, и между собой говорим «по-человечьи».
                  Но непривычно — да, возможно. Но это «на вкус и цвет» — для меня, например, Crow's Foot/IDEF1X кажутся чрезмерно сложными для описания простых вещей.
          +2

          Это какой-то просто стратегический фейл именовать сущности на русском. Мало того что задолбаешься раскладку переключать, так еще и кучу кавычек расставлять надо. Не смог читать мешанину из русских и английских слов… Варианты разночтения проще решить документацией или комментариями к столбцам.

            +1
            Не смог читать мешанину из русских и английских слов…
            Вот так — приятнее для глаз?
            Проблема, что всегда есть шанс нарваться на reserved word.
              0

              Спасибо, так гораздо приятнее. Шанс нарваться, конечно, есть всегда. Но почти всегда это проблема вскрывается при прототипировании или при разработке. Проблема дополнительно еще уменьшается, если в именах слобцов зашито указание на тип. Напрмер message_id или user_id. Трагичное попадание на ключевое слово может случиться при апргейде базы данных, увы. Но это уже довольно редкое событие.

                0
                Напрмер message_id или user_id. Трагичное попадание на ключевое слово может случиться при апргейде базы данных, увы.
                Или при миграции на другую СУБД — назвать uuid-поле вполне безобидным именем rowid в PostgreSQL, и словить артефакты в Oracle… или ctid в обратную сторону.
                  +1

                  При миграции на другую СУБД вопросы именования полей будут далеко не самыми сложными и интересными 8)

                0

                Кто мешает держать перед глазами табличку ключевых слов диалекта (текущая): https://www.postgresql.org/docs/current/sql-keywords-appendix.html
                По моему скромному мнению вероятность использовать такое слово вполне себе устремляется к нулю с таким подходом.
                Лично я вполне себе такое практикую. И горя не знаю. И да, при использовании ключевых слов без кавычек ПГ ругается страшной руганью и не позволяет создать объект, который либо сам называется ключевым словом, либо в своём составе содержит ключевое слово в качестве названия подобъекта (например, наименование столбца в таблице).
                Обновление? А вы аннотации к новой версии не читаете? Правда?

                  0
                  Даже не «внезапно нарваться на reserved при обновлении», а нарваться при переносе бизнес-сущностей в конкретные названия таблиц. Вроде и хочешь «честно» назвать таблицу user или group или столбец copy — а нет, и начинаются костыли…
                  Обновление? А вы аннотации к новой версии не читаете? Правда?
                  А как чтение release notice поможет застраховаться от необходимости переназвать объекты и переписать запросы, если вдруг возникнет конфликт?
                    0
                    Вроде и хочешь «честно» назвать таблицу user или group или столбец copy — а нет, и начинаются костыли…

                    Вот именно для этого я держу перед глазами указанную ссылку. Костыли — более, чем согласен, но ключевые слова — это ключевые слова. Увы нам.


                    А как чтение release notice поможет застраховаться от необходимости переназвать объекты и переписать запросы, если вдруг возникнет конфликт?

                    Вот смотрите: прочитали аннотацию, прослезились, обматерили разработчиков, переименовали то, что нужно, после этого обновились. Хотя бы от косяков от именования не будет после обновления.
                    Когда аннотация не читается, вероятность ошибки, связанной с именованием объектов, при запуске на обновлённой СУБД существенно выше.

              0
              не та ветка комментариев
                –2
                Вот это вы развели дискуссию не по теме статьи, пунто отлично справляется с раскладкой за частую я забываю в принципе что её нужно переключать. А писать комменты просто чтобы показать какие тут все англоговорящие… ой ну правда вы как малолетки. Реально намного проще воспринимать таблицы с русскими полями. Во вторых зачем планировать миграцию на другую бд когда нибудь в далёком далёкой будущем в далёкой далёкой галактике/ <L Уже выбрана и софт пишем под неё… высасывать проблему из пальца и пытаться тролить этим автора, а не писать объективные комменты по тексту статью… это ребяечество
                  0
                  Статья про проектирование каркаса базы данных. И для описания используется язык SQL. И вот оформление этого самого каркаса тут обсуждается. Предложение называть столбец PK таблицы так же как и саму таблицу уже приводит к некоторому замешательству. Дополнительно в такой БД запросы будут выглядеть как каша из русских и английских слов.
                  Как пример типичного запроса. При попытке его прочитать у меня вскипает мозг от частоты пререключения языка.
                  SELECT 
                      "Пользователь"."Пол"
                    , CAST("ДатаВремя" AS DATE) "Дата"
                    , COUNT(1) "КоличествоПользователей"
                  FROM "Пользователь" 
                  JOIN "Страна" ON "Пользователь"."Страна" = "Страна"."Страна"
                  WHERE 
                      UPPER("Страна"."Название") = 'РОССИЯ'
                      AND "Пользователь"."Пол" IS NOT NULL
                  GROUP BY "Пользователь"."Пол", CAST("ДатаВремя" AS DATE)
                    0
                    Так-то нет большой беды, если такой запрос аккуратно оформлен:


                    А нечитаемо-то написать запрос можно независимо от языка и названий:
                    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
                  0
                  промахнулся веткой

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое