Time-Series Compression and Columnar Storage

Scope and reader

This explainer is for engineers deciding whether and how to compress historical sensor data: BMS trends, meter readings and equipment telemetry kept for months or years. It explains the techniques and the decisions they force. Product set-up is covered elsewhere: the TimescaleDB explainer shows how to enable its columnstore, ClickHouse for telemetry covers MergeTree tables, and sizing storage for one-second telemetry explains why compressed engines must be measured separately.

Why time-series data compresses: the patterns, on real output

The script below takes eight one-minute zone CO2 readings with one missed sample and a status label, and computes the quantities that time-series encodings store instead of raw values. It is an illustration in plain SQL; nothing is compressed.

SET TIME ZONE 'UTC';

CREATE TABLE reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    status    text NOT NULL,
    PRIMARY KEY (point_id, ts)
);

-- Zone CO2 sampled every 60 s; the gateway skipped one sample at 08:04; the value holds steady for a few minutes
INSERT INTO reading VALUES
    (3, '2026-10-27 08:00+00', 452, 'ok'),
    (3, '2026-10-27 08:01+00', 452, 'ok'),
    (3, '2026-10-27 08:02+00', 452, 'ok'),
    (3, '2026-10-27 08:03+00', 455, 'ok'),
    (3, '2026-10-27 08:05+00', 455, 'ok'),
    (3, '2026-10-27 08:06+00', 455, 'ok'),
    (3, '2026-10-27 08:07+00', 461, 'ok'),
    (3, '2026-10-27 08:08+00', 461, 'stale');

-- Delta and delta-of-delta on the timestamp; delta on the value; bits that differ from the previous value (XOR)
SELECT to_char(ts, 'HH24:MI') AS ts,
       ts_delta_s,
       ts_delta_s - lag(ts_delta_s) OVER (ORDER BY ts) AS ts_delta_of_delta,
       value,
       value_delta,
       length(trim(BOTH '0' FROM xor_bits::text)) AS xor_meaningful_bits
FROM (
    SELECT ts, value,
           extract(epoch FROM ts - lag(ts) OVER w)::int AS ts_delta_s,
           value - lag(value) OVER w AS value_delta,
           ('x' || encode(float8send(value), 'hex'))::bit(64)
             # ('x' || encode(float8send(lag(value, 1, value) OVER w), 'hex'))::bit(64) AS xor_bits
    FROM reading
    WINDOW w AS (PARTITION BY point_id ORDER BY ts)
) d
ORDER BY ts;

  ts   | ts_delta_s | ts_delta_of_delta | value | value_delta | xor_meaningful_bits
-------+------------+-------------------+-------+-------------+---------------------
 08:00 |            |                   |   452 |             |                   0
 08:01 |         60 |                   |   452 |           0 |                   0
 08:02 |         60 |                 0 |   452 |           0 |                   0
 08:03 |         60 |                 0 |   455 |           3 |                   2
 08:05 |        120 |                60 |   455 |           0 |                   0
 08:06 |         60 |               -60 |   455 |           0 |                   0
 08:07 |         60 |                 0 |   461 |           6 |                   3
 08:08 |         60 |                 0 |   461 |           0 |                   0

SELECT value, count(*) AS run_length
FROM (
    SELECT value, ts,
           sum(CASE WHEN value = prev THEN 0 ELSE 1 END) OVER (ORDER BY ts) AS run_id
    FROM (SELECT value, ts, lag(value) OVER (ORDER BY ts) AS prev FROM reading) a
) b
GROUP BY run_id, value ORDER BY min(ts);

--  value | run_length
-- -------+------------
--    452 |          3
--    455 |          3
--    461 |          2

SELECT status, dense_rank() OVER (ORDER BY status) - 1 AS dictionary_code, count(*) AS occurrences
FROM reading GROUP BY status ORDER BY status;

--  status | dictionary_code | occurrences
-- --------+-----------------+-------------
--  ok     |               0 |           7
--  stale  |               1 |           1

Reading the output

• Timestamps: every delta is 60 seconds except across the missed sample, so the delta-of-delta is 0 everywhere except the two rows around the gap. TimescaleDB's documentation describes exactly this: at regular intervals delta-of-delta leaves a series of zeroes, which simple-8b and run-length encoding then store in very few bits.
• Values: most deltas are 0, and where the value changes, the XOR of the two 64-bit floats has only 2 or 3 meaningful bits between its leading and trailing zeros. XOR-based float compression (built on Facebook's Gorilla, as Tiger Data notes; ClickHouse's Gorilla codec likewise calculates the XOR with the previous value and writes it in compact binary form) stores roughly those few bits instead of 64. That is a simplification: real implementations also store control bits and the position and length of the meaningful bits, and the exact format differs between engines, so the column above shows the idea, not an encoded size.
• Runs: three runs describe eight values.
• Labels: two distinct status strings become the codes 0 and 1.

