> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# ALTER TABLE (hypercore)
> Enable the columnstore for a hypertable.
export const TIMESCALE_DB = 'TimescaleDB';
export const CHUNK = 'chunk';
export const HYPERTABLE = 'hypertable';
export const ROWSTORE = 'rowstore';
export const COLUMNSTORE = 'columnstore';
Since [2.18.0][tsdb-2.18.0]
Community
`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 {CHUNK}s that have not yet been converted to {COLUMNSTORE}, the existing {CHUNK}s in the
{COLUMNSTORE} do not change. This means that {CHUNK}s with different {COLUMNSTORE} settings can co-exist in the
same {HYPERTABLE}.
{TIMESCALE_DB} 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][add_columnstore_policy]: create a [job][job] that automatically moves {CHUNK}s in a
{HYPERTABLE} to the {COLUMNSTORE} at a
specific time interval.
* [convert\_to\_columnstore][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.
```sql theme={"dark"}
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 {CHUNK}s are added to the {COLUMNSTORE}:
```sql theme={"dark"}
ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '24 hours');
```
* To disable the option you set previously, set the interval to 0:
```sql theme={"dark"}
ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '0');
```
## Arguments
The syntax is:
```sql theme={"dark"}
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 | {TIMESCALE_DB} looks at [`pg_stats`][pgstats] and determines an appropriate column based on the data cardinality and distribution. If `pg_stats` is not available, {TIMESCALE_DB} 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 | {TIMESCALE_DB} 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 {CHUNK}s. 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 {CHUNK}s in the {COLUMNSTORE} for `table`. If you set `compress_chunk_time_interval`, {CHUNK}s added to the {COLUMNSTORE} are merged with the previous adjacent {CHUNK} within `chunk_time_interval` whenever possible. These {CHUNK}s are irreversibly merged. If you call [convert\_to\_rowstore][convert_to_rowstore], merged {CHUNK}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][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][postgres-alter-table].
[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy
[bloom-filters]: https://en.wikipedia.org/wiki/Bloom_filter
[chunk_time_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval
[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore
[convert_to_rowstore]: /api-reference/timescaledb/hypercore/convert_to_rowstore
[create-hypertable]: /api-reference/timescaledb/hypertables/create_hypertable
[default_table_access_method]: https://www.postgresql.org/docs/17/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD
[job]: /api-reference/timescaledb/jobs-automation/add_job
[pgstats]: https://www.postgresql.org/docs/current/view-pg-stats.html
[postgres-alter-table]: https://www.postgresql.org/docs/current/sql-altertable.html
[tsdb-2.18.0]: https://github.com/timescale/timescaledb/releases/tag/2.18.0