Altering Tables Safely

ACCESS EXCLUSIVE lock

The most restrictive lock in PostgreSQL. An ACCESS EXCLUSIVE lock blocks all other operations on the table — reads, writes, other DDL — until the operation completes. ALTER TABLE, VACUUM FULL, TRUNCATE, and DROP TABLE all acquire this lock.

Table rewrite

An operation that creates a new physical copy of the table with the changes applied, then swaps the old and new versions. During the rewrite, an ACCESS EXCLUSIVE lock is held. The duration depends on the table size — GBs can take minutes.

🔌 Connect to the Beer Database

Before altering any tables, connect to beer_db. Run psql -U beer_db -d beer_db to open a psql session.

psql -U beer_db -d beer_db

beer_db=>

✅ Add a Nullable Column (Safe)

Add a nullable style_notes text column to the beers table. This is a metadata-only change — no table rewrite, minimal lock.

ALTER TABLE beers ADD COLUMN style_notes text;

ALTER TABLE

🔍 Verify the Column Was Added

Run \d beers and confirm style_notes appears in the column list.

\d beers

style_notes | text | | |

⚡ Add a NOT NULL Column WITH a Default (PG 16+ Fast Path)

Add a is_seasonal boolean NOT NULL DEFAULT false column to beers. On PostgreSQL 16+, this is instant — the default is stored in the catalog, not written to every row.

ALTER TABLE beers ADD COLUMN is_seasonal boolean NOT NULL DEFAULT false;

ALTER TABLE

✏️ Rename a Column

Rename style_notes to brewing_notes. Renaming is always a metadata-only change — no data is touched, minimal lock duration.

ALTER TABLE beers RENAME COLUMN style_notes TO brewing_notes;

ALTER TABLE

🔒 Simulate a Blocking ALTER TABLE in the Background

From psql, use \! to start a background transaction that holds an AccessExclusiveLock on beers for 60 seconds:

\! psql -U beer_db -d beer_db -c "BEGIN; LOCK TABLE beers IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(60); COMMIT;" &

The & runs it in the background. Exit your current psql session with \q, then reconnect from the shell with psql -U beer_db -d beer_db. Complete all three commands before checking the step.

\! psql -U beer_db -d beer_db -c "BEGIN; LOCK TABLE beers IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(60); COMMIT;" &

\q

psql -U beer_db -d beer_db

beer_db=>

🔍 Observe the AccessExclusiveLock on pg_locks

Now query pg_locks joined with pg_stat_activity to see the AccessExclusiveLock and how long it has been held:

SELECT l.pid, l.relation::regclass, l.mode, l.granted,
       now() - a.query_start AS elapsed
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'beers'::regclass;

You should see the background session holding the lock with a growing elapsed time.

SELECT l.pid, l.relation::regclass, l.mode, l.granted, now() - a.query_start AS elapsed FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.relation = 'beers'::regclass;

pid | relation | mode | granted | elapsed -------+----------+---------------------+---------+---------------- 12345 | beers | AccessExclusiveLock | t | 00:00:08.123456

🚫 Witness a Query Blocked by the Lock

While the background lock is still held, try a simple SELECT on beers. It will hang — AccessExclusiveLock blocks even reads that conflict with it:

SELECT COUNT(*) FROM beers;

Press Ctrl+C to cancel it after a few seconds, or wait for the lock to expire and the count to return. Either result completes this step.

SELECT COUNT(*) FROM beers;

ERROR: canceling statement due to user request

🔍 Check pg_locks After the Lock Is Released

Wait for the background transaction to finish (60 seconds total), then re-run the same query. The AccessExclusiveLock row should be gone.

SELECT l.pid, l.relation::regclass, l.mode, l.granted, now() - a.query_start AS elapsed FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.relation = 'beers'::regclass;

(0 rows)