Blog / Engineering

Precomputing the Hard Queries: Materialized Views on StarRocks

How 28 StarRocks materialized views behind 928 existing views made the slowest MCP and platform queries five times faster, with no change to any caller.

Prathyush Shankar · November 4, 2026 · 12 min read

Carbon Arc builds data infrastructure for the AI economy. We take in third-party datasets, such as card transactions, point-of-sale data, clickstream, foot traffic and building permits, and map every record to a proprietary ontology of companies, brands, tickers and places. Clients reach that data in two ways: through our web platform, and through the Carbon Arc MCP server, which AI agents call to pull insight data straight into an analysis.

Both paths end in the same place: a query against a StarRocks cluster that reads Apache Iceberg tables on S3. As MCP traffic grew, a small set of expensive queries came to dominate that cluster. We moved them onto 28 precomputed StarRocks materialized views that now serve 928 Explorer views. Two weeks after the October 20, 2026 production release, the slowest 1% of MCP queries on those views take 5.0 seconds instead of 24.7, and MCP data calls slower than 20 seconds fell from 8.4% to 0.9%.

24.7s → 4.99s
5×
faster p99 for MCP queries on precomputed views
was 8.35%
0.91%
of MCP data calls take longer than 20 seconds
from 28 MVs
928
existing Explorer views now read a materialized view
was 0.85
0.16
average queued queries, with 17% more traffic

This post explains why the hot queries were slow, how we chose what to precompute, the design that lets every existing query use the precomputed data with no application change, and what we learned running it on S3 and Iceberg.

The challenge

Every Explorer dataset that clients query is a logical view. Our dbt project defines 17,344 of them, and each one aggregates a certified Iceberg table at query time. That keeps the data fresh and storage cheap, but it means the cluster redoes the same heavy rollup every time someone asks.

The worst case was our point-of-sale dataset. A brand-by-day view read a 7.0-billion-row, 188 GB Iceberg table across 2,835 files to return about 2.5 million rows, a 2,800-to-1 reduction repeated on every query. A single brand over the last 90 days took 82 seconds with a cold cache and 0.24 seconds warm. The pipeline rewrites the trailing 92 days every morning, so the cache was cold exactly where users look.

The cost was concentrated. Over one week in October, the StarRocks audit log showed that MCP queries on the point-of-sale views used 68.9% of all MCP query CPU, about 15 CPU-minutes per query. Clickstream views took another 13.6%, and the top clickstream queries each spent 1,600 to 1,860 CPU-seconds. Weighted by how often each view was queried, four datasets accounted for 67% of all MCP time spent waiting past five seconds.

Two datasets used 83% of MCP query CPU Share of StarRocks CPU time spent on production MCP queries, by dataset Point of sale 68.9% Clickstream 13.6% Card transactions 8.8% Building permits 3.1% All other datasets 5.6%
A handful of query families held most of the cost.

Those few queries also slowed everything else. They shared compute with every other client request, so one heavy rollup pushed ordinary queries into the queue. At the same time, we were planning for MCP traffic to grow by orders of magnitude. MCP traffic used about 1.6 StarRocks cores on average in October, so 1,000 times the traffic would need roughly 1,600 cores against a production peak of about 105. Adding hardware would not close that gap; doing less work per query would.

Solution overview

Our data lake is Iceberg on S3. Apache Spark jobs on EMR write each dataset’s certified tables, an Apache Polaris catalog tracks them, and Apache Airflow orchestrates the pipeline. StarRocks reads those tables in place through an external catalog: our production cluster runs on CelerData Cloud in our AWS account on Graviton instances, and our development cluster runs open source StarRocks on EKS.

We added one layer between the Iceberg tables and the views clients query. For each expensive query family, a StarRocks asynchronous materialized view stores the rollup once, at the grain the views actually group by (for example, entity by state by day). The Explorer views keep their names and columns, but now read the materialized view instead of the raw table. The pipeline refreshes each materialized view right after the Iceberg commit it depends on, on compute that is kept apart from client traffic.

