ClickHouse® Materialised Views: The Secret Weapon for Fast Analytics on Billions of Rows

When I first built the vehicle comparison feature for CarHunch, I thought I had a simple problem: show users how their car compares to similar vehicles. What I actually had was a performance nightmare. Every comparison query was scanning billions of rows across multiple tables, taking 2-5 seconds per request. Response times were awful, my server was struggling, and I knew there had to be a better way.

That’s when I discovered ClickHouse materialised views — a feature that transformed analytics from painfully slow to blazingly fast. This post shares everything I learned: the many mistakes I made, the optimisations that worked, and the production-ready patterns you can use in your own projects.

The Problem: Slow Queries on Billions of Records

The Challenge

When designing this project, I needed to analyse UK MOT (Ministry of Transport) data at massive scale:

• 136 million vehicles
• 805 million MOT tests
• 1.7 billion defect records

Users want to compare their vehicle against similar ones:

• “How does my 2015 Ford Focus compare to other 2015 Ford Focus vehicles?”
• “What’s the average failure rate for BMW 3 Series?”
• “What are the most common defects for this make/model?”

Initial Approach (Without MVs)

Listing 1: Slow direct query with joins across billions of rows

Performance: Typically 2-5 s per query (unacceptable for production)

Why It’s Slow:

• Joins between 136M vehicles and 805M MOT tests
• Aggregations computed on-the-fly
• No pre-computed statistics
• Full table scans for each comparison

What Are Materialised Views in ClickHouse?

Before we dive in, let me be clear: materialised views aren’t new technology. They’ve been around for decades in various database systems. I’m certainly no database expert, and I’m not claiming to have discovered anything revolutionary. What I have discovered, though, is how incredibly effective ClickHouse‘s implementation of materialised views is — especially for analytical workloads like mine. The combination of ClickHouse’s architecture and its native MV implementation is genuinely special, and that’s what makes it ideal for my project, and worth writing about.

Why ClickHouse Materialised Views Are Different

ClickHouse’s materialised views are engine-level reactive views (see the Altinity Knowledge Base: Materialized Views for details) — meaning they’re implemented at the storage-engine layer (using table engines, not a distinct internal mechanism). They’re physically linked to the underlying source table, and on every insert, ClickHouse synchronously or asynchronously updates the target table (the MV’s destination) using the view’s SELECT statement. No scheduler, trigger, or external job required — it’s part of the same write pipeline.

Compare that to other databases:

• PostgreSQL — Has materialised views, but they’re static snapshots; you have to manually REFRESH MATERIALIZED VIEW or schedule it. There’s no automatic incremental refresh unless you bolt on triggers or use extensions.
• Snowflake — Has automatic materialised views, but they’re restricted (limited table types, lag, cost implications). Updates are asynchronous and opaque.
• BigQuery — Supports incremental MVs, but again, they refresh periodically (every 30 mins by default), not instantly on insert.
• MySQL / MariaDB — Don’t have true MVs; people simulate them with triggers or cron jobs.

Concept

Materialised Views (MVs) are pre-computed query results stored as tables. Think of them as:

• Cached aggregations that update automatically
• After-insert triggers that populate as data arrives
• Pre-computed statistics ready for instant queries

How They Work

1. Define the MV: Write a SELECT query that aggregates your data
2. ClickHouse stores results: Creates a target table with the aggregated data
3. Auto-population: Every INSERT into source tables triggers MV updates
4. Query the MV: Read from the pre-aggregated table instead of raw data

Key Benefits

• Speed: Milliseconds instead of seconds
• Efficiency: Pre-computed aggregations avoid repeated calculations
• Scalability: Works with billions of rows
• Automatic: Updates happen as data arrives (no manual refresh)

Real-World Use Case: Vehicle Comparison Analytics

The Project Requirement

User Story: “When a user views a vehicle, show them how it compares to similar vehicles”

Required Statistics:

• Total number of similar vehicles
• Average MOT test count per vehicle
• Average mileage
• Failure rate percentage
• Most common defects

Why This Needs Materialised Views

Building the Materialised View: Step-by-Step

Step 1: Design the Target Table

Goal: Pre-aggregate vehicle + MOT test data by make/model/fuel/engine

Listing 2: Target table schema for materialised view

Key Design Decisions:

• SummingMergeTree: Automatically sums duplicate keys (perfect for aggregations)
• LowCardinality(String): Compresses repeated values (make/model/fuel_type)
• Partitioning by year: Efficient date range queries
• ORDER BY: Optimises GROUP BY queries

Step 2: Create the Materialised View

Listing 3: Materialised view definition with automatic aggregation

