Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL Time-Series Best Practices: Stock Exchange System Database

PostgreSQL Time-Series Best Practices: Stock Exchange System Database

In this article, we'll design a stock exchange system database using Alibaba Cloud's ApsaraDB for RDS PostgreSQL.

Background

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

1

Take stock trading as an example, there are thousands of stocks. There are about 240 trading days in a year, with trading hours ranging from 10:00 to 16:00.

1.  Data writing requirements:

Real-time data writing, and real-time merging by query dimension (For example, second-level data is written in real time. Minute-level data is merged in real time .)

Data is divided into time-sharing data of different granularity. (The data is exact to seconds, minutes, hours, days )

2

2.  Dimensions of data:

Each stock contains more than 10 indicators, such as time, number of orders, volume, average price, highest price, lowest price, starting price, and ending price.

3.  Database storage:

All the data needs to be stored from the listing of each stock onward, with an average of at least ten years of data.

4.  Data query requirements:

Query the data within a certain period of time; for example, query the minute-level data of a stock on July 1, 2016.

5.  Analysis requirements:

For example, linear regression analysis.

PostgreSQL provides many features to better meet these requirements.

1.  BRIN block-level indexes. The data is inserted in time order, so the time field has a strong linear correlation with HEAP storage. Using BRIN block-level indexes can reduce index size exponentially while providing excellent range query performance.

2.  JSON, HSTORE, and array type. You can use a record to store a range of data, such as a record to store time-sharing data for a day. This way, you can reduce the number of records scanned by the database, and this speeds up the scan of the range data.

3.  Range type and gist index. With the second feature, the range type can be used to indicate the start time for this record. Why not use two fields to represent it? Because the range type is more efficient, and you can refer to the following article.
Talk About the Problem of BETWEEN AND and the Solution

4.  Analysis requirements, such as linear regression, machine learning (MADlib) library, multidimensional analysis, and language extension (plpgsql,plpython,plr). The data analysis needs of the securities industry can be well supported.

OLTP Requirement Solution Design 1

Selection of Numeric Types

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

1.  numeric (131,072 digits before the decimal point and 16,383 digits after the decimal point)

2.  float8 (15 valid digits)
Numeric is a variable length type and has a palloc (memory transfer) when used, which is less efficient than float8.

3.  decimal128 extension type, which is more frequently used in the financial field. (Both performance and the range of data representation can meet the requirements)
PostgreSQL decimal64 decimal128 Efficient Numerical Type Extension

Table Structure Design

Second-level data tables, requiring fast insertion

(Each stock has 5,184 million records every 10 years, so partitioning is not considered)

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 (时间);

Minute-level data tables, requiring fast query by time period (less data, no partition required)

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 (时间);

The BRIN index. When range query requirements are high, the BRIN index can significantly reduce the index size while improving insert performance. Examples of BRIN indexes are as follows:

PostgreSQL Clustered Storage and BRIN Index - Explanations on High Concurrency Behavior and Track Type Large Throughput Data Query Scenario

The Cutting Edge Technology of PostgreSQL for IoT - Creating an Index (BRIN Index) a Fraction of the Normal Size

PostgreSQL 9.5 New Feature - BRIN (Block Range Index) Index

The client usually has data cache, so the query frequency is not very high. For scenarios where the range (massive data) query frequency is very high, the following optimal methods can be used:

Aggregate again by time period.

More extreme optimization method (optional)

If you want to perform range queries frequently (for example, querying the minute-level details of a day) and use the previous design, you need a range of 360 records. For better query efficiency, you can use aggregation (for example, aggregation by day), then only one record is returned when querying data for one day.

Minute-level data tables, requiring fast query by time period (a real-time aggregation table and a delayed aggregation table, reducing garbage)

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(时间区间);

Indicators can be represented by jsonbhstorearray, and they can always be freely chosen. Take jsonb as an example:

