PostgreSQL / 系统课时

设计分区索引与约束

设计分区索引与约束

分区表不能只把原表索引照搬过去。设计时要同时处理常用查询的局部访问、分区键与唯一性的关系,以及主键是否仍能表达业务约束。本课用一个按时间分区的事件表说明这些边界。

学习目标

  • 为分区表设计与查询模式匹配的索引,并理解分区索引与叶子分区索引的关系
  • 判断唯一约束和主键何时必须包含分区键
  • 避免为了满足约束而破坏归档、删除和分区裁剪能力

从查询模式反推索引

假设事件表按 occurred_at 按月范围分区,线上最常见的查询是按租户和时间范围读取事件。索引首先应该服务这个真实谓词,而不是机械地给每一列建索引。

sql
CREATE TABLE event_log (
    tenant_id bigint NOT NULL,
    event_id bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE event_log_2026_09 PARTITION OF event_log
FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');

CREATE INDEX event_log_tenant_time_idx
ON event_log (tenant_id, occurred_at DESC);

预期结果

text
CREATE TABLE
CREATE TABLE
CREATE INDEX

在分区父表上创建索引时,PostgreSQL 会建立分区索引,并为现有叶子分区创建或关联对应的局部索引。之后通过父表定义新增分区时,也会为该分区创建与父级索引匹配的索引;生产环境仍应通过系统目录或 \d+ 核对结构是否符合预期。

分区父表、叶子分区与局部索引关系

真正决定性能的是查询能否同时利用分区裁剪和索引。下面的查询带有明确时间范围,规划器可以排除无关月份,再在命中的分区中按 tenant_id 和时间范围访问索引。

sql
EXPLAIN (COSTS OFF)
SELECT event_id, event_type
FROM event_log
WHERE tenant_id = 42
  AND occurred_at >= TIMESTAMPTZ '2026-09-10 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-09-11 00:00:00+00'
ORDER BY occurred_at DESC;

预期结果

text
Index Scan using <PARTITION_INDEX_NAME> on event_log_2026_09
  Index Cond: ((tenant_id = 42) AND (occurred_at >= '2026-09-10 00:00:00+00'::timestamp with time zone) AND (occurred_at < '2026-09-11 00:00:00+00'::timestamp with time zone))

实际索引名称、时间显示格式和计划细节会受 PostgreSQL 版本、统计信息、数据量及会话时区影响;关键验证点是执行计划只包含命中的时间分区,而不是扫描所有月份。

唯一约束必须跨过分区边界思考

PostgreSQL 对分区表的 UNIQUE 约束和主键有一个关键限制:约束列必须包含全部分区键列。各叶子分区分别保存数据及唯一索引,只有约束包含分区键,数据库才能依靠分区边界与各叶子分区的局部唯一性共同保证父表范围内不出现重复键。

下面的主键包含分区键 occurred_at,因此可以建立:

sql
ALTER TABLE event_log
ADD CONSTRAINT event_log_pk
PRIMARY KEY (tenant_id, event_id, occurred_at);

预期结果

text
ALTER TABLE

如果尝试只用 event_id 建唯一约束,而分区键仍是 occurred_at,则会失败:

sql
ALTER TABLE event_log
ADD CONSTRAINT event_log_event_id_uk
UNIQUE (event_id);

预期结果

text
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "event_log" lacks column "occurred_at" which is part of the partition key.

不同 PostgreSQL 版本的错误文本可能略有差异,但“唯一约束必须包含全部分区键列”这一限制相同。

不要让主键方案反过来破坏生命周期设计

如果业务要求 event_id 在整个生命周期内绝对唯一,而分区键是时间,就不能依赖父分区表上的 UNIQUE (event_id) 来实现。常见做法是由应用或专门的 ID 生成机制保证 event_id 全局唯一,同时在数据库中使用包含分区键的主键或唯一约束;另一种选择是重新评估是否真的需要数据库在全部历史数据范围内验证这一单列唯一性。

索引也不宜无限复制。只为在线查询、归档查询和必要约束创建真正需要的索引,因为每增加一个索引,相关分区都会承担额外的写入、磁盘、维护和归档成本。

建立可验证的设计准则

设计完成后至少核对三件事:典型查询是否携带分区键范围并触发裁剪;常用过滤列是否在命中分区内拥有合适索引;主键或唯一约束是否包含全部分区键列。只有这三点同时成立,分区结构才不会以牺牲查询性能或数据约束为代价。

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

目录与阅读设置

阅读设置

17

记录一个想法

设计分区索引与约束

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

登录哪都通