--- title: ALTER TABLE (hypercore) | Tiger Data Docs description: Enable the columnstore for a hypertable. --- Since [2.18.0](https://github.com/timescale/timescaledb/releases/tag/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 RESET ();`. After you have enabled the columnstore, either: - [add\_columnstore\_policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md): create a [job](/docs/reference/timescaledb/jobs-automation/add_job/index.md) that automatically moves chunks in a hypertable to the columnstore at a specific time interval. - [convert\_to\_columnstore](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md): 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 SET (timescaledb.enable_columnstore, timescaledb.compress_orderby = ' [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]', timescaledb.compress_segmentby = ' [, ...]', timescaledb.sparse_index = '(), ()', timescaledb.compress_chunk_time_interval='interval', ALTER SET NOT NULL, ADD CONSTRAINT UNIQUE (, ... ) ); ``` | 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`](https://www.postgresql.org/docs/current/view-pg-stats.html) 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 compressed chunks. Requires setting `timescaledb.compress_orderby`. Supported index types include:- `bloom()`: a probabilistic index, effective for `=` filters. Cannot be applied to `timescaledb.compress_orderby` columns.- `minmax()`: stores min/max values for each compressed chunk. 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` | 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](/docs/reference/timescaledb/hypercore/convert_to_rowstore/index.md), 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](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md) 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](https://www.postgresql.org/docs/current/sql-altertable.html).