PHP小码农

Appearance

分区表最佳实践（Partitioning Best Practices） ​

分区不是「免费的加速开关」。它把一张表的维护问题转换成了结构设计问题：分区键选错了，裁剪永远失效；分区粒度选细了，规划开销反噬；唯一约束没提前规划，上线后只能返工重建整表。官方文档对分区设计的定性是「选择如何分区必须谨慎，因为糟糕的设计会负面地影响查询规划与执行的性能」。

本页把分区键选择、分区粒度、分区数量、约束与索引、DDL 自动化、运维流程整合成一套可落地的检查清单，并给出每条建议背后的原理与验证方法。

前置知识

阅读本节前，建议先了解：声明式分区概述、范围分区（RANGE）、分区裁剪（Partition Pruning）、分区索引策略（Partitioned Indexes）

基础概念 ​

分区收益的前提条件 ​

官方给出了一个经验判据：只有当表大到不分区会非常巨大时，分区收益才值得——判据是「表的规模应超过数据库服务器的物理内存」。

text
分区真正划算的三种情形：
  1. 热数据集中在少数分区（裁剪后只扫 1~2 个分区）
  2. 需要按时间/批次整批淘汰数据（DROP / DETACH 替代批量 DELETE）
  3. 冷热数据需要分层存储（老分区放到慢速廉价介质）

反过来，如果查询模式里根本没有分区键，那么分区只会带来规划开销与运维复杂度，没有任何收益。

三个必须提前定的决策 ​

分区设计的三个决策互相耦合，改动代价从低到高排列：

决策影响面事后可改吗
分区键（partition key）裁剪能力、唯一约束形态几乎不可改，要重建整棵树
分区方式（RANGE / LIST / HASH）数据分布形态、维护方式不可改，同上
分区粒度与数量规划开销、索引大小、缓存命中率可调（合并/拆分粒度），但需迁移数据

分区键一旦确定，主键就被迫包含它。 这个连锁反应决定了它必须第一个定、且必须定对。

分区数量的双刃剑 ​

官方原话是「不要想当然地认为分区越多越好，反之亦然」。两个方向各有代价：

分区太少分区太多
索引仍然过大查询规划时间变长
数据局部性差规划与执行期内存消耗升高
缓存命中率低每个分区都要向访问它的每个会话加载元数据
每个分区的数据量偏大会话数多时服务端内存显著增长

官方补充的一条重要定性：规划器能很好地处理「几千个分区」的层级，前提是典型查询能裁剪到只剩少数几个分区。

text
裁得好：1000 个分区 → 只扫 1 个 → 规划开销可忽略
裁不好：1000 个分区 → 全扫 → 规划时间可能超过执行时间

分区键与查询模式的对应关系 ​

text
查询 WHERE 里最常出现什么？          →  分区键就选它
  created_at BETWEEN ... AND ...        →  RANGE (created_at)
  region = 'CN' OR region = 'US'        →  LIST (region)
  tenant_id = $1                        →  HASH (tenant_id) 或 LIST
  没有稳定的过滤列                       →  慎重考虑是否该分区

判定标准只有一条，且很硬：分区键必须高频出现在查询的 WHERE 中，且条件形态要与分区边界兼容。

「要删的数据是否天然聚集」 ​

官方把「移除不需要的数据」也列为选择分区策略的考量因素，并给出关键提示：整分区可以被相当快地脱离，因此把「需要一次性删除的数据」设计成落在同一个分区里是有益的。

text
按月分区：淘汰「2025 年 1 月」= DROP TABLE orders_2025_01   ← 一次元数据操作
按 user_id 分区：淘汰「某批用户的历史数据」= 跨几百个分区删行   ← 退化成批量 DELETE

如果业务上真正需要淘汰的是「冷用户」，而分区键是 created_at，那么淘汰动作会被迫跨所有分区执行——这是设计阶段最容易漏掉的一致性检查。

语法与用法 ​

text
-- 1) 声明分区表：分区方式 + 分区键
CREATE TABLE parent ( ... ) PARTITION BY { RANGE | LIST | HASH } ( key [, ...] );

