Zero-Downtime Database Migrations for SaaS: A PostgreSQL Playbook

Zero-downtime migrations in a SaaS come from two habits. First, never ship a schema change the running code cannot handle: add new structure, move code and data across, then remove the old structure in a later release. Second, use lock-safe statements: set a lock timeout, build indexes concurrently, add constraints as NOT VALID and validate later, and backfill in small batches.

If you would rather have the migration plan and tooling built for you, see how RAITHub would build this near the end. If a migration has already broken production, start with recovering from a broken migration and come back here for prevention.

Why do database migrations cause downtime in a SaaS?

For two reasons that look the same from outside: locks and mismatches.

Locks. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock unless the documentation says otherwise (PostgreSQL ALTER TABLE). That lock conflicts with every other lock mode, including the one a plain SELECT takes (PostgreSQL explicit locking). The statement itself may take milliseconds, but if it has to wait behind a long-running query, every new query on that table queues behind it. To customers, that is an outage.

Mismatches. During a rolling deploy, old and new versions of your app run at the same time, both against one database. If the migration renames or drops a column the old version still uses, the old version fails until it is replaced. If the new code needs a column the migration has not created yet, the new version fails. Either way, some requests error for the length of the deploy.

A SaaS feels both more sharply than a single-customer app. One shared database means one bad migration hits every tenant at once, and there is no quiet hour when all customers are asleep.

What is expand-and-contract, and how does it work release by release?

Expand and contract, also called parallel change, splits a backward-incompatible change into three phases: expand, migrate and contract (Danilo Sato, Parallel Change, martinfowler.com). At every step, both the current and the previous app version work against the schema. Here is a rename of accounts.plan to accounts.plan_code across four releases:

Release	Migration	Application code	Safe to roll back to the previous release?
1. Expand	Add plan_code, nullable	Writes both columns, reads plan	Yes: old code ignores the new column
2. Backfill	Copy plan into plan_code in batches	Same as release 1	Yes
3. Switch reads	Add a NOT NULL guarantee on plan_code	Reads plan_code, still writes both	Yes: release 2 code still finds plan filled
4. Contract	Drop plan	Uses only plan_code	Yes: release 3 code no longer reads plan

Four releases sounds slow, but on a weekly cadence it is a month of background work with no customer impact, and each step is small enough to review in minutes. The process around it is in a SaaS release process for weekly releases.

What does the SQL look like for each step?

Each file below is a separate migration, shipped in its own release. Every file starts with a lock timeout. PostgreSQL's lock_timeout aborts any statement that waits longer than the set time to acquire a lock; it is off by default (PostgreSQL client connection defaults). Failing fast and retrying later is far better than a queue of blocked customer queries.

-- Release 1: expand (migrations/0101_add_plan_code.sql)
SET lock_timeout = '3s';
ALTER TABLE accounts ADD COLUMN plan_code text;

-- Release 2: backfill in batches (scripts/backfill_plan_code.sql)
-- Run repeatedly until it updates 0 rows.
UPDATE accounts
SET plan_code = plan
WHERE id IN (
  SELECT id FROM accounts
  WHERE plan_code IS NULL
  ORDER BY id
  LIMIT 5000
);

