PostgreSQL分区表学习记录

一、分区表类型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); -- 创建具体分区 -- 分区12026-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); -- 分区22026-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-01AND 2026-04-30; Seq Scan on operation_log_202604 operation_log (cost0.00..16.15 rows2 width170) (actual time0.021..0.024 rows2 loops1) 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文本regclassPostgreSQL特殊类型可将表名字符串自动转换为表的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);