445
技術社區[雲棲]
PostgreSQL 證券行業數據庫需求分析與應用
標簽
PostgreSQL , 證券 , 時序數據 , JSON , HSTORE , 數組 , range索引 , BRIN塊級索引 , 分時走勢 , 線性回歸 , MADlib , 機器學習
背景
證券行業產生的數據比較多，讀寫非常頻繁。
以股票交易為例，一共有幾千隻股票。一年大概有240個交易日，交易日大概是從早上10點到下午4點。
數據寫入需求:
實時的數據寫入，按查詢維度的實時數據合並（比如秒數據實時寫入。分鍾，幾分鍾，。。。則實時合並）。
數據分為不同粒度的分時數據。（精確到秒，分鍾，幾分鍾，小時，天，。。。等）
數據的維度:
每一隻股票都會包含（時間，訂單數，成交量，平均價格，最高價，最低價，開始價格，結束價格等10多個指標）。
數據庫存儲:
需要存儲每隻股票上市以來所有的數據，平均下來至少有十年的數據。
數據查詢需求:
查詢某個時間段內的數據，例如，查詢 2016年7月1日 內某個股票的分鍾數據。
分析類需求:
比如線性回歸分析。
針對這些需求，PostgreSQL提供了許多特性，可以更好的滿足這類需求。
1. BRIN塊級索引。因為數據是按時間順序插入的，所以時間字段與HEAP堆存儲有很強的線性相關性，使用BRIN塊級索引，可以指數級降低索引大小，同時提供極好的範圍查詢性能。
2. JSON , HSTORE , 數組類型。可以使用一條記錄，存儲一個範圍的數據，比如一條記錄存儲一天的分時數據。這麼做可以減少數據庫掃描的記錄數，從而加快範圍數據掃描的速度。
3. range類型與gist索引。配合第二條特性，使用範圍類型，可以表示這條記錄的起始時間。為什麼不用兩個字段來表示呢？因為range類型效率更高，可以參考如下文章。
4. 分析類需求，例如線性回歸，機器學習（MADlib）庫，多維分析，語言擴展(plpgsql,plpython,plr)等等。可以很好的支持證券行業對數據分析的需求。
設計
數值類型的選擇
PostgreSQL 的數值類型包括10種，列舉其中的3種可能和金融行業相關。
1. numeric (131072位.16383位)
2. float8 (15位有效數字)
numeric是變長類型，使用是會有palloc（內存拷貝），效率比float8低。
3. decimal128 擴展類型，在金融領域使用較多。(性能和數據表示的範圍都可以滿足需求)
《PostgreSQL decimal64 decimal128 高效率數值 類型擴展》
表結構設計
秒級數據表，要求插入快
(每隻股票每10年5184萬條記錄，所以不考慮分區)
create table tbl_sec_股票代碼 -- 每隻股票一張表，可以達到最高的查詢效率，弊端是表多，需要動態拚接表名，變更表結構時，需要調整較多的表（可以使用繼承來管理，減少管理複雜度）。
(
id serial8 primary key, -- 序列(可選字段)
時間 timestamp(0), -- 值的生成時間
指標1 numeric, -- 數據指標列
指標2 numeric,
...
指標10 numeric
);
create index idx_xx on tbl_sec_股票代碼 (時間);
或
create index idx_xx on tbl_sec_股票代碼 using brin (時間);
分鍾級數據表，要求按時間段查詢快（數據量較少，不需要分區）
create table tbl_min_股票代碼
(
id serial8 primary key, -- 序列(可選字段)
時間 timestamp(0), -- 值的生成時間
指標1 numeric, -- 數據指標列
指標2 numeric,
...
指標10 numeric
);
create index idx_xx on tbl_min_股票代碼 (時間);
或
create index idx_xx on tbl_min_股票代碼 using brin (時間);
BRIN索引，當範圍查詢需求較多時，brin索引可以大幅減少索引大小，同時提升插入性能。BRIN索引的例子如下。
《PostgreSQL 聚集存儲 與 BRIN索引 - 高並發行為、軌跡類大吞吐數據查詢場景解說》
《PostgreSQL 物聯網黑科技 - 瘦身幾百倍的索引(BRIN index)》
《PostgreSQL 9.5 new feature - BRIN (block range index) index》
通常客戶端會有數據緩存，所以查詢頻率並不是很高，對於範圍（大量數據）查詢頻率很高的場景，還可以有以下極致優化的方法。
按時間段，再次聚合。
更極致的優化方法(可選)
如果要經常進行範圍查詢，例如查詢一天的分鍾明細，如果用前麵的設計，需要範圍360條記錄。為了達到更好的查詢效率，可以使用聚合（例如按天聚合），那麼查詢1天的數據，隻會返回1條記錄。
分鍾級數據表，要求按時間段查詢快(一張實時聚合表，一張延時聚合表，減少垃圾)
create table tbl_min_股票代碼_實時聚合
(
id serial8 primary key, -- 序列(可選字段)
時間 timestamp(0), -- 表示當前記錄的寫入時間
指標1 numeric, -- 數據指標列
指標2 numeric,
...
指標10 numeric
);
create index idx_xx on tbl_min_股票代碼_實時聚合 (時間);
create table tbl_min_股票代碼_延時聚合
(
id serial8 primary key, -- 序列(可選字段)
時間區間 tsrange, -- 表示當前記錄的時間區間
指標 jsonb -- 數據指標列
);
create index idx_xx on tbl_min_股票代碼_延時聚合 using gist(時間區間);
指標可以使用jsonb\hstore\array表示，永恒可以自由選擇。以jsonb為例
{指標1: {時間點1:value, 時間點2:value, ......} , 指標2: {時間點1:value, 時間點2:value, ......}, ......}
tsrange支持gist索引，2017-01-01到2017-01-05時間段的數據，可以通過索引快速定位到記錄。（毫秒級），例子如下。
其他分時數據表設計，與分鍾級類似。
延遲聚合流程
聚合流程
秒表 ->(實時聚合) 實時聚合分鍾表 ->(延遲1天聚合) 延時聚合分鍾表
查詢流程
(查詢1 實時聚合分鍾表) union all (查詢2 延時聚合分鍾表)
業務邏輯設計
1. 插入
每隻股票的秒級數據，實時插入。
2. 實時合並
每隻股票的分組、日、。。。不同維度的數據，通過秒級數據，在到達時間點後，實時的合並到對應的實時聚合表。
例如2017-01-01 11:00:00 到點後，將2017-01-01 10:59:00 - 2017-01-01 10:59:59 的秒級數據，合並到分鍾級實時聚合表。
3. 延遲合並(可選)
延時合並，將實時合並的結果，按區間聚合為一條記錄。
例如，每隻股票每天有360條分鍾級記錄，將其合並為每天一條記錄，使用jsonb來表示。
合並的目的是減少範圍查詢的掃描記錄數，例如1天僅僅返回一條記錄。
4. 查詢
精確查詢，指定時間，查詢某一條記錄。
範圍查詢，指定時間範圍，查詢這個範圍內的記錄。
壓測
秒級數據插入壓測
創建測試基表
create table tbl_sec
(
crt_time timestamp(0),
c1 float8,
c2 float8,
c3 float8,
c4 float8,
c5 float8,
c6 float8,
c7 float8,
c8 float8,
c9 float8,
c10 float8
);
create index idx_tbl_sec_time on tbl_sec using brin (crt_time) with (pages_per_range=1);
創建3000隻股票表
do language plpgsql $$
declare
sql text;
begin
for i in 1..3000 loop
sql := format('create table %I (like tbl_sec including all)', 'tbl_sec_'||lpad(i::text, 6, '0') );
execute sql;
end loop;
end;
$$;
每隻股票的數據插入性能測試
vi test.sql
\set c1 random(1,1000)
\set c2 random(1,1000)
\set c3 random(1,1000)
\set c4 random(1,1000)
\set c5 random(1,1000)
\set c6 random(1,1000)
\set c7 random(1,1000)
\set c8 random(1,1000)
\set c9 random(1,1000)
\set c10 random(1,1000)
insert into tbl_sec_000001 values (now(),:c1,:c2,:c3,:c4,:c5,:c6,:c7,:c8,:c9,:c10);
插入延遲0.043毫秒
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1 -T 10
tps = 21714.908797 (including connections establishing)
tps = 21719.144013 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.000 \set c1 random(1,1000)
0.000 \set c2 random(1,1000)
0.000 \set c3 random(1,1000)
0.000 \set c4 random(1,1000)
0.000 \set c5 random(1,1000)
0.000 \set c6 random(1,1000)
0.000 \set c7 random(1,1000)
0.000 \set c8 random(1,1000)
0.000 \set c9 random(1,1000)
0.000 \set c10 random(1,1000)
0.043 insert into tbl_sec_000001 values (now(),:c1,:c2,:c3,:c4,:c5,:c6,:c7,:c8,:c9,:c10);
postgres=# select * from tbl_sec_000001 limit 10;
crt_time | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
---------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
2017-04-17 14:14:00 | 480 | 60 | 918 | 563 | 168 | 457 | 129 | 887 | 870 | 457
2017-04-17 14:14:00 | 189 | 894 | 707 | 598 | 701 | 418 | 191 | 287 | 688 | 668
2017-04-17 14:14:00 | 492 | 423 | 972 | 101 | 28 | 847 | 919 | 698 | 594 | 430
2017-04-17 14:14:00 | 781 | 38 | 816 | 467 | 96 | 2 | 762 | 8 | 271 | 577
2017-04-17 14:14:00 | 225 | 126 | 828 | 158 | 447 | 12 | 691 | 693 | 272 | 995
2017-04-17 14:14:00 | 125 | 18 | 589 | 472 | 424 | 884 | 177 | 754 | 463 | 468
2017-04-17 14:14:00 | 156 | 412 | 784 | 40 | 126 | 100 | 727 | 851 | 80 | 513
2017-04-17 14:14:00 | 320 | 75 | 485 | 10 | 481 | 592 | 594 | 227 | 658 | 810
2017-04-17 14:14:00 | 678 | 199 | 155 | 325 | 212 | 977 | 170 | 696 | 895 | 679
2017-04-17 14:14:00 | 413 | 512 | 535 | 319 | 99 | 520 | 39 | 502 | 207 | 160
(10 rows)
秒級數據範圍查詢性能
單隻股票，插入1000萬條秒級數據。
postgres=# insert into tbl_sec_000001 select now()+(i||' sec')::interval , 1,1,1,1,1,1,1,1,1,1 from generate_series(1,10000000) t(i);
INSERT 0 10000000
postgres=# select * from tbl_sec_000001 limit 10;
crt_time | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
---------------------+----+----+----+----+----+----+----+----+----+-----
2017-04-17 14:20:17 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:18 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:19 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:20 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:21 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:22 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:23 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:24 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:25 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
2017-04-17 14:20:26 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
(10 rows)
索引大小和表的大小，表1116MB，索引4808kB。
BRIN索引節約了非常多的空間。
public | tbl_sec_000001 | table | postgres | 1116 MB |
public | tbl_sec_000001_crt_time_idx | index | postgres | tbl_sec_000001 | 4808 kB |
查詢某一分鍾(60條)、某2個小時(7200條)的數據，約耗時47毫秒。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sec_000001 where crt_time between '2017-04-17 14:21:00' and '2017-04-17 14:21:59';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_sec_000001 (cost=777.40..778.72 rows=1 width=88) (actual time=46.612..46.628 rows=60 loops=1)
Output: crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Recheck Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 14:21:59'::timestamp without time zone))
Rows Removed by Index Recheck: 80
Heap Blocks: lossy=2
Buffers: shared hit=809
-> Bitmap Index Scan on idx_tbl_sec_000001_time (cost=0.00..777.40 rows=1 width=0) (actual time=46.597..46.597 rows=20 loops=1)
Index Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 14:21:59'::timestamp without time zone))
Buffers: shared hit=807
Planning time: 0.077 ms
Execution time: 46.664 ms
(11 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sec_000001 where crt_time between '2017-04-17 14:21:00' and '2017-04-17 16:20:59';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_sec_000001 (cost=834.57..7710.56 rows=5578 width=88) (actual time=46.194..47.437 rows=7200 loops=1)
Output: crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Recheck Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 16:20:59'::timestamp without time zone))
Rows Removed by Index Recheck: 80
Heap Blocks: lossy=104
Buffers: shared hit=911
-> Bitmap Index Scan on idx_tbl_sec_000001_time (cost=0.00..833.18 rows=5578 width=0) (actual time=46.182..46.182 rows=1040 loops=1)
Index Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 16:20:59'::timestamp without time zone))
Buffers: shared hit=807
Planning time: 0.060 ms
Execution time: 47.862 ms
(11 rows)
B-Tree vs BRIN 索引空間占用、效率
BRIN索引是塊級索引，所以占用空間非常小，非常適合字段值與HEAP表存儲物理順序相關性很好的場景。
但是由於BRIN並非精確索引，因此查詢效率可能不如B-Tree。
用戶可以根據實際的業務需求，使用不同的索引方法。
對比如下：
postgres=# drop index tbl_sec_000001_crt_time_idx;
cDROP INDEX
postgres=# create index tbl_sec_000001_crt_time_idx on tbl_sec_000001(crt_time);
CREATE INDEX
public | tbl_sec_000001_crt_time_idx | index | postgres | tbl_sec_000001 | 214 MB |
1. 空間占用
| 記錄數 | 表 | b-tree | brin |
|---|---|---|---|
| 1000萬 | 1116MB | 214MB | 4.8MB |
2. 效率
| 返回記錄數 | b-tree | brin |
|---|---|---|
| 60條 | 0.04毫秒 | 46.7毫秒 |
| 7200條 | 1.96毫秒 | 47.9毫秒 |
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sec_000001 where crt_time between '2017-04-17 14:21:00' and '2017-04-17 14:21:59';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tbl_sec_000001_crt_time_idx on public.tbl_sec_000001 (cost=0.43..4.11 rows=54 width=88) (actual time=0.007..0.022 rows=60 loops=1)
Output: crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Index Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 14:21:59'::timestamp without time zone))
Buffers: shared hit=5
Planning time: 0.095 ms
Execution time: 0.040 ms
(6 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sec_000001 where crt_time between '2017-04-17 14:21:00' and '2017-04-17 16:20:59';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tbl_sec_000001_crt_time_idx on public.tbl_sec_000001 (cost=0.43..252.61 rows=6609 width=88) (actual time=0.022..1.539 rows=7200 loops=1)
Output: crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Index Cond: ((tbl_sec_000001.crt_time >= '2017-04-17 14:21:00'::timestamp without time zone) AND (tbl_sec_000001.crt_time <= '2017-04-17 16:20:59'::timestamp without time zone))
Buffers: shared hit=126
Planning time: 0.119 ms
Execution time: 1.957 ms
(6 rows)
分鍾級數據，查詢壓測
與秒級數據的查詢需求類似，忽略測試。
分析類需求
對於證券場景，除了常見的數據查詢、插入、合並的需求。更多的是數據分析的需求。
PostgreSQL提供了非常多的分析功能
1. 聚合
《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》
《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》
2. 窗口查詢
https://www.postgresql.org/docs/9.6/static/sql-expressions.html
https://www.postgresql.org/docs/9.6/static/functions-window.html
3. 多維分析
《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》
4. MADlib(機器學習庫)
數據分析例子
線性回歸數據預測
《在PostgreSQL中用線性回歸分析(linear regression) - 實現數據預測》
《在PostgreSQL中用線性回歸分析linear regression做預測 - 例子2, 預測未來數日某股收盤價》
《PostgreSQL 多元線性回歸 - 1 MADLib Installed in PostgreSQL 9.2》
其他高級特性
PostgreSQL還具備哪些特性，可以為證券、金融行業所用：
除了前麵提到的一些在線事務處理型數據庫的基本功能，PostgreSQL還具備很強的分析能力，例如流式處理的能力，大量數據的高效處理能力（列存儲、CPU多核並行計算、JIT、CPU向量計算指令的使用、時序數據插件等。）
流計算可以幫助證券行業，實時的統計各個維度的數據，設置探針（例如當數據到達什麼條件時），觸發事件。對交易事件非常有用。
分析能力方麵，PostgreSQL結合了目前的CPU多核能力，CPU的向量計算能力，使得分析性能相比傳統數據庫有指數級的提升。
《分析加速引擎黑科技 - LLVM、列存、多核並行、算子複用 大聯姻 - 一起來開啟PostgreSQL的百寶箱》
《PostgreSQL 向量化執行插件(瓦片式實現) 10x提速OLAP》
PostgreSQL也有相應的插件可以高效的處理時序數據。
《時序數據庫有哪些特點? TimescaleDB時序數據庫介紹》
最後更新：2017-04-17 16:00:45