Renaming a column on a live database table seems simple. It's not. The moment you rename a column, every application query expecting the old name breaks. If you rename it within a transaction, Postgres locks the table; if anyone's reading it, your migration hangs for minutes. By then your uptime SLA is bleeding out.
Velocity X ships schema changes every week without downtime or failed deploys. The pattern: never rename live. Never drop live. Never add non-nullable constraints to tables with existing data. Instead: add the new column nullable, backfill asynchronously, constrain it, then drop the old column in a separate deploy cycle.
Why Naive Schema Changes Break Production
When you ALTER TABLE jobs ADD COLUMN priority NOT NULL DEFAULT 'low', Postgres acquires an access-exclusive lock. That lock prevents reads. Every user query queues. Your app's connection pool fills. Timeouts cascade. By the time the migration finishes, you've dropped peak traffic to 10%.
Renaming is worse. Rename a column inside a transaction, and you lock the table for the entire transaction duration. If your migration runner is slow—or if you're adding an index—you're locked for seconds or minutes. At scale, that's an incident.
Four Zero-Downtime Patterns
Pattern 1: Add Nullable, Then Constrain
Instead of adding a non-nullable column with a default, add it nullable in deploy-A. In deploy-B (hours or days later), once backfill is certain, add the NOT NULL constraint using SET NOT NULL in a separate migration that takes 1–2ms because rows are already populated.
{`-- Migration: 001_add_priority_nullable.sql
alter table jobs add column priority text;
-- Deploy app. Backfill in Netlify Edge Function or batch job.
-- Migration: 002_make_priority_not_null.sql (days later)
alter table jobs alter column priority set not null;`}Pattern 2: Introduce New Column, Dual-Write
Instead of renaming status to job_status, add a new column job_status. Update your application to write to both columns during the transition window. Once old-column reads are zero, delete the old column. This takes two deployments but guarantees zero breakage.
{`-- Migration: 001_add_job_status.sql
alter table jobs add column job_status text;
-- App code writes to both
async function updateJob(id, newStatus) {
return supabase
.from('jobs')
.update({ status: newStatus, job_status: newStatus })
.eq('id', id);
}
-- Migration: 002_drop_status.sql (1+ week later)
alter table jobs drop column status;`}Pattern 3: Views for Backward Compatibility
For massive tables where dual-write is risky, create a view that aliases the new column back to the old name. Application code reads through the view; queries still work. Underlying schema is clean. Once the app updates to the new column name, drop the view.
{`-- Migration: 001_rename_via_view.sql
alter table jobs rename column status to job_status;
create view jobs_legacy as
select id, job_status as status, title, created_at from jobs;
-- App reads from jobs_legacy for 2 weeks. Then update to read from jobs.
-- Migration: 002_drop_view.sql
drop view jobs_legacy;`}Pattern 4: Async Backfill with Service Role
For columns that need computed values, don't backfill inside the migration. Deploy the schema change first; then run a Netlify Edge Function (or cron job) using the service role key to batch-update rows in chunks. This avoids locking the entire table during migration.
{`// Netlify Edge Function: backfill job priorities
export default async (req: Request) => {
const supabase = createClient(
Deno.env.get('PUBLIC_SUPABASE_URL'),
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')
);
// Backfill in chunks to avoid locks
const { data: jobs } = await supabase
.from('jobs')
.select('id, created_at')
.is('priority', null)
.limit(1000);
for (const job of jobs) {
const priority = job.created_at < '2025-01-01' ? 'high' : 'low';
await supabase
.from('jobs')
.update({ priority })
.eq('id', job.id);
}
return new Response(JSON.stringify({ backfilled: jobs.length }));
};`}Migration File Convention
Keep migrations in supabase/migrations/ with timestamps: 20260612120000_add_priority_nullable.sql. Apply them during the Netlify build (not in CI). This ensures migrations run in order, once, against the actual production database. Version them. Test rollback paths. Never squash migrations.
Rollback Strategy
If a migration breaks production, you need a fast rollback. Keep a DOWN migration alongside every UP. For add-column migrations, rollback is trivial—drop the column. For renames, rollback is hard (you need the view trick). The rule: make rollback easier than the forward migration. If it isn't, the migration design is too risky.
Frequently Asked Questions
What if I already have a non-nullable column with bad data?
Add a new nullable column, migrate good data to it, then drop the old. It takes 2–3 migrations but is safer than trying to fix bad data in-place.
Can I add an index during a migration?
Indexes lock the table but don't block reads. Postgres acquires a lock but allows concurrent selects. Still, do it in a separate migration so you can monitor its progress. Use CREATE INDEX CONCURRENTLY if Postgres version allows it (11+).
How do I test migrations locally?
Use supabase start to spin up a local Postgres. Run migrations against it. Test both forward and rollback. Use supabase db pull to generate migrations from your schema.
Should I deploy the app before or after the schema migration?
Deploy the app after the schema is safe. If the new column is nullable, deploy the app that writes to both old and new, then wait for backfill, then add the constraint, then deploy the app that reads only from new. Three deploys, three nights of sleep.
What about foreign key constraints?
Adding a foreign key to a column with existing data locks the table while Postgres validates every row. Backfill the new column first, validate rows manually, then add the constraint. Or add it as DEFERRABLE INITIALLY DEFERRED so it validates at commit-time, not constraint-add-time.
The Bottom Line
Zero-downtime migrations aren't magic—they're just patience. Add, backfill, constrain, drop. Never do two of these in one migration. Never rename live columns. If your schema is changing every week and your app stays online, you're doing it right. If you've ever locked the table and heard the Slack alarm go off, you understand why.