The Database Handbook: Every Failure Scenario, How to Detect It, How to Fix It

Databases fail in a small number of recurring ways. The same twenty incidents show up at every company, from a two-person startup to a bank. This handbook collects them in one place. For each one you get the symptom you will see, how to confirm it, how to stop the bleeding, and how to stop it coming back. Examples use PostgreSQL, but the ideas carry over to MySQL and most other relational databases, and many apply to NoSQL stores too.

The failure map

When something is wrong, work through it in this order. What changed? (a deploy, a migration, a traffic spike, a new cron job). Is it every query or a few? (few means a plan or lock problem, all means a resource or topology problem). Reads or writes? Those three answers usually get you to the right section below within minutes.

Indexing: when, which, and when not

An index is a separate, sorted structure that maps column values to row locations. It trades write cost and disk space for fast reads. Most performance incidents are indexing incidents.

Index types and where to use them

Composite indexes and the leftmost-prefix rule

Column order matters. Put equality columns first, then range or sort columns. For WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC, the right index is (tenant_id, status, created_at DESC). That one index handles the filter and the sort, so there is no separate sort step.

Scenario guide

When indexes hurt

• Write amplification: every INSERT/UPDATE updates every index. Ten indexes means about ten extra writes per row.
• Low selectivity: an index on a boolean is_active that is 95% true gets ignored. Use a partial index instead.
• Functions on the column: WHERE date(created_at) = … or WHERE lower(email) = … skip a plain index. Rewrite the query as a range, or add an expression index.
• Leading wildcards: LIKE '%foo' cannot use a B-tree. Use trigram (pg_trgm) GIN or a search engine. See inverted indexing.
• Type mismatch: comparing a varchar column to an integer parameter forces a cast and a full scan.
• Unused and duplicate indexes: they cost writes and RAM and give nothing back.

-- Indexes never scanned since stats reset
SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

-- Tables with many sequential scans (candidate missing indexes)
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 15;

-- Always build in production without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC);

1. Slow queries and bad plans

Symptom: p99 latency climbs on one endpoint. Everything else looks fine.

Detect: use pg_stat_statements sorted by total time, the slow query log (log_min_duration_statement = 500ms), and EXPLAIN (ANALYZE, BUFFERS).

-- Slow at depth: scans and discards 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

-- Constant time at any depth (needs index on (created_at, id))
SELECT * FROM posts
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;

2. CPU pinned at 100%

Usually this is unnecessary work, not missing capacity. A missing index, stale statistics, a broken cache in front of the database, a connection storm, or a stray analytics job. The full triage is in Your database is at 100% CPU. The short version: check whether traffic or work-per-request went up, sort pg_stat_statements by total time, kill the runaway query, and set a statement_timeout.

3. Connection exhaustion

Symptom: FATAL: sorry, too many clients already or remaining connection slots are reserved. App threads hang waiting for a pool connection, and timeouts cascade.

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- Many 'idle in transaction' = app opened a txn and did slow work (HTTP call!) inside it
SELECT pid, now() - xact_start AS age, left(query, 80)
FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;

