我在postgres数据库中有一个现有表。为了演示，下面是它的样子：
创建表格myTable(
forDate日期不为空，
键2 int不为空，
值int不为null，
主键（forDate，键2）
);
插入myTable（forDate，key2，value）值
(‘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，升级到新版本迟早会发生，所以如果有帮助，这是一个选项
如何迁移要分区的表？
在Postgres 10中引入了“声明性分区”，这可以减轻大量工作的负担，例如使用大量if/else语句生成触发器或规则，从而重定向到正确的表。Postgres现在可以自动完成这项工作。让我们从迁移开始：
-
重命名旧表并创建新的分区表
将表myTable重命名为myTable\u old； 创建表myTable\u主控( forDate日期不为空， 键2 int不为空， 值int不为空 )按范围划分（forDate）；
这几乎不需要任何解释。旧表被重命名（在数据迁移之后，我们将删除它），我们得到一个分区的主表，它与原始表基本相同，但没有索引）
-
创建一个可以根据需要生成新分区的函数：
创建函数createPartitionIfNotExists（forDate日期）返回void 作为$body$ 声明月开始日期：=日期（“月”，forDate）； 声明月独占日期：=月开始+间隔“1个月”； --我们从表应该包含的日期推断表的名称 --例如，表mytable_200506中应包含2005年6月的日期： 声明tableName text:='mytable|| to_char（forDate，'YYYYmm'）； 开始 --检查所提供日期所需的表格是否存在。 --如果它不存在…： 如果to_regclass（tableName）为空，则 --生成用作mytable分区的新表： 执行格式（“为（%L）到（%L）之间的值创建myTable_master的表%I分区”、表名、monthStart、monthEndExclusive）； --不幸的是，Postgres迫使我们分别为每个表定义索引： 执行格式（“在%I上创建唯一索引（forDate，key2）”，tableName）； 如果结束； 终止 $body$语言plpgsql；
这将在以后派上用场
-
创建一个基本上只委托到主表的视图：
创建或替换视图myTable作为从myTable\u master中选择*； -
创建规则，这样当我们插入到规则中时，我们不仅会更新分区表，而且还会在需要时创建一个新分区：
创建或替换规则自动调用\u CreatePartitionIfn不存在于插入时 我的桌子 取而代之( 选择CreatePartitionfNotExists（NEW.forDate）； 将值（NEW.forDate，NEW.key2，NEW.value）插入myTable_主（forDate，key2，value）中 );
当然，如果您还需要update和delete，那么您还需要一个规则，用于那些应该是直接的规则
-
实际迁移旧表：
——最后将数据复制到新的分区表中 插入myTable（forDate，键2，值）从myTable_old中选择*； --扔掉那张旧桌子 把我的桌子放在旧桌子上；
现在表的迁移已经完成，不需要知道需要多少分区，而且视图myTable将是绝对透明的。您可以像以前一样从该表中进行简单的插入和选择，但您可能会从分区中获得性能优势
请注意，只需要该视图，因为分区表不能有行触发器。如果可以在需要时从代码中手动调用createpartitionfnotexists，则不需要视图及其所有规则。在这种情况下，您需要在迁移期间手动添加分区：
do
$$
申报rec记录；
开始
--循环浏览到目前为止存在的所有月份。。。
对于select distinct date_trunc（'month'，forDate'）：date YEARMOUNT from myTable_old循环
-- ... 并为它们创建一个分区
执行createPartitionIfNotExists（rec.yearmonth）；
端环；
终止
$$;