sql - 如何尽可能透明地将现有的 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 对这个巨大的表进行表扫描，我正在寻找减少问题的方法。 Table partitioning似乎很适合我的问题。我可以把我的表分成几个月。但是如何将现有表变成分区表呢？手册明确指出:

It is not possible to turn a regular table into a partitioned table or vice versa

所以我需要开发自己的迁移脚本，它会分析当前表并进行迁移。需求如下:

• 在设计时，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);
   

这几乎不需要任何解释。旧表被重命名(数据迁移后我们将删除它)并且我们得到一个分区的主表，它与我们的原始表基本相同，但没有索引)

1. 创建一个可以根据需要生成新分区的函数:

   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;
   

这个以后会派上用场的。

1. 创建一个基本上只委托(delegate)给我们的主表的 View :

   create or replace view myTable as select * from myTable_master;
   

2. 创建规则，这样当我们插入规则时，我们不仅会更新分区表，还会在需要时创建一个新分区:

   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，您还需要一个规则来处理那些应该是直截了当的规则。

1. 实际迁移旧表:

   -- 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;
   

现在表的迁移已经完成，不需要知道需要多少分区，而且 View myTable 将是绝对透明的。您可以像以前一样从该表中简单地插入和选择，但您可能会从分区中获得性能优势。

请注意， View 是唯一需要的，因为分区表不能有行触发器。如果您可以在代码需要时手动调用 createPartitionIfNotExists，那么您就不需要 View 及其所有规则。在这种情况下，您需要在迁移过程中手动添加分区:

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
$$;