時序資料庫有哪些特點? TimescaleDB時序資料庫介紹-有解無憂

背景

隨著物聯網的發展，時序資料庫的需求越來越多，比如水文監控、工廠的設備監控、國家安全相關的資料監控、通訊監控、金融行業指標資料、傳感器資料等，

在互聯網行業中，也有著非常多的時序資料，例如用戶訪問網站的行為軌跡，應用程式產生的日志資料等等，

時序資料有幾個特點

1. 基本上都是插入，沒有更新的需求，

2. 資料基本上都有時間屬性，隨著時間的推移不斷產生新的資料，舊的資料不需要保存太久，

業務方對時序資料通常有幾個查詢需求

1. 獲取最新狀態，查詢最近的資料（例如傳感器最新的狀態）

2. 展示區間統計，指定時間范圍，查詢統計資訊，例如平均值，最大值，最小值，計數等，，，

3. 獲取例外資料，根據指定條件，篩選例外資料

時序資料庫應該具備的特點

1. 壓縮能力

通常用得上時序資料庫的業務，傳感器產生的資料量都是非常龐大的，資料壓縮可以降低存盤成本，

2. 自動rotate

時序資料通常對歷史資料的保留時間間隔是有規定的，例如一個線上時序資料業務，可能只需要保留最近1周的資料，

為了方便使用，時序資料庫必須有資料自動rotate的能力，

3. 支持分片，水平擴展

因為涉及的傳感器可能很多，單個節點可能比較容易成為瓶頸，所以時序資料庫應該具備水平擴展的能力，例如分表應該支持水平磁區，

4. 自動擴展磁區，

業務對時序資料的查詢，往往都會帶上對時間區間進行過濾，因此時序資料通常在磁區時，一定會有一個時間磁區的概念，時序資料庫務必能夠支持自動擴展磁區，減少用戶的管理量，不需要人為的干預自動擴展磁區，例如1月份月末，自動創建2月份的磁區，

5. 插入性能

時序資料，插入是一個強需求，對于插入性能要求較高，

6. 磁區可洗掉

磁區可以被洗掉，例如保留1個月的資料，1個月以前的磁區都可以洗掉掉，

7. 易用性(SQL介面)

SQL是目前最通用的資料庫訪問語言，如果時序資料庫能支持SQL是最好的，

8. 型別豐富

物聯網的終端各異，會有越來越多的非標準型別的支持需求，例如采集影像的傳感器，資料庫中至少要能夠存取影像的特征值，而對于其他垂直行業也是如此，為了最大程度的詮釋業務，必須要有精準的資料型別來支撐，

9. 索引介面

支持索引，毫無疑問是為了加速查詢而引入的，

10. 高效分析能力

時序資料，除了單條的查詢，更多的是報表分析或者其他的分析類需求，這對時序資料庫的統計能力也是一個挑戰，

11. 其他特色

11.1 支持豐富的資料型別，陣列、范圍型別、JSON型別、K-V型別、GIS型別、圖型別等，滿足更多的工業化需求，例如傳感器的位置資訊、傳感器上傳的資料值的范圍，批量以陣列或JSON的形式上傳，傳感器甚至可能上傳圖片特征值，便于圖片的分析，（例如國家安全相關），軌跡資料的上層則帶有GIS屬性，

這個世界需要的是支持型別豐富的時序資料庫，而不是僅僅支持簡單型別的時序資料庫，

11.2 支持豐富的索引介面，因為型別豐富了，普通的B-TREE索引可能無法滿足快速的檢索需求，需要更多的索引來支持陣列、JSON、GIS、圖特征值、K-V、范圍型別等， (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引介面)

這兩點可以繼承PostgreSQL資料庫的已有功能，已完全滿足，

TimescaleDB介紹

TimescaleDB是基于PostgreSQL資料庫打造的一款時序資料庫，插件化的形式，隨著PostgreSQL的版本升級而升級，不會因為另立分支帶來麻煩，

TimescaleDB架構

資料自動按時間和空間分片（chunk），

TimescaleDB具備以下特點

1. 基于時序優化

2. 自動分片（按時間、空間自動分片(chunk)）

3. 全SQL介面

4. 支持垂直于橫向擴展

5. 支持時間維度、空間維度自動磁區，空間維度指屬性欄位（例如傳感器ID，用戶ID等）

