How to Change Your Database Schema Without Breaking Production

To change your database schema without breaking production, never make a change that old code cannot survive. Split every risky change into small, backward-compatible steps (the expand and contract migration pattern): add the new thing, write to both, backfill in batches, move reads, then remove the old thing in a later deploy. On Postgres, set a short lock_timeout before every migration, build indexes with CREATE INDEX CONCURRENTLY, add constraints as NOT VALID and validate them separately, and time each migration on a copy of production-size data before you run it for real.

This guide is for solo makers and tiny teams running a live product on Postgres. The worked example is made up; the database behaviour comes from the PostgreSQL 18 docs and the tool docs linked below, all checked in October 2026.

Why do schema changes break a live product?

On your laptop, a migration runs against a tiny database with nothing else happening. In production, four things are true at once:

• Old code is still running. During a deploy, the previous version keeps serving requests for seconds or minutes. If your migration renames bookings.student_name, every old server that still selects student_name starts throwing errors the moment the migration commits.

• Some changes lock the table. Many ALTER TABLE forms take the strongest lock Postgres has. While it is held, nothing else can read or write that table.

• Data changes take time. One UPDATE across a big table holds row locks until it finishes.

• Some steps cannot be undone. A dropped column takes its data with it. A down migration cannot bring that data back.

Zero-downtime database migrations mostly come down to one habit: asking what happens to the code and queries running while this executes.

What is the expand and contract migration pattern?

Martin Fowler's site describes parallel change, also known as expand and contract, as breaking a backward-incompatible change into three phases: expand, migrate and contract. The same article calls it a key part of evolutionary database design.

For a database it looks like this:

1. Expand. Add the new column, table or index next to the old one. Nothing reads it yet, so nothing can break.

2. Migrate. Make the code write to both shapes, copy old data across, then switch reads to the new shape. Old and new code can run side by side at every step.

3. Contract. Once nothing touches the old shape, remove it.

The cost is more deploys and a period where code handles two shapes. The article (by Danilo Sato) warns that skipping the contract phase can leave you worse off than before, so schedule the cleanup on day one.

How do you rename a column without downtime?

A rename is the classic trap, because RENAME COLUMN is instant in Postgres and still breaks your app. The data is fine; the old code is not. The strong_migrations README spells out the safer order: create a new column, write to both, backfill, move reads, stop writing to the old column, drop it.

Here is that order for renaming bookings.student_name to attendee_name, one deploy per step:

1. Deploy 1, expand: migration adds the column. ALTER TABLE bookings ADD COLUMN attendee_name text; No default, no constraint, so it is a quick metadata change.

2. Deploy 2, dual write: code writes the same value to both columns on every insert and update. Reads still use student_name.

3. Backfill: a script copies student_name into attendee_name for old rows, in small batches (more on that below).

4. Deploy 3, move reads: code reads attendee_name. Search for every use of the old name: raw SQL, exports, jobs.

5. Deploy 4, stop old writes: code no longer touches student_name. If your ORM caches the column list, tell it to ignore the old column first (strong_migrations shows this with ignored_columns in Rails).

6. Deploy 5, contract: a migration drops student_name, a few days later, after a backup.

Five deploys sounds like a lot, but on a small product the whole thing fits in a week.

Which Postgres schema changes are safe, and which lock the table?

The PostgreSQL ALTER TABLE docs say an ACCESS EXCLUSIVE lock is taken unless a subform says otherwise. The explicit locking chapter explains that this lock conflicts with every other lock mode, including plain reads. So the real question for Postgres schema changes is not "does it lock?" but "how long does it hold the lock, and does it scan or rewrite the table while holding it?"

A few details worth knowing from the docs:

• Constant vs volatile defaults. The PostgreSQL 11 release notes made adding a column with a constant default fast. A volatile default, stored generated column or identity column still rewrites.

• Concurrent indexes have rules. The CREATE INDEX docs say a concurrent build takes longer, cannot run inside a transaction block, and on failure leaves an INVALID index you should drop and rebuild. Rails wraps each migration in a transaction by default, so check how your tool turns that off.

• NOT VALID then VALIDATE. NOT VALID skips the scan and commits at once; new rows are checked from then on. The later VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue. PostgreSQL 18 also allows NOT VALID on not-null constraints; on older versions the CHECK route works because SET NOT NULL skips its scan when a valid CHECK constraint proves there are no nulls.

On SQLite or MySQL the lists differ, so read your own database's docs. Django's docs, for example, note that MySQL lacks transactions around schema changes, so a failed migration must be unpicked by hand.

Why can a fast ALTER TABLE still take your app down?

Your migration may need the lock for only a millisecond, but it has to get it first. If a slow report or a forgotten open transaction holds a lock on the table, your ALTER TABLE waits, and new queries line up behind it.

Squawk's guide to applying migrations safely describes exactly this: while the statement waits for its lock, all other reads and writes sent after it wait too, "bringing your application to a halt". A tiny change becomes an outage as long as the slowest query in front of it.

The fix is two settings from the Postgres client connection settings:

• lock_timeout aborts a statement that waits longer than the limit to get a lock. Default is 0, which means wait forever.

• statement_timeout aborts any statement that runs longer than the limit. Also 0 (off) by default.

Put them at the top of each migration:

SET lock_timeout = '2s'; SET statement_timeout = '60s';

If the migration hits the lock timeout, it fails cleanly, waiting queries carry on, and you retry a minute later. Squawk's example uses 1 second; strong_migrations suggests 10 seconds for the lock and a long statement timeout. Raise statement_timeout on purpose for slow, non-blocking work like a concurrent index build. If you use a pooler such as PgBouncer in transaction mode, strong_migrations notes you must set these on the database user instead.

