👋 Hey friends,

Here’s what I’ve got in store for you this week:

• A snippet from Chapter 1 of The Backend Lowdown

• Why monitoring long running transactions is important

Let’s get into it 👇

The Backend Lowdown: Chapter 1 Preview

Safe Migrations and Schema Changes

Your database schema is the backbone of your application - and changing it is one of the most dangerous things you can do.

It starts innocently enough: you need a new column, or you want to enforce a constraint. Maybe you're cleaning up an old table. But in a live system, every schema change runs the risk of locking tables, blocking queries, breaking code, or even taking down production entirely.

If you've ever merged a migration and then watched your deploy hang, you already know this. If you haven't - good. Let's keep it that way.

This chapter focuses on the practical side of schema evolution:

• What makes migrations dangerous (and why it often isn't obvious)

• Patterns and workflows for making schema changes safely

• How to monitor, validate, and deploy migrations with confidence

The good news? Most migration-related incidents are entirely preventable. With a few habits, tools, and mental models, you can change your database with confidence - even under real production load.

Why Schema Changes Are Risky

If you're building or maintaining a backend system, database schema changes are inevitable. Over time, your application will evolve - new features are introduced, data models are rethought, and assumptions get replaced. These shifts often require corresponding changes to the schema: adding columns, updating indexes, renaming tables, or removing no-longer-needed fields.

In an ideal world, these changes would be routine and uneventful. But in real systems, schema changes are one of the most common sources of production issues. A poorly planned migration can:

• Lock a critical table and block traffic,

• Corrupt or delete data,

• Stall or break your deployment process,

• Or even take your entire app offline.

Most developers learn this the hard way.

Maybe you added a column with a NOT NULL constraint to a table with millions of rows, and it locked the database. Or maybe you dropped a column you thought was unused, only to find a background job still depending on it. Schema changes are risky not just because of what they alter, but because of the tight coupling between your application code and your data's structure.

Making safe schema changes isn't just about writing correct SQL - it's about making those changes without surprising your database, your app, or your users.

Stop Letting Long-Running Transactions Squat on Your Database

Last issue I talked about dead tuples and why they quietly bloat your Postgres tables. This week’s culprit is one of the main reasons those dead tuples don’t get cleaned up: long-running transactions, especially idle in transaction connections. If dead tuples are the trash, long-running transactions are the “do not enter” tape that stops vacuum from taking the trash out.

What counts as a “long-running” transaction?

A long-running transaction is any transaction that stays open for a long time, even if it’s not actively doing work. There are 2 variations:

1. Long-running active transactions: A query or series of queries that just keeps going (reporting queries, big batch jobs, etc).

2. idle in transaction: The request finished its work, but the app never committed or rolled back. The transaction is just… sitting there.

Both of these are bad, but #2 is usually a bug or misconfiguration in typical web apps.

Why are these a problem?

Open transactions:

1. Prevent vacuum from reclaiming dead tuples
   Postgres can’t advance the “oldest xmin” past the oldest open transaction, which means dead rows remain “not yet removable” and pile up.

2. Bloat tables and indexes
   More pages, more I/O, worse cache hit rates, slower queries.

3. Block schema changes
   ALTER TABLE and some index changes may sit around waiting on locks held by those open transactions.

4. Can contribute to wraparound issues in extreme cases (if you’re unlucky and ignore it long enough)

How do I see them?

At a minimum, you want a way to answer:

• What’s the oldest transaction right now?

• Do we have any connections stuck idle in transaction?

Here are some queries you can use:

-- Long-running *active* transactions
SELECT pid, usename, state, query,
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state = 'active'
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

-- Idle in transaction
SELECT pid, usename, query,
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '1 minute'
ORDER BY duration DESC;

Common causes (where to look first)

Here are some common causes to look out for if you do get alerted on the above:

• A web handler that opens a transaction and has an early return path that never closes it.

• Background jobs that stream a huge query and keep the transaction open for the entire job.

• Admin tools that “just run a quick SELECT” but reuse a transactional connection without closing it.

• DIY transaction wrappers without a proper try/finally / ensure that always commits/rolls back.

If you’re monitoring dead tuples (you are now, right?) but not tracking long-running and idle in transaction sessions, you’re only seeing half the picture. Make your oldest transaction age visible, set some sane alerts, and hunt down any code paths that leave transactions hanging around.

Your vacuum, and your future self, will thank you.

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