PostGresql最全分区操作

 

一、Greenplum 分区原理

分区表意思是将一个大表在物理上分割成几块，GPDB中的分区表和PostgreSQL中实现原理一样，都是用过表继承、约束来实现。但是与PostgreSQL也有所不同，在PostgreSQL中，一个父表，多个子表来实现分区表，需要手动向子表插入数据，如果向父表插入数据，则直接会被插入到父表中，在GPDB中，可以直接想父表插入数据，便可以根据约束直接自动向对应的子表插入数据，当分区子表不存在时，插入失败
 

二、分区表创建

2.1、范围分区（range）

根据分区字段的值范围区间来分区，每一个分区就是一个子表

1. eg:

2. create table test_partition_range

3. (

4. id int,

5. name varchar(64),

6. fdate varchar(64)

7. ) distributed by (id)

8. partition by range(fdate)

9. (

10. partition p1 start ('2017-01-01') inclusive end ('2017-01-31') exclusive,

11. partition p2 start ('2017-02-01') inclusive end ('2017-02-29') exclusive,

12. default partition default_p

13. );

14.  

15. inclusive ：指定包含，例如上面的 start ('2017-01-01') inclusive 则是包含'2017-01-01'

16. exclusive ： 指定不包含， 例如上面的 end ('2017-01-31') exclusive 则是不包含'2017-01-31'

 

2.2、快速分区（every）

根据选定的范围，跨越基数，快速分区每一个子表

1. eg:

2. create table test_partition_every_1

3. (

4. id int,

5. name varchar(64),

6. fdate date

7. )

8. distributed by (id)

9. partition by range (fdate)

10. (

11. partition pn_ start ('2017-01-01'::date) end ('2017-12-31'::date) every ('1 day'::interval),

12. default partition default_p

13. );

14.  

15. every：指定跨越基数

 

2.3、list分区（list）

根据值的分组，相同的数据归类到一组，也就一个分区中

1. eg:

2. create table test_partition_list

3. (

4. id int,

5. name varchar(64),

6. fdate varchar(10)

7. )

8. distributed by (id)

9. partition by list (fdate)

10. (

11. partition p1 values ('2017-01-01', '2017-01-02'),

12. partition p2 values ('2017-01-03'),

13. default partition pd

14. );

 

三、分区相关操作

3.1、分区split

切割普通分区：

1. 将分区p2 在 '2017-02-20' 左右切分成两块

2. alter table test_partition_range split partition p2 at ('2017-02-20') into (partition p2, partition p3);

3.  

4. 切割默认分区：

5. alter table test_partition_range split default partition start ('2017-03-01') end ('2017-03-31') into (partition p4, default partition);

 

3.2、分区add

如果存在default partition，则不能add分区，只能split default partition

alter table test_partition_range_1 add partition p2 start ('2017-02-01') end ('2017-02-31');

 

3.3、分区drop

彻底删除对应的分区表

alter table test_partition_range_1 DROP partition p2;

 

3.4、分区truncate

清空分区表数据，相当于删除分区，然后再新建一个

alter table test_partition_range_1 truncate partition p1;

 

四、子分区创建与操作

4.1、子分区创建

在GPDB中，分区是可以嵌套增加的，分区下面可以有子分区

1. create table test_partition_range_2

2. (

3. id int,

4. name varchar(64),

5. fdate varchar(10)

6. )

7. distributed by (id)

8. partition by range(fdate)

9. subpartition by list(name)

10. subpartition template

11. (

12. subpartition c1 values ('xiaoxiao'),

13. subpartition c2 values ('xiaohua')

14. )

15. (

16. partition p1 start ('2017-01-01') end ('2017-01-31')

17. )

18. 上面的分区中，p1会再分两个c1/c2子分区

 

4.2、truncate 子分区

alter table test_partition_range_2 alter partition p1 truncate partition c2;

 

4.3、drop 子分区

alter table test_partition_range_2 alter partition p1 drop partition c2;