Case Study: Reducing Query Times with Index Optimisation

Slow databases frustrate users and cost businesses money. This case study shows how a mid-sized e-commerce platform improved database performance by optimising indexes, reducing costs, and delivering faster query times. Here's the quick takeaway:

• Problem: Slow queries during peak traffic caused delays in shopping cart updates, product searches, and checkouts. Throwing more servers at the problem didn’t help.
• Solution: The team analysed query patterns, added targeted indexes (composite, partial, and covering), and improved database efficiency without increasing hardware.
• Results: Query response times improved significantly, disk I/O dropped by 30%, and infrastructure costs were reduced.

This approach highlights the value of understanding query behaviour, using the right indexing strategies, and maintaining database health to support growth and improve user experience.

How to Speed Up Your Queries with Indexes

Database Analysis and Starting Point

Before making any adjustments, the team focused on thoroughly understanding the current database system and setting benchmarks to measure progress.

Database Setup Details

The platform relied on PostgreSQL, running on a dedicated server cluster equipped with fast SSDs. It managed millions of records while handling high query volumes during peak times. Multiple microservices built with Node.js and Python accessed the database through connection pools. The system’s workload was intensified by complex joins across key tables and the demand for real-time analytics, which occasionally led to write bottlenecks. With this setup in mind, the team delved into performance metrics to identify problem areas.

Finding Performance Issues

The investigation started with PostgreSQL’s built-in monitoring tools and query analysis features. Using EXPLAIN ANALYSE, the team discovered that many queries were performing full table scans instead of leveraging indexes. Further insights from pg_stat_statements revealed that a small number of query types were consuming a disproportionate amount of processing time. To dig deeper, tools like pgAdmin and custom monitoring scripts were employed to track real-time metrics.

Database logs provided critical clues, showing frequent lock contention that caused noticeable transaction delays. Additionally, a lack of buffer cache retention forced the system to depend on slower disk reads. Connection pool monitoring exposed another issue: during peak traffic, database connections were sometimes maxed out, triggering a cascading effect that slowed down overall performance. These findings were crucial in quantifying the system’s limitations and establishing baseline performance metrics.

Starting Performance Numbers

Initial measurements painted a concerning picture. Query response times were sluggish, and critical functions - like product searches and checkouts - suffered from significant delays. The system showed high CPU usage and considerable I/O wait times, indicating inefficiencies in handling disk operations. The underutilised buffer cache further exacerbated the problem, leading to repeated, costly disk access.

Efforts to address these issues by upgrading server specifications had already driven up infrastructure costs, but without any notable improvement in performance. This inefficiency directly impacted business outcomes, with customer complaints rising and development resources being diverted to firefight database-related problems. These baseline metrics underscored the urgency of optimising indexes to restore system efficiency and improve customer satisfaction.

Index Improvement Plan

To tackle database inefficiencies, the team crafted a detailed plan to optimise indexing. By zeroing in on specific performance issues, they aimed to enhance query speed while maintaining operational balance.

Query Review and Index Planning

The team began by analysing query patterns to identify the best candidates for indexing. Using metrics from pg_stat_statements, they pinpointed the most frequently executed queries and those consuming the highest execution time. This analysis highlighted search functions, user authentication, and reporting queries as the primary bottlenecks.

Columns with high-cardinality values, such as product SKUs, user email addresses, and timestamp fields, became a focus due to their strong selectivity. Additionally, common filter combinations in WHERE clauses - especially those involving date ranges and status fields - were prioritised for indexing.

The planning phase aimed to strike a balance between speeding up queries and minimising the impact on data modification processes. Priority was given to indexes supporting critical business functions. To refine their approach, the team used EXPLAIN ANALYSE to examine PostgreSQL's query execution plans. This revealed cases where the database relied on sequential scans instead of existing indexes, often due to outdated statistics or suboptimal index structures.

These findings shaped the targeted index improvements that followed.

Adding New Indexes

The team implemented several new indexes tailored to specific query needs. Composite indexes were introduced to optimise multi-column queries. For instance, a composite index on the orders table covering (customer_id, order_date, status) significantly improved dashboard load times for customer service teams.

Partial indexes proved particularly useful for queries with repetitive filter conditions. For example:

• A partial index on active products (WHERE status = 'active') reduced index size while improving search performance.
• Another partial index on recent transactions (WHERE created_at > NOW() - INTERVAL '90 days') sped up financial reporting queries.

To minimise disruption, indexes were added during off-peak hours. The team utilised PostgreSQL's CONCURRENTLY option to build most indexes without impacting live operations, though this required careful monitoring. Testing in a staging environment that mirrored production data ensured the new indexes performed as expected under real-world conditions.

Covering indexes were deployed for frequently accessed columns, allowing the database to fulfil many queries without needing to access the main table. This approach reduced I/O operations and addressed earlier issues with full table scans and slow query responses.

The phased implementation allowed the team to measure improvements incrementally, adjusting their strategy based on observed performance gains before proceeding with further optimisations.

Maintenance and Tracking

