postgres效率和性能对比2

一、为什么提出postgres单表与分区表的效率比对？

　在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

　　PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

　　分区的具体好处是：

· 某些类型的查询性能可以得到极大提升。

· 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。

· 批量删除可以用简单的删除某个分区来实现。

· 可以将很少用的数据移动到便宜的、转速慢的存储介质上。

二、什么时候应该分区？

　　小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

三、怎么分区？

在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

一、简介

range分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 主表创建
create table range_test(id int, date date)
  partition by range ( date );
-- 字表创建
create table range_test_201902 partition of range_test(id primary key)
  for values from ('2019-02-01') to ('2019-03-01');

create table range_test_201903 partition of range_test(id primary key)
  for values from ('2019-03-01') to ('2019-04-01');

-- 插入测试数据
INSERT INTO range_test(id,date) VALUES (1,'2019-03-18');

-- 查看插入数据
select * from range_test;
select * from range_test_201903;

检查分区表是否创建成功

插入一条数据检查效果

list分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 主表创建
create table test_list(id int, city text)
  partition by list( city );
-- 子表创建
create table test_list_jn partition of test_list(id primary key)
  for values in ('济南');

create table test_list_hz partition of test_list(id primary key)
  for values in ('杭州');

-- 插入测试数据
INSERT INTO test_list(id, city) VALUES (1,'杭州');

-- 查看插入数据
select * from test_list;
select * from test_list_hz;

检查分区表是否创建成功

插入一条数据检查效果

hash分区

这里说一句，hash算法在某些情况下可以使用list代替，比如partition by list (abs(mod(id,4)))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 主表创建
create table test_hash(id int, info text)
  partition by hash (id);
-- 子表创建
create table test_hash0 partition of test_hash
  for values WITH (MODULUS 4, REMAINDER 0);

create table test_hash1 partition of test_hash
  for values WITH (MODULUS 4, REMAINDER 1);

create table test_hash2 partition of test_hash
  for values WITH (MODULUS 4, REMAINDER 2);

create table test_hash3 partition of test_hash
  for values WITH (MODULUS 4, REMAINDER 3);

-- 插入测试数据
INSERT INTO test_list(id, info) VALUES (1,'Dzer0');

-- 查看插入数据
select * from test_hash;
select * from test_hash0;

检查分区表是否创建成功

插入一条数据检查效果

默认分区

针对pg12 默认分区只支持list/range分区不支持hash分区

默认是分区主要是针对防止 list/range规则匹配不到的数据，默认写到该分区表中。

以上面range分区’range_test’举例

1	create table range_test_default partition of range_test default;

检查分区表是否创建成功

插入不再range规则中的数据测试

其它

最后一个笔记：postgres一个主从集群里面的postgres.conf配置文件中有俩个参数对主从流复制很重要

1	synchronous_standby_names = '' # =all

意思是所有slave都被允许以同步方式连接到master，但同一时间只能有一台slave是同步模式。另外可以指定slave，将值设置为slave的application_name即可。

1	synchronous_commit = on

这个参数控制是否等待wal日志buffer写入磁盘再返回用户事物状态信息。
这个对性能影响还是比较大的，看业务实际情况可考虑关闭，在关键数据更新时在事物中将其暂时性打开，保证关键数据不会因意外停机而丢失。
默认情况下是打开状态。同步流复制模式需要打开这个参数。注意如果slave节点挂掉主节点将无法update数据会一致等待下去，需要将该参数修改为off后，pgctl reload后才能继续插入.

二、环境

操作系统：Windows Server 2008 R2 Standard

内存：32G

Cpu:12核

PostgreSQL版本：postgresql-x64-12 - PostgreSQL Server 12

三、实现分区

在收费系统tFeeDetail创建分区表，按年份创建分区，每年的一月到十二月为一个分区。

步骤：

1. 创建父表

2. 创建n个子表，每个子表都是继承于父表

3. 定义一个规则（Rule）或触发器（Trigger），把对主表的数据插入重定向到合适的分区表

（1）创建父表

