Safe schema changes
Change a live schema without breaking your app: adding, removing and renaming columns, changing types, indexes and constraints on large tables, backfilling data, locks and time limits, and planning for recovery.
Your app keeps running while a migration applies, and the old version of your code may still be serving requests after it. Two rules avoid most outages: change the schema in steps your running code tolerates, and keep each migration short.
The limits a migration runs under
- Each migration may run for 120 seconds and wait for a lock for 15 seconds. A statement that takes longer fails, and the migration changes nothing (Migrations).
- Many schema changes take a lock that blocks reads or writes on the table while they run. On a small table that is milliseconds; on a large one it is the whole rewrite.
- Statements that can't run in a transaction (
create index concurrently,vacuum) are refused in migrations.
Expand, migrate, contract
Expand
Add the new column or table, nullable or with a default. Old code ignores it.
Deploy code that writes both
New code writes the old and the new shape and reads the old one.
Backfill
Copy existing rows into the new shape in batches (below).
Switch reads
Deploy code that reads the new shape.
Contract
Once no running code uses the old column, drop it in a later migration.
Common changes
| Change | Safe way | Watch out for |
|---|---|---|
| Add a column | alter table t add column c text (nullable), or with a constant default — both are instant. | A volatile default (default random(), clock_timestamp()) rewrites the table. |
| Make a column required | Backfill, add check (c is not null) not valid, then validate constraint in a later migration, then set not null (which then skips the scan). | set not null on its own scans the whole table under a lock. |
| Remove a column | Deploy code that no longer uses it, then drop column in a later migration. | Dropping is instant but the data is gone; down SQL can bring back the column, not its values. |
| Rename a column or table | Expand/contract: add the new name, write both, backfill, switch, drop the old. | A plain rename is instant but breaks every running query that uses the old name. |
| Change a type | Add a new column of the new type, backfill, switch, drop the old. | alter column … type rewrites the table under an exclusive lock — on a large table it exceeds the time limit. |
| Add a foreign key or check | add constraint … not valid, then validate constraint in a later migration. | Without not valid, existing rows are checked while writes wait. |
| Add an index | create index in a migration for small and medium tables. | It blocks writes to the table while it builds — see below for large tables. |
| New table | Create it with row-level security enabled and forced, and its policies, in the same migration. | A table without rules is closed to app users; the Security advisor lists it (Security advisor). |
Indexes on large tables
Build the index without blocking writes over a direct connection (Database: connection, compute, disk and backups), then add a migration with if not exists so the history and your other environments match:
create index concurrently if not exists orders_customer_idx on orders (customer_id);If a concurrent build fails it leaves an invalid index; drop it and build it again.
Backfilling data
Don't update millions of rows in one migration — it holds locks for the whole update and runs past the time limit. Update in batches from a script or a function, each batch its own short transaction:
update orders
set total_cents = round(total * 100)
where id in (
select id from orders
where total_cents is null
limit 5000
);When a migration waits on a lock
A migration that can't get its lock within 15 seconds fails with a lock timeout and changes nothing — usually because a long transaction is using the table. Find it in the SQL editor, let it finish (or end it), and run the migration again at a quieter time.
select pid, state, now() - xact_start as open_for, left(query, 80) as query
from pg_stat_activity
where xact_start < now() - interval '1 minute'
order by xact_start;Plan for recovery
- Write down SQL for every migration you may want to undo, and test it on staging.
- Before a destructive change (dropping a table or column, changing a type), take or check a backup (Backups and recovery) — down SQL can't bring data back.
- Apply to staging first, then promote (Environments, staging and deploying to production); promotion checks the whole sequence on production before applying it.