The migration that locks the table

Most migrations are instant, which is why the ones that aren't come as a surprise. What decides it isn't the size of the table. It's whether the database has to rewrite it.

What's instant in Postgres

Adding a nullable column is instant. Since Postgres 11, so is adding a column with a constant default: the default is recorded in the catalogue and isn't written into every existing row.

Dropping a column is instant. The column is marked dead, and the space is reclaimed later.

Renaming a column or a table is instant, because only the catalogue changes.

These cover most of what a migration file contains, and they're why day-to-day schema changes feel free.

What rewrites the table

Changing a column's type usually rewrites the table, because every existing value has to be converted.

Adding a NOT NULL constraint to an existing column requires a full scan to prove that no nulls exist.

Adding an index without CONCURRENTLY takes a lock that blocks writes for the whole build.

Changing a default is instant, but backfilling existing rows to match it isn't, and that distinction catches people out constantly.

flowchart TB
  M["a migration"] --> C{"does it rewrite rows?"}
  C -->|no| I["catalogue-only. instant, any table size"]
  C -->|yes| L["a rewrite under ACCESS EXCLUSIVE"]
  L --> B["every read and write on the table waits"]
  B --> S["duration scales with row count"]

The lock is the problem, more than the duration

A rewrite holds an ACCESS EXCLUSIVE lock, which blocks every read and every write on that table until it finishes.

Ten seconds of that on a busy table is an outage. Requests queue behind it, connections pile up, and the pool runs out long before the migration ends.

It's worse than it sounds. Postgres queues lock requests in order, so an ALTER TABLE waiting for its own lock blocks every query that arrives after it. That includes simple reads that wouldn't have conflicted. One slow migration can stall a table it hasn't started modifying yet.

Where this application stands

In September 2026 the largest table here was quiz_options, at about 40,000 rows, and lessons had 2519. Forcing a full rewrite of either took under 200 milliseconds locally.

So this course isn't describing a problem this application has. It describes the problem it would have at a hundred times the size, and the tools that exist because other people met it first.

Knowing which migrations are safe is useful at every scale. Knowing why the unsafe ones are unsafe is what makes the tools in the next lessons make sense.

Key takeaways

• Adding nullable columns, adding constant defaults, dropping and renaming only touch the catalogue, so they're instant.
• Type changes, NOT NULL on existing columns and non-concurrent indexes rewrite or scan the table.
• A rewrite holds ACCESS EXCLUSIVE, blocking every read and write until it finishes.
• A waiting ALTER TABLE blocks the queries that arrive behind it, including harmless reads.
• This application's tables are small, and the reasoning matters at every size.