You need to migrate the schema of a 400-million-row table on a live system. Talk me through the plan, the backup you would want first, and how the connection pool affects it.

Lock strength and lock duration are two different problems

The whole discipline of online migration reduces to this pair. ALTER TABLE mostly takes an ACCESS EXCLUSIVE lock, which blocks reads as well as writes, so it is not a matter of writes pausing briefly — SELECT stops too. A lock that strong is nevertheless harmless held for a few milliseconds and catastrophic held while the table is rewritten. So for each step, ask which lock it needs and for how long, and split any step that answers "the strongest one, for the length of a full scan".

There is a second-order effect that causes more incidents than the DDL itself. PostgreSQL lock requests queue, and the queue is ordered. If your ALTER TABLE waits behind a long-running SELECT, every subsequent query on that table queues behind your ALTER, including the fast ones. A statement you expected to take a millisecond takes down the endpoint. The defence is to make the attempt fail fast rather than wait, and retry:

BEGIN;
SET LOCAL lock_timeout = '150ms';   -- give up rather than build a queue
ALTER TABLE orders ADD COLUMN settled_at timestamptz;
COMMIT;

Wrap that in a loop that retries with a short backoff. Failing forty times and succeeding on the forty-first is a completely successful migration; waiting once for thirty seconds is an outage.

Choosing steps that avoid the rewrite

Since PostgreSQL 11, adding a column with a non-volatile default does not rewrite the table: the default is recorded in the catalogue and materialised as rows are next written. That makes ADD COLUMN ... DEFAULT 'pending' a metadata change even at 400 million rows. A volatile default such as now() or gen_random_uuid() does rewrite, so if you want per-row values you add the column without a default and backfill it yourself.

SET NOT NULL used to be the expensive part, because it scans the table under a strong lock to prove no nulls exist. The way around it, and the reason this question is asked, is to prove the invariant separately under a weaker lock. Note where this sits in the sequence: a NOT VALID constraint is not checked against existing rows but is enforced on every insert and update from the moment it exists, so these four statements come after the backfill below and after the application is already writing the column, never before.

-- 1. Cheap: recorded but not checked. Applies to new and modified rows only.
ALTER TABLE orders ADD CONSTRAINT orders_settled_at_nn
  CHECK (settled_at IS NOT NULL) NOT VALID;

-- 2. Slow but takes only SHARE UPDATE EXCLUSIVE, so reads and writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_settled_at_nn;

-- 3. From PostgreSQL 12, the validated CHECK is accepted as proof, so this
--    step no longer scans the table and the strong lock is momentary.
ALTER TABLE orders ALTER COLUMN settled_at SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_settled_at_nn;

The same two-phase shape applies to foreign keys: ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT. Indexes have their own version of it, CREATE INDEX CONCURRENTLY, which makes two passes and never blocks writes. It has real caveats you should volunteer: it cannot run inside a transaction block, so it cannot be bundled with other statements atomically, and if it fails it leaves an invalid index behind that must be dropped explicitly — check pg_index.indisvalid afterwards rather than assuming success. DROP INDEX CONCURRENTLY exists for the reverse.

The backfill

Never one UPDATE. A single statement over 400 million rows holds a snapshot for hours, produces 400 million dead tuples vacuum cannot touch until it commits, generates an enormous volume of WAL, and achieves nothing if it is cancelled at 90 percent. Batch it, commit each batch, and page by key rather than by OFFSET:

-- One batch. Keyset pagination, so cost per batch stays flat.
UPDATE orders
SET    settled_at = created_at
WHERE  order_id IN (
         SELECT order_id FROM orders
         WHERE  settled_at IS NULL AND order_id > $1
         ORDER  BY order_id
         LIMIT  5000
       )
RETURNING order_id;   -- the driver takes max(order_id) as the next $1

Between batches, sleep briefly and check two things: replication lag, because the WAL you are producing has to reach the standbys, and the dead-tuple count on the table, because you are creating garbage faster than autovacuum's default settings expect. Make the loop resumable and idempotent — the settled_at IS NULL predicate already gives you that — because it will be interrupted.

Making it deployable in both directions

Sequence the release so that no single deployment contains both a code change and an incompatible schema change. Add the column and deploy code that tolerates it being null. Backfill. Deploy code that writes it. Only then, in a later release, add the constraint and remove the old path. This is the expand-and-contract pattern, and its point is that at every intermediate state both the old and the new application version work against the current schema, so a rollback is a deployment rather than a recovery. Destructive steps go last and separately: dropping a column is a metadata operation, but it is the one step you cannot undo with a deploy.

A backup is a rehearsed restore

The question is not what backup schedule you have, it is when you last restored from it and how long that took. Answers that name a tool and stop are graded as not having done this.

At this size pg_dump is a logical export, not a backup strategy: it takes hours, and restoring it takes longer because every index is rebuilt. The physical answer is a base backup plus continuous WAL archiving, which is what gives you point-in-time recovery — restoring to 14:32, one minute before someone ran an unqualified DELETE. State the recovery objectives as numbers first: an RPO of five minutes and an RTO of one hour are met by different infrastructure than an RPO of zero.

Three things must be true, and rehearsal is the only way to know they are. The restore has been performed end to end on a separate host with the elapsed time written down, because an untimed restore is an unknown RTO. It has been verified by more than an exit code: row counts on the largest tables and the application booted against it. And it has been tested to an arbitrary past timestamp rather than only to the latest available WAL, because recovering to a point mid-incident is the case you need and it is the one that exposes a broken restore_command or a missing extension on the target host.

Two clarifications worth making unprompted. A streaming replica is not a backup: it faithfully reproduces your DROP TABLE in seconds. And backups the production credentials can delete are no protection against those credentials being compromised, so retention needs immutability or a separate trust boundary.

Connection pooling, and why it belongs in this answer

PostgreSQL forks a process per connection. Each backend carries its own memory, appears in every snapshot computation, and participates in lock bookkeeping, so connections are not cheap objects to leave lying around. Beyond a certain count throughput does not merely plateau, it declines, because the machine spends its time context switching and contending on shared structures. Derive the pool size from the hardware — cores plus effective disk concurrency, so typically tens rather than hundreds — not from how many requests you hope to serve at once. A queue in front of a small pool completes more work than a large pool thrashing.

The arithmetic people get wrong is that pools multiply. Twenty application instances each holding a pool of twenty is 400 backends, and nobody configured 400 anywhere. That is the case for a server-side pooler such as PgBouncer in transaction mode, which assigns a backend only for the duration of a transaction. The trade is that anything with session state breaks or needs care: SET outside a transaction, session-level advisory locks, LISTEN/NOTIFY, and plain temporary tables. Server-side prepared statements were in that list until PgBouncer 1.21 added protocol-level support for them in transaction mode, so check your version before assuming either way.

The link back to the migration is direct. A pooled connection sitting idle in transaction holds locks, which is precisely what makes your ALTER TABLE wait and build the queue described at the start. Before a migration, know what your pooler's longest-lived transaction is, and set idle_in_transaction_session_timeout so that the answer has a ceiling.