924
偽列應用 - 數據轉存和下推
標簽
PostgreSQL , FDW , 偽列 , function pushdown , DS , 時序數據
背景
金融、氣象、物聯網、互聯網等行業，有特別多的時序數據，例如股票的交易數據，氣象傳感器的采集數據，車聯網的軌跡數據，互聯網的FEED數據，都具有非常強的時序屬性。
時序數據如何有效的存儲、方便的使用呢？
例如這樣的寫入
create table xx (xxx) ts interval day;
insert into table values (x,x,x,x) ;
數據可以根據到達時間，自動寫入對應時間存儲分片中。
而這樣的查詢，會自動從對應的存儲分片中獲取數據。
select * from table where ts between ? and ?;
這樣的功能是很好實現的，例如使用分區表，每個分區對應一個時間片。
PostgreSQL的timescale插件，就可以方便的實現時序數據存取的功能。
但是本文講的是另一種時序實現方法，當我們不想把數據存在本地，而是想存到外部時（例如對象存儲），PG提供了一個FDW的接口，阿裏雲RDS PG提供了一種OSs_fdw允許用戶將數據寫入OSS對象存儲，而不落數據庫。
有沒有一種簡單的方法可以將時序數據寫入外部，並且查詢時還能從中獲取對應的分片，減少數據傳輸呢？
使用FDW分區表是一種方法，如果INTERVAL很細的話，會造成分區表過多，分區表過多至少目前是會有一定的影響。（例如每小時一個分區，一年一章報就有365*24個分區）。
有沒有更好的方法呢？
偽列
通過偽列實現是不錯的，用戶不需要存儲一個真實的時間字段，而是將數據到達時間作為一個條件，將對應記錄寫入對應的存儲分片。
同時可以在文件頭部記錄每個分片的時間區間。
偽列實際是不存在的，所以表上其實沒有這列，（類似PG的ctid,xmin,xmax,cmin,cmax列） 但是查詢時如何將時間傳給FDW呢？
操作符, 函數 pushdown
使用操作符、函數的pushdown，可以將一些信息傳給FDW，使用HOOK，可以在中途截獲這部分pushdown，轉換成對應的請求發給遠端，從而實現偽列的用戶SQL接口和遠端的消息傳遞。
例如
select * from table where xxx and function(ts1, ts2);
中途截獲SQL，得到ts1,ts2，傳遞給遠端。
PostgreSQL postgres_fdw插件支持的pushdown介紹如下，building function可以pushdown，extension的immutable function可以下推。
我們自己定義FDW接口，也可以借鑒。
Remote Execution Options
By default, only WHERE clauses using built-in operators and functions will be considered for execution on the remote server.
Clauses involving non-built-in functions are checked locally after rows are fetched.
If such functions are available on the remote server and can be relied on to produce the same results as they do locally,
performance can be improved by sending such WHERE clauses for remote execution.
This behavior can be controlled using the following option:
extensions
This option is a comma-separated list of names of PostgreSQL extensions that are installed, in compatible versions, on both the local and remote servers.
Functions and operators that are immutable and belong to a listed extension will be considered shippable to the remote server.
This option can only be specified for foreign servers, not per-table.
When using the extensions option, it is the user's responsibility that the listed extensions exist and behave identically on both the local and remote servers.
Otherwise, remote queries may fail or behave unexpectedly.
fetch_size
This option specifies the number of rows postgres_fdw should get in each fetch operation.
It can be specified for a foreign table or a foreign server.
The option specified on a table overrides an option specified for the server.
The default is 100.
當然，這裏麵是存在一定的開發工作量的。
實現後，PG+外部存儲 能實現非常高效的時序數據寫入和檢索。
參考
最後更新：2017-07-03 21:32:19