What This Does:

• Triggers on INSERT: Every new MOT test automatically updates the MV
• Pre-aggregates: Groups by make/model/fuel/engine/registration/date
• Calculates stats: Counts, sums, averages computed once and stored
• Filters: Only includes valid data (odometer > 0, make/model not empty)

Step 3: Critical: Create MVs BEFORE Bulk Loading

Why This Matters:

• MVs only process data inserted AFTER they’re created
• In ClickHouse, MVs act like insert triggers, not like retroactive transformations
• Historical data must be backfilled manually using INSERT INTO mv_target SELECT ... FROM source (possible but requires manual work)
• Always create MVs before bulk loading into tables that have MVs attached (see staging tables exception in the MLOps section)

Query Optimisation: Before and After

Before: Direct Query (Slow)

Listing 4: Python code for slow direct query

Problems:

• Full table scan on 136M vehicles
• Join with 805M MOT tests
• Aggregations computed on-the-fly
• High CPU and memory usage

After: Materialised View Query (Fast)

Listing 5: Optimised query using materialised view

Why It’s Fast:

• Pre-aggregated data: No joins needed
• Indexed columns: Fast WHERE clause filtering
• Smaller dataset: Each MV row represents one (vehicle, date, make, model) aggregate — roughly 60% smaller than the raw joined dataset. The MV has ~808M rows vs billions in joins.
• Direct filtering: No subqueries or complex joins

Performance Comparison

MLOps Integration: Keeping MVs in Sync with Delta Processing

The Challenge: Daily Delta Updates

Problem: New MOT data arrives daily via delta files. MVs must stay in sync.

Solution: Automatic MV Population

How It Works:

1. Delta files loaded: INSERT INTO mot_tests_new ...
2. MV triggers: Automatically processes new rows
3. No manual refresh: MVs stay in sync automatically

Listing 6: Python function for delta file loading with automatic MV updates

Critical MLOps Pattern:

• Staging tables: Load data without triggering MVs (faster, less memory)
• Main tables: Copy from staging (triggers MV updates)
• Automatic sync: MVs stay current without manual intervention

Handling MV Memory Issues

Problem: Large delta loads can cause MV memory errors

Listing 7: Python function for safe large delta loading

When to Use:

• Large delta files (> 1M rows)
• Memory-constrained environments
• Need to control MV population timing

DevOps Considerations: Monitoring, Maintenance, and Troubleshooting

Partition Sizing and Memory Limits: Lessons from Production

When populating materialised views on billions of rows, I encountered several critical issues related to partition sizing and memory limits. Here’s what I learned:

The “Too Many Parts” Problem

What Happened:

During initial MV population, I hit ClickHouse’s “too many parts” error. This occurs when:

• Small batch sizes (10K records) create many small parts
• Frequent inserts create new parts faster than ClickHouse can merge them
• Partitioning strategy creates too many partitions
• Memory pressure from tracking thousands of parts

Impact:

• Loading speed: 6-12 records/sec (extremely slow)
• Partition count: 100K+ partitions causing errors
• Memory usage: Excessive memory consumption
• Error rate: Frequent “too many parts” errors

My Solution: Optimised Partitioning and Batch Sizes

1. Larger Batch Sizes

Listing 8: Optimised ClickHouse settings for large batch inserts

2. Memory Limits for MV Population

Listing 9: Memory configuration for MV population on large datasets

3. Partition Settings

Listing 10: Partition configuration to avoid “too many parts” errors

4. Table-Level Settings

Listing 11: Table-level settings for MV target tables

Results

Key Lesson: When populating MVs on large datasets, always use large batch sizes (1M-10M rows), set appropriate memory limits (100GB+), and configure partition settings to allow many parts during loading. The default settings are too conservative for billion-row datasets.

Monitoring MV Health

Key Metrics to Track:

1. MV Row Counts

Listing 12: SQL query to check MV population status

2. MV Lag (Data Freshness)

Listing 13: Check MV data freshness vs source tables

3. MV Query Performance

Listing 14: Python function to monitor MV query performance

Maintenance: Rebuilding MVs

When to Rebuild:

• Schema changes
• Data corruption
• Missing historical data
• Performance degradation

Zero-Downtime Rebuild Strategy:

Listing 15: SQL commands for zero-downtime MV rebuild

Troubleshooting Common Issues

Issue 1: MV Missing Data

Symptoms:

• MV row count < source table row count
• Queries return incomplete results

Diagnosis:

Listing 16: SQL query to diagnose missing MV data

Solution:

• Check MV was created before bulk loading
• Verify WHERE clause filters aren’t too restrictive
• Rebuild MV if needed

