PostgreSQL 分区最佳实践

概述

分区的本质是将一张大的物理表从逻辑上拆分，为 N 个较小的物理表。

分区表按照官方的解释如下：

The partitioned table itself is a “virtual” table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise-ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its partition bounds. All rows inserted into a partitioned table will be routed to the appropriate one of the partitions based on the values of the partition key column(s). Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition.

分区表本体作为 「虚拟表」 存在，自身不持有实际存储空间。其物理存储由关联的分区（即普通物理表，子表）承担，每个分区通过预定义的分区边界（Partition Bounds）存储对应的数据子集。所有插入操作将依据分区键列（Partition Key）的值自动路由到目标分区。若更新某行的分区键值导致其超出原分区的边界，该行将被迁移至新的分区。

按照上面的解释，我们可以得出以下的一些结论：

数据存储：分区表的主表是一张逻辑表（虚拟表），它不负责存储数据，只负责数据的分发，所有的数据都是存储在子表中。因此，主表的数据操作是不会产生 WAL 日志，他的 WAL 日志会由基础的子表产生。所以，我们的数据实时同步，应该监听的是子表，才能获取到 WAL 日志。
数据分发：当对主表的任何一个操作，PG 会经过处理转化下发到指定的子表。
- 数据插入&删除：根据分区键的分区策略，将操作数据自动路由到指定子分区。
- 数据更新：
  - 路由查找：数据更新的 WHERE 语句中包含分区键，则会自动到路由到指定的子表。如果没有，则会将此 UPDATE 路由到所有的分区，找到待更新的数据。
  - 数据更新中带有分区字段：假如数据更新中，更新了分区字段，且此分区字段的值改变了此条数据的所属分区，则会执行两个操作：
    - 现所属分区删除数据：因为此数据已不属于该分区，所以此条更新的数据将从此分区删除。
    - 新分区插入数据：将 UPDATE 后的数据 INSERT 到新分区。
- 数据查询：
  - 查询条件中带有分区字段且能够定位到指定的一个分区：直接查询该分区的数据，然后返回。
  - 查询条件中无分区字段或分区字段的值只能定位到一个模糊的分区范围：查询定位到的 N 个分区，然后再把查询出来的数据，进行二次处理，返回。
DDL 操作：在父表上面的任何 DDL 操作，都会经过处理，分配到每个子表上面。

分区方式

PostgreSQL 提供了以下的分区方式

范围分区（Range Partitioning)

基于分区键列（单列或多列）划分

连续且互斥

的数值区间。例如按日期范围（如 2023-Q1）或业务 ID 区间划分。边界规则：包含下限值，不包含上限值（即左闭右开）。
示例：分区 A 范围[1,10)，分区 B 范围[10,20)，数值 10 归属分区 B。

列表分区 (List Partitioning)

通过

显式枚举分区键值

定义分区。每个分区存储指定的离散值集合。
示例：按地区分区，华东分区包含('上海','江苏','浙江')。

哈希分区 (Hash Partitioning)

通过

取模运算

分配数据：指定模数（modulus）和余数（remainder），分区键哈希值取模后匹配余数的行存入对应分区。
示例：模数=4，余数=0 的分区存储哈希值 mod 4 = 0 的数据行。

对比

维度	范围分区 (Range)	列表分区 (List)	哈希分区 (Hash)
分区逻辑	连续区间（数值/日期等）	离散值枚举（地区/状态等）	哈希取模运算
边界定义	`FROM A TO B`（左闭右开）	`IN (v1, v2...)`	`WITH (MODULUS N, REMAINDER M)`
数据分布	可能不均匀（如历史数据集中）	人工指定，灵活但需预定义	强制均匀分布
查询优化	▶️ 高效支持范围查询 ▶️ 分区剪枝优化	▶️ 精准匹配查询快 ▶️ 等值查询优化	▶️ 等值查询快 ▶️ 并行扫描均衡
典型场景	时间序列（日志、销售记录）	业务分类（地区、产品线）	分布式存储（用户 ID、随机键）
边界管理	需防区间重叠	需防值重复	余数需覆盖 0 到(modulus-1)
缩容成本	高（需重组相邻分区）	中（修改枚举列表）	极高（需重分布所有数据）
扩容成本	低（增加新分区即可）	低（增加新分区即可）	极高（需重分布所有数据）
子分区支持	✅ 多级分区（如年 → 月）	✅ 多级分区（如国家 → 城市）	⚠️ 仅单层

