How PostgreSQL ADD COLUMN Behaves on Busy Tables

When you add a column to a PostgreSQL table that handles live traffic, the result depends on the column’s default and on what else the migration does. For many additions, PostgreSQL changes the table’s metadata, or stored description, without rewriting every existing row. Even then, PostgreSQL needs a strong table lock. The change can wait for active transactions, and queries can pause briefly after PostgreSQL gets the lock.

A default is the value PostgreSQL uses when an insert leaves the column out. A table rewrite rebuilds the table’s stored rows. The work grows with the amount of data and can require substantial temporary disk space. PostgreSQL 11 and later avoid a rewrite when a new column has no default or has a non-volatile default. A non-volatile default returns the same value each time PostgreSQL evaluates it. The key question is whether the migration can use this fast path or needs work on each row, which should happen separately.

When PostgreSQL adds a column without rewriting rows

Adding a nullable column without a default is the simplest case:

ALTER TABLE customers
  ADD COLUMN marketing_opt_in boolean;

Existing rows read as NULL, and PostgreSQL does not need to write a null value into every row. Inserts that omit the column also get NULL unless a default is added later.

On PostgreSQL 11 and later, a default that returns the same value each time PostgreSQL evaluates it also avoids a rewrite:

ALTER TABLE customers
  ADD COLUMN status text DEFAULT 'pending';

PostgreSQL evaluates the default when the statement runs and records the result for existing rows in table metadata. Those rows read as the same value. Future inserts use the default expression as usual. The PostgreSQL ALTER TABLE documentation describes this fast path, including now() as a non-volatile example.

Version matters. PostgreSQL 10 documented a table-and-index rewrite when adding a column with a default, so do not assume the PostgreSQL 11-and-later behavior applies to an older server. The PostgreSQL 10 reference shows the older behavior.

PostgreSQL takes an ACCESS EXCLUSIVE lock for ALTER TABLE even for a fast metadata change, unless a specific form requires a weaker lock. This lock conflicts with ordinary SELECT queries and writes. If another transaction holds a conflicting lock, PostgreSQL waits before starting the change. Once PostgreSQL gets the lock, queries and writes wait until it releases the lock.

Defaults and column types that cause a rewrite

A volatile default, such as clock_timestamp(), can return a different value each time PostgreSQL evaluates it. PostgreSQL calculates that value for each existing row and rewrites the table:

ALTER TABLE events
  ADD COLUMN added_at timestamptz DEFAULT clock_timestamp();

The rewrite also rebuilds the table’s indexes. PostgreSQL documents similar rewrites when you add a stored generated column or an identity column. It also documents them for a column whose type is a domain with constraints. On a large table, these changes can take significant time and temporarily require as much as double the disk space. Check the exact column definition against the PostgreSQL ALTER TABLE rules before choosing a one-step migration.

A scan checks rows without rebuilding the table. Adding a CHECK or NOT NULL constraint requires PostgreSQL to check existing rows, so PostgreSQL may scan the table even when it does not rewrite it. When a migration combines subcommands, PostgreSQL uses the strongest lock any of them requires. Combining changes can let PostgreSQL scan or rewrite the table in one pass, but the full statement can require more disruptive work and a stronger lock.

Plan NOT NULL separately because every existing row must meet the constraint. PostgreSQL scans existing rows to verify them, but this check does not itself require a rewrite. The work for an inline ADD COLUMN ... NOT NULL DEFAULT ... statement depends on the PostgreSQL release and the full statement. If scan time matters, test the exact DDL on the target release. A cautious plan adds the column as nullable, fills it, then enforces NOT NULL.

Backfill row-specific values in batches

When each existing row needs its own value, a volatile default makes PostgreSQL calculate those values during a table rewrite. Instead, add the column and then backfill it with an UPDATE, which fills the new column for existing rows. The operator can control the size and pace of the work by running batches, though each batch still uses database resources.

A staged rollout typically proceeds this way:

1. Add the column as nullable and without a default.
2. Deploy code that writes the new column for new rows and relevant updates.
3. Update existing rows in bounded batches, committing each batch. A stable key or key range makes progress easier to track.
4. Check that no rows remain unfilled and that the computed values are correct.
5. Set a default for future inserts if the code needs one. Changing a default does not fill existing rows.
6. Add NOT NULL or other constraints after the data meets them, allowing for the validation scan and lock.

For example, after adding normalized_email, a worker can fill one key range at a time:

UPDATE customers
SET normalized_email = lower(email)
WHERE id > :lower_id
  AND id <= :upper_id
  AND normalized_email IS NULL;

The right batch size and pace depend on the table and its workload; PostgreSQL does not prescribe one value for every case. Workers that use SKIP LOCKED to skip rows another worker has locked also need to check when the backfill is complete. PostgreSQL says SKIP LOCKED returns an inconsistent view, so it suits avoiding conflicts in queue-like work rather than general-purpose reads. A skipped row is not a completed row. See the PostgreSQL SELECT documentation.

For supported constraints, PostgreSQL also provides a way to separate adding a constraint from checking old rows: add it as NOT VALID, then validate it in a later command. The initial step skips the table scan, and validation checks existing rows under a weaker lock than ACCESS EXCLUSIVE. Confirm that the constraint type and PostgreSQL version support this pattern before using it.

Limit lock waits on a busy table

Keep the DDL transaction short. Do not group unrelated work with ALTER TABLE, because PostgreSQL holds the strong lock until the transaction ends. Set a session-level lock_timeout so the migration fails with an error instead of waiting indefinitely for the lock:

SET lock_timeout = '2s';

ALTER TABLE customers
  ADD COLUMN marketing_opt_in boolean;

Here, the timeout is an example setting, not a universal recommendation. If PostgreSQL cannot acquire the lock before the limit, the statement fails and the operator can retry later. The PostgreSQL connection settings document lock_timeout and note that it applies to each lock acquisition attempt. Set it for the migration session rather than globally, so unrelated sessions do not inherit the limit.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com