Why a Single ALTER TABLE Can Take Down Your Whole Database
A team runs a routine migration: ALTER TABLE transactions ADD COLUMN region TEXT. Nothing dramatic on paper. Twelve minutes later the connection pool is full, every request from every user is failing, and on-call is paged. The migration itself was never the problem. A lock queue was.
A Lock Isn't a Wall, It's a Queue Ticket
The word "lock" makes people picture a barrier: something is locked, everything else waits outside. That's not quite how PostgreSQL locking works, and the difference is exactly what causes outages like this one.
A better picture is a queue ticket. Every statement that touches a table takes a ticket. Some tickets are compatible with each other, holders can be served side by side, no problem. Other tickets are not compatible, and a ticket holder has to wait for the incompatible one ahead of it to finish before it can be served.
Two plain SELECT statements are always compatible. They take the weakest kind of ticket there is, and a hundred of them can run at once without ever noticing each other. The trouble starts when someone joins the line holding the strongest possible ticket.
The Strongest Ticket Blocks Everyone, Even the Compatible Ones
PostgreSQL has a handful of table-level lock strengths, but only one really matters for this story. It's called ACCESS EXCLUSIVE, and it's acquired by ALTER TABLE, DROP TABLE, and TRUNCATE. It conflicts with every other lock in the system, including a plain read.
Here's the part that catches people off guard: PostgreSQL mostly serves lock requests in the order they arrive. So if an ALTER TABLE shows up and has to wait for one long-running SELECT to finish, it doesn't just wait quietly off to the side. It gets in line. And every SELECT that shows up after it also has to get in line, behind the ALTER TABLE, even though those new SELECT statements would have been perfectly happy running alongside the original one.
One slow reader plus one waiting migration is enough to freeze every future read on that table, until the slow reader finally lets go.
How Twelve Minutes Becomes an Outage
This is close to how it actually plays out in production. An analytics query kicks off against a busy table and, because nobody set a timeout on it, keeps running for twelve minutes. A few minutes in, someone ships a routine schema migration on that same table. The migration asks for its ACCESS EXCLUSIVE ticket and starts waiting on the analytics query.
From that moment, every new request the application sends to that table queues up behind the migration. Within half a minute, hundreds of connections are stuck waiting on a lock, not on any actual work. The connection pool, which was never designed to hold hundreds of idle-but-blocked connections, fills up completely. New requests can't even get a connection to wait with. The site goes down, and the root cause line in the postmortem is almost funny in how small it is: one long read, one migration with no timeout.
The Fix Is Two Settings, Not a Rewrite
The defense here isn't clever code, it's two timeouts that should be set before any schema change ever touches a live table.
The first tells the migration itself to give up quickly if it can't get its lock in a few seconds, rather than parking itself at the front of a queue that keeps growing. The second is a ceiling on how long any query is allowed to run at all, so a stray analytics query can never hold a lock for twelve minutes in the first place. Neither setting is exotic. Both are usually just missing.
Set correctly, the same migration either succeeds in under a second because nothing was in its way, or it fails immediately and cleanly so you can retry it a minute later. What you never want is the third option: waiting, silently, gathering a crowd behind it.
Row Locks Are a Separate, Smaller Story
Everything above is about locking the table's structure. There's a second, unrelated locking system for locking individual rows of data, and it solves a different problem: two transactions trying to change the same row at the same time.
The classic example is a balance check before a debit. Read the balance, confirm there's enough money, then subtract the amount. Without a row lock, two concurrent withdrawals can both read the same starting balance and both proceed, and the account ends up wrong. Locking that one row for the duration of the transaction closes the gap.
There's also a neat variant built for queues: instead of locking a row and making every other worker wait for it, a worker can ask to skip any row that's already locked and grab the next free one instead. That's the difference between ten background workers piling up behind each other and ten workers each picking up a different job at the same instant.
Deadlocks: When Two Correct Transactions Still Collide
Occasionally two transactions each hold a lock the other one needs. Transaction A has locked account 1 and wants account 2. Transaction B has locked account 2 and wants account 1. Neither can proceed, and neither will ever let go voluntarily. PostgreSQL notices this after about a second, picks one of the two transactions, and kills it so the other can continue.
The fix isn't a database setting, it's a coding discipline: always acquire locks in the same order, everywhere in the codebase. If every transfer function locks the lower account ID first regardless of direction, the two transactions above stop being able to collide at all. It's the same trick as everyone in a crowded hallway agreeing to keep to the right.
Locks With No Data Attached
PostgreSQL also offers a kind of lock that has nothing to do with any table or row: an application-defined lock, identified by whatever number you choose. It's mainly used for one thing, making sure a background job runs on only one server at a time, even when there are several application instances that could all try to start it. Grab the lock, run the job, and if the process crashes mid-job, the lock releases itself automatically instead of leaving things stuck. It's a lighter, more reliable substitute for the Redis-based mutex a lot of teams reach for by default.
The Takeaway
A lock isn't something you fight, it's a ticket you're standing in line with, and most locking outages don't start with a bad lock. They start with someone who forgot to set a timeout and ended up holding the line for everyone behind them.
Where This Fits in the Full Course
This is Module 13 of the PostgreSQL In-Depth course, from the Architect phase covering the internals senior engineers eventually have to learn the hard way. If this scenario feels familiar, the earlier modules on MVCC and transaction internals build the concurrency model this one depends on.
#PostgreSQL #Database #Backend #SQL #ProductionIncidents #SoftwareEngineering