> ## 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.

# Hypercore

> Reference information about the TimescaleDB hybrid row-columnar storage engine

export const HYPERTABLE_CAP = 'Hypertable';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const PG = 'Postgres';

export const COLUMNSTORE = 'columnstore';

export const ROWSTORE = 'rowstore';

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERCORE = 'hypercore';

export const HYPERCORE_CAP = 'Hypercore';

{HYPERCORE_CAP} is a hybrid row-columnar storage engine in {TIMESCALE_DB}. It is designed specifically for
real-time analytics and powered by time-series data. The advantage of {HYPERCORE} is its ability
to seamlessly switch between row-oriented and column-oriented storage, delivering the best of both worlds:

![Hypercore workflow](https://assets.timescale.com/docs/images/hypertable-with-hypercore-enabled.png)

{HYPERCORE_CAP} solves the key challenges in real-time analytics:

* High ingest throughput
* Low-latency ingestion
* Fast query performance
* Efficient handling of data updates and late-arriving data
* Streamlined data management

{HYPERCORE_CAP}'s hybrid approach combines the benefits of row-oriented and column-oriented formats:

* **Fast ingest with {ROWSTORE}**: new data is initially written to the {ROWSTORE}, which is optimized for
  high-speed inserts and updates. This process ensures that real-time applications easily handle
  rapid streams of incoming data. Mutability—upserts, updates, and deletes happen seamlessly.

* **Efficient analytics with {COLUMNSTORE}**: as the data **cools** and becomes more suited for
  analytics, it is automatically converted to the {COLUMNSTORE}. This columnar format enables
  fast scanning and aggregation, optimizing performance for analytical workloads while also
  saving significant storage space.

* **Faster queries on compressed data in {COLUMNSTORE}**: in the {COLUMNSTORE} conversion, {HYPERTABLE}
  {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries. Combined with [chunk skipping][chunk-skipping], this helps you save on storage costs and keeps your queries operating at lightning speed.

* **Fast modification of compressed data in {COLUMNSTORE}**: just use SQL to add or modify data in the {COLUMNSTORE}.
  {TIMESCALE_DB} is optimized for superfast INSERT and UPSERT performance.

* **Full mutability with transactional semantics**: regardless of where data is stored,
  {HYPERCORE} provides full ACID support. Like in a vanilla {PG} database, inserts and updates
  to the {ROWSTORE} and {COLUMNSTORE} are always consistent, and available to queries as soon as they are
  completed.

For an in-depth explanation of how {HYPERTABLE}s and {HYPERCORE} work, see the [Data model][data-model].

[chunk-skipping]: /manage-data/capabilities/hypertablesimprove-query-performance/

[data-model]: /about/whitepaper/#data-model

## Samples

Best practice for using {HYPERCORE} is to:

1. **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:

   * **{HYPERTABLE_CAP}s**:

     Use [`CREATE TABLE`][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'
     );
     ```

     For {TIMESCALE_DB} [v2.23.0][tsdb-release-2-23-0] and higher, the table is automatically partitioned on the first column
     in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column,
     {TIMESCALE_DB} throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a
     time column.

     If you are self-hosting {TIMESCALE_DB} [v2.20.0][tsdb-release-2-23-0] to [v2.22.1][tsdb-release-2-23-0], to convert your
     data to the {COLUMNSTORE} after a specific time interval, you have to call [add\_columnstore\_policy][add_columnstore_policy] after you call
     [CREATE TABLE][hypertable-create-table]

     If you are self-hosting {TIMESCALE_DB} [v2.19.3][tsdb-release-2-19-3] and below, create a [{PG} relational table][pg-create-table],
     then convert it using [create\_hypertable][create_hypertable]. You then enable {HYPERCORE} with a call
     to [ALTER TABLE][alter_table_hypercore].

     [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html

     [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable

     [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table

     [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

     [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

     [chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval

     [tsdb-release-2-23-0]: https://github.com/timescale/timescaledb/releases/tag/2.23.0

     [tsdb-release-2-20-0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0

     [tsdb-release-2-22-1]: https://github.com/timescale/timescaledb/releases/tag/2.22.1

     [tsdb-release-2-19-3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3

   * **{CAGG_CAP}s**:

   1. [Use `ALTER MATERIALIZED VIEW` for a {CAGG}][compression_continuous-aggregate]:
      ```sql theme={"dark"}
      ALTER MATERIALIZED VIEW assets_candlestick_daily set (
         timescaledb.enable_columnstore = true,
         timescaledb.segmentby = 'symbol');
      ```

   2. 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.

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].

You can also [convert\_to\_columnstore][convert_to_columnstore] and [convert\_to\_rowstore][convert_to_rowstore] manually
for more fine-grained control over your data.

## Limitations

{CHUNK}s in the {COLUMNSTORE} have the following limitations:

* `ROW LEVEL SECURITY` is not supported on {CHUNK}s in the {COLUMNSTORE}.

## Available functions

### Policies

* [`add_columnstore_policy()`][add_columnstore_policy]: set a policy to automatically move {CHUNK}s in a {HYPERTABLE}
  to the {COLUMNSTORE} when they reach a given age
* [`remove_columnstore_policy()`][remove_columnstore_policy]: remove a {COLUMNSTORE} policy from a {HYPERTABLE}

### Configuration

* [`ALTER TABLE (hypercore)`][alter_table_hypercore]: enable the {COLUMNSTORE} for a {HYPERTABLE}

### Manual conversion

* [`convert_to_columnstore()`][convert_to_columnstore]: manually add a {CHUNK} to the {COLUMNSTORE}
* [`convert_to_rowstore()`][convert_to_rowstore]: move a {CHUNK} from the {COLUMNSTORE} to the {ROWSTORE}

### Statistics and information

* [`chunk_columnstore_stats()`][chunk_columnstore_stats]: get statistics about {CHUNK}s in the {COLUMNSTORE}
* [`hypertable_columnstore_stats()`][hypertable_columnstore_stats]: get {COLUMNSTORE} statistics related to the

{COLUMNSTORE}

* [`timescaledb_information.chunk_columnstore_settings`][chunk_columnstore_settings]: get information about settings on
  each {CHUNK} in the {COLUMNSTORE}
* [`timescaledb_information.hypertable_columnstore_settings`][hypertable_columnstore_settings]: get information about
  {COLUMNSTORE} settings for all {HYPERTABLE}s

[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

[alter_job]: /api-reference/timescaledb/jobs-automation/alter_job

[alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table

[chunk_columnstore_settings]: /api-reference/timescaledb/hypercore/chunk_columnstore_settings

[chunk_columnstore_stats]: /api-reference/timescaledb/hypercore/chunk_columnstore_stats

[compression_continuous-aggregate]: /api-reference/timescaledb/continuous-aggregates/alter_materialized_view

[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

[convert_to_rowstore]: /api-reference/timescaledb/hypercore/convert_to_rowstore

[hypercore]: /manage-data/capabilities/hypercore/hypercore

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[hypertable_columnstore_settings]: /api-reference/timescaledb/hypercore/hypertable_columnstore_settings

[hypertable_columnstore_stats]: /api-reference/timescaledb/hypercore/hypertable_columnstore_stats

[hypertables-section]: /manage-data/capabilities/hypertables

[informational-views]: /api-reference/timescaledb/informational-views/jobs

[remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

[secondary-indexes]: /manage-data/capabilities/hypercore/secondary-indexes

[tsdb-2.18.0]: https://github.com/timescale/timescaledb/releases/tag/2.18.0