Evolution and Practices of OLAP at Vipshop: Presto, ClickHouse, and Kylin

Introduction: Wang Yu from Vipshop's real‑time platform OLAP team introduces responsibilities covering Presto, Kylin, ClickHouse, Kudu, open‑source modifications, component optimization, and OLAP solution design.

The article is organized into four parts: the evolution of OLAP at Vipshop, Presto practice, ClickHouse usage, and future outlook.

1. OLAP evolution: Vipshop operates over 500 physical machines (≈64 CPU, 256 GB RAM each) serving 20+ online services, handling up to 5 million queries per day and ~3 PB of data. Presto is the main engine, supplemented by Kylin for batch‑oriented workloads, and ClickHouse for high‑performance analytics.

2. Architecture: The engine layer (Presto, Kylin, ClickHouse) sits above a data layer (Hadoop, Spark). Data flows from HDFS to ClickHouse via Waterdrop (or Flink). Kafka and Flink provide real‑time pipelines. A custom proxy (chproxy + HAProxy) ensures HA for ClickHouse; a self‑built tool “nebula” proxies Presto and enriches monitoring.

3. Presto practice: Presto’s coordinator is a single point of failure; cold‑hot query imbalance, lack of query persistence, missing alerting, and weak permission control cause operational pain. To address these, Vipshop built nebula to collect query metadata, store it in MySQL/Hive, and forward queries to Kafka for downstream Flink‑ClickHouse or Hive storage, enabling real‑time monitoring and historical analysis.

4. HA and load balancing: Multiple Presto clusters (A‑D) are grouped; a “spider” uses Redis and ZooKeeper to score clusters and route queries to the healthiest one, allowing seamless upgrades, blue‑green deployments, and dynamic scaling.

5. Self‑developed tools: nebula provides load balancing, blue‑green deployment, SQL tracing, and Hive‑compatible permission handling.

6. Scaling challenges: As more than 20‑30 business units use Presto, issues such as compute imbalance, traffic spikes, opaque client configuration, and dynamic borrowing of resources arise.

7. Containerization: In 2019 all Presto nodes were migrated to Kubernetes, with each physical machine hosting two workers (100 workers per cluster, 40 CPU, 110 GB RAM). Configurations are stored in HDFS, enabling rapid cluster creation, scaling, and balanced placement via anti‑affinity.

8. ClickHouse usage: ClickHouse was introduced for experiments requiring sub‑second latency on hundred‑billion‑row tables. Its columnar storage, vectorized engine, and SIMD optimizations deliver 10× faster queries than Presto for wide‑table aggregations.

9. ClickHouse advantages: Excellent performance on wide‑table aggregations, efficient partitioned joins using hash‑based sharding (murmurHash3_64), and support for large‑scale joins.

10. Experiment platform: ClickHouse stores A/B‑test logs; Flink writes data via a custom sink that resolves engine and cluster metadata from system.tables and system.clusters, batches rows, and handles null values.

11. Large‑table join strategy: Hash‑based sharding aligns rows across tables to enable distributed joins; left tables must not be sub‑queries to avoid local‑only joins.

12. Incremental data handling: MergeTree tables with appropriate default values or Nullable columns, and Flink’s coalesce function, address real‑time upserts.

13. ClickHouse tuning: Engine selection, partitioning strategy, order‑by design, TTL for hot/cold data, index granularity, and per‑node parameter tuning improve performance.

14. Materialized views: Provide pre‑computed results for low‑latency AD‑hoc queries but have limitations for large‑scale incremental updates.

15. Future outlook – Presto: Intelligent auto‑scaling, smart routing, spill‑to‑disk for massive queries, fine‑grained query lifecycle monitoring, and machine‑learning‑driven SQL quality analysis.

16. Future outlook – ClickHouse: RoaringBitmap analytics for massive user‑group data, storage‑compute separation using JuiceFS or similar, RBAC permission management, and tenant‑level resource quotas.

Conclusion: The talk covered Vipshop’s OLAP journey, practical solutions, and upcoming directions.