Modifying the schema of a database supporting a high-traffic production application is always a high-risk operation. If done improperly, PostgreSQL can lock tables, causing writes to queue up and eventually resulting in connection pool exhaustion and application downtime. Let's look at how to run common migrations safely without blocking traffic.
1. Adding an Index Concurrently
By default, creating an index in PostgreSQL takes an SHARE lock on the table, blocking all writes (INSERTs, UPDATEs, DELETEs) until the index build is complete. On a table with millions of rows, this can take minutes or hours.
The solution is to use the CONCURRENTLY keyword. This tells PostgreSQL to build the index by scanning the table twice without taking a write-blocking lock.
-- BAD: Locks the entire table for writes
CREATE INDEX idx_users_email ON users(email);
-- GOOD: Builds the index in the background without locking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Note: Concurrent index builds cannot run inside a transaction block. Make sure your migration tool runs this migration outside a transaction (e.g. disable_ddl_transaction! in Rails or using non-transactional migrations in Go/Node.js).
2. Adding Columns with Defaults Safely
Historically, adding a column with a default value required PostgreSQL to rewrite the entire table to write the default value to each existing row. On large tables, this caused heavy I/O operations and long-running locks.
Since PostgreSQL 11, adding a column with a constant default value no longer rewrites the table. It is a metadata-only change and takes less than a millisecond. However, adding columns with non-constant defaults (like random() or functions) still locks the table.
-- Safe in PostgreSQL 11+ (metadata-only update)
ALTER TABLE transactions ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
-- Unsafe (requires table rewrite because default is dynamic)
ALTER TABLE transactions ADD COLUMN token UUID DEFAULT gen_random_uuid();
If you must add a column with a dynamic default value without downtime, use this multi-step approach:
- Add the column without a default (or with a null default).
- Set the default value on the column metadata so new rows receive it automatically.
- Backfill existing rows in small batches (e.g., 5,000 rows at a time) to prevent connection timeouts and CPU spikes.
-- Step 1: Add the column (instant)
ALTER TABLE transactions ADD COLUMN token UUID;
-- Step 2: Add default metadata for new rows (instant)
ALTER TABLE transactions ALTER COLUMN token SET DEFAULT gen_random_uuid();
-- Step 3: Backfill in batches via your application background worker
-- UPDATE transactions SET token = gen_random_uuid() WHERE token IS NULL;
Conclusion
Always inspect the lock modes requested by DDL statements before executing them on production. Tools like pg_blocking_pids() can help monitor and diagnose lock contention before it escalates into database downtime.