Skip to main content

​
Optimize time-series data in hypertables

Hypertables are designed for real-time analytics, they are tables that automatically partition your data by time. Typically, you partition hypertables on columns that hold time values. Best practice is to use timestamptz column type. However, you can also partition on date, integer and timestamp types.

​
Prerequisites

To follow the steps on this page:
  • Create a target with time-series and analytics enabled.

     You need your connection details. This procedure also works for .

​
Create a hypertable

Create a for your time-series data using CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data: 
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.partition_column='time',
   tsdb.segmentby = 'device',
   tsdb.orderby = 'time DESC'
);
If you are self-hosting v2.19.3 and below, create a relational table, then convert it using create_hypertable. You then enable with a call to ALTER TABLE. To convert an existing table with data in it, call create_hypertable on that table with migrate_data to true. However, if you have a lot of data, this may take a long time.

​
Speed up data ingestion:

When you set timescaledb.enable_direct_compress_copy your data is compressed when it is ingested into memory during COPY and INSERT calls. This means that WAL records are written for the compressed batches rather than the individual tuples. Also, the columnstore policy you set is less important, INSERT already produces compressed chunks. 
SET timescaledb.enable_direct_compress_copy;

​
Optimize cooling data in the columnstore

As the data cools and becomes more suited for analytics, add a columnstore policy so your data is automatically converted to the after a specific time interval. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the conversion, chunks are compressed by more than 90%, and organized for efficient, large-scale queries. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads. To optimize your data, add a policy: 
CALL add_columnstore_policy('conditions', after => INTERVAL '1d');
You can also manually convert chunks in a to the .

​
Alter a hypertable

You can alter a hypertable, for example to add a column, by using the ALTER TABLE command. This works for both regular and distributed hypertables.

​
Add a column to a hypertable

You add a column to a hypertable using the ALTER TABLE command. In this example, the hypertable is named conditions and the new column is named humidity: 
ALTER TABLE conditions
  ADD COLUMN humidity DOUBLE PRECISION NULL;
If the column you are adding has the default value set to NULL, or has no default value, then adding a column is relatively fast. If you set the default to a non-null value, it takes longer, because it needs to fill in this value for all existing rows of all existing chunks.

​
Rename a hypertable

You can change the name of a hypertable using the ALTER TABLE command. In this example, the hypertable is called conditions, and is being changed to the new name, weather: 
ALTER TABLE conditions
  RENAME TO weather;

​
Drop a hypertable

Drop a hypertable using a standard DROP TABLE command: 
DROP TABLE weather;
All data chunks belonging to the hypertable are deleted.