# How to Change Your Database Schema Without Breaking Production
> How to change a Postgres schema without downtime: expand and contract, lock timeouts, concurrent indexes, NOT VALID, batched backfills, and a 7-day plan.

```yaml
url: "https://makerhunt.io/studio/how-to-change-your-database-schema-without-breaking-production"
markdown: "https://makerhunt.io/studio/how-to-change-your-database-schema-without-breaking-production.md"
title: How to Change Your Database Schema Without Breaking Production
type: studio_article
category: Growth
tags: "database migrations, zero-downtime database migrations, expand and contract migration, Postgres schema changes, PostgreSQL, database migration best practices, deployment, SaaS development, makers, Growth"
featured_image: "https://txmhk1zrnc.ufs.sh/f/xSkWTCqmKWx9buyTNdFnXleN6Dujc01vO79HRMIZYUCyTtKf"
published_at: "2026-10-29T09:54:42.122Z"
updated_at: "2026-10-29T09:54:42.711Z"
```

## About this studio article

How to change a Postgres schema without downtime: expand and contract, lock timeouts, concurrent indexes, NOT VALID, batched backfills, and a 7-day plan.

## Article

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](https://martinfowler.com/bliki/ParallelChange.html), 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](https://github.com/ankane/strong_migrations) 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.

![Hands typing on a laptop keyboard in a dark room, code glowing on the tilted screen](https://images.pexels.com/photos/5496464/pexels-photo-5496464.jpeg?auto=compress&cs=tinysrgb&w=1200&h=800&fit=crop)

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

The [PostgreSQL ALTER TABLE docs](https://www.postgresql.org/docs/current/sql-altertable.html) say an `ACCESS EXCLUSIVE` lock is taken unless a subform says otherwise. The [explicit locking chapter](https://www.postgresql.org/docs/current/explicit-locking.html) 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?"

Change

What Postgres does

Safer way

Add a nullable column, no default

Metadata only, no rewrite

Fine as is (with a lock timeout)

Add a column with a constant default

Default stored in metadata, no rewrite (since PostgreSQL 11)

Fine as is

Add a column with a volatile default such as `clock_timestamp()`

Rewrites the whole table and its indexes

Add without default, backfill in batches, then set the default

Create an index

Plain `CREATE INDEX` blocks writes until done

`CREATE INDEX CONCURRENTLY`

Make a column NOT NULL

Scans the whole table while blocking updates

`CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT`, then `SET NOT NULL`

Add a foreign key

Scans to check existing rows, blocking updates on both tables

Add with `NOT VALID`, then validate separately

Change a column's type

Usually rewrites the table and its indexes

Expand and contract: new column, backfill, switch, drop

Rename a column or table

Instant, but old code breaks

Expand and contract (see above)

Drop a column

Quick (column hidden, space reclaimed later), but data is gone

Stop using it in code first, back up, then drop

A few details worth knowing from the docs:

- **Constant vs volatile defaults.** The [PostgreSQL 11 release notes](https://www.postgresql.org/docs/release/11.0/) 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](https://www.postgresql.org/docs/current/sql-createindex.html) 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](https://squawkhq.com/docs/safe_migrations) 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](https://www.postgresql.org/docs/current/runtime-config-client.html):

- `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](https://makerhunt.io/studio/how-to-ship-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](https://guides.rubyonrails.org/active_record_migrations.html), [Django migrations](https://docs.djangoproject.com/en/stable/topics/migrations/), [Drizzle Kit](https://orm.drizzle.team/docs/kit-overview) (which can generate SQL migration files or push the schema directly), Prisma (which has its own [expand and contract guide](https://www.prisma.io/docs/guides/data-migration)), 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](https://github.com/sbdchd/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.

![Printed floor plan on a wooden desk with a pen and a tape measure](https://images.pexels.com/photos/4792483/pexels-photo-4792483.jpeg?auto=compress&cs=tinysrgb&w=1200&h=800&fit=crop)

## 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](https://dev.to/posting-dude/your-backups-dont-exist-until-youve-restored-one-a-monthly-drill-for-a-solo-saas-54ip) 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](https://sidehunt.io/studio/how-much-testing-does-a-side-project-need).

## 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:

Field

Example entry

Date and time

Tue 10:15, low-traffic hour

Migration

Add `attendee_name` to `bookings`

Phase

Expand (1 of 5)

Timed on copy

Under a second

Locks and timeouts

`lock_timeout` 2s, no rewrite

Backup before

Not needed (additive)

Result

Ran first try

Next step and date

Dual-write deploy Wednesday

## 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?

Weak

Better

Rename or drop a column in the same deploy as the code change

Expand and contract across several deploys

No lock timeout, so the migration queues behind a slow query

`SET lock_timeout` at the top of every migration, retry on failure

Plain `CREATE INDEX` on a busy table

`CREATE INDEX CONCURRENTLY`, outside a transaction

`SET NOT NULL` or a foreign key straight onto a big table

Add as `NOT VALID`, validate separately

One giant `UPDATE` for the backfill

Rerunnable script in small batches, outside the migration

Trusting the down migration

Roll forward; fresh backup before anything destructive

Timing the migration on seed data

Timing it on a production-size copy

Never doing the contract step

Put the cleanup on the list with a date

## 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.

Day

Do this

1

Add `lock_timeout` and `statement_timeout` to your migration template or migration database user.

2

Add a migration linter to CI (strong_migrations or Squawk) and fix or accept what it flags.

3

Restore a backup into a scratch database and time your last five migrations against it.

4

Write the backfill script template: batches, sleep, resume from last id, rerunnable.

5

Make migrations an explicit deploy step, not something that runs on app boot.

6

Start the migration log and add any half-finished expand steps you already have.

7

Run your next real change through the checklist, as expand and contract if it removes or renames anything.

## 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](https://makerhunt.io/) is a friendly place to launch it.

## Links

- Article (HTML): https://makerhunt.io/studio/how-to-change-your-database-schema-without-breaking-production
- AI-friendly Markdown: https://makerhunt.io/studio/how-to-change-your-database-schema-without-breaking-production.md

## Explore

- [Studio index](https://makerhunt.io/studio)
- [Blog](https://makerhunt.io/blog)
- [Browse projects](https://makerhunt.io/projects)

_HTML version: https://makerhunt.io/studio/how-to-change-your-database-schema-without-breaking-production_