> ## Content Index
> Fetch the complete content index at: https://stack.ghostcms.templates.codememory.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Zero-downtime migrations in Postgres
- URL: https://stack.ghostcms.templates.codememory.com/zero-downtime-migrations-in-postgres/
- Published: 2026-02-11T09:00:00.000Z
- Updated: 2026-02-11T09:00:00.000Z
- Description: Six rules for changing a schema while a thousand queries a second keep running.
- Author: Tomás Rivera
- Tags: Databases, #series Postgres in practice, #Import 2026-09-30 16:17

A migration that works on your laptop can take a production database offline. The statement is the same. The difference is ten million rows and a thousand queries a second that all want the same table. Here are the rules we follow to change a schema with nobody noticing.

![The safest migration is the one that nobody notices.](https://stack.ghostcms.templates.codememory.com/content/images/2026/09/mig-deploy-1.jpg)

The safest migration is the one that nobody notices.

## Why migrations cause outages

Most schema changes need a lock on the table. PostgreSQL's strongest lock, `ACCESS EXCLUSIVE`, blocks every read and write. If the change is quick, nobody notices. If it rewrites the table, everything waits.

There is a second trap. Your `ALTER TABLE` must wait for running queries to finish before it gets its lock. While it waits, every new query queues behind it. One slow report can turn a fast migration into a full stop.

## Rule 1: set a lock timeout

Always tell the database to give up if it cannot get the lock quickly.

```sql
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
```

If the lock is not free within three seconds, the statement fails and the queue clears. You try again a minute later. A failed migration is a small problem. A blocked table is an incident.

## Rule 2: add columns the safe way

Adding a nullable column is instant. Adding a column with a constant default is also instant on modern PostgreSQL, because the default is stored in the catalog and not written to each row.

```sql
ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';
```

What is not safe is adding a column with a default that must be computed for each row, such as `now()` or `gen_random_uuid()` on older versions, or adding `NOT NULL` in the same statement on a large table.

## Rule 3: backfill in batches

To fill a new column for existing rows, never run one giant `UPDATE`. It holds locks for minutes and bloats the table. Update a few thousand rows at a time.

```sql
UPDATE users SET plan = 'free'
WHERE id IN (
  SELECT id FROM users
  WHERE plan IS NULL
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
);
```

Run it in a loop with a short pause until it changes zero rows. Each batch commits on its own, so replicas keep up and other queries are not blocked.

> Small batches are slower in total and faster for everyone else. That is the trade you want.  
>  
> **Tomás Rivera**

## Rule 4: add constraints in two steps

A `NOT NULL` or a foreign key must check every row, under a lock. Split the work: add the constraint without checking old rows, then validate it separately.

```sql
ALTER TABLE users
  ADD CONSTRAINT users_plan_not_null CHECK (plan IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_plan_not_null;
```

The first statement is instant, and new rows are checked from that moment. The second scans the table with a light lock that does not block reads or writes.

The same pattern works for foreign keys.

```sql
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
```

## Rule 5: build indexes concurrently

A plain `CREATE INDEX` blocks writes for as long as it runs.

```sql
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
```

The concurrent form takes about twice as long and lets writes continue. It cannot run inside a transaction, so configure your migration tool to allow that.

⚠️

**Check for invalid indexes.** If a concurrent build fails, it leaves an index marked invalid. Drop it and build again. Find them with `SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;`

## Rule 6: expand, then contract

Never rename or drop something the running code still uses. Make every change in steps that are each safe to deploy and safe to roll back. To rename a column from `fullname` to `display_name`:

1. **Expand:** add the new column.
2. **Write both:** deploy code that writes to old and new.
3. **Backfill:** copy old values to the new column in batches.
4. **Read new:** deploy code that reads the new column.
5. **Contract:** stop writing the old column, then drop it.

```diff
- SELECT id, fullname FROM users WHERE id = $1;
+ SELECT id, display_name FROM users WHERE id = $1;
```

It is five deploys for what looks like a one-line change. At every step, the old code and the new code both work, which means a rollback is always possible.

## Changes to avoid on large tables

| Change                                  | Problem             | Do this                                |
| --------------------------------------- | ------------------- | -------------------------------------- |
| Change a column type                    | Rewrites the table  | New column, backfill, swap             |
| ADD COLUMN ... NOT NULL with no default | Fails or scans      | Add nullable, backfill, add constraint |
| CREATE INDEX                            | Blocks writes       | CONCURRENTLY                           |
| Rename a column                         | Breaks running code | Expand and contract                    |
| VACUUM FULL                             | Locks the table     | Use pg\_repack                         |

## A checklist for every migration

- Is there a `lock_timeout`?
- Does any statement rewrite the table or scan it under a strong lock?
- Does the old version of the application still work after this runs?
- Can we roll back the code without rolling back the schema?
- Has it been tested against a copy with production-sized data?

#### How do I know whether a statement rewrites the table?

Test it on a copy of production data and time it. As a rule, changing a type, adding a volatile default or changing storage settings rewrites the table.

#### Do I need a special tool?

No. Tools that lint migrations help a team stay consistent, and the rules in this post are what they enforce.

## The idea behind the rules

Every rule comes from one idea: keep strong locks short and do the slow work under weak locks. If you remember only that, you can work out the safe way to make almost any change.