由 John Doe 七月 4, 2025
你需要同时使用到分区表和标识列吗？
特性提交日志
支持分区表中的标识列。
此前，分区表中不允许使用标识列。主要原因是没人花时间去研究细节以实现这一功能。现在这一功能已可正常使用。
关于其行为的一些细节：
-
新创建的分区继承标识属性 分区表的分区是分区表不可分割的一部分。分区会从分区表继承标识列。分区的标识列与分区表对应的列共享标识空间。换句话说，分区表所有分区中的同一标识列，共享相同的标识空间。这是通过共享相同的底层序列实现的。
当直接向分区插入数据时，会使用与最上层分区表相关联的序列来计算相应标识列的值。
在常规继承中，子表中的标识列及其属性独立于父表中的标识列及其属性。子表不会自动从父表继承标识列或其属性。这一点未发生改变。
-
附加的分区继承标识列 被附加为分区的表会从分区表继承标识属性。这应该是可行的，因为我们期望分区表的列与分区表中相应列的类型相同。如果被附加的表是分区表，标识属性会向下传播到其分区层次结构中。
分区表中的标识列也会被标记为 NOT NULL。为了使附加操作成功，分区中相应的列需要被标记为 NOT NULL。
-
分离分区时删除标识属性 分区的标识列与分区表相应列共享标识空间（即底层序列）。如果一个分区被分离，它就不能再像以前那样共享标识空间了。因此，被分离的分区的标识列会失去其标识属性。
当删除常规表中某一列的标识时，该列会保留随标识属性而来的 NOT NULL 约束。同样，被分离的分区的列也会保留随标识属性而来的 NOT NULL 约束，即使标识属性本身已丢失。
与标识属性相关联的序列链接到分区表（而非被分离的分区）。该序列不会作为分离操作的一部分被删除。
-
不允许分区拥有自己的标识列。
-
分区表支持常见的ALTER操作（添加标识列、为现有列添加标识属性、修改标识列的属性、删除标识属性）。不允许仅在分区表或某个分区中修改列；修改需要应用于整个分区层次结构。
讨论：https://www.postgresql.org/message-id/flat/CAExHW5uOykuTC+C6R1yDSp=o8Q83jr8xJdZxgPkxfZ1Ue5RRGg@mail.gmail.com
示例
提交日志的描述已经相当详细了，让我们来看看实际操作。
CREATE TABLE some_data (
id int8 generated always as identity NOT NULL,
created_at timestamptz NOT NULL,
payload TEXT
) PARTITION BY RANGE ( created_at ) ;
CREATE TABLE some_data_old PARTITION OF some_data FOR VALUES FROM (MINVALUE) TO ('2022-01-01');
CREATE TABLE some_data_y2022 PARTITION OF some_data FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE some_data_y2023 PARTITION OF some_data FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE some_data_y2024 PARTITION OF some_data FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE some_data_y2025 PARTITION OF some_data FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
现在，我们可以向所有这些表中插入数据：
insert into some_data (created_at, payload)
select now() - '5 years'::interval * random(), 'Row #' || i
from generate_series(1,10) i;
select tableoid::regclass, * from some_data order by created_at;
tableoid | id | created_at | payload
-----------------+----+-------------------------------+---------
some_data_old | 8 | 2020-12-14 08:40:15.272099+00 | Row #8
some_data_old | 7 | 2021-01-27 12:28:57.147299+00 | Row #7
some_data_old | 4 | 2021-03-27 15:24:49.524899+00 | Row #4
some_data_old | 2 | 2021-10-14 01:33:39.531299+00 | Row #2
some_data_y2022 | 10 | 2022-10-04 06:04:15.905699+00 | Row #10
some_data_y2023 | 6 | 2023-01-02 05:19:54.267299+00 | Row #6
some_data_y2023 | 9 | 2023-03-14 09:37:02.196899+00 | Row #9
some_data_y2023 | 1 | 2023-04-09 14:36:47.566499+00 | Row #1
some_data_y2023 | 3 | 2023-10-28 22:47:40.980899+00 | Row #3
some_data_y2023 | 5 | 2023-11-14 14:18:36.593699+00 | Row #5
(10 rows)
如你所见，id 是按照插入顺序分配的，无论数据最终进入哪个分区。
直接向分区插入数据也能正常工作：
insert into some_data_y2024 (created_at, payload) values (now(), 'manual #1') returning *;
id | created_at | payload
----+------------------------------+-----------
11 | 2024-02-07 15:54:46.95793+00 | manual #1
(1 row)
insert into some_data_y2023 (created_at, payload) values (now() - '1 year'::interval, 'manual #2') returning *;
id | created_at | payload
----+-------------------------------+-----------
12 | 2023-02-07 15:54:55.463043+00 | manual #2
(1 row)
有一个问题是，我们不能在 id 列上创建主键或唯一约束：
alter table some_data add primary key (id);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "some_data" lacks column "created_at" which is part of the partition key.
alter table some_data add unique (id);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "some_data" lacks column "created_at" which is part of the partition key.
这并不理想，但我们总能直接在分区上添加唯一约束：
ALTER TABLE some_data_old ADD UNIQUE (id);
ALTER TABLE some_data_y2022 ADD UNIQUE (id);
ALTER TABLE some_data_y2023 ADD UNIQUE (id);
ALTER TABLE some_data_y2024 ADD UNIQUE (id);
ALTER TABLE some_data_y2025 ADD UNIQUE (id);
当然，PostgreSQL 无法知道哪个分区包含具有特定 id 的行，因此它必须扫描所有分区：
explain select * from some_data where id = 7;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..12.35 rows=5 width=48)
-> Seq Scan on some_data_old some_data_1 (cost=0.00..1.05 rows=1 width=48)
Filter: (id = 7)
-> Seq Scan on some_data_y2022 some_data_2 (cost=0.00..1.01 rows=1 width=48)
Filter: (id = 7)
-> Seq Scan on some_data_y2023 some_data_3 (cost=0.00..1.07 rows=1 width=48)
Filter: (id = 7)
-> Seq Scan on some_data_y2024 some_data_4 (cost=0.00..1.01 rows=1 width=48)
Filter: (id = 7)
-> Index Scan using some_data_y2025_id_key1 on some_data_y2025 some_data_5 (cost=0.15..8.17 rows=1 width=48)
Index Cond: (id = 7)
(11 rows)
但如果我们在 id 上使用索引（一旦有足够多的数据），查询应该会很快。
另一方面，如果你的分区方案是以标识列为基础进行分区的，那么它运行起来会表现不错，并且可以在标识列上创建主键：
CREATE TABLE some_data (
id int8 generated always as identity NOT NULL,
payload TEXT,
PRIMARY KEY (id)
) PARTITION BY RANGE ( id ) ;
CREATE TABLE some_data_old PARTITION OF some_data FOR VALUES FROM (MINVALUE) TO (100);
CREATE TABLE some_data_1xx PARTITION OF some_data FOR VALUES FROM (100) TO (200);
CREATE TABLE some_data_2xx PARTITION OF some_data FOR VALUES FROM (200) TO (300);
CREATE TABLE some_data_3xx PARTITION OF some_data FOR VALUES FROM (300) TO (400);
CREATE TABLE some_data_4xx PARTITION OF some_data FOR VALUES FROM (400) TO (500);
然后：
postgres=> \d some_data_2xx
Table "public.some_data_2xx"
Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
payload | text | | |
Partition of: some_data FOR VALUES FROM ('200') TO ('300')
Indexes:
"some_data_2xx_pkey" PRIMARY KEY, btree (id)
需要注意的是，这样的结构会阻止直接向分区插入数据：
insert into some_data_2xx (payload) values ('a');
ERROR: new row for relation "some_data_2xx" violates partition constraint
DETAIL: Failing row contains (1, a).
当然，这是因为标识是共享的，新分配的 id 对于 some_data_2xx 来说“太旧”了，应该放入 some_data_old 中。
确实相当不错的特性，以后我们就能够在分区上创建主键了，即使它们与分区键不一致。
感谢社区的所有相关人员。
参考
提交日志：https://git.postgresql.org/pg/commitdiff/699586315704a8268808e3bdba4cb5924a038c49