数据库教程FGMT67：PostgreSQL性能优化之分区表

数据库教程FGMT67：PostgreSQL性能优化之分区表

一、前言与内容大纲

在生产环境中，数据库表的数据量会随着业务增长不断膨胀。当一张业务表的行数达到千万级、亿级甚至更高时，无论是数据写入、查询扫描还是日常维护，都会面临显著的性能瓶颈。风哥在十余年的数据库运维服务中，处理过大量海量数据场景，几乎每个大型项目最终都要借助分区表这一核心手段来化解大表性能难题。本套风哥教程将围绕分区表这一主题，从原理到实战，带读者完整掌握 PostgreSQL 分区表的设计、创建、管理与优化技能。

风哥教程本文的定位是"理论为辅、实战为主"。全文结构安排如下：第一部分为前言与大纲，帮助读者建立整体认知；第二部分为理论部分，深入讲解什么是分区表、声明式分区与继承式分区的区别、分区表技术的发展演进，以及分区裁剪、分区键选择等核心原理；第三部分为实战部分，以一台 64G 内存、8 核 CPU 的生产规格主机为环境，完整演示范围分区、HASH 分区、列表分区、复合分区的创建，以及分区表的查看、数据更新、增删分区、与普通表转换、拆分合并分区和参数调优等全部运维操作；第四部分为总结，风哥针对本文整体内容做收束，给出落地建议与常见误区。

文中涉及的数据库实例名、数据库名统一使用 fgedudb，操作系统用户统一使用 fgedu，数据目录统一部署在 /fgedudb 路径下，实验主机分别命名为 fgedu-net-cn1、fgedu-net-cn2。所有配置参数均按 64G 内存、8 核 CPU 的规格给出。本套风哥教程以社区版 PostgreSQL 为案例讲解，文中不标注具体版本号，读者所用版本如有细微差异，以对应版本官方文档为准。

【风哥 itpux-com】

二、理论部分

2.1 什么是分区表

分区表，本质上是一种将"逻辑上一张大表"切分为"物理上多张小表"的表设计技术。一张分区父表（Partitioned Table）本身并不直接存储任何数据，它只承载表的结构定义、约束以及分区策略；真正存放数据的是它的各个子分区（Partition），每一个分区都是一张独立的、实际存在的普通表。

对应用层而言，分区表对外仍然表现为一张完整的逻辑表，用户可以像访问普通表一样对其进行查询、插入、更新和删除，不需要修改任何业务 SQL。而对数据库内核而言，数据被分散存储在多张物理子表中，这为性能优化和数据治理带来了巨大空间。

分区表带来的核心收益可以归纳为以下几类。第一，查询性能提升。当查询条件中带有分区键时，查询优化器会自动做分区裁剪（Partition Pruning），只扫描与查询条件匹配的少数几个分区，而不会扫描整张表的所有分区。对于大表而言，这种"只查一小部分"的能力往往能带来数量级的性能提升。第二，索引与缓存收益。分区把一棵原本很大的索引树拆成了多棵较小的索引，索引的上层树级被分区机制替代，高频访问的那部分索引更容易整体驻留在内存中，从而显著降低磁盘 I/O。第三，批量数据管理能力。数据的增删、归档、清理可以直接落到分区粒度上执行。比如按时间范围分区后，删除历史数据只需要 DROP 一个分区，秒级完成，而不必对亿级大表执行一条耗时极长的 DELETE。第四，冷热数据分层存储。分区可以配合不同表空间，把热分区放在高性能存储上，把冷分区放在廉价存储上，从而在性能与成本之间取得平衡。

需要特别强调的是，分区表并不是银弹。它主要适合"数据量大、且查询条件能够稳定命中分区键"的场景，例如日志类、流水类、订单类、物联网时序类数据。如果业务查询经常不带分区键做全表扫描，或者单表数据量本身很小，那么分区的收益会非常有限，反而可能因为分区数量过多带来额外的规划开销。这一点风哥在后面实战中会进一步说明。

2.2 声明式分区与继承式分区的区别

PostgreSQL 的分区能力经历了从"继承式分区"到"声明式分区"的重大演进，理解二者的区别是掌握分区表的前提。

