PolarDB-based TSDB: Stock trading data analysis - PolarDB

This topic describes how to use the PolarDB-based GanosBase time series database (TSDB) to simulate stock trading data and execute time series queries. This solution ensures that the stock trading system can accurately track price fluctuations, compute technical indicators in real time, and use high-frequency trading (HFT) algorithms for historical pattern recognition and real-time decision-making.

Background information

In stock trading scenarios, an enormous amount of high-frequency time series data is generated every second, including tick-by-tick quotes, minute-level trading volumes, and order flows. The database must ensure millisecond-level, real-time writing and persistent storage of the data while supporting high-concurrency queries, enabling fast retrieval of data within specific time ranges, and providing robust support for complex time series analysis.

The PolarDB-based GanosBase TSDB optimizes the storage structure for timestamps, and uses highly efficient data compression algorithms, and can capture and store hundreds of thousands of market data entries per second in real time. This ensures that the trading system can accurately track price fluctuations and compute technical indicators such as moving averages and relative strength index (RSI) in real time. The GanosBase TSDB can also use high-frequency trading algorithms for historical pattern matching and real-time decision-making. In addition, the time window aggregation feature of the GanosBase TSDB allows you to quickly generate minute- or hour-level K-line charts to meet the varying time granularity requirements of different trading strategies.

Best practices

Create a hypertable

Create a data table (also known as a source table).

CREATE TABLE market_trades (
    market_id BIGINT NOT NULL,            -- Market ID
    trade_id BIGINT,                      -- Trade ID
    inst_name VARCHAR,                    -- Instrument name
    price DECIMAL NOT NULL,               -- Trade price
    amount DECIMAL NOT NULL,              -- Trade amount
    trade_ts TIMESTAMP NOT NULL,          -- Trade timestamp
    insert_ts BIGINT NOT NULL             -- Insert timestamp
);

Convert the data table into a hypertable.

SELECT create_hypertable(
    'market_trades',                     -- Table name
    'trade_ts',                           -- Column for time partitioning
    chunk_time_interval => INTERVAL '1 day', -- Time partition interval
    partitioning_column => 'market_id',   -- Column for space partitioning
    number_partitions => 20            -- Number of space partitions
);

(Optional) Change the time zone

By default, PolarDB clusters use the UTC time zone. You can change the time zone as needed. In this example, the time zone is set to China Standard Time, and the start time is set to the earliest timestamp in the test dataset.

CREATE OR REPLACE FUNCTION get_start_time() RETURNS timestamptz LANGUAGE SQL AS
$$
  -- The earliest timestamp in the test dataset is '2024-01-01 01:01:38.710554+08'. Specify the start time based on the actual data.
  SELECT timezone('Asia/Shanghai', '2024-01-01 01:01:38.710554+08'::timestamp);
$$ VOLATILE;

Create fixed K-line charts

Create fixed K-line charts by using continuous aggregates based on the source table and configure a refresh policy.

Second-level K-line chart

Create a second-level K-line chart.

CREATE MATERIALIZED VIEW kline_sec
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,
    market_id,
    MIN(price) AS low,
    MAX(price) AS high,
    first(price, trade_ts) AS open,
    last(price, trade_ts) AS close,
    last(insert_ts, trade_ts) AS insert_ts,
    SUM(amount) AS vol
FROM market_trades
GROUP BY candle_ts, market_id
WITH NO DATA;

Enable real-time querying.

ALTER MATERIALIZED VIEW kline_sec SET (timescaledb.materialized_only = false);

Automate refreshing of the continuous aggregate.
1. Create a refresh procedure.
```
CREATE OR REPLACE PROCEDURE kline_sec_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('sec', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_sec', start_time, NULL);
END
$$;
```
2. Schedule the refresh job.
  Note
  If the write volume is high, such as more than 3,000 records per second, the current whole-second refresh mechanism may cause slight jitter in real-time queries. To resolve this issue, you can increase the refresh frequency. For example, you can change the refresh interval to 0.05 seconds.
```
SELECT add_job('kline_sec_refresh','1 sec');
```

Minute-level K-line chart

To create a minute-level K-line chart, create a continuous aggregate that aggregates second-level K-line data into 1-minute intervals.

Create a minute-level K-line chart.

CREATE MATERIALIZED VIEW kline_min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 min', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_sec
GROUP BY time_bucket('1 min', candle_ts), market_id
WITH NO DATA;