6. 支持多個SERVER，多個CHUNK的并行查詢，磁區在TimescaleDB中被稱為chunk，

7. 自動調整CHUNK的大小

8. 內部寫優化（批量提交、記憶體索引、事務支持、資料倒灌），

記憶體索引，因為chunk size比較適中，所以索引基本上都不會被交換出去，寫性能比較好，

資料倒灌，因為有些傳感器的資料可能寫入延遲，導致需要寫以前的chunk，timescaleDB允許這樣的事情發生(可配置)，

9. 復雜查詢優化（根據查詢條件自動選擇chunk，最近值獲取優化(最小化的掃描,類似遞回收斂)，limit子句pushdown到不同的server,chunks，并行的聚合操作）

《時序資料合并場景加速分析和實作 - 復合索引，視窗分組查詢加速，變態遞回加速》

10. 利用已有的PostgreSQL特性（支持GIS，JOIN等），方便的管理（流復制、PITR）

11. 支持自動的按時間保留策略（自動洗掉過舊資料）

疑問

1. chunk過多，會不會影響查詢性能？

這點不需要擔心，PostgreSQL 10.0已經優化了

《PostgreSQL 10.0 preview 性能增強 - 磁區表子表元資訊搜索性能增強》

例子

1. 創建時序表(hypertable)

# Create a schema for a new hypertable  
CREATE TABLE sensor_data (  
"time" timestamp with time zone NOT NULL,  
device_id TEXT NOT NULL,  
location TEXT NULL,  
temperature NUMERIC NULL,  
humidity NUMERIC NULL,  
pm25 NUMERIC  
);  
  
# Create a hypertable from this data  
SELECT create_hypertable  
('sensor_data', 'time', 'device_id', 16);

2. 遷移資料到hyper table

# Migrate data from existing Postgres table into  
# a TimescaleDB hypertable  
INSERT INTO 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 NOT NULL AND humidity > 0.5  
AND time > now() - interval '7 day'  
GROUP BY device_id;

4. 查詢最近例外的資料

# Metrics about resource-constrained devices  
SELECT time, cpu, freemem, battery FROM devops  
WHERE device_id='foo'  
AND cpu > 0.7 AND freemem < 0.2  
ORDER BY time DESC  
LIMIT 100;

5. 計算最近7天，每小時的例外次數

# Calculate total errors by latest firmware versions  
# per hour over the last 7 days  
SELECT date_trunc('hour', time) as hour, firmware,  
COUNT(error_msg) as errno FROM data  
WHERE firmware > 50  
AND time > now() - interval '7 day'  
GROUP BY hour, firmware  
ORDER BY hour DESC, errno DESC;

6. 計算巴士的每小時平均速度

# Find average bus speed in last hour  
# for each NYC borough  
SELECT loc.region, AVG(bus.speed) FROM bus  
INNER JOIN loc ON (bus.bus_id = loc.bus_id)  
WHERE loc.city = 'nyc'  
AND bus.time > now() - interval '1 hour'  
GROUP BY loc.region;

7. 展示最近12小時，每小時的平均值

=#  SELECT date_trunc('hour', time) AS hour, AVG(weight)  
    FROM logs  
    WHERE device_type = 'pressure-sensor' AND customer_id = 440  
      AND time > now() - interval '12 hours'  
    GROUP BY hour;  
  
 hour               | AVG(weight)  
--------------------+--------------  
 2017-01-04 12:00   | 170.0  
 2017-01-04 13:00   | 174.2  
 2017-01-04 14:00   | 174.0  
 2017-01-04 15:00   | 178.6  
 2017-01-04 16:00   | 173.0  
 2017-01-04 17:00   | 169.9  
 2017-01-04 18:00   | 168.1  
 2017-01-04 19:00   | 170.2  
 2017-01-04 20:00   | 167.4  
 2017-01-04 21:00   | 168.6

8. 監控每分鐘過載的設備數量

=#  SELECT date_trunc('minute', time) AS minute, COUNT(device_id)  
    FROM logs  
    WHERE cpu_level > 0.9 AND free_mem < 1024  
      AND time > now() - interval '24 hours'  
    GROUP BY minute  
    ORDER BY COUNT(device_id) DESC LIMIT 25;  
  
 minute             | heavy_load_devices  
