德歌：PostgreSQL獨孤九式搞定物聯網

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

這條語句就可以實現群集；同時PostgreSQL支援GPU，CPU平行計算，處理能力達到25GB/s，已經達到目前記憶體極限；此外PostgreSQL還相容MADLib庫(支援幾百個機器學習服務庫函數、對應各種數學型號)、PL/R,、PL/Python。

圖七破索式 -搞定資料分析

破掌式 -搞定秒殺(高並發行鎖競爭)

圖八破掌式 -搞定秒殺(高並發行鎖競爭)

在物聯網領域，例如秒殺等場景行鎖競爭強烈。傳統的行鎖具有無效等待多、無效等待用戶長時間佔用對話資源、發現鎖衝突的代碼路徑長，需進行大量CPU運算等弊端。PostgreSQL提供了超輕鎖（(advisoryLOCK)）來解決高並行鎖競爭問題，通過CPU運算發現行鎖之前就知道是不是存在衝突，大大縮短CPU計算、等待資源，比如在秒殺搶手機的活動中，給定每個手機一個編號，拿到編號的用戶才可以進行搶手機，這樣就解決了並行度的問題，整體效能得到了近百倍的升階。

破箭式 -搞定柔邊、正則查詢

圖九破箭式 -搞定柔邊、正則查詢

物聯網中，對強烈影響柔邊、相近度查詢需求，較大傳統查詢方式是採用全資料表掃描的方式，百億資料的查詢回應至少是小時等級的。在PostgreSQL中，通過使用GINR-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，阿裡雲中特推出了ApsaraDBfor RDS (PG)，提供7*24小時的頂級專家服務，同時也基於PostgreSQL的社群組建做了一定的優化工作。

本文根據阿裡雲飛天八部資料庫技術組技術專家德歌在5月17日舉辦的2016雲棲大會·武漢峰會上《PostgreSQL資料庫之物聯網的套用》演講清理而成。

·【直播預告】5月27日，阿裡雲進階技術專家德歌將在雲棲社群直播《雲資料庫RDS for PostgreSQL最佳實踐》：https://yq.aliyun.com/webinar/join/15，為你分享阿裡雲雲資料庫PostgreSQL的最佳技術實戰，包括PostgreSQL提示、上雲實戰、資料移轉與同步、外掛程式使用等內容，純乾貨。

相關產品：

1.物聯網套件

2.金融解決方案

3.企業級分散式套用服務

4.雲端服務器ECS