-- 2) 逐分区单独指定表空间与存储参数
CREATE TABLE p_2026_01 PARTITION OF parent FOR VALUES FROM (...) TO (...)
  TABLESPACE fast_ssd WITH (parallel_workers = 4);

-- 3) 约束与索引在父表声明，自动传导
ALTER TABLE parent ADD CONSTRAINT parent_pk PRIMARY KEY (id, key);
CREATE INDEX parent_key_idx ON parent (key);

-- 4) 结构自省
SELECT relid::regclass, level, isleaf FROM pg_partition_tree('parent');

一个符合最佳实践的最小完整示例：

sql
CREATE TABLE measurement (
  city_id   int  NOT NULL,
  logdate   date NOT NULL,
  peaktemp  int,
  unitsales int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2026m01 PARTITION OF measurement
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE measurement_y2026m02 PARTITION OF measurement
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE INDEX measurement_logdate_idx ON measurement (logdate);
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

注意最后一条索引：它在父表上声明，会自动传导到所有已有分区，以及之后新增或附加的任何分区。这正是官方声明式分区示例的第 3 步。

检查分区是否会被裁剪 ​

sql
EXPLAIN (COSTS OFF)
SELECT * FROM measurement WHERE logdate >= '2026-02-01' AND logdate < '2026-03-01';
text
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on measurement_y2026m02 measurement
   Filter: ((logdate >= '2026-02-01') AND (logdate < '2026-03-01'))

裁剪到单分区，Append 节点消失。如果计划里出现 Append 并列出全部分区，说明分区键设计或查询写法有问题。

详细说明 ​

用高频过滤列做分区键 ​

这是官方列出的「最关键的设计决策」：分区键应选那些最常出现在分区表查询 WHERE 子句里的列（或列组合）。因为只有与分区边界约束兼容的 WHERE 条件才能用来裁剪不需要的分区。

sql
-- 好：查询几乎都带时间范围
CREATE TABLE orders (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  user_id    bigint NOT NULL
) PARTITION BY RANGE (created_at);
sql
-- 差：业务查询从不按 region 过滤，分区键毫无用处
CREATE TABLE orders (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  region     text   NOT NULL
) PARTITION BY LIST (region);

自查方法是去统计真实查询的谓词分布，而不是凭直觉：

sql
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       left(query, 80) AS sample
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY total_exec_time DESC
LIMIT 10;
text
       queryid        |  calls  | total_ms |                    sample
----------------------+---------+----------+----------------------------------------------
 -8823174946218374201 | 9821334 | 88120433 | SELECT ... FROM orders WHERE created_at >=...
(1 row)

分区粒度：让每个分区「装得下、留得住」 ​

粒度选择要同时满足三个条件：

条件判据
单个分区不能太小否则分区数爆炸，规划开销上升
单个分区不能太大否则失去「索引变小、局部性变好」的收益
粒度应与淘汰周期对齐一次淘汰动作 = 一个分区

对时间序列表，一个常用的经验落点是「单分区数据量控制在内存的一个可观比例内，且分区总数控制在几十到几百之间」。

sql
-- 评估当前分区的实际体量，判断粒度是否合理
SELECT c.relname AS partition,
       c.reltuples::bigint AS est_rows,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'orders'::regclass
ORDER BY c.relname DESC
LIMIT 6;
text
   partition    | est_rows | total_size
----------------+----------+------------
 orders_2026_09 |  18400213 | 4210 MB
 orders_2026_08 |  17994321 | 4098 MB
 orders_2026_07 |  15100332 | 3502 MB
 orders_2026_06 |  14881002 | 3441 MB
 orders_2026_05 |  14330219 | 3310 MB
 orders_2026_04 |  13980112 | 3208 MB
(6 rows)

控制分区总数，并预估未来增长 ​

官方特别提醒要考虑未来的变化：如果按客户分区而当前只有少量大客户，那么几年后变成「大量小客户」时，LIST 分区的数量会失控。

text
反模式：按 customer_id 做 LIST 分区
  今天：50 个客户   → 50 个分区   ✅
  五年后：20 万客户 → 20 万个分区 ❌ 规划崩溃

替代方案是改用 HASH 分区并选定一个合理的分区数：

sql
-- 按 customer_id 哈希分 16 片，客户数增长不影响分区数
CREATE TABLE customer_events (
  customer_id bigint NOT NULL,
  event_at    timestamptz NOT NULL,
  payload     jsonb
) PARTITION BY HASH (customer_id);

CREATE TABLE customer_events_p0 PARTITION OF customer_events
  FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE customer_events_p1 PARTITION OF customer_events
  FOR VALUES WITH (MODULUS 16, REMAINDER 1);

定量巡检当前分区总数，把「分区数」当作一个需要监控的容量指标：

sql
SELECT count(*) AS partitions,
       pg_size_pretty(sum(pg_total_relation_size(inhrelid))) AS total_size
FROM pg_inherits
WHERE inhparent = 'orders'::regclass;
text
 partitions | total_size
------------+------------
        240 | 412 GB

让「要删的数据」落在同一个分区 ​

官方把这一条明确写进了最佳实践：整分区可以相当快地脱离，因此设计分区策略时，让「需要一次性删除的数据」落在单个分区里是有益的。

sql
-- 淘汰「2025 年 1 月」整批数据
DROP TABLE orders_2025_01;
text
DROP TABLE

对照反例——不要把「淘汰单元」切散到多个分区：

sql
-- 不推荐：按月分区却要淘汰「某个渠道」的全部历史数据
-- 结果必须扫描并删除全部 240 个分区里的行
DELETE FROM orders WHERE channel = 'legacy';
text
DELETE 98213441

前者是毫秒级元数据操作，后者是几千万行级别的批量删除，附带大量死元组与 VACUUM 压力。分区键的选择必须让「清理单元」与「分区边界」对齐。

主键与唯一约束提前设计 ​

这是最典型的「事后返工」点。分区表上的唯一约束必须包含全部分区键列，而分区键事后无法更改（分区表不能转成普通表，也不能改分区键）。

sql
-- 设计之初就把分区键纳入主键
CREATE TABLE orders (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

如果业务要求 id 全局唯一，必须提前确定替代方案（官方未提供直接手段，需自行设计）：

sql
-- 方案 A：外部小表承载全局唯一键
CREATE TABLE orders_id_registry (id bigint PRIMARY KEY);

-- 方案 B：应用侧生成全局唯一 ID（如 UUID / snowflake），把唯一性交给 ID 生成器
-- 方案 C：接受「分区内唯一」，用业务逻辑规避跨分区重复

方案选择的判断取决于「跨分区重复会造成什么后果」，这一点必须在建模评审里明确写下来。

DDL 必须脚本化，不要手写 ​

官方在声明式分区示例结尾专门提示：因为示例里每个月都要建新分区，写一个自动生成所需 DDL 的脚本是明智的。 官方的另一处措辞更直接：创建能生成子表并创建/修改关联对象的代码，比手写每一个更安全。

sql
-- 批量预建未来 6 个月分区，可重复执行
DO $$
DECLARE
  m     date := date_trunc('month', CURRENT_DATE)::date;
  i     int;
  pname text;
BEGIN
  FOR i IN 0..5 LOOP
    pname := format('orders_%s', to_char(m + (i || ' month')::interval, 'YYYY_MM'));
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
      pname,
      m + (i || ' month')::interval,
      m + ((i + 1) || ' month')::interval
    );
  END LOOP;
END $$;
text
DO

要点：用 format() 的 %I（标识符）与 %L（字面量）做安全拼接，加 IF NOT EXISTS 保证幂等。不要用字符串拼接把表名直接塞进 SQL。

认识分区的硬性限制 ​

以下限制直接决定方案可行性，必须在设计阶段确认：

限制后果
普通表与分区表不能互转「先建普通表、以后转分区」这条路不存在
分区键不可更改选错只能重建整棵树
唯一/主键约束必须含全部分区键见上节
分区不能继承除分区父表之外的任何表不能复用既有继承体系
同一分区树不允许混用临时表与永久表影响测试环境搭建方式
TRUNCATE ONLY 作用于分区表永远报错分区表本身无数据
父表上不能删除分区已有的 NOT NULL约束变更要按分区逐个处理
BEFORE ROW 触发器不能改行的目标分区触发器路由方案受限

逐条验证其中一个：

sql
CREATE TABLE measurement_only_test (city_id int, logdate date)
  PARTITION BY RANGE (logdate);
TRUNCATE ONLY measurement_only_test;
text
ERROR:  cannot truncate only a partitioned table
HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.

别忘了逐分区维护统计信息 ​

官方提示：如果使用手工 VACUUM / ANALYZE，不要忘了需要对每张子表单独执行。 自动清理（autovacuum）会自己处理每个分区，但手工维护与会话级参数设置都必须逐分区进行。

sql
-- 对每个分区单独执行 ANALYZE（父表层面不会递归到分区）
ANALYZE orders_2026_01;
ANALYZE orders_2026_02;

不过 PostgreSQL 18 有一个重要变化：VACUUM 与 ANALYZE 现在会处理父表的继承子表，旧行为需要用新增的 ONLY 选项来复现。因此在 PG 18 上：

sql
-- PostgreSQL 18：默认会处理全部分区
VACUUM (ANALYZE) orders;

-- 只想处理父表本身（不递归）
VACUUM (ANALYZE, ONLY) orders;

这改变了以往「父表 ANALYZE 毫无意义」的认知，是升级到 18 后需要重新审视的运维脚本点。

冷热分层：把老分区挪到慢速存储 ​

官方列出的分区收益之一就是「不常使用的数据可以迁移到更便宜、更慢的存储介质」。实现手段是给分区单独指定表空间。

sql
-- 热分区放在高速 SSD 表空间
CREATE TABLE orders_2026_09 PARTITION OF orders
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
  TABLESPACE fast_ssd;

-- 冷分区（归档）放在廉价大容量表空间
CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
  TABLESPACE slow_hdd;

把已有分区迁移到另一个表空间：

sql
ALTER TABLE orders_2024_01 SET TABLESPACE slow_hdd;

注意 SET TABLESPACE 会物理搬运文件，对大分区是一次实打实的 I/O 操作，应在低峰期逐分区执行。

sql
-- 巡检：各分区当前落在哪个表空间
SELECT c.relname, ts.spcname
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
JOIN pg_tablespace ts ON ts.oid = c.reltablespace
WHERE i.inhparent = 'orders'::regclass
ORDER BY c.relname;
text
   relname     |  spcname
---------------+-----------
 orders_2024_01| slow_hdd
 orders_2026_09| fast_ssd
(2 rows)

从分区表查询时保持普通写法 ​

官方在性能部分提示：分区有效地替代了索引的上层，使索引中频繁使用的部分更可能留在内存中；并且当查询或更新访问单个分区的很大比例时，用该分区的顺序扫描比用索引更快。

这意味着两件事：不要为了「走分区」而改写 SQL；也不要迷信索引。

sql
-- 正常写法即可，规划器会自动裁剪
SELECT sum(amount) FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 不推荐：手工写成 UNION ALL 反而增加了维护成本与出错概率
SELECT sum(amount) FROM orders_2026_01;

只需保证分区裁剪参数没有被关掉：

sql
SHOW enable_partition_pruning;
text
 enable_partition_pruning
--------------------------
 on

最后，把上面所有原则固化成一张上线前必须逐条确认的清单：

text
□ 分区键是查询 WHERE 中的高频过滤列吗？
□ 淘汰单元是否与某个分区边界对齐？
□ 分区键是否已纳入主键/唯一约束？
□ 分区总数在可接受范围（几十~几百）吗？
□ 未来 3~5 年的分区增长量是否预估过？
□ 是否有自动预建分区的脚本（幂等、可审计）？
□ 分区索引策略是否确定（级联 / ON ONLY + ATTACH）？
□ 冷热分区的表空间规划是否明确？
□ 有没有 DEFAULT 分区？它的体积是否被监控？
□ 逐分区 ANALYZE/VACUUM 的运维流程是否就位？

用一条 SQL 做上线前的结构自检：

sql
SELECT (SELECT count(*) FROM pg_inherits WHERE inhparent = 'orders'::regclass) AS partitions,
       (SELECT count(*) FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
        WHERE i.indrelid = 'orders'::regclass) AS parent_indexes,
       (SELECT count(*) FROM pg_inherits i WHERE i.inhparent = 'orders'::regclass
        AND i.inhdetachpending) AS pending_detach;
text
 partitions | parent_indexes | pending_detach
------------+----------------+----------------
        240 |              3 |              0
(1 row)

实战示例 ​

第 1 步：评估是否真的需要分区 ​

sql
SELECT pg_size_pretty(pg_total_relation_size('orders_flat')) AS table_size,
       (SELECT setting::bigint * 8192 FROM pg_settings WHERE name = 'shared_buffers') AS shared_buffers_bytes;
text
 table_size | shared_buffers_bytes
------------+----------------------
 412 GB     |           8589934592

表大小远超物理内存（shared_buffers），符合官方「表规模应超过服务器物理内存」的判据，分区收益可观。

第 2 步：按查询模式选分区键 ​

sql
SELECT left(query, 70) AS sample, calls
FROM pg_stat_statements
WHERE query ILIKE '%orders_flat%'
ORDER BY total_exec_time DESC
LIMIT 3;
text
                                sample                                |  calls
----------------------------------------------------------------------+---------
 SELECT ... FROM orders_flat WHERE created_at >= $1 AND created_at < $2 | 9821334
 SELECT ... FROM orders_flat WHERE id = $1                             |  412300
 SELECT ... FROM orders_flat WHERE created_at >= $1 AND user_id = $2    |  881200

三条最高频查询里有两条带 created_at 范围条件，分区键定为 created_at。

第 3 步：建表时同步规划约束与索引 ​

sql
CREATE TABLE orders (
  id         bigint        NOT NULL,
  user_id    bigint        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at date          NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
text
CREATE TABLE
CREATE INDEX

主键把分区键 created_at 包含进来，避免了事后返工。

第 4 步：脚本化预建分区 ​

sql
DO $$
DECLARE
  m     date := date_trunc('month', DATE '2026-01-01')::date;
  i     int;
  pname text;
BEGIN
  FOR i IN 0..11 LOOP
    pname := format('orders_%s', to_char(m + (i || ' month')::interval, 'YYYY_MM'));
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
      pname,
      m + (i || ' month')::interval,
      m + ((i + 1) || ' month')::interval
    );
  END LOOP;
END $$;
text
DO

第 5 步：确认新分区自动继承了索引 ​

sql
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders_2026_06'::regclass;
text
              index_name               | indisvalid | indisready
---------------------------------------+------------+------------
 orders_2026_06_pkey                   | t          | t
 orders_2026_06_user_created_idx       | t          | t
(2 rows)

第 6 步：验证裁剪与分区内索引选择 ​

sql
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01'
  AND user_id = 4242;
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using orders_2026_06_user_created_idx on orders_2026_06 orders
   Index Cond: ((user_id = 4242) AND ...)
(2 rows)

裁剪到单分区，且在该分区内选中了复合子索引。

第 7 步：把分区数纳入容量巡检 ​

sql
SELECT count(*) AS partitions,
       pg_size_pretty(sum(pg_total_relation_size(inhrelid))) AS total_size,
       pg_size_pretty(avg(pg_total_relation_size(inhrelid))::bigint) AS avg_partition_size
FROM pg_inherits
WHERE inhparent = 'orders'::regclass;
text
 partitions | total_size | avg_partition_size
------------+------------+--------------------
         12 |    24 GB   | 2048 MB
(1 row)

注意事项 ​

  • 分区键事后无法更改。 分区表不能转回普通表，也不能修改分区键；选错只能重建整棵树并迁移数据。

    sql
    -- 不存在这样的语法：ALTER TABLE ... PARTITION BY ...
-- 只能新建表 + 数据搬迁 + 改名切换

  • 不要忽略「唯一约束必须含分区键」这条连锁约束。 它决定了主键形态，必须与分区键一并设计。

    sql
    -- 错误！
PRIMARY KEY (id)     -- 分区键 created_at 未包含
    text
    ERROR:  unique constraint on partitioned table must include all partitioning columns

  • 不要为了「看起来能裁剪」而把分区键做成超细粒度。 分区数是规划开销与内存的直接来源。

    text
    -- 不推荐：按天分区 × 5 年 = 1800+ 分区
-- 推荐：按周或按月，配合合理的保留策略

  • 不要把淘汰单元切散到多个分区。 这会让「瞬时删除」退化成批量 DELETE。

    sql
    -- 不推荐
DELETE FROM orders WHERE channel = 'legacy';

  • 不要手写每个分区的 DDL。 官方明确建议用代码生成子表及相关对象。

    sql
    -- 正确：脚本化、幂等
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
               pname, lo, hi);

  • 不要忘记分区表与普通表不能互转。 「先用普通表上线，量大了再改成分区表」这条路走不通。

    sql
    -- 这类语句不存在
