绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
PgSQL · 应用案例 · 如何修改PostgreSQL分区表分区范围
2020-08-27 09:43:50

背景

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

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

语法如下,PG支持非常灵活的分区布局,看本文提到的HASH分区拆分,支持任意层级的分区,支持每个分区的层级深度不一样。特别适合某些数据分布不均匀的情况。例如id=1落在同一个分区但是数据量非常庞大,可以对这个分区再进行二级分区(使用其他分区方法,其他字段都可以,非常灵活)。

ALTER TABLE [ IF EXISTS ] name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE [ IF EXISTS ] name DETACH PARTITION partition_name
and partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) | FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) | WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )


拆分已有分区例子(hash)

1、创建一个哈希分区表,4个分区

postgres=# create table t_hash (id int , info text) PARTITION BY hash (id);  CREATE TABLE  postgres=# create table t_hash0 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER );  CREATE TABLE  postgres=# create table t_hash1 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 1);  CREATE TABLE  postgres=# create table t_hash2 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 2);  CREATE TABLE  postgres=# create table t_hash3 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 3);  CREATE TABLE


2、查看分区表

postgres=# \d+ t_hash Partitioned table "public.t_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | info | text | | | | extended | | Partition key: HASH (id) Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder ), t_hash1 FOR VALUES WITH (modulus 4, remainder 1), t_hash2 FOR VALUES WITH (modulus 4, remainder 2), t_hash3 FOR VALUES WITH (modulus 4, remainder 3)

3、插入一些记录

postgres=# insert into t_hash select generate_series(1,10); INSERT 10

4、查看每一条记录属于哪个分区

postgres=# select tableoid::regclass,* from t_hash; tableoid | id | info ----------+----+------ t_hash0 | 1 | t_hash1 | 3 | t_hash1 | 5 | t_hash1 | 8 | t_hash1 | 9 | t_hash2 | 2 | t_hash3 | 4 | t_hash3 | 6 | t_hash3 | 7 | t_hash3 | 10 | (10 rows)

5、将1号分区拆分为2个分区。按8取模,把1号分区拆分成一个分区表(即1号分区被一个耳机分区表代替。而其他分区是直接的分区表,所以看起来就像一颗非平衡树)

4() , 4(1) , 4(2) , 4(3)
拆分为
4() , 4(1){8(1) , 8(5)} , 4(2) , 4(3)

解绑分区

postgres=# alter table t_hash DETACH PARTITION t_hash1; ALTER TABLE

创建二级分区

postgres=# create table t_hash1_subp (id int, info text) PARTITION BY hash (id);; CREATE TABLE postgres=# create table t_hash1_subp1 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 1); CREATE TABLE postgres=# create table t_hash1_subp5 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 5); CREATE TABLE

绑定二级分区到一级分区。

postgres=# alter table t_hash attach partition t_hash1_subp FOR VALUES WITH ( MODULUS 4, REMAINDER 1 ); ALTER TABLE

将原来子分区的数据写入新的二级分区表(数据迁移)

postgres=# insert into t_hash1_subp select * from t_hash1; INSERT 4

查看记录,并列出记录所在分区名

postgres=# select tableoid::regclass,* from t_hash; tableoid | id | info ---------------+----+------ t_hash0 | 1 | t_hash1_subp1 | 3 | t_hash1_subp5 | 5 | t_hash1_subp5 | 8 | t_hash1_subp5 | 9 | t_hash2 | 2 | t_hash3 | 4 | t_hash3 | 6 | t_hash3 | 7 | t_hash3 | 10 | (10 rows)

列出非平衡的分区表

postgres=# \d+ t_hash Partitioned table "public.t_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | info | text | | | | extended | | Partition key: HASH (id) Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder ), t_hash1_subp FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED, t_hash2 FOR VALUES WITH (modulus 4, remainder 2), t_hash3 FOR VALUES WITH (modulus 4, remainder 3)


扩展阅读,甚至我们可以把其他分区改成别的分区方法,例如将t_hash2改成list分区

postgres=# alter table t_hash detach partition t_hash2;ALTER TABLE
postgres=# create table t_hash2_subp (id int, info text) partition by list (info);CREATE TABLEpostgres=# create table t_hash2_supb1 partition of t_hash2_subp FOR VALUES in ('hello');CREATE TABLEpostgres=# create table t_hash2_supb2 partition of t_hash2_subp FOR VALUES in ('abc','cde');CREATE TABLEpostgres=# create table t_hash2_supb3 partition of t_hash2_subp DEFAULT;CREATE TABLE
postgres=# alter table t_hash attach partition t_hash2_subp for values with (modulus 4, remainder 2);ALTER TABLE
postgres=# \d+ t_hash Partitioned table "public.t_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | info | text | | | | extended | | Partition key: HASH (id)Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder ), t_hash1_subp FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED, t_hash2_subp FOR VALUES WITH (modulus 4, remainder 2), PARTITIONED, t_hash3 FOR VALUES WITH (modulus 4, remainder 3)
postgres=# insert into t_hash select id,'abc' from t_hash2;INSERT 1postgres=# insert into t_hash select id,'def' from t_hash2;INSERT 1postgres=# insert into t_hash select id,'hello' from t_hash2;INSERT 1
postgres=# select tableoid::regclass,* from t_hash; tableoid | id | info ---------------+----+------- t_hash0 | 1 | t_hash1_subp1 | 3 | t_hash1_subp5 | 5 | t_hash1_subp5 | 8 | t_hash1_subp5 | 9 | t_hash2_supb2 | 2 | abc t_hash2_supb1 | 2 | hello t_hash2_supb3 | 2 | def t_hash3 | 4 | t_hash3 | 6 | t_hash3 | 7 | t_hash3 | 10 | (12 rows)


拆分已有分区例子(range)

与之类似,无非就是partition_bound_spec的用法不一样,略。
and partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) | FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) | WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )


合并已有分区例子(hash)

与之类似,略


合并已有分区例子(range)

与之类似,略


小结

通过attach, detach支持分区的拆分,合并。PG支持非平衡分区表,深度不一定一致。例如本文,
4() , 4(1) , 4(2) , 4(3)
拆分为
4() , 4(1){8(1) , 8(5)} , 4(2) , 4(3)
分享好友

分享这个小栈给你的朋友们,一起进步吧。

PostgreSQL中文社区小栈
创建时间:2019-04-19 17:47:49
PostgreSQL中文社区是一个非盈利的民间组织,已经在中国成功举办过8届技术大会。目前成员都以志愿者身份加入,成立的目的在于构建PG数据库技术生态圈子(内核、用户、培训机构、厂商、服务商、软件开发商、高校形成“业务与利益双向驱动”的良性发展生态圈);帮助企业解决人才培养和企业商用数据库成本问题。社区会在各运营平台发布PG最新信息和PG相关技术文章,推动PG技术在中国的发展。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • wangliyun
    栈主
  • digoal
    嘉宾
  • 飘絮絮絮丶
    嘉宾

小栈成员

查看更多
  • 栈栈
  • 喵呜
  • osdba
  • 一号管理员
戳我,来吐槽~