我的书签
添加书签
移除书签背景
已有分区表，修改分区的范围。
例如拆分分区，合并分区。
语法如下，PG支持非常灵活的分区布局，看本文提到的HASH分区拆分，支持任意层级的分区，支持每个分区的层级深度不一样。特别适合某些数据分布不均匀的情况。例如id=1落在同一个分区但是数据量非常庞大，可以对这个分区再进行二级分区（使用其他分区方法，其他字段都可以，非常灵活）。
1. ALTER TABLE [ IF EXISTS ] name
2. ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
3. ALTER TABLE [ IF EXISTS ] name
4. DETACH PARTITION partition_name
6. and partition_bound_spec is:
8. IN ( partition_bound_expr [, ...] ) |
9. FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
10. TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
11. WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
拆分已有分区例子(hash)
1、创建一个哈希分区表，4个分区
1. postgres=# create table t_hash (id int , info text) PARTITION BY hash (id);
2. CREATE TABLE
3. postgres=# create table t_hash0 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 0);
4. CREATE TABLE
5. postgres=# create table t_hash1 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 1);
6. CREATE TABLE
7. postgres=# create table t_hash2 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 2);
8. CREATE TABLE
9. postgres=# create table t_hash3 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 3);
10. CREATE TABLE
2、查看分区表
1. postgres=# \d+ t_hash
2. Partitioned table "public.t_hash"
3. Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
4. --------+---------+-----------+----------+---------+----------+--------------+-------------
5. id | integer | | | | plain | |
6. info | text | | | | extended | |
7. Partition key: HASH (id)
8. Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder 0),
9. t_hash1 FOR VALUES WITH (modulus 4, remainder 1),
10. t_hash2 FOR VALUES WITH (modulus 4, remainder 2),
11. t_hash3 FOR VALUES WITH (modulus 4, remainder 3)
3、插入一些记录
1. postgres=# insert into t_hash select generate_series(1,10);
2. INSERT 0 10
4、查看每一条记录属于哪个分区
1. postgres=# select tableoid::regclass,* from t_hash;
2. tableoid | id | info
3. ----------+----+------
4. t_hash0 | 1 |
5. t_hash1 | 3 |
6. t_hash1 | 5 |
7. t_hash1 | 8 |
8. t_hash1 | 9 |
9. t_hash2 | 2 |
10. t_hash3 | 4 |
11. t_hash3 | 6 |
12. t_hash3 | 7 |
13. t_hash3 | 10 |
14. (10 rows)
5、将1号分区拆分为2个分区。按8取模，把1号分区拆分成一个分区表（即1号分区被一个耳机分区表代替。而其他分区是直接的分区表，所以看起来就像一颗非平衡树）
1. 4(0) , 4(1) , 4(2) , 4(3)
3. 拆分为
5. 4(0) , 4(1){8(1) , 8(5)} , 4(2) , 4(3)
解绑分区
1. postgres=# alter table t_hash DETACH PARTITION t_hash1;
2. ALTER TABLE
创建二级分区
1. postgres=# create table t_hash1_subp (id int, info text) PARTITION BY hash (id);;
2. CREATE TABLE
3. postgres=# create table t_hash1_subp1 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 1);
4. CREATE TABLE
5. postgres=# create table t_hash1_subp5 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 5);
6. CREATE TABLE
绑定二级分区到一级分区。
1. postgres=# alter table t_hash attach partition t_hash1_subp FOR VALUES WITH ( MODULUS 4, REMAINDER 1 );
2. ALTER TABLE
将原来子分区的数据写入新的二级分区表(数据迁移)
1. postgres=# insert into t_hash1_subp select * from t_hash1;
2. INSERT 0 4
查看记录，并列出记录所在分区名
1. postgres=# select tableoid::regclass,* from t_hash;
2. tableoid | id | info
3. ---------------+----+------
4. t_hash0 | 1 |
5. t_hash1_subp1 | 3 |
6. t_hash1_subp5 | 5 |
7. t_hash1_subp5 | 8 |
8. t_hash1_subp5 | 9 |
9. t_hash2 | 2 |
10. t_hash3 | 4 |
11. t_hash3 | 6 |
12. t_hash3 | 7 |
13. t_hash3 | 10 |
14. (10 rows)
列出非平衡的分区表
1. postgres=# \d+ t_hash
2. Partitioned table "public.t_hash"
3. Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
4. --------+---------+-----------+----------+---------+----------+--------------+-------------
5. id | integer | | | | plain | |
6. info | text | | | | extended | |
7. Partition key: HASH (id)
8. Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder 0),
9. t_hash1_subp FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED,
10. t_hash2 FOR VALUES WITH (modulus 4, remainder 2),
11. t_hash3 FOR VALUES WITH (modulus 4, remainder 3)
扩展阅读，甚至我们可以把其他分区改成别的分区方法，例如将t_hash2改成list分区
1. postgres=# alter table t_hash detach partition t_hash2;
2. ALTER TABLE
4. postgres=# create table t_hash2_subp (id int, info text) partition by list (info);
5. CREATE TABLE
6. postgres=# create table t_hash2_supb1 partition of t_hash2_subp FOR VALUES in ('hello');
7. CREATE TABLE
8. postgres=# create table t_hash2_supb2 partition of t_hash2_subp FOR VALUES in ('abc','cde');
9. CREATE TABLE
10. postgres=# create table t_hash2_supb3 partition of t_hash2_subp DEFAULT;
11. CREATE TABLE
13. postgres=# alter table t_hash attach partition t_hash2_subp for values with (modulus 4, remainder 2);
14. ALTER TABLE
16. postgres=# \d+ t_hash
17. Partitioned table "public.t_hash"
18. Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
19. --------+---------+-----------+----------+---------+----------+--------------+-------------
20. id | integer | | | | plain | |
21. info | text | | | | extended | |
22. Partition key: HASH (id)
23. Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder 0),
24. t_hash1_subp FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED,
25. t_hash2_subp FOR VALUES WITH (modulus 4, remainder 2), PARTITIONED,
26. t_hash3 FOR VALUES WITH (modulus 4, remainder 3)
28. postgres=# insert into t_hash select id,'abc' from t_hash2;
29. INSERT 0 1
30. postgres=# insert into t_hash select id,'def' from t_hash2;
31. INSERT 0 1
32. postgres=# insert into t_hash select id,'hello' from t_hash2;
33. INSERT 0 1
35. postgres=# select tableoid::regclass,* from t_hash;
36. tableoid | id | info
37. ---------------+----+-------
38. t_hash0 | 1 |
39. t_hash1_subp1 | 3 |
40. t_hash1_subp5 | 5 |
41. t_hash1_subp5 | 8 |
42. t_hash1_subp5 | 9 |
43. t_hash2_supb2 | 2 | abc
44. t_hash2_supb1 | 2 | hello
45. t_hash2_supb3 | 2 | def
46. t_hash3 | 4 |
47. t_hash3 | 6 |
48. t_hash3 | 7 |
49. t_hash3 | 10 |
50. (12 rows)
拆分已有分区例子(range)
与之类似，无非就是partition_bound_spec的用法不一样，略。
1. and partition_bound_spec is:
3. IN ( partition_bound_expr [, ...] ) |
4. FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
5. TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
6. WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
合并已有分区例子(hash)
与之类似，略
合并已有分区例子(range)
与之类似，略
小结
通过attach, detach支持分区的拆分，合并。PG支持非平衡分区表，深度不一定一致。例如本文，
1. 4(0) , 4(1) , 4(2) , 4(3)
3. 拆分为
5. 4(0) , 4(1){8(1) , 8(5)} , 4(2) , 4(3)
参考
https://www.postgresql.org/docs/12/sql-altertable.html
