Zero-Downtime Supabase Migrations Using Expand and Contract

When you change a production database in a Supabase project, old and new versions of the code can run at the same time during deployment. A migration changes database structure, including tables and columns, or changes the data stored in those tables. If a migration removes or renames a column while active code still reads or writes it, requests can fail.

Expand and contract lowers this risk by adding the replacement before removing the original. First, you add the new column. Then you update code and copy data to it. After no active code depends on the old column, you remove it. This sequence lets you release changes in stages, but PostgreSQL can still lock tables, copying existing data can add database load, and other operational risks remain.

Replace a column in compatible stages

Suppose public.profiles.full_name needs to become public.profiles.display_name. Renaming the column in one migration would break code that still queries full_name. Instead, keep both columns during the transition.

Expand: add the replacement

Start with a migration that adds the new column while leaving the old one intact:

alter table public.profiles
  add column display_name text;

This example's nullable column lets rows have no value. Choose its data type and nullability to fit your data model. Also check the locking behavior for the PostgreSQL version running in your Supabase project. PostgreSQL documents that different ALTER TABLE operations take different locks, and many forms require ACCESS EXCLUSIVE, which conflicts with ordinary reads. PostgreSQL’s ALTER TABLE documentation describes the lock requirements by operation.

Store database structure changes in reviewed migration files, then apply those files through your production deployment path. Supabase’s database migration workflow supports versioning changes and applying pending migrations with the CLI.

Migrate: update every writer before relying on the new value

After the new column exists, deploy code that still reads full_name and writes to both columns. This lets old and new code versions work during the rollout.

Before backfilling, identify every writer. Besides the main service, check workers, scheduled jobs, Edge Functions, admin scripts, and clients that write through Supabase APIs. Older code that writes only full_name can leave display_name stale. Wait until those writers have been retired before copying data, or use a tested database mechanism that keeps the values synchronized while older writers remain active.

For a large table, copy existing values into the new column in batches instead of updating every row in one statement. This copy of existing data is called a backfill. The following statement is an example: it assumes id can order the rows and that full_name holds the correct values. The shown batch size is illustrative and does not apply to every table.

with batch as (
  select ctid
  from public.profiles
  where display_name is null
  order by id
  limit 1000
  for update skip locked
)
update public.profiles as p
set display_name = p.full_name
from batch
where p.ctid = batch.ctid;

Run each batch as a separate statement and commit it. While the backfill runs, monitor database load and replication lag, the delay before changes reach database replicas. ctid identifies rows for this statement; it is not a stable identifier to store or reuse across statements. If SKIP LOCKED skips rows held by other transactions, run a final pass or otherwise check that no rows that meet the conditions remain. PostgreSQL’s guidance on large updates discusses batching and the effects updates can have on table bloat, replication lag, and lock contention.

Before switching readers, check that the new values meet the intended rule. For a direct copy, these counts reveal missing values and mismatches:

select
  count(*) filter (where display_name is null) as missing,
  count(*) filter (
    where display_name is distinct from full_name
  ) as different
from public.profiles;

If the new value transforms the old one, compare against that transformation instead. Once the data checks pass, deploy code that reads display_name. Keep writing both fields until every reader uses the new column and every writer writes to it.

Contract: remove the old column later

Remove full_name in a separate migration after no active code, client, view, policy, or other database object uses it:

alter table public.profiles
  drop column full_name;

Treat this cleanup as a breaking change. Supabase clients can access tables through APIs, so a separate client release or direct API writer may still reference the old field after the main service has moved on. PostgreSQL also notes that dependent objects can affect a column drop. Verify those dependencies before deploying the contract migration.

Check Supabase-specific consumers

A column change can affect more than your code. Supabase projects may expose tables through the REST API, an HTTP interface for accessing data. Table access can also depend on row-level security (RLS), which sets rules for the rows a user can access. Review policies and permissions that refer to the changed columns, and test both allowed and denied access. Supabase’s RLS documentation explains how policies work alongside grants.

Also review views and the TypeScript types generated for clients. Supabase views can bypass RLS by default unless configured appropriately, so review the security and compatibility of any view that exposes the old column. When the database structure changes, regenerate and review Supabase TypeScript types alongside the code that uses them. If an API does not recognize a new column, table, view, or function, check whether PostgREST’s schema cache, its stored description of the database structure, needs attention using the Supabase troubleshooting guidance.

Match the migration to its PostgreSQL operation

Expand and contract helps old and new code work during a change, but PostgreSQL can still lock the database. PostgreSQL lock requests can wait behind transactions that hold conflicting locks. Keep each database structure change small. Check its lock requirements for the PostgreSQL version you use, and plan how the migration runner will handle a wait or failure. PostgreSQL’s locking documentation explains which lock modes conflict.

Some changes work better when you add a rule first and check existing data later. In PostgreSQL 17, you can add eligible CHECK and foreign-key constraints, rules that limit which data is allowed, as NOT VALID. This skips the initial scan of existing rows, while still enforcing the rule on new inserts and updates. Later, VALIDATE CONSTRAINT checks the existing rows. This approach does not work for every constraint type, so confirm the specific operation in the PostgreSQL documentation.

Index creation also affects how you deploy changes. PostgreSQL’s CREATE INDEX CONCURRENTLY avoids blocking ordinary writes while it builds an index. The concurrent build takes longer and does more work. It also cannot run inside a transaction block, where a group of statements is treated as one unit. Supabase’s CLI workflow documentation describes how the CLI handles this operation. Test migrations through the same deployment path you use in production. Success with one runner does not show that another runner handles transaction requirements the same way.

Test the rollout before production

Reset a local database and apply the migration history from the beginning to check that migrations run in order and produce the same result. Supabase documents this approach in its local development workflow. Then test the staged release against a non-production environment, including the backfill and the code versions that will overlap during deployment.

A clean local reset does not show how a backfill will perform on a production-sized database. Test the migration with data that represents production, watch database health while it runs, and choose a batch size based on the load you observe. Supabase preview branches can help test changes, but resetting a branch drops its data; branch resets are not a production rollback method. See Supabase’s branch guidance.

Keep the old column until the new read and write paths have been verified and the rollback plan no longer depends on it. Expand and contract gives you time to observe the new path before cleanup, but dropping the old column still requires a deliberate decision.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com