{"course":{"slug":"cloud-and-shipping","title":"Shipping It","url":"https://addaly.com/learn/cloud-and-shipping","level":"intermediate","track":"build"},"lesson":{"slug":"migrations-without-downtime","title":"Changing the schema under a running site","url":"https://addaly.com/learn/cloud-and-shipping/migrations-without-downtime","position":18,"of":74,"minutes":11,"answer":"A schema change is safe when it never takes a long lock and never assumes only one version of the code is running, and both properties are things you arrange rather than hope for.","keyIdea":"A schema change is safe when it never takes a long lock and never assumes only one version of the code is running, and both properties are things you arrange rather than hope for."},"body":{"format":"markdown","text":"## Two facts that make schema changes hard\n\nThe first you have already met: during a deploy, old and new code run at the same time. The second is that a schema change takes a lock, and locks in a busy database queue.\n\nEvery technique below follows from those two.\n\n## The lock that turns a 1ms change into an outage\n\nIn Postgres, most `ALTER TABLE` statements take an `ACCESS EXCLUSIVE` lock, which conflicts with everything, including plain `SELECT`s.\n\nHere is the sequence that gets people:\n\n1. A long-running query (an analytics report, an open transaction from a stuck worker) is reading `users`. It has a shared lock.\n2. Your `ALTER TABLE users ADD COLUMN` asks for an exclusive lock. It waits.\n3. **Every subsequent query on `users` queues behind your waiting lock request**, because Postgres does not let new shared locks jump the queue.\n\nThe change itself takes a millisecond. The site is down for as long as that report runs. And the cause looks nothing like the symptom: your monitoring shows every query on one table timing out and a migration that has not finished.\n\nThe guard is one line before the DDL:\n\n```sql\nSET lock_timeout = '3s';\nALTER TABLE users ADD COLUMN display_name text;\n```\n\nNow, if the lock is not available in three seconds, your migration fails cleanly and nothing else is disturbed. Fail, retry in a minute, and let a blocked migration be a five-second annoyance instead of an incident.\n\n## Which changes are cheap and which rewrite the table\n\n- **Adding a nullable column** is metadata only. Instant.\n- **Adding a column with a constant default** has been metadata-only since Postgres 11. Before that it rewrote the whole table. A volatile default like `random()` still rewrites.\n- **Adding `NOT NULL` to an existing column** scans the whole table under an exclusive lock. Instead, add a `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT`, which takes a weaker lock.\n- **Changing a column type** usually rewrites. `varchar(50)` to `text` is free; `int` to `bigint` is not.\n- **Creating an index** locks out writes, unless you use `CREATE INDEX CONCURRENTLY`, which does not. Two caveats: it cannot run inside a transaction, so your migration tool needs to be told, and if it fails it leaves an `INVALID` index behind that you must drop by hand before retrying.\n\nMySQL has its own version of this table, with different entries. The habit is the same: before you run a migration on a live database, know whether the statement rewrites the table.\n\n## Expand, migrate, contract, in detail\n\nRenaming `name` to `display_name` is three deploys, and it is worth walking through what each one guarantees.\n\n> Figure: Renaming a column: expand, migrate, contract. Timeline: Deploy 1: expand, Add display_name with lock_timeout set. Code writes both columns, reads the old one; Between deploys, Backfill in batches of 5,000 with a pause, never one giant UPDATE; Deploy 2: migrate, Reads switch to display_name. Still writing both, so rollback is free; Deploy 3: contract, Stop writing name; Later, once sure, Drop the old column. Old and new code run side by side during every deploy, so no single step may assume only one version exists. Each step is reversible on its own, and code can roll back at any point because the old column stays current until the last one.\n\n**Deploy 1: expand.** Add the new column. Change the code to write to both columns and read from the old one. Old instances write only `name`, which is still the source of truth, so both versions are correct at once.\n\n```sql\nSET lock_timeout = '3s';\nALTER TABLE users ADD COLUMN display_name text;\n```\n\n**Backfill, separately from any deploy.** In batches, with a pause between them:\n\n```sql\nUPDATE users SET display_name = name\nWHERE display_name IS NULL AND id IN (\n  SELECT id FROM users WHERE display_name IS NULL LIMIT 5000\n);\n```\n\nLoop that until it affects zero rows. Do not write it as a single `UPDATE users SET display_name = name`. Ten million rows in one statement holds a transaction open for minutes, blocks autovacuum on that table, and bloats it, and if it fails at 90% you get all of it rolled back and none of the work.\n\n**Deploy 2: migrate.** Switch reads to `display_name`. Keep writing both. Now roll back is still free, because `name` is still current.\n\n**Deploy 3: contract.** Stop writing `name`. Then, in a later deploy once you are sure, drop it.\n\nTedious. It is also the difference between a rename and an outage, and each step is individually reversible.\n\n## Where migrations run, and the race nobody expects\n\nIf your container's start command is `npm run migrate && npm start`, then deploying five containers runs the migration five times, simultaneously. Some migration tools handle this with their own lock; many produce a partial application and a wedged ledger.\n\nRun migrations as **one separate step, before the new code rolls out**: a release job, a one-off task, a step in the deploy script. If you cannot, take an advisory lock so exactly one process proceeds:\n\n```sql\nSELECT pg_advisory_lock(72401);\n```\n\n## Test against production-sized data\n\nA migration on your development database with 200 rows tells you the SQL is valid. It tells you nothing about duration. The same statement against ten million rows can be forty minutes.\n\nRestore a recent backup to a scratch instance and time it there. This is the only way to know, and it costs an hour of a Saturday against an outage you would otherwise discover live.\n\n## Forward-only, in practice\n\nWrite the `down` migration if your tool demands one, but do not plan to use it. Rolling a schema backwards after the new code has written data in the new shape loses that data. In an incident, the reliable move is to roll the **code** back (which the expand/migrate/contract sequence has kept safe) and then fix forward with a new migration in daylight."},"check":{"question":"You run `ALTER TABLE orders ADD COLUMN note text` on a busy production database. It normally takes milliseconds. Instead, every query touching `orders` starts timing out, and the site is effectively down until you cancel the migration. What happened?","options":[{"text":"Adding a column rewrites the entire table, and `orders` is large","correct":false,"tempting":"True before Postgres 11 and still true for volatile defaults, but a plain nullable column addition is a metadata-only change regardless of table size."},{"text":"The new column was added without an index, so the query planner fell back to sequential scans","correct":false,"tempting":"Confuses schema changes with query planning; an unused new column does not change plans for queries that never reference it."},{"text":"A long-running query already held a lock on the table, the ALTER queued behind it, and every later query queued behind the ALTER","correct":true},{"text":"The migration ran inside a transaction that was never committed, holding the lock open indefinitely","correct":false,"tempting":"A real failure mode worth knowing, but here the statement is the thing waiting, not the thing holding, and cancelling it restored service immediately."}]},"prev":{"slug":"staging-and-preview","title":"The copy you are allowed to break","url":"https://addaly.com/learn/cloud-and-shipping/staging-and-preview"},"next":{"slug":"rollback-and-the-kill-switch","title":"Undo, in under two minutes","url":"https://addaly.com/learn/cloud-and-shipping/rollback-and-the-kill-switch"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Changing the schema under a running site', in Shipping It. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/cloud-and-shipping/migrations-without-downtime (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Changing the schema under a running site","courseTitle":"Shipping It","module":{"index":2,"slug":"making-a-deploy-boring","title":"Making a deploy boring"},"answer":null,"keyIdea":"A schema change is safe when it never takes a long lock and never assumes only one version of the code is running, and both properties are things you arrange rather than hope for.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"timeline","title":"Renaming a column: expand, migrate, contract","events":[{"when":"Deploy 1: expand","what":"Add display_name with lock_timeout set. Code writes both columns, reads the old one"},{"when":"Between deploys","what":"Backfill in batches of 5,000 with a pause, never one giant UPDATE"},{"when":"Deploy 2: migrate","what":"Reads switch to display_name. Still writing both, so rollback is free"},{"when":"Deploy 3: contract","what":"Stop writing name"},{"when":"Later, once sure","what":"Drop the old column"}],"caption":"Old and new code run side by side during every deploy, so no single step may assume only one version exists. Each step is reversible on its own, and code can roll back at any point because the old column stays current until the last one."},"alt":"Figure: Renaming a column: expand, migrate, contract. Timeline: Deploy 1: expand, Add display_name with lock_timeout set. Code writes both columns, reads the old one; Between deploys, Backfill in batches of 5,000 with a pause, never one giant UPDATE; Deploy 2: migrate, Reads switch to display_name. Still writing both, so rollback is free; Deploy 3: contract, Stop writing name; Later, once sure, Drop the old column. Old and new code run side by side during every deploy, so no single step may assume only one version exists. Each step is reversible on its own, and code can roll back at any point because the old column stays current until the last one."}},"blocks":[{"t":"h2","id":"two-facts-that-make-schema-changes-hard","runs":[{"text":"Two facts that make schema changes hard"}]},{"t":"p","runs":[{"text":"The first you have already met: during a deploy, old and new code run at the same time. The second is that a schema change takes a lock, and locks in a busy database queue."}]},{"t":"p","runs":[{"text":"Every technique below follows from those two."}]},{"t":"h2","id":"the-lock-that-turns-a-1ms-change-into-an-outage","runs":[{"text":"The lock that turns a 1ms change into an outage"}]},{"t":"p","runs":[{"text":"In Postgres, most "},{"text":"ALTER TABLE","code":true},{"text":" statements take an "},{"text":"ACCESS EXCLUSIVE","code":true},{"text":" lock, which conflicts with everything, including plain "},{"text":"SELECT","code":true},{"text":"s."}]},{"t":"p","runs":[{"text":"Here is the sequence that gets people:"}]},{"t":"ol","start":1,"items":[[{"text":"A long-running query (an analytics report, an open transaction from a stuck worker) is reading "},{"text":"users","code":true},{"text":". It has a shared lock."}],[{"text":"Your "},{"text":"ALTER TABLE users ADD COLUMN","code":true},{"text":" asks for an exclusive lock. It waits."}],[{"text":"Every subsequent query on ","b":true},{"text":"users","b":true,"code":true},{"text":" queues behind your waiting lock request","b":true},{"text":", because Postgres does not let new shared locks jump the queue."}]]},{"t":"p","runs":[{"text":"The change itself takes a millisecond. The site is down for as long as that report runs. And the cause looks nothing like the symptom: your monitoring shows every query on one table timing out and a migration that has not finished."}]},{"t":"p","runs":[{"text":"The guard is one line before the DDL:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SET lock_timeout = '3s';\nALTER TABLE users ADD COLUMN display_name text;"},{"t":"p","runs":[{"text":"Now, if the lock is not available in three seconds, your migration fails cleanly and nothing else is disturbed. Fail, retry in a minute, and let a blocked migration be a five-second annoyance instead of an incident."}]},{"t":"h2","id":"which-changes-are-cheap-and-which-rewrite-the-table","runs":[{"text":"Which changes are cheap and which rewrite the table"}]},{"t":"ul","items":[[{"text":"Adding a nullable column","b":true},{"text":" is metadata only. Instant."}],[{"text":"Adding a column with a constant default","b":true},{"text":" has been metadata-only since Postgres 11. Before that it rewrote the whole table. A volatile default like "},{"text":"random()","code":true},{"text":" still rewrites."}],[{"text":"Adding ","b":true},{"text":"NOT NULL","b":true,"code":true},{"text":" to an existing column","b":true},{"text":" scans the whole table under an exclusive lock. Instead, add a "},{"text":"CHECK (col IS NOT NULL) NOT VALID","code":true},{"text":", then "},{"text":"VALIDATE CONSTRAINT","code":true},{"text":", which takes a weaker lock."}],[{"text":"Changing a column type","b":true},{"text":" usually rewrites. "},{"text":"varchar(50)","code":true},{"text":" to "},{"text":"text","code":true},{"text":" is free; "},{"text":"int","code":true},{"text":" to "},{"text":"bigint","code":true},{"text":" is not."}],[{"text":"Creating an index","b":true},{"text":" locks out writes, unless you use "},{"text":"CREATE INDEX CONCURRENTLY","code":true},{"text":", which does not. Two caveats: it cannot run inside a transaction, so your migration tool needs to be told, and if it fails it leaves an "},{"text":"INVALID","code":true},{"text":" index behind that you must drop by hand before retrying."}]]},{"t":"p","runs":[{"text":"MySQL has its own version of this table, with different entries. The habit is the same: before you run a migration on a live database, know whether the statement rewrites the table."}]},{"t":"h2","id":"expand-migrate-contract-in-detail","runs":[{"text":"Expand, migrate, contract, in detail"}]},{"t":"p","runs":[{"text":"Renaming "},{"text":"name","code":true},{"text":" to "},{"text":"display_name","code":true},{"text":" is three deploys, and it is worth walking through what each one guarantees."}]},{"t":"p","runs":[{"text":"Deploy 1: expand.","b":true},{"text":" Add the new column. Change the code to write to both columns and read from the old one. Old instances write only "},{"text":"name","code":true},{"text":", which is still the source of truth, so both versions are correct at once."}]},{"t":"code","lang":"sql","label":"SQL","text":"SET lock_timeout = '3s';\nALTER TABLE users ADD COLUMN display_name text;"},{"t":"p","runs":[{"text":"Backfill, separately from any deploy.","b":true},{"text":" In batches, with a pause between them:"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE users SET display_name = name\nWHERE display_name IS NULL AND id IN (\n  SELECT id FROM users WHERE display_name IS NULL LIMIT 5000\n);"},{"t":"p","runs":[{"text":"Loop that until it affects zero rows. Do not write it as a single "},{"text":"UPDATE users SET display_name = name","code":true},{"text":". Ten million rows in one statement holds a transaction open for minutes, blocks autovacuum on that table, and bloats it, and if it fails at 90% you get all of it rolled back and none of the work."}]},{"t":"p","runs":[{"text":"Deploy 2: migrate.","b":true},{"text":" Switch reads to "},{"text":"display_name","code":true},{"text":". Keep writing both. Now roll back is still free, because "},{"text":"name","code":true},{"text":" is still current."}]},{"t":"p","runs":[{"text":"Deploy 3: contract.","b":true},{"text":" Stop writing "},{"text":"name","code":true},{"text":". Then, in a later deploy once you are sure, drop it."}]},{"t":"p","runs":[{"text":"Tedious. It is also the difference between a rename and an outage, and each step is individually reversible."}]},{"t":"h2","id":"where-migrations-run-and-the-race-nobody-expects","runs":[{"text":"Where migrations run, and the race nobody expects"}]},{"t":"p","runs":[{"text":"If your container’s start command is "},{"text":"npm run migrate && npm start","code":true},{"text":", then deploying five containers runs the migration five times, simultaneously. Some migration tools handle this with their own lock; many produce a partial application and a wedged ledger."}]},{"t":"p","runs":[{"text":"Run migrations as "},{"text":"one separate step, before the new code rolls out","b":true},{"text":": a release job, a one-off task, a step in the deploy script. If you cannot, take an advisory lock so exactly one process proceeds:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT pg_advisory_lock(72401);"},{"t":"h2","id":"test-against-production-sized-data","runs":[{"text":"Test against production-sized data"}]},{"t":"p","runs":[{"text":"A migration on your development database with 200 rows tells you the SQL is valid. It tells you nothing about duration. The same statement against ten million rows can be forty minutes."}]},{"t":"p","runs":[{"text":"Restore a recent backup to a scratch instance and time it there. This is the only way to know, and it costs an hour of a Saturday against an outage you would otherwise discover live."}]},{"t":"h2","id":"forward-only-in-practice","runs":[{"text":"Forward-only, in practice"}]},{"t":"p","runs":[{"text":"Write the "},{"text":"down","code":true},{"text":" migration if your tool demands one, but do not plan to use it. Rolling a schema backwards after the new code has written data in the new shape loses that data. In an incident, the reliable move is to roll the "},{"text":"code","b":true},{"text":" back (which the expand/migrate/contract sequence has kept safe) and then fix forward with a new migration in daylight."}]}],"check":{"question":[{"text":"You run "},{"text":"ALTER TABLE orders ADD COLUMN note text","code":true},{"text":" on a busy production database. It normally takes milliseconds. Instead, every query touching "},{"text":"orders","code":true},{"text":" starts timing out, and the site is effectively down until you cancel the migration. What happened?"}],"options":[{"text":[{"text":"Adding a column rewrites the entire table, and "},{"text":"orders","code":true},{"text":" is large"}],"correct":false,"tempting":[{"text":"True before Postgres 11 and still true for volatile defaults, but a plain nullable column addition is a metadata-only change regardless of table size."}]},{"text":[{"text":"The new column was added without an index, so the query planner fell back to sequential scans"}],"correct":false,"tempting":[{"text":"Confuses schema changes with query planning; an unused new column does not change plans for queries that never reference it."}]},{"text":[{"text":"A long-running query already held a lock on the table, the ALTER queued behind it, and every later query queued behind the ALTER"}],"correct":true,"tempting":null},{"text":[{"text":"The migration ran inside a transaction that was never committed, holding the lock open indefinitely"}],"correct":false,"tempting":[{"text":"A real failure mode worth knowing, but here the statement is the thing waiting, not the thing holding, and cancelling it restored service immediately."}]}],"order":[1,3,2,0],"why":null},"prev":{"slug":"staging-and-preview","title":"The copy you are allowed to break","minutes":8},"next":{"slug":"rollback-and-the-kill-switch","title":"Undo, in under two minutes","minutes":9}}}