德歌：PostgreSQL独孤九式搞定物联网

【直播预告】5月27日，阿里云高级技术专家德歌将在云栖社区直播《云数据库RDS for Postgresql最佳实践》：https://yq.aliyun.com/webinar/join/15，为你分享阿里云云数据库Postgresql的最佳技术实战，包括Postgresql使用技巧、上云实战、数据迁移与同步、插件使用等内容，纯干货。

物联网行业不再仅仅只是设备的接入，设备接入后数据的采集和融合，以及融合后的分析，会为整个社会带来重要的价值。数据，让我们更真实的了解社会与自然，让人与自然、与社会更加的融合。但物联网也远没你想的那么难，经典的物联网架构分为感知层、网络层和应用层。感知层主要包括传感器网关、节点等数据采集工具；采集到的数据再经过互联网、移动通信网等传输网传递到物联网的“大脑”-应用层加以分析应用。随着物联网的越来越广泛使用，特定应用场景的需求也越发明显，如智能物流中需要对地理位置信息处理需求强烈；公安刑侦中的模糊化搜索等等。这不仅对物联网中的硬件是个挑战，同时对物联网中数据库管理系统也提出了更高的要求。

本文即为大家分享关于Postgresql如何搞定物联网的“独孤九式”——

总诀式-知己知彼、百战不殆

图一总诀式-知己知彼、百战不殆

正如兵家讲究知己知彼，百战不殆一样，要真正实现万物互联、互通的物联网，就要熟知特定场景的具体要求，有针对性地给出解决方案。通过对智能家居、环境监测、城市交通、个人保健等具体场景的分析，可以对物联网应用场景特性做一个小结：

数据量大（压缩、数据处理能力）；
数据有时序、时空、文本属性（时序、地理位置、文本数据处理能力）；
某些数据难以结构化，如图像处理（自定义能力、扩展能力、非结构化数据处理能力）；
数据处理实时性高（流式处理能力）；
数据维度多，相关性复杂（复杂查询、统计分析能力）；
有模糊、相似度查询需求（数据归类、索引功力）；
某些场景行锁竞争强烈（秒杀特性功能）。

有了总诀式作为心法总纲，就可以针对特定的“招式”一一破解。

破剑式 - 搞定非结构化、定制数据对象

图二破剑式 - 搞定非结构化、定制数据对象

要知道很多数据是不可以预先结构化的，或者是经过产品迭代过程后，预先结构化不再起作用，如图像处理等。因此非结构化的处理在物联网中显得尤为重要。

Postgresql是这样来应对非结构化数据场景的：首先Postgresql 支持JSONB数据类型，该数据类型非常适合非结构化数据场景，例如传感器采集的数据以JSON格式上传；其次在定制数据对象方面，Postgresql开放了类型扩展和索引扩展两类接口，使用者无需关注数据库内核的实现方式，只需要关注业务本身。比如电路板的质量检测场景，使用者只需要关注焊点是否虚焊，然后再通过开放的接口将其对象化到数据库中；同时Postgresql中的自定义函数支持C、Python、Java等多种语言定义，扩展性极高。

破刀式 - 搞定文本、空间、时序流式数据

图三破刀式 - 搞定文本、空间、时序流式数据

在模糊查询、分词等文本处理方面，Postgresql天然支持分词的特性，包括中文分词和英文分词，性能上能够做到每秒处理千万词汇的级别，足够满足使用者的需求。

空间地理位置数据管理方面，Postgresql 支持PostGIS和Pgrouting两种位置处理的插件，PostGIS是全球使用范围最广的地理位置信息处理插件，在美国宇航局、欧洲宇航局等企业中得到了广泛使用；Pgrouting是基于位置信息完成最短路径运算的插件。

流式处理方面，Postgresql 9.5以后的版本支持BRIN索引，非常适合带有时序属性的流式数据。如果按照时间来访问流式日志数据，以往需要创建B-tree索引进行范围查询或者精确匹配，但是B-tree索引会因为需要存储的较大信息量导致索引也很庞大；而BRIN记录的是每（连续）块元数据，索引变得很小。下图是两种索引之间差别详细对比：

图四 BRIN索引与B-TREE 索引性能对比

破枪式 - 搞定实时流处理

图五破枪式 - 搞定实时流处理

实时流处理的实时性要求很高，同时传统的流式计算开发门槛高。但采用Postgresql，仅一条sql就可以搞定流失实时处理。在数据源源不断地往数据库持续插入过程中，只需要定义好需要实时统计的窗口或者是流视图，数据库后台就可以实时地进行数据统计。查询流失处理结果的响应时间是在毫秒级别的。Postgresql在流式处理方面大大简化了开发这一环节。其处理能力相当强大，一台8G cpu的服务器每天能够处理百亿级别的流式数据。

破鞭式 - 搞定复杂查询

图六破鞭式 - 搞定复杂查询

在物联网中，因为数据维度多、相关性复杂，所以复杂查询也是一个不容忽视的问题。Postgresql中通过支持遗传算法、HASH JOIN、HASH 聚合，解决了多表查询的效率问题，在分析场景中比传统的嵌套循环性能提升100倍以上。

