What is a reasonable maximum for JSON's max_dynamic_paths?

[martijnthe]

The default value for max_dynamic_paths is 1024. (See doc here).

In our use case, a multi-tenant observability/logging system, our customers create the keypaths. We have little control over them. At the moment, we've got ~25K unique paths in the dataset. If we would keep the default of 1024, the suboptimal, fallback column would get used ~24K paths...

The doc says:

This limit is needed to avoid the enormous number of different subcolumns that can make the table unusable.

At what number of subcolumns would the table become unstable? If the answer is, "it depends", what does it depend on?

Note: we are using ClickHouse Cloud.

[martijnthe]

@Avogar do you perhaps have any guidance for us? thank you 🙏

[Avogar]

Hi! The tradeof in number of subcolumns is the following: the more subcolumns, the more files are created in the filesystem (in cloud it's S3) and the more memory is used during inserts and merges. Also in cloud we store paths to the files in Keeper, so in cloud there is also a tradeof: the more files created in data part, the more load to keeper during inserts and merges (so also number of tables with JSON can be important for keeper load).

Memory usage is increased with number of files increasing because for each file we create a write buffer that buffers some data and flushes it to the filesystem when it's full. And for S3 there are requirements for minimums size of such chunk during multipart upload (when we load dingle file in several chunks) and it's 5MB. And for each JSON path we create not a single file, but several files (you can check how paths are stored in this blog: https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse).

With local filesystem I would say you could increase the limit, but for S3 the memory usage is higher (due to minimus size of the buffer) and current default is already high enough.

Storing 25K unique paths as subcolumns sounds unrealistic to me, especially in the cloud.

In the future we have plans to improve reading of paths from the shared data subcolumn.

[martijnthe]

Thanks for you elaborate answer, @Avogar. Much appreciated.

I was thinking of perhaps creating a separate table per (set/shard of) customer(s), but based on what you are saying, it sounds like that will not help much because it will still create the ~same number of files.

[den-crane]

Thanks for you elaborate answer, @Avogar. Much appreciated.

I was thinking of perhaps creating a separate table per (set/shard of) customer(s), but based on what you are saying, it sounds like that will not help much because it will still create the ~same number of files.

Actually it may help. The problem the number of column in a part. So if you have a table partitioned by tenant or set of tables (one table per tenant) then the number of columns in part will be much smaller. The problem with unpartitioned (single) table that merges combine parts into a single part with multiple tenants, it amplifies the number of files.

[martijnthe]

@den-crane ah, thank you so much for your comment. We'll experiment with this.