Indexing data

You can use an index on your database to speed up read operations. You can create an index on any combination of columns. TimescaleDB supports all table objects supported within Postgres, including data types, indexes, and triggers.

You can create an index using the CREATE INDEX command. For example, to create an index that sorts first by location, then by time, in descending order:

CREATE INDEX ON conditions (location, time DESC);

Copy

You can run this command before or after you convert a regular Postgres table to a hypertable.

Some indexes are created by default when you perform certain actions on your database.

When you create a hypertable with a call to CREATE TABLE, a time index is created on your data. If you want to manually create a time index, you can use this command:

CREATE INDEX ON conditions (time DESC);

Copy

You can also create an additional index on another column and time. For example:

CREATE INDEX ON conditions (location, time DESC);

Copy

TimescaleDB also creates sparse indexes per compressed chunk for optimization. You can manually set up those indexes when you call CREATE TABLE or ALTER_TABLE.

For more information about the order to use when declaring indexes, see the about indexing section.

If you do not want to create default indexes, you can set create_default_indexes to false when you create a hypertable. For example:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  device      TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
) WITH (
  tsdb.hypertable,
  tsdb.create_default_indexes=false
);

Copy

When you create a hypertable using CREATE TABLE ... WITH ..., the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk_interval, defined through compress_after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert chunks in a hypertable to the columnstore.

If you have sparse data, with columns that are often NULL, you can add a clause to the index, saying WHERE column IS NOT NULL. This prevents the index from indexing NULL data, which can lead to a more compact and efficient index. For example:

CREATE INDEX ON conditions (time DESC, humidity)
  WHERE humidity IS NOT NULL;

Copy

To define an index as a UNIQUE or PRIMARY KEY index, the index must include the time column and the partitioning column, if you are using one. For example, a unique index must include at least the (time, location) columns, in addition to any other columns you want to use. Generally, time-series data uses UNIQUE indexes more rarely than relational data.

If you do not want to create an index in a single transaction, you can use the CREATE_INDEX function. This uses a separate function to create an index on each chunk, instead of a single transaction for the entire hypertable. This means that you can perform other actions on the table while the index is being created, rather than having to wait until index creation is complete.

Note

You can also use the Postgres WITH clause to perform indexing transactions on an individual chunk.