5 Surprising Truths About Database Performance

At 2:47 AM on a Tuesday, a senior DBA at a fintech company was staring at a PostgreSQL instance consuming 94% CPU. The slow query log was clean. EXPLAIN ANALYZE showed index scans everywhere. The connection pool sat at 40 out of a possible 400. Every instinct said the database was healthy — and yet, the application was on its knees, serving 12-second response times to users trying to initiate fund transfers. The problem, once found, came from none of the usual suspects. It came from five deeply held assumptions about database performance that most engineers absorb without ever questioning them.

This post dismantles those assumptions one by one. Each counterintuitive truth below has burned production systems run by experienced engineers — people who knew their query planner, their index types, and their replication topology. The mental models here will change how you read a flame graph, size a connection pool, and decide whether to add that next index.

Truth 1: Over-Indexing Hurts More Than Under-Indexing

The reflex is understandable. A slow query shows up in the slow log. You look at the execution plan, see a sequential scan, and create an index. Latency drops. The win feels clean. But repeat this pattern across a table with 30 columns, 15 queries, and four application teams adding indexes independently over two years, and you end up with a table that has more indexes than it has columns — and a write path that has quietly become catastrophic.

Why Indexes Cost on Every Write

Every index you create on a table is a separate B-tree (or hash, GiST, etc.) that PostgreSQL or MySQL must keep synchronized with the heap. A single INSERT into a table with 12 indexes does not write once — it writes 13 times: once to the heap, and once per index. A UPDATE that touches an indexed column triggers both an index delete and an index insert. The cost is not constant: indexes on high-cardinality columns with random value distributions (UUIDs, hashes) cause frequent B-tree page splits and increased WAL volume.

-- See how many indexes exist on your highest-traffic tables (PostgreSQL)
SELECT
    t.relname                                   AS table_name,
    COUNT(i.indexrelid)                         AS index_count,
    pg_size_pretty(pg_relation_size(t.oid))     AS table_size,
    pg_size_pretty(
        SUM(pg_relation_size(i.indexrelid))
    )                                           AS total_index_size
FROM pg_class t
JOIN pg_index i ON i.indrelid = t.oid
WHERE t.relkind = 'r'
  AND t.relnamespace = 'public'::regnamespace
GROUP BY t.relname, t.oid
ORDER BY COUNT(i.indexrelid) DESC
LIMIT 20;

Index bloat compounds the problem. PostgreSQL's MVCC model means deleted index entries are not immediately reclaimed. VACUUM must traverse every index on a table to remove dead tuples, and with many indexes, autovacuum takes longer, runs more frequently, and competes with application workload for I/O.

Finding Dead Indexes

-- Indexes that have never been used since last stats reset (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
      SELECT conindid FROM pg_constraint
  )
ORDER BY pg_relation_size(indexrelid) DESC;

The correct mental model: treat each index as a contract. It costs a fixed tax on every write in exchange for faster reads on specific query shapes. Before creating a new index, ask whether the query pattern it serves is in the hot path, whether an existing compound index can be extended to cover it, and whether the write-amplification cost is justified by the read gain. Audit existing indexes quarterly. Drop unused ones without ceremony.

Truth 2: The N+1 Query Problem Is Often the Real Bottleneck

You profile your application. The slowest individual query runs in 4ms. The database CPU is at 25%. Nothing in the slow query log looks alarming. And yet, a single API endpoint takes 800ms to respond. This is the N+1 pattern operating at scale: for every row returned by one query, the ORM silently fires another query, and another, and another.

How ORMs Generate N+1 Patterns

Consider a Django or ActiveRecord endpoint that lists 50 orders and their associated customer names. The ORM fetches the 50 orders in one query — that is the "1". Then, for each order, it fetches the customer record separately because you accessed order.customer.name in the template without explicitly joining. That is the "N". The result is 51 database round-trips where 1 would suffice.

# Django: This innocent-looking loop triggers 1 + N queries
orders = Order.objects.filter(status='pending')  # 1 query
for order in orders:
    print(order.customer.name)  # 1 query per order = N additional queries

# Fix: use select_related() to JOIN at the ORM level
orders = Order.objects.filter(status='pending').select_related('customer')
for order in orders:
    print(order.customer.name)  # 0 additional queries — data already fetched

-- The equivalent SQL the fixed version generates (one round-trip):
SELECT orders.*, customers.*
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE orders.status = 'pending';

The reason this evades slow query detection is that each individual query is fast. A 4ms query fired 200 times per request is 800ms of database time — plus 200 round-trip overheads at ~0.5ms each on a local network. In production with thousands of concurrent requests, the connection pool drains, queue depth rises, and the database appears busy while doing almost no useful work per unit of compute.

The correct mental model: query count matters as much as query duration. An application that fires 10 queries at 1ms each outperforms one that fires 500 queries at 0.5ms each, because the former consumes fewer connections, less pool capacity, and less CPU on both the application and database side. Measure queries-per-request, not just p99 latency of individual queries.

Truth 3: Your Connection Pool Should Be Smaller Than You Think

The intuition feels sound: more connections mean more parallelism, more parallelism means more throughput. So teams set max_connections = 1000 on PostgreSQL, configure their pool to match, and watch their application fall over under load as database CPU saturates at 100% while connection wait times balloon to seconds.

Little's Law Applied to Databases

