Tables and hypertables

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.

Databases in are made up of tables that contain your data. In PostgreSQL, these tables are relational, so the data in one table relates to the data in another table. In Timescale, you use regular PostgreSQL relational tables, in addition to special time-series hypertables.

Hypertables are designed specifically for time-series data, so they have a few special qualities that makes them different to a regular PostgreSQL table. A hypertable is always partitioned on time, but can also be partitioned on additional columns as well. The other special thing about hypertables is that they are broken down into smaller tables called chunks.

In this section, you create a hypertable for time-series data, and regular PostgreSQL tables for relational data. You also create an index on your hypertable, which isn't required, but can help your queries run more efficiently. One of the other special qualities of hypertables is that you can also create indexes later on, if you need to.

For more information, see the hypertables section.

Create your first hypertable

For the financial dataset used in this guide, create a hypertable named stocks_real_time that contains second-by-second stock-trade data for the top 100 most-traded symbols.

Creating your first hypertable

At the command prompt, use the psql connection string from the cheat sheet you downloaded to connect to your database.

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', by_range('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. You can check this by running this command at the psql prompt:
```
\dt
```
This command returns information about your tables, like this:
```
List of relations
 Schema |       Name       | Type  |   Owner
--------+------------------+-------+-----------
 public | company          | table | tsdbadmin
 public | stocks_real_time | table | tsdbadmin
(2 rows)
```