Basic compression with hypercore

Hypercore is TimescaleDB‘s hybrid row-columnar storage engine. It automatically compresses chunks from the rowstore into the columnstore, typically reducing storage by 90–98% while making analytical queries significantly faster.

How it works

When you create a hypertable with segmentby and orderby options, TimescaleDB automatically creates a columnstore policy that converts older chunks into columnar format. Recent data stays in the rowstore for fast inserts, while older data is compressed in the columnstore for efficient scans.

Enable hypercore

Prerequisites for this tutorial

To follow the procedure on this page, you'll need:

• A Tiger Cloud service with Real-time analytics or a running instance of self-hosted TimescaleDB
• Your connection details
• Any client that can run SQL (Tiger Console, psql, or your app’s SQL driver)

Create a hypertable with hypercore enabled using CREATE TABLE:

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'
);

This automatically:

• Creates a hypertable partitioned by time
• Sets device as the segment column for efficient filtering
• Orders data by time DESC for optimal compression and scan performance
• Creates a columnstore policy that compresses chunks after one chunk interval

Tips

Choose segmentby based on how you filter data (for example, by device, location, or user). Choose orderby based on your most common sort order (usually time descending). Lower cardinality segmentby columns give better compression.

Check compression results

After the columnstore policy runs, check how much space you’ve saved:

SELECT
  hypertable_size('conditions') AS total_size,
  pg_size_pretty(hypertable_size('conditions')) AS pretty_size;

For detailed per-chunk information:

SELECT * FROM chunks_detailed_size('conditions');

Benefits

• Storage savings: 90–98% compression ratios are common.
• Query performance: Analytical queries run faster on columnar data thanks to vectorized execution.
• Cost reduction: Less storage means lower cloud costs.
• Transparent: Queries work the same on both rowstore and columnstore data.

Next steps

• Understand hypercore: How the hybrid storage engine works.
• Set up hypercore: Optimize segmentby, orderby, and policies.
• Compression methods in hypercore: Algorithms used for each data type.