Clients query the same views; the views now read precomputed rollups commits tracks fills after each commit unchanged SQL EMR Apache Spark writes tables S3 Apache Iceberg tables Apache Polaris Iceberg REST catalog StarRocks on CelerData Cloud · Graviton External catalog reads Iceberg in place no copy of the data lake explorer_mv 28 materialized views rollups by entity, state, day explorer 928 views read the MVs same names and columns mv_refresh 3 × r7g.4xlarge, one AZ Apache Airflow 1. Refresh table metadata 2. Refresh MVs, all or nothing 3. Publish new date ranges Clients MCP server for AI agents Web platform and saved queries Internal tools
Spark on EMR commits Iceberg tables to S3. Airflow then refreshes table metadata, refreshes the materialized views on their own warehouse, and only then publishes new date ranges. Every client keeps sending the same SQL.

Four design decisions

Each decision starts with a problem we hit, then what we built and what we measured.

1. Precompute where the CPU goes, not where the views are

With 17,344 views, a materialized view per view was never an option. It is also more expensive than it looks: StarRocks keeps per-partition refresh state for every materialized view in frontend memory, so the cost grows with materialized views times base-table partitions. Some of our base tables have more than 100,000 partitions.

So we ranked by measured cost. The StarRocks frontend audit log, which we ship to Datadog, records CPU time, scanned bytes and the relations each query touched. We grouped a week of production MCP queries by view and ranked datasets by CPU share, and separately by time spent past five seconds weighted by how often each view was queried. Two datasets we had planned to cover fell off the list at 0.1% and 0.04% of CPU.

Then we found the smallest set of rollups that the hot views share. Most Explorer views for a dataset differ only in the entity and the geography they group by, so one rollup at entity by state by day can serve many of them. One point-of-sale materialized view of 71.7 million rows now serves 20 views that used to scan 7 billion rows each time. In total, 28 materialized views serve 928 views across six datasets.

DatasetMaterialized viewsWhat each one stores
Building permits10Company or location by geography by day
Clickstream6Entity by state by day
Card transactions (dataset A)6Entity by state by day
Card transactions (dataset B)3Entity by state by day
Foot traffic2Entity by state by day, and nationally by day
Point of sale1Entity by state by day, for 20 views

The gain is not always from fewer rows. Our clickstream company rollup holds 746 million rows against 1.76 billion in the base table, only 2.4 times smaller. Its top production queries still went from 1,600 to 1,860 CPU-seconds down to 0.3 to 0.45 seconds, because the materialized view is distributed by company ID. A single-company query now reads 1.3 million rows instead of 3.1 billion. Choosing the distribution key mattered as much as choosing the grain.

2. Repoint the views, don’t route the queries

We first tried two standard approaches. StarRocks can rewrite a query to use a materialized view automatically, but only when the view’s query is a simple select-project-join-group-by. Our views use common table expressions and joins to aggregates, and the optimizer trace showed it would not rewrite them. We also tried routing in our application, mapping each view to its materialized view in the MCP server. That missed the web platform, which stores generated SQL for saved analyses and replays it later.

What works for every caller is to change the view itself. Each Explorer view now selects from its materialized view, with the same name, columns and results. The MCP server, the web platform, saved queries and internal tools all pick it up with no code change. Because the views reach the materialized views directly, we turned automatic rewrite off on them. A rewrite candidate that never matches still added about 110 ms of planning to every query on the base table.

The rollups have to give exactly the same answers as the views they replace. They keep every column the views filter, join or group on, and store integer sums, so aggregates above them stay exact. Before the production release, we compared all 928 view definitions and row counts against the development clusters, and all 928 matched.

Before production, we also benchmarked each dataset on our development cluster: the same full-history query against the materialized view and against the base tables, 30 pairs in all, every pair with identical results.

Precomputed views answered full-history queries 6× to 430× faster Speedup over the base tables, log scale 1× 10× 100× 1000× Building permits 130× – 430× Point of sale 240× Card transactions 17× – 76× Clickstream 6× – 42×
Full-history queries, materialized view against base tables. All 30 paired queries returned identical results.

