PostgreSQL分区表实战

简介

PostgreSQL属于关系型数据库，虽然在排行一直在MySql之下，但是笔者觉得PostgreSQL功能比MySql更加丰富强大，而且使用极爽。特别是近几年推出的jsonb、分区表等功能，如果合理利用PostgreSQL这些功能，那么使用关系数据库处理亿级数据量将不再是梦想。本篇文章将给大家介绍如何使用PostgreSQL进行分区管理实现亿级数据库优化。

开始

接下来会以销售单为案例创建两种销售表，分别使用分区表和常规表方式进行对比。

创建分区表

在postgre中创建一张sale表并按照销售日期进行分区

名称	类型	是否主键	描述
id	guid	是	id
sale_date	date	否	销售日期
country_code	text	否	地区编码
product_sku	text	否	商品sku
units	int4	否	单位

创建sale主表

CREATE TABLE sale (
   Id uuid,
   sale_date       date not null,
   country_code    text,
   product_sku     text,
   units           integer

) PARTITION BY RANGE (sale_date);

创建sale分表

CREATE TABLE sale_202001 PARTITION OF sale FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');
CREATE TABLE sale_202002 PARTITION OF sale FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');
CREATE TABLE sale_202003 PARTITION OF sale FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE sale_202004 PARTITION OF sale FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE sale_202005 PARTITION OF sale FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE sale_202006 PARTITION OF sale FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE TABLE sale_202007 PARTITION OF sale FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE TABLE sale_202008 PARTITION OF sale FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');
CREATE TABLE sale_202009 PARTITION OF sale FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');
CREATE TABLE sale_202010 PARTITION OF sale FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE sale_202011 PARTITION OF sale FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE sale_202012 PARTITION OF sale FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');

给分表件索引
postgre分区表中实际数据都是存在区表上的，所以只有给区表建索引才有作用。

ALTER TABLE sale_202001 ADD PRIMARY KEY(id);
ALTER TABLE sale_202002 ADD PRIMARY KEY(id);
ALTER TABLE sale_202003 ADD PRIMARY KEY(id);
ALTER TABLE sale_202004 ADD PRIMARY KEY(id);
ALTER TABLE sale_202005 ADD PRIMARY KEY(id);
ALTER TABLE sale_202006 ADD PRIMARY KEY(id);
ALTER TABLE sale_202007 ADD PRIMARY KEY(id);
ALTER TABLE sale_202008 ADD PRIMARY KEY(id);
ALTER TABLE sale_202009 ADD PRIMARY KEY(id);
ALTER TABLE sale_202010 ADD PRIMARY KEY(id);
ALTER TABLE sale_202011 ADD PRIMARY KEY(id);
ALTER TABLE sale_202012 ADD PRIMARY KEY(id);

创建常规表

在postgre中创建一直saleAll常规表不做分区

CREATE TABLE saleAll (
   Id uuid,
   sale_date       date not null,
   country_code    text,
   product_sku     text,
   units           integer

); 

ALTER TABLE saleAll ADD PRIMARY KEY(id);

插入海量数据

通过5000万条数据对比分区表和常规表查询性能
sale分区表批量插入数据

-- 随机数 --
CREATE OR REPLACE FUNCTION random_between(low INT ,high INT) 
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;

-- sale批量新增 --
create or replace function insertSale() returns void as
$$
declare 
i int :=0;
p TEXT :='';
tempTime timestamp;
j int;
begin
while i < 50000000 loop
    j:=round(random()*80);
		p:=concat('P-', random_between(1000,2000)::TEXT);
    tempTime:=date '2020-01-01' + j;
    insert into sale(id, sale_date,    country_code, product_sku, units) 
    values(uuid_generate_v4(),tempTime, 'CN', p ,round(random() * 100));
    i:= i+1;
raise notice 'holy shit%', i;
end loop;
end;
$$ language plpgsql;

saleAll常规表批量插入数据

-- saleAll批量新增 --
create or replace function insertSaleAll() returns void as
$$
declare 
i int :=0;
p TEXT :='';
tempTime timestamp;
j int;
begin
while i < 50000000 loop
    j:=round(random()*80);
		p:=concat('P-', random_between(1000,2000)::TEXT);
    tempTime:=date '2020-01-01' + j;
    insert into saleAll(id, sale_date,    country_code, product_sku, units) 
    values(uuid_generate_v4(),tempTime, 'CN', p ,round(random() * 100));
    i:= i+1;
raise notice 'holy shit%', i;
end loop;
end;
$$ language plpgsql;

执行批量插入

SELECT insertSale();
SELECT insertSaleAll();

批量插入海量数据需要等待很久，建议在单独的测试库中进行，否则影响数据库性能。

查询性能对比

首先对分区表和常规表进行查询性能对比

全表扫描

使用count(*) 对分区表进行全表扫描

使用 count(*) 对常规表进行全部扫描

可以看出分区表和常规表全表扫描没有明显差距

日期条件查询

分区表与常规表分表使用sale_date进行查询，耗时对比如下：

查询	日期范围	sale分区表数据量	saleAll常规表数据量	分区表耗时(s)	常规表耗时(s)
count(*)	2020-01-01至2020-02-01	11113634	12991066	1.355	3.042
count(*)	2020-02-01至2020-03-01	10576517	12371957	1.224	3.030
count(*)	2020-03-01至2020-04-01	7506640	8541846	0.980	2.848
count(*)	2020-04-01至2020-05-01	5154498	4956280	0.706	2.716
count(*)	2020-05-01至2020-06-01	5321426	5199780	0.607	2.760
count(*)	2020-06-01至2020-07-01	3395589	3181768	0.495	2.657
count(*)	2020-07-01至2020-08-01	2561833	788134	0.415	2.634
count(*)	2020-08-01至2020-09-01	2524290	798648	0.326	2.601
count(*)	2020-09-01至2020-10-01	1591223	481197	0.246	2.654
count(*)	2020-10-01至2020-11-01	986697	1181387	0.240	2.617
count(*)	2020-11-01至2020-12-01	897223	1162590	0.206	2.662
count(*)	2020-12-01至2020-12-31	488046	730686	0.095	2.582

从上面对比可以分析出使用sale_date字段进行查询时sale主表自动导航到区别进行查询，所以查询耗时回根据区表的大小有所差异。常规表使用sale_date进行查询时会扫描全表，所以查询时间非常平均，而且数据量较大时查询速度会非常缓慢。

索引查询

使用索引对分区表和常规表进行查询比较
sale分区表使用索引查询如下：

saleAll常规表使用索引查询如下：

通过索引查询结果显示分区表和常规表的速度都是毫秒内的，但是如果细算一下分区表的查询速度比常规表快4倍左右。

普通字段查询

下面使用product_sku普通字段进行查询对比
分区表查询：

常规表查询：

总结

通过以上操作对比可以看出，在单表数据过大时查询速度会明细变慢，使用分区表对大数据量进行分表存储而所有增删改只需对主表进行操作会大大提高查询效率。Postgre中还要更多的配置用来提高性能，或许我们在系统架构中无需搭建大数据平台，直接使用Postgre处理大数据也会成为一种选择。