👋 Hey friends,
Hope you had a great holiday season! Here’s what I’ve got in store for you this week:
Snippet from The Backend Lowdown
DB Detective mini mystery
Let’s get into it 👇
The Backend Lowdown: Chapter 1 Preview
Every newsletter will include a snippet from my book in progress, The Backend Lowdown, available for $5 right now on Gumroad!
Get The Backend Lowdown →Workflow Tips (Cont.)
Avoid Combining Multiple Changes in One Migration
It might feel cleaner to bundle several schema changes together, but it makes it harder to:
Spot which change introduced a problem
Revert the migration cleanly
Roll out partial schema changes in a controlled way
One concern per migration. Let your migration history tell the story clearly.
Use PR Templates That Call Out Schema Changes
Include a section in your pull request template that explicitly asks:
Are there any schema changes in this PR?
Have you reviewed the query plan?
Will this block large tables or cause downtime?
This invites peer review and catches accidental footguns early.
Run Migrations in Low-Traffic Windows
Even safe migrations benefit from being run when traffic is low:
Less contention on locks
Easier to monitor impact
Safer to roll back quickly
If you're on a global app, define your "quiet hours." If you're B2B SaaS, Sunday mornings might be ideal.
DB Detective Mini Mystery
Yesterday this query was ~15ms. Today it’s 2–4 seconds, and nothing major changed (supposedly).
SELECT *
FROM appointments
WHERE account_id = $1
AND starts_at >= now()
ORDER BY starts_at
LIMIT 20;Clues
CPU is flat. Disk reads spiked
Table grew ~20% over the last week
Autovacuum is running, but not “behind” (again, supposedly)
There is an index on
(account_id)
What’s the most likely root cause? (pick one)
A) Missing composite index for filter + order
B) Row bloat from dead tuples causing extra page reads
C) Stale stats → planner picks a worse plan
Answer
The index on (account_id) can’t efficiently satisfy the ORDER BY starts_at + LIMIT 20, so Postgres often ends up scanning/sorting way more rows than you think.
Here’s the fix:
CREATE INDEX CONCURRENTLY idx_appointments_account_starts_at
ON appointments (account_id, starts_at);Why did it suddenly get worse?
As the table grows, the “scan a bunch then sort” path crosses a tipping point (more pages, more cache misses → more disk reads).
To verify, check the query plan:
EXPLAIN (ANALYZE, BUFFERS) <query>;If you see lots of heap reads + a sort, that’s the sign this was the problem.
B or C are also not wrong, they’re just less common for this specific shape of query. If B were the culprit, you might see a reasonable plan above, but BUFFERS shows a lot of shared reads (disk reads) and/or touches a lot of heap pages. If C were the culprit, the plan would likely look wrong. I.e., you’d see a Seq Scan when you expected an index path or it wildly mis-estimates row counts.
That’s a wrap for this week. If something here made your day smoother, feel free to reply and tell me about it. And if you think a friend or teammate would enjoy this too, I’d be grateful if you shared it with them.
Until next time,
Steven