create table tFeeDetailFen(Id integer,MeterReadId integer,MeterNo integer,ActualReceiveQty numeric(18,0),FeeKindId integer,FeeNameId integer,Price numeric(18,2),Discount numeric(18,2),LadderPriceId integer,LadderLavel integer,LadderPrice numeric(18,2),LadderDiscount numeric(18,2),CalculatedFines smallint,FinesDate timestamp without time zone,FinesScale numeric(18,4),FineClearDate timestamp without time zone,Fines numeric(18,2),ChargeMethods character varying(10) ,State character varying(2) ,WriteOffEmployeeNo character varying(20) ,WriteOffDate timestamp without time zone,ShouldAmount numeric(18,2),YearMonth character varying(7),IsVote smallint, IsAdjustFine smallint,AdjustFine numeric(18,2), AgreementNo character varying(20))

partition by range ( YearMonth );

（2）创建n个子表，每个子表都是继承于父表

create table tFeeDetailFen_2000 partition of tFeeDetailFen(id primary key)

for values from ('2000-01') to ('2000-12');

create table tFeeDetailFen_2001 partition of tFeeDetailFen(id primary key)

for values from ('2001-01') to ('2001-12');

create table tFeeDetailFen_2002 partition of tFeeDetailFen(id primary key)

for values from ('2002-01') to ('2002-12');

create table tFeeDetailFen_2003 partition of tFeeDetailFen(id primary key)

for values from ('2003-01') to ('2003-12');

create table tFeeDetailFen_2004 partition of tFeeDetailFen(id primary key)

for values from ('2004-01') to ('2004-12');

create table tFeeDetailFen_2005 partition of tFeeDetailFen(id primary key)

for values from ('2005-01') to ('2005-12');

create table tFeeDetailFen_2006 partition of tFeeDetailFen(id primary key)

for values from ('2006-01') to ('2006-12');

create table tFeeDetailFen_2007 partition of tFeeDetailFen(id primary key)

for values from ('2007-01') to ('2007-12');

create table tFeeDetailFen_2008 partition of tFeeDetailFen(id primary key)

for values from ('2008-01') to ('2008-12');

create table tFeeDetailFen_2009 partition of tFeeDetailFen(id primary key)

for values from ('2009-01') to ('2009-12');

create table tFeeDetailFen_2010 partition of tFeeDetailFen(id primary key)

for values from ('2010-01') to ('2010-12');

create table tFeeDetailFen_2011 partition of tFeeDetailFen(id primary key)

for values from ('2011-01') to ('2011-12');

create table tFeeDetailFen_2012 partition of tFeeDetailFen(id primary key)

for values from ('2012-01') to ('2012-12');

create table tFeeDetailFen_2013 partition of tFeeDetailFen(id primary key)

for values from ('2013-01') to ('2013-12');

create table tFeeDetailFen_2014 partition of tFeeDetailFen(id primary key)

for values from ('2014-01') to ('2014-12');

create table tFeeDetailFen_2015 partition of tFeeDetailFen(id primary key)

for values from ('2015-01') to ('2015-12');

create table tFeeDetailFen_2016 partition of tFeeDetailFen(id primary key)

for values from ('2016-01') to ('2016-12');

create table tFeeDetailFen_2017 partition of tFeeDetailFen(id primary key)

for values from ('2017-01') to ('2017-12');

create table tFeeDetailFen_2018 partition of tFeeDetailFen(id primary key)

for values from ('2018-01') to ('2018-12');

create table tFeeDetailFen_2019 partition of tFeeDetailFen(id primary key)

for values from ('2019-01') to ('2019-12');

create table tFeeDetailFen_2020 partition of tFeeDetailFen(id primary key)

for values from ('2020-01') to ('2020-12');

create table tFeeDetailFen_2021 partition of tFeeDetailFen(id primary key)

for values from ('2021-01') to ('2021-12');

create table range_test_default partition of tFeeDetailFen default;

--创建索引：

CREATE INDEX tfeedetailfen_yearmonth ON tfeedetailfen("yearmonth");

CREATE INDEX tfeedetailfen_id ON tfeedetailfen("id");

CREATE INDEX tfeedetailfen_meterreadid ON tfeedetailfen("meterreadid");

CREATE INDEX tfeedetailfen_meterno ON tfeedetailfen("meterno");

CREATE INDEX tFeeDetail_yearmonth ON public."tFeeDetail"("YearMonth");

CREATE INDEX tFeeDetail_MeterReadId ON public."tFeeDetail"("MeterReadId");