Issue 2: MV Performance Degradation

Symptoms:

• Queries getting slower over time
• High CPU usage on MV queries

Solution:

• Run OPTIMIZE TABLE mv_vehicle_mot_summary_target FINAL;
• Check for too many small parts (merge them)
• Consider adjusting partitioning strategy

Issue 3: MV Not Updating

Symptoms:

• New data inserted but MV not reflecting it
• MV lag increasing

Solution:

• Verify MV is attached (not detached)
• Check for errors in system.mutations
• Manually trigger backfill if needed

Performance Results: Real Numbers

Production Performance Metrics

Vehicle Comparison Endpoint (/vehicles/compare):

Average Improvement: 79.7% faster

System-Wide Impact

Cost Savings

Infrastructure Impact:

• CPU usage: 90% reduction
• Memory usage: 80% reduction
• Query time: Typically 5-30x faster (up to 30-50x)
• User satisfaction: Significantly improved

Business Impact:

• Faster page loads = better user experience
• Lower server costs = reduced infrastructure spend
• Better scalability = handle more traffic

Common Pitfalls and How to Avoid Them

Pitfall 1: Creating MVs After Bulk Loading

Pitfall 2: Over-Complex MV Definitions

Design Principle: Keep MV definitions simple and focused. Avoid complex joins and calculations — focus on essential aggregations that your queries actually need.

Pitfall 3: Not Monitoring MV Lag

Mistake:

• Assume MVs are always up-to-date
• No monitoring or alerts
• Users see stale data

Impact: Incorrect results, poor user experience

Monitoring Best Practice: Always monitor MV data freshness. Set up alerts for lag or errors, and track row counts regularly. Stale MVs lead to incorrect results and poor user experience.

Pitfall 4: Wrong Engine Choice

Engine Selection: Choose the right engine for your use case. SummingMergeTree for aggregations (sums, counts), AggregatingMergeTree for complex aggregations with AggregateFunction types (averages, ratios), ReplacingMergeTree for deduplication, MergeTree for general use. Wrong engine choice leads to duplicate rows or incorrect aggregations.

Lessons Learned

Key Takeaways

1. Create MVs Before Bulk Loading
   • MVs only process data inserted after creation
   • Always create MVs first, then load data
   • Saves hours of backfilling later
2. Keep MV Definitions Simple
   • Avoid complex joins and calculations
   • Focus on essential aggregations
   • Test MV population performance
3. Monitor MV Health
   • Track row counts and data freshness
   • Set up alerts for lag or errors
   • Regular performance checks
4. Plan for Maintenance
   • Design zero-downtime rebuild strategies
   • Document MV dependencies
   • Test rebuild procedures
5. Choose the Right Engine
   • SummingMergeTree for aggregations
   • ReplacingMergeTree for deduplication
   • MergeTree for general use

MLOps Best Practices

• Automate MV Management: Include MV creation in deployment scripts, automate health checks, integrate with CI/CD pipeline
• Version Control MV Definitions: Store MV SQL in git, track changes over time, document migration procedures
• Test MV Performance: Benchmark before/after, load test with production data volumes, monitor in production
• Plan for Scale: Consider partitioning strategy, monitor MV table growth, plan for maintenance windows

DevOps Integration

• Infrastructure as Code: Define MVs in SQL files, version control all definitions, automated deployment
• Monitoring and Alerting: Track MV query performance, alert on lag or errors, dashboard for MV health
• Documentation: Document MV purpose and usage, keep migration procedures updated, share knowledge with team

Conclusion

Materialised views transformed my vehicle comparison analytics from slow (2-5 seconds) to fast (50-100ms), typically achieving 5-30x faster performance (up to 30-50x in some cases).
They’re now a critical part of my production infrastructure, handling billions of records with ease.

Key Success Factors:

• Created MVs before bulk loading
• Kept definitions simple and focused
• Monitored health and performance
• Integrated with delta processing pipeline
• Planned for maintenance and scale

For Your Project:

• Start with one MV for your most common query pattern
• Measure performance before/after
• Expand to other query patterns as needed
• Always create MVs before bulk loading into tables with MVs attached (or use staging tables pattern)

Further Reading

If you found this post useful, you might also enjoy:

• Insights with MiniLM: Hands-On Text Embeddings for MLOps — Learn how to use embeddings for semantic search and clustering on real-world data
• MLOps for DevOps Engineers: MiniLM & MLflow Demo — Build production-ready ML pipelines with versioning and monitoring

Have you used Clickhouse and materialised views in your projects? I’d love to hear about your experiences and any lessons learned. Feel free to reach out or share your story in the comments below.