StarRocks

Each feature = what it is + why it's genuinely good + what happens at the edge.

1. Fully vectorized execution engine + CBO + Pipeline The real thing: C++ vectorized engine (SIMD) with a Cascades-style CBO and Pipeline parallel execution — the optimizer automatically picks join order and join strategy (broadcast / shuffle / colocate) for multi-table joins. This is its structural difference versus ClickHouse (whose join capabilities historically lagged) and the technical root of its "real-time reporting over joins" reputation. The edge truth: the CBO's "intelligence" is entirely statistics-dependent — stale or missing ANALYZE statistics and the CBO picks the wrong join order and strategy; external tables (data on the lake) have inherently weak statistics, so query plans over the lake are discounted; gray-upgrading to v3.5 once required manually disabling low-cardinality optimization (cbo_enable_low_cardinality_optimize=false), showing optimizer features are coupled to upgrade risk. See Deep dive 6.
2. Primary-key model: real upserts on a columnar store The real thing: random updates become "append a new version + primary-index deduplication", keeping columnar bulk-write advantages while achieving second-level visibility; partial updates (column mode) further reduce write amplification. This is the core reason CDC real-time-warehouse scenarios pick it — versus ClickHouse's ReplacingMergeTree (background async dedup, reads still have to cope), StarRocks' primary-key model is more direct for "frequent updates + fast queries". The edge truth: see Deep dive 2 — the primary index is a memory tax, and bad planning fails loudly with primary key memory usage exceeds the limit; DelVector adds read-time filtering overhead; compaction must reclaim old versions promptly or read amplification grows. The primary-key model is not "free updates" — it moves update cost from write time to memory and background merging.
3. Asynchronous materialized views + SPJG transparent rewrite The real thing: introduced in v2.4, rewrite-capable since v2.5; MVs can span multiple tables and data sources (Hive / Iceberg / Hudi / Paimon), with partition-level incremental refresh; the SPJG algorithm automatically matches and rewrites queries (single-table / join / aggregation / union / nested MV) without changing business SQL. This is the source of its "pre-aggregation acceleration" reputation. The edge truth: see Deep dive 3 — "refresh" and "rewrite" are two independent pipelines; data freshness and whether a query hits the MV are two separate questions; v4.1.1 disabled query rewrite for INCREMENTAL / AUTO MVs and rejected FORCE refresh (a behavior change — read the release notes before upgrading); too many candidate MVs slow down the optimizer itself.
4. Shared-data: second-level CN elasticity + Warehouse isolation The real thing: introduced in v3.0; data sinks to object storage, CN compute nodes are stateless and scale in seconds on K8s with load; Warehouses give "one dataset, multiple isolated compute groups" physical isolation, solving the classic MPP problem of "big ETL queries starving small queries". R&D emphasis is visibly shifting to this architecture (most 4.x features land there first). The edge truth: see Deep dive 1 — this trades operational complexity for elasticity: DataCache (StarCache) corruption / eviction, S3 throttling, FE leader failover losing in-flight transactions, tablet explosion crushing FE metadata; and it cannot be converted to/from shared-nothing — one choice, for life.
5. Lakehouse: External Catalogs query the lake directly The real thing: Hive / Iceberg / Hudi / Paimon / Delta / JDBC external catalogs are first-class; StarRocks can query lake data directly and accelerate with MVs — "hot data in the warehouse, cold data in the lake" without moving data. This is its differentiation against pure warehouse engines. The edge truth: external-table performance is capped by lake formats and object-storage latency; stale external-table metadata caches can misjudge "did the base table change", hurting MV refresh correctness; cross-engine lake writes (Format SDK / direct Spark writes) are newer capabilities — verify each one before production use.

Deep dive 1: Real maturity of shared-data — elasticity bought with operational complexity

