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.

candlestick

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

FieldDescription
timestarting time of the minute
symbolticker symbol
price_openopening price of the stock
price_closeclosing price of the stock
price_lowlowest price in the minute
price_highhighest price in the minute
trading_volumetrading volume in the minute

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.