SysPad › Examples › Product analytics platform
Product analytics platform
One Kafka event stream feeding two OLAP stores: Apache Pinot for the in-product analytics panel at high QPS, and ClickHouse for analyst queries and BI, loaded in batches of a thousand events per INSERT.
Why the same data needs two different query engines, how Pinot fan-out and stream ingestion share server cores, and how insert batching keeps ClickHouse under its part-merge ceiling.
- Throughput: 1,500 req/sec
- First to saturate: Nothing at this load
- Estimated cost: $19k/mo
How it works
- Analytics API
- Every panel render asks Pinot two questions, which is why the edge to it carries a multiplier of 2. Getting that number wrong is the most common reason a real cluster is twice the size anyone expected.
- Pinot (in-product panels)
- Three replica groups mean a query asks three servers instead of eight, so the tail is set by three machines rather than the whole cluster. Ingestion is a property of the node (rows/s off the stream, consumed once per replica), so the stream edge here carries no query load.
- Event Collector
- Three events per app interaction: a view, an action, and a heartbeat. Collectors do nothing but validate and publish, which is why they are small and many.
- Event Stream
- One stream, two independent consumers with different deadlines. Pinot reads it continuously for freshness; the ClickHouse loader reads it in batches for throughput.
- ClickHouse Loader
- Buffers a thousand events and sends them as one INSERT. That is the whole job, and it is the difference between a healthy cluster and a broken one. In Flow mode the same fact is the batch step’s calls: 1000, which charges this node for a thousand events while passing one statement on.
- ClickHouse (ad-hoc + BI)
- Six nodes at replication 2 is three shards, and three shards sustain roughly two dozen new parts a second. The loader sends about five statements a second, so there is real headroom. Set the loader edge multiplier to 1 (no batching) and the same cluster is 4,500 statements a second against that same ceiling.
- BI Dashboards
- Human-facing terminus: a handful of sessions a day, priced per person rather than per query. Nothing about this node scales with the event rate.
Request flows
In-product analytics panel
- Product App
- API Load Balancer
- Analytics API
- Pinot (in-product panels)
Event ingest
- Event Collector
- Event Stream
- Pinot (in-product panels)
Batch load into ClickHouse
- ClickHouse Loader
- ClickHouse (ad-hoc + BI)
Analyst query and BI refresh
- ClickHouse (ad-hoc + BI)
- BI Dashboards