БД на месинджера (ч.2): секционираме 'наживо'

Успешно проектирахме структурата на нашата PostgreSQL база за съхранение на кореспонденцията, измина година, потребителите активнo я попълват, вече в нея има милиони записи, и… нещо започна да забавя.

• Част 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