如何尽可能透明地将现有的Postgres表迁移为分区表？

Question

我在postgres数据库中有一张已存在的表。为了演示，这是它的样子：

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，升级到更新版本早晚会发生，因此如果有帮助的话，这是一个选项

如何将我的表迁移到分区表？

- yankee

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

这应该不需要太多的解释。旧表被重命名（在数据迁移后我们将删除它），我们获得一个主分区表，它基本上与原始表相同，但没有索引。

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

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;

创建规则时，不仅要更新分区表，还需在需要时创建新的分区：

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

当然，如果您还需要更新和删除，您还需要一个规则来处理那些操作，这应该很直观。

实际迁移旧表：

-- 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`来处理您的代码所需的任何情况，那么您就不需要视图和所有规则。在这种情况下，您需要在迁移过程中手动添加分区：

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

- yankee

---

这似乎是一个非常好的答案，但是如果旧表通过外键关系被其他表引用，该怎么办？我猜fk必须更新以指向新表 - 这就足够了吗？ - babis21

@babis21: 当然可以。但是对于外键支持有限制：在PostgreSQL 10中，不支持引用分区表的外键以及从分区表到其他表的外键引用。在PostgreSQL 11中有所改善：虽然仍不支持引用分区表的外键，但从分区表到其他表的外键引用是被支持的。 - yankee

感谢您提供的信息丰富的回复，我正在尝试实现您的解决方案，但是在插入分区时出现了一个错误，提示该分区已经存在。我正在使用 PostgreSQL 13.3，不确定是否有所不同。 - bnns

@bnns：从你的评论中很难找出在你的情况下出了什么问题。我认为你需要更多的文本来解释。我建议你创建一个最小可重现的示例并提出自己的问题。 - yankee

@yankee 很有趣。感谢您的澄清。 - Cerin

Answer 2

一个建议是，为您的主表访问使用视图，按照上述步骤创建新的分区表。完成后，将视图指向新的分区表，然后进行迁移，最后废弃旧表。

- Cyberience