• Domain: storage architecture / operations
• Mechanism in one sentence: CN compute nodes are stateless, data persists in object storage, a local StarCache (DataCache) accelerates hot data, and Warehouses isolate multi-tenant compute.
• At the edge:
  • DataCache is the first incident cluster: block corruption (checksum mismatch failing publishes/queries), auto-scaling evictions causing S3 round-trips, compaction falling behind triggering score alarms — three of the five documented shared-data root causes relate to cache/object storage.
  • On the S3 side: failed multipart uploads and 503 throttling directly stall writes and compaction.
  • FE leader failover once caused in-flight transaction loss (EditLog gap breaking transaction replay) — "HA failover" and "no data loss" are two separate ledgers here.
  • Runaway tablet counts crush the FE: the official issue admits the ~1GB-per-tablet guidance creates an FE metadata (shard/tablet metadata, GC, RPC) bottleneck at scale, targeting 100GB per tablet — an architecture-level known weakness, not a tuning problem.
  • Historical incident: v3.4.1 shared-data clusters lost metadata under FE leader switch + un-published compaction transactions; the version was pulled, baseline at least v3.4.5 / v3.5.
  • Hard constraints: shared-nothing and shared-data cannot mix in one cluster or convert into each other; CN hardware must be homogeneous.
• Selection implication: shared-data suits teams with spiky loads that can handle K8s operations; assess whether the team can debug object storage + cache before adopting; tablet/bucket planning matters more under shared-data (the FE is a single bottleneck); new clusters go straight to the latest patch, moving an old cluster to shared-data equals rebuilding.
• Sources: official docs (shared-data feature support); official issue #66457 (tablet sizing); community shared-data troubleshooting handbook (five root causes); community version-baseline records (v3.4.1 metadata incident).

Deep dive 2: The primary-key memory tax — upserts are not free

• Domain: storage engine
• Mechanism in one sentence: random updates become "append a new version + primary-index lookup of the old version"; the primary index lives in memory (or on disk via persistent index), reads filter old rows with DelVector, and background compaction reclaims old versions.
• At the edge:
  • The direct consequence of bad planning is the hard error primary key memory usage exceeds the limit (real case: an Airtable data-validation system hit primary-key memory limits with 7,200 buckets and loads stalled).
  • Each tablet has its own ingest memtable and primary-index memtable — tablet count × per-tablet index = total memory; the more fragmented the tablets, the heavier the memory tax (same root as the FE pressure in Deep dive 1).
  • Imports "stuck at 99%" is the classic symptom: LOAD reporting 99% means data is written; taking effect (version publish + index build) is the final 1% — most visible on primary-key tables.
  • Partial updates via column mode combined with GIN inverted indexes once returned corrupted results (fixed in v4.1.4) — new-feature combination paths are bug hotspots.
  • The mitigation enable_persistent_index moves the index to disk — trading memory for read amplification, raising point-lookup latency; not a free switch.
• Selection implication: primary-key tables require upfront "partitions × buckets × estimated rows × primary-key width" memory math; high-frequency CDC scenarios must load-test "write + compaction + query" together, not writes alone; treat enable_persistent_index as a capacity-planning option, not a fire extinguisher.
• Sources: official docs (primary-key model, persistent index); community field reports (Airtable primary-key memory case); official v4.1.4 release notes (GIN + column-mode fix).

Deep dive 3: Materialized views — "refresh" and "rewrite" are two independent pipelines

• Domain: SQL layer
• Mechanism in one sentence: the refresh pipeline (MVRefreshPartitionSelector picks partitions, BaseMVRefreshProcessor executes, then updates BaseTableVisibleVersionMap) governs data freshness; the rewrite pipeline (MvRewritePreprocessor collects candidates + SPJG pattern matching + compensation) governs whether queries hit the MV; they meet inside the optimizer.
• At the edge:
  • "Fresh data" ≠ "query can use it": wrong partition mapping or stale external-connector metadata misjudges "did the base table change", leaving MVs looking fresh while actually stale.
  • Too many candidate MVs slow the optimizer itself: cbo_materialized_view_rewrite_related_mvs_limit defaults to 64, optimizer_materialized_view_timelimit to 1000ms — more MVs mean higher optimizer search cost, and rewrite may be abandoned on timeout.
  • Behavior-change risk: since v4.1.1, INCREMENTAL / AUTO MVs have query rewrite disabled and FORCE / partition refreshes rejected — clusters relying on them must read the release notes before upgrading to 4.1.
  • Stale statistics leave "a plan that hit the MV still suboptimal"; nested MVs need rewrite-depth limits against combinatorial explosion.