{指标1: {时间点1:value, 时间点2:value, ......} , 指标2: {时间点1:value, 时间点2:value, ......}, ......}

tsrange supports the gist index. Data from 2017-01-01 to 2017-01-05 can be quickly (in milliseconds) located to the record through the index. The example is as follows:

Talk About the Problem of BETWEEN AND and the Solution

Other time-sharing data table designs are similar to the minute-level design.

Delayed aggregation process

Aggregation process

Second-level table -> (real-time aggregation) real-time aggregation minute-level table -> (One day delay for aggregation) delayed aggregation minute-level table

Query process

(Query 1 real-time aggregation minute-level table) union all (Query 2 delayed aggregation minute-level table)

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.

10.0 currently supports range and list partitions, but hash partitions have not been merged into the master branch yet.

https://www.postgresql.org/docs/devel/static/sql-createtable.html

However, pg_pathman already supports hash partitions and users can choose freely.

https://github.com/postgrespro/pg_pathman

PostgreSQL 9.6 sharding based on FDW & pg_pathman

PostgreSQL 9.5+ Efficient Partitioned Table Implementation – pg_pathman

Business Logic Design

1.  Insert

The second-level data of each stock is inserted in real time.

2.  Real-time merging

Data of different dimensions, such as grouping data and daily data, of each stock is merged into the corresponding real-time aggregation table in real time after reaching the time point through the second-level data.

For example, after reaching the point of 2017-01-01 11:00:00, the second-level data of 2017-01-01 10:59:00 to 2017-01-01 10:59:59 is merged into the minute-level real-time aggregation table.

3.  Delayed merging (optional)

Delayed merging, which aggregates the results of real-time merging into one record by interval.

For example, each stock has 360 minute-level records per day, which are merged into one record per day and represented by jsonb.

The purpose of merging is to reduce the number of scanned records for range queries, such as returning only one record per day.

4.  Query

Precise query, which specifies the time to query a record.

Range query, which specifies the time range to query the records within this range.

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

Create 3,000 stock tables

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;  

$$
;

Data insertion performance test for each stock

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

Insertion delay is 0.043 milliseconds

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)

The index size and table size are 4,808 MB and 1,116 KB, respectively.

The BRIN index saves a lot of space.

 public | tbl_sec_000001   | table | postgres | 1116 MB    |   
  
  
 public | tbl_sec_000001_crt_time_idx       | index | postgres | tbl_sec_000001   | 4808 kB    |

It takes about 47 milliseconds to query data for 1 minute (60 pieces) and 2 hours (7,200 pieces).

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.

However, BRIN is not a precise index, so the query efficiency may not be as good as B-Tree.

Users can use different indexing methods according to actual business requirements.

The comparison is as follows:

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.  Comparison of space occupancy

Number of records Table B-Tree BRIN
10 million 1116 MB 214 MB 4.8 MB

2.  Comparison of query efficiency

Number of returned records B-Tree BRIN
60 pieces 0.04 milliseconds 46.7 milliseconds
7,200 pieces 1.96 milliseconds 47.9 milliseconds 
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.

The market trading time is usually 4 hours a day and the trading market is not open on holidays. If a stock creates one record per second, there will be 4 60 60 52 5 = 3.744 million records in one day, and only 37.44 million records in 10 years.

So if you partition by stock, the time dimension is completely unnecessary.

1.  The base table is as follows:

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.  Data estimation

-- 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.  Create 3,000 stock tables

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.  Create a data insertion function to encapsulate the table name inside 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.  The stress testing shows that the writing speed is about 220,000/s, which fully meets the business requirements.

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.  Query performance

We do not recommend using the PG partitioned table to query directly, and the run and planning time is slightly longer.

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)

Querying partitions directly is very fast.

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)

The query code is encapsulated, and the table name is automatically spliced for query according to the stock code entered.

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;

The performance of using UDF to query data is very good, and takes 0.298 milliseconds.

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)

Usually, users have the demand for optional stocks, so they may need to enter data for more than one stock at a time.

