PostgreSQL / 系统课时

设计 LIST 业务域分区

设计 LIST 业务域分区

LIST 分区适合把少量、离散且业务含义稳定的值直接映射到不同子表。本课用区域订单表判断何时该选 LIST,并通过 DEFAULT 分区、分区裁剪和约束边界把“能分”进一步收敛为“可长期维护”。

学习目标

  • 判断离散业务维度是否适合作为 LIST 分区键
  • 创建显式值分区与 DEFAULT 分区并验证路由结果
  • 识别新增业务值、唯一约束和分区裁剪的维护边界

先判断业务维度是否值得分区

LIST 的关键不是字段类型,而是值集合是否能长期承担数据治理边界。例如区域、数据等级、业务线这类取值有限、含义明确的字段,常常适合按值把数据放入不同分区,以便分别归档、维护或设置表空间。

如果分区键取值持续增长,例如把数十万租户的 tenant_id 一租户一分区,LIST 会制造大量子表和元数据开销。此时应优先考虑 RANGE、HASH,或先按时间分区,再把租户作为普通索引列。

还要检查查询条件。只有查询经常直接约束分区键,LIST 的分区裁剪才容易生效;如果绝大多数查询只按订单号查找,却几乎不带区域条件,那么按区域分区不会自动变成性能优化。

建立显式业务域与兜底分区

下面建立一个按 region_code 分区的订单表。CN 独占一个分区,SG 与 JP 共享一个分区,尚未纳入正式业务域的值先进入 DEFAULT 分区。

sql
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 的预期输出:

text
     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,并关闭成本数字以便观察计划结构。

sql
EXPLAIN (COSTS OFF)
SELECT *
FROM orders_by_region
WHERE region_code = 'CN';

预期输出的核心结构如下,具体过滤条件的类型显示可能因环境略有差异:

text
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 的最终标准可以压缩为三点:值集合离散且受治理、查询经常携带该业务维度、不同值确实需要独立的数据生命周期或运维动作。只满足“字段看起来像枚举”而没有这些收益时,普通表加索引通常更简单。

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

目录与阅读设置

记录一个想法

设计 LIST 业务域分区

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

登录哪都通