德歌：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$';select * from tbl where info ~ '^???688?$';

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

 

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

 

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

 

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

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

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

 

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

• 时间序列 与 流式实时处理, 实时流式数据处理案例 (万亿每天)
  

     https://yq.aliyun.com/articles/166

• 地理位置信息处理能力
  

     https://yq.aliyun.com/articles/2999

• 中文分词
  

     https://github.com/jaiminpan/pg_jieba 
     https://github.com/jaiminpan/pg_scws 
     http://blog.163.com/digoal@126/blog/static/163877040201422410175698/ 

• 模糊查询，正则查询 (1000亿级, 5秒内返回)
  

     https://yq.aliyun.com/articles/7549

• BRIN索引，非常适合带有时序属性的流式数据
  

     http://blog.163.com/digoal@126/blog/static/163877040201531931956500/

• 标签系统，例如人物画像
  

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

• 秒杀 (TPS 从 2855 优化到 231376 的手段)
  

     https://yq.aliyun.com/articles/3010

• 高并发 ( 8000个并发会话，随机更新500万记录表中的1条记录)
  

     https://yq.aliyun.com/articles/102

• 列存储、提升压缩比。节约98%空间的例子
  

     https://yq.aliyun.com/articles/18042

 

结尾

针对PostgreSQL，阿里云也做了很多工作，为了便于大家使用PostgreSQL，阿里云中特推出了ApsaraDB for RDS (PG)，提供7*24小时的顶级专家服务，同时也基于PostgreSQL的社区版本做了一定的优化工作。 

本文根据阿里云飞天八部数据库技术组技术专家德歌 在5月17日举办的2016云栖大会·武汉峰会上《PostgreSQL数据库之物联网的应用》演讲整理而成。