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 10

PS：ECS的CPU和IO性能相比物理机会打一定的折扣，可以按下降1倍性能来估算。跑物理主机可以按这里测试的性能乘以2来估算。

越来越多的应用正在接入空间数据属性，例如 物联网、车辆轨迹管理、公安系统的稽侦系统、O2O应用、LBS交友应用、打车应用等等。

被管理的对象携带空间属性，对象的运动形成了轨迹，最后一个位置点表示对象的最终位置。

PostgreSQL在空间数据库管理领域有这几十年的发展历程，例如PostGIS空间数据库,pgrouting路由插件等，GiST空间索引，SP-GiST空间分区索引等。

本文要测试的是空间数据的合并更新性能（携带空间索引），例如，更新对象的最终空间位置，同时根据用户输入，搜索附近N米内满足条件的对象（用到了btree_gist插件以及GiST索引）。

2000万个被跟踪对象，2000万个点，含空间索引。

1、实时合并更新被跟踪对象的位置。

2、同时根据用户输入，搜索附近N米内满足条件的对象。

创建测试表、索引。

查询为多维度搜索，除了空间相近，还需要满足某些查询条件，例如：

注意，order by的两侧需要对齐类型，例如geometry <-> geometry，这样才会走索引哦，否则效率差的很。

测试仅使用一般的CPU资源(28核)。

1、更新用户的最终位置，由于空间移动有一定的速比，所以更新后是基于原有位置的一个相对位移位置。

压测

2、根据用户输入的att1, att2条件，搜索满足条件的附近5公里内的对象，根据距离顺序返回100条。

压测

1、注意，为了提高过滤性能，同时为了支持米为单位的距离，我们存储时使用4326 srid, 同时距离过滤时使用以下表达式

st_buffer输出的多边形精度(边的锯齿)，可以通过第三个参数指定

http://postgis.net/docs/manual-2.4/ST_Buffer.html

2、本文使用的插件btree_gin, btree_gist用于加速数组搜索，空间数据与其他普通字段的搜索。

3、使用的索引接口gist用于KNN搜索，距离排序。

4、unionall用于普通字段(可枚举)+gis字段的复合排序输出。

1、如果业务方要求按普通字段(当可以枚举时) + 空间字段排序，可以这样来操作，以达到最好的性能。

例如先返回空闲状态的骑手，其次返回最近7天活跃的骑手，其次。。。。

效果：

自动跳过不需要执行的SQL，类似如下

如果业务上还有数组条件的包含查询过滤，可以创建intarray插件，把数组、普通字段、空间字段放到一个GIST索引里面

异或使用单独的gin+gist索引（看哪种效率高）

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

例子(使用数组、普通字段、空间字段放到一个GIST索引里面)：

1、建表，索引

2、更新用户的最终位置(同样2000万骑手)，由于空间移动有一定的速比，所以更新后是基于原有位置的一个相对位移位置。

压测

数据样本

3、根据用户输入的att2, att4, 空间 条件，搜索满足条件的附近5公里内的对象，根据距离顺序返回100条。

执行计划

压测脚本

压测

通过intarray , btree_gist插件，使得gist索引接口同时支持了普通类型、数组类型、空间类型的复合索引。查询效率上大幅提升。

《PostgreSQL Oracle 兼容性之 - PL/SQL DETERMINISTIC 与PG函数稳定性(immutable, stable, volatile)》

《PostgreSQL 函数稳定性与constraint_excluded分区表逻辑推理过滤的CASE》

《函数稳定性讲解 - retalk PostgreSQL function's [ volatile|stable|immutable ]》

《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》

《函数稳定性讲解 - Thinking PostgreSQL Function's Volatility Categories》

当使用绑定变量时，例如本例中用到了ST函数，构建随机点，作为输入。那么绑定变量是用x,y坐标的float8呢，还是用geometry呢？

例如

或者

以上两种bind方法，实际上在优化器端看到的是有非常巨大的差别的，操作符能不能用索引过滤，取决于输入条件是不是常量，或者stable、immutable function。

如果BIND在st_makepoint里面，可能导致在使用prepared statement generic plan时，不能使用正确的索引。

1、BIND到st_makepoint里面

前五次执行计划都是custom plan，过滤条件过滤att1,att2条件，而且过滤空间条件。

5次后，generic plan变成如下执行计划，过滤条件变成只过滤att1,att2条件，而不能过滤空间条件

《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

2、BIND为geometry类型

一直都是一样的执行计划，索引作为了att1,att2,以及空间三个过滤条件

当使用BIND时，建议在明确使用索引的操作符的最外层，作为整个BIND的变量，而不要放到表达式里面的变量中。

如本例的

可以改成如下：

《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》

《数据库选型之 - 大象十八摸 - 致 架构师、开发者》

《PostgreSQL 使用 pgbench 测试 sysbench 相关case》

《数据库界的华山论剑 tpc.org》