Партиция с существующей таблицы

Question

Есть очень большая таблица. Хочу ускорить селект путем разделения ее на партиции, а точнее существующую таблицу перевести в архивную партицию, а для новых записей создавать свежие партиции. Но проблема в том, что система высоконагруженная и все эти изменения нужно проводить за максимально короткий лок данных. Думал, если партиции разделать по индексному значению это поможет, но практика показала что нет. Может кто в курсе самого оптимального решения?

Answer 1

точнее существующую таблицу перевести в архивную партицию, а для новых записей создавать свежие партиции

Такое реально сделать за секундный даунтайм весьма просто. Ключевая хитрость - необходим check constraint, подходящий для условия партицирования.

Предположим, что мы говорим о табличке вида

CREATE TABLE measurement (
     id bigserial NOT NULL primary key,
     created_at timestamp without time zone default now(),
     data bigint NOT NULL
);

Которую хотим партицировать по месяцам по полю created_at.

Для этого нужно:

удостовериться, что поле партицирования исключает значения NULL: (пояснения, пожалуй, не буду перепечатывать из другого ответа)

begin;
set local statement_timeout = '1s';
alter table measurement 
    add constraint measurement_created_at_not_null 
    check (created_at is not null) not valid;
commit;
alter table measurement validate constraint measurement_created_at_not_null;

для версии postgresql начиная с 12 можно поставить штатный set not null. Для более старых версий не надо, это будет долго.

begin;
set local statement_timeout = '1s';
alter table measurement alter column created_at set not null;
alter table measurement 
     drop constraint measurement_created_at_not_null;
commit;

добавить ограничение check на некоторую дату в будущем. До этой даты мы обязуемся завершить миграцию. Либо запись новых данных остановится.

begin;
set local statement_timeout = '1s';
alter table measurement 
    add constraint measurement_old_partition 
    check(created_at < '2022-08-01') not valid;
commit;
alter table measurement validate constraint measurement_old_partition;

в назначенный день где-то до заданной в check ограничении даты делаем фокусы:

begin;
set local statement_timeout to '1s';
alter table measurement rename to measurement_olddata;
CREATE TABLE measurement (
     id bigint NOT NULL,
     created_at timestamp without time zone not null default now(), -- если вы не ставили родной set not null, то и здесь не должно быть not null
     data bigint NOT NULL
) PARTITION BY RANGE (created_at);
ALTER SEQUENCE measurement_id_seq OWNED BY public.measurement.id;
ALTER TABLE ONLY measurement ALTER COLUMN id SET DEFAULT nextval('measurement_id_seq'::regclass);
alter table measurement attach partition measurement_olddata for values from (minvalue) to ('2022-08-01');
commit;

Всё, всем спасибо, все свободны =)

если всё сделано верно - операция пройдёт за доли секунды и может выполняться на живом проекте
если по таймауту через секунду вылетает поптыка переименования существующей таблицы - вам мешается какая-то транзакция. Смотрите, какие транзакции работают в этой базе и что творческое с ними можно сделать на время замены таблицы
если по таймауту через секунду вылетают дальнейшие шаги - что-то сделали неверно. Проверьте, валидны ли check ограничения, не забыли ли про NULL, не перепутали ли даты в check и в attach partition.

Ладно, пояснения по магии выше:

attach partition может использовать валидные check constraint для того, чтобы пропускать проверку данных в таблице во время включения её в дерево партицирования. check constraint, в свою очередь, можно добавить без длительной блокировки, если сперва внести ограничение как not valid, а затем проверить его. validate constraint не блокирует чтение или запись в таблицу.

Поскольку в исходной табличке у нас был bigserial - но это не настоящий тип данных - в определении партицированной таблицы указываем реальный bigint и перепривязываем нижележащий сиквенс корректно к новой таблице.

Само собой, create table головы партицированной таблицы должен совпадать с определение существующей таблицы. pg_dump -s может помочь с получением create table выражения.

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

БЛОГ НА HUSL

Партиция с существующей таблицы

Ответы (1 шт):