👋 Hey friends,
Here’s what I’ve got in store for you this week:
Another snippet from The Backend Lowdown
Recent post recap: Write Contention
Next post preview: Fixing the hot-row upsert
Let’s get into it 👇
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 →Chapter 2: Indexing, Query Planning, and Performance
What is an Index?
An index is a data structure the database uses to find rows faster - like a shortcut to avoid scanning every row in a table. If you've ever used a book index to jump straight to a topic, you already get the idea.
Without an index, a query like:
SELECT * FROM users WHERE email = '[email protected]';requires the database to scan every row in the users table to see if email matches. This is called a sequential scan, and it works - but it's slow at scale.
With an index on email, the database can jump directly to the rows it needs, skipping everything else. That can turn a query that takes seconds into one that runs in milliseconds.
Indexes can dramatically improve read performance - but they're not free. In fact, they're one of the easiest ways to accidentally hurt your system's performance while thinking you're helping. Here's how that happens, and how to avoid it.
Common Ways Indexes Hurt
They slow down writes.
Every time you insert, update, or delete a row, the DB has to update every relevant index. The more indexes you have, the more work each write operation does.They take up disk space.
Especially on wide tables or large datasets, indexes can significantly bloat your storage - and backups.They can mislead the query planner.
Too many indexes (especially overlapping or misaligned ones) can confuse the planner and lead to suboptimal execution paths.They slow down bulk operations.
LargeINSERTs, batch updates, or data migrations are often much slower with indexes in place. This is why it's common to temporarily drop indexes before importing millions of rows.
How to Know What to Index
Start from the query
Don't guess - useEXPLAIN ANALYZEon your slowest queries to see what the database actually needs.Only index columns used in filters or joins
Indexes help most on columns used inWHERE,JOIN, and sometimesORDER BY.Watch out for low-cardinality fields
Indexing columns likearchivedorstatusrarely helps on their own - unless paired with another condition.Keep multicolumn indexes lean and purposeful
Column order matters. Indexes on(user_id, created_at)won't help if your query only filters bycreated_at.Skip indexes during large imports
For bigINSERTjobs, drop non-essential indexes first and recreate them afterward to speed things up.
Recent Post Recap
In case you missed it, this week’s post shows how a single line of SQL can turn a healthy Postgres database into a single-file line and how to spot it quickly with the DB Load / Average Active Sessions (AAS) chart (see the previous post which introduces the chart).
The setup: low-cardinality upserts = collisions
The problematic upsert
The scenario is an INSERT ... ON CONFLICT DO UPDATE against a low-cardinality table (meaning there are only a few possible keys/rows). Under load, lots of requests “collide” on the same row(s).
The subtle footgun is this pattern:
ON CONFLICT ... DO UPDATEand you update something every time (like
updated_at)so every conflict becomes a real update, not a no-op
Result: you’ve accidentally created a hot row.
Low-cardinality UPSERT causing write contention
Why it hurts: row locks → queueing
When many requests try to update the same row, Postgres has to coordinate access using row-level locks. Only one writer can proceed at a time, so everyone else waits and the system feels “slow” not because it’s out of CPU, but because it’s lined up behind a lock.
Row-level lock coordination
The chart signal: AAS spikes, locks dominate
This shows up cleanly in the DB Load / AAS chart:
AAS spikes
the stack shifts away from CPU
and you see Lock waits grow (commonly
transactionidandtuple)
That pattern is a strong hint you’re jammed on writes, often due to a hot-row / low-cardinality upsert like this.
Next Post Preview: Fixing the hot-row upsert
In the next post, I’m going to walk through a few practical ways to fix the write contention we saw and the tradeoffs each option comes with.
We’ll start by converting the upsert from ON CONFLICT DO UPDATE to ON CONFLICT DO NOTHING. That change alone can remove a huge chunk of the contention because conflicts stop turning into real updates (and you stop forcing Postgres to serialize writers on the same row).
But it’s not a silver bullet.
Under extreme concurrency, even DO NOTHING can still run into bottlenecks and surprising edge cases. I’ll break down what can still go wrong, why it happens, and a few mitigation techniques you can use to keep the behavior you want without reintroducing a “single-file line” in the database.
Stay tuned.
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
