> ## 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. # Setup hypertables > See how to create, alter, and drop a hypertable. export const PG = 'Postgres'; export const HYPERTABLE_CAP = 'Hypertable'; export const COLUMNSTORE = 'columnstore'; export const TIMESCALE_DB = 'TimescaleDB'; export const HYPERTABLE = 'hypertable'; export const CHUNK = 'chunk'; export const SERVICE_LONG = 'Tiger Cloud service'; export const SELF_LONG = 'self-hosted TimescaleDB'; {HYPERTABLE_CAP} are designed for real-time analytics, they are {PG} tables that automatically partition your data by time. Typically, you partition {HYPERTABLE} on columns that hold time values. [Best practice is to use `timestamptz`][postgresql-timestamp] column type. However, you can also partition on `date`, `integer`, `timestamp` and [UUIDv7][uuidv7_functions] types. The following diagram shows the workflow to follow when you create {HYPERTABLE}s. ```mermaid theme={"dark"} %%{init: { 'theme':'base', 'themeVariables': { 'primaryColor':'#F4FF61', 'primaryTextColor':'#1a1a1a', 'primaryBorderColor':'#333', 'lineColor':'#777', 'secondaryColor':'#6D5AFF', 'secondaryTextColor':'#fff', 'secondaryBorderColor':'#333', 'tertiaryColor':'#4A90E2', 'tertiaryTextColor':'#fff', 'tertiaryBorderColor':'#333', 'noteBkgColor':'#F4FF61', 'noteTextColor':'#1a1a1a', 'noteBorderColor':'#333', 'background':'#fff', 'mainBkg':'#fff', 'fontFamily': "'Geist Mono', monospace", 'edgeLabelBackground':'#fff', 'labelColor':'#333', 'labelTextColor':'#333' }, 'flowchart': { 'padding': 30, 'htmlLabels': true, 'curve': 'stepAfter' } }}%% flowchart TD Start([Set up hypertable  ]) -->|New table| CreateNew[CREATE TABLE WITH  
tsdb.hypertable  ] Start -->|Existing table| Convert[create_hypertable  
with migrate_data=true  ] CreateNew --> Config[Configure:
- segmentby column
- orderby column
- chunk_interval  ] Convert --> Config Config --> Policy[Columnstore policy
automatically created  ] Policy --> Optional{Speed up
ingestion?} Optional -->|Yes| DirectCompress[Enable
direct_compress_copy  ] Optional -->|No| UseTable[Use hypertable  ] DirectCompress --> UseTable UseTable --> Maintain{Maintenance
needed?} Maintain -->|Add column| AddCol[ALTER TABLE
ADD COLUMN  ] Maintain -->|Rename| Rename[ALTER TABLE
RENAME TO  ] Maintain -->|Change type| ChangeType[ALTER TABLE
ALTER COLUMN TYPE  ] Maintain -->|Drop column| DropCol[ALTER TABLE
DROP COLUMN  ] Maintain -->|No changes| Continue[Continue using  ] AddCol --> UseTable Rename --> UseTable ChangeType --> UseTable DropCol --> UseTable Continue --> Done{Done?} Done -->|Keep using| UseTable Done -->|Remove| Drop[DROP TABLE  ] %% Primary path nodes - all white/blank backgrounds style Start fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style CreateNew fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style Convert fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style Config fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style Policy fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style Optional fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a style UseTable fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style Maintain fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a %% Maintenance operations (dashed) style AddCol fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style Rename fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style ChangeType fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style DropCol fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style Continue fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 %% Optional feature (dashed) style DirectCompress fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 %% End states style Done fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a style Drop fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 %% Connectors linkStyle default stroke:#777,stroke-width:1px %% API reference links click CreateNew "/api-reference/timescaledb/hypertables/create_table" "CREATE TABLE API reference" click Convert "/api-reference/timescaledb/hypertables/create_hypertable" "create_hypertable API reference" click Policy "/api-reference/timescaledb/hypercore/add_columnstore_policy" "add_columnstore_policy API reference" click AddCol "/api-reference/timescaledb/hypercore/alter_table" "ALTER TABLE API reference" click Rename "/api-reference/timescaledb/hypercore/alter_table" "ALTER TABLE API reference" click ChangeType "/api-reference/timescaledb/hypercore/alter_table" "ALTER TABLE API reference" click DropCol "/api-reference/timescaledb/hypercore/alter_table" "ALTER TABLE API reference" click Drop "https://www.postgresql.org/docs/current/sql-droptable.html" "PostgreSQL DROP TABLE documentation" ``` ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services [enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details ## Create a hypertable Create a [{HYPERTABLE}][hypertables-section] for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes], remember to `segmentby` the column you will use most often to filter your data: ```sql theme={"dark"} CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, device TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL ) WITH ( tsdb.hypertable, tsdb.segmentby = 'device', tsdb.orderby = 'time DESC' ); ``` When you create a {HYPERTABLE} using [`CREATE TABLE ... WITH ...`][hypertable-create-table], the default partitioning column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an interval equal to the value of the [`chunk_interval`][create_table_arguments], defined through `compress_after` in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the {COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries. You can customize this policy later using [`alter_job`][alter_job_samples]. However, to change `after` or `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy]. You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy To convert an existing table with data in it, call `create_hypertable` on that table with [`migrate_data` to `true`][api-create-hypertable-arguments]. However, if you have a lot of data, this may take a long time. ## Speed up data ingestion When you set `timescaledb.enable_direct_compress_copy` your data gets compressed in memory during ingestion with `COPY` statements. By writing the compressed batches immediately in the columnstore, the IO footprint is significantly lower. Also, the [columnstore policy][add_columnstore_policy] you set is less important, `INSERT` already produces compressed {CHUNK}s. This feature is a **tech preview** and not production-ready. Using this feature could lead to regressed query performance and/or storage ratio, if the ingested batches are not correctly ordered or are of too high cardinality. To enable in-memory data compression during ingestion: ```sql theme={"dark"} SET timescaledb.enable_direct_compress_copy=on; ``` **Important facts**: * High cardinality use cases do not produce good batches and lead to degraded query performance. * The columnstore is optimized to store 1000 records per batch, which is the optimal format for ingestion per segment by. * WAL records are written for the compressed batches rather than the individual tuples. * Currently only `COPY` is supported, `INSERT` will eventually follow. * Best results are achieved for batch ingestion with 1000 records or more, upper boundary is 10,000 records. * Continuous Aggregates are **not** supported at the moment. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy ## Alter a hypertable You can alter a {HYPERTABLE}, for example to add a column, by using the {PG} [`ALTER TABLE`][postgres-alter-table] command. Some operations are not supported for {HYPERTABLE} with {COLUMNSTORE} enabled. See [Altering hypertables with columnstore enabled][alter-schema]. ### Add a column to a hypertable You add a column to a {HYPERTABLE} using the `ALTER TABLE` command. In this example, the {HYPERTABLE} is named `conditions` and the new column is named `humidity`: ```sql theme={"dark"} ALTER TABLE conditions ADD COLUMN humidity DOUBLE PRECISION NULL; ``` If the column you are adding has the default value set to `NULL`, or has no default value, then adding a column is relatively fast. If you set the default to a non-null value, it takes longer, because it needs to fill in this value for all existing rows of all existing {CHUNK}s. ### Rename a hypertable You can change the name of a {HYPERTABLE} using the `ALTER TABLE` command. In this example, the {HYPERTABLE} is called `conditions`, and is being changed to the new name, `weather`: ```sql theme={"dark"} ALTER TABLE conditions RENAME TO weather; ``` ### Change a column data type You can change the data type of a column in a {HYPERTABLE} using the `ALTER TABLE` command. In this example, the `temperature` column data type is changed from `DOUBLE PRECISION` to `NUMERIC`: ```sql theme={"dark"} ALTER TABLE conditions ALTER COLUMN temperature TYPE NUMERIC; ``` The following restrictions apply: * You cannot change the type of `segmentby` columns. * For time dimension columns, you can only change to `TIMESTAMPTZ`, `TIMESTAMP`, `DATE`, `INTEGER` (smallint, integer, or bigint), or `UUID` (UUIDv7 only). * You cannot change the type of columns with custom partitioning functions. * You cannot change the type of columns for {HYPERTABLE}s with {COLUMNSTORE} enabled. See [Altering hypertables with columnstore enabled][alter-schema] for how to do it instead. * For columns with statistics enabled, you can only change to integer or timestamp types. To change to other types, first disable statistics using `disable_column_stats`. ### Drop a column You can drop a column from a {HYPERTABLE} using the `ALTER TABLE` command. In this example, the `humidity` column is dropped from the `conditions` {HYPERTABLE}: ```sql theme={"dark"} ALTER TABLE conditions DROP COLUMN humidity; ``` You cannot drop partitioning columns. ## Drop a hypertable Drop a {HYPERTABLE} using a standard {PG} [`DROP TABLE`][postgres-droptable] command: ```sql theme={"dark"} DROP TABLE weather; ``` All data {CHUNK}s belonging to the {HYPERTABLE} are deleted. [alter-schema]: /manage-data/schema-management/alter#altering-hypertables-with-columnstore-enabled [api-create-hypertable-arguments]: /api-reference/timescaledb/hypertables/create_hypertable#arguments [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [hypertables-section]: /manage-data/capabilities/hypertables/understand-hypertables [postgres-alter-table]: https://www.postgresql.org/docs/current/sql-altertable.html [postgres-droptable]: https://www.postgresql.org/docs/current/sql-droptable.html [postgresql-timestamp]: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29 [secondary-indexes]: /manage-data/capabilities/hypercore/secondary-indexes [uuidv7_functions]: /api-reference/timescaledb/uuid-functions