优缺点

优点

查询性能优化
- 分区剪枝
  ：自动跳过无关分区（如 WHERE date > '2023-01-01' 仅扫描新分区）
- 局部索引
  ：高频分区索引常驻内存，减少 I/O
- 并行扫描
  ：不同分区可由多个 Worker 同时读取
数据管理高效
- 秒级删除旧数据
  ：DROP TABLE partition_2020 比 DELETE 快 1000 倍以上
- 零碎片化
  ：避免 DELETE 导致的表膨胀和 VACUUM 压力
运维灵活性
- 滚动维护
  ：分区级 VACUUM 不锁全表
- 动态挂载
  ：ATTACH/DETACH PARTITION 实现数据秒级切换
- 避免出现超级大表
  ：超级大表的维护会异常的困难（例如添加索引、字段和修复数据等操作），消耗的性能和花费是时间都会让表的维护异常的困难！

缺点

设计复杂性
- 需要合理的分区设计
  ：需要合理的选择分区方案，假如分区方案选择不合理，会加大系统的负载和分区管理的复杂，导致运维起来更为复杂。
功能限制
- 全局约束受限
  ：唯一索引必须包含所有分区键
- 跨分区事务缺失
  ：不支持分布式 ACID（如跨分区行级锁）
- 子分区扩展列禁止
  ：所有分区必须与父表列完全一致
性能陷阱
- 分区键更新代价高
  ：触发行迁移（等效 DELETE + INSERT）
- 规划器超时风险
  ：超过 1000 个分区时查询计划生成延迟显著增加
- 元数据内存膨胀
  ：每个会话缓存分区树，消耗额外 RAM
运维成本
- 统计信息收集繁琐
  ：需对每个分区单独 ANALYZE
- 工具链兼容性差
  ：部分 ORM/备份工具无法正确处理分区表
- 版本升级风险
  ：PG 10-13 的分区管理性能远低于 PG 14+
对开发要求更高：
- 合理使用分区特性门槛较高：
  分区表的高效查询插入需要指定条件才能触发，如果使用不当，反而会加大数据库的负载！
- 分区表日常维护更为复杂：
  分区表的索引、字段和分区的维护比单表更为复杂，需要详细了解才能避免各种风险！

‍

分区操作

下面以这张 parcel 表来示例，我们是如何合理的进行分区操作：

CREATE TABLE parcel ( id INTEGER DEFAULT NEXTVAL('parcel_id_seq'::REGCLASS) NOT NULL, tracking_number VARCHAR, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, transporter VARCHAR(255), platform VARCHAR(50), failed_count INTEGER DEFAULT 0, server_name VARCHAR(255), archived BOOLEAN DEFAULT FALSE NOT NULL, PRIMARY KEY (id) );

分区规则

首先，我们的分区规则如下：

一级分区主要根据 archived 字段分区：

archived=FALSE : 则数据保留在 ord_parcel_hot 子表中
archived=TRUE : 则数据保留在 ord_parcel_history 子表中

二级分区是再根据 created_at 等时间字段，在 ord_parcel_history 的基础上再进行划分分区

archived=TRUE & created_at = '2025-07-21 00:00:00' ：数据表留在 ord_parcel_history_2025 分区
archived=TRUE & created_at = '2024-07-21 00:00:00' ：数据表留在 ord_parcel_history_2024 分区
...

简单来说，archived 控制是否在 hot 表，还是在 history 表，created_at 控制在那张 history 表

