Definition
- Immediate failure –
ALTER TABLE ... SET NOT NULLvalidates every row; if any row has a null, the statement fails with a not-null violation. - Naive fix is dangerous – a single huge UPDATE to backfill every null takes a long lock and generates massive WAL.
- Three-phase migration – (1) add column as nullable (instant), (2) backfill in small batches (spread the load), (3) enforce NOT NULL after zero nulls remain.
- NOT VALID trick – in PostgreSQL, add a
CHECK (col IS NOT NULL) NOT VALIDfirst (brief lock, no scan), thenVALIDATE CONSTRAINT(no write lock). - Coordinate with deploys – ensure application code writes non-null values before the constraint lands.
Suppose National Bank of India decides every account must record a branch code, but millions of old accounts have that field blank. If you declare "branch code can never be blank" immediately, the database refuses because all those blanks already break the rule. The safe way is to first allow blanks, then quietly fill in old accounts a few thousand at a time, and only once none are blank do you stamp the rule.
Understanding the Concepts
What happens when you add NOT NULL to a column with existing nulls?
The database validates that no existing row violates the rule. If any row has a null, the statement fails immediately.
-- This fails if any row has a null branch_code
ALTER TABLE accounts ALTER COLUMN branch_code SET NOT NULL;
-- ERROR: column "branch_code" of relation "accounts" contains null values
The constraint is a promise about all rows. The engine will not accept a promise the current data already breaks.
Why is a single huge UPDATE dangerous?
A naive backfill locks the table and generates enormous write-ahead log.
-- Dangerous on a large table!
UPDATE accounts SET branch_code = 'NBI-MAIN' WHERE branch_code IS NULL;
-- On 200 million rows:
-- Takes a long lock (blocks all writes)
-- Generates gigabytes of WAL
-- Causes replication lag
-- Can bloat the table
What is the safe three-phase migration?
Phase 1: add the column nullable. Phase 2: backfill in batches. Phase 3: enforce NOT NULL.
-- Phase 1: add column as nullable (instant in modern PostgreSQL)
ALTER TABLE accounts ADD COLUMN branch_code VARCHAR(20);
-- No data rewrite, no lock. Deploy app code to write branch_code for new rows.
-- Phase 2: backfill in small batches
DO $$
DECLARE
batch_size INT := 5000;
affected INT;
BEGIN
LOOP
UPDATE accounts
SET branch_code = 'NBI-MAIN'
WHERE branch_code IS NULL
AND id IN (
SELECT id FROM accounts
WHERE branch_code IS NULL
LIMIT batch_size
FOR UPDATE SKIP LOCKED
);
GET DIAGNOSTICS affected = ROW_COUNT;
COMMIT;
IF affected = 0 THEN EXIT; END IF;
PERFORM pg_sleep(0.1); -- brief pause for vacuum/replication
END LOOP;
END $$;
-- Phase 3: verify and enforce
SELECT COUNT(*) FROM accounts WHERE branch_code IS NULL;
-- Must return 0
ALTER TABLE accounts ALTER COLUMN branch_code SET NOT NULL;
What is the NOT VALID trick in PostgreSQL?
The NOT VALID approach avoids a full-table exclusive lock during enforcement.
-- Step 1: add CHECK constraint as NOT VALID (brief lock, no scan)
ALTER TABLE accounts
ADD CONSTRAINT chk_branch_not_null
CHECK (branch_code IS NOT NULL) NOT VALID;
-- Takes only a brief lock on the table structure
-- Does NOT scan existing rows
-- New inserts and updates are immediately checked
-- Step 2: validate existing rows (no write lock, ShareUpdateExclusiveLock)
ALTER TABLE accounts VALIDATE CONSTRAINT chk_branch_not_null;
-- Scans existing rows but does not block concurrent writes
-- Step 3: promote to proper NOT NULL
ALTER TABLE accounts ALTER COLUMN branch_code SET NOT NULL;
ALTER TABLE accounts DROP CONSTRAINT chk_branch_not_null;
How do you coordinate with application deploys?
The deploy must ensure that new rows always have a non-null value before the constraint lands.
package com.bank.service;
import java.sql.*;
public class AccountService {
// Deploy step 1: application always writes branch_code
public void createAccount(Connection conn, String name, String branchCode)
throws SQLException {
if (branchCode == null || branchCode.isBlank()) {
branchCode = "NBI-MAIN"; // default for migration period
}
String sql = "INSERT INTO accounts (name, branch_code) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, name);
ps.setString(2, branchCode);
ps.executeUpdate();
}
}
}
The sequence is: deploy app change (always writes non-null) → backfill historical nulls → enforce NOT NULL.
How do you monitor the backfill progress?
Track remaining nulls and replication lag during the backfill.
-- Check remaining nulls
SELECT COUNT(*) AS remaining_nulls
FROM accounts
WHERE branch_code IS NULL;
-- Check replication lag
SELECT client_addr,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
# Monitor during backfill
watch -n 5 'psql -c "SELECT COUNT(*) FROM accounts WHERE branch_code IS NULL;"'
What about adding a NOT NULL column with a default?
Modern PostgreSQL (11+) optimizes a constant default so it is instant, with no table rewrite.
-- PostgreSQL 11+: instant, no rewrite
ALTER TABLE accounts ADD COLUMN branch_code VARCHAR(20) NOT NULL DEFAULT 'NBI-MAIN';
-- New rows get the default
-- Existing rows read the default from metadata (no physical update)
However, a volatile or computed default still triggers a rewrite.
What are common mistakes?
| Mistake | Consequence |
|---|---|
| Single huge UPDATE | Long lock, WAL bloat, replication lag |
| Forgetting app deploy order | New nulls arrive after backfill, before constraint |
| Not monitoring replication lag | Replica falls behind during backfill |
| Using SET NOT NULL without NOT VALID | Full-table scan under exclusive lock |
| Not verifying zero nulls remain | SET NOT NULL fails at the end |
What should a senior engineer know?
- Choose the backfill value carefully: a static default, a computed value from other columns, or a lookup.
- Use
FOR UPDATE SKIP LOCKEDin the batch query to avoid contention with live writes. - Monitor replication lag and lock waits throughout the backfill.
- Consider a background job (pg_repack or custom script) for very large tables.
- Document the migration plan with estimated duration and rollback steps.
Visual Overview
Phase 1: Add Column Nullable (instant)
┌──────────────────────────────────┐
│ ALTER TABLE ADD COLUMN nullable │
│ Deploy app: always write non-null│
└──────────────┬───────────────────┘
│
▼
Phase 2: Backfill in Batches
┌──────────────────────────────────┐
│ Loop: UPDATE ... LIMIT 5000 │
│ COMMIT each batch │
│ pg_sleep(0.1) between batches │
│ Monitor replication lag │
└──────────────┬───────────────────┘
│
▼
Phase 3: Enforce NOT NULL
┌──────────────────────────────────┐
│ Verify COUNT(*) WHERE IS NULL=0 │
│ ADD CHECK NOT VALID │
│ VALIDATE CONSTRAINT │
│ ALTER SET NOT NULL │
└──────────────────────────────────┘
Key Takeaway
If you try to add a NOT NULL constraint to a column that already contains null values, the statement fails immediately with a not-null violation. The constraint is a promise about every row, and the database will not accept a promise the current data already breaks.
On a large table, the naive approach of a single huge UPDATE is dangerous: it takes a long lock, generates enormous WAL, and can block all writes for minutes. The safe procedure is a three-phase, zero-downtime migration. Phase one: add the column as nullable, which is instant, and deploy application code that writes a non-null value for every new row. Phase two: backfill the historical nulls in small batches of a few thousand rows each, with brief pauses so vacuum and replication keep up. Phase three: once a query confirms zero nulls remain, enforce NOT NULL.
In PostgreSQL, the NOT VALID trick avoids a full-table exclusive lock: add a CHECK constraint as NOT VALID first (brief lock, no scan), then VALIDATE CONSTRAINT to verify rows without blocking writes, then promote to SET NOT NULL.
At the National Bank of India, Rajesh made branch_code mandatory on an accounts table with hundreds of millions of rows exactly this way: added it nullable, shipped the app change, backfilled in five-thousand-row batches overnight, and enforced NOT NULL through the NOT VALID path, all with no downtime.
Remember one sentence: NOT NULL on a column with existing nulls errors out, so add the column nullable, backfill in small batches, and only then enforce NOT NULL.
---