Originally published at: SmartNews, Inc. on Medium
About SmartNews
SmartNews is a news app built around getting reliable, high-quality information to the readers who need it. Its data platform runs large-scale analytics for content ranking, recommendations and ad optimization.
SmartNews ran advertiser analytics on ClickHouse and ad-hoc and ETL work on Trino, with the same data kept in both. It is moving both onto one engine.
Key takeaway · One engine for two workloads
The original post was written by the SmartNews team.
The platform grew one workload at a time, each on the system that suited it:
- Ad-hoc analytics: data scientists and analysts used Trino on Apache Iceberg and Hive for exploration, evaluating A/B tests and dashboards.
- ML feature ETL: Trino also prepared and materialized machine learning features, acting as a lightweight feature store for smaller models.
- Customer-facing analytics: ClickHouse served the metrics advertisers see in the Ads platform, such as spend and performance, with data refreshed every minute or so.
- Storage: the Iceberg and Hive data lake was the system of record. Real-time paths also wrote a batch copy into Iceberg for recovery and consistency.
At the time of the evaluation, ClickHouse held about 20 TB, took in 3 TB of new data per day under a strict, cost-driven TTL, and served an average of about 260 QPS against a p95 target of 200 ms. The Trino side covered 4.4 PB of Iceberg tables (2.6 TB ingested daily) and 2 PB of Hive tables. It handled more than 200K non-trivial queries a day, peaking at 94K in one hour. The average query scanned 450 GB (1.6 TB at p95, 5.7 TB at p99), and analytical queries finished in under 10 seconds at p95.
The challenge
Two systems meant the same data lived in ClickHouse and in Hive/Iceberg. Two pipelines added operational work, made governance harder, slowed schema changes and at times let the copies drift apart.
On the real-time side, ClickHouse was the main constraint:
- Concurrency: the SELECT workload ran at about 100 QPS and needed to reach about 300 QPS soon. As load rose, ClickHouse grew unstable, tail latency spiked, and holding response times meant heavy over-provisioning.
- Denormalization: because joins were limited, the team maintained large pre-joined wide tables. Any schema or logic change triggered reprocessing and backfills.
- Narrow scope: the team deliberately kept ClickHouse to a few use cases, even ones that would have gained from fresher data.
On the batch side, the Trino coordinator was a single point of failure. When it went down, every running query failed and the cluster had to be recovered by hand, which had already caused several incidents. Across both systems, duplicated and denormalized data kept compute, storage and maintenance costs rising.
The solution
SmartNews looked for one platform that could handle both low-latency, high-concurrency serving and large ad-hoc and batch workloads. It considered Snowflake, Databricks and engines similar to Trino or ClickHouse, including Presto, Dremio and Apache Druid. In the team's assessment, Snowflake and Databricks fell short on the concurrency and freshness that customer-facing queries need, and cost and migration effort at SmartNews' scale ruled them out. The other engines covered either flexible analytics or real-time performance, not both. PhoenixAI Cloud, powered by StarRocks, was the one option that met both sets of needs, since it can query both streaming and batch data in place on Apache Iceberg.
In the new architecture, SmartNews replaces both ClickHouse and Trino with a single engine, PhoenixAI Cloud (powered by StarRocks). Flink feeds PhoenixAI Cloud, which serves advertisers and reads and writes Iceberg for analysts. Real-time analytics, ad-hoc queries and ETL run on one system, without the overhead of running two.
The team also checked enterprise readiness:
- Extensibility: custom Java UDFs were built and deployed.
- Security and governance: data masking, column-level access control, Apache Ranger integration and LDAP authentication all worked. The Group Provider feature was not tested in the POC; it is available in the newer version SmartNews plans to adopt.
- Connectivity: JDBC, Hive Metastore and MySQL connections were validated, plus the web console, Java and Python clients over the MySQL protocol, and Redash.
The results
The ClickHouse comparison used production queries on production data. The Trino comparison was a POC on production-like workloads.
Advertiser analytics vs. ClickHouse
The team ran a life-time-spend style query against both engines at 50, 100, 200 and 400 QPS, then pushed StarRocks to 800 QPS. It also tested daily non_break and hourly break aggregations, materialized views, scale-in, scale-out and disk expansion, HLL accuracy on about 4M rows grouped by year, and approximate distinct counts on about 50B rows. ClickHouse ran as two 16 vCPU / 64 GB pods (one active, one standby) plus a ClickHouse Keeper pod. StarRocks also ran in cluster mode, with one pod per node.
ClickHouse was fast for single queries, but past roughly 100 QPS its latency climbed, reaching a P95 of about 0.8 s at 400 QPS. StarRocks kept tail latency lower and steadier and reached 800 QPS with mean latency staying roughly flat. Results on the detailed query patterns were mixed: StarRocks was often ahead on P95, while ClickHouse was sometimes slightly faster on average. HLL counts were accurate on both; StarRocks matched the ground truth almost exactly, and ClickHouse came out slightly low.
Ad-hoc and ETL vs. Trino
Trino ran as a custom 476 build on ARM nodes in EKS (40 CPU / 330 GB coordinator, 28 CPU / 230 GB workers). PhoenixAI Cloud ran a custom 3.5.x build on Graviton EC2 nodes: an m7g.16xlarge FE and r7g.8xlarge BEs.
Replaying more than 100 production queries on clusters of the same size, PhoenixAI Cloud was about 3.6× faster on reads and 1.8× faster on ETL/write without tuning. Light tuning raised the speedup to 5×.
For a load test, the team repeatedly ran 18 heavy analytical queries. PhoenixAI Cloud completed all of them under high concurrency. Trino used more than 2.5× the compute and still did not finish the full set, with fixed capacity or with auto-scaling, and the queries it did finish were slower.
What's next
- Consolidate the ClickHouse and Trino workloads on PhoenixAI Cloud.
- Use efficient joins on fresh data for more interactive Ads analytics: richer advertiser dashboards, campaign optimization and finer-grained performance insights.
- Move storage from Hive to Iceberg, making Iceberg the single source of truth for historical and batch data, and retire the Hive compute layer.
- Once the platform is stable, explore AI workloads, for example data agents and automatically generated insights, on the same PhoenixAI Cloud and Iceberg base.