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.
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.
How it works
Section titled “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. Columnstore chunks still accept inserts, updates, and deletes directly, without needing to decompress them first.
Enable hypercore
Section titled “Enable hypercore”Prerequisites for this tutorial
To follow these steps, you'll need:
- A Tiger Cloud service, or a running instance of self-hosted TimescaleDB.
- 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
deviceas the segment column for efficient filtering - Orders data by
time DESCfor optimal compression and scan performance - Creates a columnstore policy that compresses chunks after one chunk interval
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.
Insert sample data
Section titled “Insert sample data”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.
Check compression results
Section titled “Check compression results”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.jobsWHERE 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 afterFROM 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');Benefits
Section titled “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 and writes work the same on both rowstore and columnstore data.
Next steps
Section titled “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.