ClickHouse DB::Exception: Illegal codec parameter

The "DB::Exception: Illegal codec parameter" error in ClickHouse occurs when a CODEC() clause includes a parameter value that is out of range or otherwise invalid for the specified codec. The ILLEGAL_CODEC_PARAMETER error is raised during DDL operations when the codec name is recognized and the syntax is correct, but the parameter itself is rejected by the codec's validation logic.

Impact

The DDL statement is rejected and no schema changes are applied. Existing data and tables are not affected. This is purely a configuration-time error that prevents creating or modifying tables with invalid codec parameters.

Common Causes

1. Specifying a ZSTD compression level outside the valid range (1-22)
2. Providing a negative value or zero where a positive integer is required
3. Using a Delta byte size that does not match the column's data type width (e.g., Delta(3) when only 1, 2, 4, and 8 are valid)
4. Passing parameters to a codec that does not accept any (e.g., LZ4(5) -- LZ4 does not take a compression level)
5. Using a floating-point number where an integer is expected
6. Specifying too many parameters for a codec

Troubleshooting and Resolution Steps

1. Check the valid parameter ranges for common codecs:

   • LZ4 -- no parameters
   • LZ4HC(level) -- level from 1 to 12 (default 9)
   • ZSTD(level) -- level from 1 to 22 (default 1)
   • Delta(bytes) -- 1, 2, 4, or 8 (default matches column type size)
   • DoubleDelta -- no parameters
   • Gorilla -- no parameters
   • FPC(level, float_size) -- level 1 to 28, float_size 4 or 8
   • T64 -- no parameters

2. Review your DDL statement and correct the parameter:

   -- Incorrect: ZSTD level out of range
   CODEC(ZSTD(25))
   -- Correct: ZSTD level within 1-22
   CODEC(ZSTD(3))
   

3. Remove parameters from codecs that don't accept them:

   -- Incorrect: LZ4 does not take parameters
   CODEC(LZ4(1))
   -- Correct
   CODEC(LZ4)
   -- If you want LZ4 with a compression level, use LZ4HC
   CODEC(LZ4HC(9))
   

4. Match Delta byte size to your data type:

   -- For UInt32 (4 bytes), use Delta(4) or just Delta
   column_name UInt32 CODEC(Delta(4), ZSTD)
   -- For UInt64 (8 bytes)
   column_name UInt64 CODEC(Delta(8), ZSTD)
   

5. Test the corrected codec in a simple CREATE TABLE to verify:

   CREATE TABLE test_param (x UInt64 CODEC(ZSTD(3))) ENGINE = MergeTree() ORDER BY x;
   DROP TABLE test_param;
   

Best Practices

• Consult the ClickHouse documentation for the exact parameter specifications of each codec before using them.
• Use default parameters when unsure -- omitting the parameter often yields a sensible default (e.g., ZSTD defaults to level 1).
• For Delta codecs, let ClickHouse infer the byte size by omitting the parameter rather than guessing.
• Document your codec choices and their parameters so that they can be reviewed and adjusted during performance tuning.

Frequently Asked Questions

Q: What happens if I omit the parameter for ZSTD?
A: ClickHouse uses a default compression level of 1, which offers a good balance of speed and compression ratio for most workloads.

Q: Why can't I use LZ4 with a compression level?
A: The LZ4 codec in ClickHouse is the fast variant that does not support tunable compression levels. If you want configurable compression with the LZ4 algorithm family, use LZ4HC, which accepts a level parameter from 1 to 12.

Q: Does a higher Delta byte size always give better compression?
A: No. The Delta byte size should match the width of the data type for optimal results. Using a mismatched size can actually hurt compression ratios because the delta calculation won't align with the data boundaries.