StarRocks Lakehouse: Internal Tables vs. Paimon/Iceberg – 5-10x Performance Gains

TPCH Benchmark: Revealing the True Performance Profile of Lakehouse

The author ran a 100 GB TPCH dataset on StarRocks as the query engine, comparing StarRocks internal tables, Paimon tables, and Iceberg tables. The results show a clear divide:

StarRocks internal tables (blue) : Dominate most queries, with Q1–Q17 high-frequency analytical scenarios responding in milliseconds, fully demonstrating OLAP speed.

Paimon/Iceberg lake tables : Exhibit higher latency on complex queries such as Q18 and Q22, but this is the trade-off for storage flexibility — they serve long-term archiving, multi-engine sharing, and cold-data沉淀.

This benchmark draws a sharp scenario boundary for lakehouse architecture selection.

Production Selection Rules: Put Each Table Type on Its Sweet Spot

1. StarRocks Internal Tables: The Performance Ceiling for Hot Data

Choose internal tables when the workload requires:

Real-time dashboards & high-frequency BI : e.g., e-commerce real-time GMV monitoring, financial second-level reconciliation — millisecond response lets decisions move faster.

High-concurrency queries : Tens of thousands of simultaneous report users; the MPP architecture handles the load easily.

Complex aggregation analysis : Multi-dimensional cross-analysis, multi-level drill-down; pre-aggregation (RollUp) and indexing cut compute time by over 90%.

2. Paimon/Iceberg Lake Tables: The Cost-Optimal Solution for Cold Data

Lake tables shine when the business needs:

Long-term data archiving : Moving half-year-old orders or year-old logs to Paimon/Iceberg reduces storage cost by 70%+.

Multi-engine collaboration : Spark writes, Flink streams, StarRocks analyzes — lake tables become the best carrier for “one data, many engines”.

Historical data traceability : Iceberg’s time-travel lets you retrieve “yesterday 10 AM’s report data”, satisfying audit and compliance.

Performance Optimization: Key Actions to Bring Lake Tables Close to Internal Tables

Through the following strategies, Paimon/Iceberg on StarRocks can achieve 5–10x performance gains , even approaching internal-table speed in some scenarios.

1. Metadata Layer: Equip Lake Tables with a “Navigator”

Partition pruning + predicate pushdown : Always include partition conditions (e.g., WHERE dt = '20241115'); StarRocks pushes filters down to the lake table, scanning only needed data.

Lake-table native optimizations : Paimon can enable Bloom filter indexes to accelerate point queries; Iceberg v2 format with Manifest statistics turns “full-table scans” into “partition-level scans”.

2. Data Access Layer: Make Lake Tables “Hot”

Data Cache pre-warming : Use CACHE SELECT to pull hot lake-table data onto StarRocks local disks:

CACHE SELECT * FROM paimon_catalog.sales.orders WHERE dt = '20241115';

After warming, repeated query latency drops from seconds to milliseconds.

Storage media selection : Mount the lake-table cache directory to SSDs, or point StarRocks datacache_disk_path to high-performance storage, eliminating I/O bottlenecks.

3. Query Execution Layer: Push Compute Down to the Lake

Predicate & aggregation pushdown : Ensure StarRocks pushes filters, sums, counts, etc., to the lake table, reducing data transfer. Paimon supports Upsert pushdown; Iceberg supports predicate pushdown — “compute near data”.

Vectorization & parallelism tuning : Enable vectorized execution ( enable_vectorized_engine = true) and adjust BE parallelism ( parallel_fragment_exec_instance_num), doubling lake-table parsing efficiency.

Enterprise Practice: A Retail Giant’s “Dual-Track Lakehouse” Rollout

A nationwide retail chain generating 10 TB of daily sales data faced slow real-time analytics and expensive historical storage. With StarRocks lakehouse they achieved:

Hot data (last 7 days) : Stored in StarRocks internal tables; store real-time sales dashboard latency dropped from 5 seconds to 300 ms, enabling regional managers to adjust promotions at minute-level granularity.

Cold data (older than 7 days) : Archived to Paimon lake tables; storage cost fell 65%, and historical trend analysis via StarRocks external tables compressed from 20 minutes to 2 minutes.

Multi-engine collaboration : Spark writes nightly offline results into Paimon; StarRocks reads them for daytime BI, closing the batch–stream–analytics loop.

Conclusion: The Ultimate Value of Lakehouse Is “Smarter Data Usage”

StarRocks internal tables and lake tables are not an either-or choice but a hot-cold tiering strategy that plays to each strength . Internal tables own “extreme response”; lake tables own “cost and flexibility”. Deep StarRocks optimizations make them collaborate seamlessly — this is the lakehouse endgame: data that runs fast, stores cheap, and works flexibly.