Use of generated columns with JSON flattening violates benchmark rules

[xzx0xzx]

Hi, the table in StarRocks maybe violates the benchmark rules, because its DDL uses generated columns, which flatten the JSON and turn it into multiple non-JSON columns at insertion time.

CREATE TABLE bluesky (
    `id` BIGINT AUTO_INCREMENT,
    `data` JSON NOT NULL COMMENT "Primary JSON object, optimized for field access using FlatJSON",

    sort_key VARBINARY AS encode_sort_key(
        get_json_string(data, 'kind'),
        get_json_string(data, 'commit.operation'),
        get_json_string(data, 'commit.collection'),
        get_json_string(data, 'did')
    )
)
ORDER BY (sort_key);

As shown in the CREATE TABLE statement, encode_sort_key is a generated column, and the get_json_string function flattens the JSON, which violates the benchmark rules.

[rschu1ze]

You are referring to the recent Starrocks 4.0 update.

While reviewing the PR, I was actually wondering what encode_sort_key in syntax

sort_key VARBINARY AS encode_sort_key(
    [...]
)

does. The only English reference I found was this, encode_sort_key is not described in the official Starrocks docs. I guess I confused this column with the table sort key (which btw. the ClickHouse entry defines as well) due to its name.

@murphyatwork Does encode_sort_key concatenate its arguments? And will the benchmark queries read from sort_key instead of the data column?

[wangmj17]

@murphyatwork Does encode_sort_key concatenate its arguments? And will the benchmark queries read from sort_key instead of the data column?

Hi, does that mean it is allowed if they won't read generated column in queries and only use generated column for optimizing data distribution?

[murphyatwork]

You are referring to the recent Starrocks 4.0 update.

While reviewing the PR, I was actually wondering what encode_sort_key in syntax

sort_key VARBINARY AS encode_sort_key(
    [...]
)

does. The only English reference I found was this, encode_sort_key is not described in the official Starrocks docs. I guess I confused this column with the table sort key (which btw. the ClickHouse entry defines as well) due to its name.

@murphyatwork Does encode_sort_key concatenate its arguments? And will the benchmark queries read from sort_key instead of the data column?

The function concatenates arguments to act as the ORDER BY, resembling ClickHouse's ORDER BY but with additional storage overhead.
Moreover, the query does not directly access the key.

[xzx0xzx]

Based on the discussion above, I think the configuration used here is somewhat controversial.
Would it be fairer to remove these configurations for a more balanced comparison across databases?

Some systems (e.g., Snowflake, PostgreSQL) do not support such configurations.
Relying on them to accelerate queries might not represent engineering best practices in real-world scenarios — especially considering that, as mentioned in this issue, data is often highly dynamic and its schema may not be known in advance.

[murphyatwork]

Based on the discussion above, I think the configuration used here is somewhat controversial. Would it be fairer to remove these configurations for a more balanced comparison across databases?

Some systems (e.g., Snowflake, PostgreSQL) do not support such configurations. Relying on them to accelerate queries might not represent engineering best practices in real-world scenarios — especially considering that, as mentioned in this issue, data is often highly dynamic and its schema may not be known in advance.

• The ORDER BY clause is a versatile and highly effective method for schema tuning, particularly in OLAP databases such as Databricks, BigQuery, ClickHouse, and StarRocks. It is widely utilized in real-world applications.
• Benchmarks are inherently subjective and tailored to reflect specific real-world scenarios rather than being universally fair. In the case of StarRocks, users often meticulously design the DISTRIBUTE KEY and ORDER BY clauses to optimize performance. However, to ensure comparability with ClickHouse, we opted to exclude the DISTRIBUTE KEY, even though this compromises performance. Despite this adjustment, we maintain that ORDER BY accurately mirrors practical use cases, with fields like user_id and timestamp often serving as suitable candidates, that can be pre-established.
• "JSON schema is highly flexible and dynamic", that's true, so In StarRocks, you don't need to define fields or specify data types when creating a table—the storage engine automatically interprets the JSON structure.