history 分区范围不一定要是按照年分区，假如数据量比较大，则也可以改为半年或季度分区，这个主要取决于数据量大小，建议单个 history 分区的数据量 < 5000w

分区表的结构如下：

--| parcel └--|parcel_hot └--|parcel_history └--|parcel_2025 └--|parcel_2024 └--|parcel_2023 └--|parcel_xxxx └--|parcel_before

创建分区表

创建 parcel 主表

要点：

主键为 (id, archived, created_at)

分区方式和分区键：PARTITION BY LIST (archived)

分区方式为 LIST 分区

分区键为 archived 字段

CREATE TABLE parcel ( id INTEGER DEFAULT NEXTVAL('parcel_id_seq'::REGCLASS) NOT NULL, tracking_number VARCHAR, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, transporter VARCHAR(255), platform VARCHAR(50), failed_count INTEGER DEFAULT 0, server_name VARCHAR(255), archived BOOLEAN DEFAULT FALSE NOT NULL, PRIMARY KEY (id, archived, created_at) ) PARTITION BY LIST (archived);

创建 parcel_hot 数据表挂载在 parcel 数据表下面

-- 为 parcel 添加分区： -- 当 archived = FALSE，则分配至 hot 表 CREATE TABLE parcel_hot PARTITION OF parcel FOR VALUES IN (FALSE);

创建二级分区的主表 parcel_history，挂载在 parcel 数据表下面

-- 当 archived = TRUE，则分配至 history 表,且此表再根据 created_at 的 RANGE 分区方式，再进行分区 CREATE TABLE parcel_history PARTITION OF parcel FOR VALUES IN (TRUE) PARTITION BY RANGE (created_at);

创建 parcel_history 下面的子表，均挂载到 parcel_history 二级分区的主表下

-- 添加 clr_parcel_clearance_history_2025 至 clr_parcel_clearance_history CREATE TABLE parcel_history_2025 PARTITION OF parcel_history FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'); CREATE TABLE parcel_history_2024 PARTITION OF parcel_history FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); CREATE TABLE parcel_history_2023 PARTITION OF parcel_history FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

以上，就完成了一张分区表的创建！

索引维护

官方文档：

As mentioned earlier, it is possible to create indexes on partitioned tables so that they are applied automatically to the entire hierarchy. This can be very convenient as not only will all existing partitions be indexed, but any future partitions will be as well. However, one limitation when creating new indexes on partitioned tables is that it is not possible to use the CONCURRENTLY qualifier, which could lead to long lock times. To avoid this, you can use CREATE INDEX ON ONLY the partitioned table, which creates the new index marked as invalid, preventing automatic application to existing partitions. Instead, indexes can then be created individually on each partition using CONCURRENTLY and attached to the partitioned index on the parent using ALTER INDEX ... ATTACH PARTITION. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically.

如前所述，在分区表上创建索引时可使其自动应用于整个分区层次结构。这种方式非常便捷——不仅所有现有分区会建立索引，未来新增的分区也将自动同步创建。但需要注意，分区表创建新索引时存在一项限制：无法使用 CONCURRENTLY 修饰符，这可能导致长时间锁定表。

为避免此问题，可采用 CREATE INDEX ... ONLY 语法在分区表上创建索引，此时新建索引会被标记为无效状态，且不会自动应用到现有分区。随后可执行以下操作：

在每个分区上使用 CONCURRENTLY 分别创建索引

通过 ALTER INDEX ... ATTACH PARTITION 将分区索引挂载至父表的索引

当所有分区索引都完成挂载后，父级索引将自动标记为生效状态。

由上面的官方文档我们可以得知：

分区表的父表是虚拟表，所以它的索引也是虚拟索引，当操作父表的索引的时候，它会在所有的子表上面，都创建和父表等效的索引。
创建主表的索引，无法使用 CONCURRENTLY 关键字，这意味着在主表上面操作索引，会进行长时间的锁表。
- 官方建议使用 CREATE INDEX ... ONLY 解决锁表问题

