Database Performance Optimisation: Slow Queries, Indexes, Execution Plans and Measurement

Measure first to find the queries that consume the most time, read their execution plans, fix them with better indexes, query changes or data model changes, tune connection pools, add caching where data is read often and changes rarely, separate analytics from transactions, and verify each change with before-and-after measurements on your actual database engine. Bigger hardware comes last.

This guide is for developers and engineering leads working with relational databases such as PostgreSQL and MySQL. Specific commands and behaviours differ by engine and version, so validate recommendations against your database's documentation. For wider scaling, see scaling SaaS applications.

Use the database's own statistics, such as the pg_stat_statements extension in PostgreSQL or the slow query log and Performance Schema in MySQL, plus application tracing that links queries to user requests. Rank by total time (frequency times duration), and look at the worst outliers separately. Note the parameters that make queries slow; the same query can be fast for one tenant and slow for another.

Use EXPLAIN to see the planned approach and, in PostgreSQL, EXPLAIN ANALYZE to run the query and see actual rows and timings (PostgreSQL); MySQL also provides EXPLAIN and EXPLAIN ANALYZE (MySQL). Look for full scans of large tables, large differences between estimated and actual rows (often stale statistics), expensive sorts and nested loops over many rows. Run ANALYZE-type statistics updates where estimates are wrong.

Many problems are in the application: N+1 queries that issue one query per row, selecting every column when few are needed, unbounded result sets without pagination, and functions on indexed columns that prevent index use. Fix these in the data-access layer. Keep transactions short to reduce lock contention, and batch writes where possible.

Size connection pools to what the database can handle; too many connections can be slower than fewer. Use a pooler where many application instances connect. Cache data that is read often and changes rarely, with clear invalidation; see caching strategy. Move reporting and analytics to replicas or a separate store so they do not compete with transactions.

Some performance problems are design problems: missing foreign keys and indexes on join columns, wide tables with rarely used columns, storing structured data in unindexed text, or one table serving incompatible access patterns. Archiving old data and partitioning very large tables by time or tenant can help, at the cost of complexity. Change models incrementally with backward-compatible migrations.

Record baseline latency and throughput for key queries, test changes under realistic data volumes and load, and compare. Add database metrics and slow-query alerts to monitoring so regressions are caught early; see application monitoring and observability.

For related guides, see multi-tenant SaaS architecture, architecture review, business intelligence dashboards for moving reporting off the primary database, and cloud cost optimisation.

Database performance work is a measurement loop: find the costly queries, understand their plans, fix indexes, queries and data access, separate workloads, and verify. Validate every recommendation against your engine and version, and leave hardware upgrades until the software is efficient.