3. Refresh in the pipeline, and make it all or nothing

A materialized view is only useful if it is never wrong or half-built. dbt defines each one with manual refresh. Our pipeline command refreshes them in a fixed order after every Iceberg commit: it refreshes external table metadata on each cluster, refreshes the materialized views and waits for them, fails the run if any refresh failed, and only then publishes the new date ranges that the platform shows. A user can never see a date range that the rollup doesn’t hold yet.

We tested what a reader sees during a refresh by polling every 50 ms while it ran. With the default settings, StarRocks commits one partition per task run, and 340 of 370 reads saw a mix of old and new months. Setting the strict partition-refresh strategy with no partition limit makes the whole refresh one commit, and the same test showed zero mixed reads in full, partial and forced refreshes. When the base table has not changed, StarRocks compares per-partition statistics and skips the refresh in under a second.

ALTER MATERIALIZED VIEW explorer_mv.entity_state_day SET (
  "partition_refresh_strategy" = "strict",
  "partition_refresh_number" = "-1"
);

We also learned not to hold a connection open for a long refresh. The synchronous refresh mode waits on the client connection, and our load balancer silently dropped idle connections after 200 to 400 seconds, so the client hung while the refresh succeeded. We submit refreshes asynchronously and poll the StarRocks task-run table by query ID instead.

Monthly full rebuilds, for example after an ontology change, are the hardest case. Rebuilding in place would show readers empty or partial rollups for the whole fill. Instead, the pipeline clones each materialized view as a shadow copy, fills it in chunks of 12 partitions, and swaps every materialized view on a cluster at once. If any fill fails, nothing is swapped on that cluster.

A monthly rebuild swaps every rollup at once, or none Clone each MV as a shadow copy Fill the shadows in 12-partition chunks All filled? Swap every MV on the cluster at once Drop the shadows old MVs keep serving yes no, any failure What readers see old rollups, for the whole fill new rollups, never a mix
Forced rebuilds, such as after an ontology change, fill shadow copies and swap every materialized view on a cluster together. Any failure leaves the old rollups serving.

4. Give refreshes their own compute

Refreshes are large INSERT statements, and they do not pass through StarRocks’ query queue. Early on, two concurrent fills saturated a development cluster and slowed client queries. On CelerData, production and development, we now run every refresh on a dedicated warehouse, mv_refresh, of three Graviton r7g.4xlarge nodes, cap fills at two per cluster, and limit them with a resource group.

The warehouse layout mattered more than its size. We first deployed it across three Availability Zones, which creates one compute group per zone, and StarRocks runs each query on one group. Every refresh therefore ran on a single node. Moving the warehouse to one Availability Zone put all three nodes behind each refresh, and our largest forced fill went from 3,866 seconds to 1,155 seconds, 3.35 times faster, with all three nodes near 80% CPU.

One Availability Zone put all three nodes behind each refresh Before: three AZs, three groups of one AZ 1 refresh AZ 2 idle AZ 3 idle Each refresh ran on one node 3,866 s After: one AZ, one group of three AZ 1 refresh refresh refresh Each refresh uses all three nodes, near 80% CPU 1,155 s 3.35× faster
The mv_refresh warehouse has three r7g.4xlarge nodes. Spread across three Availability Zones it formed three compute groups of one node each, and StarRocks runs each query on one group.

The refresh cost stays small. In production, the first fill of all 28 materialized views took 90 minutes in 142 refresh runs on October 19. In the week after release, the 73 incremental refreshes averaged 38 seconds, none failed, and none ran on the warehouse that serves clients.

Results

We compare the week before the October 20 release with the second full week after it, October 28 to November 4, 2026, and skip the first week, which had compute-node errors and queue spikes. As a control, we track Explorer views that did not change. These numbers reflect our data and our traffic, so your results might vary.

