Map(K, V)

### Performance Trade-offs[​](#performance-trade-offs "Direct link to Performance Trade-offs")

The following table summarizes the performance impact of `with_buckets` compared to `basic` serialization at various map sizes (10 to 10,000 keys per row). The bucket count was determined by the `sqrt` strategy capped at 32. The exact numbers depend on key/value types, data distribution, and hardware.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation 10 keys 100 keys 1,000 keys 10,000 keys Notes|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Single key lookup** (`m['key']`) 1.6–3.2x faster 4.5–7.7x faster 16–39x faster 21–49x faster Reads only one bucket instead of the entire column.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **5 key lookups** ~1x 1.5–3.1x faster 2.9–8.3x faster 4.5–6.7x faster Each key reads its own bucket; some buckets may overlap.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **PREWHERE** (`SELECT m WHERE m['key'] = ...`) 1.5–3.0x faster 2.9–7.3x faster 5.3–31x faster 20–45x faster PREWHERE filter reads only one bucket; full map read only for matching rows. Speedup depends on selectivity — fewer matching granules means less full-map I/O.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Full map scan** (`SELECT m`) ~2x slower ~2x slower ~2x slower ~2x slower Must read and reassemble all buckets.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **INSERT** 1.5–2.5x slower 1.5–2.5x slower 1.5–2.5x slower 1.5–2.5x slower Overhead of hashing keys and writing to multiple substreams. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Recommendations[​](#recommendations "Direct link to Recommendations")

- **Small maps (< 32 keys on average):** Keep `basic` serialization. The overhead of bucketing is not justified for small maps. The default `map_buckets_min_avg_size = 32` enforces this automatically.
- **Medium maps (32–100 keys):** Use `with_buckets` with `sqrt` strategy if queries frequently access individual keys. The speedup is 4–8x for single-key lookups.
- **Large maps (100+ keys):** Use `with_buckets`. Single-key lookups are 16–49x faster. Consider `map_serialization_version_for_zero_level_parts = 'basic'` to keep insert speed close to the baseline.
- **Full map scans dominate the workload:** Keep `basic`. Bucketed serialization adds ~2x overhead for full scans.
- **Mixed workload (some key lookups, some full scans):** Use `with_buckets` with zero-level parts set to `basic`. The `PREWHERE` optimization reads only the relevant bucket for the filter, then reads the full map only for matching rows, giving a significant net speedup.

### Alternative Approaches[​](#map-alternatives "Direct link to Alternative Approaches")

If bucketed `Map` serialization does not fit your use case, there are two alternative approaches for improving key-level access performance:

#### Using the JSON Data Type[​](#using-the-json-data-type "Direct link to Using the JSON Data Type")

The [JSON](/docs/sql-reference/data-types/newjson) data type stores each frequent path as a separate dynamic subcolumn. Paths that exceed the `max_dynamic_paths` limit go into a [shared data structure](/docs/sql-reference/data-types/newjson#shared-data-structure), which can use `advanced` serialization for optimized single-path reads. See the [blog post](https://clickhouse.com/blog/json-data-type-gets-even-better) for a detailed overview of the `advanced` serialization.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Aspect `Map` with buckets `JSON`|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Single key read Reads one bucket (may contain other keys). All key-value pairs in the bucket are deserialized. Frequent paths are read directly from dynamic subcolumns. Infrequent paths go to shared data; with `advanced` serialization, only the exact path's data is read.| Value types All values share the same type `V` Each path can have its own type. Paths without a type hint use `Dynamic`.| Skip index support Works with some index types created on `mapKeys`/`mapValues` Skip indexes can only be created on specific path subcolumns, not on all paths/values at once.|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Full column read ~2x slower than `basic` due to bucket reassembly Overhead from `Dynamic` type encoding and path reconstruction.| Storage overhead Minimal additional metadata Higher due to `Dynamic` type encoding, path name storage, and additional metadata in `advanced` serialization.| Schema flexibility Fixed key and value types at table creation Fully dynamic — keys and value types can vary per row. Typed path hints can be declared for known paths. | | | | | | | | | | | | | | | | | | | | |

Use `JSON` when different keys need different value types, when the set of keys varies significantly across rows, or when frequently accessed keys are known in advance and can be declared as typed paths for direct subcolumn access.

#### Manual Sharding into Multiple Map Columns[​](#manual-sharding-into-multiple-map-columns "Direct link to Manual Sharding into Multiple Map Columns")

You can manually split a single `Map` into multiple columns by key hash at the application level:

```
CREATE TABLE tab (
    id UInt64,
    m0 Map(String, UInt64),
    m1 Map(String, UInt64),
    m2 Map(String, UInt64),
    m3 Map(String, UInt64)
) ENGINE = MergeTree ORDER BY id;
```

During insertion, route each key-value pair to the column `m{hash(key) % 4}`. During queries, read from the specific column: `m{hash('target_key') % 4}['target_key']`.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Aspect `Map` with buckets Manual sharding|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Ease of use Transparent — handled by the storage engine Requires application-level routing logic for inserts and selects|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Vertical merge Not supported — all buckets belong to one column Supported — each `Map` column is an independent column and can be merged vertically| Schema changes Bucket count adapts automatically per part Changing the number of shards requires rewriting data or adding new columns|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Query syntax `m['key']` works directly Must compute the correct column: `m0['key']`, `m1['key']`, etc.| Bucket granularity Per-part, adapts to data statistics Fixed at table creation | | | | | | | | | | | | | | | | | |

Manual sharding is beneficial when vertical merges are important for reducing memory usage during merges of tables with many columns, or when the number of shards must be fixed and controlled explicitly. For most use cases, automatic bucketed serialization is simpler and sufficient.

**See Also**