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

[Anonymous]

У меня есть вариант использования, когда мне нужно обработать файл, содержащий сообщения, созданные для нескольких клиентов. Эти сообщения необходимо хранить в базе данных и пересылать соответствующему клиенту. Как только я получу подтверждение, я удалю соответствующее сообщение из базы данных. Если подтверждение не получено, я буду повторять попытку через определенный интервал несколько раз, пока сообщение не будет подтверждено.
Кроме того, существует конфигурация (customerConfig, которую я загружаю во время запуска приложения), которая содержит период хранения. для сообщений на основе клиента, типа сообщения и его динамики, что означает, что клиент может изменить эти значения в любой момент. Например, платежные сообщения клиента1 имеют период хранения 30 дней, а сообщения службы поддержки клиентов — 15 дней. Для сообщений о выставлении счетов для клиента2 срок хранения составляет 60 дней, а для сообщений поддержки клиентов — 20 дней.

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

CREATE TABLE CUSTOMER_MESSAGES (
CUSTOMER_ID         VARCHAR(50)     NOT NULL,
MESSAGE_ID          VARCHAR(50)     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 клиентов. Сейчас объем сообщений вырос примерно до 20 миллионов, а клиентов около 10 000.
Проблема текущего дизайна таблицы в том, что

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

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

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