PostgreSQL声明式分区表存储疑问及低风险迁移方案咨询

1. 创建哈希分区主表 #

create table _my_message
(
id uuid not null,
chat_id uuid constraint _my_message_chat_fkey references _chat,
...

CONSTRAINT _my_message_id_chat_id_pk PRIMARY KEY (id, chat_id)
) PARTITION BY HASH (chat_id);

2. 创建10个哈希分区表 #

CREATE TABLE _my_message_0 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 0);
CREATE TABLE _my_message_1 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 1);
CREATE TABLE _my_message_2 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 2);
CREATE TABLE _my_message_3 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 3);
CREATE TABLE _my_message_4 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 4);
CREATE TABLE _my_message_5 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 5);
CREATE TABLE _my_message_6 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 6);
CREATE TABLE _my_message_7 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 7);
CREATE TABLE _my_message_8 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 8);
CREATE TABLE _my_message_9 PARTITION OF _my_message FOR VALUES WITH (MODULUS 10, REMAINDER 9);

3. 创建索引 #

CREATE INDEX ON _my_message (chat_id);

4. 数据迁移操作 #

从旧表_message迁移数据至分区主表_my_message，因PostgreSQL不支持直接将普通表转为分区表，反之亦然。

执行后验证结果：各分区表按chat_id哈希规则存储数据，查询主表_my_message会返回所有分区数据总和，执行计划显示为Append所有分区的顺序扫描。

问题解答 #

1. 分区主表_my_message是否自身存储数据？ #

分区主表（又称分区父表）本身不存储任何实际数据，它只是一个逻辑容器，所有数据都存储在其下属的各个分区表中。查询主表时，PostgreSQL会自动扫描所有符合条件的分区并合并结果，这就是你看到执行计划中出现Append操作的原因。

2. 是否存在无需大规模迁移旧表数据的分区方案？ #

有两种可行方案可避免全量迁移带来的数据丢失风险和回滚难题：

方案一：利用ALTER TABLE ... ATTACH PARTITION结合分区交换（PostgreSQL 12+）

1. 保持分区主表_my_message的创建逻辑不变。
2. 将旧表_message直接挂载为_my_message的一个分区：

   ALTER TABLE _my_message ATTACH PARTITION _message FOR VALUES WITH (MODULUS 10, REMAINDER X);
   

   这里的X选一个未被现有分区占用的余数即可，若之前只创建了0-9的分区，可先新增_my_message_10再执行挂载，或直接用旧表作为临时分区。
3. 后续新写入的数据会自动路由到对应分区，后台可分批将旧分区的数据迁移到其他目标分区，完成后再将旧分区detach并删除。

方案二：视图+触发器实现伪分区（兼容低版本）

1. 按现有逻辑创建分区主表和各分区表。
2. 创建合并旧表与分区主表的视图：

   CREATE VIEW message_union AS
   SELECT * FROM _message
   UNION ALL
   SELECT * FROM _my_message;
   

3. 编写触发器，让写入视图的新数据自动路由到分区主表_my_message。
4. 后台分批将旧表_message的数据迁移到分区主表，完成后删除旧表，将视图直接指向分区主表即可。

两种方案都支持无停机迁移，可有效降低一次性全量迁移的风险，出现问题时能快速回滚（如detach分区、切换视图指向）。

内容的提问来源于stack exchange，提问作者NeverSleeps