[wangmj17]

If my understanding is correct,

CREATE TABLE bluesky (
    id BIGINT AUTO_INCREMENT, 
    data JSON NOT NULL,
    sort_key VARBINARY AS encode_sort_key(
        get_json_string(data, 'kind'),
        get_json_string(data, 'commit.operation'),
        get_json_string(data, 'commit.collection'),
        get_json_string(data, 'did')
    )
)
ORDER BY (sort_key);

has almost the same effect as

CREATE TABLE bluesky (
    id BIGINT AUTO_INCREMENT,
    data JSON NOT NULL,
    kind STRING AS get_json_string(data, 'kind'),
    operation STRING AS get_json_string(data, 'commit.operation'),
    collection STRING AS get_json_string(data, 'commit.collection'),
    did  STRING AS  get_json_string(data, 'did')
)
ORDER BY (kind, operation, collection, did);

The only difference is that the second approach reads generated columns in the query, while the first one does not. Our system—Alibaba Cloud Hologres—supports both methods, and we conducted a performance comparison between them. The results show limited difference in performance.
I suspect the first approach is used to avoid directly querying flattened JSON fields. However, this remains somewhat ambiguous, since the JSON fields are effectively flattened and stored as separate regular columns anyway. Personally, I don't fully support this method, but if JSONBench officially recognizes it as valid, many systems—including ours—could benefit significantly, potentially improving benchmark results considerably.

[rschu1ze]

Thanks for the good discussion, both "sides" have valid and reasonable arguments. To settle this dispute, I clarified the benchmark rules (PR). Tl;dr is that the long-standing practice to create indexes (including the sort key in Starrocks) is now legalized.

@wangmj17 Feel free to optimize the Hologres submission accordingly.

[wangmj17]

Thanks for the good discussion, both "sides" have valid and reasonable arguments. To settle this dispute, I clarified the benchmark rules (PR). Tl;dr is that the long-standing practice to create indexes (including the sort key in Starrocks) is now legalized.

@wangmj17 Feel free to optimize the Hologres submission accordingly.

Just to confirm, it is valid for me to create table like this?

CREATE TABLE bluesky (
    id BIGINT AUTO_INCREMENT,
    data JSON NOT NULL,
    kind STRING GENERATED ALWAYS AS data->>'kind',
    operation STRING GENERATED ALWAYS AS data->'commit'->>'operation',
    collection STRING GENERATED ALWAYS AS data->'commit'->>'collection',
    did  STRING GENERATED ALWAYS AS  data->>'did'
)
WITH (clustering_key= 'kind, operation, collection, did');

[rschu1ze]

Yes, that is okay.

[wangmj17]

Yes, that is okay.

Thank you for you reply! What about distribution key (or in some other systems may be called shard key or bucket key) which is usually used by distributed databases to control data distribution in different shards/buckets ?

[rschu1ze]

Thank you for you reply! What about distribution key (or in some other systems may be called shard key or bucket key) which is usually used by distributed databases to control data distribution in different shards/buckets ?

Sharding is a type of partitioning. The benchmark rules at the moment don't say something about that. The reason is (I think) that all existing JSONBench entries are for on-premise single-node databases, whereas sharding especially is important only for scale-out (e.g. database-as-a-service).

Related to that, the Starrocks submission (on-premise, single-node) avoided partitioning for better comparability (from further up):

However, to ensure comparability with ClickHouse, we opted to exclude the DISTRIBUTE KEY, even though this compromises performance. Despite this adjustment, we maintain that ORDER BY accurately mirrors practical use cases, with fields like user_id and timestamp often serving as suitable candidates, that can be pre-established.

Anyways. multi-node or cloud databases sit in their own category. I think it is okay to set a distribution / sharding key in this case, especially if it is crucial for data distribution between nodes.