postgresql - 将行从主表移动到具有外键约束的子表

Question

我有一个名为posts~10m 行的表，它降低了我的查询性能，所以我正在考虑使用分区到子表来优化它，其中每个子表将包含一年的记录。

我目前有以下策略：

1. 创建没有INHERIT约束的子表

   CREATE TABLE posts_2017 AS
   (SELECT * from posts where inserted_at < '2018-01-01 00:00:00');
   

2. 之后创建继承关系

   ALTER TABLE posts_2017 inherit posts;
   

3. 之后将约束添加CHECK到子表

现在它运行良好，但现在如果我查询SELECT * from posts，我会得到 2017 年帖子的重复结果。我的下一步是使用另一个查询从主表中删除它们

DELETE FROM ONLY posts where inserted_at < '2018-01-01 00:00:00'

但我遇到了另一个障碍，另一个表实际上有一个外键引用那些 2017 年的记录。

例如，假设我有一个users_posts只有user_id和post_id列的表，如果此表中的一行引用 2017 年的帖子，我的删除查询将失败。

在这种情况下我有什么选择？

标签： postgresqlperformanceoptimizationpartitioning

Answer 1

当前不能有指向分区表的外键。

这个功能有一个“正在进行中”的补丁，所以它可能会出现在 PostgreSQL v12 中。

目前，如果要进行分区，则必须不使用外键约束。