Migrate data to TimescaleDB from the same Postgres instance

Call CREATE TABLE to make a new table based on your existing table.

You can create your indexes at the same time, so you don't have to recreate them manually. Or you can create the table without indexes, which makes data migration faster.

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.

Insert data from the old table to the new table.

INSERT INTO new_table
  SELECT * FROM old_table;

Copy

If you created your new table without indexes, recreate your indexes now.