Does ADD COLUMN Lock a Table?

ALTER TABLE table ADD COLUMN IF NOT EXISTS timestamp TIMESTAMPTZ;

TL;DR: Yes — but only briefly.

This command does acquire an ACCESS EXCLUSIVE lock, which is the highest level of lock in PostgreSQL. While this lock is held, no other operations (not even reads) can occur on the table. But the good news is:

It’s fast, even on large tables with millions of rows.
It doesn’t rewrite the table or touch existing rows.
Existing rows will simply show NULL in the new column.

Why Is It Fast?

PostgreSQL is smart. When you add a column without a default value, it doesn’t scan the table or backfill existing rows. Instead, it:

• Updates system metadata.

• Treats the new column as NULL for all existing rows.

• Skips rewriting the table heap.

So, even for large tables, this operation is typically very quick and causes minimal disruption — assuming there’s no heavy concurrent activity.

When Is It Risky?

If you add the column with a default value, like this:

ALTER TABLE table ADD COLUMN timestamp TIMESTAMPTZ DEFAULT now();

Then PostgreSQL must backfill every row. That means:

• Table rewrite (pre-PostgreSQL 11).

• Heavy locks, longer downtime.

• High disk I/O and CPU usage.

PostgreSQL 11+ introduced a smarter way: It defers the rewrite until the column is written to. But the ACCESS EXCLUSIVE lock still applies during the ALTER TABLE, even if briefly.

✅ Summary Table

Best Practices: Safe Schema Changes

1. Monitor Locks in Real Time

Check for active locks with:

SELECT pid, locktype, mode, relation::regclass, granted FROM pg_locks WHERE relation = 'your_table'::regclass;

If granted = false, the command is waiting for a lock.

2. Use Maintenance Windows

• Run during off-peak hours.

• Avoid conflicts with long-running queries.

• If possible, schedule downtime.

3. Avoid Defaults (At First)

Add the column without a default:

ALTER TABLE table ADD COLUMN timestamp TIMESTAMPTZ;

Then later, set a default value without rewriting existing rows:

ALTER TABLE table ALTER COLUMN timestamp SET DEFAULT now();

4. Batch-Update Existing Rows (Optional)

To populate existing rows:

UPDATE table SET timestamp = now() WHERE id BETWEEN 1 AND 100000;

Repeat for different id ranges to avoid long transactions or locks.

5. Monitor Active Queries

Keep an eye on what’s running:

SELECT pid, query, state, start_time FROM pg_stat_activity WHERE state = 'active';

This helps catch slow queries that could interfere.

6. Always Backup and Test

Before making schema changes:

pg_dump your_database > backup.sql

Also, test the change in a staging environment.

7. Optional: Use Transactions

Wrap your change in a transaction for atomicity:

BEGIN; ALTER TABLE table ADD COLUMN timestamp TIMESTAMPTZ; COMMIT;

⚠️ Keep in mind: locks are held for the entire transaction duration.

✅ Summary Checklist

• Monitor locks (pg_locks, pg_stat_activity)

• Test in staging before applying to production

• Use a maintenance window or off-peak time

• Avoid defaults initially; add them later

• Batch update large tables if needed

• Backup before deploying

Last modified on 2025-09-25