ClickHouse
What it is: Open-source columnar OLAP database. Real-time analytics on billions of rows. Developed by Yandex for web analytics at scale.
What It Does Best
Blazing fast queries. Scan billions of rows in seconds. 100-1000x faster than row-based databases for analytics.
Real-time ingestion. Query fresh data immediately. No ETL delays. Perfect for operational analytics.
Compression. 10x-100x compression ratios. Store more data, query it faster.
Key Features
Columnar storage: Read only columns needed, skip the rest
Vectorized execution: Process data in batches using CPU SIMD instructions
Data skipping indexes: Skip irrelevant data blocks automatically
SQL support: Full SQL with extensions for arrays, nested data, window functions
Real-time materialized views: Precompute aggregations on insert
Pricing
Open Source: Free, Apache 2.0 license (self-hosted)
ClickHouse Cloud: $0.37/GB storage/month, $0.62/GB scanned
Free tier: 30-day free trial with $300 credits
Serverless option: Pay only for what you use, auto-scaling
When to Use It
✅ Real-time analytics dashboards
✅ Event and log analytics at scale
✅ Application monitoring and observability
✅ User behavior analytics
✅ Time-series data with aggregations
When NOT to Use It
❌ Transactional workloads (no full ACID)
❌ Frequent updates/deletes (optimized for inserts)
❌ Small datasets (overkill)
❌ Document storage with no analytics (use MongoDB)
❌ Needs OLTP features (use PostgreSQL)
Common Use Cases
Web analytics: Page views, user sessions, conversion funnels at massive scale
Observability: Logs, metrics, traces for application monitoring
Ad tech: Real-time bidding analytics and reporting
Finance: Trading analytics, market data analysis
Gaming: Player behavior analytics and real-time leaderboards
ClickHouse vs Alternatives
vs Snowflake: ClickHouse faster and cheaper, Snowflake easier and more features
vs PostgreSQL: ClickHouse 100x faster for analytics, Postgres better for transactions
vs Druid: ClickHouse simpler, Druid better for sub-second queries
Unique Strengths
Extreme compression: Store 10-100x more data in same space
Real-time ingestion: No delay between write and query
Cost-effective: Much cheaper than cloud data warehouses at scale
SQL-native: No new query language to learn
Bottom line: The fastest OLAP database you can run yourself. Cheaper than Snowflake, faster than most alternatives. Learning curve is real, but performance payoff is massive. Best for real-time analytics.