HTAP数据库 PostgreSQL 场景与性能测试之 2 - (OLTP) 多表JOIN
背景
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 10
PS：ECS的CPU和IO性能相比物理机会打一定的折扣，可以按下降1倍性能来估算。跑物理主机可以按这里测试的性能乘以2来估算。
场景 - 多表JOIN (OLTP)
1、背景
数据属性分散设计，查询时，通过多表JOIN提取各个属性表的数据。
2、设计
10张表，每张表1000万数据量，一对一，join。
3、准备测试表
create table t1(
id int primary key,
info text default 'tessssssssssssssssssssssssssssssssssssst',
state int default 0,
crt_time timestamp default now(),
mod_time timestamp default now()
);
create table t2 (like t1 including all);
create table t3 (like t1 including all);
create table t4 (like t1 including all);
create table t5 (like t1 including all);
create table t6 (like t1 including all);
create table t7 (like t1 including all);
create table t8 (like t1 including all);
create table t9 (like t1 including all);
create table t10 (like t1 including all);
4、准备测试函数(可选)
5、准备测试数据
insert into t1 select generate_series(1,10000000);
insert into t2 select * from t1;
insert into t3 select * from t1;
insert into t4 select * from t1;
insert into t5 select * from t1;
insert into t6 select * from t1;
insert into t7 select * from t1;
insert into t8 select * from t1;
insert into t9 select * from t1;
insert into t10 select * from t1;
alter role all set join_collapse_limit=1;
6、准备测试脚本
vi test.sql
\set id random(1,10000000)
select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;
7、测试
CONNECTS=112
TIMES=300
export PGHOST=$PGDATA
export PGPORT=1999
export PGUSER=postgres
export PGPASSWORD=postgres
export PGDATABASE=postgres
pgbench -M prepared -n -r -P 5 -f ./test.sql -c $CONNECTS -j $CONNECTS -T $TIMES
alter role all reset join_collapse_limit;
8、测试结果
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 112
number of threads: 112
duration: 300 s
number of transactions actually processed: 31033358
latency average = 1.083 ms
latency stddev = 1.063 ms
tps = 103408.162713 (including connections establishing)
tps = 103433.470434 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set id random(1,10000000)
1.082 select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;
TPS：103433
平均响应时间：1.083 毫秒
参考
《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》
《PostgreSQL 使用 pgbench 测试 sysbench 相关case》
https://www.postgresql.org/docs/10/static/pgbench.html