PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践...

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类型效率更高，可以参考如下文章。

《聊聊between and的坑 和 神奇的解法》

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时间段的数据，可以通过索引快速定位到记录。（毫秒级），例子如下。

《聊聊between and的坑 和 神奇的解法》

其他分时数据表设计，与分钟级类似。

延迟聚合流程

聚合流程

秒表 ->(实时聚合) 实时聚合分钟表 ->(延迟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_'