У нас есть змейка, которая постоянно ест и удлиняется — как игра на телефоне. Нам для работы нужна только её голова, а мы тащим совсем ненужный хвост. Если мы его отрубим, то станет сильно легче, мы сразу выиграем. Нужно научиться спокойно терять хвост, чтобы при этом всё работало.
У крона свой плюс в том, что он простой. Но есть большой минус. Для того, чтобы он был эффективен, он должен удалять не меньше записей, чем их постоянно прибавляется. То есть если у нас новых записей появляется больше, а он не успевает столько удалять, он не будет эффективен, и таблица будет разрастаться.
То есть нам нужна связка cron, который будет успевать вычищать все ненужные данные, и vacuum, который будет успевать освобождать ячейку. А если мы имеем уже большую таблицу, даже сочетание двух этих процессов не сможет никогда её сжать.
-- Выполняем в цикле создание 12 партиций
do $$
declare
i int4;
begin
for i in 1..12
loop
-- Создаем партицию
execute format('CREATE TABLE A_m%s ( like A including all )', i );
-- Делаем ее наследником базовой таблицы
execute format('ALTER TABLE A_m%s inherit A', i);
-- Делаем ограничение на вставку данных в эту партицию
execute format('ALTER TABLE A_m%s add constraint partitioning_check check (extract("month" from created_at) = %s )', i, i);
-- Делаем правило на базовую таблицу для изменения места назначения при INSERT-е новой записи
execute format('CREATE RULE A_m%s AS ON INSERT TO A WHERE ( extract("month" from created_at) = %s) DO INSTEAD INSERT INTO A_m%s VALUES (NEW.*);', i, i, i);
end loop;
end;
$$;
TRUNCATE TABLE ONLY A;-- добавляем в старую таблицу колонку партицирования
alter table A add column partition_key integer default 0 not null;
-- создаем новый первичный ключ, сначала как индекс
create unique index concurrently A_id_p_key_unique_idx on A(id, partition_key);
-- удаляем старый первичный ключ
alter table A drop constraint A_pkey;
-- переводим индекс в первичный ключ
alter table A add constraint A_pkey primary key using index A_id_p_key_unique_idx;
-- добавим индексы, который задействованы в первичном ключе, если такие имеются
-- create unique index concurrently A_id_p_key_unique_authed_idx on A(id, p_key) where (some_condition = true);
-- добавляем невалидированный чек колонки партицирования (позволит быстро подключать партиции). Если сразу сделать валидный чек, то это повлечет за собой LOCK таблицы на время создания
alter table A add check (partition_key = 0) not valid;
-- валидируем его через shared lock, не отразится на доступности таблицы, пока будет идти валидация
alter table A validate constraint A_p_key_check;
-- новая, уже партицированная таблица
create table A_partitioned
(
-- тут расписываем DDL как в исходной таблице, добавив...
partition_key integer default 0 not null,
PRIMARY KEY (id, partition_key)
) PARTITION BY LIST (partition_key);
-- переименовываем таблицы, чтобы партицированная была как A
alter table A rename to A_default;
alter table A_partitioned rename to A;
-- базовые индексы для партиций (полностью повторяют индексы A_default)
-- не нужен режим конкурентности, потому что это пустая таблица под партиции
-- create index ...
-- подключаем дефолтную партицию
alter table A attach partition A_default default;
-- вуаля, мы имеем партицированную таблицу с 1 партицией в которую падают все записи
-- создаем с цикле партиции 1...12 для p_key 1...12
do $$
declare
i int4;
begin
for i in 1..12
loop
execute format('CREATE TABLE A_m%s PARTITION OF A FOR VALUES IN (%s);', i, i );
end loop;
end;
$$;TRUNCATE TABLE ONLY A;Мой способ универсален для всех, кто использует PostgreSQL. Его можно модифицировать под конкретные задачи.
В большинстве случаев тяжёлые манипуляции с базой данных требуют либо понижения производительности, либо вообще остановки работы системы. Мой метод ничего не понижает и тем более не останавливает. Всё как работало, так и работает.