发现公司业务在时序类、流水类业务场景越来越多，对数据治理的需求越来越大，这里整理介绍写 PostgreSQL 分区表的应用方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
一、分区表的作用文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1. 将数据按指定的方法打算到子分区，提高 SQL 性能。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2. 解决时序类、流水类业务大表在进行老旧数据清理时 delete 引起的性能及磁盘空间碎片问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
3. 利用子分区卸载、重新挂载功能，对数据进行暂时性的隐藏、维护。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
4. 数据归档治理业务场景：定期增加子分区、定期删除不需要的子分区来对数据进行滑窗处理，保持业务系统瘦身。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
5. 添加子分区对业务透明，业务逻辑上只需要访问父表即可。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
二、业务场景举例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
以大型电商平台为例，通常订单类的信息都比较庞大，假设订单表 tab_orders 的数据量是 100G，单表 10 亿数据量，业务需要统计某个区域内订单的平均额度，往往会消耗比较漫长的时间：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
select avg(total_amount) from tab_orders where state_code=1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
如果我们能够把大表分拆成小表，查询数据的时候，只扫描数据所属的小表，就能大大降低扫描时间，提高查询速度。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
如果采用分布式架构，比如分 10 个分片，那么单个分片依旧有 1 亿条数据，对于常规数据库来说，依然容易出现严重性能问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
此时，我们可以在分布式架构的基础上，对业务大表再进行分区，那么单个分片的数据就会进一步被打散。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
PostgreSQL 的分区表可以用来解决此类问题，适用于集中式和分布式架构。解决方式是：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
创建一个表 tab_orders，作为分区表的父表，再创建 50 个子分区:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
tab_orders_1, tab_orders_2, …, tab_orders_50，
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
这样每一个分区对应一个城市的数据，分区的数据量平均是 2G，如果是分布式架构，那么单个分片内，单个子分区就是 0.1G，200w 的数据量，如果单表是百亿数据量，如果还嫌子分区数据量太大，我们可以继续进行二级、三级、四级..... 多级分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
注：pg 分区表分区方法和分区层级不限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
在本例中，这 50 分区联合在一起，组成分区父表 tab_orders。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
这里的分区父表和子分区表都是实实在在的表，和传统的分库分表不一样，分区表可以保持原普通表的查询语句保持不变，对业务透明，如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
select avg(total_amount) from tab_orders where state_code=1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
PostgreSQL 通过对执行语句的分析处理，最终把扫描的任务定位在分区 tab_order_1 上，自动把查询语句转换成下面的语句，其他分区根本不需要扫描，这就是分区裁剪技术。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
select avg(total_amount) from tab_orders_1;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
三、分区表分区方法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1.pg 分区表支持 range、list、hash（pg11 版本及以上）三种主要分区方法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2.pg 分区表的分区级数不限、方法不限：即一级分区下面可以进行二级分区，二级分区下面还可以进行 3 及分区........文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
四、分区表使用注意事项及技巧文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1. 分区表中分区键和分布式的分布键一样，不允许对分区键字段进行 update 操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2. 分区表中建议规范所有唯一性约束必须包含分区键。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1）分区父表的主键必须包含分区字段；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2）唯一索引必须包含分区字段文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
3. 分区键的选择技巧：让分区键尽可能地出现在 select、delete、update 语句的 where 条件，以发挥分区裁剪的作用来加速 SQL 性能。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
4. 在 insert 语句中，需在字段列表中指定分区键，如：insert into tab_aken (id,part_col) values (1,'2021-10-16');文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
5. 子分区数量不宜过多，现网使用中发现子分区 1000 个和子分区 300 个两者的性能有较大差别。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
五、分区表创建方法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1.range 范围分区表例子：PARTITION BY RANGE (分区键字段);文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1）. 创建父表：（pg-12 版本）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 如下使用时间字段 info_time 作为分区键，使用范围分区方法进行分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
CREATE TABLE tab_aken (
uid integer NOT NULL,
info_time timestamp NOT NULL,
money decimal(5,2) NOT NULL,
primary key (uid,info_time)
) PARTITION BY RANGE (info_time); 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2）. 按月分区 （pg-12 版本）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 方法 1：直接添加。如下添加 3 个子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
CREATE TABLE aken_2020_1 PARTITION of tab_aken FOR VALUES FROM ('2020-1-01') TO ('2020-1-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_2 PARTITION of tab_aken FOR VALUES FROM ('2020-2-01') TO ('2020-2-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_3 PARTITION of tab_aken FOR VALUES FROM ('2020-3-01') TO ('2020-3-01'::timestamp + interval '1 month');文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 方法 2：使用 generate_series 函数，按月创建 12 个子表，拼接 SQL 如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
psql -At -h 9.22.xx.xxx -p xxx -U dbmgr -d akendb -c 'SELECT 'CREATE TABLE aken_2020_' || p_month || ' PARTITION of tab_aken FOR VALUES FROM (''2020-'||p_month||'-01'') TO (''2020-'||p_month||'-01''::timestamp + interval ''1 month'');' FROM generate_series(1,12) as p_month ;' | psql -h 9.22.xx.xxx -p xxx -U dbmgr -d akendb
?column?
-------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE aken_2020_1 PARTITION of tab_aken FOR VALUES FROM ('2020-1-01') TO ('2020-1-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_2 PARTITION of tab_aken FOR VALUES FROM ('2020-2-01') TO ('2020-2-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_3 PARTITION of tab_aken FOR VALUES FROM ('2020-3-01') TO ('2020-3-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_4 PARTITION of tab_aken FOR VALUES FROM ('2020-4-01') TO ('2020-4-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_5 PARTITION of tab_aken FOR VALUES FROM ('2020-5-01') TO ('2020-5-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_6 PARTITION of tab_aken FOR VALUES FROM ('2020-6-01') TO ('2020-6-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_7 PARTITION of tab_aken FOR VALUES FROM ('2020-7-01') TO ('2020-7-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_8 PARTITION of tab_aken FOR VALUES FROM ('2020-8-01') TO ('2020-8-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_9 PARTITION of tab_aken FOR VALUES FROM ('2020-9-01') TO ('2020-9-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_10 PARTITION of tab_aken FOR VALUES FROM ('2020-10-01') TO ('2020-10-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_11 PARTITION of tab_aken FOR VALUES FROM ('2020-11-01') TO ('2020-11-01'::timestamp + interval '1 month');
CREATE TABLE aken_2020_12 PARTITION of tab_aken FOR VALUES FROM ('2020-12-01') TO ('2020-12-01'::timestamp + interval '1 month');
(12 rows)
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
3）. 查看分区表结构：可以看到父表下有 12 张分区表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# \d+ tab_aken
Partitioned table 'public.aken'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
sensor_id | integer | | not null | | plain | |
ptime | timestamp without time zone | | not null | | plain | |
temperature | numeric(5,2) | | not null | | main | |
Partition key: RANGE (ptime)
Indexes:
'aken_pkey' PRIMARY KEY, btree (sensor_id, ptime)
Partitions: aken_2020_1 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00'),
aken_2020_10 FOR VALUES FROM ('2020-10-01 00:00:00') TO ('2020-11-01 00:00:00'),
aken_2020_11 FOR VALUES FROM ('2020-11-01 00:00:00') TO ('2020-12-01 00:00:00'),
aken_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
aken_2020_2 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00'),
aken_2020_3 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00'),
aken_2020_4 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00'),
aken_2020_5 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00'),
aken_2020_6 FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00'),
aken_2020_7 FOR VALUES FROM ('2020-07-01 00:00:00') TO ('2020-08-01 00:00:00'),
aken_2020_8 FOR VALUES FROM ('2020-08-01 00:00:00') TO ('2020-09-01 00:00:00'),
aken_2020_9 FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00')文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
4）查询分区表数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# select * from tab_aken where time_col >= '2020-05-08 11:20:16'::timestamp and time_col <= '2020-05-10 16:00:00'::timestamp; 上述语句直接查询父表，DB 优化器会自动优化只查询 tab_aken_2020_5 即 5 月份这个子分区的数据，直接过滤掉其他分区，从而提高查询性能。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2.hash 分区表创建例子：PARTITION BY HASH (分区键字段)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1）. 创建父表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
CREATE TABLE tab_aken (
userid int4,
username character varying(64),
ctime timestamp(6) without time zone,
primary key(userid)) --注意：如果是pg10版本，不允许在分区表中指定主键
PARTITION BY HASH(userid); --这里表示使用userid作为分区键，使用hash进行分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2）. 创建子分区表：方法 1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 如下添加 16 个子分区，即创建 16 个子分区表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
psql -At postgres postgres -c 'SELECT 'CREATE TABLE tab_aken_' || n || ' PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER ' || n || ');' FROM generate_series(0,15) as n ;' | psql
?column?
--------------------------------------------------------------------------------------------
CREATE TABLE tab_aken_0 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE tab_aken_1 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE tab_aken_2 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE tab_aken_3 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE tab_aken_4 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE tab_aken_5 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE tab_aken_6 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE tab_aken_7 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE tab_aken_8 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE tab_aken_9 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE tab_aken_10 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE tab_aken_11 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE tab_aken_12 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE tab_aken_13 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE tab_aken_14 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE tab_aken_15 PARTITION of tab_aken FOR VALUES WITH (MODULUS 16, REMAINDER 15);
(16 rows)
db_aken=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
3）. 创建子分区表：方法 2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 如下创建 1024 个子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
do language plpgsql $$
declare
begin
for i in 0..1023 loop
execute format('create table tab_aken_%s partition of tab_aken_ for values with (MODULUS %s, REMAINDER %s)', i, 1024, i);
end loop;
end;
$$; 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
六、为分区表创建索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 在父表创建索引即可，PostgreSQL 会自动在所有子分区创建对应的索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# create index idx_sensor_id on aken using btree(sensor_id);
CREATE INDEX
akendb=# \d+ aken
Partitioned table 'public.aken'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
sensor_id | integer | | not null | | plain | |
ptime | timestamp without time zone | | not null | | plain | |
temperature | numeric(5,2) | | not null | | main | |
Partition key: RANGE (ptime)
Indexes:
'aken_pkey' PRIMARY KEY, btree (sensor_id, ptime)
'idx_sensor_id' btree (sensor_id)
Partitions: aken_2020_1 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00'),
aken_2020_11 FOR VALUES FROM ('2020-11-01 00:00:00') TO ('2020-12-01 00:00:00'),
aken_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
aken_2020_2 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00'),
aken_2020_3 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00'),
aken_2020_4 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00'),
aken_2020_5 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00'),
aken_2020_6 FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00'),
aken_2020_7 FOR VALUES FROM ('2020-07-01 00:00:00') TO ('2020-08-01 00:00:00'),
aken_2020_8 FOR VALUES FROM ('2020-08-01 00:00:00') TO ('2020-09-01 00:00:00'),
aken_2020_9 FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00')
akendb=# \d+ aken_2020_6
Table 'public.aken_2020_6'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
sensor_id | integer | | not null | | plain | |
ptime | timestamp without time zone | | not null | | plain | |
temperature | numeric(5,2) | | not null | | main | |
Partition of: aken FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00')
Partition constraint: ((ptime IS NOT NULL) AND (ptime >= '2020-06-01 00:00:00'::timestamp without time zone) AND (ptime < '2020-07-01 00:00:00'::timestamp without time zone))
Indexes:
'aken_2020_6_pkey' PRIMARY KEY, btree (sensor_id, ptime)
'aken_2020_6_sensor_id_idx' btree (sensor_id)
Access method: heap
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
七、利用分区表进行数据维护：删除分区、添加分区、卸载分区（隐藏分区）、重新挂载分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1. 删除子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 大表数据维护，mysql、PostgreSQL、Oracle 等关系型 DB 不建议使用 delete 操作，对性能影响较大。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 后期如果不需要某个时间段的数据，直接 drop 对应的子分区即可，不影响全表，对业务透明。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 当需要清理冷旧数据时，直接 drop 子分区即可，无需使用 delete 这种比较损耗性能的操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1) 首先，查看父表 tab_aken 当前有哪些子分区：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric)from pg_class where relname like 'tab_aken_part_%' order by 2;
relname | split_part
-----------------+------------
tab_aken_part_1 | 1
tab_aken_part_2 | 2
tab_aken_part_3 | 3
tab_aken_part_4 | 4
tab_aken_part_5 | 5
tab_aken_part_6 | 6
tab_aken_part_7 | 7
tab_aken_part_8 | 8
tab_aken_part_9 | 9
(9 rows)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2). 删除目标子分区：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 拼接方法，可以放到定期任务里面，如每次删除前面 N 个子分区（limit N）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 如下拼接删除 2 个最早的子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# select 'drop table '||string_agg(relname, ',')||';' as drop_target_child_partitions from ( select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric) from pg_class where relname like 'tab_aken_part%' order by 2 limit 2 ) as aaa;
drop_target_partitions
---------------------------------------------
drop table tab_aken_part_1,tab_aken_part_2;
(2 row)
akendb=# drop table tab_aken_part_1,tab_aken_part_2;
DROP TABLE
akendb=# select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric)from pg_class where relname like 'tab_aken_part_%' order by 2;
relname | split_part
-----------------+------------
tab_aken_part_3 | 3
tab_aken_part_4 | 4
tab_aken_part_5 | 5
tab_aken_part_6 | 6
tab_aken_part_7 | 7
tab_aken_part_8 | 8
tab_aken_part_9 | 9
(7 rows)
akendb=# select 'drop table '||string_agg(relname, ',')||';' as drop_target_child_partitions from ( select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric) from pg_class where relname like 'tab_aken_part%' order by 2 limit 2 ) as aaa;
drop_target_partitions
---------------------------------------------
drop table tab_aken_part_3,tab_aken_part_4;
(1 row)
akendb=# drop table tab_aken_part_3,tab_aken_part_4;
DROP TABLE
akendb=# select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric) from pg_class where relname like 'tab_aken_part_%' order by 2;
relname | split_part
-----------------+------------
tab_aken_part_5 | 5
tab_aken_part_6 | 6
tab_aken_part_7 | 7
tab_aken_part_8 | 8
tab_aken_part_9 | 9
(5 rows)
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2. 添加子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 增加子分区主要是为了承接超出已有子分区范围的业务新数据入库文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
如下当前父表已有子分区：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# select * from pg_tables where tablename like 'tab_aken%' order by tablename;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------------+------------+------------+------------+----------+-------------+-------------
public | tab_aken | dbmgr | | t | f | f | f
public | tab_aken_part_1 | dbmgr | | t | f | f | f
public | tab_aken_part_2 | dbmgr | | t | f | f | f
public | tab_aken_part_3 | dbmgr | | t | f | f | f
(3rows)
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
添加子分区方法 1：从最大子分区后面直接添加文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
-- 如下给按 range 分区的父表添加 3 个月的子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# alter table tab_aken add partitions 3; --默认会自动从最大的子分区后面添加3个子分区
ALTER TABLE
akendb=# select * from pg_tables where tablename like 'tab_aken%' order by tablename;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------------+------------+------------+------------+----------+-------------+-------------
public | tab_aken | dbmgr | | t | f | f | f
public | tab_aken_part_1 | dbmgr | | t | f | f | f
public | tab_aken_part_2 | dbmgr | | t | f | f | f
public | tab_aken_part_3 | dbmgr | | t | f | f | f
public | tab_aken_part_4 | dbmgr | | t | f | f | f
public | tab_aken_part_5 | dbmgr | | t | f | f | f
public | tab_aken_part_6 | dbmgr | | t | f | f | f
(6rows)
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
添加子分区方法 2：指定分区范围添加文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
CREATE TABLE aken_2020_7 PARTITION of aken FOR VALUES FROM ('2022-6-01') TO ('2020-6-01'::timestamp + interval '1 month');文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
3. 卸载子分区（隐藏子分区）、解绑子分区、重新绑定子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
相对于 drop 子分区，推荐先暂时将子分区从父表中移除的方式，当后续发现还需要子分区的数据，重新将子分区挂载回来即可。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
1）卸载子分区（或叫解绑子分区）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# alter table tab_aken detach partition aken_2020_6;
ALTER TABLE
akendb=# \d+ tab_aken
Partitioned table 'public.tab_aken'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-------------+-----------+----------+---------+---------+--------------+-------------
sensor_id | integer | | not null | | plain | |
ptime | timestamp without time zone | | not null | | plain | |
temperature | numeric(5,2) | | not null | | main | |
Partition key: RANGE (ptime)
Indexes:
'aken_pkey' PRIMARY KEY, btree (sensor_id, ptime)
'idx_sensor_id' btree (sensor_id)
Partitions: aken_2020_1 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00'),
aken_2020_11 FOR VALUES FROM ('2020-11-01 00:00:00') TO ('2020-12-01 00:00:00'),
aken_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
aken_2020_2 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00'),
aken_2020_3 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00'),
aken_2020_4 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00'),
aken_2020_5 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00'),
aken_2020_7 FOR VALUES FROM ('2020-07-01 00:00:00') TO ('2020-08-01 00:00:00'),
aken_2020_8 FOR VALUES FROM ('2020-08-01 00:00:00') TO ('2020-09-01 00:00:00'),
aken_2020_9 FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00')
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
和直接 DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该子表变成了一个普通的数据表:select * from tab_xxx (子分区表名)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
这样无论对 DBA 还是业务来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
在完成诸多例行性的操作之后，可以考虑是否直接删除该表 (DROP TABLE)，还是先清空该表的数据 (TRUNCATE TABLE)，或者让该表重新绑定主表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
2）重新挂载子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
akendb=# ALTER TABLE tab_aken ATTACH PARTITION aken_2020_6 FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00');
ALTER TABLE
akendb=# \d+ tab_aken
Partitioned table 'public.tab_aken'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
sensor_id | integer | | not null | | plain | |
ptime | timestamp without time zone | | not null | | plain | |
temperature | numeric(5,2) | | not null | | main | |
Partition key: RANGE (ptime)
Indexes:
'aken_pkey' PRIMARY KEY, btree (sensor_id, ptime)
'idx_sensor_id' btree (sensor_id)
Partitions: aken_2020_1 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00'),
aken_2020_11 FOR VALUES FROM ('2020-11-01 00:00:00') TO ('2020-12-01 00:00:00'),
aken_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
aken_2020_2 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00'),
aken_2020_3 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00'),
aken_2020_4 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00'),
aken_2020_5 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00'),
aken_2020_6 FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00'), <<<<<子分区6已重新绑定到父表
aken_2020_7 FOR VALUES FROM ('2020-07-01 00:00:00') TO ('2020-08-01 00:00:00'),
aken_2020_8 FOR VALUES FROM ('2020-08-01 00:00:00') TO ('2020-09-01 00:00:00'),
aken_2020_9 FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00')
akendb=# 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
4. 查看子分区文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html
select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric) from pg_class where relname like 'tab_aken_part_%' order by 2;
--split_part函数指定分隔符切割目标字符串，如 target_text=“name.cn.com” split_part(target_text,’.’,3) 切割结果为：com
--cast(target_val as numeric)相当于target_val::numeric，即cast(t_val as t_datatype)
akendb=# select relname, cast(split_part(relname,'tab_aken_part_', 2) as numeric) from pg_class where relname like 'tab_aken_part_%' order by 2;
relname | split_part
-----------------+------------
tab_aken_part_1 | 1
tab_aken_part_2 | 2
tab_aken_part_3 | 3
tab_aken_part_4 | 4
tab_aken_part_5 | 5
tab_aken_part_6 | 6
tab_aken_part_7 | 7
tab_aken_part_8 | 8
tab_aken_part_9 | 9
(9 rows)
akendb=#文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/29843.html