PostgreSQL / 系统课时
分区索引与约束
分区索引与约束
分区表的索引和唯一约束有两个容易混淆的层次:父表上的定义负责统一管理,真正保存索引项的是各个叶子分区;而跨分区唯一性又受分区键限制。本课用 SQL 验证这些规则,并说明主键设计的边界。
学习目标
- 理解分区表索引与各叶子分区索引的关系
- 能解释为什么分区表上的唯一约束必须包含全部分区键列
- 能根据业务唯一性需求选择主键、唯一约束或额外的全局校验方案
建立分区表与索引
先创建按 created_on 范围分区的订单表。父表上创建普通索引后,PostgreSQL 会为现有叶子分区建立对应索引,并把这些索引关联到父级分区索引。
DROP TABLE IF EXISTS orders_idx CASCADE;
CREATE TABLE orders_idx (
order_id bigint NOT NULL,
created_on date NOT NULL,
customer_id bigint NOT NULL,
amount numeric(10,2) NOT NULL
) PARTITION BY RANGE (created_on);
CREATE TABLE orders_idx_2026_01 PARTITION OF orders_idx
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_idx_2026_02 PARTITION OF orders_idx
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE INDEX orders_idx_customer
ON orders_idx (customer_id);预期结果
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX父级分区索引本身不保存普通表索引那样的数据项,它主要描述统一的索引结构;查询真正使用的是目标叶子分区上的本地索引。

验证叶子分区索引
系统目录可以直接看到父索引与子索引的继承关系。下面查询不依赖固定 OID,因此可以在刚才的表结构上直接运行。
SELECT
parent.relname AS parent_index,
child.relname AS child_index
FROM pg_inherits AS i
JOIN pg_class AS parent ON parent.oid = i.inhparent
JOIN pg_class AS child ON child.oid = i.inhrelid
WHERE parent.relname = 'orders_idx_customer'
ORDER BY child.relname;预期结果
parent_index | child_index
---------------------+-----------------------------------
orders_idx_customer | orders_idx_2026_01_customer_id_idx
orders_idx_customer | orders_idx_2026_02_customer_id_idx
(2 rows)自动生成的子索引名称可能因现有对象命名而不同,因此验证重点是父索引下存在两个叶子索引,而不是名称必须逐字一致。以后通过 CREATE TABLE ... PARTITION OF 新增分区时,也应检查新叶子分区是否具备预期索引。
理解唯一约束为何包含分区键
分区表没有一个覆盖全部叶子分区数据的全局唯一索引。要让父表上的 UNIQUE 或 PRIMARY KEY 保证跨分区不重复,约束必须包含全部分区键列,使潜在冲突能够被限定到同一个叶子分区。
下面只对 order_id 建唯一约束会失败。
ALTER TABLE orders_idx
ADD CONSTRAINT orders_idx_order_id_key UNIQUE (order_id);预期结果
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "orders_idx" lacks column "created_on" which is part of the partition key.错误文本可能随 PostgreSQL 版本略有变化,但含义相同:created_on 是分区键,唯一约束缺少它。
把分区键加入唯一约束后即可创建。
ALTER TABLE orders_idx
ADD CONSTRAINT orders_idx_order_created_key
UNIQUE (order_id, created_on);预期结果
ALTER TABLE此时数据库保证的是 (order_id, created_on) 组合唯一,而不是单独的 order_id 在所有月份中唯一。
主键设计的工程边界
主键同样依赖唯一约束,因此分区表主键也必须包含全部分区键列。若业务要求“订单号在所有月份绝对唯一”,但表按日期分区,就不能只靠父表上的 PRIMARY KEY (order_id) 实现。
可选设计包括:把分区键纳入主键,例如 (order_id, created_on);调整分区策略,让业务唯一键包含分区维度;或者用独立登记表、受控 ID 生成规则或写入服务维护真正的全局唯一性。最后一种方案把约束责任移到分区表之外,必须显式处理并发和事务一致性。
设计时应把“访问效率”和“约束语义”分开:父级索引统一管理结构,叶子索引承担实际访问;唯一约束能否成立则取决于分区键能否把冲突限定到同一分区。先确定业务需要组合唯一还是单列全局唯一,再反推主键与分区键,通常比事后补救更稳妥。