Choosing ClickHouse compression codecs to cut storage without slowing queries
Codecs are set per column in ClickHouse, so the question is not whether to compress a table but which codec each column should use. A general-purpose default such as LZ4 or ZSTD is usually the starting point. Specialized codecs such as Delta, DoubleDelta and Gorilla apply to specific data shapes, especially monotonic timestamps and slowly changing time series values, and they only pay off when the column actually has that shape. The right choice is therefore decided by measurement on each column, not by one setting for the whole table.
Start by measuring what the cluster stores today. Query the system.columns table for compressed and uncompressed bytes per column, and note which columns make up most of the on-disk size. Then test candidate codecs on a copy of representative data, not on synthetic rows. For each candidate, record the resulting size, the time to insert, and the time to run the queries that read that column. Keep the heavier codec only where the storage saving is meaningful for the workload. The ClickHouse documentation on column codecs is the natural next read for the exact codec names and their parameters.
Teams usually need this work when storage growth is a real cost driver, such as a log or telemetry table that grows every day and keeps months of history. Codec choice is one lever among several. Retention rules, tiering to cheaper disks, rollups of old detail and schema design, including the ordering key, often change the storage bill more than a codec change does. Codecs also interact with those levers, because a column that compresses well in a recent partition may behave differently after a rollup or a move to another volume. Before committing a codec change to production, confirm three things with your own team: the table's insert rate and whether inserts would slow under a heavier codec, the CPU headroom on the nodes that serve merges and queries, and how the change will reach existing data. A codec applies to newly written parts, so existing data keeps its old encoding until it is rewritten. Plan that rewrite, test it on a spare cluster and record the before and after numbers so the decision can be reviewed later.
Which codec fits which kind of column
This table matches each codec family to the column types it suits, the compression and CPU trade-off it brings, and when the default is the better choice. Read it before changing codecs on large tables, then measure per column on representative data.
| Codec | Suits which columns | Compression and CPU trade-off | When to keep the default |
|---|---|---|---|
| LZ4 (default) | General text, strings, categorical values and mixed columns where write speed and low CPU matter most | Fastest to write and read, with the lightest CPU load and a modest size reduction | Keep it for hot ingestion paths, high-cardinality strings and columns that are queried constantly |
| ZSTD at default level | Text, JSON-like payloads, enum-like strings and columns that repeat values over long ranges | Smaller output than LZ4 with a moderate increase in CPU on writes and merges, usually small on reads | Use it for older or colder partitions where storage matters more than merge speed |
| ZSTD at higher levels | Large, rarely read archival columns with long repeated patterns | Each higher level adds more CPU on writes and background merges for diminishing size gains | Avoid on tables that merge heavily or sit on the write path; confirm supported levels for your version |
| Delta | Monotonically increasing or steadily changing integers and timestamps, such as sequence numbers and event times | Turns small differences into compact values; cheap on CPU, most effective when chained with a general codec | Keep the default if values jump irregularly or the column is not ordered by time |
| DoubleDelta | Timestamps and counters whose step size is nearly constant, such as regular scrape or sample intervals | Stores changes in the rate of change, which can shrink regular time series further; needs data with steady intervals | Keep the default for irregular event times or values with no consistent step |
| Gorilla | Floating-point readings from sensors or metrics that change gradually between adjacent samples | Targets XOR-style differences between consecutive floats; efficient for slowly changing series and less so for noisy or random values | Keep the default for random, scientific or rapidly changing floats where the gain is unlikely to justify testing |
| Chained codecs | Time series columns that benefit from a specialized pre-transform followed by a general codec | Specialized codecs usually reduce the input first, then a general codec such as ZSTD compresses the result; CPU cost is the sum of both | Use a single codec when the column is not time or numeric in shape, or when measurements show no gain |
| Measurement before change | Any column you plan to recode on a production table | Compare sizes and merge times on a copy with representative data, reading system tables per column | Keep existing codecs until a test shows a clear gain for the workload's read and write mix |
Measuring compression per column before changing a codec
This sequence measures how much each column compresses today, tests candidate codecs against a copy of representative data, and confirms that results and performance hold before any production table changes.
Select a representative data sample and queries
The platform owner and the analysts who use the table choose a slice of real data that covers the normal range of values, key cardinality and time span, along with the queries that matter most for latency. Keep the sample in the same environment as production for access rules, but load it only into a pre-production cluster. The step is done when the team has a written list of the sample's time range, row count and the top queries, each with its expected result, so later comparisons have a fixed baseline.
Load the baseline table with the current codecs
Create a pre-production table that uses the same schema, ordering key, partitioning and codecs as production, then insert the sample. Record the table definition exactly as it was created, including the codec on every column, so the baseline can be rebuilt later. Confirm that the row count in the new table matches the source and that the table reports the expected number of active parts. The step is done when the baseline table is loaded and its definition is saved in version control.
Read per-column sizes from system tables
Query the system tables that report column storage, such as the parts column statistics, and sum the compressed and uncompressed bytes for each column of the active parts. Table and column names vary between ClickHouse versions, so confirm them against the documentation for the version in use before writing the query. Sort the output by compressed size to find which columns dominate storage. The step is done when the team has a per-column table showing compressed bytes, uncompressed bytes and the resulting ratio.
Create candidate tables with alternative codecs
Create one copy of the baseline table for each candidate codec, changing only the codec on the column under test. Pair general-purpose choices such as LZ4 and ZSTD at different levels with specialized codecs such as Delta, DoubleDelta and Gorilla only where the column's data pattern suits them. Keep one variable per copy so differences can be traced to a single change. The step is done when each candidate table exists with a saved definition that differs from the baseline in only the intended column.
Load identical data into each candidate
Insert the same sample into every candidate table using the same ingestion method and batch sizes as production, and time each insert. After loading, run the merges that production relies on, or force them in the test environment, and record how long they take and how much CPU they use. Compare the resulting per-column sizes with the baseline from the earlier query. The step is done when the team has a table of compressed size, insert duration and merge duration for every candidate.
Verify row counts and query results
Before accepting any codec, confirm that each candidate holds the same number of rows as the baseline and that every representative query returns identical results. For aggregates, compare the returned values exactly, and for row-level queries, compare checksums of the sorted output. Any mismatch stops that candidate until the cause is found. The step is done when each candidate passes every check and the failures, if any, are documented with the reason for rejection.
Time representative queries on each candidate
Run the representative queries against each passing candidate under conditions close to production, repeating runs and clearing caches between them so that results are comparable. Record query duration and CPU time, paying attention to queries that decompress heavily used columns. Heavier codecs can reduce storage while increasing read CPU, so the trade-off should be visible in these numbers. The step is done when the team can state, for each candidate, the storage saved alongside the change in query and insert cost.
Review results before changing production
Bring the size, insert, merge, correctness and query timings to the people who own the table, the platform and the downstream consumers. Agree on the codec for each column, and write down the rollback step, which is to keep the existing table definition and data available until the change is reviewed. Schedule the production change only after this review is signed off. The step is done when a written decision records the chosen codecs, the evidence behind them and the rollback plan.
Where the CPU cost of heavier codecs actually shows up
A heavier codec costs CPU in three places, and each one lands on a different part of your workload. The first is inserts: every block of a column is compressed as it is written into a new data part, so a ZSTD level set high raises the CPU needed per ingested row. The second is background merges, where ClickHouse reads existing parts, decompresses them, merges the rows and compresses the result again. Merges run continuously on busy tables, so a heavy codec can keep cores occupied long after the original insert finished. The third is reads, where queries decompress only the column blocks they touch. Codecs such as Delta, DoubleDelta and Gorilla add work mainly during decoding, while general-purpose ZSTD levels add more decompression cost when a query scans many blocks.
The trade-off depends on how the table is used. A heavier codec is usually defensible for cold or rarely queried data, such as partitions that are older than the dashboards that depend on them, because the extra CPU is spent once during a merge and the savings are paid back in disk and I/O. The same codec is a poor choice for a hot table that ingests at high rates or serves constant queries, since the cost recurs on every insert, merge and scan. Before you set a level on a hot table, identify which partitions are merged most often and which queries read the widest column ranges, because those paths determine where the CPU goes.
Whether the trade-off is worth it depends on the CPU headroom on your actual servers and on the size of the query workload. Measure on your own hardware, with representative ingest rates and your real query mix, and compare insert throughput, merge duration, CPU utilization and query latency for each candidate codec. Ask any consultant or vendor to show the measurements behind a recommendation, including the hardware, data sample, ClickHouse version and workload used to produce them. A recommendation without those measurements does not tell you what your cluster will do.
Operational risks when codecs change on live data
Changing a codec on a production table is a schema change with data consequences. The four risks below affect how new writes are encoded, how existing data can be recovered, how replicas stay consistent, and who is allowed to make the change.
New writes use the new codec
A codec change in the table definition governs how new data is written. Existing data parts keep their original encoding until a merge or an explicit rewrite replaces them, so storage savings arrive gradually, not on the day of the change. Confirm the exact rewrite behavior for the ClickHouse version you run, and check how long a rewrite of your largest partitions will take in a pre-production copy with representative data.
Backup and test restore first
Any operation that rewrites data needs a restorable backup taken beforehand, not just a backup job that reports success. ClickHouse documents that a backup never test-restored will probably not restore properly when needed. Restore the backup to a spare cluster, confirm row counts and checksums for the affected tables, and only then start the rewrite. Keep that restore record with the change ticket.
Consistent change across replicas
On ReplicatedMergeTree tables, each replica holds its own copy of the parts, and the table definition must match across the cluster. Apply the change in a controlled order, confirm every replica reports the same definition and replication queue is empty, and check that missing data is synced from a healthy replica after any restart. A mismatched definition across replicas is the failure to catch before traffic resumes.
Control who alters table definitions
A codec change is an ALTER TABLE statement, and it should be limited to a small set of accounts. Restrict schema-changing grants through roles, remove or restrict the default user, and require that every change is traceable to a named person and ticket. Record who ran the change and when, so that a later rollback or investigation starts from evidence rather than memory.
How the codec decision differs by workload
Log tables are usually wide, text-heavy and append-only, so the largest savings tend to come from message bodies, user agents, paths and other repeated strings. Timestamps and sequence-like identifiers compress well with delta-based encodings, while free text responds mainly to general-purpose compression. Because logs are queried far more often than they are rewritten, a heavier codec on older partitions can pay off if the query path rarely touches those columns.
- Columns to test: the raw message or body, request path, user agent, and any high-cardinality string fields.
- Codec families to compare: LZ4 as the baseline, ZSTD at a few levels, and Delta or DoubleDelta for timestamps and monotonic counters.
- CPU considerations: ingestion throughput and merge time rise with higher ZSTD levels, so test under your real insert rate.
- Retention considerations: long-retained log tables are where a heavier level on cold partitions can change storage cost most.
- Measure first: compressed and uncompressed bytes per column on a representative week of logs, plus insert and merge duration.
Confirm the per-column sizes against your own table before choosing a level for the whole table.
Trace spans mix fixed identifiers, nested attributes and timing fields. Trace and span IDs are effectively random, so they compress poorly no matter the codec, while start times, durations and status codes often respond well to delta-style encodings and to general-purpose compression. Attribute maps can be large and repetitive across services, which makes them a common candidate for stronger compression. Traces are often sampled and retained for shorter periods than logs, which changes how much a heavier level is worth.
- Columns to test: attribute maps or JSON-like attribute columns, span names, service names, and the timestamp columns.
- Codec families to compare: LZ4 and ZSTD for attributes and names, DoubleDelta for timestamps where values are close together.
- CPU considerations: span ingestion is usually bursty, so confirm that heavier codecs keep up at peak load rather than average load.
- Retention considerations: shorter trace retention means the storage gain from a heavier level may be small in absolute terms.
- Measure first: per-column compression for the attribute and timestamp columns, measured on sampled production spans rather than synthetic data.
Illustrative: a trace table where IDs dominate the uncompressed size will show modest codec gains, so the attribute columns deserve most of the testing effort.
Metrics and sensor readings are the workload where specialized codecs matter most. Samples arrive at regular or near-regular intervals, and values often change slowly, so delta-of-delta encoding for timestamps and Gorilla-style encoding for floating-point values are designed for that pattern. The gain depends on how smooth the signal is: noisy sensors with rapid swings compress less than steady counters or temperatures. Dimension columns such as device or metric names usually compress well with general-purpose codecs and dictionary-friendly layouts.
- Columns to test: the timestamp column, the floating-point value column, and low-cardinality label or device columns.
- Codec families to compare: DoubleDelta for timestamps, Gorilla for floating-point values, and ZSTD for labels, each checked against plain LZ4.
- CPU considerations: Gorilla and delta encodings are generally light on CPU, but the choice of final compression stage still affects merge cost.
- Retention considerations: high-frequency sensor data over long periods is where codec choice most often changes storage footprint.
- Measure first: compressed size and query latency for the value column, using data that reflects real sampling intervals and sensor noise.
Confirm that queries on the value column do not become slower after the change, since decoding cost is part of the trade-off.
Some columns already hold compressed or binary data, such as gzip-compressed blobs, images, serialized protobuf or encrypted fields. These values have high entropy, so a second compression pass usually saves little and costs CPU on every insert and merge. Encrypted or randomized content will not compress at all. The practical question is whether these columns should be compressed in ClickHouse at all, or stored with a light codec so the table is not paying for work that yields nothing.
- Columns to test: any column holding compressed blobs, binary payloads, encrypted values or hashes.
- Codec families to compare: LZ4 or no-op style behavior against ZSTD, to confirm whether a second pass changes size at all.
- CPU considerations: heavier codecs spend time on data that will not shrink, which adds cost to inserts and merges without a storage return.
- Retention considerations: storage savings on these columns are often negligible, so retention should be judged on the rest of the table.
- Measure first: the ratio of compressed to uncompressed size for the payload column on a sample, before tuning anything else.
If the ratio stays close to one across codecs, document that result and keep the column on the lightest setting that meets your query needs.
Questions teams ask before changing ClickHouse codecs
Is the default codec enough for most teams?
For many tables, the default LZ4 codec is a reasonable starting point, but it is rarely the best fit for every column. Default compression favors fast reads and writes, so columns with repetitive or slowly changing values often store more data than they need. Teams usually get the best return by first measuring which columns take the most space, then changing only those columns. Time-ordered numeric columns, such as timestamps and sensor readings, are the usual candidates for specialized codecs. Keep the default for columns that are queried heavily and compress poorly, because a heavier codec adds CPU work on every read and write that touches them.
What drives the cost of a codec review?
The main drivers are data volume, the number of tables and columns in scope, the number of replicas, and whether a pre-production environment with representative data exists. A review on a few large tables is far smaller than one covering dozens of tables across several shards. The other major factor is who does the measuring. An internal team pays in engineer time, while a partner is paid under a written scope that defines which tables, codecs and query workloads are tested. Ask any provider to state the tables in scope, the codecs to be compared, the workloads used to measure read cost, and the deliverables, such as a per-column report and a rollout plan.
How long does a codec review take, and what does it depend on?
The duration depends mostly on how many columns are under review, how much representative data can be loaded into a test environment, and how long a rebuild takes at that volume. Measurement is iterative: each candidate codec needs to be applied to a copy of the data, the resulting size compared, and query timings recorded under realistic load. Schedule changes around change windows and replica sync, since altering a column codec rewrites data that must then replicate across the cluster. Ask a provider to show how the schedule depends on these inputs, and treat any fixed duration offered without a look at your data as an assumption to challenge.
What does the work need from our team?
The work needs representative data, not just a sample of easy rows, because codec performance depends on value patterns. Typical inputs are:
- A copy of representative tables, or a test cluster that can hold them
- Query logs showing which columns are read, filtered and aggregated, and how often
- A test cluster with the same ClickHouse version and hardware class as production
- Backup and restore capacity, so the original table can be restored if a change goes wrong
- A named owner who can approve changes to production tables
Confirm with your team that the test environment can be kept close to production configuration for the duration of the review.
Can an internal team review codecs alone?
Yes, if the team can run controlled experiments and read system table output with confidence. The core skills are creating test tables with different codecs, loading the same data into each, and comparing compressed sizes per column using the system tables that report storage by column. The team also needs to time representative queries and check that results are identical after each change. Teams that lack this experience often misread compression ratios, for example by comparing tables with different data or by ignoring the CPU cost on reads. An external review can be a useful second check, but the team that owns the cluster should keep the ability to repeat the measurements after the work is finished.
How do codec choices relate to TTL and tiering decisions?
Codec choice and retention policy both change how much storage a dataset uses, so they should be decided together. A rule that moves old data to cheaper disks reduces cost per byte, but it does not reduce the bytes stored, so better compression still matters on the cheaper tier. Conversely, a TTL rule that deletes data after a fixed period limits how long any codec savings accumulate. Older data is often a good candidate for a heavier codec, because it is queried less often and the extra read cost is easier to accept. Before applying a heavier codec to a tiered table, check how the table's parts move between volumes and how long the merges that rewrite them take.
Should we change codecs on production tables before testing?
No. Changing a column codec rewrites the data, and the change must replicate to every replica, so a poor choice affects production directly. Before any change, create a backup of the table and confirm you can restore it, following the ClickHouse backup and restore documentation, which warns that backups never test-restored often fail when needed. Test the same change on a spare cluster first, verify that query results are unchanged, and record the measured size and timing differences. Only then schedule the production change with a rollback plan that names who decides to revert.
What should we ask a provider before agreeing to a codec review?
Ask for a written scope that lists the tables and columns in scope, the codecs compared, the query workloads used for read-cost measurement, and the environment used for testing. Ask how representative data will be obtained and handled, and whether any data leaves your infrastructure. Request the deliverables in writing, including a per-column size report, query timing comparisons, a rollout and rollback plan, and a note on whether later changes are supported. Under a fixed-scope engagement, scope, deliverables and timelines are agreed in writing before work starts, so use that document to compare quotes rather than headline effort.
Limits to confirm in your environment
Codec choices depend on the ClickHouse version you run, the shape of your data, and the headroom on your servers. Each card below names one limit to check against the ClickHouse documentation for your version and against your own measurements before you standardize on a codec or level.
Codec and level support by version
Confirm which codecs and compression levels your exact ClickHouse version accepts, including how Delta, DoubleDelta and Gorilla can be chained with a general-purpose codec. Support for a codec or level is not guaranteed to be identical across release lines, so check the codec reference in the documentation for the version you run. Test the exact CODEC(...) clauses on a copy of a representative table before writing them into production DDL.
Codec changes across upgrades
A codec change is a schema change that travels with your upgrade plan. Read the changelog for your target release before upgrading, because the production guidance notes that minor backward-incompatible changes can occur. Confirm that replicas on mixed versions can read parts written with the new codec, and decide whether existing data will be rewritten or only new parts will use the codec.
Fit between data shape and codec
Delta, DoubleDelta and Gorilla gain their advantage from specific data patterns, such as monotonic timestamps or slowly changing floating-point values. Measure the actual sequences in each column before assigning a specialized codec. Columns with random identifiers, high-cardinality strings or frequent value jumps may compress no better, and the codec may only add CPU cost on reads and merges.
CPU headroom on production servers
Heavier ZSTD levels cost CPU during inserts, background merges and reads that decompress data. Measure CPU use on production-like hardware under your real ingest rate and query mix, including the peak merge periods. Confirm that merges can keep pace with inserts at the chosen level, because a codec that is cheap on a test node can become the bottleneck on busy shared servers.
Backup size and restore time
Compression changes the size of each backup and the time needed to move data during a restore. Compare backup size and duration for each candidate codec, using the BACKUP and RESTORE steps you will run in production. Include the restore test on a spare cluster, since the backup documentation recommends regular restore tests and a backup that has never been restored may not restore properly.
How DeployOpen scopes and delivers codec and storage work
DeployOpen plans, implements and documents self-hosted ClickHouse inside your own infrastructure, including the codec choices for each column. Codec work is scoped in writing before any table changes, measured against representative data, and handed over with the evidence your team needs to run it.
How we scope
- Written scope lists the tables, columns and data sources whose codecs will be reviewed.
- Scope states which query patterns and retention periods the compression results must support.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts. Ongoing support is available as a retainer.
How we build
- Column-level compression is measured on a pre-production copy with representative data and queries.
- Candidate codecs such as LZ4, ZSTD levels, Delta, DoubleDelta and Gorilla are compared on size and CPU cost.
- Table definitions are changed in a staging cluster first, with query latency and insert throughput recorded before and after.
How we hand over
- Deliverables include the final codec per column, the measurement method and the reasoning behind each choice.
- Runbooks explain how to re-measure compression after schema or data volume changes.
- Restore tests confirm that codec changes do not affect backups, and results are documented for your operations team.
How we support
- Codec reviews can be repeated when ClickHouse releases change compression behavior or when data profiles shift.
- Upgrade planning follows the release policy, and changelogs are read before any version change.
- Retainer support covers ongoing measurement, tuning and documentation for your self-managed clusters.
