背景

已有分区表，修改分区的范围。

例如拆分分区，合并分区。

语法如下，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