You need to rename a column in your Supabase database. The column is actively used by production code. You panic because running `ALTER TABLE` directly locks the table — if 500 concurrent users hit your API while the rename is in flight, they queue up waiting for the lock, your app times out, and your SLA is toast. The naive approach — deploy the rename, hope it's fast, apologize on Twitter — is not a strategy.
The Aidxn playbook avoids locks entirely by splitting the change into five phases: (1) Add the new column alongside the old, (2) Update your app to write to both, (3) Backfill the new column from the old, (4) Switch reads to the new column, (5) Drop the old column. No downtime, no locks, no traffic disruption. We run this pattern weekly across SaaS products. Here's the exact playbook.
Why Naive ALTER TABLE Breaks Production
A simple `ALTER TABLE users RENAME COLUMN user_name TO username` acquires an `AccessExclusiveLock` on the table. While that lock is held, reads queue. For a large table, the rename completes in milliseconds, but even milliseconds of lock time across 500 concurrent users tanks your response times. If your code is still trying to read `user_name` while the column is locked, connections timeout. In Postgres 14+, `ALTER TABLE` is faster because catalogs are metadata-only, but locks still happen. For a live SaaS product, any lock is too long.
The 5-phase pattern eliminates locks by never altering the column in place. Instead, you add a new column, backfill it, switch your reads, then drop the old column weeks later. Each phase is backwards-compatible with live traffic.
Phase 1: Add the New Column (Non-Blocking)
First, add the new column alongside the old. This is a non-blocking operation in Postgres 11+.
-- In Supabase SQL Editor
alter table users add column username text;
-- Takes seconds, no lock on reads.
The column defaults to `NULL` for all existing rows. Your code still reads and writes to `user_name`. The new `username` column sits empty and harmless. This phase takes 5 seconds. Zero downtime.
Phase 2: Deploy Dual Writes (App Code)
Update your application code to write to BOTH columns whenever you write to `user_name`. This is the only code change required; no data migration yet.
// Before: writing user_name only
await supabase
.from('users')
.update({ user_name: newName })
.eq('id', userId);
// After: dual writes
await supabase
.from('users')
.update({
user_name: newName, // old column
username: newName // new column
})
.eq('id', userId);
Deploy this code. From now on, every write to `user_name` also writes to `username`. Old reads still come from `user_name`. New rows get both columns populated. Reads still use `user_name` and never block. This phase is safe to roll back — if you revert the code, `user_name` never stops working.
Phase 3: Backfill the New Column (Async Job)
While dual writes are running, backfill the new column with data from the old column. This happens in the background without locking reads or writes.
-- In Supabase, create a backfill function
create or replace function backfill_username()
returns void as $$
begin
-- Update in batches to avoid locking
update users
set username = user_name
where username is null
and user_name is not null
limit 1000;
-- Repeat until done
if found then
-- Schedule next batch in 5 seconds
perform pg_sleep(5);
perform backfill_username();
end if;
end;
$$ language plpgsql;
-- Trigger the backfill
select backfill_username();
Or trigger it from an Edge Function every 30 seconds until done:
// supabase/functions/backfill-username/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0';
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);
Deno.serve(async () => {
const { count } = await supabase
.from('users')
.select('id', { count: 'exact' })
.is('username', null);
if (count === 0) {
return new Response(JSON.stringify({ status: 'done' }), { status: 200 });
}
await supabase.rpc('backfill_username');
return new Response(JSON.stringify({ remaining: count }), { status: 200 });
});
Monitor progress: `select count(*) from users where username is null;`. Once it hits zero, move to phase 4. This can take hours for large tables — that's fine, there's no rush.
Phase 4: Switch Reads to the New Column (App Code)
Once backfill is done and you're confident `username` is populated, update your reads to use the new column. Writes still go to both columns for safety.
// Before: reading from user_name
const user = await supabase
.from('users')
.select('user_name')
.eq('id', userId)
.single();
console.log(user.data.user_name);
// After: reading from username
const user = await supabase
.from('users')
.select('username')
.eq('id', userId)
.single();
console.log(user.data.username);
Deploy this code. All reads now use `username`. All writes still update both columns (Phase 2 dual writes are still active). If something breaks, roll back — reads go back to `user_name` which is still being written to. Zero downtime even if the switchover goes wrong.
Phase 5: Drop the Old Column (Non-Blocking)
Once all code is reading from the new column and you're confident it's working (1–2 weeks of stable reads), drop the old column:
-- In Supabase SQL Editor
alter table users drop column user_name;
Also clean up Phase 2 dual writes from your code — stop writing to `user_name` since it no longer exists. This completes the migration. Total time: 2–3 weeks of patience, zero seconds of downtime.
Rollback Strategy: Each Phase Is Reversible
Phase 1: Roll back by dropping the new column. `drop column username;`
Phase 2: Roll back by reverting the code deploy. Dual writes stop, old column still works.
Phase 3: Roll back by cancelling the backfill job. Incomplete data in the new column is harmless — it's not being read yet.
Phase 4: Roll back by reverting the code deploy. Reads go back to the old column, which is still being written to (Phase 2 dual writes).
Phase 5: Can't roll back once the column is dropped, but by this point you've had 2 weeks of confidence that reads work on the new column.
The entire pattern is designed so you can abort at any point and lose nothing.
Real Example: Renaming user_email to email
Here's the full workflow for a real migration — renaming `user_email` to `email` in a SaaS product with 50,000 active users:
-- Week 1: Phase 1 (add column)
alter table users add column email text;
-- Deploy immediately.
-- Week 1, Day 2: Phase 2 (dual writes)
// In your email service code:
await supabase
.from('users')
.update({
user_email: newEmail,
email: newEmail
})
.eq('id', userId);
// Deploy.
-- Week 2: Phase 3 (backfill)
// Backfill runs for 2 hours. Monitor: `select count(*) from users where email is null;`
// Once zero, move to Phase 4.
-- Week 2, Day 5: Phase 4 (switch reads)
// In your auth/profile code:
const { data: user } = await supabase
.from('users')
.select('email')
.eq('id', userId)
.single();
// Deploy. Monitor for errors.
-- Week 3: Phase 5 (drop old column)
alter table users drop column user_email;
// Remove dual writes from code.
Total disruption: zero. Total time investment: 30 minutes across two code deploys.
Six FAQs
What about indexes on the old column?
In Phase 1, add an index on the new column: `create index idx_username on users(username);`. In Phase 5, the old index is dropped automatically when you drop the column. If the old column had a unique constraint, add it to the new column in Phase 1 as well. This ensures query performance stays the same.
Can I skip Phase 2 dual writes?
No. If you skip dual writes and go straight from Phase 1 to Phase 3, you backfill old data but new writes after Phase 3 starts won't touch the new column. You'll have inconsistent data. Dual writes are mandatory even though they feel redundant.
What if I need to add constraints (NOT NULL, UNIQUE)?
Add them in Phase 1 as you create the column: `alter table users add column email text not null default 'pending';`. After backfill, the default ensures old rows are valid. Once backfill is done and you're confident, you can tighten validation in your app code (Phase 4 is a good time).
How long should I wait between phases?
Phase 1 → 2: Deploy immediately, no waiting. Phase 2 → 3: Wait until your next release window (if batching deploys). Phase 3 → 4: Wait until backfill finishes (hours to days). Phase 4 → 5: Wait 1–2 weeks of stable reads, then drop. Patience here prevents rollbacks.
What if backfill fails or times out?
Backfill failures are fine — rerun it. Postgres won't fail the `update` statement; it'll just update fewer rows in that batch. The job is idempotent. Keep retrying until the count of nulls in the new column is zero. No rescue needed.
Does this work for adding new columns or only renames?
This pattern works for renames, type changes (e.g. `text` → `uuid`), and moving data. For simple new columns that don't need data from old ones, you can skip phases 2–4 — just add the column (Phase 1) and you're done. For any data-dependent change, follow all five phases.
The Bottom Line
Postgres schema changes on live traffic feel like a scare, but the 5-phase pattern makes them boring. Add the column, dual write, backfill, switch reads, drop the old. No locks, no downtime, no rollbacks (unless you want them). Aidxn ships this weekly without incident. For SaaS with uptime requirements, this is non-negotiable. Ready to scale safely? Check our backend partnership model or dive deeper into Supabase backup strategies for the full resilience picture.