因此，我们创建索引有两种方式：父表创建索引和子表创建索引，两种创建索引的对比：

特性	父表（Partitioned Table）创建索引	子表（Partition）创建索引
索引定义方式	`CREATE INDEX idx_parent ON parent_table (key);` （自动级联到所有子表）	需在每个子表单独创建： `CREATE INDEX idx_child1 ON child1 (key);`
索引物理存储	虚拟索引（无实际数据），实际数据在各子表的本地索引	独立的物理索引
查询优化器行为	自动识别分区剪枝，仅扫描相关分区的本地索引	需手动确保所有子表有索引，否则未索引分区全表扫描
新增分区支持	自动为新分区创建索引	需手动为新分区创建索引
索引类型限制	不支持表达式索引/部分索引（需在子表单独创建）	支持任意索引类型
唯一约束实现	必须包含分区键（全局唯一性难保障）	可创建子表局部唯一索引（但无法跨分区唯一）

索引添加

因此，根据以上的信息，假如我们需要在数据表上面添加索引，按照下面的例子：

本次我们目前需要在 parcel_history 上面添加 tracking_number 索引。

使用 CREATE INDEX ... ONLY 关键字在主表 parcel_history 上面添加索引：

-- 注意添加 ONLY 关键字 CREATE INDEX idx_parcel_history_tracking_number ON ONLY parcel_history (tracking_number);

使用此 SQL 查询当前父表 parcel_history 索引是否标记为有效：

-- 当前索引状态应该返回 FALSE SELECT c.relname AS index_name, i.indisvalid AS is_valid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = 'idx_parcel_history_tracking_number';

在对应子表上面使用 CONCURRENTLY 关键字添加索引，避免锁表操作：

 CREATE INDEX CONCURRENTLY idx_parcel_history_2025_tracking_number ON parcel_history_2025 (tracking_number); CREATE INDEX CONCURRENTLY idx_parcel_history_2024_tracking_number ON parcel_history_2024 (tracking_number); -- ...

将新加的索引，通过 ATTACH PARTITION 操作，添加到 parcel_history 表 idx_parcel_history_tracking_number 上面：

ALTER INDEX idx_parcel_history_tracking_number ATTACH PARTITION idx_parcel_history_2021_tracking_number; ALTER INDEX idx_parcel_history_tracking_number ATTACH PARTITION idx_parcel_history_2022_tracking_number;

待所有子表都添加完索引后，校验父表 parcel_history 索引标记是否有效：

-- 当前索引状态应该返回 TRUE SELECT c.relname AS index_name, i.indisvalid AS is_valid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = 'idx_parcel_history_tracking_number';

此步可忽略：校验阶段，添加一个新的分区表，查看新分区表是否添加了对应的索引：

CREATE TABLE parcel_history_2026 PARTITION OF parcel_history FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

索引删除

假如子表的索引是由父表进行维护，则当通过子表去删除索引的时候，这个操作是不允许的，PostgreSQL 会直接拒绝掉这个操作：

DROP INDEX idx_parcel_history_2025_tracking_number; -- ERROR: cannot drop index parcel_2023_tracking_number_idx because index idx_parcel_history_tracking_number requires it -- 建议：You can drop index idx_parcel_history_tracking_number instead.

所以，假如需要某个分区表的索引，则一定需要删除父表索引，子表的索引就会自动删除！

DROP INDEX idx_parcel_history_tracking_number; -- completed in 400 ms

创建新分区

在 parcel_history 数据表上面，创建 parcel_history_2026 新分区

CREATE TABLE parcel_history_2026 PARTITION OF parcel_history FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

挂载分区

流程介绍

挂载分区的操作和创建新分区来对比，复杂了很多！因为创建新分区是生成一张全新的数据表，PostgreSQL 只需要维护对应的元数据（字段、索引、分区约束和主键等等），而挂载新分区的时候，因为待挂载的分区已经存在大量的数据了，在挂载到分区表之前，PostgreSQL 需要做一堆数据校验工作，以下是 PostgreSQL 文档的原文：

