Apply a Zero-Downtime PostgreSQL Column Migration

1. Expand the schema by adding the new nullable column and a trigger that keeps both columns in sync in either direction.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '3s';

ALTER TABLE app.orders ADD COLUMN customer_email text;

CREATE OR REPLACE FUNCTION app.sync_order_email() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.customer_email := COALESCE(NEW.customer_email, NEW.email);
    NEW.email := COALESCE(NEW.email, NEW.customer_email);
  ELSIF NEW.customer_email IS DISTINCT FROM OLD.customer_email THEN
    NEW.email := NEW.customer_email;
  ELSIF NEW.email IS DISTINCT FROM OLD.email THEN
    NEW.customer_email := NEW.email;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_sync_email
BEFORE INSERT OR UPDATE ON app.orders
FOR EACH ROW EXECUTE FUNCTION app.sync_order_email();

COMMIT;
SQL

Expected output: psql reports ALTER TABLE, CREATE FUNCTION, CREATE TRIGGER, and COMMIT; the new column is nullable and both directions are synchronized.

2. Backfill existing rows in small batches, re-checking the null condition in the update so a concurrent new-version write is never overwritten.

set -euo pipefail
while true; do
  updated=$(psql "$DATABASE_URL" -Atc "
    WITH batch AS (
      SELECT id FROM app.orders
      WHERE customer_email IS NULL
      ORDER BY id
      LIMIT 1000
    )
    UPDATE app.orders o
       SET customer_email = o.email
      FROM batch
     WHERE o.id = batch.id AND o.customer_email IS NULL
    RETURNING 1" | wc -l | tr -d '[:space:]')
  if [ "$updated" -eq 0 ]; then
    break
  fi
  echo "backfilled $updated rows"
done

Expected output: Each iteration prints a batch size and the loop exits when the final batch returns 0 rows; no row is left with a null customer_email.

3. Add and validate a not-null check without a long table scan, then set the column to NOT NULL and drop the temporary check.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '3s';

ALTER TABLE app.orders
  ADD CONSTRAINT orders_customer_email_not_null
  CHECK (customer_email IS NOT NULL) NOT VALID;

ALTER TABLE app.orders VALIDATE CONSTRAINT orders_customer_email_not_null;
ALTER TABLE app.orders ALTER COLUMN customer_email SET NOT NULL;
ALTER TABLE app.orders DROP CONSTRAINT orders_customer_email_not_null;

COMMIT;
SQL

Expected output: The constraint validates successfully, SET NOT NULL completes without a full scan, and the temporary check is removed.

4. Deploy the new application version that reads and writes only customer_email, while the trigger keeps email populated for old instances.

const { rows } = await pool.query(
  `UPDATE app.orders
      SET customer_email = $1
    WHERE id = $2
  RETURNING id, customer_email`,
  [email, orderId],
);

Expected output: New instances write customer_email; the trigger copies it to email, so old instances that still select email continue to see the value.

5. Verify there is no drift, and simulate an old-version insert in staging to prove the trigger still fills the new column.

psql "$DATABASE_URL" -c "
SELECT
  count(*) FILTER (WHERE customer_email IS NULL) AS missing_new,
  count(*) FILTER (WHERE email IS DISTINCT FROM customer_email) AS drift
FROM app.orders;"

psql "$DATABASE_URL" -c "
INSERT INTO app.orders (id, email)
VALUES (gen_random_uuid(), '[email protected]')
RETURNING id, email, customer_email;"

Expected output: The drift query returns 0 | 0; the simulated old-version insert returns the same address in both email and customer_email.

6. After every old instance is gone, remove the trigger and the old column in a short transaction with a lock timeout.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '3s';

DROP TRIGGER orders_sync_email ON app.orders;
DROP FUNCTION app.sync_order_email();
ALTER TABLE app.orders DROP COLUMN email;

COMMIT;
SQL

Expected output: The trigger, function, and old column are dropped; new code continues to use customer_email with no synchronization overhead.

7. Build any required index concurrently, outside a transaction, and confirm it is valid.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_email_idx ON app.orders (customer_email);"
psql "$DATABASE_URL" -Atc "SELECT indisvalid FROM pg_index WHERE indexrelid = 'app.orders_customer_email_idx'::regclass;"

Expected output: The first command prints CREATE INDEX and the second prints t, proving the concurrent index is valid.

8. Confirm the old column is gone and the new column is not nullable.

psql "$DATABASE_URL" -c "
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'
  AND column_name IN ('email', 'customer_email');"

Expected output: Exactly one row remains: customer_email with is_nullable equal to NO.

Problem

The ALTER TABLE times out waiting for a lock

Solution

Retry during lower traffic and find the blocker with SELECT pid, state, query FROM pg_stat_activity. Do not remove lock_timeout, or the migration can queue every later query.

Problem

The backfill overwrites a value written by the new application

Solution

The batch update must re-check customer_email IS NULL in the UPDATE, not only in the CTE that selects IDs.

Problem

CREATE INDEX CONCURRENTLY leaves an invalid index

Solution

A concurrent build can fail without dropping the index. Drop it and rebuild; check pg_index.indisvalid before relying on it.