CREATE INDEX tFeeDetail_MeterNo on public."tFeeDetail"("MeterNo");

CREATE INDEX tMeterRead_Id ON public."tMeterRead"("Id");

CREATE UNIQUE INDEX tPaymentsDetail_Id on public."tPaymentsDetail"("Id");

CREATE INDEX tPaymentsDetail_MeterReadId on public."tPaymentsDetail"("MeterReadId");

CREATE INDEX tPaymentsDetail_FeeDetailId on public."tPaymentsDetail"("FeeDetailId");

CREATE INDEX tPaymentsDetail_MeterNo on public."tPaymentsDetail"("MeterNo");

插入数据：

insert into public.tfeedetailfen

select * from public."tFeeDetail"

设置分区表的最大起始值：

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

select max("Id") from public."tFeeDetail"

select setval('"tFeeDetail_Id_seq"',58717686,false);

创建好的分区表如下图：

四、分区前后效率对比分析

（1）查询所有的数据，单表查询快。

单表：

select count(*) from public."tFeeDetail"

--单表：58386627。查询运行时间总数: 26 秒 877 毫秒。

分区表：

select count(*) from tfeedetailfen

--分区表：58386627。查询运行时间总数: 45 秒 915 毫秒。

（2）单表按月份查询，分区表查询快。

单表：

select count(*) from public."tFeeDetail" where "YearMonth" between '2020-01' and '2020-12';

--单表：8164780 查询运行时间总数: 29 秒 524 毫秒。

分区表：

select count(*) from tfeedetailfen where "yearmonth" between '2020-01' and '2020-12'

--分区表：8164780 查询运行时间总数: 16 秒 644 毫秒。

（3）关联表按月份查询，分区表查询快。

单表：

select count(*) from public."tFeeDetail" a,public."tMeterRead" b

where a."MeterReadId"=b."Id" and a."YearMonth" between '2020-01' and '2020-12';

-- 单表：8164780 查询运行时间总数: 55 秒 849 毫秒。

分区表：

select count(*) from tfeedetailfen a,public."tMeterRead" b

where a.meterreadid=b."Id" and a.yearmonth between '2020-01' and '2020-12'

-分区表：8164780 -查询运行时间总数: 30 秒 4 毫秒。

（二）关联表按月份修改比较，分区表修改快。

单表：

update public."tFeeDetail" set "ActualReceiveQty"=10 from public."tMeterRead" where "MeterReadId"=public."tMeterRead"."Id" and public."tFeeDetail"."YearMonth"='2020-10'

-- 单表：689201 耗时13 分 48 秒成功返回查询。

分区表：

update tfeedetailfen set actualreceiveqty=10 from public."tMeterRead" where tfeedetailfen.meterreadid=public."tMeterRead"."Id" and tfeedetailfen.yearmonth='2020-10'

-- 分表：689201 耗时2 分 12 秒成功返回查询。

（三）多表关联插入比较，分区表插入快。

单表

insert into public."tFeeDetail" ("MeterReadId","MeterNo","ActualReceiveQty","FeeKindId","FeeNameId","Price","Discount","LadderPriceId","LadderLavel","LadderPrice",

"LadderDiscount","CalculatedFines","FinesDate","FinesScale",

"FineClearDate","Fines","ChargeMethods","State","WriteOffEmployeeNo","WriteOffDate","ShouldAmount","YearMonth","IsVote","IsAdjustFine","AdjustFine","AgreementNo")

select public."tFeeDetail"."MeterReadId",public."tFeeDetail"."MeterNo",public."tFeeDetail"."ActualReceiveQty",public."tFeeDetail"."FeeKindId",

public."tFeeDetail"."FeeNameId",public."tFeeDetail"."Price",public."tFeeDetail"."Discount",public."tFeeDetail"."LadderPriceId",

public."tFeeDetail"."LadderLavel",public."tFeeDetail"."LadderPrice",

public."tFeeDetail"."LadderDiscount",public."tFeeDetail"."CalculatedFines",public."tFeeDetail"."FinesDate",public."tFeeDetail"."FinesScale",

public."tFeeDetail"."FineClearDate",public."tFeeDetail"."Fines",public."tFeeDetail"."ChargeMethods",public."tFeeDetail"."State",public."tFeeDetail"."WriteOffEmployeeNo",

