Airflow with ClickHouse: The New Standard for Big Data Analytics and ETL
A mid-sized online store with 500,000 daily visitors generates 15 million events per month. Over a year, that's 200 million rows across user, order, payment, and log tables. At these volumes, PostgreSQL struggles with analytics, making Airflow + ClickHouse the go-to combo for ETL pipelines.
PostgreSQL shines in OLTP workloads but falls short for OLAP on massive datasets compared to specialized columnar databases. Job market trends back this up: "Airflow ClickHouse" listings outpace "Airflow PostgreSQL" by a wide margin.
PostgreSQL's Limits for Analytics
PostgreSQL is built for transactional loads but hits roadblocks as data grows:
- Scanning large tables (>100M rows) takes minutes instead of seconds.
- Aggregations on terabyte-scale datasets cause overload.
- Limited compression and no MPP architecture hinder scalability.
- Storing a year's history (>500M rows) slows queries to a crawl.
| Feature | PostgreSQL | ClickHouse |
|---------|------------|------------|
| Scan 100M rows | Slow | <1 sec |
| Data Compression | Basic | Up to 10x |
| Parallelism | Limited | Full MPP |
| Analytical JOINs | Tricky | Optimized |
Once tables exceed 100M rows, PostgreSQL becomes inefficient for dashboards and reports, forcing overnight aggregations.
Why ClickHouse Wins in Data Engineering
ClickHouse's column-oriented storage is perfect for aggregations and scans. Key features:
- Vectorized execution: Queries process data in vectors on the CPU for blazing-fast analytics.
- MergeTree family: Table engines with primary keys and partition merging.
- Materialized Views: Pre-computed views to speed up frequent queries.
- ASYNC INSERT: Non-blocking async inserts for event streams.
Unlike row-oriented PostgreSQL, ClickHouse compresses data 5–10x, letting you keep full click histories without cleanup. Year-long slice queries run in seconds, even on 500M rows.
The mindset is shifting: store everything for future analysis instead of scrubbing data. Airflow DAGs adapt seamlessly: sources → Kafka/others → ClickHouse via JDBC or HTTP.
The Job Market Sets the Standard
Russian job listings show ClickHouse dominating data stacks:
- Airflow + PostgreSQL: Fewer openings.
- Airflow + ClickHouse: 50–100% more demand.
Teams pick stacks based on ROI: instant dashboards speed up decisions. ClickHouse plugs into Airflow with ClickHouseOperator or PostgresToClickHouseOperator for smooth migrations.
Key Takeaways
- ClickHouse beats PostgreSQL by 10–100x in OLAP scan and aggregation speed.
- Ideal for event data: logs, metrics, A/B tests—no pre-aggregation needed.
- Airflow + ClickHouse cuts TCO with compression and columnar storage.
- Scales to petabytes via replicated, sharded clusters.
- Not for OLTP: pair with PostgreSQL in a hybrid setup.
— Editorial Team
No comments yet.