• Selection implication: MV governance (naming, partition alignment, count caps) is DBA daily work, not "build and forget"; verify every MV behavior change before upgrading; verify rewrite actually happens with EXPLAIN (look for MV Scan) instead of relying on "it feels faster".
• Sources: official docs (MV documentation, v4.1.1 release notes); community MV deep-dives (SPJG, rewrite matrix, refresh strategies); community source-level analysis (refresh/rewrite dual pipelines, optimizer cost).

Deep dive 4: The invisible write bottleneck — version publishing

• Domain: writes / transactions
• Mechanism in one sentence: Stream Load runs prepare + commit two-phase; data must "publish its version" (publish version) to become visible after landing; Group Commit merges high-frequency small batches into fewer versions; labels provide deduplication.
• At the edge:
  • High-frequency small writes (e.g. per-row CDC) inflate version counts: more versions → more compaction pressure → higher query read amplification — a tax that "real-time" pays against "query performance".
  • When publishing stalls, txn_max_committed_pending_publish_ms (new in v3.5) raises the alarm — "committed but invisible" is a StarRocks-specific intermediate state; check this metric first, not just write QPS.
  • FE leader failover can lose in-flight transactions under shared-data — "exactly-once" is achieved by the Flink connector + transaction interface together, not an unconditional storage guarantee; any link (FE failover, label-dedup window) degrading the semantics downgrades the guarantee.
  • Publishing lag directly inflates HTAP / real-time-reporting "data freshness" — what the business thinks of as "second-level real-time" may be "second-level writes + minute-level visibility".
• Selection implication: real-time-warehouse POCs must load-test both write throughput and publish lag, and watch the compaction queue; run end-to-end semantics tests for CDC pipelines (including FE failover drills), not just happy paths.
• Sources: official docs (Stream Load transaction interface, Group Commit); official release notes (v3.5 new metric); community troubleshooting handbook.

Deep dive 5: Tablet / bucket planning — day-one table design sets the ceiling

