Zero-Downtime Postgres Schema Changes in a Live Supabase App

You can change a Postgres schema under a live Supabase app without downtime if every change you make still works with the code that is already running. The expand-and-contract pattern does this. You add the new shape first, move data and clients over step by step, and remove the old shape only when nothing uses it.

The SQL is not the hard part. The hard part is that your web app, your edge functions and several versions of your mobile app all use the same database at once.

Why running the migration in one go breaks things

Most migration outages in Supabase projects have one of two causes.

Lock queues. An ALTER TABLE needs an ACCESS EXCLUSIVE lock, even when the change itself is instant. If a long-running query already holds a lock on the table, your ALTER has to wait. Every new query on that table then waits behind the ALTER. A change that should take one millisecond becomes a minute of timeouts.

Deployed clients. supabase-js and PostgREST ask for columns by name. If you rename full_name to display_name, every client that still selects full_name starts failing right away. You can redeploy the web app in minutes. You cannot make every user update their mobile app.

Expand-and-contract deals with both problems. Each step is small, needs only short locks, and keeps older code working.

The four phases

1. Expand: add new columns, tables or indexes. Remove nothing.

2. Migrate: backfill the data and keep the old and new shapes in sync.

3. Switch: ship client code that reads and writes the new shape.

4. Contract: once no client uses the old shape, drop it.

Each phase gets its own migration file and its own deploy. The example below renames profiles.full_name to profiles.display_name.

Phase 1: Expand safely

-- supabase/migrations/20261008090000_expand_display_name.sql
set lock_timeout = '3s';

alter table public.profiles
  add column if not exists display_name text;

Most teams skip the lock_timeout line. With it, if the ALTER cannot get its lock within 3 seconds, it fails instead of blocking traffic, and you try again a few minutes later. A failed migration is easy to retry. A queue of blocked requests is an outage.

Rules for adding columns:

• A nullable column with no default only changes metadata, so it is instant.

• On Postgres 11 and later, a constant default such as default 'free' is instant too.

• A volatile default such as default gen_random_uuid() rewrites the whole table. Add the column without the default, backfill it, then set the default.

• Do not add NOT NULL yet. Old clients will insert rows without the new column.

Phase 2: Keep both columns in sync

During the transition, old clients write full_name and new clients write display_name. A trigger copies each change to the other column, so neither side reads stale data.

-- supabase/migrations/20261008090100_sync_display_name.sql
create or replace function public.sync_display_name()
returns trigger language plpgsql as $$
begin
  if tg_op = 'INSERT' then
    new.display_name := coalesce(new.display_name, new.full_name);
    new.full_name := coalesce(new.full_name, new.display_name);
  elsif new.display_name is distinct from old.display_name then
    new.full_name := new.display_name;
  elsif new.full_name is distinct from old.full_name then
    new.display_name := new.full_name;
  end if;
  return new;
end $$;

create trigger profiles_sync_display_name
  before insert or update on public.profiles
  for each row execute function public.sync_display_name();

Backfill in batches

A single update profiles set display_name = full_name locks every row it touches until it finishes. It also bloats the table and can increase replication lag. Update in batches instead, and commit after each one.

-- supabase/migrations/20261008090150_backfill_procedure.sql
create or replace procedure public.backfill_display_name(batch_size int default 5000)
language plpgsql as $$
declare
  updated int;
begin
  loop
    update public.profiles p
       set display_name = p.full_name
     where p.id in (
       select id from public.profiles
        where display_name is null and full_name is not null
        limit batch_size
        for update skip locked
     );
    get diagnostics updated = row_count;
    exit when updated = 0;
    commit;
    perform pg_sleep(0.1);
  end loop;
end $$;

Run call public.backfill_display_name(); from psql outside an explicit transaction. Inside a transaction, the commit in the procedure fails. While it runs, watch CPU and replication lag in the Supabase dashboard, and use a smaller batch if either one rises. If your role has a statement_timeout, raise it for that session first, because the whole call may count as one statement.

Build indexes concurrently

-- supabase/migrations/20261008090200_index_display_name.sql
create index concurrently if not exists profiles_display_name_idx
  on public.profiles (display_name);

A plain create index blocks writes until it finishes. With concurrently, writes keep going and the build takes longer. Two things to watch for:

• It cannot run inside a transaction block. Make it the only statement in its migration file. If your migration runner wraps each file in a transaction, run this one through psql.

• If the build fails partway, it leaves an INVALID index that still slows down writes. Find it and clean it up:

select indexrelid::regclass as index_name
from pg_index where not indisvalid;
-- then: drop index concurrently profiles_display_name_idx; and retry

Adding NOT NULL without a long lock

alter table ... set not null scans the whole table while it holds ACCESS EXCLUSIVE. Add a check constraint first:

set lock_timeout = '3s';
alter table public.profiles
  add constraint display_name_not_null
  check (display_name is not null) not valid;

alter table public.profiles validate constraint display_name_not_null;

alter table public.profiles alter column display_name set not null;
alter table public.profiles drop constraint display_name_not_null;

Adding the constraint with not valid is instant. validate scans the table under a weaker lock that still allows reads and writes. On Postgres 12 and later, set not null sees the validated constraint and skips its own scan. You can use the same not valid then validate steps for foreign keys.

Only do this once every write fills in display_name, either because clients send it or because the trigger copies it.

Phase 3: Switch the clients

Ship code that reads and writes display_name. In a Supabase project, this covers more than the app itself:

• Regenerate types with supabase gen types so TypeScript flags old references.

• Update any RLS policies, views and database functions that mention full_name.

• Update edge functions, webhooks and scheduled jobs.

• If PostgREST does not see the new column, run notify pgrst, 'reload schema';.

For mobile apps, this phase lasts as long as people still use old versions. Track app versions in your analytics, and think about a minimum-version check that asks users to update.

Phase 4: Contract

Only when nothing reads the old column:

-- supabase/migrations/20261115090000_contract_full_name.sql
set lock_timeout = '3s';
drop trigger if exists profiles_sync_display_name on public.profiles;
drop function if exists public.sync_display_name();
alter table public.profiles drop column if exists full_name;

To confirm nothing reads it, search pg_stat_statements for queries that mention full_name, grep every repo, and check your API logs. Then wait at least one full release cycle. Dropping a column is the only step in this process that is hard to undo.

Pre-deploy checklist

Go through this before every migration that touches a live table:

• [ ] Old client code still works after this migration. If not, split it into smaller steps.

• [ ] lock_timeout is set at the top of the file.

• [ ] No long-running transactions are open on the table (check pg_stat_activity).

• [ ] New columns are nullable or have a constant default.

• [ ] Indexes use concurrently, each in its own file.

• [ ] Constraints are added as not valid and validated in a separate step.

• [ ] Large updates run in batches, not as one statement.

• [ ] RLS policies, views, functions and edge functions are checked for renamed or dropped columns.

• [ ] The migration was tested on a branch or staging copy with a realistic amount of data.

• [ ] You know how to roll back this step without restoring a backup.

• [ ] Columns are dropped only in a contract migration, at least one release after the switch.

Decision rule

If a change can break a client that is already deployed, it needs at least two deploys. Renames, type changes, splitting a column and adding a required field all need two. Adding a nullable column or building an index concurrently needs only one.

This takes longer than a single migration. In return, you can ship schema changes in the middle of the working day without scheduling a maintenance window.

For what else changes as a Supabase app grows, see the Geminate Solutions guide to Supabase production scaling.