继承式分区是早期版本中主要依靠表继承（Table Inheritance）机制手动实现的分区方案。其做法是：先创建一张父表，再分别创建多张继承自父表的子表，然后在父表上建立触发器或规则，由触发器在插入数据时根据分区键将数据路由到对应的子表。继承式分区可以正常工作，但存在明显的短板。第一，路由逻辑依赖触发器或规则，每条数据插入都要经过额外的函数调用与判断，性能开销较大；第二，约束排除（Constraint Exclusion）对复杂场景的裁剪能力有限，容易退化成顺序扫描所有子表；第三，主键、唯一约束等无法在父表上统一声明，需要逐个子表单独维护，表结构的一致性难以保证；第四，后续 ALTER 父表结构时，子表结构的同步要手动或依赖特定机制处理，运维成本高。

声明式分区（Declarative Partitioning）是 PostgreSQL 原生内置的一等公民特性，通过 CREATE TABLE 语句直接声明分区方式和分区边界，由内核自动完成数据路由、约束绑定与分区裁剪，无需任何触发器。声明式分区具有以下优势：第一，数据路由由内核实现，插入时自动根据分区键定位到正确分区，性能远优于触发器方案；第二，分区裁剪由查询优化器直接执行，裁剪逻辑健壮且高效；第三，父表上的主键、唯一约束、外键等可以统一声明，子表自动继承，结构一致性有保障；第四，分区管理（增、删、拆分、合并）都有标准的 ALTER TABLE 语法支撑，运维体验接近其他商业数据库的分区能力。

风哥教程本文强烈建议读者一律采用声明式分区。继承式分区只作为理解历史机制和排查遗留系统时的背景知识，不再推荐用于新项目。

2.3 分区表技术的发展

PostgreSQL 分区表能力的成熟不是一蹴而就的，而是经过多个大版本的持续迭代。早期版本只有继承式分区的雏形，功能简陋。随后各版本逐步补齐了声明式分区的各项能力，包括 HASH 分区、支持对分区表执行 UPDATE 时跨分区移动行、支持分区表的剪枝优化、支持分区级聚合（Partition-wise Aggregation）、支持分区级联接（Partition-wise Join）、支持分区表的逻辑复制等。到了较新的社区版本，声明式分区的功能已经非常完善，成为生产环境处理海量数据的标准手段。

技术演进带来的关键能力包括：分区裁剪从基础的约束排除演进为更精准的内核级剪枝；新增了默认分区（DEFAULT partition），解决了新数据落入未定义边界时直接报错的问题；分区表支持外键、支持被其他表引用、支持创建在父表上的全局约束与索引自动传播到子分区；复合分区（在范围分区之下再叠加列表或哈希分区）得到支持，进一步提升了分区设计的灵活性。理解这些能力边界，有助于读者在生产中合理设计分区方案，也能在排障时更快定位问题。

2.4 分区类型与分区键选择

声明式分区目前支持三种基础分区类型，以及由它们组合而成的复合分区。

范围分区（RANGE）依据分区键的取值区间来划分分区，区间采用"下界包含、上界不包含"的规则。它特别适合按时间、按数值区间组织的数据，是最常用的分区类型，也是本套风哥教程实战部分的主角。

列表分区（LIST）通过显式列举分区键的离散取值来划分分区，适合分区键取值有限且明确的场景，例如按地区、按业务类型、按状态字段分区。

HASH 分区依据分区键值对指定模数取余的结果来划分分区，能将数据相对均匀地分散到各分区，适合分区键取值不连续、分布不均匀、难以用范围或列表表达的场景，常用于提升并发写入时的数据分散度。

复合分区（Composite）是在一种分区之上再叠加另一种分区，例如先按月做范围分区，再在每个范围内按地区做列表分区，或者按哈希分布到多个分区后再按时间做范围分区。复合分区适用于分区维度多于一个、单一维度无法满足管理或性能需求的场景。

分区键的选择直接决定分区表的成败，是设计阶段最关键的决策。风哥给出以下几条选择准则。第一，分区键必须是查询中高频出现的过滤条件列，这样才能让分区裁剪真正生效，否则分区形同虚设。第二，分区键应具有足够的区分度，能稳定地把数据切分到不同分区，避免绝大多数数据涌入同一个分区造成"热点分区"。第三，分区键的选择要考虑数据治理需求，例如按时间分区天然适合定期归档和清理历史数据。第四，主键或唯一约束必须包含分区键，这是 PostgreSQL 声明式分区的硬性约束，设计时务必提前规划。第五，分区数量要适度，一般建议控制在上百个以内，避免分区过多导致规划开销上升和文件句柄压力增大。

2.5 分区裁剪与性能收益原理

