WebTech1223411 logo WebTech1223411Web tech, read closely
Back-End

How to Run Database Migrations Without Locking Tables

The migration itself took 41 milliseconds. The outage lasted nine minutes. That gap is the whole subject, and I learned it on a Tuesday evening deploy that added one nullable column to a 12 million row orders table in Postgres.

A developer screen showing a database lock monitoring query beside a migration file open in an editor

The migration itself took 41 milliseconds. The outage lasted nine minutes. That gap is the whole subject, and I learned it on a Tuesday evening deploy that added one nullable column to a 12 million row orders table in Postgres.

What happened is that the ALTER TABLE could not get its lock immediately, because a reporting query had been running against the same table for about four minutes. So the migration sat in the lock queue. And every query that arrived after it, including the plain selects that serve the product page, queued behind the migration, because a pending exclusive lock blocks everything behind it even though it holds nothing yet. Connections filled, the pool ran dry, and the API started returning 500s for a table nobody was writing to.

The lock queue, not the lock, is what takes you down

Most people picture a lock as a door: the migration holds it for 41 ms, everyone else waits 41 ms, nobody notices. The real behaviour is a queue with strict ordering, and that ordering is what surprises you.

A schema change takes ACCESS EXCLUSIVE, which conflicts with every other lock mode including the ACCESS SHARE that a simple select takes. When it cannot be granted, Postgres parks the request and parks everything that conflicts with it behind that request, to stop the exclusive waiter from starving forever. So the duration of your outage is not the duration of your migration. It is the remaining runtime of the longest transaction that already touched the table, plus your migration, applied to every query that arrives in that window.

Three transaction types are the usual culprits. Analytics queries that scan the table for 2 to 10 minutes. Idle in transaction connections, where an application opened a transaction, read one row and then went off to call an external API for 30 seconds. And autovacuum, which usually yields but will not if it is in the middle of a wraparound prevention run.

Two settings that turn an outage into a retry

Before any of the clever stuff, set a lock timeout for the session that runs migrations. In Postgres that is SET lock_timeout = '2s' at the top of the migration transaction. Now your schema change waits two seconds for the lock, fails if it cannot get it, and releases the queue instead of building it.

Failing is the goal. A migration that fails fast and gets retried 30 seconds later is a non event, and the second or third attempt almost always lands, because long analytics queries are not continuous. I use 2 seconds with five retries at 20 second intervals, and in two years that pattern has escalated to a human maybe four times.

Pair it with a statement timeout so a rewrite slower than expected cannot run for an hour. Keep both inside the one transaction, and never set either globally for the application role.

One trap in the ORM tooling: several migration frameworks wrap each migration in their own transaction and run multiple statements inside it. If statement one is fast and statement two sits waiting, statement one is still holding its exclusive lock the entire time. One schema change per transaction is the rule, which is annoying and is also the whole fix.

What is actually safe on a live table

The list changed significantly between Postgres 9.x and 11, and a lot of advice online predates that. These are the current behaviours I work from, measured on tables between 2 and 40 million rows.

OperationRewrites the tableLock heldHow to do it safely
Add a nullable columnNoMillisecondsJust do it, with a lock timeout
Add a column with a constant defaultNo, since Postgres 11MillisecondsSafe. A volatile default such as now() does rewrite
Add NOT NULL to an existing columnFull scanMinutes on a big tableAdd a CHECK constraint as NOT VALID, VALIDATE it, then convert
Create an indexNo, but blocks writesWhole buildCREATE INDEX CONCURRENTLY, outside a transaction
Add a foreign keyScans both tablesMinutesCreate NOT VALID, then VALIDATE CONSTRAINT separately
Change a column typeUsually yesWhole rewriteNew column, backfill, swap in code, drop later
Rename a column or tableNoMillisecondsCheap in the database and a breaking change for clients
Drop a columnNoMillisecondsCheap, but only after no deployed code selects it

Note the shape of that table. The dangerous operations are not the ones that sound destructive. Dropping a column is metadata only and takes no time at all, while adding a NOT NULL constraint, which feels like tightening a screw, scans every row. Renaming is instant in the database and the most dangerous of the lot for your users, for reasons that have nothing to do with locks and everything to do with the old code still running, which is the same problem we work through in API versioning without breaking existing clients.

Why a maintenance window is the wrong instinct

The standard advice is to schedule schema changes for 3 am behind a maintenance page. I have done that, it works, and I think it is the wrong default for most teams.

A window buys you a quiet table and nothing else. The real risk in a schema change is that the old application code and the new schema overlap for some period, and a maintenance window hides that risk rather than removing it. During a rolling deploy you always have both versions live, so if the migration and the code ship together, there is a stretch of minutes where half your processes are querying a column that does not exist yet or no longer does. That failure happens at 3 am just as happily as at noon.

