The ALTER TABLE That Locked Production for Nine Seconds

The alert came in at 2:14 PM on a Wednesday. Response times on the checkout endpoint had spiked from 180ms to over nine seconds. By the time I pulled up the dashboard, three engineers were already in a war room call, and the on-call lead had the classic look of someone scrolling through Datadog while trying not to panic.

The checkout page wasn't returning errors. It was just... waiting. Every request hung, then eventually completed in a burst. The pattern repeated every few seconds — a flatline, then a spike, then normal, then flatline again.

My first instinct was a downstream service. Maybe the payment provider was slow, or a cache had gone cold. But the traces showed the bottleneck was the database query itself — a simple SELECT on the orders table. Postgres was taking eight to nine seconds to return rows from a table that normally responded in under 5ms.

The coincidence nobody mentioned

It took about fifteen minutes of digging before someone in the channel casually said, "Oh, we deployed that shipping feature about twenty minutes ago." The deployment included a database migration. Nobody had flagged it because it was "just adding a column."

The migration looked innocent enough:

BEGIN;
ALTER TABLE orders ADD COLUMN shipping_method varchar(50);
UPDATE orders SET shipping_method = 'standard' WHERE shipping_method IS NULL;
ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL;
COMMIT;

Three statements in one transaction. Add a column, backfill existing rows, then make it non-nullable. If you've written Rails migrations or Flyway scripts, this pattern probably looks familiar. It's the kind of thing that works perfectly on a dev database with 500 rows and destroys a production database with 8.4 million.

How Postgres locking actually works here

Here's the part that trips people up. In PostgreSQL 11 and later, ALTER TABLE ... ADD COLUMN with a non-volatile default doesn't rewrite the table. The catalog gets updated, and Postgres synthesizes the default value on read. It's fast. Milliseconds, even on enormous tables.

So the team assumed the migration would be quick. And the ALTER TABLE part was quick.

The problem was the UPDATE. Updating 8.4 million rows takes time — in this case, about nine seconds on their RDS instance. And because everything ran inside a single transaction, the ACCESS EXCLUSIVE lock that the ALTER TABLE acquired at the start was held for the entire duration. That lock blocks everything: reads, writes, even SELECT statements. Every query that touched the orders table sat in a queue, waiting for that transaction to finish.

Nine seconds of total blockage on the table behind the checkout page. During afternoon peak traffic.

The team got lucky, honestly. Nine seconds was enough to trigger alerts and confuse some customers, but the site stayed up. I've seen this same pattern cause cascading failures when the lock holds long enough for connection pools to fill, which tips the entire application over.

The lock queue makes it worse

There's a second-order effect that catches people off guard. When a long-running transaction holds an ACCESS EXCLUSIVE lock, new queries don't just wait — they queue up behind it. And in Postgres, a pending ACCESS EXCLUSIVE lock blocks new queries from acquiring any lock on the table, even ACCESS SHARE (the one a regular SELECT takes).

So it's not just the nine-second UPDATE that blocks reads. If there were other queries already running when the ALTER TABLE started, the DDL itself waits for those to finish before it can acquire its lock. Meanwhile, all new queries pile up behind the pending DDL lock. You get a convoy effect where the total user-visible impact is longer than the migration itself.

In our case, the application had a few long-running analytics queries against the orders table. The ALTER TABLE had to wait for those to complete before it could start, extending the total window of pain to about fourteen seconds from first impact to recovery.

The fix

We split the migration into separate, safe steps. Each one takes a brief lock and completes quickly.

Step 1: Add the column as nullable, with no default.

ALTER TABLE orders ADD COLUMN shipping_method varchar(50);

Brief ACCESS EXCLUSIVE lock, finishes in milliseconds. No table rewrite, no data changes.

Step 2: Backfill in batches, outside any DDL transaction.

UPDATE orders
SET shipping_method = 'standard'
WHERE id IN (
  SELECT id FROM orders
  WHERE shipping_method IS NULL
  LIMIT 5000
);

Run this in a loop until no rows are left. Each batch takes a ROW EXCLUSIVE lock on the affected rows only, so reads continue unblocked. We used batches of 5,000 with a 100ms pause between them to avoid saturating I/O.

Step 3: Add the NOT NULL constraint with validation deferred.

ALTER TABLE orders
  ADD CONSTRAINT orders_shipping_method_not_null
  CHECK (shipping_method IS NOT NULL) NOT VALID;

This takes a brief ACCESS EXCLUSIVE lock to add the constraint metadata, but NOT VALID means it skips checking existing rows. New inserts and updates are enforced immediately.

Step 4: Validate the constraint in the background.

ALTER TABLE orders VALIDATE CONSTRAINT orders_shipping_method_not_null;

This acquires a SHARE UPDATE EXCLUSIVE lock, which doesn't block reads or normal writes. It scans the table to confirm all rows satisfy the constraint, then marks it as valid. On 8.4 million rows, this took about forty seconds — but during those forty seconds, the checkout page worked fine.

What the team changed

The obvious fix was the migration itself. But the bigger problem was that nobody on the team had a mental model of Postgres locking behavior. The migration had passed code review with three approvals. Nobody questioned it because the pattern looked like every migration they'd ever written.

We added three things to their process. First, a migration lint step in CI that flags any transaction containing both DDL and DML statements. It's crude, but it catches the worst cases. Second, a wiki page with the safe migration pattern above — not because wiki pages are great documentation, but because it gave reviewers something to point to. Third, a lock_timeout setting of two seconds on their migration runner. If a migration can't acquire a lock within two seconds, it aborts instead of blocking traffic. You'd rather have a failed deployment than a locked table.

SET lock_timeout = '2s';

That last one is the cheapest safeguard, and it would have saved them that Wednesday afternoon.

The uncomfortable part

I've seen this exact pattern at four different clients in the past two years. Smart teams, solid codebases, thoughtful review processes. The migration syntax is correct. The logic is correct. It's the concurrency behavior that nobody thinks about, because you can't see locks by reading SQL.

Your test database has a hundred rows and no concurrent connections. The migration runs in 20 milliseconds. It gets approved, it gets deployed, and then it meets 8 million rows and 400 concurrent queries at 2 PM on a Wednesday.

If your migrations run inside a single transaction by default — and most frameworks do this — every migration is a potential table lock incident. The safe migration pattern is more steps and more code. It feels like overkill until you've spent a Wednesday afternoon explaining to your VP why the checkout page froze.