PostgreSQL / 系统课时
设计 LIST 业务域分区
设计 LIST 业务域分区
LIST 分区适合把少量、离散且业务含义稳定的值直接映射到不同子表。本课用区域订单表判断何时该选 LIST,并通过 DEFAULT 分区、分区裁剪和约束边界把“能分”进一步收敛为“可长期维护”。
学习目标
- 判断离散业务维度是否适合作为 LIST 分区键
- 创建显式值分区与
DEFAULT分区并验证路由结果 - 识别新增业务值、唯一约束和分区裁剪的维护边界
先判断业务维度是否值得分区
LIST 的关键不是字段类型,而是值集合是否能长期承担数据治理边界。例如区域、数据等级、业务线这类取值有限、含义明确的字段,常常适合按值把数据放入不同分区,以便分别归档、维护或设置表空间。
如果分区键取值持续增长,例如把数十万租户的 tenant_id 一租户一分区,LIST 会制造大量子表和元数据开销。此时应优先考虑 RANGE、HASH,或先按时间分区,再把租户作为普通索引列。
还要检查查询条件。只有查询经常直接约束分区键,LIST 的分区裁剪才容易生效;如果绝大多数查询只按订单号查找,却几乎不带区域条件,那么按区域分区不会自动变成性能优化。
建立显式业务域与兜底分区
下面建立一个按 region_code 分区的订单表。CN 独占一个分区,SG 与 JP 共享一个分区,尚未纳入正式业务域的值先进入 DEFAULT 分区。
DROP TABLE IF EXISTS orders_by_region CASCADE;
CREATE TABLE orders_by_region (
order_id bigint NOT NULL,
region_code text NOT NULL,
amount numeric(10, 2) NOT NULL
) PARTITION BY LIST (region_code);
CREATE TABLE orders_region_cn
PARTITION OF orders_by_region
FOR VALUES IN ('CN');
CREATE TABLE orders_region_apac
PARTITION OF orders_by_region
FOR VALUES IN ('SG', 'JP');
CREATE TABLE orders_region_default
PARTITION OF orders_by_region
DEFAULT;
INSERT INTO orders_by_region (order_id, region_code, amount) VALUES
(1, 'CN', 100.00),
(2, 'SG', 80.00),
(3, 'JP', 120.00),
(4, 'US', 60.00);
SELECT tableoid::regclass AS partition_name,
order_id,
region_code,
amount
FROM orders_by_region
ORDER BY order_id;最后一条 SELECT 的预期输出:
partition_name | order_id | region_code | amount
-------------------------+----------+-------------+--------
orders_region_cn | 1 | CN | 100.00
orders_region_apac | 2 | SG | 80.00
orders_region_apac | 3 | JP | 120.00
orders_region_default | 4 | US | 60.00同一个 LIST 值只能属于一个分区,因此不能再创建另一个包含 CN 的分区。LIST 也可以显式接收 NULL,但本例把分区键设为 NOT NULL,避免“未知区域”和“缺失区域”混成同一种业务状态。
用执行计划确认分区裁剪
分区并不等于自动减少扫描范围。对分区键写出可裁剪的条件后,规划器才能排除无关子表。下面只查询 CN,并关闭成本数字以便观察计划结构。
EXPLAIN (COSTS OFF)
SELECT *
FROM orders_by_region
WHERE region_code = 'CN';预期输出的核心结构如下,具体过滤条件的类型显示可能因环境略有差异:
Seq Scan on orders_region_cn
Filter: (region_code = 'CN'::text)这里没有扫描 orders_region_apac 和 orders_region_default,说明 LIST 边界参与了分区裁剪。子分区本身仍可能执行顺序扫描;是否需要 B-tree 等索引,要根据每个分区内的数据量和查询条件单独决定。
管好 DEFAULT 与业务域演进
DEFAULT 能避免新业务值一出现就让写入失败,但它也会把“尚未建模的值”悄悄收进去。生产环境应持续统计默认分区中的分区键,并把非预期值视为需要处理的模型信号,而不是永久兜底。
当准备把已经存在于默认分区中的 US 提升为独立分区时,不能直接创建重叠边界。PostgreSQL 必须确认默认分区中不存在应属于新分区的行;如果确实存在,就要先在受控窗口中搬迁这些数据,再创建或附加新分区。默认分区很大时,这类边界校验还可能带来扫描和锁等待。
约束决定长期可维护性
分区表上的主键或唯一约束若要由 PostgreSQL 在整个分区层级上保证,必须包含全部分区键列。因此本例如果要求全局唯一语义,不能只在父表上声明 UNIQUE (order_id),而应重新审视标识设计,常见做法是让唯一键同时包含 region_code。
选择 LIST 的最终标准可以压缩为三点:值集合离散且受治理、查询经常携带该业务维度、不同值确实需要独立的数据生命周期或运维动作。只满足“字段看起来像枚举”而没有这些收益时,普通表加索引通常更简单。