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

• Enable secondary indexing on all data you add to the columnstore Early access: TimescaleDB v2.18.0

  alter table metrics
     set access method hypercore,
     set (timescaledb.compress_orderby = 'created_at',
             timescaledb.compress_segmentby = 'location_id');

  Copy

• Enable secondary indexing on a chunk you are adding to the columnstore Early access: TimescaleDB v2.18.0

  alter table _timescaledb_internal._hyper_1_21_chunk
  set access method hypercore;

  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 }
);

Copy

Name	Type	Default	Required	Description
table_name	TEXT	-	✖	The hypertable to enable columstore for.
timescaledb.enable_columnstore	BOOLEAN	true	✖	Enable columnstore.
timescaledb.orderby	TEXT	Descending order on the time column in table_name.	✖	The order in which items are used in the columnstore. Specified in the same way as an ORDER BY clause in a SELECT query.
timescaledb.segmentby	TEXT	No segementation by column.	✖	Set the list of columns used to segment data in the columnstore for table. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate.
column_name	TEXT	-	✖	The name of the column to orderby or segmentby.
timescaledb.compress_chunk_time_interval	TEXT	-	✖	EXPERIMENTAL: reduce the total number of chunks in the columnstore for table. If you set compress_chunk_time_interval, chunks added to the columnstore are merged with the previous adjacent chunk within chunk_time_interval whenever possible. These chunks are irreversibly merged. If you call convert_to_rowstore, merged chunks are not split up. You can call compress_chunk_time_interval independently of other compression settings; timescaledb.enable_columnstore is not required.
interval	TEXT	-	✖	Set to a multiple of the chunk_time_interval for table.
SET ACCESS METHOD	TEXT	DEFAULT (heap)	✖	To enable indexing on the columnstore, set to hypercore after you create a hypertable. Early access: TimescaleDB v2.18.0