Hey friends 👋

If you’ve noticed I’ve been posting a little less this week, it’s because I’ve been on vacation! 🌴 Still squeezing in a few backend videos between beach days though, just at a slightly more chill cadence.

This week’s edition includes:

• A backend triage quiz that sparked 70+ comments

• A fresh excerpt from The Backend Lowdown book

• A community comment that made me laugh and nod in agreement

Let’s dive in 👇

From The Backend Lowdown: How to Use Explain

When you prepend a query with EXPLAIN, the database doesn't execute it - instead, it shows you the execution plan: a step-by-step blueprint of how it would run the query. Each line in the plan represents an operation the database will perform, in order, from the innermost data access to the final output. Here's a basic example:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

The output might look something like:

Index Scan using index_users_on_email on users (cost=0.29..8.30 rows=1 width=100) Index Cond: (email = '[email protected]')

Let's break it down.

The Plan Is a Tree

Even though it's shown top-to-bottom, the plan is actually a tree of operations - each one feeding into the next. For simple queries, it's often just one step. For complex joins or subqueries, you'll see multiple nested levels.

Key Things to Look At

Scan Type

This tells you how the database is accessing the data:

• Seq Scan means a full-table scan - often slow on large tables.

• Index Scan or Index Only Scan means it's using an index - usually good.

• Bitmap Heap Scan means it's using part of an index, but still hitting the heap.

Index Used

Shown in lines like Index Scan using index_users_on_email. Confirms which index the planner chose.

Filter or Condition

Index Cond: or Filter: shows the condition being applied. Sometimes, an index exists but isn't used because the condition doesn't match it exactly.

Cost

Displayed as cost=START..END. It's not in seconds - it's a relative estimate of work, useful for comparing steps. Lower is better.

Rows Estimate

The planner's guess of how many rows this step will produce. If it's wildly off from reality, your statistics may be stale.

Weekly Video Highlight

What do you do when you get paged at 3:15am for a slow query on the coupons table?

This quick triage quiz hit a nerve: over 70 comments came in, and the discussion was great.

The “correct” answer was to add an index, but a lot of folks (rightly) pointed out that rolling out a schema change in the middle of the night might not be the wisest move.

Was it a bit contrived? Sure. But the goal wasn’t to be perfect, it was to spark thinking around what you should do vs. what you can do at 3am.

Check out the video below:

@stevencodes.swe

Can you fix this backend issue before your pager goes off again? Take this triage quiz and test your instincts! #tektok #coding #software ... See more

Community Corner

There were so many good comments on the above video, so I’m sharing a few here:

I have to admit, I’ve dropped the ball once or twice over the course of my career while being on triage and missing an alert is actually a realistic answer. Unfortunately some people are deeper sleepers than others despite their best efforts! To guarantee the highest possible chance of being woken up, I recommend an automated phone call instead of just a text or app notification if you’re on call.

What I loved the most about the comments (besides the funny ones) was how everyone’s different experiences shined through. Some denied adding an index was the right answer, especially at 3AM. While redis can still work despite cache misses, I want to point out that most of the disagreements seemed to be from a process standpoint. I.e., commenters who work at companies with very mature processes pointed out how this sort of thing would not fly at their place of work and that is completely valid and true! Those of us who have had the pleasure of working at a startup, however, know how process may not always be perfect and sometimes you really do need to “ssh into prod”. All in all, both commenters above here are correct! The correct answer just depends on the context and it’s very difficult to give enough for a contrived 60 second video!

That’s a wrap for this week’s newsletter. I’m still on vacation for another day so expect more content on a regular basis soon!

Also: The Backend Lowdown has quietly passed $100 in revenue this week! I’ll now be able to send updates to all buyers directly through Gumroad so expect a ping whenever new chapters drop.

Until next time,

— Steven