The slowest 1% of queries on precomputed views got about 5× faster Before · Oct 13–20 After · Oct 28–Nov 4 VIEWS NOW SERVED BY MATERIALIZED VIEWS CONTROL · VIEWS LEFT UNCHANGED 0.5s 1s 2s 5s 10s 20s 40s MCP server 24.7 4.99 4.9× Web platform service 7.01 1.18 5.9× Platform query workers 4.89 0.99 4.9× MCP server 11.3 9.28 1.2× Platform query workers 3.85 2.29 1.7×
p99 StarRocks query time on production, log scale. The control views, which we did not change, barely moved.

On the views that moved to materialized views, the slowest 1% of queries got about five times faster for every caller, and the median fell from between 0.75 and 0.99 seconds to between 0.04 and 0.22 seconds. The control views barely moved, so the gain comes from the materialized views rather than from lighter traffic. On release night, a sample point-of-sale query went from 42 seconds to 0.5 seconds, and a card-transaction query from 27 seconds to 0.3 seconds.

The MCP server’s tool calls, which include model, metering and authentication time as well as the query, show the same change at the tail.

MCP measureBeforeAfter
Data tool calls over 20 seconds8.35%0.91%
Data tool p9940.8 s19.4 s
All tool calls over 20 seconds6.19%2.84%
All tool calls p9949.8 s29.0 s
Data fetch p9916.2 s8.56 s

MCP call volume rose about 2.5 times between the two windows, and the median tool call got slower, which we attribute to the change in workload mix. We report the tail, where the materialized views remove the long queries.

The cluster also does less work for the same answers. In the week after release, a query on a materialized-view-backed view averaged 6.5 CPU-seconds and a median of 43 ms, against 24.6 CPU-seconds and 399 ms for the views we did not change. CPU used by the web platform’s resource groups fell from 0.71 to 0.27 cores on average while it ran more queries. Across the cluster, total queries rose 17%, yet the average query queue fell from 0.85 to 0.16 waiting queries and the peak from 20 to 5.

Refreshing the materialized views has a cost: in the after window, refreshes used about 17% of the query CPU recorded in the StarRocks audit log. That work runs once per data update on the separate refresh warehouse, instead of on every client query on the warehouse that serves them.

Lessons learned

  • Rank by measured cost, then by popularity. The query audit log told us where CPU went, and a small share of query families held most of it. Measuring first kept the program to 28 materialized views instead of hundreds, and it dropped two datasets we would otherwise have built for.
  • Prove the results are identical, every time. Before any view moved, we compared results from the materialized view against the base tables in 30 cases, and every one matched. That habit also caught a real optimizer bug: on StarRocks 4.0.11, bucket-aware execution on lake tables split one brand into four rows that summed to the correct total. The fix shipped in 4.0.14, and we now require that version or later.
  • Test with the production session settings. One refresh failure surfaced only in production, because our test harness set a longer planner timeout than the pipeline client did. Under fill load, planning the catalog reads took 35 to 92 seconds against a 30-second default.
  • Check that infrastructure changes did what they said. A warehouse grant script reported success while it ran before the warehouse existed, and granted nothing. We now check grants after every apply.
  • Keep refresh compute apart and fully usable. Refreshes bypass the query queue, so they need their own warehouse and a concurrency cap. Its layout matters too: a warehouse spread across Availability Zones ran each refresh on one node.

What’s next

We are moving more datasets onto a unified long-format panel layout on Iceberg, where one rollup per panel can serve every view of that dataset. The same ranking will decide what gets precomputed next. We also want to add a scheduled scaling policy to the refresh warehouse, so it grows during the monthly rebuild window and shrinks after.

Conclusion

We kept our data lake as Iceberg on S3 and added one precomputed layer where measurement showed the cost was: 28 StarRocks materialized views behind 928 existing views. Every client, including AI agents on our MCP server, now gets the same answers faster with no change to how they query, and the cluster does far less repeated work. The refresh path runs inside the pipeline on its own compute, and commits all at once.

If you serve many query shapes from a lake on S3, start the same way: rank query families by measured CPU, find the few rollups they share, repoint the views rather than the callers, and give refreshes their own compute.

  • starrocks
  • materialized-views
  • iceberg
  • mcp
  • performance

Share this post

Written by

Prathyush Shankar

More from Engineering