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

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

创建分区表

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

创建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);
$$ 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;
$$ 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;
$$ language plpgsql;
执行批量插入
SELECT insertSale();
SELECT insertSaleAll();
批量插入海量数据需要等待很久，建议在单独的测试库中进行，否则影响数据库性能。
查询性能对比
首先对分区表和常规表进行查询性能对比
使用count(*) 对分区表进行全表扫描
使用 count(*) 对常规表进行全部扫描
可以看出分区表和常规表全表扫描没有明显差距
日期条件查询
分区表与常规表分表使用sale_date进行查询，耗时对比如下：
sale分区表数据量
saleAll常规表数据量
分区表耗时(s)
常规表耗时(s)