ALTER TABLE (hypercore)

enable the columnstore for a hypertable.

Since 2.18.0

Enable the or change the settings for a . The settings are applied on a per- basis. You do not need to convert the entire back to the before changing the settings. The new settings apply only to the s that have not yet been converted to , the existing s in the do not change. This means that s with different settings can co-exist in the same .

calculates default settings for each when it is created. These settings apply to each , and not the entire . To explicitly disable the defaults, set a setting to an empty string. To remove the current configuration and re-enable the defaults, call ALTER TABLE <your_table_name> RESET (<columnstore_setting>);.

After you have enabled the , either:

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

Samples

To enable the using ALTER TABLE:

• Configure a that ingests device data to use the :

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

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

• Specify the interval without changing other settings:

  • Set the time interval when s are added to the :

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

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

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

Arguments

The syntax is:

ALTER TABLE <table_name> SET (timescaledb.enable_columnstore,
   timescaledb.compress_orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   timescaledb.compress_segmentby = '<column_name> [, ...]',
   timescaledb.sparse_index = '<index>(<column_name>), <index>(<column_name>)'
   timescaledb.compress_chunk_time_interval='interval',
   ALTER <column name> SET NOT NULL,
   ADD CONSTRAINT <constraint_name> UNIQUE (<column name>, ... )
);

Name	Type	Default	Required	Description
table_name	TEXT	-	✖	The to enable columstore for.
timescaledb.enable_columnstore	BOOLEAN	true	✖	Set to false to disable .
timescaledb.compress_orderby	TEXT	Descending order on the time column in table_name.	✖	The order in which items are used in the . Specified in the same way as an ORDER BY clause in a SELECT query. Setting timescaledb.compress_orderby automatically creates an implicit min/max sparse index on the orderby column.
timescaledb.compress_segmentby	TEXT	looks at pg_stats and determines an appropriate column based on the data cardinality and distribution. If pg_stats is not available, looks for an appropriate column from the existing indexes.	✖	Set the list of columns used to segment data in the 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.sparse_index	TEXT	evaluates the columns you already have indexed, checks which data types are a good fit for sparse indexing, then creates a sparse index as an optimization.	✖	Configure the sparse indexes for compressed s. Requires setting timescaledb.compress_orderby. Supported index types include: bloom(<column_name>): a probabilistic index, effective for = filters. Cannot be applied to timescaledb.compress_orderby columns. minmax(<column_name>): stores min/max values for each compressed . Setting timescaledb.compress_orderby automatically creates an implicit min/max sparse index on the orderby column. Define multiple indexes using a comma-separated list. You can set only one index per column. Set to an empty string to avoid using sparse indexes and explicitly disable the default behavior. To remove the current sparse index configuration and re-enable default sparse index selection, call ALTER TABLE your_table_name RESET (timescaledb.sparse_index);.
timescaledb.compress_chunk_time_interval	TEXT	-	✖	EXPERIMENTAL: reduce the total number of s in the for table. If you set compress_chunk_time_interval, s added to the are merged with the previous adjacent within chunk_time_interval whenever possible. These s are irreversibly merged. If you call convert_to_rowstore, merged s 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.
ALTER	TEXT		✖	Set a specific column in the columnstore to be NOT NULL.
ADD CONSTRAINT	TEXT		✖	Add UNIQUE constraints to data in the columnstore.