• Put PgBouncer (transaction mode) or RDS Proxy in front of the database.
• Size pools on purpose. Total = pods × pool size, and it must stay below max_connections. A useful starting point per DB: about cores × 2 active connections (see Little's Law).
• Set idle_in_transaction_session_timeout and never make network calls inside a transaction.
• Make sure connections are returned to the pool on error paths (finally / using / defer).
• Serverless functions: always go through a pooler, because each cold start opens a new connection.

4. Lock contention and deadlocks

Symptom: queries that are normally fast suddenly wait for seconds. Throughput drops while CPU stays low. Logs show deadlock detected.

SELECT blocked.pid AS blocked_pid, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query,
       now() - blocking.xact_start AS blocking_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

5. Transactions and isolation anomalies

ACID guarantees only hold for the isolation level you actually run at. Most databases default to Read Committed, and that allows several bugs that look like "the database lost my data".

-- 1. Let the database do the arithmetic atomically
UPDATE accounts SET balance = balance - 10 WHERE id = 1 AND balance >= 10;

-- 2. Pessimistic: lock the row
BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; ... UPDATE ...; COMMIT;

-- 3. Optimistic: version column, retry if 0 rows updated
UPDATE accounts SET balance = $new, version = version + 1
WHERE id = 1 AND version = $read_version;

At Serializable, the database aborts conflicting transactions with a serialization failure, so your code must retry. For duplicate writes across retries and networks, add idempotency keys with a unique constraint. See the duplicate payments bug.

6. Replication lag and stale reads

Symptom: "I saved it but it's gone". Counts disagree between pages. Replicas fall minutes behind during batch jobs.

-- On primary
SELECT client_addr, state, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;
-- On replica
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

• Read-your-writes: send a user's reads to the primary for a few seconds after they write, or track the commit LSN and only read from replicas that have caught up to it.
• Remove lagging replicas from the load balancer when lag goes over a threshold.
• Causes of lag: big batch writes, long queries on the replica that conflict with replay, a slow network or disk on the replica. Break batch jobs into chunks.
• Never read money or inventory from a replica when making a decision.

7. Primary failure and split brain

When the primary dies, a replica has to be promoted. The dangerous case is split brain. The old primary is not dead, only cut off from the others. It keeps accepting writes, and now two nodes disagree.

• Use a consensus-based manager (Patroni + etcd, managed RDS/Aurora Multi-AZ, Cloud SQL HA). Do not use a hand-written health-check script.
• Fence the old primary: STONITH, revoke its VIP, or have it demote itself when it loses quorum.
• Asynchronous replication can lose the last few seconds of commits on failover (RPO > 0). Use synchronous replication to at least one standby for data you cannot lose.
• Clients should connect through a stable endpoint and reconnect with backoff, not to a hard-coded IP.
• Test failover regularly. One you have never tested will fail when you need it.

8. Disk full, WAL growth and bloat

When the disk fills up the database stops accepting writes, and Postgres may shut down. This is one of the most common outages and also one of the easiest to prevent.

9. Memory, cache misses and I/O

Databases are fast when the working set fits in RAM. When it stops fitting, latency jumps sharply rather than gradually.

SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0) AS hit_pct
FROM pg_stat_database;

• Rough Postgres settings: shared_buffers ≈ 25% of RAM, effective_cache_size ≈ 50–75%.
• work_mem applies per sort/hash node per connection, so 200 connections × 64MB can trigger an OOM kill.
• Watch disk IOPS and throughput limits on cloud volumes. Burst credits run out quietly.
• Shrink the working set: archive cold data, partition, add covering indexes, and drop unused indexes.

10. Hot rows, hot partitions and sharding

• Hot row: a like counter or inventory row updated thousands of times a second. Fix it with sharded counters (N rows summed on read), batching writes through a queue, or atomic operations in Redis.
• Hot partition from the key choice: sharding by date sends all of today's writes to one shard. Shard by a high-cardinality key (user_id, tenant_id) or a hash of it.
• Celebrity / large tenant: give the biggest tenants their own shards, or split their data further with a salted sub-key.
• Cross-shard queries: pick the shard key so the hot queries stay on one shard, and keep global views in a separate read model (CQRS).
• Rebalancing: use consistent hashing or many small virtual shards so you can move data without rehashing everything.

Sharding should be the last step. Before it, try indexes, read replicas, caching, partitioning and a bigger machine.

11. Schema migrations gone wrong

Run migrations with lock_timeout, take a backup or snapshot first, and make sure the old version of the application keeps working against the new schema.

12. Cache failures that hit the database

• Stampede: a hot key expires and 10,000 requests rebuild it at once. Fix with request coalescing (single-flight), locks, or early probabilistic refresh.
• Synchronised expiry: everything was cached at deploy time with the same TTL, so it all expires together. Add jitter to TTLs.
• Cache penetration: repeated lookups for keys that do not exist. Cache the negative result, or use a Bloom filter.
• Cold restart: warm the cache before sending traffic.

More in Your Redis cache suddenly became useless and the cache-aside pattern.

13. Data loss, corruption and backups

14. Application-side anti-patterns

Related: circuit breaker, outbox pattern, SQL injection (security failures are database failures too).

What to monitor

Cheat sheet

1. Most database incidents are caused by the application or a query, not by hardware.
2. Index for your access patterns: equality columns first, then range or sort columns. Remove indexes you do not use.
3. Keep transactions short, set timeouts everywhere, and lock rows in a consistent order.
4. Replicas give you read scale and availability, but their reads can be stale. Plan for read-your-writes.
5. Monitor disk, WAL, vacuum and XID age, because they fail without warning.
6. Only backups you have restored can be trusted.