Schema & compression

The cheapest byte is the one you never store. ClickHouse gives you per-column codecs, LowCardinality, skipping indexes and projections — but defaults rarely use them well. This playbook shows how to find columns and tables paying a hidden tax, and the exact DDL to fix them.

Symptoms

• Tables are larger on disk than the data really warrants.
• Low compression ratios on numeric, timestamp or repetitive string columns.
• Filters on non-primary-key columns force full-table scans.
• Common aggregations re-read the whole table every time.

Step 1 — Find poorly compressed columns

`system.columns` exposes compressed and uncompressed bytes per column. A low ratio means the column is a codec candidate — numeric and timestamp columns especially can compress 2–5× better with the right codec.

SELECT
    table,
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS comp,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncomp,
    round(sum(data_uncompressed_bytes)
        / sum(data_compressed_bytes), 1)             AS ratio
FROM system.columns
WHERE database = currentDatabase()
  AND data_compressed_bytes > 0
GROUP BY table, name
ORDER BY sum(data_compressed_bytes) DESC
LIMIT 30;

Pick the right codec

-- Apply a codec to an existing column (rewrites on next merge)
ALTER TABLE events
    MODIFY COLUMN created_at DateTime CODEC(Delta, ZSTD);

ALTER TABLE metrics
    MODIFY COLUMN value Float64 CODEC(Gorilla, ZSTD);

Step 2 — LowCardinality for repetitive strings

A string column with few distinct values (status, country, plan, source) stored as plain String repeats the same text on every row. LowCardinality dictionary-encodes it, shrinking storage and speeding up GROUP BY and filters.

-- Check distinct count first — LowCardinality wins under ~100k uniques
SELECT uniqExact(status) FROM events;

ALTER TABLE events
    MODIFY COLUMN status LowCardinality(String);

Step 3 — Skipping indexes for non-PK filters

When you frequently filter on a column that is not in the primary key, a data-skipping index lets ClickHouse skip granules that cannot match — without a full scan.

-- minmax for range filters, bloom_filter for equality on high-cardinality
ALTER TABLE events
    ADD INDEX idx_user user_id TYPE bloom_filter GRANULARITY 4;

ALTER TABLE events
    ADD INDEX idx_amount amount TYPE minmax GRANULARITY 4;

-- Build it on existing data
ALTER TABLE events MATERIALIZE INDEX idx_user;

Step 4 — Projections for hot read patterns

If one aggregation or a different sort order is read constantly, a projection stores a pre-sorted / pre-aggregated copy inside the table. ClickHouse picks it automatically when it serves the query.

ALTER TABLE events
    ADD PROJECTION p_by_user
    (
        SELECT user_id, count(), sum(amount)
        GROUP BY user_id
    );

ALTER TABLE events MATERIALIZE PROJECTION p_by_user;

How OptiHouse automates this