> ## 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 hypercore > Reduce your chunk size by up to 98% and speed up your queries by automatically converting data between the rowstore and columnstore export const CAGG = 'continuous aggregate'; export const SERVICE_LONG = 'Tiger Cloud service'; export const SELF_LONG = 'self-hosted TimescaleDB'; export const CHUNK = 'chunk'; export const HYPERCORE = 'hypercore'; export const COLUMNSTORE = 'columnstore'; export const ROWSTORE = 'rowstore'; export const HYPERTABLE = 'hypertable'; export const TIMESCALE_DB = 'TimescaleDB'; export const HYPERCORE_CAP = 'Hypercore'; [{HYPERCORE_CAP}][hypercore] is the hybrid row-columnar storage engine in {TIMESCALE_DB} used by {HYPERTABLE}s. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). {HYPERCORE_CAP} eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities. {HYPERCORE_CAP} dynamically stores data in the most efficient format for its lifecycle: ![Move from rowstore to columstore in hypercore][move-from-rowstore-to-columstore-in-hypercore] * **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the {ROWSTORE}, ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage. * **Columnar storage for analytical performance**: chunks are automatically compressed into the {COLUMNSTORE}, optimizing storage efficiency and accelerating analytical queries. Unlike traditional columnar databases, {HYPERCORE} allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database. [hypercore]: /manage-data/capabilities/hypercore/understand-hypercore [move-from-rowstore-to-columstore-in-hypercore]: https://assets.timescale.com/docs/images/hypercore_intro.svg When you convert {CHUNK}s from the {ROWSTORE} to the {COLUMNSTORE}, multiple records are grouped into a single row. The columns of this row hold an array-like structure that stores all the data. For example, data in the following {ROWSTORE} chunk: | Timestamp | Device ID | Device Type | CPU | Disk IO | | --------- | --------- | ----------- | ----- | ------- | | 12:00:01 | A | SSD | 70.11 | 13.4 | | 12:00:01 | B | HDD | 69.70 | 20.5 | | 12:00:02 | A | SSD | 70.12 | 13.2 | | 12:00:02 | B | HDD | 69.69 | 23.4 | | 12:00:03 | A | SSD | 70.14 | 13.0 | | 12:00:03 | B | HDD | 69.70 | 25.2 | Is converted and compressed into arrays in a row in the {COLUMNSTORE}: | Timestamp | Device ID | Device Type | CPU | Disk IO | | ------------------------------------------------------------- | ------------------- | ------------------------------- | ------------------------------------------- | ------------------------------------- | | \[12:00:01, 12:00:01, 12:00:02, 12:00:02, 12:00:03, 12:00:03] | \[A, B, A, B, A, B] | \[SSD, HDD, SSD, HDD, SSD, HDD] | \[70.11, 69.70, 70.12, 69.69, 70.14, 69.70] | \[13.4, 20.5, 13.2, 23.4, 13.0, 25.2] | Because a single row takes up less disk space, you can reduce your chunk size by up to 98%, and can also speed up your queries. This saves on storage costs, and keeps your queries operating at lightning speed. For an in-depth explanation of how {HYPERTABLE}s and {HYPERCORE} work, see the [Data model][data-model]. This page shows you how to get the best results when you set a policy to automatically convert {CHUNK}s in a {HYPERTABLE} from the {ROWSTORE} to the {COLUMNSTORE}. ## 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 The code samples in this page use the [crypto\_sample.zip][crypto_samplezip] data from [this key features tutorial][ingest-data]. ## Optimize your data with columnstore policies The following figure shows the workflow to follow when you setup {HYPERCORE}. ```mermaid theme={"dark"} %%{init: { 'theme':'base', 'themeVariables': { 'primaryColor':'#fff', 'primaryTextColor':'#1a1a1a', 'primaryBorderColor':'#333', 'lineColor':'#666', 'secondaryColor':'#fff', 'secondaryTextColor':'#1a1a1a', 'secondaryBorderColor':'#333', 'tertiaryColor':'#fff', 'tertiaryTextColor':'#1a1a1a', 'tertiaryBorderColor':'#333', 'noteBkgColor':'#fff', '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' }, /* Hover tint (best-effort; renderer may ignore themeCSS) */ 'themeCSS': ` .node rect:hover, .node polygon:hover, .node path:hover { fill: rgba(244, 255, 97, 0.18) !important; transition: fill 120ms ease-in-out; } ` }}%% graph TB A[Hypertable with time-series data  ] --> B{Define compression strategy} B --> C[Choose segmentby column  ] C --> D[Choose orderby column  ] D --> E[Enable columnstore  ] E -->|CREATE TABLE or ALTER TABLE| F[Columnstore enabled on hypertable  ] F --> G{Add conversion policy?} G -->|Yes| H[Set time interval  ] H -->|add_columnstore_policy| I[Policy scheduled  ] I --> J[Background job converts chunks] J --> K[Query optimized data  ] G -->|Manual conversion| L[Call convert_to_columnstore  ] L --> K M[Check compression stats  ] -.->|hypertable_columnstore_stats| J N[Pause policy  ] -.->|alter_job| I O[Remove policy  ] -.->|remove_columnstore_policy| I P[Disable columnstore  ] -.->|ALTER TABLE| F %% Border-only hierarchy %% - Primary nodes: thicker border (E, F, I) %% - Standard nodes: normal border (A, B, C, D, G, H, J, K, L) %% - Optional nodes: dashed + lighter border (M, N, O, P) style A fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style B fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a style C fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style D fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style E fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style F fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style I fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4 style G fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a style H fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style J fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style K fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style L fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4 style M fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style N fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style O fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 style P fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4 %% connectors linkStyle default stroke:#777,stroke-width:1px click E "/api-reference/timescaledb/hypertables/create_table" "CREATE TABLE API reference" click F "/manage-data/capabilities/hypercore/understand-hypercore" "Learn about hypercore" click H "/api-reference/timescaledb/hypercore/add_columnstore_policy" "add_columnstore_policy API reference" click L "/api-reference/timescaledb/hypercore/convert_to_columnstore" "convert_to_columnstore API reference" click M "/api-reference/timescaledb/informational-views/hypertable_columnstore_stats" "hypertable_columnstore_stats API reference" click N "/api-reference/timescaledb/jobs/alter_job" "alter_job API reference" click O "/api-reference/timescaledb/hypercore/remove_columnstore_policy" "remove_columnstore_policy API reference" ``` The compression ratio and query performance of data in the {COLUMNSTORE} is dependent on the order and structure of your data. Rows that change over a dimension should be close to each other. With time-series data, you `orderby` the time dimension. For example, `Timestamp`: | Timestamp | Device ID | Device Type | CPU | Disk IO | | --------- | --------- | ----------- | ----- | ------- | | 12:00:01 | A | SSD | 70.11 | 13.4 | This ensures that records are compressed and accessed in the same order. However, you would always have to access the data using the time dimension, then filter all the rows using other criteria. To make your queries more efficient, you segment your data based on the following: * The way you want to access it. For example, to rapidly access data about a single device, you `segmentby` the `Device ID` column. This enables you to run much faster analytical queries on data in the {COLUMNSTORE}. * The compression rate you want to achieve. The [lower the cardinality][cardinality-blog] of the `segmentby` column, the better compression results you get. When {TIMESCALE_DB} converts a {CHUNK} to the {COLUMNSTORE}, it automatically creates a different schema for your data. It also creates and uses custom indexes to incorporate the `segmentby` and `orderby` parameters when you write to and read from the {COLUMNSTORE}. To set up your hypercore automation: 1. **Connect to your service** In [Tiger Console][services-portal] open an SQL editor. You can also connect to your service using [psql][connect-using-psql]. 2. **Enable columnstore on a hypertable** For [efficient queries][secondary-indexes], remember to `segmentby` the column you will use most often to filter your data. For example: * **Hypertables**: [Use `CREATE TABLE` for a hypertable][hypertable-create-table] ```sql theme={"dark"} CREATE TABLE crypto_ticks ( "time" TIMESTAMPTZ, symbol TEXT, price DOUBLE PRECISION, day_volume NUMERIC ) WITH ( timescaledb.hypertable, timescaledb.segmentby='symbol', timescaledb.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 * **Continuous aggregates** 1. [Use `ALTER MATERIALIZED VIEW` for a continuous aggregate][compression_continuous-aggregate]: ```sql theme={"dark"} ALTER MATERIALIZED VIEW assets_candlestick_daily SET ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol' ); ``` Before you say "huh", a {CAGG} is a specialized {HYPERTABLE}. 2. Add a policy to convert {CHUNK}s to the {COLUMNSTORE} at a specific time interval: Create a [columnstore\_policy][add_columnstore_policy] that automatically converts {CHUNK}s in a {HYPERTABLE} to the {COLUMNSTORE} at a specific time interval. For example: ```sql theme={"dark"} CALL add_columnstore_policy('assets_candlestick_daily', after => INTERVAL '1d'); ``` {TIMESCALE_DB} is optimized for fast updates on compressed data in the {COLUMNSTORE}. To modify data in the {COLUMNSTORE}, use standard SQL. 3. **Check the columnstore policy** 1. View your data space saving: When you convert data to the {COLUMNSTORE}, as well as being optimized for analytics, it is compressed by more than 90%. This helps you save on storage costs and keeps your queries operating at lightning speed. To see the amount of space saved: ```sql theme={"dark"} SELECT pg_size_pretty(before_compression_total_bytes) AS before, pg_size_pretty(after_compression_total_bytes) AS after FROM hypertable_columnstore_stats('crypto_ticks'); ``` You see something like: | before | after | | ------ | ----- | | 194 MB | 24 MB | 2. View the policies that you set or the policies that already exist: ```sql theme={"dark"} SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression'; ``` See [`timescaledb_information.jobs`][informational-views]. 4. **Pause a columnstore policy** ```sql theme={"dark"} SELECT * FROM timescaledb_information.jobs WHERE proc_name = 'policy_compression' AND relname = 'crypto_ticks'; -- Select the JOB_ID from the results, then use it in the next command SELECT alter_job(, scheduled => false); ``` See [`alter_job`][alter_job]. 5. **Restart a columnstore policy** ```sql theme={"dark"} SELECT alter_job(, scheduled => true); ``` See [`alter_job`][alter_job]. 6. **Remove a columnstore policy** ```sql theme={"dark"} CALL remove_columnstore_policy('crypto_ticks'); ``` See [`remove_columnstore_policy`][remove_columnstore_policy]. 7. **Disable columnstore** If your table has {CHUNK}s in the {COLUMNSTORE}, you have to [convert the {CHUNK}s back to the {ROWSTORE}][convert_to_rowstore] before you disable the {COLUMNSTORE}. ```sql theme={"dark"} ALTER TABLE crypto_ticks SET (timescaledb.enable_columnstore = false); ``` See [`ALTER TABLE`][alter_table_hypercore]. ## Reference For integers, timestamps, and other integer-like types, data is compressed using [delta encoding][delta], [delta-of-delta][delta-delta], [simple-8b][simple-8b], and [run-length encoding][run-length]. For columns with few repeated values, [XOR-based][xor] and [dictionary compression][dictionary] is used. For all other types, [dictionary compression][dictionary] is used. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_job]: /api-reference/timescaledb/jobs/alter_job [alter_table_hypercore]: /api-reference/timescaledb/hypertables/alter_table [cardinality-blog]: https://www.tigerdata.com/blog/what-is-high-cardinality [compression_continuous-aggregate]: /api-reference/timescaledb/continuous-aggregates/alter_materialized_view [connect-using-psql]: /integrations/query-admin/psql [convert_to_rowstore]: /api-reference/timescaledb/hypercore/convert_to_rowstore [crypto_samplezip]: https://assets.timescale.com/docs/downloads/candlestick/crypto_sample.zip [data-model]: /about/whitepaper#data-model [delta]: /manage-data/capabilities/hypercore/compression-methods#delta-encoding [delta-delta]: /manage-data/capabilities/hypercore/compression-methods#delta-of-delta-encoding [dictionary]: /manage-data/capabilities/hypercore/compression-methods#dictionary-compression [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [ingest-data]: /tutorials/quick-start-with-hypertables#optimize-time-series-data-in-hypertables-with-hypercore [informational-views]: /api-reference/timescaledb/informational-views/jobs [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy [run-length]: /manage-data/capabilities/hypercore/compression-methods#run-length-encoding [secondary-indexes]: /manage-data/capabilities/hypercore/secondary-indexes [services-portal]: https://console.cloud.timescale.com/dashboard/services [simple-8b]: /manage-data/capabilities/hypercore/compression-methods#simple-8b [xor]: /manage-data/capabilities/hypercore/compression-methods#xor-based-compression