Originally published at: AWS Containers Blog
About Amazon
Amazon's WW Stores FinTech team builds and runs analytical products for Amazon's finance organization: reporting, planning and allocation, self-serve analysis, and AI-generated financial insights. Thousands of finance users rely on them every business day.
On simple queries the two engines performed about the same. They pulled apart on the queries finance users actually write, which combine joins across many tables, pivots over hierarchies and filters on high-cardinality columns.
Key takeaway · Benchmark the real query mix
The original post was written by Vara Bonthu, Principal Open Source Specialist SA leading Data on EKS at AWS; Navaneeth Sagar, Senior Engineer in the Amazon Stores Finance Technology team; and Apurva Sherke, Data Engineer at Amazon's WW Stores FinTech. It credits Candice Xu (Data Engineer) and JP Bonard (Senior Data Engineer) with the benchmarking, load testing and data modeling behind the choice of StarRocks.
The challenge
During monthly close, thousands of finance staff need access at the same time, and data volumes were rising from the hundreds of GB into the TB range, with billions of records. The team needed three things together: responses from under a second to a few seconds over terabytes of data, hundreds of concurrent users at peak, and scale-out without disruptive data rebalancing. Existing systems met one or two of these at the projected volumes, never all three, so the team built a new foundation.
A benchmark built from real queries
Generic benchmarks rarely capture financial queries, so the team tested on two production-representative datasets: hierarchical financial data with joins across three to seven tables, and operational data with high-cardinality aggregations over millions of distinct values. A Query Complexity Framework covered five patterns: high-cardinality filters, complex GROUP BY aggregation, star-schema joins across more than three tables, pivots into report columns, and drill-downs through parent-child hierarchies. The targets were under 5 seconds for standard queries, under 20 seconds for complex ones, and linear scaling from 50 to 1,000 concurrent users.
StarRocks 3.4.0 and ClickHouse 25.6.4.12 ran on identical infrastructure across multiple availability zones. ClickHouse led on simple scans without joins, with 33% higher throughput, and loaded data faster: about 6 minutes per TB after EBS tuning, against 30+ minutes per TB for StarRocks native tables through the Spark connector. Both did well on high-cardinality GROUP BY. As complexity and concurrency rose, StarRocks querying through External Catalog pulled ahead: 1.5X better P95 on simple scans at 1,000 users, 2X higher throughput on scans with more than three filters, and 3X–5X higher throughput on multi-table joins (over 2–3 tables). With data held in native tables on BE nodes, StarRocks scanned 36x less data on those joins.
The solution
Why StarRocks
The team credits the cost-based optimizer, which adapts plans as data distributions change, for keeping joins, pivots and filters fast without manual tuning. External Catalog queries through AWS Glue gave good scan performance on existing Hive and Iceberg tables with no ingestion step. StarRocks' Compute Nodes (CN) hold no persistent data, so they can join or leave the cluster without rebalancing or interrupting queries, which is what makes autoscaling workable.
Architecture on Amazon EKS
The StarRocks Kubernetes Operator manages the cluster through a declarative StarRocksCluster CRD, with rolling updates and self-healing. Three Frontend (FE) nodes form a Raft quorum for leader election and metadata. Below them, one cluster mixes two tiers, defined with starRocksBeSpec and starRocksCnSpec:
- Backend (BE) nodes run as StatefulSets on Amazon EBS volumes and hold indexed dimension tables and operational telemetry, so the most frequent reports read local indexes instead of S3.
- Compute Nodes (CN) run as Deployments, scan fact tables in Amazon S3 through the AWS Glue Catalog and keep only a temporary cache. Without the ordered startup of a StatefulSet, they can be added almost immediately.
The StarRocks query planner routes work to the right tier automatically.
BI tools and SQL Workbench connect privately over AWS PrivateLink via a Network Load Balancer. The AWS Glue Data Catalog serves as the shared metastore, with cross-account access. Batch loading into native tables uses the StarRocks Spark Connector, run as AWS Glue jobs. Prometheus collects StarRocks metrics, and Amazon CloudWatch handles dashboards and alerts.
Scaling with KEDA and Karpenter
KEDA reads queue depth, CPU and memory use, and pending query counts from Prometheus and sets pod counts from that demand. CN nodes absorb bursts because they serve queries as soon as they join. BE nodes scale separately for sustained growth and need time to warm up while StarRocks copies data shards to them.
When pods cannot be scheduled, Karpenter launches right-sized EC2 instances instead of keeping warm spare nodes. Each role has its own NodePool: On-Demand instances for FE and BE, Spot for CN, since a stateless node can lose its instance without harm. When load falls, Karpenter consolidates underused nodes.
The results
The platform answers standard queries in under 5 seconds and complex queries in under 20 seconds at 1,000 concurrent users. Reaching that took several fixes:
- Memory. Early 1,000-user stress tests saw about 40% of queries fail on memory limits. The team moved BE nodes to memory-optimized EC2 instances, used Resource Groups to cap work and queue extra queries rather than reject them, and set query_mem_limit per workload. Peak error rates fell below 5%.
- S3 I/O. CN-heavy setups hit S3 request and network limits under concurrent scans, causing P95 spikes on complex joins. Indexed dimension tables moved to BE nodes on EBS, while CN nodes kept fact scans and bursts, with no application changes.
- SQL migration. Queries moving from Amazon Athena and Presto ran into differences in function names, array syntax, type conversion and correlated subqueries. A translation guide and a test suite now check queries before migration.
- EBS throughput. In the ClickHouse evaluation, loading 4.7 TB took 13 hours at the default gp3 throughput of 125 MB/s and 25 minutes at 1,000 MB/s with tuned IOPS, 31x faster. The team advises tuning EBS before comparing any EBS-backed system.
FE nodes turned out to be CPU-bound, so they run on compute-optimized types such as c6i.8xlarge (32 vCPU). BE and CN nodes ran short of memory and network throughput, so they use memory-optimized types with enhanced networking such as r6i.24xlarge (96 vCPU, 384–768 GB). Each NodePool lists several types so Karpenter can find Spot capacity.
What's next
EBS remains the production baseline. For the most latency-sensitive workloads, the team is looking at storage-optimized EC2 instances that have local NVMe SSDs on the BE tier.