ALTER TABLE (hypercore)

Enable the columnstore for a hypertable.

After you have enabled the columnstore, either:

• add_columnstore_policy: create a job that automatically moves chunks in a hypertable to the columnstore at a specific time interval.
• convert_to_columnstore: manually add a specific chunk in a hypertable to the columnstore.

To enable the columnstore:

• Configure a hypertable that ingests device data to use the columnstore:

  In this example, the metrics hypertable is often queried about a specific device or set of devices. Segment the hypertable by device_id to improve query performance.

  ALTER TABLE metrics SET(
     timescaledb.enable_columnstore, 
     timescaledb.orderby = 'time DESC', 
     timescaledb.segmentby = 'device_id');

  Copy

• Specify the chunk interval without changing other columnstore settings:

  • Set the time interval when chunks are added to the columnstore:

    ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '24 hours');

    Copy

  • To disable the option you set previously, set the interval to 0:

    ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '0');

    Copy

The syntax is:

ALTER TABLE <table_name> SET (timescaledb.enable_columnstore,
   timescaledb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   timescaledb.segmentby = '<column_name> [, ...]',
   timescaledb.compress_chunk_time_interval='interval',
   SET ACCESS METHOD { new_access_method | DEFAULT },
   ALTER <column name> SET NOT NULL,
   ADD CONSTRAINT <constraint_name> UNIQUE (<column name>, ... ) 
);

Copy