Database diagnosis: Finding the root causes of poor performance
Discover how we diagnosed database performance issue and designed a scalable data architecture to support faster analytics and growing user demand.
Updated
Categories
High cost of poor design
A well-designed database not only ensures data integrity, security, and scalability for future growth, but also plays a crucial role in performance. For end users, performance is often the most critical factor - nobody likes to sit scowling at the loading screen of a web application while the backend churns away at some unseen process. The ability to provide users with the requested information promptly can be the deciding factor in an application’s success, directly impacting user satisfaction and retention.
We were approached by a client whose analytics application was struggling with database performance. Our task was to investigate the database performance, identify the root causes of the problem, and develop a technical plan for a scalable solution. In this article, we’ll explore how we benchmarked the existing system, what the investigation revealed, and outline the technical solution we proposed.
Database dilemma
The client had developed an innovative web application that allowed users to explore trading data through interactive dashboards and detailed analytics. Behind the scenes, however, the PostgreSQL database was struggling to keep up. Complex queries had to process large datasets and calculate metrics across multiple dimensions, with some results taking up to 20 seconds to load.
With data volumes expected to grow, the application needed to deliver results in milliseconds while supporting more than 1000 concurrent users. We needed to identify the causes of the bottlenecks and determine whether the existing database could meet these demands or whether a different approach was needed.
Digging deeper
To understand where the bottlenecks were occurring, we built a load-testing setup using Locust, a Python tool for simulating user activity. Working from query patterns supplied by the client, we recreated a typical five-minute user session, including queries running concurrently. We containerised the test and deployed it to AWS, using Terraform and GitLab CI/CD to make the setup repeatable.
The results revealed that the database was struggling even under the load of a single user session. Some queries took close to 20 seconds to complete, while CPU usage remained consistently high. A single session could also require up to 24 database connections. Memory usage, by contrast, remained stable, suggesting that it was not the main bottleneck.
These findings gave us a clearer picture of the problem. The database was working hard to process the queries, but the response times and connection demands made it difficult to see how the existing setup could support the level of concurrency the client needed. We next looked at whether query optimisation could make a meaningful difference.
Query quality
The application used an object-relational mapper (ORM) to translate database operations into SQL queries. This approach has benefits for security and maintainability, so replacing it wasn’t our first choice. However, ORMs can generate inefficient SQL, particularly when dealing with complex joins and analytical calculations.
We examined the slowest queries and found opportunities to simplify the SQL. This included removing redundant operations, avoiding repeated calculations, and restructuring parts of the queries to make them more efficient.
The changes made a measurable difference. We reduced the response time of one of the slowest queries by 50%. But it still took around nine seconds to complete, far from the millisecond-range responses the application needed.
We concluded that query optimisation could help, but it couldn’t solve the underlying problem on its own. The application was performing complex analytical calculations on large datasets, and meeting the client’s performance and concurrency requirements would require a different database architecture.
From symptoms to solutions
With query optimisation alone falling short, we looked at whether a different database architecture could better meet the application’s needs. We evaluated several analytical databases, including ClickHouse, Apache Pinot and Apache Druid.
We recommended ClickHouse. The application needed to calculate metrics across a large dataset with many possible filter combinations, making it impractical to precompute every possible result. ClickHouse was a good fit for this type of analytical workload, with options to scale as demand grew. Its similarities to traditional relational databases also made the transition from PostgreSQL more straightforward.
Our proposed architecture combined ClickHouse with AWS S3 and AWS Glue. Raw data would be stored in S3, with Glue handling scheduled data cleaning, transformation and selected aggregations before loading it into ClickHouse. This would move some of the heavier processing out of users’ requests, while leaving calculations that depended on their chosen filters to run when needed.
The design also allowed for future growth. We proposed starting with a single ClickHouse server and expanding the setup as demand increased, with additional read capacity available if required. This approach would reduce query latency while keeping infrastructure and processing costs manageable.
Good foundations for growth
A well-designed data architecture does more than improve performance. It helps users get the information they need without frustrating delays, while giving businesses a system that can scale as their needs grow. The right design also helps businesses make effective use of their data and resources as those needs evolve.
Every application has different requirements, which is why understanding the problem is just as important as finding the right technology. By investigating the client’s specific needs and benchmarking the existing system, we were able to identify its limitations and develop a tailored plan for a more scalable, cost-effective solution.
If your database is slowing down your application, or you’re planning a data-intensive product that needs to scale, we can help you find the right approach. Get in touch to discuss how we can help build a data architecture that works for your users and your business.