🗜️ Data Compression

🏗️ Why Column Stores Champion Compression

Row-oriented databases store one row's columns contiguously. Column-oriented databases store one column's values contiguously. That single difference changes everything for compression.

⚡ The ClickHouse Compression Philosophy

ClickHouse optimizes for compression ratio first, decompression speed second. In OLAP workloads, data is read far more often than it is written, and queries scan millions of rows. Getting more data per read is the primary goal.

🗜️ Generic Compression Algorithms

ClickHouse supports several general-purpose compression algorithms. These are the workhorses — they're the fallback when specialized codecs don't apply, and they often work very well as the final stage in a codec stack.

🔬 Specialized Codecs — The ClickHouse Secret Sauce

Generic compression treats data as opaque bytes. ClickHouse's specialized codecs understand the semantics of your data — timestamps increment, floats drift slowly, IDs share prefixes. This understanding lets them achieve dramatically better ratios than generic algorithms.

-- UUIDs don't compress well
CREATE TABLE events (
    event_id UUID DEFAULT,
    user_id UUID DEFAULT,
    payload String DEFAULT  -- already compressed external data
) ENGINE = MergeTree()
ORDER BY (event_id);

-- Delta(4) stores deltas as 4-byte integers
-- Good for timestamps where deltas fit in 4 bytes
CREATE TABLE sensor_events (
    timestamp DateTime64(3) CODEC(Delta(4)),
    sensor_id UInt32 CODEC(Delta(4)),
    temperature Float32 CODEC(Gorilla)
) ENGINE = MergeTree()
ORDER BY (sensor_id, timestamp);

-- DoubleDelta(4) for 4-byte timestamp deltas
-- Excellent for regular-interval time series
CREATE TABLE metrics (
    timestamp DateTime64(3) CODEC(DoubleDelta),
    metric_name LowCardinality(String),
    value Float64 CODEC(Gorilla)
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp);

-- DoubleDelta shines for DateTime64(3) at millisecond precision
-- Ratio improvement over raw: 10-50× typical

-- T64 as a preprocessing codec
-- Reorganizes bytes for better downstream compression
CREATE TABLE events (
    event_id UInt64 CODEC(T64, ZSTD(1)),
    user_id UInt64 CODEC(T64, LZ4),
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD(1))
) ENGINE = MergeTree()
ORDER BY event_id;

CREATE TABLE temperature_sensors (
    sensor_id UInt32,
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    temperature Float64 CODEC(Gorilla),       -- slowly varying → great with Gorilla
    humidity Float64 CODEC(Gorilla),         -- also slowly varying
    battery_voltage Float64 CODEC(Gorilla)  -- gradual discharge, Gorilla works too
) ENGINE = MergeTree()
ORDER BY (sensor_id, timestamp);

🔗 Codec Stacking — The ClickHouse Advantage

This is where ClickHouse's compression system gets really powerful. You can chain up to 4 codecs per column — each codec in the chain handles a specific pattern, transforming data progressively until the final generic codec squeezes out the last bits.

📏 Measuring Your Compression Ratios

ClickHouse provides several ways to inspect actual compression effectiveness. Use these queries to understand how your data is actually compressing and identify opportunities for improvement.

SELECT
    database,
    table,
    partition_id,
    name AS part_name,
    active,
    bytes_on_disk AS compressed_bytes,
    primary_key_bytes_in_memory AS pk_bytes,
    rows,
    ROUND(data_compressed_bytes / data_uncompressed_bytes, 3) AS compression_ratio,
    data_uncompressed_bytes,
    data_compressed_bytes
FROM system.parts
WHERE database = 'default' 
  AND table = 'events'
  AND active = 1
ORDER BY partition_id, name;

SELECT
    name AS column_name,
    data_type,
    codec_desc AS codec,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.columns
WHERE database = 'default'
  AND table = 'events'
  AND data_compressed_bytes > 0
ORDER BY data_uncompressed_bytes DESC;

