Running a database migration without downtime on a single Postgres box comes down to one thing: knowing which operations take a heavy ACCESS EXCLUSIVE lock and avoiding holding one longer than your app's request timeout. Most common migrations, adding a column, adding a constraint, adding an index, don't have to hold one for long at all. The exceptions are worth knowing before you run into one live.

Why a naive migration takes your app down

Postgres locks a table for the duration of most ALTER TABLE operations. An ACCESS EXCLUSIVE lock, the level most schema changes require by default, blocks every other read and write against that table until the migration finishes. On a small table that's invisible. On a table with real production rows, a migration that scans the whole table under that lock can take long enough that every request touching it starts timing out, which is what actually causes the outage, not the migration itself.

This is the same real setup Docker + Postgres on a single box already covers: one Postgres instance, no replica to fail over to while a migration runs. The lock-duration problem is the whole game.

Three migrations that don't have to cost you anything

Adding a column. As of Postgres 11, adding a column with a non-volatile DEFAULT is fast even on a large table, it evaluates the default once and stores it in the table's metadata instead of rewriting every row:

ALTER TABLE users ADD COLUMN plan_tier text DEFAULT 'free';

That's still true. The advice to avoid DEFAULT on ADD COLUMN for performance reasons is years out of date for a non-volatile value like a literal or NULL.

Adding a NOT NULL or foreign key constraint. Adding either directly requires Postgres to scan the whole table under the heavy lock to verify every existing row. Split it into two statements instead:

ALTER TABLE users ADD CONSTRAINT plan_tier_not_null
  CHECK (plan_tier IS NOT NULL) NOT VALID;
 
ALTER TABLE users VALIDATE CONSTRAINT plan_tier_not_null;

The first statement is nearly instant, it doesn't check existing rows yet. The second does the actual scan, but under a much lighter lock that doesn't block concurrent reads or writes. The same two-step pattern works for foreign keys.

Adding an index. Use CREATE INDEX CONCURRENTLY instead of a plain CREATE INDEX. It takes longer to finish, but it doesn't hold the table locked while it builds.

Where this breaks down

Changing a column's data type is the one operation without a clean trick here. In most cases it rewrites the entire table and every index on it, under a full lock, for the whole duration. A varchar length increase is a documented exception since Postgres treats it as binary-compatible with no rewrite needed, but most real type changes, an int to a bigint, a text to a jsonb, don't get that exception.

For a single-box setup with no replica, there's no trick that makes this free. The real options are: schedule it for a real maintenance window if the table's small enough to make that tolerable, or handle it the way larger teams do, add a new column of the target type, backfill it in batches, cut over the application, then drop the old column, which trades a fast migration for a multi-step, multi-deploy process. Neither is downtime-free by accident. Pick the one that matches how much the table actually matters.

The three techniques above cover the database migration work a small team runs most often. The type-change case is rare enough, and expensive enough when it hits, that it's worth knowing you'll need a real plan for it before the day you're doing it under pressure.