Create a hypertable

Hypertables are PostgreSQL tables that automatically partition your data by time. You interact with hypertables in the same way as regular PostgreSQL tables, but with extra features that makes managing your time-series data much easier.

In Timescale, hypertables exist alongside regular PostgreSQL tables. Use hypertables to store time-series data. This gives you improved insert and query performance, and access to useful time-series features. Use regular PostgreSQL tables for other relational data.

With hypertables, Timescale makes it easy to improve insert and query performance by partitioning time-series data on its time parameter. Behind the scenes, the database performs the work of setting up and maintaining the hypertable's partitions. Meanwhile, you insert and query your data as if it all lives in a single, regular PostgreSQL table.

For more information, see the hypertables section.

Create your first hypertable

When you create a hypertable, it is automatically partitioned on the time column you provide as the second parameter to create_hypertable(). Also, Timescale automatically creates an index on the time column. However, you'll often filter your time-series data on other columns as well. Using indexes appropriately helps your queries perform better.

Because you often query the stock trade data by the company symbol, you can also add an index for it. Include the time column because time-series data typically looks for data in a specific period of time.

Creating your first hypertable

Create a regular PostgreSQL table to store the real-time stock trade data using CREATE TABLE:


CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

Convert the regular table into a hypertable partitioned on the time column using the create_hypertable() function provided by Timescale. You must provide the name of the table (stocks_real_time) and the column in that table that holds the timestamp data to use for partitioning (time):
```
SELECT create_hypertable('stocks_real_time','time');
```
Create an index to support efficient queries on the symbol and time columns:
```
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);
```

Create regular PostgreSQL tables for relational data

Timescale isn't just for hypertables. When you have other relational data that enhances your time-series data, you can create regular PostgreSQL tables just as you would normally. For this dataset, there is one other table of data called company.

Creating regular PostgreSQL tables

Add a table to store the company name and symbol for the stock trade data:


CREATE TABLE company (
  symbol TEXT NOT NULL,
  name TEXT NOT NULL
);

You now have two tables within your Timescale database. One hypertable named stocks_real_time, and one normal PostgreSQL table named company.

Next steps

Ingest some sample stock trade data into Timescale. The next section, 'Add time-series data', shows you how to populate the tables you just created.

Learn more about hypertables and chunks

For more information about hypertables and chunks, see the hypertables section.