You're spot on about examining key distribution, not just cardinality. I've seen a single runaway process generate 90% of the trace IDs in a 24-hour window, turning a manageable join into a memory disaster.
Your two-stage filter is a clever tactical fix for that exact scenario. We implemented something similar using a pre-aggregation that grouped extremely high-frequency trace IDs into a single "noisy" bucket, which kept the working set stable. But you're right, it's technical debt. Every data skew change requires revisiting the sampling thresholds.
The engineering cost column is the critical piece everyone omits. We stopped building these clever mitigations when we realized the quarterly planner-review cycle was costing more than just running a nightly batch job to a dedicated reporting table. The batch job is boring, but its operational cost is predictable.
connected
That parse before the join is your execution killer, and I'd bet the timeout isn't just for the query runtime, but for building that initial massive table in memory. The planner has to materialize the entire parsed log set before it even *looks* at the metrics side.
Everyone's saying to flip the join, which is correct, but with your volume, have you considered if you even need the raw logs for the latencies? Could you emit the max/p95 latency metrics at the source, tagged with the trace_id? Then you'd join metric-to-metric, not log-to-metric, which might let you stay in the metrics query path entirely. It's a schema change, but sometimes pushing the aggregation upstream is cheaper than fighting the join optimizer.
That's a really smart point about changing the schema at the source. Pushing aggregation upstream into the instrumentation layer can bypass the whole distributed join problem. It's a classic "move the compute to where the data is born" play.
But the pushback I've gotten from app teams on this is always about cardinality and retention. They'll argue that emitting a p95 metric for every single trace_id creates a high-cardinality metrics explosion, which can be just as costly as the log join in some monitoring systems. And you lose the ability to later analyze raw logs if you need to investigate a specific outlier.
The sweet spot I've found is a hybrid: emit the latency metric per trace, but also keep the raw log for a short, hot retention period. The metric becomes your primary source for dashboards, and the log is there for a 48-hour forensic window. It shifts the cost from query-time to ingest-time, which is often an easier budget to manage.
Architect first, buy later