• Domain: data distribution
• Mechanism in one sentence: tables are cut by partitions, partitions by buckets into tablets; the tablet is the unit of data distribution, replication, compaction, and scheduling.
• At the edge:
  • Too many tablets: each has an independent memtable / index / compaction; BE memory and CPU scale linearly with tablet count; the FE must maintain vast shard/tablet metadata, hitting CPU, memory, GC, and RPC limits first (official issue #66457 calls this an architecture-level weakness).
  • Too few / skewed tablets: writes and compaction on a single tablet are single-threaded — one skewed tablet bottlenecks the whole pipeline.
  • The official ~1GB-per-tablet (compressed) guidance is an empirical rule, not adaptive — automatic tablet splitting since v4.0, production maturity pending validation.
  • Picking the wrong bucket key (e.g. a high-cardinality column hash that skews) means rebuilding the table later.
• Selection implication: table design requires "data volume / partition count / bucket count / tablet size" math; large tables prefer time partitioning + sensible bucketing; put tablet counts on the routine patrol checklist (FE metadata pressure dies slowly, not loudly).
• Sources: official docs (Data_distribution, including MERGE TABLETS / range-distribution limits); official issue #66457; community capacity-planning rules of thumb.

Deep dive 6: The CBO's strengths and weaknesses — statistics are oxygen

• Domain: SQL layer
• Mechanism in one sentence: the Cascades-style CBO estimates costs from statistics (cardinality, histograms) collected by ANALYZE to decide join order, join strategy (broadcast / shuffle / colocate), and MV hits.
• At the edge:
  • Stale or missing statistics make the CBO pick the wrong join order, broadcast a huge table (memory blowup), or shuffle when colocate was right — "the query suddenly got slow" always suspects statistics first.
  • External tables (Hive / Iceberg etc.) have inherently weak statistics; complex queries over the lake are systematically worse-planned than on internal tables.
  • New optimizer features couple to upgrades: gray-upgrading to v3.5 required disabling low-cardinality optimization first (cbo_enable_low_cardinality_optimize=false) — optimizer behavior is version-dependent, so core-report plans need regression checks after upgrades.
  • Low-cardinality optimization, Runtime Filters and friends all depend on correct cardinality estimates from statistics.
• Selection implication: make ANALYZE a scheduled job in the operations runbook (especially after ETL); diff core-report execution plans before and after upgrades; set separate expectations for external-table query performance in lakehouse scenarios — don't hold lake queries to internal-table benchmarks.
• Sources: official docs (CBO, statistics, v3.5 upgrade notes); community consensus.

Strengths get depth too. Each = why it's real + its edge and limits.

1. Fast queries, especially multi-table joins
   • Why it's real: fully vectorized engine + CBO + Pipeline; TPC-H / TPC-DS style complex analytics are its home turf; community field reports of "noticeably faster than Hive / Presto after migration" (vendor-claimed multiples not adopted, but the direction of "faster" is community consensus).
   • Edges and limits: speed assumes fresh statistics and sane tablet planning; it can't beat ClickHouse on single-table scan extremes (the vectorization originator with higher compression); external-table queries are object-storage-latency-bound. And benchmarks show a tuned ceiling, not your day-one floor.
   • Sources: community consensus; observed on v3.5 / v4.1.
2. MySQL protocol — BI tools plug right in
   • Why it's real: MySQL protocol on port 9030; Tableau / Superset / PowerBI / DataGrip connect directly, zero learning curve for analysts; generic JDBC drivers work.
   • Edges and limits: compatibility covers "protocol and common query syntax", not MySQL behavior — no stored procedures or triggers, some function semantics differ; BI-generated complex SQL can still trigger bad CBO plans. Low connection cost doesn't mean "any SQL runs fast".
   • Sources: official docs; community consensus. Observed on v4.1.
3. Second-level write visibility, CDC-friendly
   • Why it's real: mature Stream Load / Routine Load / Flink connector pipelines; primary-key upserts visible in seconds; the Flink transaction interface guarantees exactly-once — a mainstream real-time-warehouse pick.
   • Edges and limits: see Deep dive 4 — "second-level writes" ≠ "second-level visibility"; publish lag is the hidden cost; high-frequency small writes pay the version-inflation tax; semantics degrade under anomalies like FE failover. Sign SLAs on "end-to-end visible latency", not "write success".
   • Sources: official docs; community consensus. Observed on v3.5 / v4.1.
4. Simple architecture, no external dependencies
   • Why it's real: two role types (FE / BE, plus CN), metadata replicated via embedded BDB-JE — no ZooKeeper / HDFS / external coordination; adding nodes rebalances automatically. Genuinely fewer moving parts than ClickHouse (shard planning + Keeper).
   • Edges and limits: "no external dependencies" ≠ "no single point" — the FE's BDB-JE metadata cluster (3 nodes, leader election <3s) is still central, and the FE bottlenecks first on tablet explosion (Deep dives 1 and 5); shared-data brings "external dependencies" back in S3 form (throttling and multipart failures included).
   • Sources: official docs; community consensus. Observed on v4.1.
5. Genuinely open Apache 2.0 + Chinese ecosystem
   • Why it's real: kernel fully on GitHub (including shared-data), no crippled community edition; complete Chinese docs, responsive Chinese community, low onboarding cost for domestic teams.
   • Edges and limits: open source is code, not operational capability — deep-dive problems (tablet planning, primary-key memory, DataCache debugging) ultimately require reading source or buying commercial support; cloud Serverless bills by compute usage, and self-hosting may be cheaper under sustained high load — model costs against your own load curve. And GitHub stars measure popularity, not quality.
   • Sources: official statements; community consensus. Observed on v4.1.

• One-line positioning: One of the best fits for real-time analytics over many joined tables — CBO + vectorization + primary-key model + lakehouse, at the price of front-loading day-one constraints like tablet planning, primary-key memory, and statistics maintenance.
• Good for:
  • Real-time reporting / BI over multi-table joins (ads, retail, SaaS user-side analytics) with complex query patterns and high concurrency;
  • Flink CDC real-time warehouses needing primary-key upserts with second-level visibility;
  • Lakehouse: hot data in the warehouse, cold data in the lake, one engine querying both;
  • Teams with spiky loads that can handle K8s operations and want shared-data elasticity;
  • MySQL / BI-stack teams wanting "new warehouse, same tools" on the analytics side.
• Not for:
  • Single-table massive log scans chasing extreme throughput (ClickHouse still sets the single-table bar);
  • Systems needing stored procedures / triggers;
  • "De-O" Oracle-replacement projects (no Oracle compatibility);
  • Teams without dedicated data engineers wanting "install and forget" (tablet planning, ANALYZE, compaction watch are all mandatory);
  • Tiny datasets (GB scale) with simple queries — a single-node DuckDB / MySQL is simpler.
• Migration cost:
  • MySQL (analytics side) → Low: protocol-compatible, BI tools connect directly; mainly replacing "row-store thinking" with "columnar + partitioning + bucketing" modeling.
  • ClickHouse → Medium: single-table scan logic must be reshaped to exploit multi-table strengths; ReplacingMergeTree dedup habits become the primary-key model; shard planning becomes tablet planning.
  • Hive / Spark warehouses → Medium: ETL pipelines can stay, the query layer migrates; wide-table models carry over, but partitioning strategies need redoing.
  • Doris → Low-medium: same-origin architecture (FE/BE, port 9030, three data models, Stream Load) is highly similar and easy to pick up; differences in CBO tuning, the MV system, and shared-data details need item-by-item verification.

• Versions and release cadence: GitHub starrocks/starrocks release notes (v4.1.4 2026-08-05, v4.0.14 2026-08-11, v3.5.20 2026-07-23; v4.1.0 container-image issue, v4.1/v3.5 downgrade constraints, v3.5 JDK 17 requirement)
• Technical lineage: community architecture deep-dive (2026-08, Mesa → Palo → Doris → DorisDB → StarRocks timeline; V1.0–V4.1 feature table; commercial ecosystem: CelerData / MirrorShip / cloud-vendor integrations)
• TDE: official BE parameter docs (enable_transparent_data_encryption, introduced in v3.3.1 / v3.4.0 / v3.5.0 / v4.0.0; BE aborts on FE/BE flag mismatch)
• TLS: official v3.4 release notes (MySQL-protocol SSL), SSL configuration docs (ssl_force_secure_transport, error 5205)
• Auditing: official logs.md and FE configuration docs (fe.audit.log, audit_log_modules, JSON format, 30-day retention)
• Privileges: official SSL/LDAP auth docs, v3.4 release notes (secure views); masking-policy syntax from community skill docs, official docs pending verification
• Backup & restore: official Backup_and_restore docs (BACKUP/RESTORE syntax, partition/table/database granularity, single-backup-task limit), v3.4 release notes (shared-data automatic snapshots)
• Stream Load transactions: official Stream_Load_transaction_interface docs (2PC, exactly-once, single-database multi-table since v4.0, single-client concurrency limit)
• Shared-data constraints & troubleshooting: official feature-support-shared-data docs (no mixing/conversion, homogeneous CNs); community shared-data troubleshooting handbook (DataCache / S3 / FE-failover root causes); official issue #66457 (tablet sizing target 100GB); community version-baseline records (v3.4.1 metadata incident)
• Primary-key model: official primary-key model docs; community field reports (Airtable primary-key memory case); official v4.1.4 release notes (GIN + column-mode fix)
• Materialized views: community MV deep-dives (SPJG, rewrite matrix, refresh strategies); community source-level analysis (refresh/rewrite dual pipelines, optimizer cost); official v4.1.1 release notes (INCREMENTAL/AUTO MV rewrite disabled)
• CBO/statistics: official CBO and statistics docs, v3.5 upgrade notes (low-cardinality optimization switch); community consensus
• Doris comparison: community lineage梳理 (neutral facts: common origin, fork ~2020, independent development, similar interfaces); one partisan article's claims ("closed-source commercial modules" etc.) not adopted, cross-verified against official docs
• Complaints collection directions (to be scaled by the LLM pipeline): GitHub Issues, Zhihu, CSDN / V2EX practice posts
• Next review: track production maturity of automatic tablet splitting in v4.1+; verify masking-policy official docs; track cross-database multi-table transactions and multi-client concurrent writes

last_reviewed: 2026-09-29 · reviewed_version: v4.1.x