除此之外，在监测场景中，传统基于阀值或状态的监测方式是无法发现监测过程中存在抖动、趋势异常的情况。Postgresql中采用基于方差的监测方式用于抖动检测；同时基于时间或属性相关性，进行趋势检测，防患于未然。

破索式- 搞定数据分析

Postgresql具有强大的数据挖掘能力，可以通过一条sql搞定数据挖掘，例如：

SELECT kmeans(ARRAY[x, y, z], K) OVER (), * FROM samples;

这条语句就可以实现聚类分析；同时Postgresql 支持GPU，cpu并行计算，处理能力达到25GB/s，已经达到目前内存极限；此外Postgresql还兼容MADLib库(支持几百个机器学习库函数、对应各种数学模型)、PL/R,、PL/Python。

图七破索式- 搞定数据分析

破掌式 - 搞定秒杀(高并发行锁竞争)

图八破掌式 - 搞定秒杀(高并发行锁竞争)

在物联网领域，例如秒杀等场景行锁竞争强烈。传统的行锁具有无效等待多、无效等待用户长时间占用会话资源、发现锁冲突的代码路径长，需进行大量cpu运算等弊端。Postgresql提供了超轻锁（(advisory LOCK)）来解决高并发锁竞争问题，通过cpu运算发现行锁之前就知道是不是存在冲突，大大缩短cpu计算、等待资源，比如在秒杀抢手机的活动中，给定每个手机一个编号，拿到编号的用户才可以进行抢手机，这样就解决了并行度的问题，整体性能得到了近百倍的提升。

破箭式 - 搞定模糊、正则查询

图九破箭式 - 搞定模糊、正则查询

物联网中，对高效的模糊、相近度查询需求，较大传统查询方式是采用全表扫描的方式，百亿数据的查询响应至少是小时级别的。在Postgresql中，通过使用GIN R-TREE索引可以将查询时间缩短到秒级。

这里举一个模糊查询的例子，如上图所示的车牌，尽管对其中一部分做了遮挡，在Postgresql中，通过下几行语句，就可以轻松查出车主的个人信息：

select 'postregsql' % 'postgresql';
postgres=# select similarity('postregsql','postgresql');
similarity
------------
0.375
(1 row)
select * from tbl where info ~ '^???6888$';
'^???688?$';

Postgresql这一特性，也是其广泛地用于公安刑侦、车牌、地址、邮箱等查询中。

破气式 - 搞定大数据处理能力

图十破气式 - 搞定大数据处理能力

随着数据量的增大，会衍生出非常多的问题。在Postgresql采取了以下几种方式处理大数据：

对于单机节点，采用基于cpu和GPU的计算；
Postgresql添加了FDW插件用于数据的冷热分离，可以将数据放置在Hadoop或者Spark，通过Postgresql提供的统一访问接口，实现HTAP（在线与离线处理一份数据）；
支持OLTP分库分表；
支持读写分离、一主多备、多副本强同步；
通过级联复制，解决主库压力问题和跨机房的多份数据传输问题；
服务端编程能力，解决move data带来的网络延迟问题；
支持多主复制，解决物联网地区节点和中心节点的数据相互同步问题。

接下来，针对几个特殊的特性具体分析下它们的实现过程：

FDW - 搞定HTAP

图十一 FDW - 搞定HTAP

FDW目前仅在开源数据库中支持；对于商用数据库，目前仅SAP商业产品支持类似的功能。FDW可以实现数据的冷热分离和跨界访问。比如，可以将热数据存储在Postgresql本地，冷数据存在Hadoop或者Spark、MysqL中，通过Postgresql提供的统一的接口完成数据的跨界访问。目前mongodb就在使用Postgresql 的FDW来解决BI数据分析的问题。

数据库端编程 - 搞定网络瓶颈

图十二数据库端编程 - 搞定网络瓶颈

在目前的硬件条件下，普通的服务器都能达到上百核，内存达到PB级别。在这种硬件设备下，一台主机就能达到千万级别的QPS。这样就带来了一个问题，在数据库中us级别可处理的数据量，在网络中才传输可能会花费ms的时间。传统的解决方式将业务逻辑放到应用程序端实现，然后将数据库做的尽量简单。现在通过Postgresql，可以将代码放到数据库端，Postgresql提供了C、Python、R、Perl等语言的开发接口，通过数据库端编程解决数据移动带来的网络RT瓶颈。

rank化和相关性计算 - 搞定最强压缩比

图十三 rank化和相关性计算 - 搞定最强压缩比

随着数据量的增大，数据的存放成本也随之增大。Postgresql 中提供了列存储、压缩插件，可自动整理数据压缩。

本文所提到的所有案例的详细介绍文章链接如下：

JSONB非结构化类型使用方法

http://www.postgresql.org/docs/9.5/static/datatype-json.html
http://www.postgresql.org/docs/9.5/static/functions-json.html