Noisy analogue signals behave differently. A temperature with random noise in the last decimal changes most low-order bits on every sample, so XOR finds less to remove; Tiger Data's documentation notes that floating-point numbers are usually harder to compress than integers. In our view, rounding values to the sensor's real precision before storage is likely to help in any engine that uses XOR or delta encodings.

Row storage, columnar storage and where compression happens

Row storage (PostgreSQL heap, MySQL InnoDB) keeps each reading's columns together, which suits inserting and updating single rows. Neighbouring values of one column are separated by the other columns, so the patterns above are invisible to a compressor working on pages. In PostgreSQL, compression of row data is in any case limited to TOAST, which the documentation says is triggered only when a row value is wider than about 2 kB; a narrow sensor row is around 50 bytes (from the page layout arithmetic, and the row size in an illustrative measurement on a small PGlite sample in the storage sizing article, not representative of your server).

Columnar storage keeps each column's values together for a block of rows. The encodings then see long runs of similar values, and a query that reads two columns does not read the others. The block is the unit of work: a batch of rows per segment in TimescaleDB's columnstore, a part and its column files in ClickHouse, a page within a column chunk in Parquet, a block of a series in an InfluxDB TSM file.

Many time-series systems combine the two: recent data is written to a write-friendly structure (the TimescaleDB rowstore, the InfluxDB in-memory cache and WAL) and converted or compacted into compressed columns as it ages. QuestDB stores columns natively from the start and documents converting partitions to Parquet as its preferred way to compress them. Either way, the conversion schedule is the main operational decision.

Design decisions

1. Sort and segment so that similar values sit together

Compression depends on what is adjacent. ClickHouse's guide names the ordering key, the data types and the codecs as the three principal factors, and explains that sorted values put identical values next to each other. TimescaleDB groups columnstore data by segmentby and orders it by orderby; its documentation advises choosing segmentby from the column you filter on, ordering by your most common sort order (usually time descending), and notes that lower-cardinality segment columns compress better; its own example segments by device and orders by time DESC. For BMS data, a reasonable starting point (our judgement, not a vendor recommendation for this data) is segmentby = point_id (or asset) and orderby = ts DESC, so each segment is one point's readings in time order. Test it against your filters and compression results.

Our inference, not a documented threshold: with thousands of points and short chunks, each segment may hold only a few readings, which is likely to compress poorly; the TimescaleDB chunk sizing page notes that very small chunks compress less effectively. Segmenting by asset or site instead of point, or longer chunks, are the levers to test, at the cost of reading a whole asset's segment when a query filters on one point. See choosing partition intervals.

2. Pick types and precision before codecs

In our view, smaller and more regular types generally help the encodings above: integer quality codes rather than text, timestamps at the precision the sensor really has, values rounded to the instrument's resolution, and dictionary-encoded types for repeated labels (QuestDB documents that its SYMBOL type uses dictionary encoding). ClickHouse's own worked example optimises types and the ordering key before it turns to codecs.

3. Choose codecs where the engine lets you

ClickHouse applies LZ4 by default in self-managed deployments and ZSTD in ClickHouse Cloud, and lets each column declare a codec chain. Its guidance: ZSTD as the general default, Delta-based codecs for monotonic sequences or small consecutive differences, Gorilla for gauge-like floating-point data, and T64 for sparse data or small ranges. It also notes that with ZSTD present other codecs rarely add much, so compare the choices on your own data using system.columns.

CREATE TABLE bms_reading
(
    point_id  UInt32,
    ts        DateTime      CODEC(DoubleDelta, ZSTD),
    value     Float64       CODEC(Gorilla, ZSTD),
    quality   UInt8         CODEC(T64, ZSTD)
)
ENGINE = MergeTree
ORDER BY (point_id, ts);

-- Compare compressed and uncompressed size per column
SELECT name,
       formatReadableSize(sum(data_compressed_bytes))   AS compressed_size,
       formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.columns
WHERE table = 'bms_reading'
GROUP BY name;

4. Decide when data becomes compressed

Converting too early puts writes, upserts and late data onto compressed storage; converting too late leaves weeks of data uncompressed. TimescaleDB's columnstore accepts inserts, updates, deletes and upserts directly, but Tiger Data describes the rowstore as the place for fast inserts and updates and converts data to the columnstore as it cools. In our judgement, a practical starting point for building data is to compress after the period in which late readings normally arrive and corrections are made, which the late-arriving data page helps you measure.

