In this chapter, we’re going to run some SQL commands in PGAdmin, to setup our TimescaleDB database.

Make sure the Docker containers are up and running:

$ docker-compose ps

If you followed the instructions in the previous chapter, you can now access PGAdmin to run SQL queries on your TimescaleDB database, at http://localhost:9000 or http://127.0.0.1:9000. Log in with the username and password you added to your .env file.

Now that you’ve logged into PGAdmin, right-click on “Servers” and under “Create” click “Server…”. Name it “TimescaleDB Local” in the “General” tab, and type the following into the “Connection” tab:

• Host: timescale
• Port: 5432
• Maintenance database: postgres
• Username: postgres
• Password: password

The host name is “timescale” since that’s what we named the TimescaleDB container in our “docker-compose.yml” file. Since the PGAdmin container is on the same Docker network timescale_network as the TimescaleDB container, PGAdmin can talk to TimescaleDB.

Click “Save” and you should be connected. Now you can double-click on “TimescaleDB Local” and you can access your database tables at “/Databases/postgres/Schemas/public/Tables”. You don’t have any tables yet, but we’ll create a few shortly. Pretty cool, huh? Under the “Tools” menu, click on “Query Tool” and you’re ready to start writing SQL.

You’re now the proud commander of a TimescaleDB database, which is identical to a PostgreSQL database (“The world’s most advanced open source database”), except that it now has special abilities for dealing with high-frequency time series data.

Time Series Data

Time series data is a bit different from regular relational data for describing users and things. Time series data can arrive any second, or even multiple times per second, depending on what you’re storing, so the database needs to be able to handle lots of insertions. Some examples are financial data, such as stock market trading prices like we’ll be using, or internet of things (IoT) data, usually for monitoring environmental metrics like temperature, pressure, humidity, or anything else you can think of. Usually, when you query time series data, you’re interested in the most recent data, and you’re usually filtering on the timestamp column, so that definitely needs to be indexed. TimescaleDB specializes in this sort of thing.

Hypertable

Let’s create a special TimescaleDB “Hypertable” and insert some data to play with.

In PGAdmin, if you’re not already there, under the “Tools” menu, click on “Query Tool” and type the following SQL to create two database tables in the default “public” schema:

CREATE TABLE stock_tickers (
  ticker TEXT PRIMARY KEY,
  name TEXT,
  industry TEXT
);

CREATE TABLE stock_prices (
  time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  ticker TEXT,
  open NUMERIC,
  high NUMERIC,
  low NUMERIC,
  close NUMERIC,
  close_adj NUMERIC,
  volume NUMERIC,
  FOREIGN KEY (ticker) REFERENCES stock_tickers (ticker)
);

Press the “play” button, or click “F5” to run the query.

After running the above query, you can now right-click on “Tables” on the left, and click “Refresh…” to see your two new tables, stock_prices and stock_tickers.

Now for the special part that you can’t do in a regular PostgreSQL database: We’re going to transform the stock_prices table into a “Hypertable”. Behind the scenes, TimescaleDB is going to partition the data on the time dimension, making it much faster and easier to filter, index, and drop old time series data.

If you’ve come to this course to take advantage of TimescaleDB’s unique features, the following is where the magic happens.

Run the following query in PGAdmin to create the hypertable, automatically partitioned on the “time” dimension:

SELECT create_hypertable('stock_prices', 'time');

Now that our specialized time series table has been created, let’s create a special index on the stock ticker, since we’re very likely to filter on both ticker and time:

create index on stock_prices (ticker, time desc);

Let’s now add a few different stocks to the “stock_tickers” table along with their industries:

INSERT INTO stock_tickers (ticker, name, industry) VALUES
  ('MSFT','Microsoft Corporation','Technology'),
  ('TSLA','Tesla Inc','Auto Manufacturers'),
  ('CVX', 'Chevron Corp','Energy'),
  ('XOM', 'Exxon Mobil Corporation','Energy');

Have a quick look at your new stock_tickers table with the following query:

SELECT * FROM public.stock_tickers;

Conclusion

In this chapter, we’ve created a few database tables for our stock price data. We even created a special TimescaleDB hypertable for storing our time series data. Well done.

In the next chapter, we’ll use Python to download stock price data and insert it into our database.

Next: Adding Data to TimescaleDB
Prev: Optional VS Code IDE Setup