Zero-Downtime Postgres Migrations on an SMB Budget

Zero-Downtime Postgres Migrations on an SMB Budget

Most schema migrations don't cause outages because the SQL is wrong — they cause outages because of what Postgres has to lock while running it. A small team without a dedicated platform or database reliability group doesn't need a custom tooling stack to avoid this. It needs a short list of safe defaults and the discipline to follow them every time a migration touches a production table.

Why Simple ALTER Statements Get Dangerous at Scale

Adding a column with a `DEFAULT` value used to rewrite the entire table, taking an `ACCESS EXCLUSIVE` lock for the duration. Modern Postgres versions handle constant defaults without a rewrite, which removes one common source of pain — but plenty of other operations still lock aggressively. Adding a `NOT NULL` constraint, changing a column type, or adding a foreign key can all require a full table scan under a lock that blocks reads and writes. On a small table this is invisible. On a busy orders or events table, it's the difference between a quiet deploy and a page at 2 a.m.

The fix isn't avoiding these changes — it's breaking them into steps that each take a brief, cheap lock instead of one step that takes a long, expensive one.

A Safer Pattern for Adding and Backfilling Columns

The reliable sequence looks like this:

1. Add the column as nullable, with no default, or with a constant default on versions that support it without a rewrite.

2. Backfill existing rows in small batches, outside of a single giant transaction. Batching by primary key range keeps individual update statements short and avoids holding row locks against concurrent writes for too long.

3. Add any `NOT NULL` or `CHECK` constraint as `NOT VALID` first, which skips validating existing rows under lock.

4. Run `VALIDATE CONSTRAINT` separately. This still scans the table, but it only takes a lock that permits concurrent reads and writes — a very different risk profile than a blocking rewrite.

The same batching principle applies to backfills triggered by application logic: throttle them, monitor replication lag while they run, and give the database room to breathe between batches rather than racing to finish.

Dropping Indexes and Other Cleanup Work

Index changes are the other place teams get burned. Building an index with `CREATE INDEX CONCURRENTLY` avoids locking writes, but it's not transactional — a failed concurrent build can leave an invalid index behind that needs to be dropped and retried. Dropping an index is generally cheap, but dropping one that a hot query path still depends on is a self-inflicted outage with a delay: nothing breaks until the next slow-query spike shows up in production. Before dropping, confirm with `pg_stat_user_indexes` or query logs that nothing active is relying on it, and drop during low-traffic windows anyway, since caution here costs nothing.

Making This Repeatable Without a Platform Team

The common thread across all of these steps is that they're mechanical, not clever — which makes them good candidates for a checklist or a thin wrapper script rather than institutional memory. Teams that keep getting burned by migrations usually aren't missing expertise; they're missing a habit of checking lock behavior and query plans before shipping, not after. Tools like Querk that surface slow queries and lock contention after a migration ships can catch the cases a checklist misses, turning a potential pager incident into a routine query optimization fix instead.