Design database schema

When you design a database schema, you need to think about what kind of data it stores.

This tutorial is about analyzing intraday stock data, so you need to create a schema that can handle candlestick data. This is what a typical candlestick looks like:

At least four data points are needed to create a candlestick chart: high, open, close, low.

You also need to have fields for the ticker symbol, time, and trading volume. The data fields we are using are:

Based on this, you can create a table called stocks_intraday:

2. CREATE TABLE public.stocks_intraday (

4. "time" timestamptz NOT NULL,

6. symbol text NULL,

8. price_open double precision NULL,

10. price_close double precision NULL,

12. price_low double precision NULL,

14. price_high double precision NULL,

16. trading_volume int NULL

18. );

This creates a regular PostgreSQL table with all the columns needed to ingest candlestick data records.

Create hypertable

To use TimescaleDB features, you need to enable TimescaleDB, and create a hypertable from the stocks_intraday table.

Enable TimescaleDB extension:

2. CREATE EXTENSION IF NOT EXISTS timescaledb;

Create hypertable from stocks_intraday table:

2. /*

4. stocks_intraday: name of the table

6. time: name of the timestamp column

8. */

10. SELECT create_hypertable('stocks_intraday', 'time');

At this point, you have an empty hypertable, ready to ingest time-series data.