Note that when running the ATTACH PARTITION command, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on that partition. As shown above, it is recommended to avoid this scan by creating a CHECK constraint matching the expected partition constraint on the table prior to attaching it. Once the ATTACH PARTITION is complete, it is recommended to drop the now-redundant CHECK constraint. If the table being attached is itself a partitioned table, then each of its sub-partitions will be recursively locked and scanned until either a suitable CHECK constraint is encountered or the leaf partitions are reached.

请注意，执行 ATTACH PARTITION 命令时，
将对分区表加

ACCESS EXCLUSIVE

锁
，
并扫描表内数据以验证分区约束
。如前所述，建议在挂载分区
之前
，在目标表上
预先创建一个与预期分区约束相匹配的

CHECK

约束
，
以规避此扫描操作
。ATTACH PARTITION 操作完成后，
建议删除此时已冗余的

CHECK

约束
。
如果待挂载的表本身也是一个分区表
，那么它的
每个子分区都将被递归地加锁并扫描
，
直到遇到匹配的

CHECK

约束或到达叶子分区为止
。

For each index in the target table, a corresponding one will be created in the attached table; or, if an equivalent index already exists, it will be attached to the target table's index, as if ALTER INDEX ATTACH PARTITION had been executed.

对于目标表中的每个索引，系统
将在被挂载的表中新建一个对应索引
；或者，
若该表上已存在结构等效的索引，则直接将该索引挂载至目标表的索引层级
——该操作等同于
自动执行了

ALTER INDEX ATTACH PARTITION

命令
。

根据上面的官网信息，我们可以得知以下几点：

ATTACH PARTITION 操作，会为数据表添加
ACCESS EXCLUSIVE
（访问独占锁，阻塞该表的所有操作），这个操作将导致数据表锁死，严重影响业务系统的操作
ATTACH PARTITION 操作，有两个比较耗时的操作，但是目前这两个操作，官方都提供了解决方案！
- 对待添加的分区表添加
  CHECK
  约束校验，校验改分区内的所有数据，是否都满足分区键的约束！
- 校验待添加的分区表中，是否存在和父表的等效索引，以维护父表的索引在子表中的传递！

下面的流程图，是 DeepSeek 对 1000w 的数据表执行 ATTACH PARTITION 大致流程：

‍

graph TD A[开始ATTACH PARTITION] --> B[立即请求 ACCESS EXCLUSIVE 锁] B --> C{是否预创建已验证的 CHECK 约束？} C -- 是 --> D[跳过全表扫描<br>直接信任约束] C -- 否 --> E[在锁保护下扫描数据<br>SSD:20-50min] D --> F{新分区是否有等效索引？} E --> F F -- 无索引 --> G[在锁保护下创建索引<br>SSD:10-30min] F -- 有索引 --> H[挂载索引<br>0.1-1s] G --> I[更新元数据] H --> I I --> J[删除预创建约束] J --> K[结束释放锁] classDef red fill:#f9d5d5,stroke:#e88; classDef green fill:#d5f0d5,stroke:#8e8; class E,G red; class D,H green;

由流程图可以得知，假如我们控制好约束和索引，则 ATTACH PARTITION 基本上可以在秒级执行（这点我已经做过测试）！

实际操作-hot 表

目前我们需要将一张 4000w 的 parcel_hot 挂载到 parcel 数据表。

parcel 表目前有如下特征：

索引：有一个 tracking_number 的索引，idx_parcel_tracking_number
主键：主键为 id, archived, created_at，主键名称为 parcel_pkey

parcel_hot 有如下特征：

索引：无任何索引
主键：有一个 id, created_at 主键，主键名称为 parcel_hot_pkey

分区校验

