Insights · Nov 7, 2026 · 4 min read · FAQ

Changing a live SaaS database: expand first, contract later

Most schema changes can ship while customers keep working. Add the new shape first, move over in small steps, keep locks short and remove the old shape last.

ZERO DOWNTIMELive database changes SELLWRIGHT

Sooner or later a feature needs the database to change while customers are using it: a new column, a new data type, an index on a table with millions of rows. Done carelessly, one statement freezes the app, or code and schema disagree mid-deploy. Neither usually needs a maintenance window. This is what we expect developers to do, shown in PostgreSQL, because locking differs between databases.

Why can one schema change take the whole app down?

Because ALTER TABLE takes PostgreSQL's strongest lock, ACCESS EXCLUSIVE, unless the documentation for that specific form says otherwise. It conflicts with every other lock, including a plain SELECT, so while it is held nobody reads or writes the table.

Two things make it worse. Some changes also rewrite the table and its indexes under that lock: most column type changes do, and so does adding a column with a volatile default like clock_timestamp(). On a large table that can take a long time and temporarily need up to double the disk space. And the lock waits its turn: behind a long-running query, the migration waits, and every new query on that table queues behind the migration. A millisecond change can stall traffic for as long as the slowest open transaction.

What does expand and contract look like?

It splits one breaking change into safe steps, so the database works with both the live code and the next release. Say a busy table needs a column changed to a new type. Instead of altering it in place:

  1. Expand: add a new column, nullable or with a constant default. PostgreSQL stores a non-volatile default in the table's metadata rather than in every row, so this is quick even on a large table.
  2. Deploy code that writes to both columns and still reads the old one.
  3. Backfill existing rows in small batches.
  4. Switch reads to the new column once the backfill is checked.
  5. Contract: stop writing the old column, then drop it in a later release.

Until that last step, rolling the code back is safe, because the old column is still there and current. The drop itself is quick: PostgreSQL marks the column invisible rather than rewriting the table.

Batch the backfill because an UPDATE locks each row it changes until its transaction ends, blocking other writes to those rows. Small batches, committed one by one, keep locks short and can be paused. We make backfills skip rows already done, so a rerun is harmless.

How do you add an index without blocking writes?

Use CREATE INDEX CONCURRENTLY. A normal CREATE INDEX allows reads but blocks inserts, updates and deletes until it finishes, and very large tables can take many hours to index. The concurrent build keeps writes flowing, with conditions:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
  ON orders (customer_id);
  • It cannot run inside a transaction block, so if your migration tool wraps each migration in one, this migration must opt out.
  • It scans the table twice and waits for existing transactions, so it takes significantly longer and adds CPU and I/O load.
  • A failed build leaves an invalid index that queries ignore but writes still pay for. Drop it and build again.

How do you add a constraint to a big table?

Add it as NOT VALID, then validate it. Normally a new foreign key, check or not-null constraint scans the table to prove existing rows pass, locking out other updates until it commits. NOT VALID skips the scan, so the command commits straight away, and new or updated rows are checked from then on.

VALIDATE CONSTRAINT then checks the existing rows while holding only a SHARE UPDATE EXCLUSIVE lock, so reads and writes carry on. It also buys time: new violations are already blocked, and old ones can be fixed before validation succeeds.

What should a migration do when it cannot get its lock?

Fail fast and retry later. The lock_timeout setting aborts any statement that waits longer than the limit for a lock; it is off by default. Set it for the migration's own transaction, not in postgresql.conf, where the documentation warns it would affect every session.

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN plan_tier text;
COMMIT;

If the lock is not free within two seconds, the statement fails instead of holding up the queries behind it, and a script retries later. Even instant changes need this: the waiting is what hurts.

How do you test a migration before customers see it?

Run it on a production-sized copy, with personal data masked, and time every step. What is instant on a few hundred rows can scan or rewrite for minutes on the real table. Before release we check:

  • Which lock each statement takes, and whether it rewrites or scans the table.
  • How long each step takes, and whether there is disk space for any rewrite.
  • The way back for every step, usually the previous code release. That is why the drop comes last, after a release that no longer reads the old column.
  • How to spot a stuck migration: pg_blocking_pids() lists the sessions a waiting process is blocked behind, and pg_stat_activity shows their queries.

Where to start

List the roadmap items that touch large or busy tables and ask for each as expand and contract steps, with a lock timeout and a way back, before feature work starts. Our own SaaS runs on PostgreSQL too: see the boring stack behind our own SaaS. For schema changes planned and shipped this way, see our web app development service.

FAQ

Frequently asked questions

Can we rename a column without downtime?

Not as a single rename if any running code uses the old name. The rename takes effect the moment it commits, and servers still running the previous release then fail on the old name. Treat it as expand and contract instead: add the new column, write to both, backfill, move reads over, and drop the old column in a later release.

How do we make a new column NOT NULL on a big table?

After the backfill, add a check constraint that the column is not null, as NOT VALID, then run VALIDATE CONSTRAINT. When a valid check already proves no nulls exist, PostgreSQL skips the full table scan for SET NOT NULL, so that step needs its lock only briefly. The current documentation also allows a not-null constraint itself to be added as NOT VALID.

Can we add a unique constraint without locking the table?

Yes. Build the index with CREATE UNIQUE INDEX CONCURRENTLY, then attach it with ADD CONSTRAINT ... UNIQUE USING INDEX, which is a fast step. Clean up duplicates first, because they make the build fail and leave an invalid index behind. Uniqueness is also enforced against other transactions from the build's second table scan, so other queries can hit violations before the index is ready.

Do we ever still need a maintenance window?

Sometimes. A few operations hold an ACCESS EXCLUSIVE lock for as long as they run, such as VACUUM FULL or a type change that rewrites a large table. When expand and contract cannot avoid one, schedule it for your quietest hours, time it on full-size data first and tell customers in advance.

Does dropping a column free its disk space straight away?

No. DROP COLUMN makes the column invisible to queries rather than removing it from disk, so the table does not shrink at once. The space is reclaimed over time as existing rows are updated. Getting it back immediately means rewriting the whole table under the strongest lock, so we usually let it happen gradually.

Next step

Ready for a website that sells?

Book a short call. We will look at your current site, agree what matters most and send a written scope with a fixed quote.

Book a callOr write to hello@sellwrightstudio.com