How Fresha Scaled Real‑Time Analytics with StarRocks: A Deep Dive into Their Hybrid Architecture

Current Situation and Challenges

By mid‑2024 Fresha’s data platform suffered from severe architecture mismatch: Postgres (OLTP) was overloaded by ad‑hoc and dashboard queries, causing head‑of‑line blocking and occasional production slowdowns, while Snowflake struggled with high‑frequency near‑real‑time analytics due to cost and latency constraints.

Postgres (OLTP) : Used for core business services but forced to handle wide‑table joins and heavy aggregations, leading to noisy‑neighbor effects and occasional order‑processing slowdowns.

Snowflake (BI / Data Export) : Good for traditional BI dashboards and bulk exports, but unable to sustain the cost and response time required for frequent, near‑real‑time product and operations analysis.

The team needed a solution that could process massive historical data without consuming Postgres resources and support standard protocols to keep migration costs low while delivering predictable performance at scale.

Core Requirements

Offload historical analysis from the OLTP path.

Prefer open formats (Iceberg/Paimon) on object storage, keeping the Lakehouse as the single source of truth.

Support the MySQL protocol and standard drivers to minimize re‑engineering.

Provide predictable scalability for traffic spikes without days of capacity planning.

Achieve sub‑second to minute‑level latency on critical paths while keeping other paths within minutes.

Maintain low operational complexity and avoid custom pipelines.

Why StarRocks?

StarRocks stood out with its hybrid query model: it can perform federated queries over external catalogs for breadth and simultaneously store time‑sensitive metrics in internal columnar tables for depth and performance.

Native columnar storage : Supports detailed, aggregated, and primary‑key models with high‑throughput ingestion (e.g., Flink or Routine Load), providing the shortest path to real‑time metrics.

Lakehouse acceleration : Reads Iceberg/Paimon/Hive tables directly via catalog, pushing down filters and projections to minimize object‑storage scans.

Materialized view auto‑rewrite : Defines incremental or pre‑joined aggregates; the optimizer rewrites matching queries to hit the materialized view.

Compute‑storage separation : Elastic compute scaling without data reshuffling, ensuring predictable cost and latency during peaks.

MySQL protocol compatibility : Works out‑of‑the‑box with common BI tools and client libraries.

New Architecture Overview

The platform can be visualised as a unified ingestion spine that branches into three logical chains:

Real‑time chain : CDC events from Postgres are captured by Debezium, serialized with Avro, and streamed via Kafka to Flink, which writes directly into StarRocks internal tables.

Historical chain : Long‑term facts and slowly changing dimensions are persisted to Iceberg/Paimon by Flink and Spark; StarRocks accesses these via external catalog for federated queries.

Search chain : Text‑heavy workloads are indexed into Elasticsearch; StarRocks’ experimental Elasticsearch catalog exposes these indices as SQL tables.

Engineers connect to a single MySQL endpoint; the optimizer routes queries to the appropriate chain without the user needing to know the data’s physical location.

Home Page Query Performance Optimization

The homepage provides analytics such as “Top Employees”, “Hot Services”, and real‑time sales. Initially powered by Postgres, the queries took 15‑20 seconds and sometimes timed out, also degrading OLTP performance.

After migrating these views to StarRocks, the team imposed a hard requirement of minute‑level data latency. Iceberg alone could not sustain this due to small‑file explosion and compaction pressure, so the hot path was switched to StarRocks internal tables while Iceberg/Paimon remained for long‑term storage.

They built a layered SQL view hierarchy: rt_sales (CDC upsert stream: Debezium → Kafka → Flink → StarRocks) vw_sales_enriched (adds business joins, status filters, partition keys) vw_recent_sales (defines recent‑time window and eligible status)

Higher‑level views such as vw_top_employees_2m and vw_top_services aggregate on the previous layers.

Product teams now query only the top‑level views, relying on StarRocks to push down filters, column pruning, and materialized‑view rewrites, achieving ~200 ms response time even for complex aggregations.

Practice Issues and Solutions

Accurate DDL Migration

The team built an ActiveRecord‑style migration tool that records explicit up/down SQL for each change and stores a declarative schema version in StarRocks. Because many DDL operations are asynchronous, the tool polls until the change reaches a FINISHED state before bumping the version; on failure it rolls back using the down script.

Query Performance Analysis

Using EXPLAIN ANALYZE profiles, they standardized metrics such as scanned bytes, partition hits, join type, and P50/P95 latency to pinpoint bottlenecks (e.g., excessive partitions, unsuitable join strategies, or un‑pushed predicates).

Partition Strategy

Tables are partitioned by date and bucketed by business keys (e.g., provider_id). Filtering predicates are encapsulated inside views to prevent accidental full‑table scans.

Example: vw_recent_sales defines the recent time window and status filter; higher‑level views inherit these constraints while the planner still pushes them down to the storage engine.

Dimension Join Optimization

For fact‑to‑dimension joins they use broadcast for small dimensions and colocate (aligned bucket keys) for large ones; if colocate is impossible they fall back to bucket‑shuffle. Dimension tables are versioned and kept narrow to stay broadcast‑friendly; when they grow, they are promoted to colocate groups with matching bucket counts.

Data Skipping and Index Trade‑offs

StarRocks’ Zone Map and prefix/short‑key indexes are leveraged by default. Bloom filters or bitmap indexes are added only after profiling proves a measurable reduction in scanned bytes, and unused indexes are periodically cleaned.

Schema Evolution

All schema changes start with Avro Schema Registry compatibility checks. Writers publish after validation. Internal tables follow an “add‑only” policy, preferring new columns. Views are versioned (e.g., vw_sales_enriched_v2) with a pointer view ( vw_sales_enriched) that atomically switches after backfill completes. CI pipelines block any change that could break downstream models.

Summary

StarRocks has become Fresha’s reliable core for daily analytics, offering a unified SQL entry point that seamlessly integrates real‑time, historical, and search workloads. The compute‑storage separation guarantees stable performance under peak loads, while the optimizer and materialized‑view rewrite mechanisms let engineers write simple SQL without worrying about data placement. The architecture delivers sub‑second query latency, minute‑level data freshness, and preserves the Lakehouse as the single source of truth.