--------------------+---------------------  
 2017-01-04 14:59   | 1653  
 2017-01-04 15:01   | 1650  
 2017-01-04 15:00   | 1605  
 2017-01-04 15:02   | 1594  
 2017-01-04 15:03   | 1594  
 2017-01-04 15:04   | 1561  
 2017-01-04 15:06   | 1499  
 2017-01-04 15:05   | 1460  
 2017-01-04 15:08   | 1459

9. 最近7天，按韌體版本，輸出每個韌體版本的報錯次數

=#  SELECT firmware_version, SUM(error_count) FROM logs  
    WHERE time > now() - interval '7 days'  
    GROUP BY firmware_version  
    ORDER BY SUM(error_count) DESC LIMIT 10;  
  
 firmware_version  | SUM(error_count)  
-------------------+-------------------  
 1.0.10            | 191  
 1.1.0             | 180  
 1.1.1             | 179  
 1.0.8             | 164  
 1.1.3             | 161  
 1.1.2             | 152  
 1.2.1             | 144  
 1.2.0             | 137  
 1.0.7             | 130  
 1.0.5             | 112  
 1.2.2             | 110

10. 某個范圍，每小時，溫度高于90度的設備數量，

=#  SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)  
    FROM logs  
    JOIN devices ON logs.device_id = devices.id  
    WHERE logs.temperature > 90 AND devices.location = 'SITE-1'  
    GROUP BY hour;  
  
 hour               | COUNT(logs.device_id)  
--------------------+------------------------  
 2017-01-04 12:00   | 994  
 2017-01-04 13:00   | 905  
 2017-01-04 14:00   | 875  
 2017-01-04 15:00   | 910  
 2017-01-04 16:00   | 905  
 2017-01-04 17:00   | 840  
 2017-01-04 18:00   | 801  
 2017-01-04 19:00   | 813  
 2017-01-04 20:00   | 798

小結

1. TimescaleDB是基于PostgreSQL的時序資料庫插件，完全繼承了PostgreSQL的功能，對于復雜查詢，各種型別(GIS,json,k-v,影像特征值,range,陣列,復合型別,自定義型別,.....)的支持非常豐富，非常適合工業化的時序資料庫場景需求，

1.1 支持豐富的資料型別，陣列、范圍型別、JSON型別、K-V型別、GIS型別、圖型別等，滿足更多的工業化需求，例如傳感器的位置資訊、傳感器上傳的資料值的范圍，批量以陣列或JSON的形式上傳，傳感器甚至可能上傳圖片特征值，便于圖片的分析，（例如國家安全相關），軌跡資料的上層則帶有GIS屬性，

未來，這個世界更多需要的是支持型別豐富的時序資料庫，而不僅僅是支持簡單型別的時序資料庫，

1.2 支持豐富的索引介面，因為型別豐富了，普通的B-TREE索引可能無法滿足快速的檢索需求，需要更多的索引來支持陣列、JSON、GIS、圖特征值、K-V、范圍型別等， (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引介面)

2. 資料的后期處理，分析，結合PostgreSQL退出的HTAP特性，可以更好的滿足大量時序資料的實時查詢，實時挖掘的需求，

結合技術包括: CPU多核并行計算、向量計算、LLVM、列存盤、算子復用、內置的sharding 等等，

《PostgreSQL 10.0 preview 性能增強 - 推出JIT開發框架(朝著HTAP邁進)》

《分析加速引擎黑科技 - LLVM、列存、多核并行、算子復用大聯姻 - 一起來開啟PostgreSQL的百寶箱》

《PostgreSQL 向量化執行插件(瓦片式實作) 10x提速OLAP》

《PostgreSQL 10.0 preview 功能增強 - OLAP增強向量聚集索引(列存盤擴展)》

《PostGIS 地理資訊資料多核并行處理》

《PostgreSQL 9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水平分庫場景設計與實踐》

《PostgreSQL 9.6 引領開源資料庫攻克多核并行計算難題》

參考

http://www.timescale.com/index.html

http://www.timescale.com/papers/timescaledb.pdf

https://github.com/timescale/timescaledb

TimescaleDB PPT

作者

digoal

日期

2017-04-09

標簽

PostgreSQL , TimescaleDB , 時間序列 , 物聯網 , IoT

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/Introduction_to_TimescaleDB_time_series_database.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374419.html

標籤：PostgreSQL

上一篇：Oracle資料庫入門(二)

下一篇：PostgreSQL 記憶體表