Postgresql - Table Partitioning - 10.6 & 11 实验

分区表在9，10，11中有很多不同点，做了很多改进。列举几个常用的。

1. 在10以后，不用写继承关系。

2. 在11中，可以添加主键。

3. 在11中出现了Hash索引。

 

10.6：

------------------------------------------------------------

表中无法增加主键，索引需要在分区子表中建立。

Range

--------

CREATE TABLE test(id int, value text) PARTITION BY RANGE(id);
create table test_1 partition of test for values from (1) to (10000000);
create table test_2 partition of test for values from (10000000) to (20000000);
create table test_3 partition of test for values from (20000000) to (30000000);

insert into test select generate_series(1,29999999),'abcdefghijklmnopqrstuvwxyz';

create index on test_1(id);
create index on test_2(id);
create index on test_3(id);

List

--------

create table test_list_part(id int, state boolean) PARTITION BY list(state);
create table test_list_part_t partition of test_list_part for values in ('t');
create table test_list_part_f partition of test_list_part for values in ('f');

insert into test_list_part values ('1','t');
insert into test_list_part values ('1','f');

create index on test_list_part_f (state);
create index on test_list_part_t (state);

 

11：

------------------------------------------------------------

可以在表中增加主键，可以在主表建索引。

Range

--------

create table test (id int primary key, col1 varchar(128), col2 varchar(128)) partition by range (id);
create table test_1 partition of test for values from (1) to (10000000);
create table test_2 partition of test for values from (10000000) to (20000000);
create table test_3 partition of test for values from (20000000) to (30000000);

 

主键必须包含分区列

alter table test add constraint test_pkey primary key (id, col1);

如果主键没有分区列，则报错

mytest=# alter table test add constraint test_pkey primary key (col1);
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "test" lacks column "id" which is part of the partition key.

 

List

--------

create table test_list_part(id int, state boolean) PARTITION BY list(state);
create table test_list_part_t partition of test_list_part for values in ('t');
create table test_list_part_f partition of test_list_part for values in ('f');

同样主键必须包含分区列，如果主键只加在分区列上，则没意义，可以用组合索引。

alter table test_list_part add constraint test_list_part_pkey primary key(id,state);

同样如果主键没有分区列，则报错

mytest=# alter table test_list_part add constraint test_list_part_pkey primary key(id);
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "test_list_part" lacks column "state" which is part of the partition key.

 

Hash

--------

create table test_hash(id int, col1 varchar(100), col2 varchar(100)) partition by hash(id);

-- 创建Hash分区表，需要自己指定模数与余数（modulus and remainder）。

create table test_hash_0 partition of test_hash for values with (MODULUS 8, REMAINDER 0);
create table test_hash_1 partition of test_hash for values with (MODULUS 8, REMAINDER 1);
create table test_hash_2 partition of test_hash for values with (MODULUS 8, REMAINDER 2);
create table test_hash_3 partition of test_hash for values with (MODULUS 8, REMAINDER 3);
create table test_hash_4 partition of test_hash for values with (MODULUS 8, REMAINDER 4);
create table test_hash_5 partition of test_hash for values with (MODULUS 8, REMAINDER 5);
create table test_hash_6 partition of test_hash for values with (MODULUS 8, REMAINDER 6);
create table test_hash_7 partition of test_hash for values with (MODULUS 8, REMAINDER 7);

insert into test_hash (select generate_series(0,200000));

 

数据库会先将分区列的值算出hash，然后再按照模数和余数插入对应的分区。

 

Information

======================================================

10

--------

主表名称和分区形式

select c.relname,p.partstrat from pg_partitioned_table p,pg_class c where p.partrelid=c.oid ;

子表表名

select relname from pg_catalog.pg_class where relispartition = 't';