TimescaleDB – One of the Best Ways to Store Market Data

Over the years I worked on many quantitative projects. Every project has different ways of storing market data. If you want to store your market data properly TimeScale DB might be a very good solution for you. It’s a Time Series database on to of PostgreSQL. It seems to be pretty quick and robust. It can allow you to do really interesting queries like resampling your data on the fly.

TimeScale is a plugin over PostgreSQL so you can use the usual SQL syntax to create a table:

/* Creating a table */
CREATE TABLE candles_1m (
   time   TIMESTAMPTZ NOT NULL,
   open   DOUBLE PRECISION NOT NULL,
   high   DOUBLE PRECISION NOT NULL,
   low    DOUBLE PRECISION NOT NULL,
   close  DOUBLE PRECISION NOT NULL,
   volume DOUBLE PRECISION NOT NULL
);

This table is for 1m candles. It has a time column that represents the open of every bar and will be a column Timescale will use for indexing.

Now let’s add some random values to the table:

/* Adding Values */
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:00:00', 10, 12, 5.3, 11, 255);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:01:00', 11, 15, 7, 9, 200);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:02:00', 9, 11, 10, 10, 123);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:03:00', 10, 17, 9, 14, 90);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:04:00', 14, 39, 30, 20, 987);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:05:00', 20, 26, 25, 25, 33);
INSERT INTO candles_1m(time, open, high, low, close, volume)
   VALUES ('2021-03-17 14:06:00', 25, 39, 19, 30, 983);

Now we have a table with some values. You can check that to works:

select * from candles_1m;

For now, it’s a usual PostgreSQL table, to make it work with TimeScale you have to call the create_hypertable function:

SELECT create_hypertable('candles_1m', 'time', migrate_data => 'TRUE');

Here I pass to this function name of the table I created, a column in it for time index, and the flag to migrate existing data.

Now you can run the Timescale DB function and queries. So here for example the code that will create 5m bars from your 1-minute bars:

SELECT time_bucket('5 minutes', time) AS new_time,
  first(open, time) as open,
  MAX(high)   as high,
  MIN(low)    as low, 
  last(close, time) as close, 
  sum(volume) as volume
FROM candles_1m
GROUP BY new_time

Leave a Reply

Your email address will not be published. Required fields are marked *