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:

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:

CREATE TABLE public.stocks_intraday (
    "time" timestamp 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 EXTENSION IF NOT EXISTS timescaledb;

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!