Skip to content

Basic compression with hypercore

Compress data into the columnstore for up to 98% storage savings and faster analytical queries

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.

Try this with your AI agent

In Tiger Cloud, set up Tiger MCP and ask your AI agent to do this for you. See example prompts to get started, and best practices to keep it safe.

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. Columnstore chunks still accept inserts, updates, and deletes directly, without needing to decompress them first.

Prerequisites for this tutorial

To follow these steps, you'll need:

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

Add a month of readings from ten devices, one row a minute each, so there is enough data to compress:

INSERT INTO conditions (time, location, device, temperature, humidity)
SELECT
time,
'office',
'sensor-' || device,
round((20 + 5 * sin(extract(epoch FROM time) / 3600) + random())::numeric, 1),
round((45 + 10 * cos(extract(epoch FROM time) / 7200) + random())::numeric, 1)
FROM generate_series(now() - INTERVAL '30 days', now(), INTERVAL '1 minute') AS time,
generate_series(1, 10) AS device;

The columnstore policy converts chunks that are older than one chunk interval, seven days by default. The oldest three weeks of this data are eligible; the newest week stays in the rowstore.

The policy runs once a day. To convert the eligible chunks now instead of waiting, find the policy's job ID and run it:

SELECT job_id FROM timescaledb_information.jobs
WHERE hypertable_name = 'conditions' AND proc_name = 'policy_compression';
CALL run_job(<JOB_ID>);

Then check how much space the columnstore saved on the converted chunks:

SELECT pg_size_pretty(before_compression_total_bytes) AS before,
pg_size_pretty(after_compression_total_bytes) AS after
FROM hypertable_columnstore_stats('conditions');

The result looks like this:

before | after
--------+---------
21 MB | 3632 kB
(1 row)

For the size of the whole hypertable, rowstore chunks included:

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');
  • 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 and writes work the same on both rowstore and columnstore data.