To sustain the performance improvements, the team implemented robust monitoring and maintenance practices. Automated updates to database statistics ensured PostgreSQL's query planner had accurate, current data distribution patterns, preventing inefficient execution plans.

Index usage was closely monitored to confirm effectiveness. This tracking also helped identify unused indexes, which were either consuming unnecessary storage or slowing write operations. Regular VACUUM and ANALYSE operations were scheduled during maintenance windows to preserve index performance, and index bloat was monitored to catch potential issues early.

Performance benchmarks set during the initial analysis phase became the baseline for ongoing monitoring. Automated alerts notified the team if query response times exceeded acceptable thresholds, enabling proactive intervention before users noticed any decline in performance.

Finally, all index changes, along with their rationale, were meticulously documented. This knowledge base proved invaluable for addressing similar challenges elsewhere in the system and served as a guide for future optimisations. These benchmarks and practices ensured the improvements were both effective and sustainable.

Results and Outcomes

After implementing the planned index upgrades, systematic monitoring revealed notable gains in database performance. The targeted enhancements, as outlined earlier, brought measurable improvements in speed, efficiency, and cost-effectiveness.

Speed and Performance Gains

The optimised indexing significantly boosted query response times while cutting down disk I/O by around 30% [2]. Additionally, resource usage - such as CPU and memory - dropped, proving the effectiveness of these changes. The result? A smoother, faster experience for users, even during high-demand periods.

Cost Savings

These performance improvements translated directly into financial benefits. By avoiding the need to move to more expensive cloud database tiers, the project met its cost-saving objectives. Hosting expenses were reduced, along with storage and network transfer costs, aligning with the budget goals outlined in the planning phase [1].

Before and After: A Snapshot

Here’s a quick look at the impact of the changes:

These improvements have proven sustainable over time, providing a solid base for future upgrades and ensuring smooth, cost-efficient database operations.

What We Learned and Best Practices

This database optimisation project uncovered some key takeaways that can guide future efforts to boost performance.

Main Lessons

Regular monitoring is a cornerstone of maintaining a well-performing database. If you don’t track query patterns and resource usage, performance issues can creep in unnoticed until they start affecting the user experience. Setting baseline metrics before making changes is equally important - it helps you measure the impact of your adjustments effectively.

Taking a phased approach to implementation is another valuable strategy. Instead of making sweeping changes all at once, breaking the process into smaller steps allows for better monitoring and reduces the risk of unintended consequences. It also helps pinpoint which changes are delivering the most impact.

Balancing read and write performance is a critical consideration. While indexes can speed up query execution, they also add a slight overhead to insert and update operations. Being aware of this trade-off helps in determining the right number and types of indexes for your specific needs.

Lastly, keeping database statistics up-to-date is non-negotiable. The query optimiser relies on these statistics to develop efficient execution plans. If they’re outdated, even the best-designed indexes won’t perform as expected.

Index Management Guidelines

These lessons directly shape the following best practices for managing indexes:

• Regular Query Profiling: Keep an eye on query performance to catch and address potential slowdowns before they become user-facing issues.
• Avoid Excessive Indexing: Too many indexes can lead to unnecessary maintenance and storage costs. Focus only on what’s needed.
• Monitor Index Usage: Identify and remove unused or rarely accessed indexes that waste resources without adding value. This step can noticeably improve database efficiency.
• Use Composite Indexes Wisely: For queries involving multiple columns, create composite indexes. Make sure the column order reflects query patterns, typically starting with the most selective columns.
• Plan Maintenance Windows: When making significant index changes - especially on large tables - schedule these updates during maintenance periods to minimise performance disruptions.

Conclusion

Key Results Summary

Index optimisation brought a transformative effect to the database, dramatically cutting query response times and lowering resource consumption. These improvements directly translated into reduced infrastructure costs. By adopting a phased approach, each enhancement was carefully monitored, ensuring measurable and lasting benefits.

Query execution times saw dramatic reductions after implementing targeted index strategies. Beyond the speed gains, this optimisation also delivered cost savings by reducing server resource demands and enhancing overall database efficiency.

The use of composite indexing proved especially effective for handling complex queries involving multiple columns. By crafting the right indexes and eliminating unnecessary overhead, the database achieved its most significant performance gains.

Closing Thoughts

This project demonstrates how index optimisation offers long-term value to data-driven businesses. It highlights that improving database performance doesn’t always require costly hardware upgrades or complete system overhauls. Instead, targeted index optimisation can yield substantial results.

The success of this initiative underscores the importance of a systematic approach to database management. Regular monitoring, thorough analysis, and strategic implementation are key to creating sustained improvements that benefit both users and business operations. Lessons from this project - such as the importance of baseline metrics and balancing read and write performance - serve as a guide for similar optimisation efforts.

Hokstad Consulting's expertise in DevOps transformation and database performance tuning played a pivotal role in achieving these results. Their structured approach to cloud cost management and infrastructure optimisation aligns seamlessly with the strategies applied in this project.

The takeaway is simple: proactive database optimisation is more than a technical task - it’s a business strategy that enhances user satisfaction and operational efficiency.

FAQs