分区裁剪是分区表性能收益的根本来源。当查询语句的 WHERE 条件中带上了分区键的等值或范围过滤时，查询优化器会在规划阶段就确定哪些分区一定不包含匹配数据，从而把这些分区直接从扫描计划中剔除，只保留候选分区参与执行。

从执行计划层面看，启用分区裁剪后，EXPLAIN 输出的扫描目标会从整张父表变为具体的若干子分区；而在较新的内核版本中，甚至可以对裁剪结果做更深度的分区级优化。与之配套的参数 enable_partition_pruning 控制着分区裁剪的总开关，默认开启。

分区裁剪除了作用于查询，也作用于 DELETE 与 UPDATE 语句，同样能够避免全表大范围扫描。此外，当分区表上建立了与分区键对应的分区索引（子分区各自建立索引，或在父表上声明索引自动传播到各分区）后，每个分区的索引规模显著变小，热索引更易常驻内存，进一步降低 I/O。这就是分区表在大表场景下能够稳定带来性能提升的底层原因。

需要提醒的是，分区裁剪的生效前提是查询条件对分区键使用了可直接求值的表达式。如果在 WHERE 中对分区键套用了函数（例如对日期列执行 date_trunc 后再比较），裁剪就可能失效，这一点在实战中要格外注意。

【风哥教程 113257174】

三、实战操作

3.1 环境准备与参数配置

本套风哥教程的实战环境采用两台生产规格主机，主机名分别为 fgedu-net-cn1 与 fgedu-net-cn2，每台主机硬件规格为 64G 内存、8 核 CPU。操作系统用户统一为 fgedu，数据库实例名与数据库名统一为 fgedudb，数据目录统一部署在 /fgedudb 路径下。读者在自己的实验环境中不必拘泥于两台主机，单台主机即可完成本文全部实战操作。

首先，以 fgedu 用户登录 fgedu-net-cn1，确认数据目录与实例状态：

# 以操作系统用户 fgedu 登录 fgedu-net-cn1
ssh fgedu@fgedu-net-cn1

# 查看数据目录
ls -ld /fgedudb/fgedudb

# 确认实例进程
ps -ef | grep postgres | grep -v grep

登录数据库实例，确认当前会话信息：

# 连接 fgedudb 实例
psql -h 127.0.0.1 -p 5432 -U fgedu -d fgedudb

-- 查看当前用户与数据库
SELECT current_user, current_database();

-- 查看数据库版本信息
SELECT version();

-- 确认服务器内存与 CPU 相关参数，符合 64G/8 核规格
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

针对 64G 内存、8 核 CPU 的规格，风哥给出如下一套推荐的分区表相关与内存参数配置。以下参数写入数据目录下的 postgresql.conf 文件，修改后重启或 reload 实例生效。

# 编辑参数文件，位于 /fgedudb/fgedudb/postgresql.conf
vim /fgedudb/fgedudb/postgresql.conf

在参数文件中追加或修改如下配置项：

# 内存相关：64G 内存规格推荐
shared_buffers = 16GB
work_mem = 256MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB

# 分区相关
enable_partition_pruning = on
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

# 磁盘 I/O 相关
random_page_cost = 1.1
effective_io_concurrency = 2

修改完成后，重新加载配置：

# 重新加载参数配置
/usr/local/pgsql/bin/pg_ctl -D /fgedudb/fgedudb reload

重新登录确认参数生效：

SHOW enable_partition_pruning;
SHOW enable_partitionwise_join;
SHOW enable_partitionwise_aggregate;
SHOW shared_buffers;

【风哥数据库教程 itpux-com】

3.2 范围分区表创建实战

下面创建一个按交易日期做范围分区的业务表，模拟订单流水场景。分区键为交易时间字段，按季度划分分区，并预留一个默认分区兜底。

-- 创建范围分区父表
CREATE TABLE fgedudb.t_order_log (
    order_id     bigint         NOT NULL,
    cust_id      bigint         NOT NULL,
    order_amt    numeric(14,2)  NOT NULL,
    status       varchar(20)    NOT NULL,
    order_time   timestamptz    NOT NULL,
    PRIMARY KEY (order_id, order_time)
) PARTITION BY RANGE (order_time);

注意，主键中必须包含分区键 order_time。接下来创建各个季度分区。分区边界遵循"下界包含、上界不包含"的规则：

