How to Safely Add a Column with a Default Value in PostgreSQL

The Problem: Table Rewrites

Before PostgreSQL 11, adding a column with a DEFAULT value always rewrote the entire table. PostgreSQL had to physically write the default value into every existing row. During this rewrite, the table was locked with an ACCESS EXCLUSIVE lock, blocking all reads and writes.

-- PostgreSQL 10 and earlier: FULL TABLE REWRITE
-- On a 100M row table, this could take 10+ minutes
-- The table is completely locked during the entire operation
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

On a table with 100 million rows, this could take 10 minutes or more. During that time, every query against the table — reads included — queues up. Connection pools exhaust. Application timeouts cascade. Users see errors. This was one of the most common causes of database outages in production PostgreSQL deployments.

PostgreSQL 11: The Fast-Path Default

PostgreSQL 11 (released October 2018) introduced a significant optimization. When you add a column with a non-volatile DEFAULT, PostgreSQL stores the default value in the catalog (pg_attribute.attmissingval) instead of writing it to every row. The operation completes in milliseconds regardless of table size.

-- PostgreSQL 11+: INSTANT operation (no table rewrite)
-- Works because 'active' is a constant (non-volatile)
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- Also instant — integer constant
ALTER TABLE orders ADD COLUMN version INT DEFAULT 1;

-- Also instant — boolean constant
ALTER TABLE features ADD COLUMN enabled BOOLEAN DEFAULT false;

-- Also instant — NULL default (always was instant)
ALTER TABLE users ADD COLUMN middle_name TEXT;

When a query reads a row that was created before the column was added, PostgreSQL automatically returns the stored default value. Rows created after the ALTER TABLE include the column value physically. Over time, as rows are updated, the default value gets written to disk naturally through MVCC.

Volatile vs Non-Volatile Defaults

The optimization only works for non-volatile defaults. A non-volatile expression always returns the same value. A volatile expression might return a different value each time it is evaluated.

The distinction matters because a volatile function must produce a unique value per row. PostgreSQL cannot store a single value in the catalog and use it for all existing rows — it needs to evaluate the function for each one, which requires a table rewrite.

The Safe Pattern for Volatile Defaults

When you need a volatile default (like gen_random_uuid() or now()), split the operation into three steps:

-- Step 1: Add the column without a default (instant, no rewrite)
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ;

-- Step 2: Set the default for new rows (instant, metadata-only)
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();

-- Step 3: Backfill existing rows in batches (no lock)
UPDATE users SET created_at = now()
WHERE id BETWEEN 1 AND 10000 AND created_at IS NULL;

UPDATE users SET created_at = now()
WHERE id BETWEEN 10001 AND 20000 AND created_at IS NULL;
-- ... continue in batches

This approach never locks the table for more than milliseconds. The backfill runs as normal DML, which only acquires ROW EXCLUSIVE locks. You can batch the updates to avoid holding locks for too long and to give autovacuum time to clean up dead tuples between batches.

Common Pitfall: UUID Primary Keys

One of the most common mistakes is adding a UUID column with gen_random_uuid() as the default:

-- DANGEROUS: gen_random_uuid() is volatile
-- This rewrites the entire table on PG 11+
ALTER TABLE orders ADD COLUMN external_id UUID DEFAULT gen_random_uuid();

-- SAFE: Split into separate operations
ALTER TABLE orders ADD COLUMN external_id UUID;
ALTER TABLE orders ALTER COLUMN external_id SET DEFAULT gen_random_uuid();
-- Then backfill existing rows in batches

Common Pitfall: Timestamps

Another frequent mistake is adding a created_at or updated_at column with now():

-- DANGEROUS: now() is stable within a transaction
-- but PostgreSQL classifies it as volatile for this purpose
ALTER TABLE events ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();

-- SAFE: Use the three-step pattern
ALTER TABLE events ADD COLUMN created_at TIMESTAMPTZ;
ALTER TABLE events ALTER COLUMN created_at SET DEFAULT now();
-- Backfill in batches...

A subtle detail: now() is technically STABLE (returns the same value within a transaction), but PostgreSQL still treats it as volatile for the purposes of the fast-path default optimization. The reason is that a STABLE function could still return different values across transactions, so PostgreSQL cannot store a single value in the catalog.

Backfill Strategies

When backfilling existing rows, there are several approaches:

Batched updates by primary key range

-- Process 10,000 rows at a time
DO $$
DECLARE
  batch_size INT := 10000;
  min_id BIGINT;
  max_id BIGINT;
  current_id BIGINT;
BEGIN
  SELECT min(id), max(id) INTO min_id, max_id FROM users;
  current_id := min_id;

  WHILE current_id <= max_id LOOP
    UPDATE users
    SET status = 'active'
    WHERE id >= current_id
      AND id < current_id + batch_size
      AND status IS NULL;

    current_id := current_id + batch_size;
    COMMIT;  -- Release locks between batches (PG 11+ procedures)
  END LOOP;
END $$;

Using a temporary helper function

-- For large backfills, a loop in a script is often simpler
-- Run this from your application or a migration runner:

-- Bash / psql loop:
for offset in $(seq 0 10000 1000000); do
  psql -c "UPDATE users SET status = 'active'
           WHERE id IN (SELECT id FROM users
                        WHERE status IS NULL
                        ORDER BY id LIMIT 10000)"
done

How to Check if a Default Triggers a Rewrite

You can check the volatility of a function using:

SELECT proname, provolatile
FROM pg_proc
WHERE proname = 'now';

-- provolatile values:
-- 'i' = immutable (safe)
-- 's' = stable (triggers rewrite in ADD COLUMN DEFAULT)
-- 'v' = volatile (triggers rewrite)

If provolatile is anything other than 'i' (immutable), the default will trigger a table rewrite when used in ADD COLUMN. Use the three-step pattern instead.

Version-Specific Summary

Catch This Automatically

Remembering which defaults are volatile and which are safe is easy to get wrong under pressure. MigrationPilot detects volatile defaults in ADD COLUMN statements (rule MP003) and suggests the safe three-step pattern automatically. It also auto-detects your PostgreSQL version and adjusts its advice accordingly. Run it in your CI pipeline to catch these issues before they reach production.

npx migrationpilot analyze migrations/003_add_timestamps.sql