PostgreSQL分区实战


分区是为了解决表过大造成的性能问题,一般当单张表大小超过了内存大小就应该考虑分区了。 PostgreSQL 10.x 之前的版本的分区比较繁琐。10.x之后的内置分区简化了操作,将部分操作内置,最终简单三步就能够创建分区表。但是只支持范围分区(RANGE)和列表分区(LIST),11.x 版本添加了对 HASH 分区的支持。

一.三种分区方式

查询的时候查询主表即可,会自动落到分区表,当然也可以指定查询某个分区。

select * from pkslow_person_r1;

1.Range范围分区

先创建一张表带有年龄,然后我们根据年龄分段来进行分区,创建表语句如下:

CREATE TABLE pkslow_person_r (  
age int not null,  
city varchar not null  
) PARTITION BY RANGE (age);

这个语句已经指定了按age字段来分区了,接着创建分区表:

create table pkslow_person_r1 partition of pkslow_person_r for values from (MINVALUE) to (10);  
create table pkslow_person_r2 partition of pkslow_person_r for values from (11) to (20);  
create table pkslow_person_r3 partition of pkslow_person_r for values from (21) to (30);  
create table pkslow_person_r4 partition of pkslow_person_r for values from (31) to (MAXVALUE);

插入一些数据

insert into pkslow_person_r(age, city) VALUES (1, 'GZ');  
insert into pkslow_person_r(age, city) VALUES (2, 'SZ');  
insert into pkslow_person_r(age, city) VALUES (21, 'SZ');  
insert into pkslow_person_r(age, city) VALUES (13, 'BJ');  
insert into pkslow_person_r(age, city) VALUES (43, 'SH');  
insert into pkslow_person_r(age, city) VALUES (28, 'HK');

2.List列表分区

列表分区是按特定的值来分区,比较某个城市的数据放在一个分区里。

主表:

create table pkslow_person_l (  
age int not null,  
city varchar not null  
) partition by list (city);

分区表:

CREATE TABLE pkslow_person_l1 PARTITION OF pkslow_person_l FOR VALUES IN ('GZ');  
CREATE TABLE pkslow_person_l2 PARTITION OF pkslow_person_l FOR VALUES IN ('BJ');  
CREATE TABLE pkslow_person_l3 PARTITION OF pkslow_person_l DEFAULT;

插入测试数据

insert into pkslow_person_l(age, city) VALUES (1, 'GZ');  
insert into pkslow_person_l(age, city) VALUES (2, 'SZ');  
insert into pkslow_person_l(age, city) VALUES (21, 'SZ');  
insert into pkslow_person_l(age, city) VALUES (13, 'BJ');  
insert into pkslow_person_l(age, city) VALUES (43, 'SH');  
insert into pkslow_person_l(age, city) VALUES (28, 'HK');  
insert into pkslow_person_l(age, city) VALUES (28, 'GZ');

3.Hash哈希分区

哈希分区是指按字段取哈希值后再分区。

为哈希分区表创建分区时,使用 FOR VALUES WITH 子句指定分区的计算方法,其中的 MODULUS 子句用于指定除数,REMAINDER 子句用于指定哈希值被除后的余数。

所以如果要创建N个分区表,就要取N取模。

主表:

create table pkslow_person_h (  
age int not null,  
city varchar not null  
) partition by hash (city);

分区表:

create table pkslow_person_h1 partition of pkslow_person_h for values with (modulus 4, remainder 0);  
create table pkslow_person_h2 partition of pkslow_person_h for values with (modulus 4, remainder 1);  
create table pkslow_person_h3 partition of pkslow_person_h for values with (modulus 4, remainder 2);  
create table pkslow_person_h4 partition of pkslow_person_h for values with (modulus 4, remainder 3);

插入测试数据

insert into pkslow_person_h(age, city) VALUES (1, 'GZ');  
insert into pkslow_person_h(age, city) VALUES (2, 'SZ');  
insert into pkslow_person_h(age, city) VALUES (21, 'SZ');  
insert into pkslow_person_h(age, city) VALUES (13, 'BJ');  
insert into pkslow_person_h(age, city) VALUES (43, 'SH');  
insert into pkslow_person_h(age, city) VALUES (28, 'HK');

二.实际应用

1.现状以及目标

  • 待分的表数据量级在亿级,字段很多,是一张记录表。
  • 查询sql主要是按照创建时间倒叙排序查询,一般按照天,周,月去查询。
  • 表是多租户的。
  • 租户存在活跃租户(日产生的数据量5k+)和非活跃。
  • 分表后的最终结果是保证每个分区表数据量在2000万的量级。

2.具体分表思路

按照两个纬度来分表。

  • 首先按照创建时间,以月为单位Range范围分区。
  • 再按照租户划分活跃和非活跃,按照租户id进行哈希取模分区。
  • 经过测算单租户日产生的记录最大为2万,月产生最大记录为60万,分区2000万记录数允许的活跃租户数为3个,因此活跃租户的哈希MODULUS设置为3。
  • 同样方法测算非活跃租户,哈希MODULUS设置为20。

3.关于索引

  • 由于查询条件是租户id+创建时间倒叙,因此使用这两个字段的联合索引。
  • 对历史表分区后需要重建索引vacuum analyse be_user_record

4.注意事项

  • 分区主表的字段与与子表的字段一致,子表不能单独增加字段。
  • 分区子表需要手动创建,此处需要增加定时任务。
  • 分区主表上创建的索引被级联到了分区子表上。
  • 分区主表不存数据,如果插入数据不能落特定分区,数据库会报错,通过创建默认分区表,不符合分区约束的数据将会插入到默认分区。目前,range/list支持默认分区,hash分区不支持。
  • 分区子表的取值是FROM<=value<TO,取的FROM值。
  • 分区子表是对外不可见的,但是可以直接通过sql的方式对其进行增删改查。

声明:Eironn's Blog|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - PostgreSQL分区实战


Java开发,同时会一些旁门左道。