-- Test different codecs on your data
SELECT
    codec,
    compressed_size,
    uncompressed_size,
    ROUND(compression_ratio, 3) AS ratio,
    encode_time_us,
    decode_time_us
FROM (
    SELECT *
    FROM testCompression('your_table')
    WHERE compression_method NOT LIKE 'Preliminary%'
)
ORDER BY compression_ratio DESC;

-- Find columns with poor compression (ratio < 2x)
SELECT
    database,
    table,
    name AS column_name,
    data_type,
    codec_desc,
    ROUND(data_uncompressed_bytes / NULLIF(data_compressed_bytes, 0), 2) AS ratio
FROM system.columns
WHERE database = 'default'
  AND table LIKE '%events%'
  AND data_compressed_bytes > 0
HAVING ratio < 2.0
ORDER BY ratio ASC;

🔑 Primary Key Columns and Compression

Primary key columns appear in every MergeTree data file — the primary key index (.idx), the primary key column data (.bin), and the mark file (.mrk). Compression on these columns has outsized impact.

⏭️ Data Skipping with Compressed Data

ClickHouse's data skipping relies on reading marks (offset pointers) and using the sparse primary key index to determine which granules (8192-row chunks) to decompress and scan. Compression doesn't break this — it makes it more important.

🏷️ Low Cardinality Columns — Built-In Efficiency

Low-cardinality columns (columns with few unique values relative to total rows) compress exceptionally well. ClickHouse has both codec-level optimizations and a dedicated LowCardinality data type that encodes values internally.

-- country_code has only ~200 unique values across 100M rows
CREATE TABLE events (
    event_id UInt64,
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    country_code LowCardinality(String),  -- internal dictionary encoding
    status_code UInt16 CODEC(Delta(2), ZSTD(1)),
    value Float64 CODEC(Gorilla)
) ENGINE = MergeTree()
ORDER BY (country_code, timestamp);

CREATE TABLE http_requests (
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    status_code Enum8('200' = 200, '301' = 301, '404' = 404, '500' = 500),
    request_count UInt64 CODEC(Delta(8), ZSTD(1))
) ENGINE = MergeTree()
ORDER BY timestamp;

-- Integer status codes: 200, 301, 404, 500 (4 unique values)
ALTER TABLE events MODIFY COLUMN status_code CODEC(Delta(2), ZSTD(1));

-- The Delta codec is highly effective for small integers
-- because tiny deltas compress much better than raw values

📊 Real-World Example: IoT Sensor Time Series

Complete table design for a sensor monitoring system, with codec selection for each column.

CREATE TABLE iot_sensors (
    -- Primary key columns: frequently used in WHERE, ORDER BY
    device_id UInt64 CODEC(Delta(8), ZSTD(1)),
    timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    
    -- Measurement values: slowly varying floats → Gorilla
    temperature Float64 CODEC(Gorilla, ZSTD(1)),
    pressure Float64 CODEC(Gorilla, ZSTD(1)),
    humidity Float64 CODEC(Gorilla, ZSTD(1)),
    battery_voltage Float64 CODEC(Gorilla, ZSTD(1)),
    
    -- Signal quality: 0-100 integer, low cardinality
    signal_quality UInt8 CODEC(Delta(1), ZSTD(1)),
    
    -- Location: LowCardinality string
    location_id LowCardinality(String),
    
    -- Error code: low cardinality integer → Delta
    error_code UInt16 CODEC(Delta(2), ZSTD(1)),
    
    -- Raw payload: high entropy JSON, ZSTD only option
    raw_payload String CODEC(ZSTD(3))
) ENGINE = MergeTree()
ORDER BY (device_id, timestamp)
PARTITION BY toYYYYMM(timestamp)
TTL timestamp + INTERVAL 90 DAY;

🏗️ MergeTree Storage and Compression Interaction

ClickHouse's MergeTree storage engine is where codec selection actually happens. Understanding how MergeTree stores data helps you make better codec choices.

📋 Codec Quick Reference