Как максимально прозрачно перенести существующую таблицу Postgres в многораздельную таблицу?

Question

У меня есть существующая таблица в postgres-DB. Для демонстрации это выглядит так:

create table myTable(
    forDate date not null,
    key2 int not null,
    value int not null,
    primary key (forDate, key2)
);

insert into myTable (forDate, key2, value) values
    ('2000-01-01', 1, 1),
    ('2000-01-01', 2, 1),
    ('2000-01-15', 1, 3),
    ('2000-03-02', 1, 19),
    ('2000-03-30', 15, 8),
    ('2011-12-15', 1, 11);

Однако в отличие от этих немногих ценностей, myTable на самом деле огромен, и он постоянно растет. Я создаю различные отчеты из этой таблицы, но в настоящее время 98% моих отчетов работают за один месяц, а остальные запросы работают с еще более коротким периодом времени. Часто мои запросы заставляют Postgres выполнять сканирование таблицы по этой огромной таблице, и я ищу способы уменьшить проблему. Разделение таблиц, кажется, идеально подходит для моей проблемы. Я мог бы просто разделить мой стол на месяцы. Но как мне превратить мою существующую таблицу в многораздельную таблицу? В руководстве прямо говорится:

Невозможно превратить обычную таблицу в многораздельную таблицу или наоборот

Поэтому мне нужно разработать собственный скрипт миграции, который будет анализировать текущую таблицу и переносить ее. Потребности заключаются в следующем:

• На этапе проектирования сроки, которые myTable обложки неизвестны.
• Каждый раздел должен охватывать один месяц с первого дня этого месяца до последнего дня этого месяца.
• Таблица будет расти бесконечно, поэтому у меня нет разумного "стоп-значения", сколько таблиц нужно сгенерировать
• Результат должен быть максимально прозрачным, а это означает, что я хочу коснуться как можно меньше моего существующего кода. В лучшем случае это похоже на обычную таблицу, которую я могу вставить и выбрать без каких-либо специальных предложений.
• Время простоя базы данных для миграции приемлемо
• Настоятельно рекомендуется работать с чистыми Postgres без каких-либо плагинов или других вещей, которые необходимо установить на сервере.
• База данных - PostgreSQL 10, в любом случае обновление до новой версии произойдет рано или поздно, так что это вариант, если он помогает

Как я могу перенести свою таблицу в раздел?

Answer 1

В Postgres 10 было введено "Декларативное разбиение", которое может избавить вас от большой работы, такой как генерация триггеров или правил с огромными операторами if/else, перенаправляющими на правильную таблицу. Postgres может сделать это автоматически сейчас. Начнем с миграции:

1. Переименуйте старую таблицу и создайте новую многораздельную таблицу

   alter table myTable rename to myTable_old;
   
   create table myTable_master(
       forDate date not null,
       key2 int not null,
       value int not null
   ) partition by range (forDate);
   

Это вряд ли должно требовать каких-либо объяснений. Старая таблица переименовывается (после переноса данных мы удалим ее), и мы получим основную таблицу для нашего раздела, которая в основном совпадает с нашей исходной таблицей, но без индексов)

2. Создайте функцию, которая может генерировать новые разделы по мере необходимости:

   create function createPartitionIfNotExists(forDate date) returns void
   as $body$
   declare monthStart date := date_trunc('month', forDate);
       declare monthEndExclusive date := monthStart + interval '1 month';
       -- We infer the name of the table from the date that it should contain
       -- E.g. a date in June 2005 should be int the table mytable_200506:
       declare tableName text := 'mytable_' || to_char(forDate, 'YYYYmm');
   begin
       -- Check if the table we need for the supplied date exists.
       -- If it does not exist...:
       if to_regclass(tableName) is null then
           -- Generate a new table that acts as a partition for mytable:
           execute format('create table %I partition of myTable_master for values from (%L) to (%L)', tableName, monthStart, monthEndExclusive);
           -- Unfortunatelly Postgres forces us to define index for each table individually:
           execute format('create unique index on %I (forDate, key2)', tableName);
       end if;
   end;
   $body$ language plpgsql;
   

Это пригодится позже.

3. Создайте представление, которое в основном просто делегирует нашу основную таблицу:

   create or replace view myTable as select * from myTable_master;
   

4. Создайте правило, чтобы при вставке в правило мы не только обновляли секционированную таблицу, но и при необходимости создавали новый раздел:

   create or replace rule autoCall_createPartitionIfNotExists as on insert
       to myTable
       do instead (
           select createPartitionIfNotExists(NEW.forDate);
           insert into myTable_master (forDate, key2, value) values (NEW.forDate, NEW.key2, NEW.value)
       );
   

Конечно, если вам также нужно update а также deleteВам также нужно правило для тех, кто должен быть прямым.

3. На самом деле перенести старую таблицу:

   -- Finally copy the data to our new partitioned table
   insert into myTable (forDate, key2, value) select * from myTable_old;
   
   -- And get rid of the old table
   drop table myTable_old;
   

Теперь миграция таблицы завершена без необходимости знать, сколько нужно разделов, а также просмотреть myTable будет абсолютно прозрачным. Вы можете просто вставить и выбрать из этой таблицы, как и раньше, но вы можете получить выигрыш в производительности от разделения.

Обратите внимание, что представление требуется только потому, что секционированная таблица не может иметь триггеры строк. Если вы можете ладить со звонками createPartitionIfNotExists вручную, когда это необходимо из вашего кода, вам не нужно представление и все его правила. В этом случае вам нужно добавить разделы als вручную во время миграции:

do
$$
declare rec record;
begin
    -- Loop through all months that exist so far...
    for rec in select distinct date_trunc('month', forDate)::date yearmonth from myTable_old loop
        -- ... and create a partition for them
        perform createPartitionIfNotExists(rec.yearmonth);
    end loop;
end
$$;

Answer 2

Можно предложить использовать представление для доступа к основной таблице, выполнить шаги, упомянутые выше, при создании новой таблицы разделов. после завершения укажите в представлении новую секционированную таблицу, а затем выполните миграцию, наконец, исключите старую таблицу.