我要评论一、什么是表分区？它和分库分表有什么区别？
1.1 什么是表分区（table partitioning）？
表分区是数据库的一种物理设计技术，它将一个大表从逻辑上视为一个整体，但从物理上拆分成多个子表（分区），每个分区存储一部分数据。
- 逻辑上：你仍然像操作一张表一样查询它
- 物理上：数据分散在多个子表中，按规则存储
- ✅ 举个例子：
把jgpt_jzd_test按年份拆成p2023、p2024、p2025三个分区，查询时仍用select * from jgpt_jzd_test，但数据库只扫描相关分区。
1.2 表分区 vs 分库分表：关键区别
| 对比项 | 表分区 | 分库分表 |
|---|---|---|
| 实现层级 | 数据库内部（单库） | 应用层或中间件（跨库） |
| 透明性 | 高（应用无感知） | 低（需改代码） |
| 管理复杂度 | 低（自动路由） | 高（需路由规则） |
| 事务支持 | 完整支持 | 跨库事务复杂 |
| 适用场景 | 单表过大（百万~亿级） | 数据量极大（tb级+） |
| 技术栈 | postgresql、mysql 8.0+ | shardingsphere、mycat |
✅ 简单说：
- 表分区是“数据库帮你拆”
- 分库分表是“你自己写代码拆”
二、表分区的优缺点与使用场景
优点
| 优势 | 说明 |
|---|---|
| 查询性能提升 | 分区剪枝（partition pruning）自动跳过无关分区 |
| 数据管理高效 | 删除旧数据从 delete 变为 drop partition（秒级） |
| 维护更方便 | 可对单个分区做 vacuum、analyze、备份 |
| i/o 分散 | 不同分区可分布到不同磁盘（高级用法） |
缺点
| 缺点 | 说明 |
|---|---|
| 全表扫描变慢 | 需扫描所有分区，元数据开销增加 |
| 分区键固定 | 一旦选定（如 gmt_create），不能更改 |
| 管理复杂度上升 | 需定期创建新分区 |
| 不支持主键跨分区 | 主键必须包含分区键 |
三、postgresql 表分区的三种方式
postgresql 支持三种分区策略：
1.range 分区（按范围）
- 适用：时间、数值范围
- 示例：按
gmt_create按年/月分区 - 语法：
partition by range (gmt_create)
2.list 分区（按枚举值）
- 适用：固定分类，如省份、状态
- 示例：按
province分区 - 语法：
partition by list (province)
3.hash 分区（按哈希值）
- 适用：数据均匀分布，无明显查询模式
- 示例：按
id哈希分 4 份 - 语法：
partition by hash (id)
四、实战：jgpt_jzd_test表分区操作全流程
将 3000 万+ 的 jgpt_jzd_test 表改造为按年分区的分区表。
步骤 1：创建分区主表
-- 创建主表（逻辑表，不存数据）
create table jgpt_jzd_test_partitioned (
id varchar(32),
jzdbh varchar(255),
xzb varchar(255),
yzb varchar(255),
htxxid varchar(255),
gmt_create timestamp(6) not null, -- 必须 not null
gmt_modified timestamp(6),
del_flag varchar,
created_user_id varchar(255),
created_user varchar(255),
last_modified_user_id varchar(255),
last_modified_user varchar(255),
dkh varchar(255),
dkms varchar(255),
batchnum varchar(255)
) partition by range (gmt_create);
步骤 2：创建子分区（按年）
-- 2023 年分区
create table jgpt_jzd_test_p2023
partition of jgpt_jzd_test_partitioned
for values from ('2023-01-01') to ('2024-01-01');
-- 2024 年分区
create table jgpt_jzd_test_p2024
partition of jgpt_jzd_test_partitioned
for values from ('2024-01-01') to ('2025-01-01');
-- 2025 年分区
create table jgpt_jzd_test_p2025
partition of jgpt_jzd_test_partitioned
for values from ('2025-01-01') to ('2026-01-01');步骤 3：迁移数据
-- 从旧表插入到新分区表（自动路由） insert into jgpt_jzd_test_partitioned select * from jgpt_jzd_test;
步骤 4：创建索引
-- 在主表创建索引，所有分区自动继承 create index idx_jgpt_jzd_test_htxxid on jgpt_jzd_test_partitioned (htxxid);
步骤 5：切换表名（原子操作）
-- 1. 备份原表 alter table jgpt_jzd_test rename to jgpt_jzd_test_backup; -- 2. 新表启用原名 alter table jgpt_jzd_test_partitioned rename to jgpt_jzd_test;
✅ 至此，jgpt_jzd_test 已是分区表！
五、验证表分区是否成功
1. 查看分区结构
-- 查询系统表
select
inhrelid::regclass as child_table,
inhparent::regclass as parent_table
from pg_inherits
where inhparent = 'jgpt_jzd_test'::regclass;
2. 验证分区剪枝是否生效
explain select count(*) from jgpt_jzd_test where gmt_create >= '2025-01-01' and gmt_create < '2026-01-01';
可以看到只查询了jgpt_jzd_test_p2025一张分区表
六、安全删除分区：detachvsdrop（关键区别）
在表分区的日常维护中，删除历史数据是一个高频操作。postgresql 提供了两种方式来“移除”分区，但它们的安全性、可逆性和使用场景完全不同。
我们以 jgpt_jzd_test_p2024 分区为例，对比两种操作：
1.detach partition—— 安全的“解绑”操作
alter table jgpt_jzd_test_partitioned
detach partition jgpt_jzd_test_p2024;操作特点：
- 数据不会丢失！
jgpt_jzd_test_p2024表变成一个独立的普通表- 主表
jgpt_jzd_test_partitioned不再包含该分区的数据- 可随时对
jgpt_jzd_test_p2024进行查询、导出、备份或重新挂载
适用场景：
- 需要归档数据
- 删除前做审计或备份
- 不确定是否永久删除
-- 确认无误后，再删除 drop table jgpt_jzd_test_p2024;
✅ 推荐做法：先 detach，再 drop，避免误删。
2.drop partition—— 永久删除
alter table jgpt_jzd_test_partitioned
drop partition jgpt_jzd_test_p2024;操作特点：
- 数据立即永久丢失！无法通过
drop回滚- 相当于执行了
drop table，文件被物理删除- 无法恢复（除非有数据库备份）
适用场景：
- 确认数据不再需要
- 紧急释放磁盘空间
- 自动化脚本中已确认安全
对比总结
| 操作 | 数据是否保留 | 是否可逆 | 安全性 | 推荐使用场景 |
|---|---|---|---|---|
detach partition | ✅ 保留 | ✅ 可逆 | 高 | 所有删除操作的首选 |
drop partition | ❌ 丢失 | ❌ 不可逆 | 低 | 确认永久删除 |
七、自己的理解
1.表分区之后,你在datagrip或navicat里面看到的还是一张表，数据也都在这张表里，但实际上这张表是主表，没有存储数据。
2.数据实际存储在分区表里，例如jgpt_jzd_test_p2024，因此代码里面是可以直接调用这张表的
3.平时基本不用管分区表，正常使用主表就行了，比如你插入数据，直接往jgpt_jzd_test插入，数据库会根据你的gmt字段自动插入到相应的分区表里面，平时使用基本是无感的
总结：我的认知升级
| 旧认知 | 新认知 |
|---|---|
| 分区就是“拆表” | 分区是“逻辑统一，物理分离” |
| 数据存在主表 | 主表是“空壳”，数据在分区 |
| 只能查主表 | 可直查分区，性能更优 |
| 分区很复杂 | 日常使用完全无感 |
到此这篇关于postgresql表分区的三种方式和操作方法的文章就介绍到这了,更多相关postgresql表分区内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论