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:

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:

CREATE TABLE public.stocks_intraday (
"time" timestamptz NOT NULL,
symbol text NULL,
price_open double precision NULL,
price_close double precision NULL,
price_low double precision NULL,
price_high double precision NULL,
trading_volume int NULL

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:


Create hypertable from stocks_intraday table:

stocks_intraday: name of the table
time: name of the timestamp column
SELECT create_hypertable('stocks_intraday', 'time');

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

Found an issue on this page?

Report an issue!


Related Content