> ## Content Index
> Fetch the complete content index at: https://debugly.dev/llms.txt
> Use this file to discover other available public pages before exploring further.

# The Migration That Locked the Table for Eleven Minutes
- URL: https://debugly.dev/migration-locked-the-table/
- Published: 2026-10-09T18:52:00.000Z
- Updated: 2026-10-10T13:58:39.000Z
- Description: The column was added with a default that required a full table rewrite, so the table took an exclusive lock and every write queued behind it until the…
- Author: Rohit Bhadani
- Tags: DevOps, PostgreSQL, Database

I would like to say the incident was the problem, because that would be a shorter post, but it started at the stopped.

Strip the migration that takes an exclusive lock down and one property makes it surprising: the statement is small and reads as a metadata change, and the database turns it into a full table rewrite because of the default value.

This was Postgres with a large table, and the mechanics depend on the version, so the version is the first thing to check.

## Why the small statement was expensive

The statement was the ADD COLUMN with the DEFAULT, and the DEFAULT was the non-volatile, and the non-volatile was the fast in the modern Postgres, and the modern was the not the version, and the not version was the older, and the older was the rewrite, and the rewrite was the every row, and the every row was the exclusive lock, and the lock was the queue.

The lock was the ACCESS EXCLUSIVE, and the EXCLUSIVE was the reads and the writes, and the two were the blocked, and the blocked was the queue, and the queue was the connection's exhaustion, and the exhaustion was the pool's, and the pool's was the whole service, and the service was the incident, and the incident was the one line.

The version was the determinant, and the determinant was the not checked, and the not checked was the assumption, and the assumption was the fast, and the fast was the wrong, and the wrong was the incident, and the incident is the reason the migration's cost should be checked against the actual server version rather than the documentation's general case.

## Why the queue became an outage

The lock was the held, and the held was the migration's duration, and the duration was the eleven minutes, and the eleven minutes was the every request's wait, and the wait was the lock\_timeout's absence, and the absence was the indefinite, and the indefinite was the connection's hold, and the hold was the pool's exhaustion, and the exhaustion was the outage.

The absence of the lock timeout is the amplifier, and the amplifier is the indefinite wait, and the wait is the connection, and the connection is the finite, and the finite is the exhausted, and the exhausted is the every query's failure, and the failure is the incident, and the incident is the reason the lock timeout should be set before the migration, so the migration fails rather than the service.

This is the same queue as [the idle transaction that held the lock for six hours](https://debugly.dev/idle-in-transaction-held-the-lock/), and the common root is the property, not the platform.

## What actually fixed it

**Set lock\_timeout before the migration.** The timeout was the seconds, and the seconds was the fail fast, and the fail fast was the migration's abort, and the abort was the service's survival, and the survival was the fix, and the fix was the one SET, and the SET was the discipline, because the migration should lose rather than the service.

**Added the column nullable, then backfilled, then set the default.** The nullable was the metadata-only, and the metadata-only was the instant, and the instant was the no lock, and the no lock was the fix, and the fix was the three steps, and the steps were the discipline, because the one statement is the rewrite and the three are the metadata.

**Backfilled in batches.** The batch was the thousand rows, and the thousand was the short lock, and the short lock was the queue's absence, and the absence was the fix, and the fix was the loop, and the loop was the discipline, because the single UPDATE is the one long transaction and the long transaction is the vacuum's problem.

**Checked the version's behaviour before running.** The version was the documented, and the documented was the fast path's presence, and the presence was the assumption's verification, and the verification was the fix, and the fix was the check, and the check was the discipline, because the fast path arrived in a specific version and the older server does the rewrite.

**Ran the migration in the low-traffic window with the rollback planned.** The window was the reduced impact, and the impact was the users', and the users' was the mitigation, and the mitigation was the fix, and the rollback was the plan, and the plan was the discipline, because the migration without the rollback plan is the prayer.

## The takeaway

Adding a column with a default can require a full table rewrite depending on the server version, and the rewrite takes an exclusive lock that queues every read and write. Set lock\_timeout first, add the column nullable then backfill in batches then set the default, and verify the version's behaviour before running.

One ALTER TABLE statement locked a large table for eleven minutes and took the service down through connection pool exhaustion, because the default value forced a full rewrite on that server version and no lock timeout was set. Name that one property in review and this class of bug gets hard to ship.