Skip to content

Databases · 2 of 9

Step 1 · The pattern

Readers share

Ordinary queries take a light, shared lock on a table. Any number can hold it at once, so reads and writes run side by side without waiting.

  • query
  • served

In short

Lock queue: how it works and how it breaks

Most schema changes need an exclusive lock on the table, even if only for an instant. An exclusive lock conflicts with everything, including plain reads, so the change has to wait until every query already using the table has finished. That alone would be harmless. The trouble is that Postgres hands out locks in the order they were requested. While the migration waits, every new query on that table, even a simple SELECT that would normally run alongside the others, lines up behind it. One slow analytics query plus one tiny migration equals a table nobody can read. The migration itself takes milliseconds; it is the waiting that causes the outage. The defence is to set lock_timeout before any schema change, so it gives up after a second or two and you retry later. Look for long-running transactions first, and split changes so the locked part is brief.

How this comes up in interviews

"A migration that adds one column took the site down for a minute, although the change itself takes milliseconds. Explain what happened."

Then they ask

  1. Why were plain SELECT queries blocked when the migration had not even started?
  2. How do you run this migration safely on a busy table?
  3. The migration only touches a new, empty table but adds a foreign key to a busy one. Is it safe?

A strong answer covers

  • Most schema changes need an exclusive lock, which conflicts with everything, including plain reads.
  • The change waits for every query already using the table, such as one long analytics query.
  • Postgres grants locks in request order, so every new query on the table lines up behind the waiting migration.
  • Set lock_timeout before any schema change so it gives up quickly, then retry later.
  • Look for long-running transactions first, and split changes so the locked part is brief.

Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.

On one page: the track cheat sheet.

Last updated: Official documentation

Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.