public."tFeeDetail"."WriteOffDate",public."tFeeDetail"."ShouldAmount",public."tFeeDetail"."YearMonth",public."tFeeDetail"."IsVote",public."tFeeDetail"."IsAdjustFine",

public."tFeeDetail"."AdjustFine",public."tFeeDetail"."AgreementNo"

from public."tFeeDetail",public."tMeterRead" where "MeterReadId"=public."tMeterRead"."Id" and

public."tFeeDetail"."YearMonth"='2020-10'

--单表：689201 耗时38 秒 572 毫秒成功返回查询。

分区表

insert into tfeedetailfen(id, meterreadid, meterno, actualreceiveqty, feekindid, feenameid, price, discount, ladderpriceid, ladderlavel, ladderprice, ladderdiscount, calculatedfines, finesdate, finesscale, finecleardate, fines, chargemethods, state, writeoffemployeeno, writeoffdate, shouldamount, yearmonth, isvote, isadjustfine, adjustfine, agreementno)

select public."tFeeDetail"."Id",public."tFeeDetail"."MeterReadId",public."tFeeDetail"."MeterNo",public."tFeeDetail"."ActualReceiveQty",public."tFeeDetail"."FeeKindId",

public."tFeeDetail"."FeeNameId",public."tFeeDetail"."Price",public."tFeeDetail"."Discount",public."tFeeDetail"."LadderPriceId",

public."tFeeDetail"."LadderLavel",public."tFeeDetail"."LadderPrice",

public."tFeeDetail"."LadderDiscount",public."tFeeDetail"."CalculatedFines",public."tFeeDetail"."FinesDate",public."tFeeDetail"."FinesScale",

public."tFeeDetail"."FineClearDate",public."tFeeDetail"."Fines",public."tFeeDetail"."ChargeMethods",public."tFeeDetail"."State",public."tFeeDetail"."WriteOffEmployeeNo",

public."tFeeDetail"."WriteOffDate",public."tFeeDetail"."ShouldAmount",public."tFeeDetail"."YearMonth",public."tFeeDetail"."IsVote",public."tFeeDetail"."IsAdjustFine",

public."tFeeDetail"."AdjustFine",public."tFeeDetail"."AgreementNo"

from public."tFeeDetail",public."tMeterRead" where "MeterReadId"=public."tMeterRead"."Id" and

public."tFeeDetail"."YearMonth"='2020-10' and public."tFeeDetail"."Id">58717695

--分区表：689201 耗时25 秒 362 毫秒成功返回查询。

（四）多表关联数据删除比较，单表删除快。

分区表

delete from tfeedetailfen where id in (

select public."tFeeDetail"."Id"

from public."tFeeDetail" ,public."tMeterRead" where "MeterReadId"=public."tMeterRead"."Id" and

public."tFeeDetail"."YearMonth"='2020-10' and public."tFeeDetail"."Id">58717695

)

--分区表：689201 耗时41 秒 346 毫秒成功返回查询。

单表

delete from public."tFeeDetail" where "Id" in (

select public."tFeeDetail"."Id"

from public."tFeeDetail" ,public."tMeterRead" where "MeterReadId"=public."tMeterRead"."Id" and

public."tFeeDetail"."YearMonth"='2020-10' and public."tFeeDetail"."Id">58717695

)

--单表：689201 耗时5 秒 566 毫秒成功返回查询。

五、结论

1. 某些类型的查询性能得到提升。如按分区的字段去查询，能节约资源快速的把数据查询出来。

2. 更新的性能也可以得到提升，因为某块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。

3. 批量删除可以通过简单的删除某个分区来实现。

4. 可以将很少用的数据移动到便宜的、转速慢的存储介质上。比如一些历史的数据，很小用的数据就迁移到固定的同一个地方，需要用的时候再调出来查询。

5. 对于分区表来说，如果有50个分区表，对于某个条件的值如果能确定，那么很可能直接过滤掉49个分区，大大提高扫描速度，当然分区表也能放在不同的物理盘上，提高IO速度。

6.分区技术可以让postgres对数据的管理能力提升。通过优良的分区，postgres可以简化特定数据操作的执行方式。postgres在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性。postgres不需要手工的去划分和维护。