Databases
Advanced 15 min read Databases

Database Performance Tuning

Spend I/O, RAM, and locks where they pay

Performance tuning is a budget: work_mem, buffers, connection counts, and deadlocks trade where time is spent. Systematic profiling — not folklore — finds the biggest bottleneck first.

work_membuffer poolconnection pooldeadlockprofilebottleneck
Prerequisites: Query Planning and EXPLAIN Indexing
Before we start

What you will learn

  • Rank a slow-database symptom against the classic bottleneck set
  • Tune work_mem, buffer caches, and connection pooling deliberately
  • Read a profile to decide where to spend next
Introduction

What is this?

A database under load degrades for a few recurring reasons: sorts/hashes spilling to disk (work_mem), cold buffer cache (cache misses), connection churn and lock waits, checkpoint storms, and corkscrewing indexes. Tuning starts with measuring which of those is real, then adjusting the parameter that owns it.

Motivation

Why do you need it?

  • Bigger hardware is plan B; the same budget yields 50-90% latency to a correctly-tuned config.
  • Concurrency and deadlocks are data-pipeline-specific: batch loads bring their own lock profile.
  • Profiling first prevents the classic 'bumped work_mem, nothing changed' waste.
Think about it

Mental model

Imagine

A kitchen with too many cooks, one fridge, and one stove

Think of

A database under load

Adding another stove (CPU) doesn't help if the fridge (cache), sink (I/O), or the rulebook (locks) is the actual bottleneck. Watch the kitchen for the queue, not the chef.

Mechanics

How it works

  • work_mem sets per-operator sort/hash memory; spills write temp files and dominate latency at scale.
  • The shared buffer pool caches hot pages; a low hit ratio means I/O per query, not RAM.
  • Connection counts multiply planner state and locks; a pool sizes to keep active concurrency near core count.
  • Lock waits (row/page/table locks, deadlocks) turn a batch job into a serialized blob.
  • Checkpoint and WAL tuning smooth write bursts into a database's steady state.

A latency budget under load.

Query in
Read path
Compute
Lock/wait
Latency out
Reference

Key commands

SET work_mem = '128MB';

Give this session's sorts/hashes more RAM.

pg_stat_activity / SHOW max_connections

Inspect active queries, states, and connection budget.

SELECT count(*) FROM pg_locks WHERE NOT granted;

Spot queued lock waits faster than any book.

EXPLAIN (ANALYZE, BUFFERS) ...

The universal measurement before and after a tweak.

Hands-on

Examples

A spilled-sort fix tells the story

A spilled-sort fix tells the story
-- Run from a slow report query:
EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id, sum(amount)
FROM orders GROUP BY customer_id;
 
-- Check: Sort Method: external merge  Disk: 2244kB
-- fix: raise work_mem for this workload
SET work_mem = '128MB';  -- or: prepared for session/transaction only

The EXPLAIN output states spill explicitly; the boundary between RAM and disk is the first thing to chase.

Real world

In production

  • A nightly ETL spins big GROUP BYs; a session-scoped work_mem change cut its runtime by half.
  • A connection pool of 12-40 (matched to cores and query shape) replaced 'grab a connection per request' chaos.
  • On-call reads pg_locks when a 2am batch appears to hang, and kills one locked-backend safely.
Guidelines

Best practices

  • Measure first: a profile or EXPLAIN decides which knob to touch; never random-tune.
  • Keep work_mem high on heavy batch sessions, not globally, to avoid RAM oversubscription.
  • Align the pool size and isolation of batch vs interactive traffic.
  • Log every shot: before/after latency, throughput, and the config value.
Watch out

Common mistakes

  • Raising work_mem globally to '500MB' and running 200 sessions — the sum exceeds RAM and thrashes.
  • Buying a bigger machine when pg_locks shows lock waits and indexes prove the code's the problem.
  • Tuning one worker while a checkpoint storm flushes your entire cache once an hour.
When things break

Debugging tips

  • Pull the top SQL by total time first; the hot query is where almost all gains live.
  • Check pg_stat_bgwriter checkpoints; checkpoint_timed spikes align suspiciously with latency cliffs.
  • Use pg_stat_lock waits plus slowlog to distinguish waits from spins.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

EXPLAIN shows 'Sort Method: external merge Disk: 2GB'. What is the most direct fix?

2

More concurrent connections always speed up a Postgres workload.

Go deeper

Official documentation

PostgreSQL performance tuning
docs
MySQL performance best practices
docs
SQLite tuning
docs