# ALTER TABLE takes forever on a large table

> Why ALTER TABLE hangs or locks your app on large tables, which changes rewrite the whole table, and safe patterns using lock_timeout, NOT VALID constraints and concurrent indexes.

Source: https://supahost.app/kb/slow-alter-table · Updated 2026-11-11

## 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:

| Change | Cost on a large table |
|---|---|
| `add column` with no default, or a constant default | Instant |
| `add column ... default gen_random_uuid()` (volatile default) | Rewrites the table |
| `alter column ... type` (for example `int` to `bigint`) | Rewrites the table and its indexes |
| `alter column ... set not null` | Scans the whole table |
| `add foreign key` / `add check` | Scans the whole table |
| `create index` (without `concurrently`) | Blocks writes until built |
| `rename`, `drop column`, `set default` | Instant |

## 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:

   ```sql
   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](/kb/blocked-queries).

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

   ```sql
   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:

   ```sql
   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:

   ```sql
   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](/docs/database/migrations)
- [Finding queries blocked by locks](/kb/blocked-queries)
- [Indexes](/docs/database/indexes)
- [Canceling statement due to statement timeout](/kb/statement-timeout)