在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);
这应该不需要太多的解释。旧表被重命名(在数据迁移后我们将删除它),我们获得一个主分区表,它基本上与原始表相同,但没有索引。
创建一个可以根据需要生成新分区的函数:
create function createPartitionIfNotExists(forDate date) returns void
as $body$
declare monthStart date := date_trunc('month', forDate);
declare monthEndExclusive date := monthStart + interval '1 month';
declare tableName text := 'mytable_' || to_char(forDate, 'YYYYmm');
begin
if to_regclass(tableName) is null then
execute format('create table %I partition of myTable_master for values from (%L) to (%L)', tableName, monthStart, monthEndExclusive);
execute format('create unique index on %I (forDate, key2)', tableName);
end if;
end;
$body$ language plpgsql;
这会在以后变得有用。
创建一个视图,基本上只是委派给我们的主表:
create or replace view myTable as select * from myTable_master;
创建规则时,不仅要更新分区表,还需在需要时创建新的分区:
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)
);
当然,如果您还需要更新和删除,您还需要一个规则来处理那些操作,这应该很直观。
实际迁移旧表:
insert into myTable (forDate, key2, value) select * from myTable_old;
drop table myTable_old;
现在表的迁移已经完成,无需知道需要多少分区,视图`myTable`也将完全透明。您可以像以前一样简单地插入和从该表中选择,但您可能会从分区中获得性能优势。
请注意,视图仅在需要时才需要,因为分区表不能有行触发器。如果您可以手动调用`createPartitionIfNotExists`来处理您的代码所需的任何情况,那么您就不需要视图和所有规则。在这种情况下,您需要在迁移过程中手动添加分区:
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
$$;