Joins are not new. Analytical databases have supported them for decades.
What is still hard is running complex joins at high concurrency with tight latency goals – especially in real-time analytics systems where fresh data is continuously arriving.
That is why many teams avoid joins in latency-sensitive analytical workloads. They denormalize aggressively, precompute more tables, duplicate business logic across pipelines, or push complexity upstream. Those choices can work, but they come with costs: more data movement, more operational overhead, more pipeline logic, and less flexibility when the business question changes.
Pinot MSQE is an important step forward in making these patterns practical for real-time analytics. I covered the broader journey of MSQE join maturity in my LinkedIn post, but this post is about the next question that comes up in the field: what happens when you push that capability through a real competitive POC with high QPS, multi-terabyte data, and fresh data continuously arriving?
The good and expected news: Pinot MSQE handled the join pattern well.
The more interesting question was how to sustain that performance efficiently. In a POC, it is always tempting to solve pressure by adding more nodes. Sometimes that is the right answer. But if the goal is to prove production readiness, compute footprint matters too. We wanted to understand where the system was actually spending work before deciding whether more hardware was the answer.
That is what made the load test useful. Once the workload got hot, the bottlenecks were not sitting in one neat box. JVM object lifecycle, old-gen behavior, heap sizing, sorted-index locality, and storage utilization all started interacting under pressure.
That distinction matters for how to read the rest of this post. The tuning work below was not about making Pinot joins work through exotic knobs. Pinot MSQE already handled the join pattern. The work was about what good load testing reveals once the engine is capable enough to take on the workload: how query shape, indexing, memory, JVM behavior, and I/O interact under pressure.
So the takeaway is not “memorize a bag of JVM flags before running joins.” The takeaway is simpler: watch how the workload moves through the system, identify the layer actually creating pressure, and avoid making the JVM, storage layer, or cluster size do unnecessary heroics.
POC Setup
Before getting into GC charts and I/O graphs, it is worth grounding the workload profile. This was a production-style load test over multi-terabyte data, with ongoing ingestion and a demanding multi-table join query shape.
| Dimension | Target / Profile |
|---|---|
| Query throughput | 50 QPS |
| Latency goal | p95 under 2 seconds; p99 under 5 seconds |
| Ingestion rate | 15K events/sec |
| Data size | ~8 TB |
| Query pattern | Multi-table joins, large time windows, many group-bys, aggregations, derived metrics, ordering, and high join hash table limits |
This combination matters. A single join query running once is interesting; a complex join query running repeatedly at 50 QPS while fresh data continues to ingest is a very different test. At that point, you are not only testing SQL support. You are testing planning, distributed execution, segment pruning, index effectiveness, intermediate result handling, JVM allocation rate, GC behavior, disk access patterns, and broker/server coordination.
The query itself is intentionally anonymized here, but the shape is what matters:
SET useMultiStageEngine = true;
SET timeoutMs = <large timeout for POC testing>;
SET maxRowsInJoin = <large join safety limit>;
WITH main AS (
SELECT time_bucket, dimension_1, dimension_2, ...,
SUM(metric_1), SUM(metric_2), SUM(metric_3), ...
FROM main_hourly_fact_table
WHERE tenant_filter AND campaign_filter AND device_filter AND time_range
GROUP BY time_bucket, dimension_1, dimension_2, ...
),
cost_adjusted AS (
SELECT m.*, cost_rules.adjusted_cost
FROM main m
LEFT JOIN cost_rules
ON m.time_bucket BETWEEN cost_rules.start_time AND cost_rules.end_time
),
conversions AS (
SELECT visit_time_bucket, dimension_1, dimension_2, ...,
SUM(conversions), SUM(cost), SUM(revenue)
FROM conversion_hourly_fact_table
WHERE same_business_filters AND conversion_time_range
GROUP BY visit_time_bucket, dimension_1, dimension_2, ...
)
SELECT joined dimensions, metrics, ROI/profit-style derived measures
FROM cost_adjusted m
LEFT JOIN conversions c
ON m.time_bucket = c.visit_time_bucket
AND m.dimension_1 = c.dimension_1
AND m.dimension_2 = c.dimension_2
AND ...
ORDER BY business_metric DESC
LIMIT 1000;Code language: SQL (Structured Query Language) (sql)This query is interesting for a few reasons:
- Large fact-table aggregation with selective business and time filters.
- A range join against cost/rule data, which is harder than a simple equality lookup.
- A second aggregation over conversion-side data followed by a wide equality join on time plus dimensions.
- Derived business metrics after the joins, followed by top-N ordering.
- Large join limits during POC testing, which makes memory lifecycle and intermediate result behavior worth watching.
In other words, this is exactly the kind of workload where ‘the query runs’ is not enough. The real test is whether it keeps running predictably at high concurrency with optimized compute footprint.
Note: Query/table names and filter values above are intentionally generic. The point is the workload shape, not customer-specific schema or data.
Challenge: Queries piling up
The first challenge was not exotic: under sustained high-QPS complex join load, queries started piling up and latency became less predictable.
The cluster had enough CPU on paper. The servers were backed by NVMe. The JVM had a large heap. In other words, this did not look like an obviously under-provisioned setup.
The initial configuration was a reasonable starting point:
- A 40 GB heap.
- G1GC.
- A 200 ms pause target.
Nothing about that screams bad configuration.
But load tests have a way of making reasonable defaults confess. Under this workload, the JVM showed frequent full GCs, long pauses, young GC pressure, and old-generation instability. Some pauses stretched into multi-second territory.
That matters because in a distributed query engine, GC pressure does not stay politely inside one process.
A long pause on one server becomes a query fanout delay. Fanout delay becomes broker wait time. Broker wait time becomes user-visible latency. Enough of that becomes timeout behavior.
Once queries pile up, the failure mode can become self-reinforcing:
Complex join workload
↓
High allocation and intermediate objects
↓
Young GC pressure
↓
Promotion into old generation
↓
Old-gen saturation risk
↓
Evacuation failure / long pause
↓
Query pile-up
↓
Objects stay live longer
↓
More GC pressure
That is the moment a load test gets useful: the system starts showing which layer is really setting the pace.
What the JVM Was Telling Us
The useful signal was not simply that GC happened.
GC is an expected and desired outcome, especially for object-heavy analytical execution.
The problem was that G1GC was being pushed close to a structural failure mode: old-generation saturation under promotion pressure.
Complex joins create intermediate state. Depending on the query plan and data distribution, the engine may create hash tables, row containers, buffers, exchange blocks, serialized and deserialized values, and execution metadata. Many of these objects are short-lived. Some survive long enough to promote.
Under high-QPS load, that promotion pressure can become the thing that quietly changes the whole system.
The failure pattern looked like this:
High allocation rate
↓
Promotion spike into old generation
↓
Old gen approaches saturation
↓
Insufficient free regions for evacuation
↓
Evacuation failure
↓
Full compaction
↓
Multi-second stop-the-world pause
This is the old-gen cliff.
Before the cliff, the system looks busy but alive. After the cliff, the JVM is in emergency recovery.
That distinction matters. The goal was not to eliminate GC. The goal was to keep the JVM away from the edge where it had only expensive recovery options left.
Useful Heap-Sizing Lesson from the Load Test
One tempting response to JVM pressure is to add heap. Sometimes that is right. In this case, it was not the best first move.
The baseline used a 40 GB heap, which crossed the compressed-oops-friendly range. For object-heavy workloads, that can reduce effective memory density: references get larger, object graphs take more space, and G1 has more memory to scan and evacuate.
So the extra nominal heap does not always translate into equivalent useful headroom. Moving to around 31 GB kept compressed oops enabled and made evacuation cheaper.
The lesson is not “31 GB is magic” or “larger heaps are bad.” The lesson is to size heap based on live set, object lifetime, latency target, and collector behavior – not just available RAM.
Configuration Strategy
The useful part of the JVM work was that every change mapped to a symptom.
That is the difference between tuning and flag bingo.
The tuned configuration moved in this direction:
-Xms31g
-Xmx31g
-XX:+UseG1GC
-XX:MaxGCPauseMillis=200
-XX:InitiatingHeapOccupancyPercent=25
-XX:G1ReservePercent=20
-XX:ConcGCThreads=4
-XX:G1MaxNewSizePercent=30
There were five practical ideas behind the configuration.
1. Keep the heap in the compressed Oops friendly range
This was the heap-sizing lesson described above: the goal was not to minimize memory, but to keep the JVM in a more efficient operating range for this object-heavy workload.
2. Start marking earlier
Lowering InitiatingHeapOccupancyPercent from 45 to 25 gave G1 more runway. Under bursty promotion pressure, starting concurrent marking too late can mean old gen approaches saturation before marking completes.
3. Increase evacuation reserve
Increasing G1ReservePercent from 10 to 20 created more to-space for promotion spikes. Without enough evacuation headroom, G1 can be forced into failure behavior.
4. Increase concurrent marking capacity
Increasing ConcGCThreads helped marking complete faster. It costs some application CPU, but that can be a good trade if it prevents multi-second stop-the-world pauses later.
5. Cap young-gen growth
Reducing G1MaxNewSizePercent from 60 to 30 bounded young-generation growth and made evacuation work more predictable for this latency-sensitive workload.
Result: Boring is beautiful
After tuning, the behavior changed in the way you want a load test to change: fewer surprises.
Before tuning:
- Full GCs occurred repeatedly.
- Max pause reached roughly 6.45 seconds.
- p95 pause was around 700 ms.
- Evacuation failures were frequent.
- Mark aborts were present.
- Query latency was unstable.
After tuning:
- Full GCs dropped to zero.
- Evacuation failures dropped to zero.
- Mark aborts dropped to zero.
- Median pause was roughly 12 ms.
- p95 pause was roughly 87 ms.
- Old generation stayed in a stable operating band.
Before tuning query P95/P99:
- POC Goal – P95/P99 latencies of 2s/5s
- Actual – P95 of 6 seconds and P99 degrading to 15 seconds
After tuning query P95/P99:
- P95 went down to 1.8 seconds
- P99 3.6 seconds
This is the real win.
Not GC got better in isolation.
The system stopped falling into recovery mode. It stayed in control under sustained high-QPS complex join pressure and was able to hit the desired P95/P99 application latencies.
That is what you want from Pinot MSQE in production: predictable execution, low drama, and no surprise cliff at the worst possible time.
Lessons learned: Sorted indexes are about locality
The next major lever was data layout.
Before adding the sorted index, disk I/O was doing more seek-heavy work than it needed to. NVMe helped, but fast storage still pays a tax when matching rows are scattered.
The workload filtered heavily on a high-value business dimension used in nearly every query. Sorting each segment by that dimension physically co-located matching rows within the segment. The win was locality: instead of chasing many scattered reads, Pinot could access a more contiguous set of records for the filter.
Figure 8: Disk reads before the sorted index. NVMe helps, but fast storage still pays when matching rows are scattered and query execution turns into many small or random reads.
After adding the sorted index, the observed disk-read pattern improved by more than 3-5x under load.
That is especially important on EBS-style network-attached block storage where random access and seek-heavy patterns can waste I/O potential even when nominal throughput looks fine.
The key distinction: this was not simply about reading less data. It was about reading the relevant data with better locality and fewer seeks.
A sorted index helped because:
- matching rows for the frequently filtered dimension were physically co-located inside the segment
- the access pattern shifted from many scattered/random reads to fewer, more contiguous reads
- the storage layer spent less time seeking and more time streaming useful data
- I/O bandwidth was used more efficiently, which matters even more on remote block storage such as EBS
- query latency became more predictable at high throughput
- less storage-level jitter propagated upward into MSQE execution
This is where load testing gets fun: the storage layer, execution engine, and JVM are all part of the same story – no one feels left out.
The sorted index improved the physical access pattern. The JVM tuning made the remaining work execute predictably. MSQE then had the runtime conditions it needed to shine.
A good MSQE proof point
This POC was a useful proof point for the kind of join workloads Pinot MSQE can now take on.
The point was not whether a single query completed. The useful question was whether the system could sustain complex joins under realistic analytical pressure, with latency goals and ingestion still in the picture.
That is the maturity line.
That kind of workload benefits from the usual production ingredients around the engine:
- good data layout
- effective pruning
- right indexing strategy
- predicate push-down
- balanced server execution
- predictable memory behavior
- runtime settings aligned with object lifecycle
- observability that shows the full path from query shape to runtime pressure
When these pieces line up, Pinot MSQE can handle high-QPS complex join patterns effectively at product scale and under real SLAs.
What to watch in a Pinot load test
Do not start and end with average latency. Average latency is where interesting failures go to hide.
For workloads like this, watch:
- p95 and p99 query latency
- timeout rate
- broker wait time
- per-server skew
- CPU distribution
- disk read throughput
- segment pruning effectiveness
- join intermediate size
- heap occupancy trends
- old-gen behavior
- young GC pause distribution
- Full GC count
- evacuation failures
- mark aborts
- off-heap memory trends
More importantly, correlate them.
A GC spike alone is interesting. A GC spike plus old-gen saturation plus query pile-up is a root-cause path.
High disk reads alone are interesting. High disk reads with a scattered access pattern and a common filter predicate is a sorted-index conversation.
Complex join latency alone is interesting. Complex join latency plus server skew plus GC instability tells you where to look next.
This is where the dashboard turns from a wall of charts into a map.
Observability matters
A final practical point: load testing is only as useful as the signals you can correlate. In this POC, the insight did not come from any single chart. It came from connecting query latency with GC pauses, old-gen occupancy, CPU distribution, and storage behavior. That is the kind of correlation StarTree Cloud is designed to make easier across SaaS, BYOC, and BYOK deployments: surfacing Pinot, JVM, and infrastructure signals together so teams can see whether a slowdown is coming from query shape, memory pressure, server skew, or I/O behavior.
The point is not more dashboards for their own sake; it is reducing the time between “p99 moved” and “we understand why.” Learnings from POCs like this also feed back into StarTree Cloud defaults, deployment guidance, and operational guardrails over time.
Final Takeaways
The headline is not that we found a few JVM flags.
The headline is that Pinot MSQE handled a high-QPS complex join workload well, and the POC gave us a useful field guide for keeping the surrounding system healthy under load.
The main lessons:
- Pinot MSQE is ready for serious complex join workloads. The engine handled the pattern; the practical work was about validating the full system around it and making the right efficiency improvements.
- Heap size is a tradeoff, not a virtue. Heap sizing should follow the workload. Bigger heaps are not bad, but crossing the compressed-oops boundary changes object layout and memory behavior. Make that decision intentionally.
- GC tuning should map to failure modes. Earlier marking, larger evacuation reserve, more concurrent marking threads, and young-gen caps each addressed a specific observed issue.
- Indexing strategy directly affects runtime stability. The sorted index on frequently filtered dimension improved locality and I/O utilization by turning many scattered reads into fewer, more contiguous reads; that lowered storage-level jitter and helped query performance at high throughput.
- Steady state is the real scalability goal. Peak numbers are nice. Predictable behavior under pressure is what wins POCs.
That last point is probably the most important.
At scale, performance is not just about peak throughput. It is about whether the system remains boring when the workload is not.
For this POC, Pinot MSQE again proved it could handle the complex join workload. The rest of the work was practical systems engineering: understand the bottleneck, make the right change, and keep the engine out of unnecessary friction.
