在Postgres 10中引入了“声明性分区”,它可以减轻您的大量工作,例如使用巨大的if / else语句重定向到正确的表来生成触发器或规则。Postgres现在可以自动执行此操作。让我们从迁移开始:
重命名旧表并创建新的分区表
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’; – 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;
这将在以后派上用场。
创建一个基本上只委托给我们的主表的视图:
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)
);
当然,如果您还需要update
和delete
,则还需要一个简单明了的规则。
实际迁移旧表:
-- 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
$$;