PostgreSQL , 证券 , 时序数据 , JSON , HSTORE , 数组 , range索引 , BRIN块级索引 , 分时走势 , 线性回归 , MADlib , 机器学习
1、数据写入需求:
实时的数据写入,按查询维度的实时数据合并(比如秒数据实时写入。分钟,几分钟,。。。则实时合并)。
数据分为不同粒度的分时数据。(精确到秒,分钟,几分钟,小时,天,。。。等)
2、数据的维度:
每一只股票都会包含(时间,订单数,成交量,平均价格,最高价,最低价,开始价格,结束价格等10多个指标)。
3、数据库存储:
需要存储每只股票上市以来所有的数据,平均下来至少有十年的数据。
4、数据查询需求:
查询某个时间段内的数据,例如,查询 2016年7月1日 内某个股票的分钟数据。
5、分析类需求:
比如线性回归分析。
针对这些需求,PostgreSQL提供了许多特性,可以更好的满足这类需求。
1. BRIN块级索引。因为数据是按时间顺序插入的,所以时间字段与HEAP堆存储有很强的线性相关性,使用BRIN块级索引,可以指数级降低索引大小,同时提供极好的范围查询性能。
2. JSON , HSTORE , 数组类型。可以使用一条记录,存储一个范围的数据,比如一条记录存储一天的分时数据。这么做可以减少数据库扫描的记录数,从而加快范围数据扫描的速度。
3. range类型与gist索引。配合第二条特性,使用范围类型,可以表示这条记录的起始时间。为什么不用两个字段来表示呢?因为range类型效率更高,可以参考如下文章。
4. 分析类需求,例如线性回归,机器学习(MADlib)库,多维分析,语言扩展(plpgsql,plpython,plr)等等。可以很好的支持证券行业对数据分析的需求。
OLTP类需求方案设计 1
数值类型的选择
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 延时聚合分钟表)
表分区建议
如果业务层面不想每只股票一张表的话,也可以使用PostgreSQL的分区表功能,股票ID作为分区字段,使用hash分区。
10.0目前已经支持range,list分区,hash分区还没有合并到master分支。
https://www.postgresql.org/docs/devel/static/sql-createtable.html
但是pg_pathman已经支持了hash分区,用户可以自由选择。
https://github.com/postgrespro/pg_pathman
《PostgreSQL 9.6 sharding based on FDW & pg_pathman》
《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》
业务逻辑设计
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_'