-- ALTER TABLE orders_flat PARTITION BY RANGE (created_at);

  • 不要手写 ONLY 做分区级维护而忘了逐分区执行。 父表上的手工维护不会自动覆盖分区（PG 18 除外，其 VACUUM/ANALYZE 会递归）。

    sql
    -- PostgreSQL 18 之前：父表 ANALYZE 不覆盖分区
ANALYZE orders_2026_01;

  • 不要指望 BEFORE ROW 触发器能改变行的目标分区。 官方明确限制：分区表上的 INSERT 触发器不能改变哪一行进入哪个分区。

    text
    -- 路由由分区键决定，触发器只能在"已确定分区"之后做加工

  • 不要在同一个分区树里混用临时表与永久表。 官方列为不允许的行为，会让测试环境搭建方式受限。

    text
    -- 分区树必须全为永久表，或全为临时表

  • 不要忘了 TRUNCATE ONLY 对分区表永远报错。 分区表本身没有存储。

    text
    ERROR:  cannot truncate only a partitioned table

最佳实践 ​

  • 分区键选「查询 WHERE 里的高频过滤列」。 这是官方给出的最关键设计决策，直接决定裁剪能力。

    sql
    -- 高峰查询多为 created_at 范围条件
PARTITION BY RANGE (created_at)

  • 表的规模超过物理内存时再分区。 官方经验判据，避免为小表引入无谓复杂度。

    sql
    SELECT pg_size_pretty(pg_total_relation_size('orders_flat')) AS table_size;

  • 把主键与分区键一并设计，唯一约束含全部分区键列。

    sql
    PRIMARY KEY (id, created_at)

  • 让「淘汰单元」与分区边界对齐。 淘汰动作应能落成一次 DROP TABLE 或 DETACH。

    sql
    DROP TABLE orders_2025_01;

  • 分区总数控制在几十到几百，并对未来增长建模。 官方指出规划器能处理几千个分区，前提是裁剪到少数几个。

    sql
    SELECT count(*) FROM pg_inherits WHERE inhparent = 'orders'::regclass;

  • 增长不确定时优先用 HASH 而不是 LIST。 官方原文指出：与其用 LIST 分区并期望「客户数不会增长到不实用的程度」，不如改用 HASH 并选定合理分区数。

    sql
    PARTITION BY HASH (customer_id)
-- 各分区：FOR VALUES WITH (MODULUS 16, REMAINDER n)

  • DDL 全部脚本化，用 format() + IF NOT EXISTS 保证幂等可审计。

    sql
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
               pname, lo, hi);

  • 索引与约束在父表声明，让级联自动覆盖未来分区。

    sql
    CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);

  • 冷热数据用表空间分层，老分区迁到廉价介质。 这是官方列出的分区收益之一。

    sql
    ALTER TABLE orders_2024_01 SET TABLESPACE slow_hdd;

  • 把分区结构巡检固化成一条 SQL，纳入日常监控。 分区数、父索引数、pending detach 三项一起看。

    sql
    SELECT (SELECT count(*) FROM pg_inherits WHERE inhparent = 'orders'::regclass) AS partitions,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = 'orders'::regclass) AS parent_indexes,
       (SELECT count(*) FROM pg_inherits i
        WHERE i.inhparent = 'orders'::regclass AND i.inhdetachpending) AS pending_detach;

下一节

下一节：表空间创建与使用（Tablespace）——从分区表的存储分层出发，学习表空间的创建、目录布局与在分区表上的实际用法。

参考链接 ​