-- 2026 年各季度分区
CREATE TABLE fgedudb.t_order_log_2026q1 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-01-01 00:00:00+08') TO ('2026-04-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_log_2026q2 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-04-01 00:00:00+08') TO ('2026-07-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_log_2026q3 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-07-01 00:00:00+08') TO ('2026-10-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_log_2026q4 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-10-01 00:00:00+08') TO ('2027-01-01 00:00:00+08');

-- 创建默认分区，承接未定义边界的未来数据
CREATE TABLE fgedudb.t_order_log_default PARTITION OF fgedudb.t_order_log DEFAULT;

数据路由由内核自动完成。插入测试数据验证：

-- 插入 2026 年第一季度与第二季度的数据
INSERT INTO fgedudb.t_order_log (order_id, cust_id, order_amt, status, order_time) VALUES
(1001, 201, 1000.50, 'SUCCESS', '2026-02-15 10:30:00+08'),
(1002, 202, 200.00, 'PENDING', '2026-05-20 14:00:00+08');

-- 查看数据落在哪个分区
SELECT tableoid::regclass AS part_table, count(*)
FROM fgedudb.t_order_log
GROUP BY tableoid::regclass
ORDER BY part_table;

验证结果应显示第一条数据落在 2026q1 分区，第二条落在 2026q2 分区，说明范围分区路由正确。

【网上搜索风哥教程可以学习全套数据库教程】

3.3 HASH 分区表创建实战

对于分区键取值不均匀、难以用范围或列表表达的场景，适合使用 HASH 分区。下面创建一张按客户号做 HASH 分区的用户画像表，按模数 4 划分成 4 个分区。

-- 创建 HASH 分区父表
CREATE TABLE fgedudb.t_user_profile (
    user_id    bigint       NOT NULL,
    user_name  varchar(64)  NOT NULL,
    region     varchar(32)  NOT NULL,
    last_login timestamptz  NOT NULL,
    PRIMARY KEY (user_id)
) PARTITION BY HASH (user_id);

-- 按模数 4、余数 0~3 创建 4 个分区
CREATE TABLE fgedudb.t_user_profile_p0 PARTITION OF fgedudb.t_user_profile
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE fgedudb.t_user_profile_p1 PARTITION OF fgedudb.t_user_profile
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE fgedudb.t_user_profile_p2 PARTITION OF fgedudb.t_user_profile
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE fgedudb.t_user_profile_p3 PARTITION OF fgedudb.t_user_profile
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

批量插入数据验证哈希分布的均衡性：

-- 批量插入测试数据
INSERT INTO fgedudb.t_user_profile (user_id, user_name, region, last_login)
SELECT g, 'user_' || g, 'REGION_' || (g % 10),
       now() - interval '1 day' * (g % 30)
FROM generate_series(1, 10000) AS g;

-- 查看各分区数据分布
SELECT tableoid::regclass AS part_table, count(*)
FROM fgedudb.t_user_profile
GROUP BY tableoid::regclass
ORDER BY part_table;

风哥提示：HASH 分区各分区的数据量通常比较均衡，这是因为 user_id 对 4 取余的结果在 0 到 3 之间近似均匀分布。如果实际业务出现热点，应检查分区键本身的取值分布，而不是盲目增加分区数量。

3.4 列表分区表创建实战

列表分区适合分区键取值为明确离散集合的场景。下面创建一张按地区做列表分区的订单明细表。

