BXW

博哥的blog

博哥的技术博客

0%

PostgreSQL分区实战

发表于 分类于

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

三种分区方式

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

1
2
select * from pkslow_person_r;
select * from pkslow_person_r1;
Range范围分区

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

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

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

1
2
3
4
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);

插入一些数据

1
2
3
4
5
6
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');
List列表分区

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

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

分区表：

1
2
3
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;

插入测试数据

1
2
3
4
5
6
7
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');
Hash哈希分区

哈希分区是指按字段取哈希值后再分区。
为哈希分区表创建分区时，使用 FOR VALUES WITH 子句指定分区的计算方法，其中的 MODULUS 子句用于指定除数，REMAINDER 子句用于指定哈希值被除后的余数。
所以如果要创建N个分区表，就要取N取模。

主表：

1
2
3
4
reate table pkslow_person_h (  
age int not null,  
city varchar not null  
) partition by hash (city);

分区表：

1
2
3
4
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);

插入测试数据

1
2
3
4
5
6
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. 待分的表数据量级在亿级，字段很多，是一张记录表。
  2. 查询sql主要是按照创建时间倒叙排序查询，一般按照天，周，月去查询。
  3. 表是多租户的。
  4. 租户存在活跃租户（日产生的数据量5k+）和非活跃。
  5. 分表后的最终结果是保证每个分区表数据量在2000万的量级。
具体分表思路

按照两个纬度来分表。

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

关于索引
  • 由于查询条件是租户id+创建时间倒叙，因此使用这两个字段的联合索引。
  • 对历史表分区后需要重建索引vacuum analyse be_user_record
注意事项
  • 分区主表的字段与与子表的字段一致，子表不能单独增加字段。
  • 分区子表需要手动创建，此处需要增加定时任务。
  • 分区主表上创建的索引被级联到了分区子表上。
  • 分区主表不存数据，如果插入数据不能落特定分区，数据库会报错，通过创建默认分区表，不符合分区约束的数据将会插入到默认分区。目前，range/list支持默认分区，hash分区不支持。
  • 分区子表的取值是FROM<=value<TO，取的FROM值。
  • 分区子表是对外不可见的，但是可以直接通过sql的方式对其进行增删改查。