Сокращение накладных расходов на вакуумирование и стратегия секционирования для сообщений большого объема в PostgreSQL 1 ⇐ JAVA

[Anonymous]

У меня есть вариант использования, когда мне нужно обработать файл, содержащий сообщения, созданные для нескольких клиентов. Эти сообщения необходимо хранить в базе данных и пакетно пересылать соответствующему клиенту.
Как только я получу подтверждение (которое будет представлять собой индивидуальный ответ, отправленный клиентом на каждое сообщение), Я сопоставлю подтверждение
с CORRELATION_ID и удалю соответствующее сообщение из базы данных. Если подтверждение не получено, я буду повторять одно и то же сообщение клиенту каждые 10 минут, пока не получу подтверждение, иначе срок действия сообщения истечет. (допустим, срок действия = 10 дней, я буду повторять попытку до 10 дней, и если подтверждение не будет получено, я удалю это сообщение из базы данных).
Обычно мы получаем подтверждение за 70-80 дней. % сообщений с первой попытки. Для повторного сообщения мы не можем предсказать поведение: либо мы получили подтверждение при нескольких повторных попытках, либо срок действия сообщения истечет.
Кроме того, у каждого клиента есть конфигурация (customerConfig), загружаемая во время запуска приложения. У него есть срок действия поля для каждого типа сообщения и его динамики, что означает, что клиент может изменить эти значения в любой момент. Пример ниже

Код: Выделить всё

[{
"customer": "524AI9KS",
"messages": [{
"message_type": "Billing",
"expiry ": "30"           // in days
},
{
"message_type": "Promotional",
"expiry ": "10"           // in days
},
{
"message_type": "Support ",
"expiry ": "15"           // in days
}]
},
{
"customer": "695AS5JW96",
"messages": [{
"message_type": "Billing",
"expiry ": "40"           // in days
},
{
"message_type": "Promotional",
"expiry ": "12"           // in days
},
{
"message_type": "Support ",
"expiry ": "11"           // in days
}]
}]

Ниже приведен текущий скрипт базы данных

Код: Выделить всё

CREATE TABLE CUSTOMER_MESSAGES (
CUSTOMER_ID         VARCHAR(50)     NOT NULL,
MESSAGE_ID          VARCHAR(50)     NOT NULL,
MESSAGE_TYPE        VARCHAR(20)     NOT NULL,
CORRELATION_ID      VARCHAR(50)     NOT NULL,
RETRY_COUNT         NUMERIC(10, 0)  NOT NULL,
CRTE_TS             TIMESTAMP       NOT NULL,
UPTS_TS             TIMESTAMP       NOT NULL,
MESSAGE             VARCHAR,
CONSTRAINT CUSTOMER_MESSAGES_PK PRIMARY KEY (CUSTOMER_ID, MESSAGE_ID)
) PARTITION BY HASH (CUSTOMER_ID);

CREATE TABLE CUSTOMER_MESSAGES_P0 PARTITION OF CUSTOMER_MESSAGES FOR VALUES WITH (MODULUS 10, REMAINDER 0);
CREATE TABLE CUSTOMER_MESSAGES_P1 PARTITION OF CUSTOMER_MESSAGES FOR VALUES WITH (MODULUS 10, REMAINDER 1);
CREATE TABLE CUSTOMER_MESSAGES_P2 PARTITION OF CUSTOMER_MESSAGES FOR VALUES WITH (MODULUS 10, REMAINDER 2);
...
CREATE TABLE CUSTOMER_MESSAGES_P9 PARTITION OF CUSTOMER_MESSAGES FOR VALUES WITH (MODULUS 10, REMAINDER 9);

Сопряжение сообщений и подтверждений выполняется на основе CORRELATION_ID.
Вышеуказанная стратегия разделения была выбрана, когда объем сообщений был небольшим (менее 50 000 в день). ) и клиентов было всего 20. Сейчас объем сообщений вырос примерно до 15-20 миллионов в день, а клиентов около 10 000.
Проблема текущего дизайна в том, что

• Это создает много «вакуума» или пустого пространства, что приводит к фрагментации базы данных и в конечном итоге влияет на производительность запросов.
• Одно разделение заканчивается иметь много сообщений, поскольку распределение сообщений зависит от клиента. Вполне возможно, что один клиент имеет объем 30-45% в определенный период.

Мы также рассмотрели разделение по CRTE_TS (вместо CUSTOMER_ID), поэтому что мы можем удалить разделы по истечении периода хранения. Однако, поскольку период хранения различен для каждого клиента и для каждого типа сообщения, его может быть сложно реализовать.
Я использую PostgreSQL 13, и серверное приложение загружается весной.
Может ли кто-нибудь подсказать, какую стратегию разделения мне следует использовать? (учитывая, что PostgreSQL допускает секционирование по RANGE, HASH или LIST) и по какому столбцу следует создать раздел?

Подробнее здесь: https://stackoverflow.com/questions/790 ... ges-in-pos