一、分区表类型

1. 范围分区

按指定范围拆分数据，常用拆分方式如下：

• 按时间：按天、按月、按年分区（适用于日志、订单等时间相关数据）

• 按数值：按用户ID范围、订单金额范围等数值区间拆分

范围分区核心概念：

• 父表：统一定义表结构，指定分区键，不存储实际数据

• 分区键：用于拆分数据的字段（如create_time），需设置为NOT NULL

• 分区裁剪：查询时where条件包含分区键，PostgreSQL会自动过滤无关分区，提升查询性能

2. 列表分区

按固定枚举值拆分数据，适用场景：

• 按地区（如省份、城市）

• 按业务线（如电商、金融、教育）

• 按状态（如订单状态、用户状态）

3. 哈希分区

按字段的哈希值均匀打散数据，适用场景：

• 高并发写入场景

• 无明显范围或枚举规则的数据

• 需要均匀拆分数据压力的场景

二、分区表测试

1. 建表（范围分区，按月份拆分）

-- 创建分区父表，指定 RANGE 范围分区，分区键 create_time 
CREATE TABLE operation_log ( 
id BIGSERIAL, user_id INT,
operate_type VARCHAR(50), 
content TEXT, 
create_time TIMESTAMP NOT NULL ) 
PARTITION BY RANGE (create_time); 
-- 创建具体分区 
-- 分区1：2026-04 数据 
CREATE TABLE operation_log_202604 
PARTITION OF operation_log 
FOR VALUES FROM ('2026-04-01 00:00:00') TO ('2026-05-01 00:00:00'); 
-- 分区2：2026-05 数据 
CREATE TABLE operation_log_202605 
PARTITION OF operation_log 
FOR VALUES FROM ('2026-05-01 00:00:00') TO ('2026-06-01 00:00:00'); 
-- 创建默认分区（兜底存储不在规则内的数据） 
CREATE TABLE operation_log_default PARTITION OF operation_log DEFAULT;

2. 插入测试数据

-- 写入 4月数据 → 自动进入 operation_log_202604
INSERT INTO operation_log (user_id,operate_type,content,create_time)
VALUES
(1001,'login','用户登录','2026-04-10 08:20:00'),
(1002,'logout','用户退出','2026-04-20 14:30:00');

-- 写入 5月数据 → 自动进入 operation_log_202605
INSERT INTO operation_log (user_id,operate_type,content,create_time)
VALUES
(1003,'add','新增数据','2026-05-05 09:10:00'),
(1001,'edit','编辑内容','2026-05-15 16:40:00');

-- 写入异常时间 → 进入 default 默认分区
INSERT INTO operation_log (user_id,operate_type,content,create_time)
VALUES (9999,'test','测试兜底数据','2026-07-01 10:00:00');

3. 验证分区归属

-- 查询父表（查看所有分区数据，业务层面统一操作父表） 
SELECT * FROM operation_log; 
-- 查询单个分区，验证数据隔离 
-- 只查4月分区 
SELECT * FROM operation_log_202604; 
-- 只查5月分区 
SELECT * FROM operation_log_202605; 
-- 查看默认分区 
SELECT * FROM operation_log_default;

4. 验证分区裁剪

执行带分区键的条件查询，观察执行计划，确认只扫描目标分区：

fenqu=# EXPLAIN ANALYZE SELECT * FROM operation_log WHERE create_time BETWEEN '2026-04-01'AND '2026-04-30'; 
Seq Scan on operation_log_202604 operation_log (cost=0.00..16.15 rows=2 width=170) (actual time=0.021..0.024 rows=2 loops=1) 
Filter: ((create_time >= '2026-04-01 00:00:00'::timestamp without time zone) AND (create_time <= '2026-04-30 00:00:00'::timestamp without time zone)) 
Planning Time: 0.207 ms 
Execution Time: 0.047 ms

执行结果说明：仅扫描operation_log_202604分区，不扫描5月分区和默认分区，即分区裁剪生效。

三、核心系统表

1. pg_inherits

存储PostgreSQL中继承关系和分区父子关系，关键字段说明：

• inhrelid：子分区的OID（唯一标识）

• inhparent：父表的OID

查询示例：

fenqu=# SELECT * FROM pg_inherits;
 inhrelid | inhparent | inhseqno | inhdetachpending 
----------+-----------+----------+------------------
    16400 |     16396 |        1 | f
    16406 |     16396 |        1 | f
    16412 |     16396 |        1 | f
(3 rows)

2. pg_class

存储PostgreSQL中所有对象（普通表、分区表、索引、序列、视图等），关键字段说明：

• relpartbound：分区表专属字段，存放分区边界的内部原始表达式（二进制压缩编码，无法直接查看）

四、日常运维命令

1. 查询最新分区

SELECT    
c.relname AS 最新分区名,    
pg_get_expr(c.relpartbound, c.oid) AS 分区范围 
FROM pg_inherits i 
JOIN pg_class c ON c.oid = i.inhrelid 
WHERE i.inhparent = 'operation_log'::regclass 
ORDER BY pg_get_expr(c.relpartbound, c.oid) DESC LIMIT 1;

相关说明：

• pg_get_expr(表达式字段, 所属对象oid)：将relpartbound的内部编码转换为人类可读的SQL文本

• regclass：PostgreSQL特殊类型，可将表名字符串自动转换为表的OID，无需手动查询，避免大小写、schema错误

2. 新增下个月分区

CREATE TABLE operation_log_202606
PARTITION OF operation_log
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

3. 删除历史分区（快速清理，无数据碎片）

DROP TABLE operation_log_202604;

数据碎片：是指数据被删除，更新后，原本存储该数据的磁盘空间未被彻底释放，形成的零散，无法被有效利用的空闲空间

delete删除会留下碎片，删除后的数据空间无法直接被新数据完整占用， 零散分布在磁盘中

drop删除会直接删除整个物理文件，相当于彻底清理该分区的所有数据和占用空间，不会留下零散空闲空间

碎片影响：占用磁盘空间、降低查询和写入性能（数据库需跳过碎片空间查找有效数据）

4. 分离分区与重新加入分区

-- 分离分区（分区变为普通表，脱离父表管理，数据保留）
ALTER TABLE operation_log DETACH PARTITION operation_log_202604;

-- 重新加入分区（普通表重新挂载到父表，需符合分区范围规则）
ALTER TABLE operation_log ATTACH PARTITION operation_log_202604
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

分离分区的原因：

• 安全删除历史数据：先分离，确认数据无误后再删除，避免误删

• 归档历史数据：分离后可单独备份、迁移，存储到廉价存储介质

• 不影响业务：detach为轻量级操作，几乎不锁父表，不中断业务查询

五、分区表索引与主键

1. 索引创建

在父表创建索引，PostgreSQL会自动为所有子分区创建索引，后续新建分区会自动继承该索引：

CREATE INDEX idx_operation_log_create_time ON operation_log(create_time);

2. 主键创建

分区表主键必须包含分区键（确保主键唯一性，避免跨分区主键冲突）：

ALTER TABLE operation_log ADD PRIMARY KEY (id, create_time);