DEV Community

Cover image for Day 86: Faster Map Key Lookups in ClickHouse® 26.3 with Bucketed Map Serialization
Kanishga Subramani
Kanishga Subramani

Posted on

Day 86: Faster Map Key Lookups in ClickHouse® 26.3 with Bucketed Map Serialization

#clickhouse #devops #database #analytics

Semi-structured data has become a standard part of modern analytics. Whether you're storing application logs, telemetry, user attributes, event metadata, or feature flags, it's common to encounter records where every row contains a different set of key-value pairs.

ClickHouse® provides the Map data type to handle these dynamic datasets without requiring hundreds of nullable columns or repeatedly parsing JSON during query execution. A Map offers both flexibility and a clean schema for attributes that vary from one record to another.

However, querying individual keys from large Map columns has traditionally been less efficient than querying regular columns. Prior to ClickHouse® 26.3, retrieving a single key often required processing the serialized map representation for every row, even when only one value was needed.

ClickHouse® 26.3 introduces bucketed Map serialization, a new storage format designed to make selective key lookups significantly faster. By reorganizing how map data is stored on disk, ClickHouse® reduces the amount of data that must be processed during queries while preserving the fast insert performance that the database is known for.

In this article, we'll explore:

  • How Map serialization worked before ClickHouse® 26.3
  • Why selective key lookups were expensive
  • How bucketed serialization works
  • The available configuration settings
  • Expected performance improvements
  • When you should enable this feature

Understanding the Map Data Type

A Map stores key-value pairs inside a single column.

For example:

