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

概述介绍《德歌：PostgreSQL独孤九式搞定物联网》开发教程，希望对您有用。

《德歌：Postgresql独孤九式搞定物联网》要点：
本文介绍了德歌：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 索引性能对比

破q式 - 搞定实时流处置

图五破q式 - 搞定实时流处置

实时流处理的实时性要求很高,同时传统的流式计算开发门槛高.但采用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采取了以下几种方式处置大数据：

对于单机节点,采纳基于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 非结构化类型使用办法

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

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

地理位置信息处理才能

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

中文分词

https://github.com/jaiminpan/pg_jIEba

https://github.com/jaiminpan/pg_scws