What are best practices to manage different Resolution with Timescale DB

TimescaleDB and PostgreSQL
,
1

I am pretty new to TSDB and especially with TimeScale DB. I have successfully managed to store the 1Min OHLCV in Materialized with, setup continous aggregation and everything is running fine.

I am using TradingView Charting library on the FE which automatically creates 5min, 15min, 30min and other charts from the 1min OHLCV data.

My response object for TradingView /history request :

{
"s":"ok",
"t":[1666703100,1666703160,1666703220],
"o":[19288.1,19285.4,19289.1],
"h":[19294.8,19289.1,19289.1],
"l":[19285.4,19285.4,19288.7],
"c":[19285.4,19289.0,19288.8],
"v":[180.10899999999992,83.95100000000004]
}

the Result I get from TimeScale DB :

{
"day": 2022-10-22 13:35:00+05:30,
"open": 19230.326,
"high":19500,
"low":19200.20,
"close":19250.68,
"volume":2.658
}

So I have to loop through ResultSet from DB → map to my response object → send to TV.

Now as the data grows, 1min candles take up lot of processing due to this loop.

Question:

1. What should be an ideal way from performance and resource optimization point of view
to manage multiple resoution with TimeScale DB ?
2

This question was answered here, let’s keep a copy of the answer here too:

Timescaledb is testing an experimental set of functions to cover ohlc and soon ohlcv will also be available.

You can rollup it over ohlc data but not use materialized views over materialized views (at least for now).

Let me share a small example that maybe can help.

First, a simple ticks table to store the trade events:

CREATE TABLE ticks
( time TIMESTAMPTZ NOT NULL,
    symbol varchar,
    price double precision,
    volume int);

SELECT create_hypertable('ticks', 'time', chunk_time_interval => INTERVAL '1 day');

Now, let’s create the base ohlcv from 1 minute being the most granular pre-calculated materialized view:


CREATE MATERIALIZED VIEW _ohlcv_1m
WITH (timescaledb.continuous) AS
    SELECT time_bucket('1 minute'::interval, time),
      symbol,
      toolkit_experimental.ohlc(time, price),
      sum(volume) as volume
    FROM ticks
    GROUP BY 1,2 WITH DATA;

As the continuous aggregates in the top of another continuous aggregates is not allowed, we can create a simple view and benefit from rollup that will “merge” the previous ohlc structures.


CREATE VIEW _ohlcv_1h AS
    SELECT time_bucket('1 hour'::interval, time_bucket),
      symbol,
      toolkit_experimental.rollup(ohlc) as ohlc,
      sum(volume) as volume
    FROM _ohlcv_1m
    GROUP BY 1,2;

The views don’t have access to the values directly, so, we can create views to access the ohlcv values and the time of each event.


CREATE VIEW ohlcv_1m as
SELECT time_bucket,
  symbol,
  toolkit_experimental.open(ohlc),
  toolkit_experimental.open_time(ohlc),
  toolkit_experimental.high(ohlc),
  toolkit_experimental.high_time(ohlc),
  toolkit_experimental.low(ohlc),
  toolkit_experimental.low_time(ohlc),
  toolkit_experimental.close(ohlc),
  toolkit_experimental.close_time(ohlc),
  volume
FROM _ohlcv_1m;

CREATE VIEW ohlcv_1h as
SELECT time_bucket,
  symbol,
  toolkit_experimental.open(ohlc),
  toolkit_experimental.open_time(ohlc),
  toolkit_experimental.high(ohlc),
  toolkit_experimental.high_time(ohlc),
  toolkit_experimental.low(ohlc),
  toolkit_experimental.low_time(ohlc),
  toolkit_experimental.close(ohlc),
  toolkit_experimental.close_time(ohlc),
  volume
FROM _ohlcv_1h;

Now, seeding some data for tests:


INSERT INTO ticks
SELECT time, 'SYMBOL', (random()*30)::int, 100*(random()*10)::int
FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
                 TIMESTAMP '2000-01-02 00:00:00' + INTERVAL '1 hour',
             INTERVAL '1 second') AS time;

Testing the final views:

playground=# select time_bucket, open, high, low, close, volume from ohlcv_1h ORDER BY time_bucket LIMIT 1;
┌────────────────────────┬──────┬──────┬─────┬───────┬─────────┐
│      time_bucket       │ open │ high │ low │ close │ volume  │
├────────────────────────┼──────┼──────┼─────┼───────┼─────────┤
│ 2000-01-01 00:00:00-02 │   13 │   30 │   0 │     8 │ 1809600 │
└────────────────────────┴──────┴──────┴─────┴───────┴─────────┘
(1 row)

