Skip to main content
Stackbastion

Draft, not yet published

Running database migrations on a live AI-generated app without downtime

7 July 2026· 6 min read · by Stackbastion

You need to rename a column or add a NOT NULL field. You run the migration, and for 40 seconds every request errors out because the migration locked the table while your app was still trying to read it. Or worse, the new code expects a column the old code just dropped, and requests fail in the gap between deploy and migration. Schema changes on a running app are where a lot of “why did we go down?” incidents come from.

Why this happens

A migration changes the shape of your database while your app is still using it. Two things make that dangerous.

Locks. Some changes make Postgres take a heavy lock on the table. While that lock is held, reads and writes queue up and wait. A big ALTER TABLE that rewrites every row can hold that lock long enough that your app looks completely down. On a large table this can be minutes.

The code-and-schema gap. Your app code and your database schema have to agree. If you drop a column and then deploy the new code, there’s a window where the old code is still running against the new schema, hitting a column that no longer exists. Every request in that window fails. The same happens in reverse if you deploy first and migrate second.

AI tools generate migrations that assume an empty or tiny database, where locks are instant and nobody’s online. The generated ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'user' is fine on 10 rows. On 2 million rows with live traffic, it can lock the table long enough to page you.

The fix is to stop treating a change as one big flip. Break it into small, safe steps where the old and new versions of your app can both run against the database at every point in between. This is the expand-and-contract pattern, sometimes called additive-first.

How to fix it

The core rule: never make the old code and the new schema incompatible at the same time. Add first, backfill, switch the app over, and only remove the old thing once nothing uses it.

Take a common risky change: renaming users.name to users.full_name. Done naively, that’s one ALTER that instantly breaks all the running code referencing name. Done safely, it’s five small steps.

Step 1: expand, add the new column, don’t touch the old one

-- Fast and safe: adding a nullable column takes no meaningful lock
ALTER TABLE users ADD COLUMN full_name text;

Adding a nullable column with no default is nearly instant in modern Postgres, because it doesn’t rewrite existing rows. The old name column is untouched, so all current code keeps working.

Step 2: write to both columns

Deploy app code that writes to both name and full_name on every insert and update. Now new data lands in both places. The old code, if any is still running, only sees name and stays happy.

// During the transition, keep both columns in sync on write
await db.query(
  `UPDATE users SET name = $1, full_name = $1 WHERE id = $2`,
  [newName, userId]
);

Step 3: backfill the old rows, in batches

Existing rows have name but a null full_name. Fill them in. Do it in small batches so you never lock the whole table at once:

-- Repeat until zero rows are updated. Small batches keep locks short.
UPDATE users
SET full_name = name
WHERE full_name IS NULL
  AND id IN (
    SELECT id FROM users WHERE full_name IS NULL LIMIT 5000
  );

Run that in a loop from a script, pausing briefly between batches. A batch of 5,000 finishes fast and releases its lock, so live traffic barely notices.

Step 4: switch reads to the new column

Now that every row has full_name, deploy app code that reads from full_name. At this point nothing depends on name anymore, but it’s still there as a safety net. Watch your error logs for a day. If something breaks, the old column is still populated and you can roll the app back instantly.

Step 5: contract, remove the old column

Only after the new code has run clean for a while, and only once you’re sure nothing reads name, drop it:

-- Safe now: nothing references this column
ALTER TABLE users DROP COLUMN name;

Five steps instead of one, but at no single point are the running code and the schema in conflict. That’s what makes it zero-downtime.

The lock trap to avoid

The single most common way people take a table offline is adding a NOT NULL column with a default, or adding a foreign key, on a big table in one shot. Instead:

  • Add the column nullable, backfill, then add the NOT NULL constraint separately using NOT VALID and a later VALIDATE, which checks existing rows without a full-table lock.
  • Always set a lock_timeout before a migration so a stuck migration fails fast instead of freezing every request behind it:
-- If we can't get the lock in 3 seconds, give up rather than block traffic
SET lock_timeout = '3s';
ALTER TABLE users VALIDATE CONSTRAINT users_role_not_null;

If it times out, you retry during a quieter moment. Your app never stalls waiting on the lock.

Or, we do it for you

Safe, staged migrations with a rollback path are part of how we run every production database. If you’re about to make a schema change on a live app and you’re not sure it’s safe, get a free audit first.

FAQ

Why not just run migrations at 3am when nobody’s online?

Because “nobody’s online” isn’t a real strategy once you have users in more than one timezone, and it doesn’t protect against a long lock that runs past your quiet window. The expand-and-contract pattern is safe at any hour, so you stop scheduling your life around deploys.

Is adding a column really safe on a big table?

Adding a nullable column with no default is safe and near-instant in Postgres 11 and up, because it doesn’t rewrite existing rows. Adding a column with a non-null default, or adding constraints, is where the danger is. Split those into add-then-backfill-then-constrain.

What if a migration goes wrong halfway through?

That’s exactly why you keep the old column until the new one is proven. If step 4 shows errors, you redeploy the previous app version, which still reads the old column that’s still fully populated. Never drop the old thing in the same change that adds the new one.

Do I need special tooling for this?

No. The pattern is just ordering and small batches. A migration tool that tracks which migrations have run helps, and a lock_timeout guard is essential, but the core discipline is add-before-remove. Tooling makes it tidier, it doesn’t replace the thinking.

Related posts