ClickHouse DB::Exception: FixedString size doesn't match

The "DB::Exception: FixedString size doesn't match" error appears when you try to insert or cast a value whose byte length does not match the declared width of a FixedString(N) column. Unlike the variable-length String type, FixedString(N) stores exactly N bytes — no more, no less. Values shorter than N are padded with null bytes, but values longer than N are rejected outright.

Impact

This error causes the offending INSERT or query to fail. In bulk inserts it can reject an entire batch, stalling data ingestion pipelines. It is especially common when migrating data from systems that use variable-length strings into ClickHouse tables that use FixedString for storage efficiency.

Common Causes

1. Inserting a value longer than N bytes — a string that exceeds the declared FixedString(N) width, particularly with multi-byte UTF-8 characters where the character count may be less than the byte count.
2. Casting between different FixedString widths — attempting CAST(value AS FixedString(M)) where the source value is wider than M bytes.
3. Schema mismatch during data migration — the source system stores variable-length strings, but the ClickHouse target uses FixedString with an insufficient width.
4. Multi-byte encoding surprises — a column sized for ASCII characters (1 byte each) receives UTF-8 data where characters may occupy 2–4 bytes.
5. Materialized view or ETL transformations — an intermediate expression produces a string longer than the target FixedString column.

Troubleshooting and Resolution Steps

1. Check the declared width of the column:

   SELECT name, type FROM system.columns
   WHERE database = 'your_db' AND table = 'your_table' AND type LIKE 'FixedString%';
   

2. Measure the actual byte length of the failing value. Use length() which returns byte length in ClickHouse:

   SELECT length('your_value');
   

3. Widen the FixedString column if the data legitimately needs more space:

   ALTER TABLE your_table MODIFY COLUMN col_name FixedString(32);
   

4. Switch to String type. If the data has variable lengths and padding is not needed, String is usually the better choice:

   ALTER TABLE your_table MODIFY COLUMN col_name String;
   

5. Truncate values before insertion. If you must keep FixedString(N), truncate long values in your INSERT or ETL:

   INSERT INTO your_table
   SELECT substring(long_col, 1, 16) AS col_name
   FROM source_table;
   

6. Handle multi-byte characters explicitly. If the data is UTF-8, consider using substringUTF8 or sizing the column based on the maximum byte length rather than character count.

Best Practices

• Prefer String over FixedString unless you have a genuine fixed-width use case (e.g., UUIDs, ISO country codes, MD5 hashes).
• When using FixedString, size it based on the maximum byte length of the data, not the character count.
• Validate incoming data lengths before insertion, especially when loading from external systems.
• Document the expected format and encoding for every FixedString column so downstream users understand the constraints.

Frequently Asked Questions

Q: What happens to values shorter than N in a FixedString(N) column?
A: ClickHouse right-pads them with null bytes (\0) to reach exactly N bytes. This is important to remember when comparing values or exporting data, as the trailing nulls are part of the stored value.

Q: Is FixedString faster or more efficient than String?
A: For truly fixed-width data it can be slightly more compact because it avoids storing a per-value length prefix. However, the difference is small, and String is more flexible. Use FixedString only when every value genuinely occupies the same number of bytes.

Q: Can I use FixedString for UUIDs?
A: You can store UUIDs as FixedString(16) (binary representation) or FixedString(36) (hyphenated string). However, ClickHouse has a native UUID type that is more appropriate and efficient for this use case.

Q: How do I find rows that would violate a FixedString width before altering the column?
A: Query the source data to find values that exceed the target width:

SELECT col_name, length(col_name) AS len
FROM source_table
WHERE length(col_name) > 16
LIMIT 100;