ALTER TABLE (hypercore)

Since 2.18.0

ALTER TABLE (hypercore) replaces ALTER TABLE (Compression), deprecated in 2.18.0.

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

TimescaleDB calculates default columnstore settings for each chunk when it is created. These settings apply to each chunk, and not the entire hypertable. 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 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.

Samples

To enable the columnstore using ALTER TABLE:

• 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');

• 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');

  • 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 hypertable to enable columstore for.
timescaledb.enable_columnstore	BOOLEAN	true	✖	Set to false to disable columnstore.
timescaledb.compress_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. Setting timescaledb.compress_orderby automatically creates an implicit min/max sparse index on the orderby column.
timescaledb.compress_segmentby	TEXT	TimescaleDB looks at pg_stats and determines an appropriate column based on the data cardinality and distribution. If pg_stats is not available, TimescaleDB looks for an appropriate column from the existing indexes.	✖	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.sparse_index	TEXT	TimescaleDB 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 columnstore chunks. Requires setting timescaledb.compress_orderby. Aliases: timescaledb.index, timescaledb.compress_index, timescaledb.compress_sparse_index. 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 columnstore batch. 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. When a query filters on multiple columns that have bloom indexes, TimescaleDB automatically uses composite bloom filters to push multi-column predicates directly into the columnstore scan for both SELECT and UPSERT operations. 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	INTERVAL	-	✖	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.
ALTER	TEXT		✖	Set a specific column in the columnstore to be NOT NULL.
ADD CONSTRAINT	TEXT		✖	Add UNIQUE constraints to data in the columnstore.

Returns

For standard ALTER TABLE return behavior, see the PostgreSQL ALTER TABLE documentation.