PostgreSQL / 系统课时

分区索引与约束

分区索引与约束

分区表的索引和唯一约束有两个容易混淆的层次:父表上的定义负责统一管理,真正保存索引项的是各个叶子分区;而跨分区唯一性又受分区键限制。本课用 SQL 验证这些规则,并说明主键设计的边界。

学习目标

  • 理解分区表索引与各叶子分区索引的关系
  • 能解释为什么分区表上的唯一约束必须包含全部分区键列
  • 能根据业务唯一性需求选择主键、唯一约束或额外的全局校验方案

建立分区表与索引

先创建按 created_on 范围分区的订单表。父表上创建普通索引后,PostgreSQL 会为现有叶子分区建立对应索引,并把这些索引关联到父级分区索引。

sql
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);

预期结果

text
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

父级分区索引本身不保存普通表索引那样的数据项,它主要描述统一的索引结构;查询真正使用的是目标叶子分区上的本地索引。

父级分区索引与叶子索引关系

验证叶子分区索引

系统目录可以直接看到父索引与子索引的继承关系。下面查询不依赖固定 OID,因此可以在刚才的表结构上直接运行。

sql
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;

预期结果

text
    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 建唯一约束会失败。

sql
ALTER TABLE orders_idx
ADD CONSTRAINT orders_idx_order_id_key UNIQUE (order_id);

预期结果

text
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 是分区键,唯一约束缺少它。

把分区键加入唯一约束后即可创建。

sql
ALTER TABLE orders_idx
ADD CONSTRAINT orders_idx_order_created_key
UNIQUE (order_id, created_on);

预期结果

text
ALTER TABLE

此时数据库保证的是 (order_id, created_on) 组合唯一,而不是单独的 order_id 在所有月份中唯一。

主键设计的工程边界

主键同样依赖唯一约束,因此分区表主键也必须包含全部分区键列。若业务要求“订单号在所有月份绝对唯一”,但表按日期分区,就不能只靠父表上的 PRIMARY KEY (order_id) 实现。

可选设计包括:把分区键纳入主键,例如 (order_id, created_on);调整分区策略,让业务唯一键包含分区维度;或者用独立登记表、受控 ID 生成规则或写入服务维护真正的全局唯一性。最后一种方案把约束责任移到分区表之外,必须显式处理并发和事务一致性。

设计时应把“访问效率”和“约束语义”分开:父级索引统一管理结构,叶子索引承担实际访问;唯一约束能否成立则取决于分区键能否把冲突限定到同一分区。先确定业务需要组合唯一还是单列全局唯一,再反推主键与分区键,通常比事后补救更稳妥。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

分区索引与约束

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通