Compress your data using hypercore

Timescale Cloud: Performance, Scale, Enterprise

Self-hosted products

MST

Over time you end up with a lot of data. Since this data is mostly immutable, you can compress it to save space and avoid incurring additional cost.

TimescaleDB is built for handling event-oriented data such as time-series and fast analytical queries, it comes with support of hypercore featuring the columnstore.

Hypercore enables you to store the data in a vastly more efficient format allowing up to 90x compression ratio compared to a normal PostgreSQL table. However, this is highly dependent on the data and configuration.

Hypercore is implemented natively in PostgreSQL and does not require special storage formats. When you convert your data from the rowstore to the columnstore, TimescaleDB uses PostgreSQL features to transform the data into columnar format. The use of a columnar format allows a better compression ratio since similar data is stored adjacently. For more details on the columnar format, see hypercore.

A beneficial side effect of compressing data is that certain queries are significantly faster, since less data has to be read into memory.

Optimize your data in the columnstore

To compress the data in the transactions table, do the following:

Connect to your Timescale Cloud service
In Timescale Console open an SQL editor. The in-Console editors display the query speed. You can also connect to your service using psql.
Convert data to the columnstore:
You can do this either automatically or manually:
- Automatically convert chunks in the hypertable to the columnstore at a specific time interval:
```
CALL add_columnstore_policy('transactions', after => INTERVAL '1d');
```
- Manually convert all chunks in the hypertable to the columnstore:
```
CALL convert_to_columnstore(c) from show_chunks('transactions') c;
```

Take advantage of query speedups

Previously, data in the columnstore was segmented by the block_id column value. This means fetching data by filtering or grouping on that column is more efficient. Ordering is set to time descending. This means that when you run queries which try to order data in the same way, you see performance benefits.

Connect to your Timescale Cloud service
In Timescale Console open an SQL editor. The in-Console editors display the query speed.

Run the following query:


WITH recent_blocks AS (
 SELECT block_id FROM transactions
 WHERE is_coinbase IS TRUE
 ORDER BY time DESC
 LIMIT 5
)
SELECT
 t.block_id, count(*) AS transaction_count,
 SUM(weight) AS block_weight,
 SUM(output_total_usd) AS block_value_usd
FROM transactions t
INNER JOIN recent_blocks b ON b.block_id = t.block_id
WHERE is_coinbase IS NOT TRUE
GROUP BY t.block_id;

Performance speedup is of two orders of magnitude, around 15 ms when compressed in the columnstore and 1 second when decompressed in the rowstore.