БД месенджера (ч.2): секціонуємо «наживу»

Ми вдало спроектували структуру нашої PostgreSQL-бази для зберігання листування, пройшов рік, користувачі активно її наповнюють, ось у ньому вже мільйони записів, І ... щось все почало підгальмовувати.

• Частина 1: проектуємо каркас бази
• Частина 2: секціонуємо «наживу»

Справа в тому, що зі зростанням обсягу таблиці зростає і «глибина» індексів — хоч і логарифмічно. Але згодом це змушує сервер для виконання тих же завдань читання/запису обробляти у рази більше сторінок данихніж на початку.

Ось тут на допомогу і приходить секціонування.

Зауважу, що мова піде не про шардінг, тобто розподіл даних між різними базами чи серверами. Тому що, навіть розділивши дані на кілька серверів, ви ніяк не позбавитеся проблеми «розпухання» індексів з часом. Зрозуміло, що якщо ви можете дозволити собі щодня вводити в дію новий сервер, то ваші проблеми будуть лежати вже зовсім не на площині конкретної БД.

Ми ж розглянемо не конкретні скрипти для реалізації секціонування «в залізі», а сам підхід — що і як варто «нарізати на часточки», і до чого таке бажання призводить.

концепт

Ще раз визначимо нашу мету: ми хочемо зробити так, щоб і сьогодні, і завтра, і через рік кількість PostgreSQL даних, що читаються, при будь-якій операції читання/запису залишалося приблизно одним і тим же.

Для будь-яких хронологічно накопичуваних даних (повідомлення, документи, логи, архіви, …) природним вибором як ключ секціонування є дата/час події. У нашому випадку такою подією є момент відправлення повідомлення.

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

З цих обмежень стає очевидним, що оптимальним рішенням для повідомлень будуть «подобові» секції — адже майже завжди наш користувач читатиме те, що прийшло йому сьогодні чи вчора.

Якщо ми протягом дня пишемо та читаємо практично лише в одну секцію, то це дає нам ще й більш ефективне використання пам'яті та диска — оскільки всі індекси секції легко вміщаються в оперативну пам'ять, на відміну від «великих і жирних» по всій таблиці.

крок за кроком

Загалом все сказане вище звучить як один суцільний профіт. І він досяжний, але задля цього нам доведеться добре постаратися — бо рішення секціонувати одну з сутностей призводить до необхідності «розпилювати» та пов'язані з нею.

Повідомлення, його властивості та проекції

Раз ми вирішили порізати повідомлення за датами, то і залежні від них сутності-властивості (додані файли, список адресатів) розумно теж ділити, і теж за датою повідомлення.

Оскільки одним із типових наших завдань є саме перегляд реєстрів повідомлень (непрочитані, вхідні, всі), їх теж логічно «втягнути» в секціонування за датами повідомлень.

Додаємо ключ секціонування (дату повідомлення) до всіх таблиць: адресати, файл, реєстри. У саме повідомлення можна не додавати, а використовувати існуючий Дата Час.

теми

Оскільки тема одна на кілька повідомлень, її «порізати» в тій же моделі вже ніяк не вийде, треба спиратися на щось інше. У нашому випадку ідеально підходить дата першого повідомлення у листуванні - Тобто момент створення, власне, теми.

Додаємо ключ секціонування (дату теми) до всіх таблиць: тема, учасник.

Але тепер у нас виникають одразу дві проблеми:

• у якій секції шукати повідомлення на тему?
• у якій секції шукати тему повідомлення?

Можна, звичайно, продовжувати шукати по всіх секціях, але це буде дуже сумно, і зведе нанівець усі наші виграші. Тому, щоб знати, де саме шукати, зробимо логічні посилання/покажчики на секції:

• у повідомленні додамо поле з датою теми
• до теми додамо набір дат повідомлень цього листування (можна окремою таблицею, а можна і масивом дат)

Оскільки модифікацій списку дат повідомлень для кожного окремого листування буде небагато (адже майже всі повідомлення потрапляють в 1-2 сусідні дні), я зупинюся саме на такому варіанті.

Отже, структура нашої бази набула наступного вигляду з урахуванням секціонування:

Таблиці : RU, при відразі до кирилиці в назвах таблиць/полів краще не дивитися

-- секции по дате сообщения
CREATE TABLE "Сообщение_YYYYMMDD"(
  "Сообщение"
    uuid
      PRIMARY KEY
, "Тема"
    uuid
, "ДатаТемы"
    date
, "Автор"
    uuid
, "ДатаВремя" -- используем как дату
    timestamp
, "Текст"
    text
);

CREATE TABLE "Адресат_YYYYMMDD"(
  "ДатаСообщения"
    date
, "Сообщение"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Сообщение", "Персона")
);

CREATE TABLE "Файл_YYYYMMDD"(
  "ДатаСообщения"
    date
, "Файл"
    uuid
      PRIMARY KEY
, "Сообщение"
    uuid
, "BLOB"
    uuid
, "Имя"
    text
);

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

-- секции по дате темы
CREATE TABLE "Тема_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
      PRIMARY KEY
, "Документ"
    uuid
, "Название"
    text
);

CREATE TABLE "УчастникТемы_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
, "Персона"
    uuid
, PRIMARY KEY("Тема", "Персона")
);

CREATE TABLE "ДатыСообщенийТемы_YYYYMMDD"(
  "ДатаТемы"
    date
, "Тема"
    uuid
      PRIMARY KEY
, "Дата"
    date
);

Заощаджуємо копієчку

Ну, а якщо ми використовуємо не класичний варіант секціонування на основі розподілу значень поля (через тригери та успадкування або PARTITION BY), а «вручну» на рівні програми, можна помітити, що значення ключа секціонування вже зберігається в назві самої таблиці.

Тому якщо ви настільки сильно переживаєте за обсяг даних, що зберігаються, то цих «зайвих» полів можна позбутися і звертатися адресно до конкретних таблиць. Щоправда, всі вибірки з кількох секцій у разі доведеться винести на бік програми.

Джерело: habr.com