首先，我们需要提前执行好 parcel_hot 的分区约束，避免执行 ATTACH PARTITION 时，锁表太长时间。parcel_hot 分区约束比较简单：archived=FALSE。下面是约束执行的详细 SQL：

-- 添加 parcel_hot_archived_false 约束，并且只对新数据执行，老数据不执行校验 ALTER TABLE parcel_hot ADD CONSTRAINT parcel_hot_archived_false CHECK (archived = FALSE) NOT VALID; -- completed in 244 ms -- 校验 parcel_hot_archived_false 老数据校验 ALTER TABLE parcel_hot VALIDATE CONSTRAINT parcel_hot_archived_false; -- completed in 37 s 561 ms

主键替换

目前 parcel_hot 的主键为 id, created_at，而 parcel 的主键为 id, archived, created_at，而这主键不一致，因此无法进行挂载，所以需要我们手动更换主键，对齐两张数据表的主键！下面是更换主键的 SQL 操作：

-- 增加新的主键 CREATE UNIQUE INDEX CONCURRENTLY parcel_hot_pkey_new ON parcel_hot (id, archived, created_at); -- Time: 160.987s -- 替换主键 BEGIN; ALTER TABLE parcel_hot DROP CONSTRAINT parcel_hot_pkey; ALTER TABLE parcel_hot ADD PRIMARY KEY USING INDEX parcel_hot_pkey_new; COMMIT; -- 此事务 0.5s 左右

等效索引

目前 parcel 有一个 tracking_number 索引，而 parcel_hot 无任何索引，因此需要在 parcel_hot 也添加 tracking_number 索引。下面是添加索引的 SQL：

CREATE INDEX CONCURRENTLY idx_parcel_hot_tracking_number ON parcel_hot (tracking_number); -- completed in 53 s 704 ms

挂载分区

以上的操作钧执行完成后，现在就可以执行挂载分区的操作了！

ALTER TABLE parcel ATTACH PARTITION parcel_hot FOR VALUES IN (FALSE) -- Time: 0.277s

删除分区校验

ALTER TABLE parcel_hot DROP CONSTRAINT parcel_hot_archived_false;

实际操作-history

目前我们需要将一张 4000w 的 parcel_history_2023 挂载到 parcel_history 数据表。

parcel_history 表目前有如下特征：

索引：有一个 tracking_number 的索引，idx_parcel_history_tracking_number
主键：主键为 id, archived, created_at，主键名称为 parcel_history_pkey

parcel_history_2023 有如下特征：

索引：无任何索引
主键：有一个 id, created_at 主键，主键名称为 parcel_history_2023_pkey

分区校验

首先，我们需要提前执行好 parcel_history_2023 的分区约束，避免执行 ATTACH PARTITION 时，锁表太长时间。parcel_history_2023 分区约束比较简单：archived=FALSE AND created_at >= '2023-01-01' AND created_at < '2024-01-01'。

注意：一定不能够将分区条件写为下面这样：
archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01'

因为 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01') 的时间区间为：['2023-01-01 00:00:00', '2024-01-01 00:00:00)

