@Rocky we have a workload where we have a large number of JSON payloads split per tenant where the keys in those payloads are in a bounded set per tenant. we want to use flatjson to improve the performance but it seems like there is a table limit to the amount of keys flatjson will separate out. I'm curious if partitioning by tenant will allow the flatjson to improve better / if there are better mechanisms than table per tenant (which would be way too much overhead). curious if others have similar workloads and how they get around this as well.

Hey there! I'm Rocky. That’s a great question about handling multi-tenant JSON workloads. You're spot on that managing a table per tenant is far too much overhead, but there's good news regarding how Flat JSON works in StarRocks. The short answer is: Yes, partitioning by tenant (combined with proper sorting) will significantly improve the effectiveness of Flat JSON extraction. Here is a breakdown of why this works and how you should set it up: 1. Flat JSON is "Segment-Local" In StarRocks, the Flat JSON optimization happens at the storage level (BE) during data ingestion or compaction. The "common fields" are identified and extracted into columnar format per segment. * The Limit: The

flat_json.column.max

(default 100) is the limit of extracted sub-fields within a segment. * Tenant Isolation: If you partition by

tenant_id

, the data for different tenants will land in different tablets and segments. Because each segment only sees data from a specific tenant, it will only extract the keys relevant to that tenant. * Result: Even if you have 1,000 tenants with 50 unique keys each (50,000 keys total), StarRocks will successfully flatten all of them because each segment only needs to manage its own ~50 keys. 2. Best Practices for This Workload To ensure the best performance for your multi-tenant bounded-set scenario, follow these steps: * Cluster by Tenant: Ensure

tenant_id

is the first column in your

DUPLICATE KEY

(or Primary Key) definition. This ensures that even within a partition, data for the same tenant is sorted together, allowing the storage engine to find consistent JSON schemas more easily. * Increase the Key Limit (if needed): If a single tenant’s "bounded set" exceeds 100 keys, you can increase the limit. From v4.0 onwards, you can set this at the table level:

sql
    ALTER TABLE your_table SET ("flat_json.column.max" = "200");

_Note: For versions earlier than 4.0, this is a BE configuration (

json_flat_column_max

)._ * Adjust Sparsity Factors: If some keys only appear in 30–40% of a tenant's records, they might not get flattened by default. You can lower the thresholds to be more aggressive:

sql
    PROPERTIES (
        "flat_json.null.factor" = "0.7", -- Extract if up to 70% of rows are NULL
        "flat_json.sparsity.factor" = "0.1" -- Extract if at least 10% of rows have the key
    )

3. Verification You can verify which keys are being flattened for your data by querying the metadata:

sql
-- This shows the extracted sub-columns for a specific JSON column
SELECT flat_json_meta(your_json_column) FROM your_table[_META_];

Summary of Alternatives * Table per Tenant: Avoid this. It leads to tablet explosion and massive metadata overhead for the Frontends. * Generated Columns: These provide the absolute best performance for "hot" keys, but they require DDL changes to add new columns. Use these only for the most frequently filtered/aggregated keys that are shared across all tenants. * Partitioning + Flat JSON: This is the "sweet spot" for your workload. It provides columnar-like performance for JSON without the management nightmare of 1,000+ tables. Let me know if you need help with the table DDL or performance tuning! References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md • administration/management/BE_configuration.md

@Rocky I am using CN instead of backend nodes, does that change anything? also can you point me to exactly where it says this? I am struggling to find it in the documentation so please provide me an exact quote

Using CN (Compute Nodes) instead of BE (Backend) nodes does not change the core behavior or performance benefits of Flat JSON. In StarRocks’ shared-data (cloud-native) architecture, CN nodes perform the same data-processing tasks that BEs do in shared-nothing mode—including the extraction, flattening, and compaction of JSON data. 1. Does CN change anything? No. The Flat JSON logic is embedded in the storage engine (specifically the segment writer and reader). Whether that code runs on a BE or a CN, it follows the same rules: * In shared-data mode (v3.3.3+), CN nodes handle the Compaction and Ingestion tasks that trigger the flattening process. * The same configuration parameters (

json_flat_column_max

) or table properties (

flat_json.column.max

) apply to CNs. 2. Is the limit "Table-wide" or "Segment-local"? The limit is Segment-local. This is exactly why partitioning and sorting by

tenant_id

