Postgres Add Column: The Safe Way on a Live Table

Adding a nullable column in modern PostgreSQL is nearly instant. Adding a NOT NULL column with a default used to rewrite the whole table, and the habits from that era still cause outages.

The syntax for adding a column takes thirty seconds to learn. What takes longer is knowing which variants are safe against a table carrying live traffic, because some forms acquire a lock and hold it while rewriting every row.

PostgreSQL 11 improved this considerably. But the constraint that still bites — adding NOT NULL to a table that already has rows — has not gone away, and the safe path is a sequence of steps rather than a single statement.

The basic syntax
What actually locks, and for how long
The lock queue problem nobody expects
Adding a NOT NULL column without downtime
Adding a column with an index or a foreign key
How this fits the rest of the stack
FAQ

The basic syntax

One column, several columns, or conditionally if it does not already exist.

-- Single column
ALTER TABLE users ADD COLUMN last_login timestamptz;

-- Several in one statement, one lock acquisition
ALTER TABLE users
  ADD COLUMN last_login  timestamptz,
  ADD COLUMN login_count integer DEFAULT 0,
  ADD COLUMN is_verified boolean DEFAULT false;

-- Idempotent, useful in migrations that may re-run
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login timestamptz;

New columns always append to the end of the table. PostgreSQL has no AFTER column clause and no way to reorder — if column order matters to you, it should not, because SELECT * in application code is the actual problem.

Batching several additions into one statement is worth doing. Each ALTER TABLE takes an ACCESS EXCLUSIVE lock; doing three separately means acquiring it three times, and each acquisition is a chance to queue behind a long-running query.

What actually locks, and for how long

Every ALTER TABLE ADD COLUMN takes an ACCESS EXCLUSIVE lock. That is the strongest lock PostgreSQL has: it blocks reads, writes, and everything else on that table for the duration.

The critical question is how long it holds. Since PostgreSQL 11, adding a column with a constant default only updates the catalogue — the default is stored as metadata and applied on read for existing rows. No table rewrite, lock held for milliseconds regardless of table size.

Before version 11, that same statement rewrote every row. Plenty of advice still circulating assumes this, which is why you see people recommending you never add a default. On a modern version that advice is obsolete for constant defaults.

Nullable column, no default: catalogue-only, fast on any table size
Column with a constant default: catalogue-only since PG 11, fast
Column with a volatile default such as now() or gen_random_uuid(): full table rewrite
Column with NOT NULL and no default: fails outright if the table has rows
Adding a foreign key reference: requires a validation scan of the table

The volatile-default case is the trap. DEFAULT now() must produce a different value per row, so PostgreSQL cannot store one value in the catalogue and has to write every row. On a large table that is a long rewrite under the strongest lock available.

The lock queue problem nobody expects

Here is the failure that turns a fast migration into an outage. Your ALTER TABLE is instant. It still takes down the table for a minute.

The mechanism: a long-running query holds a lock on the table. Your ALTER requests ACCESS EXCLUSIVE and waits. Every subsequent query — including trivial reads — queues behind your ALTER, because lock requests are ordered. The table is now effectively unavailable until the original slow query finishes.

The fix is a lock timeout. Fail fast rather than blocking the world.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE users ADD COLUMN last_login timestamptz;
COMMIT;

If the lock cannot be acquired within three seconds the statement errors and rolls back, leaving traffic untouched. Retry in a loop until a quiet moment gives you the lock. This one line belongs in every migration that touches a busy table.

Adding a NOT NULL column without downtime

You cannot add NOT NULL to a populated table in one statement — there is nothing to put in the existing rows, so PostgreSQL refuses. Adding it with a default works on modern versions, but if the value must be computed per row, you need the multi-step approach.

Add the column as nullable. Fast, catalogue-only.
Deploy application code that writes the new column on every insert and update. Both old and new rows are now correct going forward.
Backfill existing rows in batches, committing between batches so you never hold a long transaction.
Add a NOT VALID check constraint asserting the column is not null. This is instant because it skips verifying existing rows.
Run VALIDATE CONSTRAINT, which scans the table under a weak lock that does not block reads or writes.
Optionally promote to a real NOT NULL, which PostgreSQL 12 and later can do cheaply by trusting the validated constraint.

-- Step 1
ALTER TABLE users ADD COLUMN tenant_id bigint;

-- Step 3: backfill in batches, not one giant UPDATE
UPDATE users SET tenant_id = derive_tenant(id)
WHERE tenant_id IS NULL AND id IN (
  SELECT id FROM users WHERE tenant_id IS NULL LIMIT 5000
);

-- Steps 4 and 5
ALTER TABLE users
  ADD CONSTRAINT users_tenant_id_not_null
  CHECK (tenant_id IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_tenant_id_not_null;

The batched backfill matters. A single UPDATE across ten million rows holds one enormous transaction, bloats the table with dead tuples, and can block vacuum for the duration. Batches of a few thousand with a commit between each keeps the system healthy.

Adding a column with an index or a foreign key

Two follow-on operations commonly accompany a new column, and both have a concurrent variant that avoids blocking writes.

-- Index: CONCURRENTLY cannot run inside a transaction block
CREATE INDEX CONCURRENTLY users_tenant_id_idx ON users (tenant_id);

-- Foreign key: add unvalidated, then validate under a weaker lock
ALTER TABLE users
  ADD CONSTRAINT users_tenant_fk FOREIGN KEY (tenant_id)
  REFERENCES tenants (id) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_tenant_fk;

CREATE INDEX CONCURRENTLY takes longer and scans the table twice, but it does not block writes. It also cannot run inside a transaction, which means it cannot go in a migration file that wraps everything in BEGIN — a common source of confusing errors in ORM migration tooling.

If a concurrent index build fails partway it leaves an invalid index behind. Check for those with a query against pg_index where indisvalid is false, and drop them before retrying.

How this fits the rest of the stack

Schema changes are the moment a database stops being an abstraction. Knowing your table sizes, your lock behaviour, and your connection ceiling before running a migration is the difference between a deploy and an incident. RunxBuild managed Postgres instances come with backups configured before the first migration runs, so a bad ALTER is recoverable rather than final — and the RunxBuild hosting calculator shows the database and its storage as separate line items so the cost of the safety margin is visible up front.

Useful related references:

FAQ

Does ADD COLUMN lock the table in PostgreSQL?

It takes an ACCESS EXCLUSIVE lock, but for a nullable column or one with a constant default on PostgreSQL 11+, it is held only for milliseconds because only the catalogue is updated. A volatile default forces a full table rewrite under that lock.

Can I add a NOT NULL column to a table with existing rows?

Not in a single statement without a default, because existing rows would violate it. Either supply a default, or add the column nullable, backfill in batches, and promote it via a NOT VALID check constraint that you then validate.

Why did my instant ALTER TABLE cause an outage?

Almost certainly lock queuing. Your ALTER waited behind a long-running query, and every subsequent query queued behind your ALTER. Set lock_timeout so the statement fails fast instead of blocking all traffic.

Can I add a column in a specific position?

No. PostgreSQL always appends new columns at the end and offers no reordering. If column order matters to your application, that is a signal to name columns explicitly rather than relying on SELECT *.

Is DEFAULT now() safe on a large table?

No. A volatile default must produce a distinct value per row, so PostgreSQL rewrites the entire table under an exclusive lock. Add the column nullable and backfill in batches instead.