Unpredictable Queries, Predictable Latency: Architecting Joins for Analytical Agents

Building analytical agents? See what it takes to keep ad hoc, multi-table joins sub-second at scale. → See PhoenixAI: https://bit.ly/4ycMopx → Questions about your workload? 👉 https://bit.ly/4zcAhJH Why can’t you precompute your way out of an agent’s queries? Because agents change the workload. One question can fan out into many queries — queries that may have never run before and may never run again. Precomputation can still accelerate repeatable BI and reporting, but it can’t anticipate every join an AI analyst, MCP agent, or customer-facing answer engine will generate. In this session, PhoenixAI PM Sida Shen breaks down what it takes to run those joins live: sub-second latency, thousands of QPS, and seconds-old data — without flattening everything upfront. If something other than a human is writing your SQL, the database has to be ready for queries you didn’t plan for. In this video: 🌟 Why agent-generated queries are inherently ad hoc and multi-table — and where precomputation falls short. 🌟 The real cost of flattening data — stateful pipelines, storage amplification, and massive backfills when schemas change. 🌟 Gather vs. broadcast vs. shuffle — and why scalable joins need the right distributed execution architecture. 🌟 Why agentic workloads make a cost-based optimizer a must-have — not a nice-to-have. 🌟 Columnar, vectorized, SIMD execution, plus P99 latency and physical workload isolation. 🌟 Real-world results — Demandbase consolidated 49 ClickHouse clusters to one, with 90% lower storage costs. Conductor went from agent idea to production in two months, with queries scanning hundreds of millions of rows in under a second — with no precomputation. ------------------------------------------------------------------------------------------------------------------- Timestamps 00:00 Intro & agenda 01:41 What changes when an agent writes the query 03:34 Four requirements for agentic analytics 04:10 The denormalization workaround 04:47 Cost #1: the pipeline is stateful 06:23 Cost #2: 5-10x storage 07:46 Cost #3: schema changes mean full backfills 09:24 What a fast join requires: life of a query 10:52 Data movement in a distributed join 12:28 Gather vs. broadcast vs. shuffle 14:43 How to shuffle: disk-staged vs. pipelined MPP 16:37 Query planning: is a good optimizer still optional? 18:52 Cost-based vs. rule-based optimizers 20:32 Inside the optimizer: runtime filters 22:07 Query execution: columnar, vectorized, SIMD 24:17 Robustness: p99 latency & workload isolation 25:38 Architecture recap: what to look for 27:06 Introducing PhoenixAI 28:35 The semantic context layer 29:58 Case study: Demandbase — 49 ClickHouse clusters to 1 31:11 Case study: Conductor — sub-second AI answers 32:47 Three things to remember 33:16 Try it on your own workload 34:10 Q&A: when does pre-joining still make sense? 35:52 Q&A: pre-aggregation vs. denormalization ------------------------------------------------------------------------------------------------------------------- Learn more at https://www.phoenixdata.ai/ Connect with us: LinkedIn: https://www.linkedin.com/company/phoenixai-data/ Twitter: https://x.com/phoenixdataai CelerData Website: https://www.phoenixdata.ai/ StarRocks GitHub: https://github.com/StarRocks/StarRocks StarRocks Website: https://www.starrocks.io/ Slack: https://starrocks.io/redirecting-to-slack #RealTimeAnalytics #AIAgents #AgenticAI #DataEngineering #DataInfrastructure #DataArchitecture #OLAP #Database #QueryOptimization #DistributedSystems #DatabasePerformance #RealTimeData #StarRocks #OpenSource #SQL