Windows also train the wrong habit. Teams that migrate in windows write migrations that need windows, and after a year the schema can only be changed by waking someone up. The honest exception is a genuine full table rewrite where no incremental path exists, which is rarer than it feels.

Expand and contract, in three deploys

Renaming user_name to display_name on a live table is the standard teaching example, and the sequence generalises to type changes and table splits.

Deploy one, expand. Add the new nullable column. Change the application to write both columns and to read the old one. Nothing reads the new column yet, so a bug here is invisible to users. Backfill in batches, 5,000 to 20,000 rows per transaction with a short sleep between batches, because one giant UPDATE holds row locks and bloats the table while it runs.

Deploy two, switch. Point reads at the new column, keep writing both. This is the deploy you can roll back for free, which is why it goes on its own. Leave it in production for at least one full day and look at your error rate before continuing, since stale cached rows and queued background jobs surface here.

Deploy three, contract. Stop writing the old column, then drop it in a separate migration days later. Dropping is instant, so there is no reason to rush it, and keeping the column for a week means a rollback does not lose data. Only after the drop do you add NOT NULL to the new column, using the NOT VALID and VALIDATE pair so the scan never blocks writes.

Three deploys for one rename feels absurd the first time. It takes about twenty extra minutes of work and it converts a change that can break checkout into one that cannot. The same reasoning about overlapping versions decides how a process should exit mid request, which we cover in how graceful shutdown stops deploys from dropping requests.

Checking your migration before it meets production

Run the migration on a copy of production sized data with a load generator pointed at it, then watch pg_locks joined to pg_stat_activity and look for rows where granted is false. That query tells you, in one line, whether your migration created a queue. If nothing waited, you are fine. If a select waited 4 seconds, your users would have waited 4 seconds.

The other check takes ten seconds and catches more problems: before you deploy, query pg_stat_activity for the oldest transaction age on the target table. If something has been open for six minutes, your migration will inherit that wait, and the right move is to deploy after it finishes. Index choices interact with all of this, and the sizing side of that sits in how database indexes speed up slow API queries.

FAQ

Does CREATE INDEX CONCURRENTLY have downsides?

It takes roughly two to three times as long, it cannot run inside a transaction, and it can leave an invalid index behind if it fails. Check for invalid indexes after any failed run and drop them, because they cost write throughput and are never used for reads.

Do these problems exist in MySQL too?

Yes, with a different shape. InnoDB does many changes in place now, but metadata locks queue in much the same way, and long running transactions block DDL identically. The expand and contract pattern is engine independent, which is why it is the part worth learning.

How large can a backfill batch be?

Size it by duration rather than row count: aim for batches that finish in under a second, which is usually 5,000 to 20,000 rows. Commit each batch, sleep 100 to 500 ms, and track progress in a small table so a restart resumes instead of starting over.

Should migrations run from the application container on startup?

No, in my experience. Startup migrations race each other across replicas, run with whatever timeout the app sets, and block the health check. Run them as a separate job that completes before the new version receives traffic.

KO
Kofi Oosterhuis

Kofi spent years keeping APIs and game servers alive through traffic spikes and the occasional bad deploy. He covers back-end design, scaling and networking, with a preference for boring systems that do not page anyone at night.

More posts by Kofi

More in Back-End

01doorganico 084goad 3hot8 4465456 after5internprogram ahandfulofblessings ahmadpc ahomcybersecurity allodialresources annodex antiquesandperiodcollectibles aotc asianworldkashmir beedlectrics bloggertemplatefree bluefeatheragency bodyweightsolution campaign08blog carfixadvisor catstreetcollective cbstvsports cnfanspurchasing committeeof500yearsofdignityandresistance covertopsdjs cs-kaiyuangame cuestacomunicaciontotal cxoutlet dancebetgame deskgearhub destinyvillastay detectmagazine di33 diamondsteelbuildings donmo dyanadropship emarsysfinance emimattl escapechan eskitarih evautoplay everythingweddings-orlando farmzone feedviewerapp foxstudiosscratch gamevui123 gleasongrade greetingsday hacklabasu haskell iw-kaiyun jagoanproperti jilidskripsi joltbusinessfinance juegodepokergratis juegospucca kaeru-festival latestmehndidesigns lestanzediottaviano litemagazines localcommunityhero lustrsazan mariashebets menswheels newwebstechs1 nikkit ootheday oxigenvpn plurfitness prefetial probudgetcarpetcleaning psychicmrsmartin pyaten qvantor rejoignezlogis rexzbot riselayouts ruchin rwxqsgd5 sakaltime shibiaowang shop-bot skrtips spotlesshaven strcoding tetapatrium topselller uneplanteartificielle vipertechcr wo-kygaming yamaha-mekar yeknava yoybuysheets zenunwa yamaha-mekar jilidskripsi ankertoto ankertoto ankertoto