👋 hey everyone. I have a usecase of an events table where the events table has 100 attributes. I wanted to check if there are any benchmarks around flat vs EAV approach on star rocks. This table will not have any updates and only inserts and would like to optimize read performance over write performance

I have done wide table vs EAV benchmarks before and EAV is a minimum of 3.3x slower. Parquet is awesome at wide tables and so is star rocks.

thanks a ton for this

@Rocky - what information can you provide for users interested in EAV data models on starrocks? Will this be faster than a wide table?

On StarRocks, while you can implement an EAV (Entity-Attribute-Value) data model, it is generally not faster than a wide table for analytical workloads. StarRocks is a columnar storage engine, and its performance optimizations (like vectorized execution and prefix indexes) are designed specifically to excel with wide tables. Here is the breakdown of information regarding EAV vs. Wide Tables in StarRocks: 1. EAV Data Model on StarRocks An EAV model in StarRocks typically looks like a table with columns:

entity_id

,

attribute_name

, and

attribute_value

. * Best Model for EAV: If you must use EAV for point lookups (retrieving all attributes for a single ID), you should use the Primary Key model. This allows for high-concurrency, low-latency lookups. * The Problem: For analytical queries (e.g., "What is the average price of all blue items?"), an EAV model requires complex self-joins,

CASE WHEN

statements, or

PIVOT

operations. These operations are computationally expensive and cannot fully leverage StarRocks' columnar pruning. 2. EAV vs. Wide Table Performance In almost all OLAP (Online Analytical Processing) scenarios, a Wide Table will be significantly faster than an EAV model for the following reasons: Feature | Wide Table (Recommended) | EAV Model Storage | Columnar (highly compressed). | Row-like (one attribute per row, redundant IDs). Data Pruning | Only reads the specific columns needed for a query. | Must scan all rows to filter for specific attributes. Vectorization | High efficiency with SIMD instructions. | Lower efficiency due to row-based filtering/pivoting. Query Logic | Simple SQL (e.g.,

SELECT price FROM table

). | Complex SQL (e.g.,

SELECT value FROM table WHERE attr='price'

). Speed | Fastest for aggregations and filtering. | Slower for bulk analytics; better only for sparse schema flexibility. 3. The StarRocks Alternative: Flat JSON If the reason you are considering EAV is schema flexibility (i.e., you have hundreds of sparse attributes that change often), StarRocks offers a modern alternative that outperforms traditional EAV: The JSON Data Type with Flat JSON. * How it works: You can store your sparse attributes in a single

JSON

column. * Automatic Flattening: Starting from version 3.3.0 (and enabled by default in v4.0), StarRocks features Flat JSON. It automatically detects frequently queried keys inside your JSON and materializes them into internal standard columns. * The Result: You get the flexibility of EAV (adding new attributes without a schema change) with the performance of a wide table (direct columnar access to those attributes). Summary Advice * If performance is your priority: Use a Wide Table. StarRocks handles hundreds or even thousands of columns very efficiently. * If you have highly sparse data: Use a JSON column with Flat JSON enabled. This provides the best balance between flexibility and performance. * Only use EAV if: Your application requires an extremely dynamic, user-defined attribute system and you primarily perform point lookups rather than large-scale data analysis. References • using_starrocks/Flat_json.md • best_practices/query_tuning/schema_tuning.md • knowledge/usage/best-practice.md • sql-reference/data-types/semi_structured/JSON.md

cc @Vassilis Dourdounis

Hi there! I'm Rocky. Comparing Flat JSON and a traditional Wide Table (where each JSON field is manually defined as a separate column) is a common trade-off in StarRocks. While Flat JSON (introduced in v3.3 and enabled by default in v4.0) provides a "middle ground" that offers much better performance than raw JSON, there are still several reasons why a manually defined Wide Table might be preferred for high-performance scenarios. Here are the key issues and limitations of Flat JSON compared to a Wide Table: 1. Performance Overhead * Static vs. Dynamic: In a wide table, the schema is fixed and known at compile time. In Flat JSON, StarRocks must dynamically detect common fields and types during loading. Even though it stores common fields as standard types internally, there is still a small amount of overhead for type inference and handling the "reserved" fields (the parts of the JSON that didn't fit the extracted schema). * Vectorized Execution: Wide tables benefit fully from vectorized execution on standard types. While Flat JSON uses internal sub-columns to achieve similar results, complex queries that fall back to the raw JSON blob will be significantly slower. 2. Lack of Array Flattening * Object Only: Flat JSON is excellent at extracting keys from JSON Objects. However, it cannot currently "flatten" or materialize keys or elements inside JSON Arrays. If your data heavily uses nested arrays and you need to query them frequently, a wide table (potentially using

ARRAY

types) will be much faster. 3. Sorting and Indexing Limitations * Data Sorting: In StarRocks, performance is heavily tied to the

ORDER BY

(sorting) keys, which allow for efficient data pruning. You cannot use a JSON column (or a field within it via Flat JSON) as a sorting key. In a wide table, you can pick your most important fields as the

DUPLICATE/PRIMARY KEY

to drastically speed up filtering. * Secondary Indexes: You cannot currently build Bitmap Indexes or Bloom Filter Indexes directly on the sub-fields within a Flat JSON column. In a wide table, you have full control to add these indexes to any specific column. 4. Query & Join Complexity * Joining/Grouping: JSON columns (even with Flat JSON) have restrictions in

JOIN

,

GROUP BY

, and

ORDER BY

clauses. While the Cost-Based Optimizer (CBO) can sometimes rewrite these if you enable

cbo_json_v2_rewrite

, a wide table with standard types is supported natively across all SQL operations without needing specific session variables or rewrites. * Schema Consistency: Flat JSON relies on a "Sparsity Factor." If a field is only present in 10% of your rows, Flat JSON might not extract it into a sub-column, leaving it in the slow JSON blob. In a wide table, that field is a column regardless, ensuring consistent performance. 5. Loading Speed * Extra Processing: Enabling Flat JSON increases the computation required during the data load process. The more fields StarRocks has to extract and infer, the longer the

INSERT

or

ROUTINE LOAD

will take compared to loading into a pre-defined wide table. Comparison Summary Feature | Flat JSON | Wide Table Schema | Flexible (Schema-on-read feel) | Rigid (Requires

ALTER TABLE

) Performance | High (Good for ad-hoc) | Maximum (Best for fixed reports) Arrays | Not materialized | Fully supported via

ARRAY

type Pruning | Limited (No sorting keys) | Excellent (Uses

ORDER BY

keys) Indexes | Not supported on sub-fields | Full support (Bitmap, Bloom Filter) Rocky's Recommendation: Use Flat JSON when your schema changes frequently or you have "long-tail" fields that you don't want to manage manually. Use a Wide Table for your most critical, high-concurrency queries where you need every millisecond of performance and need to sort or index the data. Let me know if you want to see how to configure the Flat JSON sparsity settings or how to transition from one to the other!