Little's Law from queuing theory states: L = λW, where L is the number of items in the system, λ is the arrival rate, and W is the average time an item spends in the system. For a database connection pool, L is the number of active connections needed, λ is query requests per second, and W is average query duration.

If your application handles 500 queries per second and each query takes 10ms on average, you need: 500 × 0.010 = 5 connections to sustain that throughput. Not 50. Not 500. Five. The rest of your "available" connections sit idle, consuming memory (each PostgreSQL backend uses ~5–10 MB of RAM), and adding overhead to every connection-state broadcast during checkpoints and autovacuum.

# PgBouncer configuration: a sane starting point for a 16-core PostgreSQL host
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode         = transaction
max_client_conn   = 1000     # application threads can connect freely
default_pool_size = 25       # actual backend connections per database/user pair
min_pool_size     = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout  = 600

The HikariCP documentation (the most widely used Java connection pool) is direct about this: for a 4-core machine running PostgreSQL, the recommended pool size is ((4 * 2) + 1) = 9 connections — a formula derived from disk I/O wait characteristics, not from maximizing connection count.

The correct mental model: the connection pool is a throttle, not an accelerator. Its job is to match the concurrency level that maximizes database CPU utilization without causing scheduler contention. Start with (2 × num_cores) + num_spindle_disks as a baseline, measure, and adjust upward only when you can confirm that active connections — not idle ones — are consistently hitting the ceiling.

Truth 4: Reading from Replicas Can Be Slower

Directing read traffic to replicas is standard HA architecture. The assumption baked into this pattern is that replicas are functionally identical to the primary, just with less write load. In practice, replicas diverge from primaries in ways that make reads slower, wrong, or both.

Replication Lag Means Stale Data

Streaming replication in PostgreSQL and MySQL is asynchronous by default. The replica applies WAL or binlog entries after they are committed on the primary. During high write bursts, replica lag can reach seconds or minutes. A user who writes a record and immediately reads it back may get a 404 or an empty result set — not because the query is wrong, but because the read hit a replica that has not yet received the write.

-- Check replication lag on PostgreSQL primary (shows lag per connected standby)
SELECT
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn)  AS replication_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;

Index Divergence Between Primary and Replica

Replicas replay all DDL from the primary, so their schema is identical — unless you have ever created an index CONCURRENTLY on the replica only, applied a migration that failed mid-way on the replica, or used logical replication with filtered table sets. A query that uses an index on the primary may do a sequential scan on the replica if the replica's index is missing, partially built, or in an invalid state after a failed operation.

-- Compare index validity between primary and replica (run on each host)
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef,
    pg_get_indexdef(indexrelid) AS definition
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
-- Diff the output between primary and replica to detect divergence

The correct mental model: a replica is a lagging, potentially diverged copy of your primary. It is appropriate for analytics queries, reporting, and read workloads where slight staleness is acceptable and explicitly acknowledged. It is not a transparent substitute for primary reads in transactional flows. Always measure replica lag in your monitoring, and always define a staleness budget per workload type before routing traffic.

Truth 5: EXPLAIN Does Not Show What Actually Happened

A DBA runs EXPLAIN on a query that is slow in production. The plan looks optimal: index scans, low estimated row counts, small hash joins. They conclude the problem must be somewhere else. But EXPLAIN — without ANALYZE — never executes the query. It shows what the planner intends to do, based on statistics that may be weeks out of date.

The Gap Between Plan and Reality

The PostgreSQL query planner estimates row counts from pg_statistic, which is populated by ANALYZE. If autovacuum has not run recently on a fast-growing table, the planner's row-count estimates can be off by orders of magnitude. A table with 10 million rows that the planner thinks has 50,000 rows will get a nested-loop join plan instead of a hash join — and will execute 10 million hash probes instead of one batch hash build.

-- EXPLAIN alone: planner's intent, zero execution, potentially stale estimates
EXPLAIN
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- EXPLAIN ANALYZE: actually executes, shows actual rows vs. estimated rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

The BUFFERS option reveals cache hit ratio at the node level. A plan that shows 2,000 buffer hits and 50,000 buffer reads (from disk) for what appears to be a simple index scan tells you the working set does not fit in shared_buffers — a configuration problem, not a query problem.

What EXPLAIN ANALYZE Still Cannot Tell You

Even EXPLAIN ANALYZE has blind spots. It shows execution time for a single run in isolation, not under concurrent load. Lock waits do not appear in the plan output — a query that spends 900ms waiting on a row lock shows 900ms total time with no indication why. For production diagnosis, use pg_stat_activity alongside EXPLAIN ANALYZE.

-- Find queries currently waiting on locks in production (PostgreSQL)
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND wait_event IS NOT NULL
ORDER BY duration DESC;

The correct mental model: EXPLAIN is a first-pass hypothesis. EXPLAIN (ANALYZE, BUFFERS) is the evidence. pg_stat_activity and pg_stat_statements are the production witnesses. Use all three before declaring a root cause.

Working with JusDB on Database Performance

Performance problems at the database layer are rarely solved by a single query fix or a single configuration change. They emerge from the interaction between schema design, connection management, query patterns, and operational discipline — the kind of holistic view that takes years of production incidents to develop. JusDB embeds senior DBAs directly into engineering teams, providing on-call diagnosis, query review, connection pool tuning, replica topology audits, and proactive index governance. We catch the over-indexed table before it degrades your write path, and the N+1 pattern before it hits Black Friday traffic.

Explore JusDB database performance services →