七月 14, 2025
摘要：在本教程中，您将学习如何使用 PostgreSQL 中的表继承在线对现有表进行分区。
目录
准备数据集
让我们先创建一个简单的表，插入一些数据：
create table t ( a int primary key, b text );
insert into t select i, 'aaa' from generate_series(1, 1000000) i;
insert into t select i + 1000000, 'bbb' from generate_series(1, 1000000) i;
如果我们想根据这张表的 b 列进行列表分区，有哪些选择？我们可以在当前表的旁边创建新的分区结构，然后在某个时间点将现有数据加载到分区表中（可以使用insert into ... select * from语句，也可以通过转储和重新加载）。另一个选择是设置从旧表到新分区表的逻辑复制。第三个选择，也就是我们现在要讨论的，是使用表继承作为中间步骤，实现几乎在线地对表进行分区。
将数据转移到子表
首先我们需要准备的是，用来保存 “aaa” 和 “bbb” 值数据的子表：
create table t_aaa ( a int, b text check (b in ('aaa')) ) inherits ( t );
create table t_bbb ( a int, b text check (b in ('bbb')) ) inherits ( t );
这里的重点是检查约束（稍后还会介绍），当然还有继承。目前我们已经构建好了继承关系，但子表还不包含任何数据。在加载子表之前，我们需要通过创建触发器来确保：到达父表的新数据会被路由到正确的子表中：
CREATE OR REPLACE FUNCTION tmp_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.b = 'aaa' )
THEN
INSERT INTO t_aaa VALUES (NEW.*);
ELSIF ( NEW.b = 'bbb' )
THEN
INSERT INTO t_bbb VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Value out of range!';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tmp_insert_trigger
BEFORE INSERT ON t
FOR EACH ROW EXECUTE PROCEDURE tmp_trigger();
快速测试一下触发器是否正常工作：
insert into t values (-1, 'aaa');
insert into t values (-2, 'bbb');
select * from only t_aaa;
a | b
----+-----
-1 | aaa
(1 row)
select * from only t_bbb;
a | b
----+-----
-2 | bbb
(1 row)
select * from t where a in (-1, -2);
a | b
----+-----
-1 | aaa
-2 | bbb
(2 rows)
这样验证了：
- 数据会被路由到正确的子表中
- 数据可以通过父表进行访问
确认之后，我们可以从父表中删除数据，并将其插入到子表中（可以分多个批次进行）：
with aa as ( delete from t where b = 'aaa' returning * )
insert into t_aaa select * from aa;
with bb as ( delete from t where b = 'bbb' returning * )
insert into t_bbb select * from bb;
我们这样做，一直到父表为空，并且所有的数据都到了子表中：
select count(*) from only t;
count
-------
0
(1 row)
select count(*) from only t_aaa;
count
---------
1000000
(1 row)
select count(*) from only t_bbb;
count
---------
1000000
(1 row)
创建分区表
最后一步是，移除继承关系，并将子表作为新分区附加到新创建的分区表中，最后重命名旧表和新分区表：
begin;
create table tt ( a int, b text ) partition by list(b);
alter table t_aaa no inherit t;
alter table tt attach partition t_aaa for values in ('aaa');
alter table t_bbb no inherit t;
alter table tt attach partition t_bbb for values in ('bbb');
alter table t rename to t_old;
alter table tt rename to t;
commit;
由于检查约束与分区键匹配，PostgreSQL 可以直接将表附加为新分区，而无需扫描表。因此，这是一个非常快速的操作，并且锁定时间非常短。下面是新的表结构：
postgres=# \d+ t
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | text | | | | extended | | |
Partition key: LIST (b)
Partitions: t_aaa FOR VALUES IN ('aaa'),
t_bbb FOR VALUES IN ('bbb')
总结
但是，请记住：
- 这是一个非常简单的测试用例，当然效果很好。但是在繁忙的系统中，这可能会变得更加棘手。
- 使用触发器路由数据，可能会导致性能下降。
- 当您从旧结构转到新结构时，您必须以某种方式处理插入的数据，或者在最后一步停止数据的插入。