What is it? #
The database is usually the hardest component to scale and the most common source of slow responses, which makes it the most valuable thing to monitor closely.
Five signals cover most problems: slow queries, connection usage, cache hit ratio, lock contention and replication lag.
Slow queries are where nearly all database performance work starts. One unindexed query running frequently can dominate load entirely.
Connection exhaustion is the failure that arrives suddenly. Everything works until the pool is full, and then every request fails at once.
Think of it like this #
A kitchen with a limited number of hobs. Most dishes are quick, one takes forty minutes and occupies a hob the whole time.
You can add hobs, or you can find out why that dish takes forty minutes. The second is usually cheaper.
Simple example #
Response times climb during peak hours. Query statistics show one endpoint running a query 4,000 times an hour at 180ms each — an N+1 pattern that one index and one eager load remove entirely.
Code #
-- The single most useful query: what is consuming the time?
SELECT
substring(query, 1, 80) AS query,
calls,
round(mean_exec_time::numeric, 1) AS avg_ms,
round(total_exec_time::numeric / 1000, 1) AS total_sec,
rows / GREATEST(calls, 1) AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
-- Sort by TOTAL time, not average. A 20ms query called a million times
-- costs far more than a 2-second query called twice.-- Connections: the sudden failure
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- active 12 doing work
-- idle 40 connected, doing nothing
-- idle in transaction 8 ← dangerous: holding locks, blocking others
SHOW max_connections;
-- Alert at 80% of the limit. Beyond it, every new request fails.
-- Find long-running transactions
SELECT pid, now() - xact_start AS duration, state, substring(query, 1, 60)
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - xact_start > interval '30 seconds'
ORDER BY duration DESC;-- Cache hit ratio: is the working set in memory?
SELECT round(100.0 * sum(heap_blks_hit) /
GREATEST(sum(heap_blks_hit) + sum(heap_blks_read), 1), 2) AS hit_pct
FROM pg_statio_user_tables;
-- Below about 95% means the database is reading from disk frequently.
-- More shared_buffers, or less data, is usually the answer.
-- Locks: who is blocking whom?
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
substring(blocked.query, 1, 50) AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
-- Replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS lag;What to alert on
connections above 80% of max minutes before total failure
replication lag above 30s stale reads, failover risk
slow query count rising a regression, or data growth
cache hit ratio below 95% the working set no longer fits
deadlocks increasing a concurrency bug
disk usage on the data volume a full disk stops the database entirely
long-running transactions they hold locks and block VACUUMHow it works #
pg_stat_statements aggregates every query by shape, so parameterised queries are grouped together. Sorting by total time rather than average time is the key insight: frequency matters as much as duration.
The connection state breakdown separates three situations. Active connections are working; idle connections are merely holding a slot; idle in transaction connections are holding locks while doing nothing, which blocks other work and prevents cleanup.
That last state usually indicates an application bug — a transaction opened and not committed, often because an external call was made inside it.
Connection exhaustion is abrupt rather than gradual. Below the limit everything works; at the limit every new request fails immediately. Alerting at 80% converts a sudden outage into a warning.
Cache hit ratio measures whether the working set fits in memory. A low ratio means the database is reading from disk repeatedly, which shows up as general slowness rather than as an obvious database alert.
Lock queries answer "why is this query hanging" directly, showing which session is blocking which.
Replication lag matters both for read correctness and for failover: a replica far behind will lose data if promoted.
Long-running transactions deserve their own alert. In PostgreSQL they prevent VACUUM from cleaning up old row versions, which causes table bloat that persists after the transaction ends.
Real-world use #
Most application slowness traces back to the database, and most of that traces back to a small number of queries. Query statistics identify them in minutes.
The N+1 pattern is the most common cause: a loop issuing one query per item. It looks harmless in development with ten rows and dominates production with ten thousand.
Connection pool sizing is a recurring incident. Application workers multiplied by pool size can exceed the database limit during a scaling event, which is exactly when you need it most. A connection pooler such as PgBouncer is the standard answer.
Managed databases provide much of this monitoring built in, including query insights and automatic alerts, which is a strong argument for them.
The practical routine is short: review the top queries by total time weekly, and watch connections, replication lag and cache hit ratio continuously.
Common mistakes #
- Sorting queries by average time and missing the frequent cheap ones.
- No alert on connection usage, so exhaustion arrives as a sudden outage.
- Ignoring
idle in transactionsessions holding locks. - Pool sizes that multiply across workers and exceed the database limit.
- Treating a low cache hit ratio as normal rather than as a memory problem.
Practice #
Enable query statistics and list the ten queries consuming the most total time. Check connection states, the cache hit ratio and any transaction open longer than thirty seconds. Then pick the worst query and determine whether an index would fix it.