天天看點
傳回

TimescaleDB時間序列資料庫

TimescaleDB:

這是一款支援完整sql開源的時間序列資料庫。

用處

1、資料量龐大

2、隻做時間索引類的插入

3、很少更新資料

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. 支援自動的按時間保留政策（自動删除過舊資料）

超表的建立 

--将普通的表轉換為超标 将時間字段作為分片字段
SELECT create_hypertable('表名', '字段');

超表的操作跟普通表的操作一樣，隻不過是插件自動幫我們分片

參數詳解：

chunk_time_interval：顯式配置時間間隔，就是根據時間來自行分區，預設是7天,選擇時間間隔的關鍵屬性是屬于最近間隔的塊（包括索引）（或塊，如果使用空間分區）适合記憶體。是以，我們通常建議設定間隔，使這些塊不超過主記憶體的 25%。

--建立超表的時候設定時間間隔 例：設定一天
SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

針對已建立的超标設定

SELECT set_chunk_time_interval('表名', interval '24 hours');
SELECT set_chunk_time_interval('表名', 86400000000);
按空間分區，建立4個分區
SELECT create_hypertable('表名', 'time', 'location', 4);

擷取超表空間大小

SELECT hypertable_size('devices') ;

資料保留政策：

相當于任務，定時清除曆史資料

--清除6個月以前的曆史資料
SELECT add_retention_policy('表名', INTERVAL '6 months');
           
--删除現有表的政策
SELECT remove_retention_policy('表名');

手動删除舊的資料塊

--要删除超過三周的所有數
SELECT drop_chunks('stocks_real_time', INTERVAL '3 weeks');
           
--删除超過兩周但新于三周的所有資料：
SELECT drop_chunks(
  'stocks_real_time',
  older_than => INTERVAL '2 weeks',
  newer_than => INTERVAL '3 weeks'
)

建立連續聚合:

聚合查詢：

--目前語句查詢目前資料集 每天的 最高值，最低值，第一個值和最後的一個值
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

從聚合查詢變為連續聚合：

可以通過CREATE MATERIALIZED VIEW指令觸發資料庫建立具有給定名稱的物化視圖，（相當于裡面是有資料存儲的）WITH (timescaledb.continuous)訓示 TimescaleDB 建立一個連續的聚合，而不僅僅是一個通用的物化視圖。最後，在AS關鍵字之後添加之前的查詢。

CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;

查詢資料的話隻需要查詢目前視圖資料即可

SELECT * FROM stock_candlestick_daily
  ORDER BY day DESC, symbol;

實時連續聚合

預設情況下，所有連續聚合都建立為實時聚合。這意味着 TimescaleDB 會将UNION尚未通過重新整理政策具體化的最新資料附加（或）到連續聚合的輸出中

使用和設定連續聚合政策：

1、自動連續聚合重新整理政策

--此政策每天運作一次，由 設定schedule_interval。當它運作時，
--它會具體化 3 天前到 1 小時前的資料，由 start_offset和設定end_offset。
--偏移時間是相對于查詢執行時間計算的。
--執行的查詢是在連續聚合中定義的查詢stock_candlestick_daily。
SELECT add_continuous_aggregate_policy('stock_candlestick_daily',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 days');

2、手動重新整理

這在插入或修改超出重新整理政策start_offset和end_offset間隔的資料時最有用。這在邊緣物聯網系統中很常見，其中裝置長時間失去網際網路連接配接，并在重新連接配接後最終發送曆史讀數。

--此手動重新整理僅更新您的連續聚合一次。它不會自動使聚合保持最新。
--要設定自動重新整理政策，請參閱前面關于連續聚合重新整理政策的部分。
--目前語句是重新整理1周前到目前的資料到連續聚合中
CALL refresh_continuous_aggregate(
  'stock_candlestick_daily',
  now() - INTERVAL '1 week',
  now()
);

資料壓縮

要啟用壓縮，您需要ALTER超stocks_real_time表。啟用壓縮時可以指定三個參數：

timescaledb.compress（必需）：在超表上啟用 TimescaleDB 壓縮

timescaledb.compress_orderby（可選）：用于對壓縮資料進行排序的列

timescaledb.compress_segmentby（可選）：用于對壓縮資料進行分組的列

如果不指定compress_orderby或compress_segmentby列，則壓縮資料按超表時間列自動排序

在超表上啟用壓縮

ALTER TABLE stocks_real_time SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'symbol'
);

自動壓縮：

壓縮政策：

add_compression_policy：許您設定一個政策，系統會根據該政策在塊達到給定年齡後在背景自動壓縮塊。

添加政策以壓縮“cpu”超表上超過 60 天的塊。

SELECT add_compression_policy('cpu', INTERVAL '60d');

壓縮超表上超過兩周的資料

SELECT add_compression_policy('stocks_real_time', INTERVAL '2 weeks');

與連續聚合政策和保留政策類似，當您運作此 SQL 時，包含至少兩周前的資料的所有塊都将被壓縮stocks_real_time，并建立循環壓縮政策。

不要試圖壓縮所有資料，這一點很重要。盡管您可以将新資料插入到壓縮塊中，但無法更新或删除壓縮行。是以，最好隻在資料老化後才壓縮資料，一旦資料不太可能需要更新。

手動壓縮：

compress_chunk 函數用于壓縮特定的塊。當使用者想要更多地控制壓縮的排程時，這最常用來代替 add_compression_policy函數。

使用此查詢手動壓縮包含超過 2 周的資料的塊。如果您手動壓縮超表塊，請考慮添加 if_not_compressed=>true（設定為true跳過已壓縮的塊）到compress_chunk()函數中。否則，TimescaleDB 在嘗試壓縮已壓縮的塊時會顯示錯誤

SELECT compress_chunk(i, if_not_compressed=>true)
  FROM show_chunks('stocks_real_time', older_than => INTERVAL ' 2 weeks') i;

驗證壓縮：

可以使用此查詢檢查超表的整體壓縮率，以檢視應用壓縮前後壓縮塊的大小

SELECT pg_size_pretty(before_compression_total_bytes) as "before compression",
  pg_size_pretty(after_compression_total_bytes) as "after compression"
  FROM hypertable_compression_stats('stocks_real_time');
上一篇: worker
下一篇: iSCSI網絡SCSI接口