Does ALTER TABLE DROP COLUMN lock the table in PostgreSQL?

Short answer

ALTER TABLE orders DROP COLUMN legacy_code takes an ACCESS EXCLUSIVE lock, so reads and writes wait while it runs. It runs for a millisecond or two whatever the table’s size, because PostgreSQL doesn’t remove anything from the rows: it marks the column as dropped and stops showing it.

Two things go wrong in practice:

• The lock queue. If a long query or an idle transaction is using the table, the DROP COLUMN waits, and every query that arrives after it waits too. Use lock_timeout.
• The application. Code that still names the column fails as soon as the drop commits, and prepared SELECT * statements fail with cached plan must not change result type.

What it locks

• ACCESS EXCLUSIVE on the table, until the transaction ends. Every other query on the table, including plain SELECT, waits.
• Indexes and table constraints that use the column are dropped in the same statement, under the same lock. Dropping an index is instant.
• If a view or another table’s foreign key depends on the column, the drop fails unless you add CASCADE, which drops those too:

ERROR:  cannot drop column a of table seo_mig.t because other objects depend on it
DETAIL:  view seo_mig.v depends on column a of table seo_mig.t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Check what CASCADE would remove before you use it.

Does it rewrite the table?

No. The data file is the same, nothing is scanned, and the old values stay on disk. In pg_attribute the column becomes ........pg.dropped.2........ with attisdropped = true. New and updated rows store a null in its place.

So dropping a wide column doesn’t shrink the table. On a 100,000-row table with a 200-character column:

before drop: 23 MB
after drop: 23 MB
after VACUUM FULL: 3544 kB

The space comes back as rows are updated, or all at once with a rewrite such as VACUUM FULL, which locks the table for the whole copy. See VACUUM FULL before you reach for it.

Run it safely

1. Deploy code that no longer uses the column first. Stop reading and writing it, ship that, then drop the column in a later release. If your ORM caches the list of columns, make sure it’s told to ignore this one.

2. Watch for prepared statements. A server-side prepared SELECT * that was prepared before the drop fails when it runs afterwards:

ERROR:  cached plan must not change result type

Drivers that prepare statements automatically can hit this until the statement is prepared again, which often means until the connection is replaced. Naming the columns you need, rather than SELECT *, avoids it.

3. Set lock_timeout and retry. The drop itself is instant; the risk is waiting for the lock:

SET lock_timeout = '2s';
ALTER TABLE orders DROP COLUMN legacy_code;

If it fails with ERROR: canceling statement due to lock timeout, nothing changed: wait a few seconds and run it again. See lock timeout and the retry loop on adding a column.

4. Drop dependent views yourself. Rather than CASCADE, drop and recreate the views in the same short transaction, so you know exactly what changes:

BEGIN;
SET LOCAL lock_timeout = '2s';
DROP VIEW order_summary;
ALTER TABLE orders DROP COLUMN legacy_code;
CREATE VIEW order_summary AS SELECT id, total FROM orders;
COMMIT;

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac):

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
SELECT pg_relation_filenode('seo_mig.big');   -- data file before
ALTER TABLE seo_mig.big DROP COLUMN s;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT pg_relation_filenode('seo_mig.big');   -- same number: no rewrite
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;
ROLLBACK;

A second session held ALTER TABLE seo_mig.t DROP COLUMN body open on a 1,000-row table while a third tried a SELECT and an INSERT with lock_timeout = '1s':

Dropping a column with an index on it, or with CASCADE and a dependent view, also took only the table’s ACCESS EXCLUSIVE lock and didn’t rewrite. The cached plan error came from PREPARE q1 AS SELECT * FROM seo_mig.ps, then DROP COLUMN, then EXECUTE q1, on all five versions; the disk sizes came from pg_relation_size().

In Inlet

The structure editor shows the ALTER TABLE … DROP COLUMN before it runs and warns when a change rewrites or scans the table. If the drop is stuck waiting, the Activity monitor shows the session blocking it, so you can see whether it’s a long query or a forgotten transaction.