PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践
背景
证券行业产生的数据比较多，读写非常频繁。
以股票交易为例，一共有几千只股票。一年大概有240个交易日，交易日大概是从早上10点到下午4点。
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_'||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万条秒级数据。3000只股票约300亿测试数据。
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)
OLTP类需求方案设计 2
schemaless方案
每只股票一张表，后缀为股票代码，可以在应用程序端拼接表名，也可以在数据库中使用FUNCTION封装拼接代码。
交易市场通常每天4小时，节假日休息，对于一只股票来说，如果一秒一条记录的话，4*60*60*52*5=374.4万，10年才3744万条记录。
所以如果按股票分区，时间维度完全没有必要分区。
1、基表如下：
create table tbl
(
gid text,
crt_time timestamp,
c1 float8,
c2 float8,
c3 float8,
c4 float8,
c5 float8,
c6 float8,
c7 float8,
c8 float8,
c9 float8,
c10 float8
) PARTITION BY list (gid)
;
-- create index idx_tbl_sec_time on tbl_sec using btree (crt_time);
2、数据预估
-- CREATE TABLE tbl_000000 PARTITION OF tbl FOR VALUES IN ('000000') PARTITION BY RANGE (crt_time);
-- 4*60*60*52*5=374.4万，10年才3744万条记录。
3、创建3000只股票表
do language plpgsql $$
declare
sql text;
begin
for i in 1..3000 loop
sql := format('create table %I PARTITION OF tbl for values in (%L)', 'tbl_'||lpad(i::text, 6, '0'), lpad(i::text, 6, '0'));
execute sql;
sql := format('create index %I on %I (crt_time)', 'idx_tbl_'||lpad(i::text, 6, '0')||'_1', 'tbl_'||lpad(i::text, 6, '0'));
execute sql;
end loop;
end;
$$;
4、创建数据插入函数，在PG内部封装表名
create or replace function ins_tbl(
i_gid text,
i_crt_time timestamp,
i_c1 float8,
i_c2 float8,
i_c3 float8,
i_c4 float8,
i_c5 float8,
i_c6 float8,
i_c7 float8,
i_c8 float8,
i_c9 float8,
i_c10 float8
) returns void as $$
declare
begin
execute format('insert into %I values (%L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L)', 'tbl_'||i_gid, i_gid, i_crt_time, i_c1, i_c2, i_c3, i_c4, i_c5, i_c6, i_c7, i_c8, i_c9, i_c10);
end;
$$ language plpgsql strict;
5、压测，写入速度约22万/s，完全满足业务需求。
vi test.sql
\set id random(1,3000)
select ins_tbl(lpad(:id, 6, '0'), now()::timestamp, 1::float8,1::float8,1::float8,1::float8,1::float8,1::float8,1::float8,1::float8,1::float8,1::float8);
nohup pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 15000 > /dev/null 2>&1 &
6、查询性能
暂时不建议使用PG的分区表直接查询，执行计划略久。
postgres=# explain (analyze) select * from tbl where gid='000001' and crt_time between '2017-07-17 15:17:42.336503' and '2017-07-17 15:38:08.981317';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..20.47 rows=286 width=95) (actual time=0.017..0.033 rows=30 loops=1)
-> Index Scan using idx_tbl_000001_1 on tbl_000001 (cost=0.29..20.47 rows=286 width=95) (actual time=0.016..0.030 rows=30 loops=1)
Index Cond: ((crt_time >= '2017-07-17 15:17:42.336503'::timestamp without time zone) AND (crt_time <= '2017-07-17 15:38:08.981317'::timestamp without time zone))
Filter: (gid = '000001'::text)
Planning time: 141.484 ms
Execution time: 0.116 ms
(6 rows)
直接查询分区是很快的
postgres=# explain (analyze) select * from tbl_000001 where gid='000001' and crt_time between '2017-07-17 15:17:42.336503' and '2017-07-17 15:38:08.981317';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tbl_000001_1 on tbl_000001 (cost=0.29..21.60 rows=290 width=95) (actual time=0.009..0.016 rows=30 loops=1)
Index Cond: ((crt_time >= '2017-07-17 15:17:42.336503'::timestamp without time zone) AND (crt_time <= '2017-07-17 15:38:08.981317'::timestamp without time zone))
Filter: (gid = '000001'::text)
Planning time: 0.199 ms
Execution time: 0.036 ms
(5 rows)
封装查询代码，根据输入的股票代码，自动拼接表名进行查询
create or replace function sel_tbl(
i_gid text,
begin_crt_time timestamp,
end_crt_time timestamp
) returns setof tbl as $$
declare
begin
return query execute format('select * from %I where crt_time between %L and %L', 'tbl_'||i_gid, begin_crt_time, end_crt_time);
end;
$$ language plpgsql strict;
使用UDF查询数据，性能很好，0.298毫秒。
postgres=# select * from sel_tbl('000001', '2017-07-17 15:17:42.336503', '2017-07-17 15:38:08.981317');
gid | crt_time | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
--------+----------------------------+----+----+----+----+----+----+----+----+----+-----
000001 | 2017-07-17 15:17:42.336503 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:47.083672 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:53.633412 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:54.092175 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:55.452835 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:55.55255 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:59.689178 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:04.051391 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:11.255866 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:12.217447 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:12.456304 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:19.640116 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:22.022434 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:27.141344 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:33.709304 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:34.285168 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:52.501981 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:21.891636 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:36.091745 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:37.481345 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:37:43.894333 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:37:44.921234 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:37:45.317703 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.799772 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.897194 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.938029 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.953457 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.954542 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.959182 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.981317 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
(30 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from sel_tbl('000001', '2017-07-17 15:17:42.336503', '2017-07-17 15:38:08.981317');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on postgres.sel_tbl (cost=0.25..10.25 rows=1000 width=120) (actual time=0.277..0.279 rows=30 loops=1)
Output: gid, crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Function Call: sel_tbl('000001'::text, '2017-07-17 15:17:42.336503'::timestamp without time zone, '2017-07-17 15:38:08.981317'::timestamp without time zone)
Buffers: shared hit=9
Planning time: 0.030 ms
Execution time: 0.298 ms
(6 rows)
通常用户都有自选股的需求，那么可能需要一次输入多只股票的数据
select * from tbl where gid in ('000001','002999','001888') and crt_time between ? and ?
同样可以使用UDF满足用户的查询需求。
create or replace function sel_tbl(
i_gid text[],
begin_crt_time timestamp,
end_crt_time timestamp
) returns setof tbl as $$
declare
v_gid text;
begin
foreach v_gid in array i_gid
loop
return query execute format('select * from %I where crt_time between %L and %L', 'tbl_'||v_gid, begin_crt_time, end_crt_time);
end loop;
end;
$$ language plpgsql strict;
性能一样是超棒的, 0.662毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from sel_tbl(array['000001','002999','001888'], '2017-07-17 15:17:42.336503', '2017-07-17 15:38:08.981317');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on postgres.sel_tbl (cost=0.25..10.25 rows=1000 width=120) (actual time=0.632..0.638 rows=86 loops=1)
Output: gid, crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Function Call: sel_tbl('{000001,002999,001888}'::text[], '2017-07-17 15:17:42.336503'::timestamp without time zone, '2017-07-17 15:38:08.981317'::timestamp without time zone)
Buffers: shared hit=30
Planning time: 0.048 ms
Execution time: 0.662 ms
(6 rows)
postgres=# select * from sel_tbl(array['000001','002999','001888'], '2017-07-17 15:17:42.336503', '2017-07-17 15:38:08.981317');
gid | crt_time | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
--------+----------------------------+----+----+----+----+----+----+----+----+----+-----
000001 | 2017-07-17 15:17:42.336503 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:47.083672 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:53.633412 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:54.092175 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:55.452835 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:55.55255 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:17:59.689178 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:04.051391 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:11.255866 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:12.217447 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:12.456304 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:19.640116 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:22.022434 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:27.141344 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:33.709304 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:34.285168 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:18:52.501981 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:21.891636 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:36.091745 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:19:37.481345 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
000001 | 2017-07-17 15:37:43.894333 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:37:44.921234 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:37:45.317703 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.799772 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.897194 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.938029 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.953457 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.954542 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.959182 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
000001 | 2017-07-17 15:38:08.981317 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:18:04.116816 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:08.720714 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:11.021059 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:13.17118 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:19.349304 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:20.525734 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:38.480529 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:42.462302 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:18:42.81403 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:19:11.211989 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:19:14.861736 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:19:20.240403 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:19:32.747798 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:19:35.191558 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:29:58.143158 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
002999 | 2017-07-17 15:38:08.800312 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.801949 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.824119 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.835612 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.860339 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.918502 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.9365 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.944578 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.951397 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.963564 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.980547 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
002999 | 2017-07-17 15:38:08.980656 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:17:42.353113 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:17:43.15402 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:17:46.316366 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:17:51.982603 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:07.32869 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:16.798675 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:36.947117 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:39.629393 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:42.56243 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:48.777822 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:50.850458 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:51.693084 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:18:55.660418 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:19:07.735869 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:19:32.331744 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:19:34.409026 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:29:56.634906 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
001888 | 2017-07-17 15:38:08.749017 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.801824 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.829437 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.855895 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.857959 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.858431 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.882241 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.930556 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.938661 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.942828 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.9459 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
001888 | 2017-07-17 15:38:08.966001 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
(86 rows)
7、每只股票写入1000万记录，总共300亿测试数据。
postgres=# select count(*) from tbl_000001;
count
----------
10135995
(1 row)
测试查询某只股票，某个时间段的记录，响应时间在1毫秒以内。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from sel_tbl('000001', '2017-07-17 15:17:42.336503', '2017-07-17 15:38:08.981317');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on postgres.sel_tbl (cost=0.25..10.25 rows=1000 width=120) (actual time=0.303..0.305 rows=30 loops=1)
Output: gid, crt_time, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
Function Call: sel_tbl('000001'::text, '2017-07-17 15:17:42.336503'::timestamp without time zone, '2017-07-17 15:38:08.981317'::timestamp without time zone)
Buffers: shared hit=12
Planning time: 0.040 ms
Execution time: 0.328 ms
(6 rows)
8、将schemaless进行到底，《PostgreSQL schemaless 的实现(类mongodb collection)》
分钟级数据，查询压测
与秒级数据的查询需求类似，忽略测试。
OLAP类需求方案设计
对于证券场景，除了常见的数据查询、插入、合并的需求。更多的是数据分析的需求。
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#SYNTAX-WINDOW-FUNCTIONS
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时序数据库介绍》
小结
1、以十年的股票数据来计算，约300亿数据。
采用本方案，我们做到了任意股票，任意时间段的查询响应时间0.04毫秒左右，采用schemaless的UDF封装，响应速度也能达到0.2毫秒左右。
这样的性能指标，还要时序数据库干什么？PostgreSQL就是一个顶尖的时序数据库。
2、股票数据的写入速度约22万行/s。远超业务预期需求。
3、分析类需求，PostgreSQL 的JIT，多核并行计算，给AP业务提供了强大的计算能力支撑。已经有很多用户在使用PG的并行计算解决OLTP+OLAP的混合需求了。