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',
   timescaledb.enable_segmentwise_recompression = 'ON' | 'OFF',
   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.
timescaledb.enable_segmentwise_recompression	TEXT	ON	✖	Set to OFF to disable segmentwise recompression on chunks in the columnstore. This can be beneficial for some user workloads where segmentwise recompression is slow, and full recompression is more performant.
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