CREATE TABLE events
(
    event_time DateTime,
    user_id UInt64,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY event_time;
Enter fullscreen mode Exit fullscreen mode

A row might contain:

{
    "country": "India",
    "browser": "Chrome",
    "device": "Mobile",
    "campaign": "SummerSale"
}
Enter fullscreen mode Exit fullscreen mode

Another row might contain completely different attributes:

{
    "country": "Germany",
    "language": "German",
    "membership": "Premium"
}
Enter fullscreen mode Exit fullscreen mode

Instead of creating dozens or even hundreds of nullable columns, dynamic attributes remain grouped inside a single Map.

This makes the data model both simpler and more flexible.

How Map Serialization Worked Before ClickHouse® 26.3

Internally, a Map is stored as two arrays:

  • Keys
  • Values

Conceptually:

Keys:
["country", "browser", "campaign"]

Values:
["India", "Chrome", "SummerSale"]
Enter fullscreen mode Exit fullscreen mode

Suppose a query requests:

SELECT attributes['browser']
FROM events;
Enter fullscreen mode Exit fullscreen mode

Although only one key is needed, ClickHouse® still has to process the serialized map representation for every row in order to locate the requested key.

For small maps, this overhead is usually negligible.

For maps containing dozens or hundreds of keys, the additional work becomes much more noticeable.

Conceptually, the process looks like this:

Map

country
browser
device
campaign
source
region
...

↓

Query:
attributes['browser']

↓

Process serialized map
Locate requested key
Return value
Enter fullscreen mode Exit fullscreen mode

The larger each map becomes, the more work ClickHouse® performs for every lookup.

Why This Becomes a Performance Bottleneck

Imagine each row stores 100 different attributes.

A dashboard might only need:

attributes['country']
Enter fullscreen mode Exit fullscreen mode

Even though only one key is requested, the database still has to inspect the serialized map representation for every row.

Across billions of rows, this additional processing increases:

  • Data read
  • CPU utilization
  • Query latency

As maps continue to grow, the cost of repeatedly locating individual keys also grows.

Bucketed Map Serialization in ClickHouse® 26.3

ClickHouse® 26.3 introduces a new serialization format called with_buckets.

Instead of storing every key-value pair together as a single serialized structure, map entries are distributed across multiple buckets according to the hash of each key.

Conceptually:

Bucket 1

country
city
zip

----------------

Bucket 2

browser
device

----------------

Bucket 3

campaign
source
medium
Enter fullscreen mode Exit fullscreen mode

Now consider the same query:

SELECT attributes['browser']
FROM events;
Enter fullscreen mode Exit fullscreen mode

Rather than processing the entire serialized map, ClickHouse® determines which bucket may contain the requested key and reads only that portion of the data.

The workflow now becomes:

Query

attributes['browser']

↓

Hash("browser")

↓

Locate Bucket 2

↓

Read Bucket 2 only

↓

Return value
Enter fullscreen mode Exit fullscreen mode

This significantly reduces the amount of data that must be processed for selective key lookups.

How Bucketed Serialization Works

One of the clever aspects of this feature is when the optimization occurs.

Bucketing is not performed during inserts.

Instead, newly inserted parts continue using the existing serialization format.

During MergeTree background merges, ClickHouse® automatically rewrites eligible parts into the bucketed storage layout.

This design offers two major advantages.

First, insert performance remains fast because ingestion isn't slowed by additional bucketing work.

Second, read performance gradually improves as background merges reorganize the stored data.

Applications don't need to change their SQL queries.

The optimization happens entirely within the storage layer.

Configuration Options

ClickHouse® 26.3 introduces several settings that control bucketed serialization.

map_serialization_version

Determines which serialization format should be used.

map_serialization_version_for_zero_level_parts

Specifies the serialization used for newly created parts before background merges occur.

max_buckets_in_map

Defines the maximum number of buckets available for storing map entries.

Increasing the bucket count can improve selective key access for larger maps but also introduces additional metadata and storage overhead.

map_buckets_strategy

Controls how ClickHouse® chooses the appropriate number of buckets for a given map.

map_buckets_min_avg_size

Defines the minimum average map size before bucketed serialization becomes worthwhile.

These settings allow administrators to balance storage efficiency with query performance according to their workload.

Performance Improvements

According to the ClickHouse® 26.3 release information, bucketed serialization can dramatically improve queries that retrieve individual keys from large Map columns.

The observed improvements depend on several factors, including:

  • Average number of keys per map
  • Data distribution
  • Query patterns
  • Bucket configuration

For some workloads, ClickHouse® reports 2× to 49× faster single-key lookups compared to the previous serialization format.

The greatest improvements occur when:

  • Maps contain many keys
  • Queries repeatedly access only a few keys
  • Large amounts of data are scanned
  • Interactive dashboards repeatedly query the same attributes

As with any performance optimization, results depend on workload characteristics. Smaller maps or queries that read the entire map may experience limited improvements.

Example

Consider a log analytics table.

CREATE TABLE logs
(
    timestamp DateTime,
    metadata Map(String, String)
)
ENGINE = MergeTree
ORDER BY timestamp;
Enter fullscreen mode Exit fullscreen mode

Example metadata:

service
host
region
environment
cluster
namespace
container
status
trace_id
version
Enter fullscreen mode Exit fullscreen mode

Suppose a dashboard runs:

SELECT
    metadata['service'],
    count()
FROM logs
GROUP BY metadata['service'];
Enter fullscreen mode Exit fullscreen mode

Prior to bucketed serialization, ClickHouse® would process the serialized map representation to locate the service key for every row.

After background merges rewrite the data into the bucketed format, ClickHouse® can identify the appropriate bucket and process only the relevant portion of the map.

The SQL query remains identical.

Only the storage layout changes.

When Should You Use Bucketed Serialization?

This feature is particularly valuable for workloads that:

  • Store large Map columns
  • Frequently retrieve individual keys
  • Analyze billions of rows
  • Process application logs
  • Collect observability metrics
  • Store telemetry data
  • Analyze event metadata
  • Build interactive dashboards

If your maps are very small or most queries retrieve the complete map, the performance gains may be relatively modest.

Best Practices

To get the most from bucketed serialization:

  • Enable it for workloads with large Map columns.
  • Allow MergeTree background merges to complete before benchmarking.
  • Select bucket counts appropriate for your average map size.
  • Benchmark using your own production workloads.
  • Don't expect significant improvements if most queries read the entire map rather than individual keys.

Things to Keep in Mind

Before enabling the feature, remember a few important points.

  • Existing data isn't rewritten immediately after upgrading.
  • Bucketed serialization is applied gradually through MergeTree background merges.
  • Insert performance remains largely unchanged because bucketing isn't performed during ingestion.
  • Query improvements depend heavily on workload characteristics and access patterns.

Understanding these behaviors helps set realistic expectations during evaluation.

Why This Matters

Many modern analytical workloads rely heavily on dynamic attributes that continuously evolve.

Application logs, telemetry, user events, feature flags, and metadata often contain different fields for every record.

The Map data type provides the flexibility needed for these datasets, but fast access to individual keys is equally important for interactive analytics.

Bucketed serialization improves the physical storage layout without changing the SQL interface.

Existing applications continue using the same queries while benefiting from reduced I/O and faster selective key lookups once background merges have reorganized the data.

Conclusion

Bucketed Map serialization is one of the most practical storage optimizations introduced in ClickHouse® 26.3.

Rather than treating every map as a single serialized structure, ClickHouse® now organizes map entries into buckets, allowing queries to process only the portion of the data that may contain the requested key.

The optimization happens automatically during MergeTree background merges, preserving fast insert performance while significantly improving read efficiency for many analytical workloads.

Depending on data characteristics and query patterns, the ClickHouse® team reports speedups ranging from 2× to 49× for selective key lookups.

For organizations working with logs, observability platforms, telemetry pipelines, event metadata, or other semi-structured datasets, bucketed serialization offers a compelling way to improve query performance without changing existing SQL queries or redesigning data models.

As always, benchmark the feature against your own workload, but if your applications frequently access individual keys within large Map columns, bucketed serialization is a feature well worth evaluating after upgrading to ClickHouse® 26.3.

References

Top comments (0)