playground=# select time_bucket, open, high, low, close, volume from ohlcv_1m ORDER BY time_bucket LIMIT 60
;
┌────────────────────────┬──────┬──────┬─────┬───────┬────────┐
│      time_bucket       │ open │ high │ low │ close │ volume │
├────────────────────────┼──────┼──────┼─────┼───────┼────────┤
│ 2000-01-01 00:00:00-02 │   13 │   30 │   0 │    25 │  32700 │
│ 2000-01-01 00:01:00-02 │    0 │   30 │   0 │     2 │  33000 │
│ 2000-01-01 00:02:00-02 │    1 │   30 │   0 │     6 │  26300 │
│ 2000-01-01 00:03:00-02 │    4 │   30 │   1 │    28 │  30300 │
│ 2000-01-01 00:04:00-02 │   30 │   30 │   0 │    27 │  29200 │
│ 2000-01-01 00:05:00-02 │   24 │   29 │   2 │    16 │  27500 │
│ 2000-01-01 00:06:00-02 │    9 │   28 │   0 │    15 │  30200 │
│ 2000-01-01 00:07:00-02 │   21 │   30 │   2 │     3 │  30300 │
│ 2000-01-01 00:08:00-02 │   10 │   29 │   1 │     3 │  35300 │
│ 2000-01-01 00:09:00-02 │    6 │   30 │   0 │    16 │  26700 │
│ 2000-01-01 00:10:00-02 │   10 │   30 │   0 │    24 │  30300 │
│ 2000-01-01 00:11:00-02 │   30 │   30 │   1 │    27 │  29800 │
│ 2000-01-01 00:12:00-02 │    6 │   30 │   1 │    26 │  25500 │
│ 2000-01-01 00:13:00-02 │   13 │   29 │   0 │    22 │  30400 │
│ 2000-01-01 00:14:00-02 │    2 │   30 │   1 │    21 │  30800 │
│ 2000-01-01 00:15:00-02 │   22 │   30 │   0 │    15 │  31400 │
│ 2000-01-01 00:16:00-02 │   10 │   30 │   1 │     8 │  33700 │
│ 2000-01-01 00:17:00-02 │    3 │   30 │   1 │    22 │  27600 │
│ 2000-01-01 00:18:00-02 │   13 │   29 │   0 │     5 │  31400 │
│ 2000-01-01 00:19:00-02 │   17 │   29 │   1 │    16 │  30900 │
│ 2000-01-01 00:20:00-02 │   24 │   29 │   1 │    10 │  26500 │
│ 2000-01-01 00:21:00-02 │   16 │   29 │   1 │     9 │  34100 │
│ 2000-01-01 00:22:00-02 │   14 │   30 │   0 │    22 │  31500 │
│ 2000-01-01 00:23:00-02 │   10 │   30 │   1 │     1 │  28700 │
│ 2000-01-01 00:24:00-02 │    1 │   30 │   1 │     1 │  32900 │
│ 2000-01-01 00:25:00-02 │   11 │   30 │   1 │    24 │  25700 │
│ 2000-01-01 00:26:00-02 │   21 │   28 │   0 │     1 │  32100 │
│ 2000-01-01 00:27:00-02 │    1 │   30 │   0 │    23 │  33800 │
│ 2000-01-01 00:28:00-02 │    1 │   30 │   1 │    22 │  30100 │
│ 2000-01-01 00:29:00-02 │   15 │   30 │   0 │     6 │  26900 │
│ 2000-01-01 00:30:00-02 │   15 │   29 │   1 │     7 │  32800 │
│ 2000-01-01 00:31:00-02 │    9 │   30 │   0 │     7 │  30200 │
│ 2000-01-01 00:32:00-02 │   16 │   30 │   0 │    10 │  28000 │
│ 2000-01-01 00:33:00-02 │    6 │   30 │   1 │     9 │  28800 │
│ 2000-01-01 00:34:00-02 │    6 │   30 │   1 │     9 │  30300 │
│ 2000-01-01 00:35:00-02 │   20 │   29 │   0 │    17 │  30800 │
│ 2000-01-01 00:36:00-02 │   27 │   29 │   0 │    21 │  28400 │
│ 2000-01-01 00:37:00-02 │   29 │   30 │   0 │    20 │  30100 │
│ 2000-01-01 00:38:00-02 │   23 │   30 │   0 │     5 │  30300 │
│ 2000-01-01 00:39:00-02 │    1 │   30 │   0 │    24 │  34000 │
│ 2000-01-01 00:40:00-02 │   10 │   30 │   0 │    30 │  27500 │
│ 2000-01-01 00:41:00-02 │    3 │   30 │   0 │    20 │  32200 │
│ 2000-01-01 00:42:00-02 │    2 │   29 │   0 │    12 │  29400 │
│ 2000-01-01 00:43:00-02 │    5 │   29 │   0 │     4 │  31800 │
│ 2000-01-01 00:44:00-02 │   22 │   30 │   0 │     6 │  32700 │
│ 2000-01-01 00:45:00-02 │   11 │   29 │   1 │     9 │  30400 │
│ 2000-01-01 00:46:00-02 │   10 │   30 │   0 │     1 │  26700 │
│ 2000-01-01 00:47:00-02 │   12 │   30 │   0 │    17 │  28400 │
│ 2000-01-01 00:48:00-02 │   15 │   30 │   0 │    16 │  30800 │
│ 2000-01-01 00:49:00-02 │    6 │   30 │   0 │    29 │  27900 │
│ 2000-01-01 00:50:00-02 │   20 │   30 │   0 │    28 │  26500 │
│ 2000-01-01 00:51:00-02 │    7 │   30 │   0 │    14 │  30500 │
│ 2000-01-01 00:52:00-02 │   23 │   29 │   0 │     4 │  31200 │
│ 2000-01-01 00:53:00-02 │   26 │   30 │   1 │    10 │  31000 │
│ 2000-01-01 00:54:00-02 │   24 │   30 │   1 │     3 │  30200 │
│ 2000-01-01 00:55:00-02 │    3 │   30 │   0 │    20 │  32800 │
│ 2000-01-01 00:56:00-02 │   24 │   30 │   1 │    12 │  27300 │
│ 2000-01-01 00:57:00-02 │   19 │   30 │   0 │    26 │  30600 │
│ 2000-01-01 00:58:00-02 │    8 │   29 │   1 │    21 │  33500 │
│ 2000-01-01 00:59:00-02 │   10 │   30 │   1 │     8 │  28900 │
└────────────────────────┴──────┴──────┴─────┴───────┴────────┘
(60 rows)

