Understanding String Types in ClickHouse: FixedString vs String with Practical Examples

When storing text in ClickHouse, it’s essential to choose the right string type. ClickHouse offers two primary string types—each with different use cases and behaviors.

 

1. Fixed Length: FixedString(N)

FixedString(N) stores a string with exactly N bytes.

 

Key Behaviors

·      If the input exceeds N bytes, ClickHouse throws an error.

·      If the input is shorter than N bytes, ClickHouse pads the remaining space with null bytes (\0).

 

Use Cases

Storing fixed-length values such as:

·      Country codes (US, IN, DE, etc.)

·      Short fixed-format identifiers

·      Compact, uniformly sized strings for memory efficiency

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.fixed_example (
    code FixedString(4)
) ENGINE = MergeTree()
ORDER BY tuple();

-- OK
INSERT INTO demo_db.fixed_example VALUES ('ABCD'); 

-- Stored as 'A\0\0\0'
INSERT INTO demo_db.fixed_example VALUES ('A');    

-- Error: String too long
INSERT INTO demo_db.fixed_example VALUES ('ABCDE'); 

 

krishna :) INSERT INTO demo_db.fixed_example VALUES ('ABCDE');

INSERT INTO demo_db.fixed_example FORMAT Values

Query id: 51bb1ab6-cde9-4e81-aeb7-805e95926322

Ok.
Error on processing query: Code: 131. DB::Exception: String too long for type FixedString(4): while executing 'FUNCTION if(isNull(-dummy-0) : 3, defaultValueOfTypeName('FixedString(4)') :: 2, _CAST(-dummy-0, 'FixedString(4)') :: 4) -> if(isNull(-dummy-0), defaultValueOfTypeName('FixedString(4)'), _CAST(-dummy-0, 'FixedString(4)')) FixedString(4) : 1': While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (TOO_LARGE_STRING_SIZE) (version 25.5.1.1919 (official build))

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.fixed_example;

SELECT *
FROM demo_db.fixed_example

Query id: 57ae5cd9-3d6e-41e2-bc86-f3c52c72d14c

   ┌─code─┐
1. │ ABCD │
2. │ A    │
   └──────┘

2 rows in set. Elapsed: 0.002 sec. 

  

Let’s check the length of strings in fixed_example table.

SELECT code, length(code) FROM demo_db.fixed_example;

krishna :) SELECT code, length(code) FROM demo_db.fixed_example;

SELECT
    code,
    length(code)
FROM demo_db.fixed_example

Query id: b2ff70c8-1a12-4dde-9276-f59ee22215ac

   ┌─code─┬─length(code)─┐
1. │ A    │            4 │
2. │ ABCD │            4 │
   └──────┴──────────────┘

2 rows in set. Elapsed: 0.002 sec. 

  

As you see the output, even though the character A occupies one byte, since we set the type as FixedString(4), the character A still occupied 4 bytes (Remaining 3 bytes are padded with null bytes).

 

2. Variable Length: String

The String type stores strings of any length (up to ~1 GB per row).

 

Key Behaviors:

·      Dynamically sized.

·      Efficient for variable-length text such as Names, addresses, URLs, configurations, logs, etc.,

·      No padding or truncation.

·      Internally uses a length prefix to store values.

 

CREATE TABLE demo_db.string_example (
    name String
) ENGINE = MergeTree()
ORDER BY tuple();

INSERT INTO demo_db.string_example VALUES ('Ram'), ('Krishna'), ('Shiva Shankar');
  

Let's query the name and its length.

SELECT name, length(name) FROM demo_db.string_example;

krishna :) SELECT name, length(name) FROM demo_db.string_example;

SELECT
    name,
    length(name)
FROM demo_db.string_example

Query id: 1f4cc12d-c8c5-4101-a7b8-3b21459c472e

   ┌─name──────────┬─length(name)─┐
1. │ Ram           │            3 │
2. │ Krishna       │            7 │
3. │ Shiva Shankar │           13 │
   └───────────────┴──────────────┘

3 rows in set. Elapsed: 0.001 sec.

In summary, Choose FixedString for compact, predictable memory usage, and String when flexibility and variable input lengths are required.

 

Previous                                                    Next                                                    Home