PostgreSQL Time-Series Best Practices: Stock Exchange System Database

Background

The securities industry produces more data, and reads and writes frequently.

OLTP Requirement Solution Design 1

Selection of Numeric Types

PostgreSQL has 10 numerical types, three of which may be related to the financial industry.

Table Structure Design

Second-level data tables, requiring fast insertion

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 (时间);
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(时间区间);
{指标1: {时间点1:value, 时间点2:value, ......} , 指标2: {时间点1:value, 时间点2:value, ......}, ......}

Table Partitioning Recommendations

If you do not want to have a table for each stock at the business level, you can also use the PostgreSQL partitioned table feature, taking the stock ID as the partition field and using hash partitions.

Business Logic Design

1. Insert

Stress Testing

Second-Level Data Insertion Stress Testing

Create a test base table

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);
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);
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)

Second-Level Timestamp Range Query Performance

For a single stock, insert 10 million pieces of second-level data. Therefore, 3,000 stocks have about 30 billion pieces of test data.

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)
public | tbl_sec_000001   | table | postgres | 1116 MB    |   


public | tbl_sec_000001_crt_time_idx | index | postgres | tbl_sec_000001 | 4808 kB |
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)

Comparison between B-Tree and Brin Index in Space Occupancy and Efficiency

The BRIN index is a block-level index, so not much space is occupied and it is very suitable for scenarios where field values have a good correlation with the physical order of HEAP table storage.

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 |
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 Requirement Solution Design 2

Schemaless Solutions

Each stock has a table with a suffix of the stock code. You can splice the table names at the application side or use FUNCTION to encapsulate the spliced code in the database.

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);
-- 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万条记录。
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;
$$
;
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;
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 &
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;
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 ?
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;
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)
postgres=# select count(*) from tbl_000001;
count
----------
10135995
(1 row)
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)

Summary of Schemaless Solution Advantages

The schemaless solution solves the problem that the HEAP PAGE I/O scales up (each sensor is active, so checking several records of a sensor ID actually requires scanning as many HEAP PAGEs as returning records). Using the schemaless solution, the data of the sensor ID is redistributed and stored, which directly solves the problem that the I/O scales up.

Minute-Level Data Query Stress Testing

This is similar to the query requirement for second-level data, so the test is ignored.

OLAP Requirement Solution Design 1

For securities scenarios, in addition to the common requirements for data query, insertion, and merging, there are more stringent requirements for data analysis.

Data Analysis Examples

Prediction of linear regression data

Other Advanced Features

Other features provided by PostgreSQL that can be used by the securities and financial industries:

Summary

1. When calculated using ten years of stock data, there are about 30 billion pieces of data.

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store