Performance Optimisation For A Business Intelligence SaaS Tool

Our client is a software vendor building a modern business intelligence platform that enables companies to create interactive dashboards and reports from their data. They chose ClickHouse Cloud as their analytics backend due to its performance characteristics.

As their customer base grew and data volumes increased, they began experiencing performance challenges. Some customer queries were taking too long, and their ClickHouse Cloud costs were growing faster than expected.

They needed expert guidance to optimise their usage of ClickHouse Cloud for both performance and cost efficiency.

The customer were experiencing the following challenges prior to the engagement:

• Variable query performance with some customer dashboards experiencing slow load times.
• Rapidly growing ClickHouse Cloud costs as data volumes and query complexity increased.
• Difficulty optimising for diverse query patterns across their multi-tenant customer base.
• Uncertainty about best practices for schema design in a BI context with unpredictable query patterns.
• Need to balance performance with cost efficiency in their ClickHouse Cloud deployment.

We took the following approach to this project:

• Analysed query patterns across their customer base to identify common access patterns and optimisation opportunities.
• Implemented strategic projections to accelerate common query patterns while managing storage overhead.
• Optimised their schema design including sort key selection, data types, and compression settings for BI workloads.
• Reviewed and optimised their ClickHouse Cloud service configuration to balance performance and cost.
• Implemented query-level optimisations and provided guidance on generating efficient SQL from their BI layer.