select * from tbl where gid in ('000001','002999','001888') and crt_time between ? and ?

The UDF can also be used to meet users' query requirements.

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;

The performance is also excellent, and takes 0.662 milliseconds.

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.  Ten million records are written for each stock, a total of 30 billion pieces of test data.

postgres=# select count(*) from tbl_000001;
  count   
----------
 10135995
(1 row)

The test queries the records of a stock in a certain period of time, and the response time is within 1 ms.

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.  Implement schemaless solution through to the end - PostgreSQL Schemaless Implementation (Class Mongodb Collection)

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.

Using the BRIN index, the problem with a large BTREE index can be solved (but you can still continue to use BTREE index).

3

4

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.

PostgreSQL provides a lot of analysis functions:

1.  Aggregation

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.  Window query

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.  Multidimensional analysis

PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.

4.  MADlib (machine learning library)

See What MADlib can Do Through a Picture

Data Analysis Examples

Prediction of linear regression data

Using Linear Regression Analysis in PostgreSQL - Implementing Data Prediction

PostgreSQL Linear Regression - Stock Price Forecast 1

Using Linear Regression Analysis in PostgreSQL to Make Predictions - Example 2 to Predict the Closing Price of a Stock in the Next Few Days

PostgreSQL Multiple Linear Regression - 1 MADLib Installed in PostgreSQL 9.2

PostgreSQL Multiple Linear Regression - Stock Forecast 2

Other Advanced Features

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

In addition to some of the basic functions of the online transaction database mentioned earlier, PostgreSQL also has strong analysis capabilities, such as the ability to stream data. It can also efficiently process large amounts of data (including column-store, CPU multi-core parallel computing, JIT, use of CPU vector computing instructions, and time-series data plug-ins).

StreamCompute can help the securities industry to count data in various dimensions in real time, set agents (for example, when data reaches certain conditions), and trigger events. It is very useful for transaction events.

PostgreSQL StreamCompute Application

In terms of analysis capability, PostgreSQL combines the current CPU multi-core capability and the CPU vector computing capability, giving analysis performance an exponential improvement over the traditional database.

Secrets to Analysis Acceleration Engines - LLVM, Column-Store, Multi-Core Parallel, Operator Multiplex Integrate - Open the Treasure Chest of PostgreSQL

Accelerating OLAP by 10 Times Through PostgreSQL Vectorized Execution Extension (Tiled Implementation)

PostgreSQL also has corresponding plug-ins that can efficiently process time-series data.

What are the Characteristics of the Time-Series Database? Introduction to TimescaleDB Time-Series Database

Summary

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

We have achieved a query response time of about 0.04 ms for any stock in any time period by using this solution, and a response speed of about 0.2 ms by using schemaless UDF encapsulation.

With these performance indicators, do you still need a time-series database? PostgreSQL itself is a top-level time-series database.

2.  The writing speed of stock data is about 220,000 lines/s. This far exceeds the business requirements.

3.  Analysis requirements. The JIT of PostgreSQL and multi-core parallel computing provide strong computing power support for AP business. Many users are already using PG parallel computing to solve the mixed requirements of OLTP+OLAP.

4.  For I/O scaling-up, this can also be solved by using a method similar to the aggregation table (index-only scan), which does not impact the business. Please see:
PostgreSQL IoT Internet of Vehicles (IoV) - Real-Time Trajectory - Travel Practice 2

PostgreSQL Database ApsaraDB for RDS JSON Time-Series Data
1 0 0
Share on

Read previous post:

PostgreSQL Time-Series Database Plug-in TimescaleDB Deployment Practices

Read next post:

PostgreSQL Business Data Quality Real-time Monitoring Practices

digoal

282 posts | 25 followers

You may also like

Comments

5295094317782498 July 9, 2020 at 6:03 pm

Thanks for the great article!What postgres configuration was used and on what hardware did it run?

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All