MySQL & PostgreSQL Query Optimization Under Heavy Load: A Practical Guide.

What Happens When Databases Experience Heavy Load

In local development or with a small number of users, almost every SQL query runs in under 10 milliseconds. Even an unindexed table scan across 50,000 rows feels instant. But when your application handles thousands of concurrent requests per second, system behavior changes significantly.

A single unindexed query taking 250ms might not seem problematic on its own. But if 200 requests run it simultaneously, the database suddenly faces 50 seconds of CPU work. Connection pools become exhausted, threads wait on locks, memory usage spikes, and response times degrade significantly.

Addressing this usually does not require upgrading hardware immediately. In most cases, identifying and fixing a few critical bottlenecks stabilizes the system.

Step 1: Identify Slow Queries with Profiling Tools

Before changing any code, you need real data on which queries consume the most CPU cycles and disk I/O. Both MySQL and PostgreSQL provide built-in tools to track this.

In PostgreSQL: Use pg_stat_statements

pg_stat_statements records total execution time, call counts, and buffer reads across every query shape.

-- Find the top 5 queries taking the most total CPU time:
SELECT 
    round((total_exec_time / 1000)::numeric, 2) AS total_time_seconds,
    calls,
    round((mean_exec_time)::numeric, 2) AS avg_time_ms,
    rows,
    substr(query, 1, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

In MySQL: Turn on the Slow Query Log

Log queries that take longer than 500 milliseconds or do not use indexes:

# Enable slow query logging in MySQL configuration:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5            # Catch anything taking over 500ms
log_queries_not_using_indexes = 1

Step 2: Understand EXPLAIN & EXPLAIN ANALYZE

When you run a query, the database optimizer chooses an execution plan to retrieve data. Running EXPLAIN ANALYZE executes the query and shows the actual execution time and steps taken.

-- Run EXPLAIN ANALYZE on your suspect query:
EXPLAIN ANALYZE
SELECT id, email, created_at 
FROM users 
WHERE status = 'ACTIVE' AND country_code = 'IN';

-- BAD OUTPUT (Sequential Table Scan):
-- Seq Scan on users  (cost=0.00..18420.00 rows=48210 width=36) (actual time=0.045..248.120 rows=45120 loops=1)
-- Filter: ((status = 'ACTIVE') AND (country_code = 'IN'))
-- Rows Removed by Filter: 954880

Step 3: Understanding Composite Indexes (The Leftmost Prefix Rule)

Creating single-column indexes on every column (one on status, one on country_code, and one on created_at) is often inefficient. Under heavy load, the database may not be able to combine multiple indexes efficiently for the same query.

Rule of Thumb for Composite Index Column Order:
Place exact equality filters first (WHERE status = ...), then range filters (WHERE created_at > ...), and finally sorting fields (ORDER BY ...).

-- Create a composite index in the exact order of filtering and sorting:
CREATE INDEX idx_users_country_status_created 
ON users (country_code, status, created_at DESC);

-- Now this query executes in < 2ms using an Index Scan:
SELECT id, email, created_at 
FROM users 
WHERE country_code = 'IN' 
  AND status = 'ACTIVE' 
ORDER BY created_at DESC 
LIMIT 20;

PostgreSQL Partial Indexes

If 90% of rows have status = 'PROCESSED' and queries only check for pending jobs, you do not need to index the processed rows. Index only the rows you search for:

-- Index only rows that actually need active processing:
CREATE INDEX idx_orders_pending 
ON orders (created_at) 
WHERE status = 'PENDING';
-- Keeps the index significantly smaller in memory and reduces INSERT/UPDATE overhead:

The 4 Common Anti-Patterns That Hurt Database Throughput

-- ❌ BAD: Database must evaluate YEAR() on every single row:
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- ✅ GOOD: Range query uses the B-Tree index directly:
SELECT id, amount, status FROM orders 
WHERE created_at >= '2026-01-01 00:00:00' 
  AND created_at <  '2027-01-01 00:00:00';

-- ❌ BAD: Discards 100,000 rows on every page request:
SELECT * FROM audit_logs ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- ✅ GOOD: Keyset Pagination (cursor-based) avoids scanning and discarding previous rows:
SELECT id, message, created_at 
FROM audit_logs 
WHERE id < 948210  -- last seen ID from previous page
ORDER BY id DESC 
LIMIT 20;

-- ❌ BAD: 1 query for orders + N queries for each customer:
SELECT * FROM orders WHERE status = 'PENDING';
-- (followed by 100 individual: SELECT * FROM customers WHERE id = ?)

-- ✅ GOOD: 1 single query returns all required relations:
SELECT o.id, o.amount, o.status, c.id, c.name, c.email 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'PENDING';

Connection Pool Tuning (Why More Connections Is Often Worse)

A common mistake is increasing the connection pool size to 200 or 500 when queries slow down. On a database server with 8 CPU cores, running 200 concurrent active queries means 200 threads competing for 8 cores. The CPU spends significant time context switching instead of executing queries.

PostgreSQL / HikariCP Pool Sizing Formula:
connections = ((CPU_cores * 2) + effective_spindle_count)
On an 8-core server with modern SSDs, a connection pool of 16 to 25 connections will often handle more throughput and have lower latency than a pool of 200.

# Recommended HikariCP configuration for high-concurrency Spring Boot:
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.connection-timeout=20000   # Fail fast if pool exhausted (20s)
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000