Setup hypertables

See how to create, alter, and drop a hypertable.

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

The following diagram shows the workflow to follow when you create s.

Prerequisites

To follow the steps on this page:

• Create a target with Real-time 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, 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.segmentby = 'device',
   tsdb.orderby = 'time DESC'
);

When you create a using CREATE TABLE ... WITH ..., the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , 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 conversion, s 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 the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert s in a to the .

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 gets compressed in memory during ingestion with COPY statements. By writing the compressed batches immediately in the columnstore, the IO footprint is significantly lower. Also, the columnstore policy you set is less important, INSERT already produces compressed s.

This feature is a tech preview and not production-ready. Using this feature could lead to regressed query performance and/or storage ratio, if the ingested batches are not correctly ordered or are of too high cardinality.

To enable in-memory data compression during ingestion:

SET timescaledb.enable_direct_compress_copy=on;

Important facts:

• High cardinality use cases do not produce good batches and lead to degraded query performance.
• The columnstore is optimized to store 1000 records per batch, which is the optimal format for ingestion per segment by.
• WAL records are written for the compressed batches rather than the individual tuples.
• Currently only COPY is supported, INSERT will eventually follow.
• Best results are achieved for batch ingestion with 1000 records or more, upper boundary is 10,000 records.
• Continuous Aggregates are not supported at the moment.

Alter a hypertable

You can alter a , for example to add a column, by using the ALTER TABLE command. Some operations are not supported for with enabled. See Altering hypertables with columnstore enabled.

Add a column to a hypertable

You add a column to a using the ALTER TABLE command. In this example, the 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 s.

Rename a hypertable

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

ALTER TABLE conditions
  RENAME TO weather;

Change a column data type

You can change the data type of a column in a using the ALTER TABLE command. In this example, the temperature column data type is changed from DOUBLE PRECISION to NUMERIC:

ALTER TABLE conditions
  ALTER COLUMN temperature TYPE NUMERIC;

The following restrictions apply:

• You cannot change the type of segmentby columns.
• For time dimension columns, you can only change to TIMESTAMPTZ, TIMESTAMP, DATE, INTEGER (smallint, integer, or bigint), or UUID (UUIDv7 only).
• You cannot change the type of columns with custom partitioning functions.
• You cannot change the type of columns for s with enabled. See Altering hypertables with columnstore enabled for how to do it instead.
• For columns with statistics enabled, you can only change to integer or timestamp types. To change to other types, first disable statistics using disable_column_stats.

Drop a column

You can drop a column from a using the ALTER TABLE command. In this example, the humidity column is dropped from the conditions :

ALTER TABLE conditions
  DROP COLUMN humidity;

You cannot drop partitioning columns.

Drop a hypertable

Drop a using a standard DROP TABLE command:

DROP TABLE weather;

All data s belonging to the are deleted.