ClickHouse / ClickHouse/ClickHouse

Add FixedBase58(N) and FixedBase64(N) semantic types backed by FixedString(N)

Open
#105,251 12 comments 0 reactions 0 assignees View on GitHub
comp-data-types external feature st-discussion
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

[circular.fi](https://circular.fi ) - Data analytics on the solana blockchain.

### Use case

We store identifiers that are externally represented as Base58 or Base64 strings, but internally have a fixed-size binary representation.

Today, the efficient way to store this kind of value in ClickHouse is to manually decode the textual representation and store the result as `FixedString(N)`. Queries then also need to manually decode input literals before comparison, and output needs to be manually encoded back to Base58/Base64.

Example of the current workflow:

```sql
CREATE TABLE items
(
id FixedString(32)
)
ENGINE = MergeTree
ORDER BY id;
```

Insertion requires explicit conversion:

```sql
INSERT INTO items
SELECT base58Decode('8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9');
```

Filtering also requires explicit conversion:

```sql
SELECT *
FROM items
WHERE id = base58Decode('8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9');
```

Output requires explicit conversion:

```sql
SELECT base58Encode(id)
FROM items;
```

This is error-prone and makes schemas less self-describing.

The important performance requirement is that comparisons must remain binary comparisons on the stored bytes. ClickHouse should not encode the whole column to Base58/Base64 strings in order to compare values.

### Describe the solution you'd like

Add two semantic data types:

```sql
FixedBase58(N)
FixedBase64(N)
```

They would be backed internally by fixed-size binary storage, similar to `FixedString(N)`, but with custom serialization, deserialization, and type conversion rules.

Desired behavior:

```sql
CREATE TABLE items
(
id FixedBase58(32),
ids Array(FixedBase58(32))
)
ENGINE = MergeTree
ORDER BY id;
```

Insertion from string:

```sql
INSERT INTO items VALUES
(
'8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9',
['8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9']
);
```

The string should be decoded once and stored as exactly 32 bytes.

Selecting the column should return the textual representation:

```sql
SELECT id FROM items;
```

Expected result:

```text
8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9
```

Filtering should accept string literals:

```sql
SELECT *
FROM items
WHERE id = '8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9';
```

The literal should be decoded to `FixedBase58(32)` and then compared as bytes.

`IN` should work with constants and subqueries:

```sql
SELECT *
FROM items
WHERE id IN
(
'8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9'
);
```

```sql
SELECT *
FROM items
WHERE '8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9' IN
(
SELECT id FROM items
);
```

Array functions should also preserve binary semantics:

```sql
SELECT has(ids, '8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9')
FROM items;
```

```sql
SELECT hasAny(
ids,
['8jNMFNf5H4mWEjUynsyDSLAU3retbZTXXv4YW5n2k9']
)
FROM items;
```

The same should work when the right-hand side is already typed as `FixedBase58(32)` or `Array(FixedBase58(32))`.

### Expected type behavior

`FixedBase58(N)` should behave like a semantic wrapper around fixed-size binary storage:

- stored as exactly `N` raw bytes;
- inserted from Base58 text;
- selected as Base58 text;
- compared as raw bytes;
- usable in `ORDER BY`, primary keys, `IN`, `JOIN`, `GROUP BY`, arrays, `has`, `hasAny`, etc.;
- reject invalid Base58 strings;
- reject decoded values whose size is different from `N`.

Similarly, `FixedBase64(N)` should decode/encode Base64 while storing exactly `N` raw bytes.

### Performance requirement

For expressions like:

```sql
WHERE id = '...'
```

the constant string should be converted to the binary representation once, and the comparison should happen against the stored bytes.

ClickHouse should avoid plans that encode the whole `FixedBase58(N)` column to strings just to compare it with a string literal.

This also matters for:

```sql
has(Array(FixedBase58(32)), '...')
hasAny(Array(FixedBase58(32)), Array(String))
IN / GLOBAL IN
subqueries returning FixedBase58(N)
```

### Describe alternatives you've considered

1. Continue using `FixedString(N)` directly.

This is efficient but forces every query to use explicit conversion functions. It also makes the schema less self-documenting.

2. Store Base58/Base64 as `String`.

This is simpler for input/output but wastes storage and makes comparisons slower because the encoded representation is variable-length and larger than the binary value.

3. Use materialized columns.

For example, store both the original string and decoded bytes. This increases storage and still requires users to know which column to use for efficient filtering.

4. Use only functions such as `base58Decode` / `base58Encode`.

This works, but it puts the burden on every query and every insert pipeline. A semantic type would centralize the behavior and reduce mistakes.

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.