Originally published at: StarRocks blog
About Cisco Webex
Cisco Webex is Cisco's video conferencing software. It covers voice and video calls, online meetings, meeting room devices and instant messaging.
Pinot lacked multi-table joins and subqueries, so complex queries went through Trino as slow federated queries in a second SQL dialect. One engine for both workloads removed that split.
Key takeaway · One engine instead of two
The original post was written by Xu Bai, Cisco Software Engineer and Apache Amoro PPMC member, and Hongkun Xu, Cisco Senior Software Engineer.
The Webex team uses analytics in three ways: troubleshooting performance problems from meeting metrics such as join latency and call quality, analytical queries and data extraction by data scientists and analysts, and dashboards and reports for real-time monitoring.
The challenge
Before the migration, several engines each covered part of the workload. Apache Pinot served low-latency real-time queries but had no subqueries or multi-table joins. Trino covered complex logic and read the data lake through Apache Iceberg, so slow federated queries across both engines were common. TiDB stored aggregated results, Apache Kyuubi on Spark ran workflow orchestration, and data sat on AWS S3 and private HDFS clusters, a split driven by global compliance requirements.
Most of the complaints the team heard were about Pinot:
- Operations: Trino and Pinot each needed their own monitoring, which raised maintenance effort and cost.
- Stability: Pinot instances crashed at times, so the team ran two replicas with a ZooKeeper-based failover setup.
- Missing features: Without joins or subqueries in Pinot, and without materialized views for federated Trino queries, a query over 180 days of data scanned the same data repeatedly.
- Data quality: Fixing bad data in a real-time Pinot table meant replaying its whole time range, adding load and reducing freshness.
- User experience: Analysts worked in several SQL dialects, and engineers tuned slow queries by hand.
What the next platform had to do
- Fast joins and subqueries, plus materialized views for frequent queries.
- Semi-structured data whose fields and dimensions differ from date to date.
- One query system for analysts and the platform team.
- Lower total cost of ownership: data on S3 or HDFS instead of Pinot, with ZSTD compression and compaction.
The solution
Webex moved to an architecture with storage and compute separated, then compared StarRocks and Trino on its own production queries. About 70% of the queries ran faster on StarRocks, and average latency was about 50% lower. Common queries defined in advance as materialized views, and reused across scenarios, ran more than 10× faster.
Autoscaling on Kubernetes
On Kubernetes, a Horizontal Pod Autoscaler (HPA) watches CPU and memory usage of the compute node (CN) pods and adds or removes compute with load. A pod is reclaimed only after its SQL has finished, so running queries are not cut off.
Self-service access and one query layer
Permissions are handled in one framework: Apache Ranger holds the rules, LDAP handles authentication, and both are managed through Cisco's Unified Data Platform (UDP) Auth system. Access requests are approved automatically by maintainers or a workflow, which replaced manual authorization in Trino and reduced administrative work. Automatic dialect conversion for Trino and Pinot SQL means analysts no longer need several syntaxes, and results stay consistent across architectures.
Why StarRocks suited the Pinot migration
- Partitions: Pinot keeps several days in one segment, so exact backfills were impossible. StarRocks reloads only the affected daily partitions.
- DML: Pinot has no INSERT, UPDATE or DELETE, so fixes meant full reloads. StarRocks supports standard DML.
- Joins and subqueries: StarRocks runs them natively, with operators pushed down to storage, instead of federating through Trino.
- Migration tooling: Backup & Restore and the Cross-cluster Data Migration Tool sync data automatically; Pinot had no mature equivalent.
- Materialized views: Instead of external aggregation jobs, StarRocks refreshes them incrementally every 15 minutes or every hour.
- Built-in ETL: Transformations such as INSERT INTO SELECT run inside StarRocks.
- Compaction: Pinot upsert tables keep duplicate rows and grow. StarRocks merges them and keeps the latest data.
- MPP and vectorization: Queries over billions of rows return in about one second.
Pinot Dialect Transformer
Business teams saw little value in rewriting SQL that already performed well on Pinot, so the first step was a Pinot Dialect Transformer that converts Pinot SQL into syntax StarRocks accepts. Following the approach of the Trino dialect, it rewrites function names, arguments and syntax while parsing. In tests it converted more than 70% of queries automatically. The transformer can be extended with further functions and syntax rules.
Rack-based resource isolation
StarRocks does not yet support tenants, so Webex isolates workloads with rack policies. In a 30-node cluster, each service, such as Device, Meeting or Calling, gets its own machine group, so heavy queries in one do not slow the others.
At first, StarRocks could not place replicas automatically within one rack label such as rack:device, which forced sub-racks (device1, device2 and so on). With the community, the team added an optional scheduling parameter that controls replicas per rack, and improved rebalancing and scheduling to respect rack topology and availability.
Flat JSON, bucketing and sort keys
For large volumes of semi-structured data, the team tried Flat JSON, which stores JSON fields as materialized subcolumns. Disk usage dropped by about 80%, although missing index support still adds some query overhead. The team also redesigned bucket and sort keys to keep different kinds of data in separate tablets and segments. This improved data locality and, in tests, cut query latency by up to 80% compared with the earlier design.
The results
- Query performance: about 70% of production queries faster than on Trino, about 50% lower average latency, and more than 10× faster common queries with materialized views.
- Semi-structured data: about 80% less disk with Flat JSON, and up to 80% lower latency from bucket and sort key design.
- Migration: about 90% of Trino queries and more than 70% of Pinot queries converted without manual rewrites.
- Operations: compute that follows load on Kubernetes, and self-service access through UDP Auth.
With Trino and Pinot no longer running in parallel, analysts use one interface, with one SQL syntax, for troubleshooting, analysis and dashboards across Webex's global services.
What's next
- Query Insight: show execution time per operator and suggest tuning changes.
- Semi-structured data: keep improving Flat JSON and push Variant Shredding forward to match Pinot's performance.
- Text search: wider inverted index support (primary key tables, separated storage and compute), and an evaluation of Tantivy or StarRocks' built-in text search.