-- Release 3: guarantee NOT NULL without a long lock
-- (migrations/0103_plan_code_not_null.sql)
SET lock_timeout = '3s';
ALTER TABLE accounts
  ADD CONSTRAINT accounts_plan_code_nn CHECK (plan_code IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT accounts_plan_code_nn;
ALTER TABLE accounts ALTER COLUMN plan_code SET NOT NULL;
ALTER TABLE accounts DROP CONSTRAINT accounts_plan_code_nn;

-- Release 4: contract (migrations/0104_drop_plan.sql)
SET lock_timeout = '3s';
ALTER TABLE accounts DROP COLUMN plan;

Why release 3 works: with NOT VALID, adding the constraint skips the table scan and commits immediately, and the later VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue. SET NOT NULL normally scans the whole table, but skips the scan if a valid CHECK constraint already proves no nulls exist (PostgreSQL ALTER TABLE). If your migration tool runs each file in one transaction, consider splitting the validate step into its own file so its lock is not held alongside the others.

Run the backfill from a script or job, not inside a single migration transaction. Each batch commits on its own, so no lock is held for long and the job can stop and resume. Watch replication lag and database load while it runs, and slow down if either climbs.

Which PostgreSQL operations are safe, and which need care?

Operation	Risk	Safer form
Add a nullable column	Low: brief lock only	Fine as is, with a lock timeout
Add a column with a default	Low for a non-volatile default; a volatile default such as clock_timestamp() rewrites the table and its indexes	Use a constant default, or add nullable and backfill
Create an index	A plain build blocks inserts, updates and deletes until it finishes	CREATE INDEX CONCURRENTLY, outside a transaction
Add a foreign key or check constraint	Full scan under a strong lock	NOT VALID, then VALIDATE CONSTRAINT
Rename or drop a column	Breaks code still using the old name	Expand-and-contract over several releases
Change a column type	Often rewrites the table	New column, backfill, switch, drop old

The default and index rows come from the ALTER TABLE and CREATE INDEX documentation. A concurrent index build scans the table twice and cannot run inside a transaction block. If it fails, it leaves an "invalid" index that queries ignore but writes still maintain; drop it and run the command again.

-- migrations/0105_idx_invoices_tenant_created.sql
-- Must not be wrapped in a transaction by the migration tool.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_invoices_tenant_created
  ON invoices (tenant_id, created_at);

What changes for multi-tenant SaaS schemas?

It depends on how tenants are stored. The trade-offs between the models are in database per tenant vs shared schema; for migrations they differ like this:

• Shared schema, tenant ID column. One migration changes every tenant at once. Tables are larger, so lock and rewrite risks matter more, and backfills take longer. Every new tenant-scoped table also needs its row-level security policy or tenant filter in the same migration. The PostgreSQL row-level security guide covers the pattern.
• Schema or database per tenant. Each migration runs once per tenant. Tables are smaller, but you need a runner that records which tenants are on which version, retries failures and can pause. Partial rollouts are possible, which helps, but code must handle tenants on two schema versions at once, which is expand-and-contract by necessity.

Which CI checks enforce zero-downtime rules?

1. Replay every migration on an empty database on each pull request. On Sundor Skin, a B2B wholesale platform RAITHub built with 146 PostgreSQL tables, CI replays all 76 migrations on every change, among 530+ automated tests.
2. Run the test suite against the migrated schema, not one generated from ORM models.
3. Run the previous release's tests against the new schema. This is the most direct proof that a rollback is safe: if the old code's tests fail on the new schema, the migration is not backward compatible.
4. Lint migration files for drops, renames, type changes and non-concurrent indexes, and require a reviewed comment to pass. A ready-made script is in the broken-migration guide.
5. Fail on edits to applied migrations. Changing a file that already ran in production means fresh databases and production quietly differ.

How long does it take to set this up yourself?

For a team that already uses a migration tool, about three to five days for one engineer: add lock timeouts to the migration template, write the linter and the "old tests against new schema" job, and document the expand-and-contract steps. Each later incompatible change then costs a little extra planning. The main risk of doing it yourself is the backfill: an unbatched UPDATE on a large table, or one that runs inside the migration transaction, is the usual way a "safe" migration still causes an outage.

Buy, build or hire?

Option	What you get	Choose this when
Off-the-shelf migration tooling	A migration tool or managed schema-change service with built-in safety checks	Your stack is supported and your team will still follow the release order
Template or no-code backend	A hosted back end that manages schema changes through its own dashboard	Your product is early and schema changes are simple; you accept the platform's limits
Custom build (in-house or hired)	Migration templates, a linter, rollback-safety tests and a tenant-aware runner fitted to your schema	You have large tables, many tenants or a schema-per-tenant model, and downtime costs you customers

Why RAITHub for this

RAITHub runs migrations as gated code on every product it ships. Sundor Skin's pipeline replays its full migration history on each change, and the same discipline applies to the shared-schema and row-level security designs RAITHub builds for multi-tenant products. The SaaS development service includes this from the first migration; for an existing product with a drifting schema, it is a fixed-scope hardening job.

When you don't need us

• Your tables are small (thousands of rows, not millions) and you deploy in one step. A lock timeout and a rule against same-release renames may be all you need.
• A scheduled maintenance window is acceptable to your customers and contract. Then a short planned outage can be simpler than four releases.
• Your managed back end owns the schema. Follow its documented migration path instead.

How RAITHub would build this

• Scope: review your migration history and largest tables; add lock timeouts and a migration template; build the migration linter and the "previous tests against new schema" CI job; write a batched backfill runner; for schema-per-tenant products, a tenant-aware migration runner with version tracking.
• Timeline: as part of a new SaaS build (4–6 weeks for a fixed-scope MVP), or as a fixed-scope hardening job inside a backend engagement (6–12 weeks for larger work), confirmed in the written quote.
• What you receive: the tooling and CI checks in your repository, a written migration runbook with the expand-and-contract steps, tests, IP assigned to you and an NDA as standard.
• Next step: a free 15-minute technical audit, then a fixed written quote.

To make schema changes a non-event for your customers, book the free 15-minute technical audit.

Documentation checked on 7 October 2026.

Frequently asked questions

What is a zero-downtime migration?

A schema change applied while the app keeps serving traffic, with no errors or blocked requests. It relies on backward-compatible steps (expand, migrate, contract) and on statements that avoid long, strong locks.

How do I rename a column without downtime?

Add the new column, write to both, backfill in batches, switch reads to the new column, then drop the old one in a later release. Never rename in a single step while old code is still running.

Why does a fast ALTER TABLE still cause an outage?

Because it may wait for a lock behind a long-running query, and every new query on the table queues behind it. Set lock_timeout so the migration fails fast instead, then retry at a quieter moment.

Can CREATE INDEX CONCURRENTLY run inside a migration transaction?

No. PostgreSQL does not allow it inside a transaction block. Put it in its own migration and configure your tool to run that file without a transaction. If it fails, drop the invalid index and run it again.

How big should backfill batches be?

Small enough that each batch finishes in well under a second on your hardware, often a few thousand rows. Commit each batch separately and watch database load and replication lag while it runs.

Do I still need backups if my migrations are zero-downtime?

Yes. Safe statements prevent locks and mismatches, not a wrong UPDATE or DELETE. Keep point-in-time recovery on and test restores, as covered in SaaS backup and disaster recovery.