-- 创建列表分区父表
CREATE TABLE fgedudb.t_order_detail (
    order_id   bigint       NOT NULL,
    region     varchar(16)  NOT NULL,
    amount     numeric(14,2) NOT NULL,
    detail_time timestamptz NOT NULL,
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

-- 创建各地区分区
CREATE TABLE fgedudb.t_order_detail_sc PARTITION OF fgedudb.t_order_detail
    FOR VALUES IN ('SC', 'CQ');   -- 四川、重庆
CREATE TABLE fgedudb.t_order_detail_gd PARTITION OF fgedudb.t_order_detail
    FOR VALUES IN ('GD', 'GX');   -- 广东、广西
CREATE TABLE fgedudb.t_order_detail_other PARTITION OF fgedudb.t_order_detail
    DEFAULT;                       -- 兜底分区

插入数据验证路由：

INSERT INTO fgedudb.t_order_detail (order_id, region, amount, detail_time) VALUES
(2001, 'SC', 500.00, '2026-03-01 09:00:00+08'),
(2002, 'GD', 800.00, '2026-03-02 10:00:00+08'),
(2003, 'BJ', 300.00, '2026-03-03 11:00:00+08');

SELECT tableoid::regclass AS part_table, region
FROM fgedudb.t_order_detail
ORDER BY order_id;

可以看到 ‘SC’ 落入 SC 分区，‘GD’ 落入 GD 分区，而未被枚举的 ‘BJ’ 自动落入兜底分区 other。列表分区配合 DEFAULT 分区，可以避免新取值导致插入失败。

【上51CTO搜索风哥可以学习全套数据库教程】

3.5 复合分区表创建实战

当单一分区维度无法同时满足数据治理与查询裁剪需求时，可以使用复合分区。下面创建一张先按时间做范围分区、再在每个范围内按地区做列表分区的复合分区表。

-- 创建复合分区父表：范围分区 + 列表分区
CREATE TABLE fgedudb.t_biz_event (
    event_id   bigint       NOT NULL,
    region     varchar(16)  NOT NULL,
    event_type varchar(20)  NOT NULL,
    event_time timestamptz  NOT NULL,
    PRIMARY KEY (event_id, event_time, region)
) PARTITION BY RANGE (event_time);

-- 先创建按月的子分区（这些子分区自身也是分区父表）
CREATE TABLE fgedudb.t_biz_event_202601 PARTITION OF fgedudb.t_biz_event
    FOR VALUES FROM ('2026-01-01 00:00:00+08') TO ('2026-02-01 00:00:00+08')
    PARTITION BY LIST (region);

CREATE TABLE fgedudb.t_biz_event_202602 PARTITION OF fgedudb.t_biz_event
    FOR VALUES FROM ('2026-02-01 00:00:00+08') TO ('2026-03-01 00:00:00+08')
    PARTITION BY LIST (region);

在每个时间子分区下再创建地区列表分区：

-- 2026 年 1 月内的地区分区
CREATE TABLE fgedudb.t_biz_event_202601_sc PARTITION OF fgedudb.t_biz_event_202601
    FOR VALUES IN ('SC');
CREATE TABLE fgedudb.t_biz_event_202601_other PARTITION OF fgedudb.t_biz_event_202601
    DEFAULT;

-- 2026 年 2 月内的地区分区
CREATE TABLE fgedudb.t_biz_event_202602_sc PARTITION OF fgedudb.t_biz_event_202602
    FOR VALUES IN ('SC');
CREATE TABLE fgedudb.t_biz_event_202602_other PARTITION OF fgedudb.t_biz_event_202602
    DEFAULT;

插入并验证复合路由：

INSERT INTO fgedudb.t_biz_event (event_id, region, event_type, event_time) VALUES
(3001, 'SC', 'LOGIN', '2026-01-15 12:00:00+08'),
(3002, 'BJ', 'PAY',   '2026-02-10 13:00:00+08');

SELECT tableoid::regclass AS part_table, event_time, region
FROM fgedudb.t_biz_event
ORDER BY event_id;

复合分区使得"按时间归档 + 按地区查询"两类需求都能被分区裁剪高效支撑，是海量多维度数据的常用设计。

3.6 查看分区表

实战中经常需要查看分区表的结构、层级与数据分布。PostgreSQL 提供了多种查看手段。

使用 pg_partition_tree 函数查看分区的完整层级结构：

-- 查看复合分区表的树形结构
SELECT * FROM pg_partition_tree('fgedudb.t_biz_event');

使用 \d 元命令查看分区表的定义：

\d+ fgedudb.t_order_log

查询系统目录 pg_inherits，查看继承关系：

SELECT inhrelid::regclass AS child,
       inhparent::regclass AS parent
FROM pg_catalog.pg_inherits
WHERE inhparent = 'fgedudb.t_order_log'::regclass
ORDER BY child;

使用 information_schema 查询分区信息：

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'fgedudb'
  AND table_name LIKE 't_order_log%'
ORDER BY table_name;

统计各分区的行数与大小，用于日常监控：

SELECT tableoid::regclass AS part_table,
       count(*)            AS rows,
       pg_size_pretty(pg_total_relation_size(tableoid::regclass)) AS size
FROM fgedudb.t_order_log
GROUP BY tableoid::regclass
ORDER BY part_table;

3.7 分区表数据更新

分区表支持 INSERT、UPDATE、DELETE 操作。需要特别注意 UPDATE 跨分区移动行的场景：当更新操作修改了分区键的值，使行所属分区发生变化时，PostgreSQL 会自动把该行从原分区删除并插入到目标分区，这一行为由内核透明处理。

-- 正常更新，不改变分区键
UPDATE fgedudb.t_order_log
SET status = 'SUCCESS'
WHERE order_id = 1002;

-- 更新分区键，触发跨分区移动
-- 将订单 1001 的时间从第一季度改到第三季度
UPDATE fgedudb.t_order_log
SET order_time = '2026-08-15 10:30:00+08'
WHERE order_id = 1001;

-- 验证跨分区移动是否生效
SELECT tableoid::regclass AS part_table, order_id, order_time
FROM fgedudb.t_order_log
WHERE order_id = 1001;

风哥提示：跨分区 UPDATE 会先删除旧分区的行，再插入新分区的行，属于较高成本的操作。在批量更新分区键的场景中，务必评估其对性能与锁的影响，尽量将这类操作收敛到业务低谷期执行。

3.8 分区表增加分区

随着时间推移，需要不断为范围分区表增加新分区。语法与创建初始分区一致：

-- 为 2027 年第一季度增加分区
CREATE TABLE fgedudb.t_order_log_2027q1 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2027-01-01 00:00:00+08') TO ('2027-04-01 00:00:00+08');

若数据日期尚未跨越原默认分区的边界，新分区会正常承接对应区间的数据。这里再演示如何检查某条数据实际落入哪个分区：

INSERT INTO fgedudb.t_order_log (order_id, cust_id, order_amt, status, order_time) VALUES
(2001, 301, 1500.00, 'SUCCESS', '2027-02-01 09:00:00+08');

SELECT tableoid::regclass AS part_table, order_id, order_time
FROM fgedudb.t_order_log
WHERE order_id = 2001;

3.9 分区表删除分区

删除分区是分区表数据清理的核心优势。删除某个分区会连同其中全部数据一起移除，速度远快于对子表执行 DELETE。以按季度范围分区为例：

-- 删除 2026 年第一季度分区及其全部数据
DROP TABLE fgedudb.t_order_log_2026q1;

-- 确认分区已移除
SELECT * FROM pg_partition_tree('fgedudb.t_order_log');

如需保留表结构但清空数据，可以使用 TRUNCATE：

TRUNCATE TABLE fgedudb.t_order_log_2026q2;

在生产环境中，删除历史分区通常与数据保留策略绑定，配合定时任务即可实现按周或按月自动归档清理历史数据，这是分区表运维中收益最明显的场景之一。

【风哥 itpux-com】

3.10 分区表与普通表转换

某些场景下需要把已有普通表改造为分区表，或把分区表还原为普通表。由于 PostgreSQL 不支持直接把带数据的普通表原地转换成分区表，通常需要走"建新分区表 + 数据迁移"的路径。

从普通表转换到分区表的通用步骤：

-- 第一步：创建结构相同的分区父表
CREATE TABLE fgedudb.t_order_new (
    order_id   bigint        NOT NULL,
    cust_id    bigint        NOT NULL,
    order_amt  numeric(14,2) NOT NULL,
    status     varchar(20)   NOT NULL,
    order_time timestamptz   NOT NULL,
    PRIMARY KEY (order_id, order_time)
) PARTITION BY RANGE (order_time);

-- 第二步：创建各分区
CREATE TABLE fgedudb.t_order_new_2026q1 PARTITION OF fgedudb.t_order_new
    FOR VALUES FROM ('2026-01-01 00:00:00+08') TO ('2026-04-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_new_2026q2 PARTITION OF fgedudb.t_order_new
    FOR VALUES FROM ('2026-04-01 00:00:00+08') TO ('2026-07-01 00:00:00+08');

-- 第三步：从普通表迁移数据
INSERT INTO fgedudb.t_order_new (order_id, cust_id, order_amt, status, order_time)
SELECT order_id, cust_id, order_amt, status, order_time
FROM fgedudb.t_order_log_old;

-- 第四步：核对数据量后，重命名并切换应用指向
SELECT count(*) FROM fgedudb.t_order_log_old;
SELECT count(*) FROM fgedudb.t_order_new;

反过来，如果希望把分区表合并为一张普通表，同样不能原地转换，需要新建普通表并聚合各分区数据：

-- 创建普通目标表
CREATE TABLE fgedudb.t_order_flat (LIKE fgedudb.t_order_log INCLUDING ALL);

-- 将各分区数据聚合插入普通表
INSERT INTO fgedudb.t_order_flat
SELECT * FROM fgedudb.t_order_log;

-- 校验数据量一致后，删除原分区表并重命名
DROP TABLE fgedudb.t_order_log;
ALTER TABLE fgedudb.t_order_flat RENAME TO t_order_log;

风哥提示：无论是普通表转分区表还是分区表转普通表，都涉及数据全量迁移，务必在业务低峰期操作，并在切换前完成数据量一致性核对与备份。

3.11 分区表拆分分区

当某个分区数据量过大，希望将其细分为多个更小分区时，需要执行分区拆分。拆分的大致思路是：先新建两个目标分区，再从原分区按条件把数据分别迁移过去，最后删除原分区。

以把一个季度分区 t_order_log_2026q2 拆分为月度分区为例：

-- 第一步：在父表下新建两个月度分区
CREATE TABLE fgedudb.t_order_log_202604 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-04-01 00:00:00+08') TO ('2026-05-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_log_202605 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-05-01 00:00:00+08') TO ('2026-06-01 00:00:00+08');
CREATE TABLE fgedudb.t_order_log_202606 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-06-01 00:00:00+08') TO ('2026-07-01 00:00:00+08');

-- 第二步：将原分区中的数据迁移到新分区
INSERT INTO fgedudb.t_order_log
SELECT * FROM fgedudb.t_order_log_2026q2;

-- 第三步：删除原季度分区
DROP TABLE fgedudb.t_order_log_2026q2;

-- 第四步：确认拆分结果
SELECT * FROM pg_partition_tree('fgedudb.t_order_log');

需要说明的是，拆分操作期间对原分区的并发写入需要妥善处理，生产环境通常采用"低峰期停机或短时锁表"的方式，或结合分批迁移降低锁持有时间。

3.12 分区表合并分区

与拆分相反，当相邻分区过小、希望减少分区数量时，可以合并多个分区。思路是把多个源分区的数据聚合到一个新分区，再删除源分区。同样以合并月度分区回季度分区为例：

-- 第一步：新建一个季度分区
CREATE TABLE fgedudb.t_order_log_2026q3 PARTITION OF fgedudb.t_order_log
    FOR VALUES FROM ('2026-07-01 00:00:00+08') TO ('2026-10-01 00:00:00+08');

-- 第二步：将三个月度分区数据合并迁入
INSERT INTO fgedudb.t_order_log_2026q3
SELECT * FROM fgedudb.t_order_log_202607
UNION ALL
SELECT * FROM fgedudb.t_order_log_202608
UNION ALL
SELECT * FROM fgedudb.t_order_log_202609;

-- 第三步：删除被合并的月度分区
DROP TABLE fgedudb.t_order_log_202607;
DROP TABLE fgedudb.t_order_log_202608;
DROP TABLE fgedudb.t_order_log_202609;

-- 第四步：核对合并后数据量一致
SELECT count(*) FROM fgedudb.t_order_log_2026q3;

风哥针对合并分区再次提醒：合并与拆分都涉及数据搬迁，属于重量级维护操作，务必做好备份与数据量核对，避免迁移过程中丢数据。

【风哥教程 113257174】

3.13 分区表相关参数

分区表的性能表现受多个内核参数影响，本套风哥教程结合 64G 内存、8 核 CPU 规格给出配置建议。

enable_partition_pruning 控制分区裁剪开关，默认开启，通常无需关闭，仅在排查裁剪失效导致的异常时临时关闭验证：

SHOW enable_partition_pruning;
-- 临时关闭以验证裁剪对计划的影响
SET enable_partition_pruning = off;
SET enable_partition_pruning = on;

enable_partitionwise_join 控制分区级联接。当两个分区表按相同分区键做 JOIN 时，开启该参数可以让优化器对对应分区两两联接，显著降低内存与 I/O 开销，适合多分区大表联接场景：

SHOW enable_partitionwise_join;

enable_partitionwise_aggregate 控制分区级聚合。开启后，聚合操作可以先在单个分区内完成，再汇总结果，减少数据传输与排序开销：

SHOW enable_partitionwise_aggregate;

与分区裁剪直接相关的还有约束排除。虽然声明式分区主要依赖内核级裁剪，但理解约束排除仍有助于排查复杂场景：

SHOW constraint_exclusion;

此外，维持分区数量的合理性对规划器开销很重要。分区数过多时，即使不扫描，规划器也需要为每个分区生成计划信息。风哥建议将活跃分区控制在数百个以内，超过后应评估是否需要引入分区维度更粗的设计或使用外部定时工具（如 pg_partman）来管理分区生命周期。

3.14 分区表性能对比实战

最后，风哥通过一次可复现的对比实验，验证分区表的真实性能收益。实验在 fgedu-net-cn1 上进行，规格 64G 内存、8 核 CPU，使用 EXPLAIN ANALYZE 对比分区表与普通表的查询开销。

先准备一张等量数据的普通表作为对照：

-- 将分区表数据复制为普通表
CREATE TABLE fgedudb.t_order_plain AS
SELECT * FROM fgedudb.t_order_log;

-- 普通表与分区表数据量一致
SELECT count(*) FROM fgedudb.t_order_log;
SELECT count(*) FROM fgedudb.t_order_plain;

对分区表执行带分区键的过滤查询，观察是否发生分区裁剪：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM fgedudb.t_order_log
WHERE order_time >= '2026-04-01 00:00:00+08'
  AND order_time <  '2026-07-01 00:00:00+08';

执行计划中应只出现被裁剪后保留的候选分区，而不会出现父表全表扫描。再对比普通表的同类查询：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM fgedudb.t_order_plain
WHERE order_time >= '2026-04-01 00:00:00+08'
  AND order_time <  '2026-07-01 00:00:00+08';

风哥提示：普通表即使建了索引，其 B 树索引规模远大于单分区索引，热数据难以常驻内存，在高并发查询下 I/O 成本更高；而分区表借助分区裁剪与更小的分区索引，往往能在执行时间与缓冲命中上取得明显优势。读者可自行在千万级数据规模下复现这一对比，直观感受分区带来的收益。

为让分区裁剪在更多场景生效，风哥建议在父表上统一声明索引，内核会自动传播到各分区：

-- 在父表上创建索引，自动传播到所有子分区
CREATE INDEX idx_order_log_cust ON fgedudb.t_order_log (cust_id);

-- 查看各分区是否都建立了对应索引
SELECT child.relname AS part_table, indexname
FROM pg_indexes idx
JOIN pg_class child ON child.oid = (idx.tablename::regclass)::oid
WHERE idx.tablename LIKE 't_order_log_%'
ORDER BY part_table;

【风哥数据库教程 itpux-com】

四、风哥针对本文总结

本套风哥教程围绕 PostgreSQL 分区表这一性能优化核心手段，从理论到实战做了完整梳理。在理论层面，风哥教程本文讲清楚了什么是分区表、声明式分区与继承式分区的本质区别、分区表技术的发展演进，以及分区类型选择、分区键选取、分区裁剪与性能收益的底层原理。在实战层面，风哥教程本文在 64G 内存、8 核 CPU 的 fgedu-net-cn1、fgedu-net-cn2 主机上，完整演示了范围分区、HASH 分区、列表分区、复合分区的创建，以及分区表的查看、数据更新、增加分区、删除分区、与普通表转换、拆分分区、合并分区、参数调优和性能对比等全部运维操作，所有数据目录统一部署在 /fgedudb 路径，实例与用户统一使用 fgedudb、fgedu。

风哥针对本文总结几条核心要点，供读者落地参考。第一，分区表适合"数据量大且查询稳定命中分区键"的场景，切勿盲目对全表分区。第二，分区键必须包含在主键或唯一约束中，这是声明式分区的硬性约束。第三，务必使用声明式分区而非继承式分区，由内核自动完成路由与裁剪。第四，按时间范围分区 + 定期增删分区，是日志、流水类数据的标准治理方案，能带来查询性能与管理效率的双重提升。第五，删除历史数据优先使用 DROP TABLE 或 TRUNCATE，避免对亿级大表执行慢速 DELETE。第六，拆分与合并分区属于重量级维护操作，务必备份并核对数据量。第七，合理控制分区数量，善用 enable_partition_pruning、enable_partitionwise_join、enable_partitionwise_aggregate 等参数，让分区裁剪真正发挥威力。

最后，风哥提醒：分区表是性能优化工具箱中的利器，但它需要与索引设计、内存参数、SQL 写法协同配合。读者在掌握本文操作的基础上，还应结合执行计划分析，持续验证分区裁剪是否生效，从而把分区表的性能优势落到实处。【网上搜索风哥教程可以学习全套数据库教程】

（全文完。更多数据库实战技能，上51CTO搜索风哥可以学习全套数据库教程。）