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



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

Вот тут на помощь и приходит секционирование.

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

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

Концепт


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

Для любых хронологически накапливаемых данных (сообщения, документы, логи, архивы, ...) естественным выбором в качестве ключа секционирования является дата/время события. В нашем случае таким событием является момент отправки сообщения.

Заметим, что пользователи практически всегда работают только с «последними» такими данными — читают последние сообщения, анализируют последние логи,… Нет, конечно, они могут пролистать и дальше назад во времени, только делают это очень редко.

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

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

step-by-step


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

Сообщение, его свойства и проекции


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

Поскольку одной из типовых наших задач является как раз просмотр реестров сообщений (непрочитанные, входящие, все), то их тоже логично «втянуть» в секционирование по датам сообщений.


Добавляем ключ секционирования (дату сообщения) во все таблицы: адресаты, файл, реестры. В само сообщение можно не добавлять, а использовать существующее ДатаВремя.

Темы


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


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

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

  • в какой секции искать сообщения по теме?
  • в какой секции искать тему от сообщения?

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

  • в сообщении добавим поле с датой темы
  • к теме добавим набор дат сообщений этой переписки (можно отдельной таблицей, а можно и массивом дат)



Поскольку модификаций списка дат сообщений для каждой отдельно взятой переписки будет немного (ведь почти все сообщения попадают в 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), а «вручную» на уровне приложения, то можно заметить, что значение ключа секционирования уже хранится в названии самой таблицы.

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