Database table partitioning

Задача

Реализовать партиционирование таблиц по месяцам на основе поля createdDate.

Реализация

Используем партициоривание в Postrges. При партицировании через наследование нет необходимости переписывать код приложения: обращения на чтение и запись выполняются к мастер-таблице, PostgreSQL самостоятельно определяет, к каким партициям следует обратиться

SELECT *
FROM users;

INSERT INTO users (login, created_date, last_modified_date)
VALUES ('login', '2023-04-30', NOW());

SELECT *
FROM users;

DROP TABLE users_2023_04;

SELECT *
FROM users;

Ограничения

1. При создании индекса в секционируемой таблице автоматически будет создан соответствующий индекс в каждой секции и все секции, которые будут созданы позднее, будут так же содержать такой индекс. Индексы или ограничения уникальности, созданные в секционированной таблице, являются виртуальными, как и сама секционированная таблица: фактически данные находятся в дочерних индексах отдельных таблиц-секций.
2. Индекс по ключу партиционирования создавать не обязательно, но в большинстве случаев он будет полезен.
3. Создать ограничение уникальности или первичного ключа для секционированной таблицы можно, только если ключи секций включают все столбцы ключа секционирования. Это условие объясняется тем, что отдельные индексы, образующие ограничение, могут непосредственно обеспечивать уникальность только в своих секциях. Поэтому сама структура секционирования должна гарантировать отсутствие дубликатов в разных секциях.
4. Создать ограничение-исключение, охватывающее всю секционированную таблицу, нельзя; можно только поместить такое ограничение в каждую отдельную секцию с данными. И это также является следствием того, что установить ограничения, действующие между секциями, невозможно.
5. Триггеры BEFORE ROW для INSERT не могут менять секцию, в которую в итоге попадёт новая строка.
6. На уровне реализации отдельные секции связываются с секционированной таблицей средствами наследования.

Миграция с Postgres 12 на 13

Ссылки

1. Секционирование таблиц
2. Партицирование таблиц в PostgreSQL: чек-лист для старта