Last updated: 17 May 2026
Database performance tuning improves query execution speed and resource efficiency through indexing, query optimisation, connection pooling, caching and server configuration. Most database performance issues — 80% of them — are caused by missing indexes or poorly written queries, not hardware limitations. A well-tuned database can run 10–100x faster than an untuned one on identical hardware.
Database performance directly determines application response time. A web application that takes 8 seconds to load is typically waiting for a slow database query — not for network latency, application code, or server compute. Reducing that query from 8 seconds to 200 milliseconds requires database tuning, not a hardware upgrade.
The business case for database performance tuning is straightforward. Amazon's 2006 research famously quantified that every 100ms of additional latency cost them 1% in sales. More recent data from Google's Core Web Vitals programme shows that a one-second improvement in page load time increases mobile conversion rates by up to 27% (Google/Deloitte, 2019). For any business running a database-backed application, slow queries translate directly to lost revenue and user abandonment.
Hardware vs tuning:
A common mistake is reaching for a hardware upgrade before tuning the database. Doubling server RAM or moving to a faster SSD can provide a 20–50% performance improvement at significant cost. Fixing a missing index on a high-traffic table can provide a 10–1,000x improvement at near-zero cost. Hardware upgrades mask the problem; query optimisation and indexing solve it.
The relationship between tuning and hardware is additive, not alternative. A well-tuned database on modest hardware consistently outperforms an untuned database on expensive hardware. Once the database is tuned, hardware upgrades provide their full marginal benefit without being wasted filling in for tuning gaps.
When to tune vs when to upgrade:
Tune first when: query execution plans show full table scans on large tables, CPU utilisation is low but queries are slow (indicative of I/O waits from disk reads that indexes would prevent), or new features have been deployed without index review. Consider hardware upgrades when: all indexes are optimal, queries are well-written, connection pooling is configured, caching is implemented, and the database is still hitting CPU or memory limits under genuine load.
The seven core database performance tuning techniques are: indexing strategy, query optimisation, connection pooling, caching, server configuration tuning, monitoring and profiling, and schema design review. Each addresses a different layer of the performance stack. Most real-world performance problems require a combination of two or three of these techniques rather than a single intervention.
The sections below cover each technique with specific, actionable guidance and SQL examples for MySQL, PostgreSQL and MSSQL.
Indexing is the single most impactful database performance tuning technique. An index allows the database engine to find rows matching a query condition without scanning every row in the table — the difference between looking up a word in a sorted dictionary versus reading every page. A missing index on a million-row table can cause a query to take 8 seconds; the correct index on the same table can reduce it to under 10 milliseconds.
Index types and when to use them:
=), range conditions (>, <, BETWEEN) and sorting (ORDER BY). The correct choice for most column types on OLTP workloads.(customer_id, order_date) supports queries filtering on customer_id alone or on customer_id AND order_date, but not on order_date alone.orders WHERE status = 'pending' in a system where most orders are completed and only a fraction are pending.MATCH...AGAINST in MySQL, @@ in PostgreSQL's tsvector, CONTAINS in MSSQL). Standard B-tree indexes do not support efficient substring matching.Creating indexes — SQL examples:
-- MySQL: simple index on a frequently filtered column
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- MySQL: composite index for queries filtering on both columns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- PostgreSQL: covering index (INCLUDE adds non-key columns)
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date)
INCLUDE (total_amount, status);
-- PostgreSQL: partial index for pending orders only
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- MSSQL: filtered index equivalent
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- MSSQL: covering index with INCLUDE
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date)
INCLUDE (total_amount, status);
Index selectivity:
Index selectivity measures how many distinct values exist in a column relative to total rows. A column with high selectivity (many distinct values, like customer_email) benefits greatly from an index. A column with low selectivity (few distinct values, like a boolean is_active or a status column with 3 values) benefits little — the database will often prefer a full table scan over using a low-selectivity index, especially for queries that return more than 10–20% of rows.
Check selectivity in MySQL with:
SELECT COUNT(DISTINCT customer_id) / COUNT(*) AS selectivity
FROM orders;
-- Closer to 1.0 = high selectivity = good candidate for index
-- Closer to 0.0 = low selectivity = index unlikely to help
When NOT to index:
LIKE '%searchterm%') — B-tree indexes cannot accelerate these; full-text search is requiredEXPLAIN plan analysis:
Before and after adding an index, use EXPLAIN to verify the execution plan change:
-- MySQL: check execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2026-01-01';
-- Look for: type=ref or type=range (good) vs type=ALL (bad - full table scan)
-- Look for: key column showing which index was used
-- Look for: rows column showing estimated rows scanned
-- PostgreSQL: EXPLAIN ANALYZE runs the query and shows actual row counts
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 12345 AND order_date > '2026-01-01';
-- Look for: Index Scan or Index Only Scan (good) vs Seq Scan (investigate)
-- Look for: actual rows vs estimated rows - large discrepancy suggests stale statistics
-- MSSQL: SET STATISTICS IO ON to see logical reads
SET STATISTICS IO ON;
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2026-01-01';
-- Lower logical reads = better index usage
If EXPLAIN shows a full table scan (type=ALL in MySQL, Seq Scan in PostgreSQL) on a large table for a query that runs frequently, a missing index is almost certainly the cause. Adding the correct index and re-running EXPLAIN should show the plan switch to an index scan.
Query optimisation means rewriting SQL to reduce the work the database engine must do — fewer rows read, fewer joins, better use of existing indexes, and avoiding patterns that force the engine to abandon an efficient execution plan. Most application-level slow queries fall into four categories: SELECT *, N+1 query patterns, inefficient subqueries and avoidable full table scans.
Avoid SELECT *:
Selecting all columns forces the database to read the full row, even when only 2–3 columns are needed. This bypasses covering index optimisations and increases I/O unnecessarily.
-- Bad: reads all columns, cannot use covering index
SELECT * FROM orders WHERE customer_id = 12345;
-- Good: reads only needed columns, can use covering index
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 12345;
N+1 query pattern:
The N+1 pattern occurs when application code executes one query to fetch N parent records, then N additional queries to fetch related data for each parent — totalling N+1 queries. With 100 customers, this becomes 101 queries instead of 1. N+1 is the most common performance anti-pattern in ORM-backed applications (Rails, Django, Laravel, Hibernate).
-- N+1 pattern (bad): 1 query + N queries per order
-- Application code: for each order, fetch customer
SELECT * FROM orders WHERE date = '2026-05-17'; -- returns 500 rows
-- Then 500 individual queries:
SELECT * FROM customers WHERE id = 1;
SELECT * FROM customers WHERE id = 2; -- ... repeated 500 times
-- Fix: JOIN or subquery fetches all data in one query
SELECT o.order_id, o.total_amount, c.customer_name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date = '2026-05-17';
-- 1 query, same result set, fraction of the execution time
JOINs vs subqueries:
Correlated subqueries (subqueries that reference the outer query) execute once per row in the outer query — the same problem as N+1. In most cases, a JOIN or a non-correlated subquery is faster:
-- Correlated subquery (potentially slow - runs once per row in orders)
SELECT order_id, total_amount,
(SELECT customer_name FROM customers WHERE id = o.customer_id) AS customer_name
FROM orders o;
-- JOIN equivalent (faster - single execution)
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Non-correlated subquery in WHERE clause (fine - executes once)
SELECT order_id, total_amount FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'GB'
);
Avoiding full table scans:
Functions applied to indexed columns in WHERE clauses prevent index usage and force full table scans:
-- Bad: wrapping indexed column in YEAR() prevents index use
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- Good: range condition on the column directly uses the index
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
-- Bad: implicit type conversion can prevent index use (string vs integer)
SELECT * FROM orders WHERE customer_id = '12345'; -- customer_id is INT
-- Good: match column data type exactly
SELECT * FROM orders WHERE customer_id = 12345;
-- Bad: leading wildcard LIKE cannot use B-tree index
SELECT * FROM products WHERE name LIKE '%widget%';
-- Good: full-text search for substring matching
SELECT * FROM products WHERE MATCH(name) AGAINST('widget' IN BOOLEAN MODE); -- MySQL
-- PostgreSQL: use pg_trgm extension for trigram indexes on LIKE patterns
Query result pagination:
OFFSET-based pagination becomes exponentially slower as the offset increases. Keyset (cursor) pagination is the correct approach for large result sets:
-- Bad: LIMIT/OFFSET pagination degrades as page number increases
-- Page 1000 requires the database to read and discard 999,000 rows
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 19980;
-- Good: keyset pagination uses an index seek for constant-time performance
SELECT * FROM orders WHERE order_id > 19980 ORDER BY order_id LIMIT 20;
Connection pooling maintains a pre-established set of database connections that applications reuse, rather than opening and closing a new connection for every query. Establishing a database connection takes 20–100ms and involves authentication, session initialisation and memory allocation on the database server. Under load, an application making thousands of requests per minute can exhaust database connection limits and spend more time connecting than querying.
Why connection limits matter:
MySQL and PostgreSQL have configurable maximum connection limits (typically 100–200 by default). Each active connection consumes memory on the database server — PostgreSQL allocates approximately 5–10MB per connection. With 100 connections, that is 500MB–1GB consumed just by connection overhead before any query work begins. Connection pooling reduces the number of active backend connections while supporting a much larger number of application threads.
PgBouncer for PostgreSQL:
PgBouncer is the standard connection pooling solution for PostgreSQL. It sits between the application and PostgreSQL, maintaining a small pool of real database connections that it multiplexes across a much larger number of application connections. PgBouncer operates in three modes:
# PgBouncer pgbouncer.ini configuration (transaction mode)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000 ; Max application connections
default_pool_size = 25 ; Real PostgreSQL connections per database/user pair
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600
With this configuration, 1,000 application connections share 25 real PostgreSQL connections. This is sufficient for most OLTP workloads because real-world transactions are short — the connection is held only for the transaction duration, then returned to the pool.
MySQL connection pool settings:
MySQL handles connection management differently from PostgreSQL. The thread-based architecture in MySQL means connection overhead is lower, but connection pooling is still beneficial under high concurrency. Most application frameworks that connect to MySQL use built-in connection pooling:
-- MySQL server settings (my.cnf)
max_connections = 200 # Maximum simultaneous connections
thread_cache_size = 50 # Cached threads for reconnecting clients
wait_timeout = 600 # Close idle connections after 10 minutes
interactive_timeout = 600
For PHP applications using MySQL (including applications built on Laravel, WordPress or Magento), the PDO connection layer maintains its own connection pool within the PHP process lifetime. For long-running processes (queue workers, daemons), explicitly managing connection lifetime avoids stale connection errors.
Connection pool sizing formula:
A common guideline from PostgreSQL performance expert Markus Winand: pool size = (number of CPU cores * 2) + number of effective spindles. For a 4-core server with SSD storage (treat as 1 effective spindle): pool size = (4 * 2) + 1 = 9. This is smaller than many engineers expect. A pool of 9–25 connections on typical application hardware handles most OLTP workloads efficiently. Larger pools often cause more context switching than they prevent.
Caching stores the results of database queries in memory (Redis, Memcached, or application memory), serving subsequent identical requests from the cache rather than hitting the database. For read-heavy workloads where the same data is queried repeatedly, caching can reduce database load by 60–90%, dramatically improving response times and freeing database capacity for writes and complex queries.
Query result caching with Redis:
Redis is the most widely used caching layer for web applications. Store query results as JSON or serialised objects with an expiry time appropriate to how often the underlying data changes:
// PHP example: cache a slow database query with Redis
$cacheKey = 'products:category:' . $categoryId;
$cached = $redis->get($cacheKey);
if ($cached) {
return json_decode($cached, true);
}
// Query only runs on cache miss
$products = $db->query(
'SELECT id, name, price, stock FROM products WHERE category_id = ? AND active = 1',
[$categoryId]
);
$redis->setex($cacheKey, 300, json_encode($products)); // Cache for 5 minutes
return $products;
Memcached vs Redis:
Both Memcached and Redis work as in-memory caching layers. Redis is preferred for most modern applications because it supports richer data structures (hashes, sets, sorted sets, streams), persistence to disk, pub/sub messaging, and Lua scripting. Memcached is simpler and marginally faster for pure key-value caching. Unless you have a specific reason to choose Memcached, Redis is the practical default in 2026.
Cache invalidation patterns:
The hardest problem in caching is cache invalidation — ensuring the cache is cleared or updated when the underlying data changes. The three main patterns are:
MySQL query cache (deprecated note):
MySQL's built-in query cache, which cached the raw result set of SELECT queries, was deprecated in MySQL 5.7 and removed in MySQL 8.0. It was found to cause contention under write-heavy workloads. Do not rely on it; use an application-level cache with Redis instead.
PostgreSQL result caching:
PostgreSQL does not have a native query result cache. Application-level caching with Redis is the standard approach. PostgreSQL's shared_buffers acts as a buffer cache for raw data pages (not query results), reducing disk I/O — this is configured at the server level and is distinct from query result caching.
Database server configuration tuning adjusts memory allocation, parallelism and I/O settings to match your hardware and workload. Default database installations are conservative — they are designed to run on minimal hardware rather than to maximise performance. Applying the settings below to a properly-resourced server typically delivers a 30–200% performance improvement over defaults without changing a single query or index.
MySQL — Key Configuration Settings (my.cnf):
[mysqld]
# InnoDB buffer pool: the most important MySQL setting
# Set to 60-80% of total server RAM for a dedicated MySQL server
# Example for 16GB server:
innodb_buffer_pool_size = 10G
# For buffer pools > 1GB, use multiple instances to reduce contention
innodb_buffer_pool_instances = 8
# InnoDB log file size: larger = fewer checkpoints, better write throughput
# Set to 1-2GB for write-heavy workloads
innodb_log_file_size = 1G
# Flush logs to disk on each commit (safest, but slower writes)
# Set to 2 for higher write throughput with slight durability risk
innodb_flush_log_at_trx_commit = 1
# Read ahead for full scans (useful for reporting workloads)
innodb_read_ahead_threshold = 56
# Thread concurrency: 0 = unlimited (recommended in MySQL 8.0)
innodb_thread_concurrency = 0
# Temporary tables in memory before spilling to disk
tmp_table_size = 256M
max_heap_table_size = 256M
# Sort and join buffers (per-query allocations, not global)
sort_buffer_size = 4M
join_buffer_size = 4M
PostgreSQL — Key Configuration Settings (postgresql.conf):
# Shared buffers: PostgreSQL's page cache
# Set to 25% of total server RAM (PostgreSQL also relies on OS page cache)
# Example for 16GB server:
shared_buffers = 4GB
# Work memory: per-sort-operation allocation for ORDER BY, hash joins
# Total memory use = work_mem * max_connections * sort operations per query
# Start conservative and increase if you see temp file usage in EXPLAIN ANALYZE
work_mem = 64MB
# Effective cache size: estimate of total memory available for caching
# Used by query planner to decide index vs seq scan cost
# Set to 75% of server RAM
effective_cache_size = 12GB
# Maintenance work memory: for VACUUM, CREATE INDEX, ALTER TABLE
maintenance_work_mem = 512MB
# Parallelism: allow PostgreSQL to use multiple CPU cores per query
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# Write-ahead log settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
# Random page cost: set lower for SSD storage (default 4.0 assumes HDD)
random_page_cost = 1.1 # For SSD
# Statistics collection for query planner
default_statistics_target = 100 # Increase for tables with skewed data
MSSQL — Key Configuration Settings:
-- Maximum server memory: leave 2-4GB for the OS
-- Example for 16GB server: set to 12288 MB
EXEC sp_configure 'max server memory (MB)', 12288;
RECONFIGURE WITH OVERRIDE;
-- MAXDOP: maximum degree of parallelism (CPU cores to use per query)
-- For OLTP workloads: set to number of physical cores per NUMA node, max 8
-- For reporting/OLAP workloads: can be higher
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE WITH OVERRIDE;
-- Cost threshold for parallelism
-- Queries below this cost estimate run single-threaded
-- Default is 5 (very low); increase to 25-50 to prevent excess parallelism on small queries
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE WITH OVERRIDE;
-- Enable optimise for ad hoc workloads to reduce plan cache bloat
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE WITH OVERRIDE;
-- Check current configuration
SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'max degree of parallelism',
'cost threshold for parallelism', 'optimize for ad hoc workloads');
Identifying performance bottlenecks requires monitoring specific metrics before tuning anything. Optimising a query that is not actually responsible for slow application response is wasted effort. The correct approach is: measure first, identify the top 5 slowest queries by total execution time, fix those, then measure again.
MySQL slow query log:
# Enable slow query log in my.cnf
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0 # Log queries taking more than 1 second
log_queries_not_using_indexes = ON # Log queries with full table scans
# Analyse the slow query log with mysqldumpslow
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# -s t: sort by total time
# -t 20: show top 20 queries
# Or use pt-query-digest (Percona Toolkit) for more detailed analysis
pt-query-digest /var/log/mysql/slow.log
PostgreSQL pg_stat_statements:
-- Enable pg_stat_statements in postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all
-- After enabling, view the slowest queries by total execution time:
SELECT query,
calls,
total_exec_time / 1000 AS total_seconds,
mean_exec_time AS mean_ms,
rows,
(total_exec_time / calls) AS avg_ms_per_call
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Find queries with the most buffer hits (high I/O workload):
SELECT query,
shared_blks_hit + shared_blks_read AS total_buffer_hits,
shared_blks_hit,
shared_blks_read
FROM pg_stat_statements
ORDER BY total_buffer_hits DESC
LIMIT 20;
MSSQL Query Store:
-- Enable Query Store on a database
ALTER DATABASE MyDatabase SET QUERY_STORE = ON
WITH (OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900);
-- Find top 20 queries by total CPU time
SELECT TOP 20
qt.query_sql_text,
rs.count_executions,
rs.avg_duration / 1000 AS avg_ms,
rs.avg_cpu_time / 1000 AS avg_cpu_ms,
rs.avg_logical_io_reads
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan qp ON rs.plan_id = qp.plan_id
JOIN sys.query_store_query qq ON qp.query_id = qq.query_id
JOIN sys.query_store_query_text qt ON qq.query_text_id = qt.query_text_id
ORDER BY rs.avg_cpu_time DESC;
Key metrics to monitor continuously:
SHOW ENGINE INNODB STATUS. In PostgreSQL, query pg_locks and pg_stat_activity for blocking sessions.Database performance tuning approaches differ significantly based on workload type. OLTP (Online Transaction Processing) workloads — typified by eCommerce, SaaS applications and ERP systems — involve many short concurrent transactions. OLAP (Online Analytical Processing) workloads — typified by reporting databases, data warehouses and business intelligence systems — involve fewer but very large query operations across millions of rows.
OLTP optimisation priorities:
innodb_flush_log_at_trx_commit=1 (MySQL) for ACID compliance on financial dataOLAP optimisation priorities:
max_parallel_workers_per_gather) for large analytical queriesSaaS multi-tenant database optimisation:
Multi-tenant SaaS databases — where a single database instance serves multiple customers — have specific optimisation concerns. Every query must include a tenant_id filter. Composite indexes that lead with tenant_id are essential: a query without a tenant_id filter on a multi-tenant table performs a full table scan across all tenants' data. Row-level security (PostgreSQL) or always-filtered views (MSSQL) enforce tenant isolation at the database level. Tenant-specific query patterns that diverge (one tenant with 10 million orders, another with 100) can cause query planner decisions optimised for average tenant to perform poorly for edge-case tenants.
Ecommerce database specifics:
Ecommerce databases experience predictable traffic spikes — sale events, seasonal peaks. Connection pool limits that are adequate for average traffic become bottlenecks under peak load. Pre-tune for your peak, not your average. The product catalogue and category pages benefit most from Redis caching, as they are read-heavy and change infrequently relative to their query volume.
After database performance audits across dozens of UK business applications, the pattern is consistent: 70% of slow query tickets have a missing composite index as the root cause. The most impactful single change is almost always adding a covering index on the most-queried table's filtered columns. We have seen this reduce page load times from 8 seconds to 200 milliseconds. Hardware was never the issue.
The second most common finding is N+1 query patterns in ORM-generated SQL. Most PHP frameworks (Laravel, Symfony) and Python frameworks (Django) have ORM methods that prevent N+1 — Laravel's with(), Django's select_related() — but they require the developer to explicitly invoke them. When these are missed, what appears to be a fast query in testing (10 records) becomes a slow query in production (10,000 records), because the number of database round-trips scales linearly with the record count.
A specific example from a client audit: an inventory management system was loading a warehouse dashboard in 14 seconds. The EXPLAIN plan showed a sequential scan on a 4.2 million row stock_movements table, filtering on warehouse_id and created_at. There was an index on warehouse_id alone, but the query always filtered on both columns together. Adding a composite index on (warehouse_id, created_at) and adding quantity, product_id, movement_type as INCLUDE columns to make it a covering index reduced the dashboard query from 14 seconds to under 120 milliseconds. The fix took 15 minutes to implement after the 2-hour diagnostic session to find it.
A third recurring observation: developers fear VACUUM and table statistics maintenance in PostgreSQL. Autovacuum handles routine cases, but tables with very high write throughput — order lines, event logs, audit tables — often have stale statistics that cause the query planner to choose poor execution plans. Running ANALYZE table_name after large data loads, and configuring autovacuum thresholds more aggressively on high-write tables, prevents planner regression without manual intervention.
If you are experiencing slow queries on a business-critical application and want a structured diagnosis, business process automation starts with understanding what your systems are doing — database audits are a key part of that diagnostic process.
Use this checklist as a systematic database performance audit for any MySQL, PostgreSQL or MSSQL instance. Work through the items in order — the items near the top of the list deliver the highest return for most applications.
pg_stat_user_indexes (PostgreSQL) or Query Store unused index reports (MSSQL) to identify indexes that have not been used in 30+ days. Drop them.The most common causes of slow database performance are, in order of frequency: missing or incorrect indexes causing full table scans on large tables; N+1 query patterns where application code makes hundreds of individual queries instead of one efficient JOIN; poorly written queries that prevent index usage (functions on indexed columns, leading wildcard LIKE searches); insufficient buffer pool or shared memory for the working data set; and connection limit exhaustion under load. Hardware limitations are a genuine cause only after these software-level issues have been addressed.
In MySQL, enable the slow query log with slow_query_log = ON and long_query_time = 1 in my.cnf, then analyse the log with mysqldumpslow or Percona's pt-query-digest. In PostgreSQL, enable the pg_stat_statements extension and query it with SELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. Both approaches identify the top consumers of database execution time so you tune the queries that actually matter.
Adding a composite covering index on the most-queried table's filtered columns is almost always the highest-impact single change. For a table of 1 million+ rows that lacks indexes on frequently filtered columns, the improvement can be 100–1,000x. Run EXPLAIN on your slowest queries, look for Seq Scan or type=ALL on large tables, and add the correct composite index. This change costs nothing and takes minutes to implement.
A database performance audit and optimisation project for a UK SME typically costs £1,500–£4,000 for a single database with clear performance problems, covering slow query analysis, index review and implementation, connection pooling configuration and server settings review. Complex multi-database environments, or projects requiring query rewrites across application code, cost £4,000–£12,000. Many performance problems are resolved in a focused 1–2 day engagement once the slow query analysis points to the root cause.
Connection pooling maintains a pre-established set of reusable database connections rather than opening a new connection for every query. Each new database connection takes 20–100ms to establish and consumes memory on the server. Under load, applications without connection pooling exhaust database connection limits and degrade sharply. You need connection pooling if your application runs more than 20–30 concurrent database-using threads. PgBouncer is the standard solution for PostgreSQL; most application frameworks (Laravel, Rails, Django) have built-in connection pool support for MySQL.
Tuning PostgreSQL for performance involves four areas in priority order: first, enable pg_stat_statements and identify your top slow queries; second, add composite and covering indexes for your most frequent query patterns and verify with EXPLAIN ANALYZE; third, configure shared_buffers to 25% of RAM, work_mem to 32–64MB, effective_cache_size to 75% of RAM, and random_page_cost to 1.1 if running on SSD; fourth, configure PgBouncer for connection pooling in transaction mode. These four steps deliver the majority of available PostgreSQL performance improvement on any workload.
OLTP (Online Transaction Processing) databases handle many short concurrent transactions — eCommerce orders, CRM updates, ERP transactions. Optimisation focuses on index coverage for point lookups, short transaction duration to minimise lock contention, and connection pooling for concurrency. OLAP (Online Analytical Processing) databases handle fewer but very large queries across millions of rows for reporting and business intelligence. Optimisation focuses on partitioning by date range, materialised views for pre-computed aggregates, columnstore indexes (MSSQL), and parallel query execution (PostgreSQL). Many businesses run both patterns on the same database, which requires careful balancing of competing tuning recommendations.
Consider hardware upgrades when: all queries are properly indexed and use efficient execution plans, connection pooling is configured and not saturated, caching is implemented for appropriate read workloads, server configuration settings are tuned for your hardware, and CPU or memory is still running at sustained high utilisation under normal load. If any of these conditions are not met, fix the software-level issues first. A hardware upgrade on an untuned database provides a temporary improvement that disappears as data volume grows, because the underlying inefficiency scales with data.
Database performance tuning is not a one-time project. Query patterns change as applications evolve, data volumes grow, and new features add new query patterns. A database that performs well today can degrade over 12–18 months without ongoing index review, query monitoring and server configuration adjustment as data volumes change. The 15-step checklist above provides a repeatable audit framework. Run it when you first identify a performance problem, after any significant data volume increase, and after any major application release that adds new database-intensive features.
The practical starting point for any database performance investigation is always the slow query log. Enable it, wait 24–48 hours, and sort by total execution time. The top 5 queries almost always account for 70–80% of total database execution time. Fix those five queries — typically through indexing and query rewriting — and the performance improvement will be visible immediately. Server configuration and connection pooling are valuable second-tier improvements; they rarely move the needle as dramatically as fixing the queries that are doing the most work.
Softomate Solutions provides database performance tuning consultancy for UK businesses. Based in Stanmore, London. Request a free database audit.
Written by the Softomate Solutions technical team, database and software specialists based in Stanmore, London.
More from the blog
Let us help
Talk to our London-based team about how we can build the AI software, automation, or bespoke development tailored to your needs.
Softomate Solutions Limited
Deen Dayal Yadav
Online