Enable real-time querying.

ALTER MATERIALIZED VIEW kline_min SET (timescaledb.materialized_only = false);

Automate refreshing of the continuous aggregate.

Create a refresh procedure.

CREATE OR REPLACE PROCEDURE kline_min_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('min', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_min', start_time, NULL);
END
$$;

Schedule the refresh job.

SELECT add_job('kline_min_refresh','1 min');

Hour-level K-line chart

To create an hour-level K-line chart, create a continuous aggregate that aggregates minute-level K-line data into 1-hour intervals.

Create an hour-level K-line chart.

CREATE MATERIALIZED VIEW kline_hour
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_min
GROUP BY time_bucket('1 hour', candle_ts), market_id
WITH NO DATA;

Enable real-time querying.

ALTER MATERIALIZED VIEW kline_hour SET (timescaledb.materialized_only = false);

Automate refreshing of the continuous aggregate.

Create a refresh procedure.

CREATE OR REPLACE PROCEDURE kline_hour_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('hour', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_hour', start_time, NULL);
END
$$;

Schedule the refresh job.

SELECT add_job('kline_hour_refresh','1 hour', initial_start => date_trunc('hour', now()));

Day-level K-line chart

To create a day-level K-line chart, create a continuous aggregate that aggregates hour-level K-line data into 1-day intervals.

Create a day-level K-line chart.

CREATE MATERIALIZED VIEW kline_day
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_hour
GROUP BY time_bucket('1 day', candle_ts), market_id
WITH NO DATA;

Enable real-time querying.

ALTER MATERIALIZED VIEW kline_day SET (timescaledb.materialized_only = false);

Automate refreshing of the continuous aggregate.

Create a refresh procedure.

CREATE OR REPLACE PROCEDURE kline_day_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('day', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_day', start_time, NULL);
END
$$;

Schedule the refresh job.

SELECT add_job('kline_day_refresh','1 day',initial_start => date_trunc('hour', now()));

(Optional) Create a dynamic K-line chart

If fixed K-line charts do not meet your query requirements, you can create dynamic K-line charts based on fixed K-line charts. For example, you can create a 30 minute-level K-line chart based on the minute-level K-line chart.

SELECT
    market_id,
    (EXTRACT(EPOCH FROM time_bucket('30 min', candle_ts)) * 1000)::BIGINT AS candle_ts,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    SUM(vol) AS vol
FROM kline_min
WHERE market_id = 100001
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC
limit 10;

Simulate stock trading data

CREATE OR REPLACE FUNCTION test_trade_data(cnt int)
    RETURNS void
AS $$
DECLARE
   sql text;
   i int := 0;
   tm timestamp without time zone := '2024-01-01 01:01:38.710554+08'::TIMESTAMPTZ;
BEGIN
    FOR i IN 1..cnt
    LOOP
        -- Data is generated every 0.1 seconds.
        tm = tm + '0.1 sec';
        sql = format('INSERT INTO market_trades(market_id, price, amount, trade_ts, insert_ts) VALUES(100001, random(), random(), ''%s'', (EXTRACT(EPOCH FROM ''%s''::timestamp) * 1000)::bigint);', tm ,tm);
        execute sql;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql';

SELECT test_trade_data(1000000);

View K-line charts

Second-level K-line chart

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_sec WHERE market_id = 100001 ORDER BY candle_ts limit 10;

Sample result:

 market_id |      candle_ts      |         low         |       high        |        open        |        close        |         vol
-----------+---------------------+---------------------+-------------------+--------------------+---------------------+---------------------
    100001 | 2024-01-01 01:01:38 |   0.279485633997758 | 0.681338873589468 |  0.279485633997758 |   0.681338873589468 |   0.910744891936063
    100001 | 2024-01-01 01:01:39 |  0.0525641673891641 | 0.962955545742446 |  0.385015528170719 |  0.0525641673891641 |   4.620692226670329
    100001 | 2024-01-01 01:01:40 |  0.0633381498328447 | 0.954725391899331 |  0.801216359386196 |   0.242894295894374 |  3.6880741489060102
    100001 | 2024-01-01 01:01:41 |   0.077063096976957 | 0.619752482643143 |  0.619752482643143 |   0.362329000463575 |  5.4477741841190114
    100001 | 2024-01-01 01:01:42 |   0.130160131849806 | 0.904649178467757 |  0.751462633424246 |     0.8657081421063 | 4.60274994611772491
    100001 | 2024-01-01 01:01:43 |  0.0245408224841981 | 0.915085200903377 |  0.659205003206203 |  0.0245408224841981 |   6.882016111031889
    100001 | 2024-01-01 01:01:44 | 0.00436203999301199 | 0.999261582297358 |  0.999261582297358 | 0.00436203999301199 |   6.343432050925331
    100001 | 2024-01-01 01:01:45 |  0.0235861333017553 | 0.998218944784099 |  0.837373493299758 |   0.690308731587692 | 5.02263333895452072
    100001 | 2024-01-01 01:01:46 |   0.167417759341774 |   0.9150153383248 |  0.213189330042447 |   0.796555201647649 |  4.9323075845995145
    100001 | 2024-01-01 01:01:47 |  0.0337299584831712 | 0.765553193903251 | 0.0337299584831712 |   0.160180953095164 |   5.136451153685312
(10 rows)

Minute-level K-line chart

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_min WHERE market_id = 100001 ORDER BY candle_ts limit 10;

Sample result:

 market_id |      candle_ts      |         low          |       high        |        open        |        close        |          vol
-----------+---------------------+----------------------+-------------------+--------------------+---------------------+------------------------
    100001 | 2024-01-01 01:01:00 |  0.00436203999301199 | 0.999261582297358 |  0.279485633997758 |   0.660205011565097 |  107.67711307252750133
    100001 | 2024-01-01 01:02:00 | 0.000382727352629786 | 0.997355089315917 |  0.638467919873655 |   0.741995626890457 | 300.032527480741814196
    100001 | 2024-01-01 01:03:00 |  0.00102311960848311 | 0.998987023712075 |  0.494493945315959 | 0.00933623188228694 | 296.670624428911073230
    100001 | 2024-01-01 01:04:00 | 0.000317312327567265 | 0.999400263658501 |  0.914812683364666 |   0.565009458532018 |  308.44719638361391718
    100001 | 2024-01-01 01:05:00 |  0.00165624319932078 | 0.990618859346739 |  0.456251672433702 |   0.825974036343325 |  297.87230623497759724
    100001 | 2024-01-01 01:06:00 |  0.00446447743636469 | 0.994494939263078 | 0.0486883210703937 |   0.690970192417581 | 304.612071727522449497
    100001 | 2024-01-01 01:07:00 | 0.000609624951934506 | 0.999061576948947 |  0.663441135677637 |   0.843439992945548 |  317.33816039424379517
    100001 | 2024-01-01 01:08:00 | 0.000305593115932368 | 0.999032452035479 |  0.595979795892312 |   0.545231376105335 |   293.4771868344440740
    100001 | 2024-01-01 01:09:00 |  0.00180631157653011 | 0.999969521686225 |  0.687172827619953 |    0.52655462636659 |  308.64126233531476704
    100001 | 2024-01-01 01:10:00 |   0.0042720602925499 | 0.999550291053481 | 0.0572916237256678 |   0.827660040200954 |  302.52094683726853244
(10 rows)

Hour-level K-line chart

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_hour WHERE market_id = 100001 ORDER BY candle_ts limit 10;

Sample result:

 market_id |      candle_ts      |          low           |       high        |       open        |       close       |            vol
-----------+---------------------+------------------------+-------------------+-------------------+-------------------+----------------------------
    100001 | 2024-01-01 01:00:00 |  0.0000311086421334039 | 0.999988514373982 | 0.279485633997758 | 0.122312797390521 |  17496.2413504152012247561
    100001 | 2024-01-01 02:00:00 |  0.0000770308891162585 | 0.999986986486086 | 0.545347700734887 | 0.125192844770879 |  18083.5114260775312946946
    100001 | 2024-01-01 03:00:00 |  0.0000845044836879083 | 0.999952117680969 | 0.426587036254354 | 0.532456849129801 | 18030.73594659831131232683
    100001 | 2024-01-01 04:00:00 |  0.0000918109849195048 | 0.999995562667976 |   0.6330394930823 | 0.373607753625436 |  18015.5229018762197568761
    100001 | 2024-01-01 05:00:00 |  0.0000109588696517449 | 0.999994772802758 | 0.258798587687728 | 0.507375948925699 |  18051.3594940787675161618
    100001 | 2024-01-01 06:00:00 |  0.0000487699150326648 | 0.999988924300197 |  0.28910545006503 | 0.634414585708271 |  18025.7784576000281124843
    100001 | 2024-01-01 07:00:00 | 0.00000107306622965098 | 0.999981224025916 | 0.433356636233988 | 0.559994887160599 |  18010.8893090606375400777
    100001 | 2024-01-01 08:00:00 |  0.0000171556727366351 | 0.999982847376515 | 0.208111940739776 | 0.706701461479895 |  18042.9095973077942234180
    100001 | 2024-01-01 09:00:00 |  0.0000391086173721078 | 0.999991541819419 | 0.270102209152959 | 0.877127614373137 |  18006.6961534152591878249
    100001 | 2024-01-01 10:00:00 |  0.0000039186593809859 | 0.999974658507568 | 0.999237150569492 | 0.266570661557068 |  17965.2763406833559493498
(10 rows)

Day-level K-line chart

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_day WHERE market_id = 100001 ORDER BY candle_ts limit 10;

Sample result:

 market_id |      candle_ts      |          low           |       high        |       open        |       close       |             vol
-----------+---------------------+------------------------+-------------------+-------------------+-------------------+-----------------------------
    100001 | 2024-01-01 00:00:00 | 0.00000107306622965098 | 0.999995695245573 | 0.279485633997758 | 0.400038274292626 | 413100.59225003849417556234
    100001 | 2024-01-02 00:00:00 |  0.0000168010291758947 | 0.999989906749448 | 0.383386779917068 | 0.842509523496489 |  86494.66799924685771865093
(2 rows)

30 minute-level K-line chart

SELECT
    market_id,
    time_bucket('30 min', candle_ts) AS candle_ts,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    SUM(vol) AS vol
FROM kline_min
WHERE market_id = 100001
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC
limit 10;

Sample result:

 market_id |      candle_ts      |          low          |       high        |       open        |       close       |            vol
-----------+---------------------+-----------------------+-------------------+-------------------+-------------------+---------------------------
    100001 | 2024-01-02 04:30:00 | 0.0000868399533757724 | 0.999973308725529 | 0.245099633513782 | 0.842509523496489 |   5504.421751602083391644
    100001 | 2024-01-02 04:00:00 | 0.0000322917197408401 | 0.999930785807123 | 0.205706123491304 | 0.461819536540872 |   9105.530763894508472418
    100001 | 2024-01-02 03:30:00 | 0.0000195752894498469 |  0.99995978492796 | 0.864512243732669 | 0.877628779498252 |  8976.6974200916718262393
    100001 | 2024-01-02 03:00:00 | 0.0000965453148040751 | 0.999730903399971 | 0.277744581160881 |  0.40121776736629 |  8950.0175974347619817090
    100001 | 2024-01-02 02:30:00 | 0.0000168010291758947 | 0.999989906749448 | 0.628462923153656 |  0.59543446274796 |  8994.8387945492956960329
    100001 | 2024-01-02 02:00:00 | 0.0000243959481878164 | 0.999908087762357 | 0.397794388402794 | 0.253756033949969 |  8999.4657434897451608921
    100001 | 2024-01-02 01:30:00 |  0.000107806352637851 | 0.999920775786947 |  0.72757380777356 | 0.487782751467137 |   8978.433358958287225616
    100001 | 2024-01-02 01:00:00 |  0.000149064256355302 | 0.999904610171029 | 0.474390355164054 | 0.349888134466767 |  9026.5921875235542230817
    100001 | 2024-01-02 00:30:00 |  0.000112103957143006 | 0.999988398043051 |  0.45504042836459 | 0.491025347273023 |  8951.0063568393831949862
    100001 | 2024-01-02 00:00:00 | 0.0000272969867580741 | 0.999900252112297 | 0.383386779917068 | 0.353689845851306 | 9007.66402486356654603173
(10 rows)

Performance comparison

Query	Original data size	Query execution time without continuous aggregates	Query execution time with continuous aggregates	Performance improvement multiplier
Maximum value for each hour over the past seven days	100,000,000 rows	12 seconds	0.2 seconds	60
Average value for each day over the past year	1,000,000,000 rows	Timeout (more than 5 minutes)	0.5 seconds	More than 600