而 BETWEEN '2023-01-01' AND '2024-01-01' 的时间取件为：['2023-01-01 00:00:00', '2024-12-01 00:00:00]

当写成 archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01' 这样，依旧会执行分区校验的 SQL，导致锁表时间大大的加长了！

下面是约束执行的详细 SQL：

-- 添加 parcel_hot_archived_false 约束，并且只对新数据执行，老数据不执行校验 ALTER TABLE parcel_history_2023 ADD CONSTRAINT parcel_history_2023_archived_true_created_at CHECK (archived = TRUE AND created_at >= '2023-01-01' AND created_at < '2024-01-01') NOT VALID; -- Time: 0.194s -- completed in 244 ms -- 校验 parcel_hot_archived_false 老数据校验 ALTER TABLE parcel_history_2023 VALIDATE CONSTRAINT parcel_history_2023_archived_true_created_at; -- Time: 72.051s

主键替换

目前 parcel_history_2023 的主键为 id, created_at，而 parcel_history 的主键为 id, archived, created_at，而这主键不一致，因此无法进行挂载，所以需要我们手动更换主键，对齐两张数据表的主键！下面是更换主键的 SQL 操作：

-- 增加新的主键 CREATE UNIQUE INDEX CONCURRENTLY parcel_history_2023_new_key ON parcel_history_2023 (id, archived, created_at); -- Time: 160.987s -- 替换主键 BEGIN; ALTER TABLE parcel_hot DROP CONSTRAINT parcel_history_2023_pkey; ALTER TABLE parcel_hot ADD PRIMARY KEY USING INDEX parcel_history_2023_pkey_new; COMMIT; -- 此事务 0.5s 左右

等效索引

目前 parcel_history 有一个 tracking_number 索引，而 parcel_history_2023 无任何索引，因此需要在 parcel_history_2023 也添加 tracking_number 索引。下面是添加索引的 SQL：

CREATE INDEX CONCURRENTLY idx_parcel_history_2023_tracking_number ON parcel_history_2023 (tracking_number); -- completed in 53 s 704 ms

挂载分区

以上的操作钧执行完成后，现在就可以执行挂载分区的操作了！

ALTER TABLE parcel_history ATTACH PARTITION parcel_history_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') -- Time: 0.277s

删除分区校验

ALTER TABLE parcel_history_2023 DROP CONSTRAINT parcel_history_2023_archived_true_created_at;

卸载分区

卸载分区一般速度都比较快，所以相对来说比较安全。卸载分区后，卸载的分区将以独立的数据表存在，且不再与主表有任何关联。

将 parcel_history_2023 从 parcel_history 分区卸载：

锁表卸载分区（锁的时间很短）：

ALTER TABLE parcel_history DETACH PARTITION parcel_history_2023;

并发卸载分区（不锁表）：

ALTER TABLE parcel_history DETACH PARTITION parcel_history_2023 CONCURRENTLY;

‍

参考文档

PostgreSQL: Documentation: 17: ALTER TABLE

PostgreSQL: Documentation: 17: 5.12. Table Partitioning

‍

昵称

邮箱

主页

PostgreSQL 分区最佳实践

概述

分区方式

范围分区（Range Partitioning)

连续且互斥

列表分区 (List Partitioning)

显式枚举分区键值

哈希分区 (Hash Partitioning)

取模运算

对比

维度

分区逻辑

边界定义

数据分布

查询优化

典型场景

边界管理

缩容成本

扩容成本

子分区支持

优缺点

优点

查询性能优化

分区剪枝

局部索引

并行扫描

数据管理高效

秒级删除旧数据

零碎片化

运维灵活性

滚动维护

动态挂载

避免出现超级大表

缺点

设计复杂性

需要合理的分区设计

功能限制

全局约束受限

跨分区事务缺失

子分区扩展列禁止

性能陷阱

分区键更新代价高

规划器超时风险

元数据内存膨胀

运维成本

统计信息收集繁琐

工具链兼容性差

版本升级风险

对开发要求更高：

合理使用分区特性门槛较高：

分区表日常维护更为复杂：

分区操作

分区规则

创建分区表

索引维护

特性

父表（Partitioned Table）创建索引

子表（Partition）创建索引

索引定义方式

索引物理存储

查询优化器行为

本地索引

新增分区支持

索引类型限制

唯一约束实现

索引添加

索引删除

创建新分区

挂载分区

流程介绍

将对分区表加

​ACCESS EXCLUSIVE​

锁

并扫描表内数据以验证分区约束

之前

预先创建一个与预期分区约束相匹配的

​CHECK​

约束

以规避此扫描操作

建议删除此时已冗余的

`ACCESS EXCLUSIVE`

`CHECK`

`CHECK`

`CHECK`

`ALTER INDEX ATTACH PARTITION`

`ACCESS EXCLUSIVE`

`CHECK`