5. Use columnar exports for analytics

For portfolio analytics and machine learning, write older data out as Parquet into a lakehouse rather than keeping everything in the operational database. Parquet is a column-oriented file format; within a file each column chunk is split into pages, and a page is the unit of encoding and compression. Dictionary encoding falls back to plain encoding if the dictionary grows too large, so very high-cardinality string columns lose that benefit.

What compression does to queries

• Scans and aggregates can get cheaper because less data is read, and some engines answer from metadata: TimescaleDB documents that summary queries such as COUNT, MIN, MAX, FIRST and LAST read results from batch metadata, and that sparse minmax and bloom indexes skip batches without decompressing them.
• Point lookups and single-row changes can cost more: in a batch-encoded format one row's values are spread across encoded column batches, so reading or changing one reading may mean decoding a batch. That is our inference from how batch encoding works; the documentation we checked does not quantify it, and engines optimise it differently. Tiger Data states that its columnstore chunks accept inserts, updates and deletes directly, without needing to decompress them first, and that bloom indexes speed up UPDATE, DELETE and UPSERT on compressed data, so check the behaviour of your engine and version.
• Selecting fewer columns matters more: in columnar storage an unused column is not read at all, so SELECT * on a wide table reads more data than naming two columns.
• CPU replaces I/O: ClickHouse's guide states that the CPU overhead of compression is in most cases outweighed by the reduction in I/O, but on CPU-bound servers the balance can shift.

Worked example: one-second plant-room data

An illustrative scenario (the point count, rate and the 7-day and 90-day windows are assumptions for the example; set yours from how late data arrives, how long corrections are made and how long raw data must stay queryable): a plant room samples 300 chiller, pump and boiler points every second. Streaming (MQTT into Kafka) moves the data; the database stores and serves it; a lakehouse handles long-range analytics.

1. Days 0 to 7: rows in the TimescaleDB rowstore (or a ClickHouse table receiving batched inserts), where late readings from the gateway and manual corrections are cheap.
2. After 7 days: chunks converted to the columnstore, segmented by point_id and ordered by time; dashboards continue to query them with the same SQL.
3. Roll-ups: 1-minute and 15-minute aggregates maintained separately and kept for years; see retention and downsampling.
4. After 90 days: raw chunks exported to Parquet in object storage for the lakehouse, then dropped from the database.
5. Check: compare before and after sizes with the engine's own functions (for example hypertable_columnstore_stats() in TimescaleDB or system.columns in ClickHouse) on real chunks, and plan capacity on that, not on a published percentage.

Vendor figures, limitations and when not to compress

• Vendor figures: the vendor reports, in Tiger Data's basic compression documentation, that hypercore typically reduces storage by 90 to 98%. That is the vendor's own figure, not one reproduced here; other vendors publish examples on different data, and the numbers are not comparable across products. Your result depends on sampling regularity, noise, cardinality and sort order.
• Small data: our editorial view is that a building with a few hundred points at one-minute intervals can usually be stored in plain PostgreSQL rows, and that compression then adds an extension or engine to operate for a modest gain; check with your own volumes.
• Update-heavy data: if historical readings are corrected often, keep them uncompressed for longer or accept the higher cost of changing compressed batches.
• Hidden copies: compression reduces table size, but the write-ahead log, replicas, backups and exports each have their own size and format.
• Security and operations: compression changes nothing about access control; monitor conversion jobs and their lag, and alert if uncompressed data grows past the planned window.

For the performance side see Time-Series Database Performance; for product choice, ClickHouse vs TimescaleDB and the time-series database selection guide.

Frequently asked questions

Sources

• Tiger Data documentation: Compression methods in hypercore
• Tiger Data documentation: Understand hypercore
• Tiger Data documentation: Basic compression with hypercore
• Tiger Data documentation: Size hypertable chunks
• ClickHouse documentation: Compression in ClickHouse
• ClickHouse documentation: CREATE TABLE
• Apache Parquet documentation: Overview
• Apache Parquet documentation: Concepts
• Apache Parquet documentation: Encodings
• Apache Parquet documentation: Compression
• InfluxDB OSS v2 documentation: InfluxDB storage engine
• QuestDB documentation: Compression (ZFS)
• PostgreSQL 18 documentation: TOAST
• QuestDB documentation: Schema design
• ClickHouse documentation: Column compression codecs
• ClickHouse documentation: Table parts

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.