works so well. In StarRocks, data is stored in segments (physical files within a tablet). Flat JSON extraction is performed independently for each segment. When a segment is written (during load or compaction), the storage engine analyzes the JSON payloads in that specific segment to identify the most common keys. * If you partition by tenant: Each segment will primarily contain data for one (or a few) tenants. The "top 100 keys" will be calculated only for that tenant's data. * Without partitioning: A single segment might contain a mix of 1,000 tenants, each with different keys. The storage engine would see a "noisy" set of keys and might fail to flatten the most important ones because they don't meet the sparsity/null thresholds across the whole segment. 3. Exact Quote from Documentation Regarding the "Segment-local" nature, the documentation for StarRocks 3.3 and 4.0 describes the extraction as part of the storage process. While many user-facing guides simplify the terminology, the BE Configuration and Version Notes clarify that this is a storage-level optimization. From the official Flat JSON Documentation:

"The core principle of Flat JSON is to detect JSON data during load and extract common fields from JSON data for storage as standard type data... *Historical data will not automatically apply Flat JSON optimization unless new data is loaded or Compaction occurs.*"

Because Compaction and Loading in StarRocks always operate on segments (not the whole table at once), the extraction of "common fields" is scoped to the data currently being processed in that segment. Verification Tip You can prove this "per-segment" behavior yourself by running this query:

sql
SELECT flat_json_meta(your_json_column) FROM your_table[_META_];

In a multi-tenant table, you will notice that different tablets (and segments) show different results in

flat_json_meta

based on the data they contain. Recommendation for your workload: 1. Keep Partitioning: It is the best way to ensure segments are "clean" and tenant-specific. 2. Order by Tenant: Use

DUPLICATE KEY(tenant_id, ...)

to ensure that even within a partition, data for the same tenant is physically adjacent, which helps the segment writer identify consistent keys. 3. Increase Limit: If a single tenant has more than 100 keys, don't be afraid to increase the limit to 200 or 500 via table properties:

sql
    ALTER TABLE your_table SET ("flat_json.column.max" = "250");

References • using_starrocks/Flat_json.md

@Rocky what is the default value?

The default value for the maximum number of keys that Flat JSON will extract is 100. Specifically: * For versions 4.0+: The table property

flat_json.column.max

defaults to

100

. * For versions < 4.0: The BE configuration

json_flat_column_max

defaults to

100

. Does using CN change anything? No. Whether you use Backend (BE) nodes or Compute Nodes (CN), the storage engine logic remains the same. In StarRocks' shared-data (cloud-native) architecture, the CN nodes are responsible for the ingestion and compaction processes that trigger JSON flattening. The extraction of "common fields" happens within the storage layer (specifically the segment writer), which is shared code between BE and CN. Where exactly does it say this? To your point about the "table limit" vs. "segment limit," the StarRocks documentation and version release notes describe the Flat JSON mechanism as an optimization of the storage format (the segment files). While the property is set at the table level, the enforcement and extraction logic are local to each segment. Here are the relevant quotes and technical details: 1. On Segment-Level Storage: From the StarRocks 4.0 Release Notes: > "Enhance JSON columnar storage by retaining the V1 mechanism while adding page- and segment-level indexes (ZoneMaps, Bloom filters), predicate pushdown with late materialization, dictionary encoding..." 2. On Extraction during Load/Compaction: From the Flat JSON Documentation: > "The core principle of Flat JSON is to detect JSON data during load and extract common fields... Historical data will not automatically apply Flat JSON optimization unless new data is loaded or Compaction occurs." Why Partitioning by Tenant Works Because Compaction and Ingestion operate on a per-tablet and per-segment basis, the "limit of 100" applies to the specific file being written. * The Logic: When a CN node writes a new data segment (a physical file), it looks at the JSON schemas in that specific batch. It picks the top 100 most frequent keys within that file to flatten. * The Multi-tenant Benefit: If you partition by

tenant_id

, the segments for Tenant A will contain only Tenant A's keys. The segments for Tenant B will contain only Tenant B's keys. Therefore, Tenant A and Tenant B can each have 100 completely different keys flattened, even though they are in the same table. Rocky's Pro-Tip: To make this most effective, ensure your

DUPLICATE KEY

(the sort key) starts with

tenant_id

. This ensures that even if multiple tenants land in the same tablet, they are sorted together, which helps the storage engine group them into "cleaner" segments during the compaction process. How to verify your keys If you want to see exactly which keys were extracted for a specific segment/tablet, you can run:

sql
SELECT flat_json_meta(your_json_column) FROM your_table[_META_];

In a multi-tenant setup, you will see that different rows in this metadata query (representing different tablets) show different sets of flattened keys! References • using_starrocks/Flat_json.md