Zero-Downtime Postgres Migrations on an SMB Budget

Zero-Downtime Postgres Migrations on an SMB Budget

Most schema migration horror stories share a common thread: a team assumed a simple `ALTER TABLE` would be simple. On a small database, it often is. On a production table with millions of rows and live traffic, the same statement can hold locks long enough to queue up connections, blow past application timeouts, and turn a Tuesday afternoon into an incident. For SMB engineering teams without a dedicated platform or database reliability team, the fix isn't more headcount — it's understanding which operations are safe by default and which ones need to be broken into smaller steps.

Why "Simple" Schema Changes Aren't Simple

Postgres migrations get dangerous when they require an `ACCESS EXCLUSIVE` lock, even briefly. Adding a column with a non-null default used to rewrite the entire table in older Postgres versions; modern Postgres (11+) handles constant defaults without a rewrite, but adding a column with a volatile default, changing a column type, or adding a `NOT NULL` constraint directly can still trigger a full table scan and lock. The danger isn't the DDL statement itself — it's the duration that statement holds a lock that blocks reads and writes.

The practical rule: separate the fast, metadata-only change from the slow, data-touching work. Add the column as nullable first. Backfill in batches afterward. Add constraints last, using `NOT VALID` plus a separate `VALIDATE CONSTRAINT` step so validation doesn't block concurrent writes.

Backfilling Without Blocking

Backfilling is where most timeouts happen. Updating every row in a single transaction locks those rows for the duration and can bloat the table with dead tuples, hurting query performance well after the migration finishes. Instead, backfill in small batches — a few thousand rows at a time — using a primary key range or `WHERE id BETWEEN` clauses, with short pauses or explicit commits between batches. This keeps individual transactions short, lets autovacuum keep up, and gives the application room to breathe under concurrent load.

It's also worth checking how query plans shift once new indexes or columns are introduced, since a backfill job competing for I/O with production traffic can quietly degrade response times even if no lock is held.

Indexes: Build Concurrently, Drop Carefully

`CREATE INDEX` takes a lock that blocks writes for the table's duration unless `CONCURRENTLY` is used. It's slower and can't run inside a transaction block, but it's the only safe option for production tables. Dropping an index is lower-risk but not zero-risk: dropping the wrong index, or one still in use by a foreign key or unique constraint, can silently regress query performance until someone notices slow dashboards days later. Before dropping, confirm actual usage through `pg_stat_user_indexes` rather than assumptions about what "should" be redundant.

Building a Repeatable Playbook

The goal for a small team isn't heroics during each migration — it's a repeatable checklist: nullable-first column adds, batched backfills, concurrent index builds, `NOT VALID` constraints validated separately, and monitoring lock wait times before and after deploy. Teams using query performance monitoring as part of their normal workflow tend to catch lock contention and slow backfills earlier, since the same visibility used for everyday slow-query triage also flags migration-induced regressions. Tools like Querk fit naturally into that loop by surfacing the query and lock behavior that raw migration logs won't show.

Codifying these patterns into migration tooling or code review templates turns "zero