Optimising A Hospitality Industry Analytics Platform For Performance And Reliability

Our client provides a SaaS analytics platform for the hospitality industry, helping restaurants and leisure businesses understand their operational metrics, customer behaviour, and revenue performance.

Their ClickHouse deployment was experiencing significant performance issues that were impacting their customers' experience. Query response times were inconsistent, and they were seeing unexplained data quality issues.

They needed expert help to diagnose and resolve these issues while also improving their ability to monitor and maintain the system going forward.

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

• Duplicate data appearing in reports due to issues with their ingestion pipeline and lack of proper deduplication.
• Slow and unpredictable query performance affecting customer-facing dashboards.
• Heavy use of mutations causing significant background load and impacting cluster performance.

We took the following approach to this project:

• Conducted deep-dive analysis of their ingestion pipelines and schema to identify the root cause of data duplication, implementing ReplacingMergeTree and proper deduplication strategies.
• Profiled query performance to identify slow queries and implemented optimisations including query rewrites, partitioning strategy, query patterns, additional projections, and caching strategies.
• Analysed mutation patterns and redesigned their data update strategy to minimise mutation usage, leveraging alternative approaches where possible.
• Reviewed and optimised cluster configuration settings for their specific workload patterns.