For the part 2 of your question, you can use array_agg from postgresql to be concatenating the data. Example:

select array_agg(time_bucket) as t,
array_agg(open) as o,
array_agg(high) as h,
array_agg(low) as l,
array_agg(close) as c,
array_agg(volume) as v from ohlcv_1m group BY time_bucket('5 m', time_bucket) limit 3 ;

I’m limiting to 3 records just for the sake of readability, here are the results:

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────┬──────────────────┬─────────────┬────────────────┬─────────────────────────────────┐
│                                                               t                                                                │        o        │        h         │      l      │       c        │                v                │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────┼──────────────────┼─────────────┼────────────────┼─────────────────────────────────┤
│ {"2000-01-01 00:02:00-02","2000-01-01 00:00:00-02","2000-01-01 00:01:00-02","2000-01-01 00:03:00-02","2000-01-01 00:04:00-02"} │ {16,0,27,29,14} │ {30,30,30,29,30} │ {1,0,2,1,0} │ {27,14,23,1,0} │ {29500,32400,32100,28500,25400} │
│ {"2000-01-01 00:07:00-02","2000-01-01 00:08:00-02","2000-01-01 00:05:00-02","2000-01-01 00:09:00-02","2000-01-01 00:06:00-02"} │ {22,26,9,17,5}  │ {30,30,29,30,29} │ {0,2,0,1,0} │ {21,5,18,14,5} │ {29000,28700,31600,30700,31200} │
│ {"2000-01-01 00:12:00-02","2000-01-01 00:11:00-02","2000-01-01 00:13:00-02","2000-01-01 00:10:00-02","2000-01-01 00:14:00-02"} │ {29,20,27,19,2} │ {30,30,29,30,30} │ {0,0,0,1,0} │ {1,17,21,9,16} │ {28400,31500,30800,25900,32100} │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────┴──────────────────┴─────────────┴────────────────┴─────────────────────────────────┘

Note that the final aggregated array is not sorted.

Also, the nested continuous aggregates feature is in progress.

4

@jonatasdp Thanks a lot for the detailed explanation… It helped a lot. For now, I have created separate MATERIALIZED VIEW for each resolution that my app supports. Also, created continuous Aggregation for each resolution. It’s working fine right now. I didn’t wanted to use too much processing when OHLCV is queried. Whether it’s TimescaleDB or my Java code, either of them will consume RAM when calculating 5min OHLCV from 1min. Storage cost is not an issue, but performance is required.

However, I need some more help in regards to my 2nd question. Below is the query I use to get OHLCV from 1 min materialized view :

@Query(nativeQuery = true, value = "SELECT * FROM one_minute where market= :market AND day >= :start AND day <= :end ORDER BY day ASC LIMIT 1000;")
    List<OHLCModel> getOneMinuteOHLCV(@Param(value = "market") String market, @Param(value = "start") ZonedDateTime start, @Param(value = "end")ZonedDateTime end);

Can you guide how can I get the array result as per my response object using the query you have posted… I am slightly confused on generating the query.