标签
PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试
背景
PostgreSQL是一个历史悠久的数据库，历史可以追溯到1973年，最早由2014计算机图灵奖得主，关系数据库的鼻祖Michael_Stonebraker 操刀设计，PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。
PostgreSQL社区的贡献者众多，来自全球各个行业，历经数年，PostgreSQL 每年发布一个大版本，以持久的生命力和稳定性著称。
2017年10月，PostgreSQL 推出10 版本，携带诸多惊天特性，目标是胜任OLAP和OLTP的HTAP混合场景的需求：
《最受开发者欢迎的HTAP数据库PostgreSQL 10特性》
1、多核并行增强
2、fdw 聚合下推
3、逻辑订阅
4、分区
5、金融级多副本
6、json、jsonb全文检索
7、还有插件化形式存在的特性，如 向量计算、JIT、SQL图计算、SQL流计算、分布式并行计算、时序处理、基因测序、化学分析、图像分析 等。
在各种应用场景中都可以看到PostgreSQL的应用：
PostgreSQL近年来的发展非常迅猛，从知名数据库评测网站dbranking的数据库评分趋势，可以看到PostgreSQL向上发展的趋势：
从每年PostgreSQL中国召开的社区会议，也能看到同样的趋势，参与的公司越来越多，分享的公司越来越多，分享的主题越来越丰富，横跨了 传统企业、互联网、医疗、金融、国企、物流、电商、社交、车联网、共享XX、云、游戏、公共交通、航空、铁路、军工、培训、咨询服务等 行业。
接下来的一系列文章，将给大家介绍PostgreSQL的各种应用场景以及对应的性能指标。
环境
环境部署方法参考：
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》
阿里云 ECS：56核，224G，1.5TB*2 SSD云盘。
操作系统：CentOS 7.4 x64
数据库版本：PostgreSQL 12
PS：ECS的CPU和IO性能相比物理机会打一定的折扣，可以按下降1倍性能来估算。跑物理主机可以按这里测试的性能乘以2来估算。
场景 - 大表JOIN统计查询 (OLAP)
1、背景
多张大表的JOIN，聚合分析。例如有一张用户表，一张业务日志表表示活跃用户的行为数据，按天分区。
用户表1亿条记录，每天1000万活跃用户，产生10亿行为数据。
根据用户行为join用户表，group 用户表的某些字段，生成用户画像。
2、设计
2张表，1张1亿，uid为主键。1张10亿，订单号为主键，uid关联第一张表。
3、准备测试表
create unlogged table t_user (uid int8, info text, c1 int, c2 int, c3 int, crt_time timestamp);
create unlogged table t_user_log_20191212(id int8, uid int8, info text, crt_time timestamp);
4、准备测试函数(可选)
5、准备测试数据
insert into t_user select generate_series(1,100000000), md5(random()::text), random()*10, random()*100, random()*1000, now();
insert into t_user_log_20191212 select generate_series(1,1000000000), random()*10000000, md5(random()::text), now();
空间占用分别为8.8GB, 87GB：
postgres=# \dt+ t_user
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+----------+---------+-------------
public | t_user | table | postgres | 8880 MB |
(1 row)
postgres=# \dt+ t_user_log_20191212
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------------+-------+----------+-------+-------------
public | t_user_log_20191212 | table | postgres | 87 GB |
(1 row)
6、准备测试脚本
7、测试
alter table t_user set (parallel_workers =64);
alter table t_user_log_20191212 set (parallel_workers =64);
set max_parallel_workers=128;
set max_parallel_workers_per_gather =32;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
set jit=on;
set parallel_leader_participation =off;
set enable_sort =off;
set work_mem='64MB';
explain (analyze,verbose,timing,costs,buffers) select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
select t1.c1,count(*) from t_user t1 join t_user_log_20191212 t2 using (uid) group by c1;
8、测试结果
postgres=# explain select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Finalize HashAggregate (cost=13518803.57..13518803.68 rows=11 width=12)
Group Key: t_user.c1
-> Gather (cost=13518801.70..13518801.81 rows=352 width=12)
Workers Planned: 32
-> Partial HashAggregate (cost=13518801.70..13518801.81 rows=11 width=12)
Group Key: t_user.c1
-> Parallel Hash Join (cost=1221935.52..13362551.69 rows=31250002 width=4)
Hash Cond: (t_user_log_20191212.uid = t_user.uid)
-> Parallel Seq Scan on t_user_log_20191212 (cost=0.00..11676137.02 rows=31250002 width=8)
-> Parallel Hash (cost=1167614.01..1167614.01 rows=3125001 width=12)
-> Parallel Seq Scan on t_user (cost=0.00..1167614.01 rows=3125001 width=12)
JIT:
Functions: 20
Options: Inlining true, Optimization true, Expressions true, Deforming true
(14 rows)
postgres=# select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
c1 | count
----+-----------
8 | 100163144
7 | 99799644
10 | 50012738
9 | 100095291
1 | 99945550
5 | 99818748
4 | 99862794
2 | 100080723
0 | 50167364
6 | 99907094
3 | 100146857
(11 rows)
Time: 46214.349 ms (00:46.214)
TPS： xx
响应时间： 46 秒
不开并行和jit的话，耗时735秒，相差16倍。
参考
《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》
《PostgreSQL 使用 pgbench 测试 sysbench 相关case》
https://www.postgresql.org/docs/10/static/pgbench.html
免费领取阿里云RDS PostgreSQL实例、ECS虚拟机
