Practice And Capstone
Performance Tuning in Practice
Never change a setting, add an index, or rewrite
JrCodex·8 min read
Jr Codex DBMS Notes
Level: Advanced Prerequisites: Module 9, Chapter 4 Time to complete: ~20 minutes
Table of Contents
- Measure Before Touching Anything
- Finding the Real Problem
- The Layered Checklist
- Four Worked Symptoms
- Load Testing Honestly
- What Not to Tune
- Summary & Next Steps
1. Measure Before Touching Anything
The Rule
─────────────────────────────────────────
Never change a setting, add an index, or rewrite
a query without a MEASUREMENT that says which one
matters.
Every module in this curriculum offered
techniques. Applying them without measurement
means guessing which of forty possible causes you
have.
─────────────────────────────────────────
-- Enable the two extensions worth having on every database.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- log_min_duration_statement = 1000 -- log anything over 1 second
-- track_io_timing = on -- so EXPLAIN reports real I/O time-- The first query to run on any slow system.
SELECT calls,
ROUND(total_exec_time::numeric) AS total_ms,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
ROUND(100 * total_exec_time / SUM(total_exec_time) OVER (), 1) AS pct_of_total,
LEFT(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;Sort by TOTAL, Not Average
─────────────────────────────────────────
Module 5, Chapter 4 made this point and it is
worth repeating because it is the single most
common analysis error.
A 20ms query run 2,000 times per second costs
more than a 5-second query run hourly. The second
one is what people optimise; the first is what is
hurting them.
─────────────────────────────────────────
2. Finding the Real Problem
Four Questions, In Order
─────────────────────────────────────────
1. IS IT THE DATABASE AT ALL?
Check application time versus database time.
Often the answer is N+1 queries, or slow
serialisation, or a network hop — and no
amount of database tuning helps.
2. IS IT ONE QUERY OR THE WHOLE SYSTEM?
One query ──► EXPLAIN it (Module 6, Ch.4)
Everything ──► resources, locks, or
configuration
3. IS IT CPU, I/O, MEMORY OR LOCKS?
Four very different fixes.
4. IS IT NEW, OR HAS IT ALWAYS BEEN SLOW?
New ──► something CHANGED: data volume, a
deploy, statistics going stale
(Module 6, Chapter 3)
─────────────────────────────────────────
-- Question 3, answered. What are sessions actually waiting on?
SELECT wait_event_type, wait_event, COUNT(*), LEFT(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2, 4
ORDER BY COUNT(*) DESC;Reading Wait Events
─────────────────────────────────────────
IO / DataFileRead reading pages from disk
──► indexes, or buffer pool
too small (Module 5)
Lock / transactionid waiting on another
transaction
──► contention. Module 7.
LWLock internal contention
──► often too many
connections
CPU (no wait event) genuinely computing
──► query rewrite, or a
missing index causing a
scan
This one query usually narrows forty possible
causes to one family.
─────────────────────────────────────────
3. The Layered Checklist
Work outward. Each layer is cheaper to change than the next.
LAYER 1 — THE QUERY (minutes, no risk)
─────────────────────────────────────────
□ EXPLAIN (ANALYZE, BUFFERS) — find the first
estimate/actual divergence (Module 6, Ch.4)
□ Is the WHERE clause sargable? (Module 5, Ch.4)
□ Is it selecting columns nobody uses?
□ Is there an accidental cross join, or a
fan-out inflating aggregates? (Module 3, Ch.3)
□ Could a correlated subquery be a window
function? (Module 3, Ch.5)
LAYER 2 — INDEXES AND STATISTICS (hours, low risk)
─────────────────────────────────────────
□ ANALYZE the table — stale statistics first,
always
□ Is there an index for this predicate and this
ORDER BY?
□ Would a COVERING index remove the heap fetch?
□ Would a PARTIAL index fit a skewed filter?
□ Are correlated columns causing bad estimates?
──► CREATE STATISTICS
□ Are foreign keys indexed? (Module 5, Ch.4)
LAYER 3 — SCHEMA (days, medium risk)
─────────────────────────────────────────
□ Are the types right? NUMERIC for money,
smallest integer that fits
□ Are rows wider than they need to be?
(Module 5, Ch.1)
□ Should a large rarely-read column move to its
own table?
□ Would partitioning help this large table?
□ Is a deliberate, drift-checked denormalisation
justified? (Module 4, Ch.5)
LAYER 4 — CONFIGURATION (hours, needs testing)
─────────────────────────────────────────
□ shared_buffers sized for the working set
(Module 5, Ch.5)
□ work_mem raised enough to stop spills
(Module 6, Ch.1)
□ random_page_cost lowered for SSDs
(Module 6, Ch.3)
□ A connection POOLER in front — PgBouncer
LAYER 5 — ARCHITECTURE (weeks or months)
─────────────────────────────────────────
□ Read replicas (Module 8, Ch.3)
□ Caching layer
□ Archive cold data
□ Scale the machine up
□ Only then: a different database (Module 9)
─────────────────────────────────────────
4. Four Worked Symptoms
SYMPTOM: one query is slow, others are fine
─────────────────────────────────────────
──► EXPLAIN ANALYZE it. Find the first
estimate/actual divergence. Layer 1-2.
Most common causes, in order:
missing index · non-sargable predicate ·
stale statistics · fan-out
SYMPTOM: everything is slow, and it got worse
gradually
─────────────────────────────────────────
──► The WORKING SET outgrew the buffer pool
(Module 5, Chapter 5). This is the cliff, not
a curve.
Confirm with the cache hit rate. Fix by shrinking
the working set (archive, partition, narrower
rows) or by adding RAM.
SYMPTOM: slow only at peak, fine otherwise
─────────────────────────────────────────
──► CONTENTION, not query cost.
Check pg_stat_activity for Lock waits, and
pg_locks for blockers. Usually: long
transactions, an unindexed UPDATE over-locking
(Module 7, Chapter 3), or a hot row.
SYMPTOM: slow after a deploy, with no query change
─────────────────────────────────────────
──► A PLAN FLIP. The data or statistics changed
enough for the optimiser to choose
differently (Module 6, Chapter 3).
Compare EXPLAIN output before and after. Usually
fixed by ANALYZE, extended statistics, or a
raised statistics target.
─────────────────────────────────────────
-- Who is blocking whom, right now.
SELECT blocked.pid AS blocked_pid, LEFT(blocked.query, 50) AS blocked_query,
blocking.pid AS blocking_pid, LEFT(blocking.query, 50) AS blocking_query,
age(clock_timestamp(), blocking.xact_start) AS blocker_age
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;5. Load Testing Honestly
What Makes a Test Meaningless
─────────────────────────────────────────
✗ 1,000 rows when production has 50 million —
every plan differs (Module 6, Chapter 3)
✗ uniformly distributed synthetic data when
production is skewed
✗ one query at a time — no lock contention
(Module 7)
✗ a warm cache after the first run, so you
measure memory not disk
✗ testing on a laptop for a workload that will
run on different hardware
─────────────────────────────────────────
What Makes It Useful
─────────────────────────────────────────
✓ PRODUCTION-SCALE row counts, and production-
like DISTRIBUTIONS
✓ REALISTIC CONCURRENCY — many sessions
✓ the actual MIX of queries, in production
proportions
✓ measure p50, p95 AND p99 — averages hide the
tail that users experience
✓ run long enough for the cache to reach a steady
state
─────────────────────────────────────────
# pgbench with a custom script beats a synthetic default benchmark.
pgbench -f mixed_workload.sql -c 50 -j 4 -T 300 -P 10 mydb
# custom queries 50 clients, 4 threads, 5 minutes, progress every 10s6. What Not to Tune
Where Effort Is Wasted
─────────────────────────────────────────
RANDOM CONFIG CHANGES from a blog post.
Settings interact, and a value that helped
someone else's workload may hurt yours. Change
one thing, measure, keep or revert.
MICRO-OPTIMISING SQL SYNTAX. `IN` versus `EXISTS`
is usually rewritten to the same plan (Module 6,
Chapter 1). Check the plan before believing a
rewrite matters.
INDEXING EVERYTHING. Every index taxes every
write and competes for the buffer pool
(Module 5, Chapter 2).
DENORMALISING FIRST. It is step 7 of Module 4,
Chapter 5's procedure, not step 1.
UPGRADING HARDWARE to fix a missing index. It
works, briefly, and costs money forever.
─────────────────────────────────────────
The Order That Actually Pays
─────────────────────────────────────────
1. Fix the query (Module 3, 6)
2. Fix the index (Module 5)
3. Fix the statistics (Module 6)
4. Fix the schema (Module 4, 5)
5. Fix the configuration (Module 5, 6)
6. Fix the architecture (Module 8, 9)
Roughly 80% of real problems are resolved by
steps 1-3, in hours rather than months.
─────────────────────────────────────────
7. Summary & Next Steps
Key Takeaways
- Measure before changing anything, and rank queries by total time rather than average — the frequent fast query usually outweighs the rare slow one.
- Establish whether the problem is CPU, I/O, memory or locks before choosing a fix; wait events narrow forty causes to one family in a single query.
- Work outward through the layers — query, indexes and statistics, schema, configuration, architecture — because each is cheaper and safer than the next.
- Gradual system-wide slowdown means the working set outgrew the buffer pool; slowness only at peak means contention; slowness after a deploy usually means a plan flip.
Concept Check
- Why does "everything got gradually slower" point at a different cause than "this one query is slow"?
- What makes a load test on 1,000 rows actively misleading rather than merely incomplete?
- Which three steps resolve most performance problems, and why are they attempted before schema changes?
Next Chapter
→ Chapter 2: Capstone — Designing a Real System
Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index