ALTER TABLE takes forever on a large table

What you see

An ALTER TABLE on a big table runs for minutes, or seems to hang. While it runs, your app slows down or stops: queries on that table pile up, and supabase-js calls time out. Sometimes it ends with canceling statement due to statement timeout.

Why it happens

There are two separate problems that look the same.

1. Waiting for a lock. Most ALTER TABLE forms need an exclusive lock on the table. If any transaction is using the table (a slow report, or a session left idle in transaction), the ALTER waits. Worse, every new query on the table queues behind the waiting ALTER. A change that would take a millisecond can freeze the table for as long as the oldest transaction runs.

2. Rewriting or scanning the table. Some changes touch every row while holding the lock:

How to fix it

1. Make DDL give up instead of queueing. Set a short lock_timeout so a blocked ALTER fails fast and doesn't stall your app. Retry it a moment later:

   set lock_timeout = '3s';
   alter table public.bookings add column notes text;
   

2. Find what's holding the lock if it keeps timing out. See Finding queries blocked by locks.

3. Build indexes concurrently. This allows reads and writes while the index builds:

   create index concurrently if not exists bookings_guest_idx on public.bookings (guest_id);
   

   concurrently can't run inside a transaction. Run it as a statement on its own.

4. Add constraints in two steps. not valid adds the constraint instantly for new rows; validate checks old rows without blocking writes:

   alter table public.bookings
     add constraint bookings_venue_fk foreign key (venue_id)
     references public.venues (id) not valid;
   
   alter table public.bookings validate constraint bookings_venue_fk;
   

5. Set NOT NULL without a long lock. A validated check constraint lets Postgres skip the full scan:

   alter table public.bookings add constraint bookings_guest_not_null check (guest_id is not null) not valid;
   alter table public.bookings validate constraint bookings_guest_not_null;
   alter table public.bookings alter column guest_id set not null;
   alter table public.bookings drop constraint bookings_guest_not_null;
   

6. Avoid rewrites for type changes. Add a new column, backfill it in batches, then swap names in one quick transaction.

7. If a step legitimately needs longer than your timeout, raise it for that session only: set statement_timeout = '30min';.

On SupaHost

• MCP apply_migration runs the whole migration as one transaction, so create index concurrently fails there. Run concurrent index builds in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio) as a single statement, or with psql through the SSH tunnel on Business.
• Each project's Data API has 5 database connections. A table locked by a waiting ALTER can tie up all 5 within seconds, and then every API request for that project waits, not only those touching the table. A short lock_timeout prevents this.
• Other projects on the instance have separate databases, so they aren't affected by the lock, though a big rewrite does use the shared CPU and disk.

Related

• SQL migrations
• Finding queries blocked by locks
• Indexes
• Canceling statement due to statement timeout