PostgreSQL , TimescaleDB , 時(shí)間序列 , 物聯(lián)網(wǎng) , IoT
隨著物聯(lián)網(wǎng)的發(fā)展，時(shí)序數(shù)據(jù)庫(kù)的需求越來(lái)越多议忽，比如水文監(jiān)控十减、工廠的設(shè)備監(jiān)控、國(guó)家安全相關(guān)的數(shù)據(jù)監(jiān)控嫉称、通訊監(jiān)控、金融行業(yè)指標(biāo)數(shù)據(jù)壳繁、傳感器數(shù)據(jù)等荔棉。
在互聯(lián)網(wǎng)行業(yè)中，也有著非常多的時(shí)序數(shù)據(jù)润樱，例如用戶訪問(wèn)網(wǎng)站的行為軌跡，應(yīng)用程序產(chǎn)生的日志數(shù)據(jù)等等嗅钻。
時(shí)序數(shù)據(jù)有幾個(gè)特點(diǎn)
1. 基本上都是插入，沒(méi)有更新的需求养篓。
2. 數(shù)據(jù)基本上都有時(shí)間屬性赂蕴，隨著時(shí)間的推移不斷產(chǎn)生新的數(shù)據(jù)，舊的數(shù)據(jù)不需要保存太久概说。
業(yè)務(wù)方對(duì)時(shí)序數(shù)據(jù)通常有幾個(gè)查詢需求
1. 獲取最新?tīng)顟B(tài)，查詢最近的數(shù)據(jù)（例如傳感器最新的狀態(tài)）
2. 展示區(qū)間統(tǒng)計(jì)萍丐，指定時(shí)間范圍放典，查詢統(tǒng)計(jì)信息船万，例如平均值骨田，最大值态贤，最小值，計(jì)數(shù)等悠汽。芥驳。。
3. 獲取異常數(shù)據(jù)兆旬，根據(jù)指定條件，篩選異常數(shù)據(jù)
時(shí)序數(shù)據(jù)庫(kù)應(yīng)該具備的特點(diǎn)
1. 壓縮能力
通常用得上時(shí)序數(shù)據(jù)庫(kù)的業(yè)務(wù)宿饱，傳感器產(chǎn)生的數(shù)據(jù)量都是非常龐大的，數(shù)據(jù)壓縮可以降低存儲(chǔ)成本谬以。
2. 自動(dòng)rotate
時(shí)序數(shù)據(jù)通常對(duì)歷史數(shù)據(jù)的保留時(shí)間間隔是有規(guī)定的由桌，例如一個(gè)線上時(shí)序數(shù)據(jù)業(yè)務(wù)，可能只需要保留最近1周的數(shù)據(jù)行您。
為了方便使用，時(shí)序數(shù)據(jù)庫(kù)必須有數(shù)據(jù)自動(dòng)rotate的能力片橡。
3. 支持分片淮野，水平擴(kuò)展
因?yàn)樯婕暗膫鞲衅骺赡芎芏啵瑔蝹€(gè)節(jié)點(diǎn)可能比較容易成為瓶頸骤星，所以時(shí)序數(shù)據(jù)庫(kù)應(yīng)該具備水平擴(kuò)展的能力，例如分表應(yīng)該支持水平分區(qū)舆吮。
4. 自動(dòng)擴(kuò)展分區(qū)，
業(yè)務(wù)對(duì)時(shí)序數(shù)據(jù)的查詢色冀，往往都會(huì)帶上對(duì)時(shí)間區(qū)間進(jìn)行過(guò)濾，因此時(shí)序數(shù)據(jù)通常在分區(qū)時(shí)屯换，一定會(huì)有一個(gè)時(shí)間分區(qū)的概念。時(shí)序數(shù)據(jù)庫(kù)務(wù)必能夠支持自動(dòng)擴(kuò)展分區(qū)彤悔，減少用戶的管理量索守，不需要人為的干預(yù)自動(dòng)擴(kuò)展分區(qū)。例如1月份月末卵佛，自動(dòng)創(chuàng)建2月份的分區(qū)。
5. 插入性能
時(shí)序數(shù)據(jù)望拖，插入是一個(gè)強(qiáng)需求挫鸽。對(duì)于插入性能要求較高。
6. 分區(qū)可刪除
分區(qū)可以被刪除丢郊，例如保留1個(gè)月的數(shù)據(jù)，1個(gè)月以前的分區(qū)都可以刪除掉架诞。
7. 易用性(SQL接口)
SQL是目前最通用的數(shù)據(jù)庫(kù)訪問(wèn)語(yǔ)言，如果時(shí)序數(shù)據(jù)庫(kù)能支持SQL是最好的谴忧。
8. 類型豐富
物聯(lián)網(wǎng)的終端各異角虫，會(huì)有越來(lái)越多的非標(biāo)準(zhǔn)類型的支持需求。例如采集圖像的傳感器戳鹅，數(shù)據(jù)庫(kù)中至少要能夠存取圖像的特征值。而對(duì)于其他垂直行業(yè)也是如此妇穴，為了最大程度的詮釋業(yè)務(wù)爬虱，必須要有精準(zhǔn)的數(shù)據(jù)類型來(lái)支撐跑筝。
9. 索引接口
支持索引，毫無(wú)疑問(wèn)是為了加速查詢而引入的继蜡。
10. 高效分析能力
時(shí)序數(shù)據(jù)逛腿，除了單條的查詢单默，更多的是報(bào)表分析或者其他的分析類需求。這對(duì)時(shí)序數(shù)據(jù)庫(kù)的統(tǒng)計(jì)能力也是一個(gè)挑戰(zhàn)搁廓。
11. 其他特色
11.1 支持豐富的數(shù)據(jù)類型耕皮，數(shù)組、范圍類型凌停、JSON類型、K-V類型台诗、GIS類型、圖類型等拉队。滿足更多的工業(yè)化需求阻逮，例如傳感器的位置信息、傳感器上傳的數(shù)據(jù)值的范圍叔扼，批量以數(shù)組或JSON的形式上傳，傳感器甚至可能上傳圖片特征值慷蠕，便于圖片的分析。（例如國(guó)家安全相關(guān)）流炕，軌跡數(shù)據(jù)的上層則帶有GIS屬性。
這個(gè)世界需要的是支持類型豐富的時(shí)序數(shù)據(jù)庫(kù)每辟，而不是僅僅支持簡(jiǎn)單類型的時(shí)序數(shù)據(jù)庫(kù)。
11.2 支持豐富的索引接口渠欺，因?yàn)轭愋拓S富了，普通的B-TREE索引可能無(wú)法滿足快速的檢索需求胳岂，需要更多的索引來(lái)支持 數(shù)組、JSON乳丰、GIS内贮、圖特征值、K-V夜郁、范圍類型等奕筐。 (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引接口)
這兩點(diǎn)可以繼承PostgreSQL數(shù)據(jù)庫(kù)的已有功能，已完全滿足著角。
TimescaleDB是基于PostgreSQL數(shù)據(jù)庫(kù)打造的一款時(shí)序數(shù)據(jù)庫(kù)剑勾，插件化的形式虽另，隨著PostgreSQL的版本升級(jí)而升級(jí)，不會(huì)因?yàn)榱砹⒎种?lái)麻煩捂刺。
數(shù)據(jù)自動(dòng)按時(shí)間和空間分片（chunk）募寨。
1. 基于時(shí)序優(yōu)化
2. 自動(dòng)分片（按時(shí)間、空間自動(dòng)分片(chunk)）
3. 全SQL接口
4. 支持垂直于橫向擴(kuò)展
5. 支持時(shí)間維度拔鹰、空間維度自動(dòng)分區(qū)∏』空間維度指屬性字段（例如傳感器ID宾茂，用戶ID等）
6. 支持多個(gè)SERVER跨晴，多個(gè)CHUNK的并行查詢片林。分區(qū)在TimescaleDB中被稱為chunk。
7. 自動(dòng)調(diào)整CHUNK的大小
8. 內(nèi)部寫(xiě)優(yōu)化（批量提交费封、內(nèi)存索引、事務(wù)支持弓摘、數(shù)據(jù)倒灌）。
內(nèi)存索引，因?yàn)閏hunk size比較適中爷抓，所以索引基本上都不會(huì)被交換出去，寫(xiě)性能比較好蓝撇。
數(shù)據(jù)倒灌，因?yàn)橛行﹤鞲衅鞯臄?shù)據(jù)可能寫(xiě)入延遲虽抄，導(dǎo)致需要寫(xiě)以前的chunk，timescaleDB允許這樣的事情發(fā)生(可配置)迈窟。
9. 復(fù)雜查詢優(yōu)化（根據(jù)查詢條件自動(dòng)選擇chunk忌栅，最近值獲取優(yōu)化(最小化的掃描,類似遞歸收斂)，limit子句pushdown到不同的server,chunks索绪，并行的聚合操作）
《時(shí)序數(shù)據(jù)合并場(chǎng)景加速分析和實(shí)現(xiàn) - 復(fù)合索引，窗口分組查詢加速瑞驱，變態(tài)遞歸加速》
10. 利用已有的PostgreSQL特性（支持GIS，JOIN等）凳寺，方便的管理（流復(fù)制、PITR）
11. 支持自動(dòng)的按時(shí)間保留策略（自動(dòng)刪除過(guò)舊數(shù)據(jù)）
1. chunk過(guò)多读第，會(huì)不會(huì)影響查詢性能？
這點(diǎn)不需要擔(dān)心怜瞒，PostgreSQL 10.0已經(jīng)優(yōu)化了
《PostgreSQL 10.0 preview 性能增強(qiáng) - 分區(qū)表子表元信息搜索性能增強(qiáng)》
1. 創(chuàng)建時(shí)序表(hypertable)
# Create a schema for a new hypertable? CREATE TABLE sensor_data ("time"timestamp with time zone NOTNULL,? device_id TEXT NOTNULL,? location TEXTNULL,? temperature NUMERICNULL,? humidity NUMERICNULL,? pm25 NUMERIC? );# Create a hypertable from this data? SELECT create_hypertable? ('sensor_data','time','device_id',16);
2. 遷移數(shù)據(jù)到hyper table
# Migrate data from existing Postgres table into? # a TimescaleDB hypertable? INSERTINTO sensor_data (SELECT * FROM old_data);
3. 查詢hyper table
# Query hypertable like any SQL table? SELECT device_id, AVG(temperature) from sensor_data? WHERE temperature IS NOTNULLANDhumidity >0.5ANDtime > now() - interval'7 day'GROUP BY device_id;
4. 查詢最近異常的數(shù)據(jù)
# Metrics about resource-constrained devices? SELECTtime, cpu, freemem, battery FROM devops? WHERE device_id='foo'AND cpu >0.7AND freemem <0.2ORDER BYtimeDESC? LIMIT100;
5. 計(jì)算最近7天，每小時(shí)的異常次數(shù)
# Calculate total errors by latest firmware versions? # per hour over the last 7 days? SELECT date_trunc('hour', time)ashour, firmware,? COUNT(error_msg)aserrno FROM data? WHERE firmware >50ANDtime > now() - interval'7 day'GROUP BY hour, firmware? ORDER BY hour DESC, errno DESC;
6. 計(jì)算巴士的每小時(shí)平均速度
# Find average bus speed in last hour? # for each NYC borough? SELECT loc.region, AVG(bus.speed)FROM bus? INNER JOIN locON(bus.bus_id = loc.bus_id)? WHERE loc.city='nyc'AND bus.time > now() - interval'1 hour'GROUP BY loc.region;
7. 展示最近12小時(shí)惠窄，每小時(shí)的平均值
=#? SELECT date_trunc('hour', time) AS hour, AVG(weight)? FROM logs? ? ? WHERE device_type ='pressure-sensor'AND customer_id =440AND time > now() - interval'12 hours'GROUP BY hour;? ? hour| AVG(weight)?
--------------------+--------------?
2017-01-04 12:00? |170.02017-01-0413:00| 174.2?
2017-01-04 14:00? |174.02017-01-0415:00| 178.6?
2017-01-04 16:00? |173.02017-01-0417:00| 169.9?
2017-01-04 18:00? |168.12017-01-0419:00| 170.2?
2017-01-04 20:00? |167.42017-01-0421:00| 168.6?
8. 監(jiān)控每分鐘過(guò)載的設(shè)備數(shù)量
=#? SELECT date_trunc('minute', time) AS minute, COUNT(device_id)? FROM logs? ? ? WHERE cpu_level >0.9AND free_mem <1024AND time > now() - interval'24 hours'GROUP BY minute? ? ? ORDER BY COUNT(device_id) DESC LIMIT25;? ? minute| heavy_load_devices?
--------------------+---------------------?
2017-01-04 14:59? |16532017-01-0415:01| 1650?
2017-01-04 15:00? |16052017-01-0415:02| 1594?
2017-01-04 15:03? |15942017-01-0415:04| 1561?
2017-01-04 15:06? |14992017-01-0415:05| 1460?
2017-01-04 15:08? |1459
9. 最近7天杆融，按固件版本，輸出每個(gè)固件版本的報(bào)錯(cuò)次數(shù)
=#? SELECT firmware_version, SUM(error_count) FROM logs? WHERE time > now() - interval'7 days'GROUP BY firmware_version? ? ? ORDER BY SUM(error_count) DESC LIMIT10;? ? firmware_version| SUM(error_count)?
-------------------+-------------------?
1.0.10? ? ? ? ? ? |1911.1.0| 180?
1.1.1? ? ? ? ? ? |1791.0.8| 164?
1.1.3? ? ? ? ? ? |1611.1.2| 152?
1.2.1? ? ? ? ? ? |1441.2.0| 137?
1.0.7? ? ? ? ? ? |1301.0.5| 112?
1.2.2? ? ? ? ? ? |110
10. 某個(gè)范圍脾歇，每小時(shí)淘捡，溫度高于90度的設(shè)備數(shù)量。
=#? SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)? FROM logs? ? ? JOIN devices ON logs.device_id = devices.id? ? ? WHERE logs.temperature >90AND devices.location ='SITE-1'GROUP BY hour;? ? hour| COUNT(logs.device_id)?
--------------------+------------------------?
2017-01-04 12:00? |9942017-01-0413:00| 905?
2017-01-04 14:00? |8752017-01-0415:00| 910?
2017-01-04 16:00? |9052017-01-0417:00| 840?
2017-01-04 18:00? |8012017-01-0419:00| 813?
2017-01-04 20:00? |798
1. TimescaleDB是基于PostgreSQL的時(shí)序數(shù)據(jù)庫(kù)插件激况，完全繼承了PostgreSQL的功能，對(duì)于復(fù)雜查詢乌逐，各種類型(GIS,json,k-v,圖像特征值,range,數(shù)組,復(fù)合類型,自定義類型,.....)的支持非常豐富创葡，非常適合工業(yè)化的時(shí)序數(shù)據(jù)庫(kù)場(chǎng)景需求。
1.1 支持豐富的數(shù)據(jù)類型蹈丸，數(shù)組、范圍類型逻杖、JSON類型、K-V類型闻伶、GIS類型、圖類型等蓝翰。滿足更多的工業(yè)化需求光绕，例如傳感器的位置信息诞帐、傳感器上傳的數(shù)據(jù)值的范圍，批量以數(shù)組或JSON的形式上傳停蕉，傳感器甚至可能上傳圖片特征值钙态，便于圖片的分析。（例如國(guó)家安全相關(guān)）册倒，軌跡數(shù)據(jù)的上層則帶有GIS屬性。
未來(lái)驻子，這個(gè)世界更多需要的是支持類型豐富的時(shí)序數(shù)據(jù)庫(kù)，而不僅僅是支持簡(jiǎn)單類型的時(shí)序數(shù)據(jù)庫(kù)脾歧。
1.2 支持豐富的索引接口，因?yàn)轭愋拓S富了，普通的B-TREE索引可能無(wú)法滿足快速的檢索需求司顿，需要更多的索引來(lái)支持 數(shù)組、JSON大溜、GIS、圖特征值钦奋、K-V、范圍類型等朦拖。 (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引接口)
2. 數(shù)據(jù)的后期處理，分析璧帝，結(jié)合PostgreSQL退出的HTAP特性，可以更好的滿足大量時(shí)序數(shù)據(jù)的實(shí)時(shí)查詢睬隶，實(shí)時(shí)挖掘的需求。
結(jié)合技術(shù)包括: CPU多核并行計(jì)算苏潜、向量計(jì)算、LLVM恤左、列存儲(chǔ)、算子復(fù)用赃梧、內(nèi)置的sharding 等等。
《PostgreSQL 10.0 preview 性能增強(qiáng) - 推出JIT開(kāi)發(fā)框架(朝著HTAP邁進(jìn))》
《分析加速引擎黑科技 - LLVM授嘀、列存、多核并行览闰、算子復(fù)用 大聯(lián)姻 - 一起來(lái)開(kāi)啟PostgreSQL的百寶箱》
《PostgreSQL 向量化執(zhí)行插件(瓦片式實(shí)現(xiàn)) 10x提速OLAP》
《PostgreSQL 10.0 preview 功能增強(qiáng) - OLAP增強(qiáng) 向量聚集索引(列存儲(chǔ)擴(kuò)展)》
《PostGIS 地理信息數(shù)據(jù) 多核并行處理》
《PostgreSQL 9.6 引領(lǐng)開(kāi)源數(shù)據(jù)庫(kù)攻克多核并行計(jì)算難題》
http://www.timescale.com/index.html
http://www.timescale.com/papers/timescaledb.pdf