How do you backfill data without locking everything?

Do not run UPDATE bookings SET attendee_name = student_name; on a big production table. It touches every row in one transaction and cannot be paused.

Write a small script instead:

1. Update one batch by primary key range, a few thousand rows at a time: UPDATE bookings SET attendee_name = student_name WHERE id BETWEEN 1 AND 5000 AND attendee_name IS NULL;

2. Commit, sleep briefly, move on. Log the last id so you can resume.

3. Run it outside the schema migration. strong_migrations lists the three keys as batching, throttling and running outside a transaction.

4. Run it once more at the end to catch stragglers.

The attendee_name IS NULL condition makes it safe to rerun.

Should you run the migration before or after deploying the code?

Use one rule: the database must work with both the old and the new code at every moment.

• Adding things: migrate first, then deploy code that uses them. Old code ignores a column it does not know about.

• Removing things: deploy code that stops using them first, then migrate. New code does not miss a column it never asks for.

• Never in one step: a migration that removes something and code that depends on the removal in the same deploy. That is the shape of most migration outages.

If you want to switch reads to the new column for a few users before everyone, a flag works well here; the MakerHunt guide to shipping risky changes behind feature flags covers the deploy versus release split, so I will not repeat it.

Where should migrations run: CI, deploy, or by hand?

Pick one migration tool for your stack and let it own the schema. Common ones: Rails Active Record migrations, Django migrations, Drizzle Kit (which can generate SQL migration files or push the schema directly), Prisma (which has its own expand and contract guide), Alembic for SQLAlchemy, Flyway and golang-migrate. The tool matters less than the rules around it:

• Read the SQL before it runs. A one-line model change can become a table rewrite. Django's sqlmigrate prints a migration's SQL; Drizzle Kit writes plain SQL files.

• Use versioned migration files in production, not schema "push" commands, which are for dev databases.

• Lint migrations in CI. strong_migrations (Rails) blocks dangerous operations by default and explains the safer way. Squawk lints raw Postgres SQL and has rules for missing lock timeouts, non-concurrent indexes and constraints without NOT VALID.

• Run migrations as one explicit deploy step, not on every app boot, where several servers can race to run the same migration.

Can you roll back a database migration?

Sometimes. Mostly the down migration is fiction: written in a hurry, never run, and unable to restore data the up migration deleted. Roll back a dropped column and you get an empty column.

Plan to roll forward: if a change goes wrong, ship a new migration that fixes it. Expand and contract helps, because until the contract step the old column still has its data and switching back is a code change.

Before any destructive step (drop, type change, data rewrite), take a fresh backup or snapshot and know how long a restore takes. A backup you have never restored is a hope; the DEV walkthrough of a monthly restore drill for a solo SaaS shows how to check yours.

How do you test a migration on production-size data?

A migration that is instant on seed data can crawl on a table with years of rows. Before anything risky:

1. Restore a recent backup into a separate database.

2. Run the migration there with \timing on in psql and write the time down.

3. While it runs, query pg_locks or pg_stat_activity from a second session to see what it holds and what waits.

4. Run one batch of the backfill and multiply out the total time.

That checks the migration only; for the rest of the app, see SideHunt's guide on how much testing a side project needs.

A pre-flight checklist and migration log

Before every migration that touches a table with real traffic, answer five questions:

• Can the currently deployed code run against the new schema?

• Does any statement rewrite or scan a large table while holding a strong lock?

• Is lock_timeout set?

• Did I time it on a production-size copy?

• If it removes anything, is there a fresh backup and is the code already off it?

Then keep a short log:

A made-up example: Cobblehive renames a column in five deploys

This example is invented to show the steps. Cobblehive and Halvard are not real.

Halvard runs Cobblehive, a class-booking app for small pottery studios, on one Postgres database. Many bookings were gift vouchers for someone else, so "student" was the wrong word. He wanted bookings.student_name to become attendee_name.

His first draft was a RENAME COLUMN plus a code change in one deploy, which would have broken every booking page on the old servers during rollout. So he split it:

• Monday: added the nullable column with lock_timeout set. It failed once on a lock timeout because a nightly export was still running; he retried ten minutes later and it went through.

• Tuesday: shipped dual writes, then ran the backfill script in batches and logged the last id after each batch.

• Wednesday: switched reads. A search for the old name found it in a CSV export and an email template he had forgotten.

• Friday: stopped writing the old column.

• The next week: took a backup, dropped student_name, and wrote the log entry.

Nothing broke for users, and the forgotten export would have broken in the one-step version.

What mistakes cause the most migration outages?

A 7-day plan to make your next schema change safe

Day counts are a planning guess for a small product; move faster or slower to fit.

Database migration FAQ

Is ADD COLUMN safe in Postgres?

Usually. A nullable column or one with a constant default is a metadata change and does not rewrite the table. A volatile default, a stored generated column or an identity column does rewrite it. Either way, set a lock timeout, because even a quick change has to wait for its lock.

Do I need expand and contract for a brand-new table?

No. Creating a table nothing uses yet is additive and safe. The pattern matters when existing code depends on the shape you are changing.

Can I just take the app down for a minute instead?

For a tiny product, a short announced maintenance window is a fair choice for a large rewrite. Time it on a copy first, so "a minute" really is a minute.

Small steps, boring deploys

Database migration best practices come down to never asking old code to survive a change it cannot handle, and never holding a lock longer than you meant to. And when the thing you are building is ready for more makers to try, MakerHunt is a friendly place to launch it.