Raw data behind What happens when you DROP COLUMN. The harness which produced these numbers is run-benchmark.sh.

Reproducing

The harness creates a disposable PostgreSQL cluster under /private/tmp, uses localhost port 56434, and removes the cluster when it exits. It defaults to the PostgreSQL 18 binaries from Postgres.app. Override PG_BIN, PORT, DATA_DIR or CHURN_CYCLES if necessary, and set SECTIONS (e.g. SECTIONS="3 4") to run a subset.

bash run-benchmark.sh

Run date: 2026-09-21

Environment:

  • PostgreSQL 18.1 (Postgres.app)
  • Apple Silicon, macOS 15
  • fsync=off and full_page_writes=off, so the numbers below are a floor rather than a prediction for your production cluster

1. The catalog flip

A 500,000 row table carrying a 200 byte text column, 126 MB of heap.

Step Result
ALTER TABLE flip DROP COLUMN notes 1.377 ms
Heap size before 126 MB
Heap size after 126 MB
VACUUM FULL flip 211.497 ms
Heap size after VACUUM FULL 25 MB

pg_attribute immediately after the drop:

           attname            | attnum | attisdropped | atttypid
------------------------------+--------+--------------+----------
 id                           |      1 | f            |       20
 customer_id                  |      2 | f            |       20
 ........pg.dropped.3........ |      3 | t            |        0
 status                       |      4 | f            |       25

2. The cascading index drop

Each table carries two indexes on the dropped column. Three runs per size, each against a freshly built table.

Rows Index size ALTER TABLE ... DROP COLUMN
250,000 85 MB 3.197 ms, 3.285 ms, 3.092 ms
500,000 169 MB 5.911 ms, 6.570 ms, 7.704 ms
1,000,000 338 MB 27.377 ms, 9.066 ms, 10.921 ms
2,000,000 676 MB 21.832 ms, 26.306 ms, 41.370 ms

The same tables, with both indexes removed by DROP INDEX CONCURRENTLY first:

Rows Index size ALTER TABLE ... DROP COLUMN
250,000 85 MB 0.527 ms
2,000,000 676 MB 0.534 ms

Roughly 0.04 ms of extra lock hold per MB of dependent index.

3. The lock queue

A reporting query holds AccessShareLock for ten seconds. DROP COLUMN arrives one second in, and a trivial SELECT arrives one second after that.

  pid  |                 query                  |        mode         | granted
-------+----------------------------------------+---------------------+---------
 38091 | BEGIN; SELECT count(*) FROM queued; SE | AccessShareLock     | t
 38101 | ALTER TABLE queued DROP COLUMN notes   | AccessExclusiveLock | f
 38109 | SELECT id FROM queued LIMIT 1          | AccessShareLock     | f
Statement Wall clock
ALTER TABLE queued DROP COLUMN notes 8.998 s
SELECT id FROM queued LIMIT 1 behind it 7.991 s

4. lock_timeout as the mitigation

Re-run on 2026-10-05 with SECTIONS=4. The first version of this section started the SELECT after the ALTER had already given up, so it measured nothing useful.

Same ten second reporting query as section 3, with SET lock_timeout before the ALTER TABLE. The ALTER arrives one second in, and the SELECT arrives 0.2 seconds after that, whilst the ALTER is still waiting.

ERROR:  canceling statement due to lock timeout
lock_timeout ALTER waited SELECT behind it waited
none (section 3) 8.998 s 7.991 s
2s 2.014 s 1.808 s
500ms 0.515 s 0.308 s

The SELECT waits for whatever is left of the ALTER’s timeout, so the timeout is the stall every query arriving behind it absorbs, on every retry.

5. attnum exhaustion

A table with one real column, put through 1,599 ADD COLUMN / DROP COLUMN cycles.

State Ghost slots Live columns Highest attnum
After the churn 1,599 1 1,600
After VACUUM FULL 1,599 1 1,600
After CREATE TABLE ... AS SELECT 0 1 1

Adding one more column to the churned table:

ERROR:  tables can have at most 1600 columns

6. Tuple widths either side of the drop

Twenty rows inserted with a 500 byte pad column, the column dropped, then twenty more rows inserted.

Page Tuples Min tuple bytes Max tuple bytes
0 14 541 541
1 26 37 541

Page 1 holds the tail of the pre-drop rows at 541 bytes alongside the post-drop rows at 37 bytes.