Dropping a column sounds like it should be an expensive operation - the database has to go and remove that column from every row in the table, which for a large table is a lot of data to rewrite. In Postgres it turns out to be almost free, however there is a different part of the operation which can take your application down, and it has nothing to do with how big the column was.

Glossary

  • pg_attribute - system catalog with one row per column, including the dropped ones
  • attisdropped - flag on the pg_attribute row which hides the column from the planner
  • attnum - the column’s position within the table, never reused
  • AccessExclusiveLock - the strongest table lock (Mode 8), conflicting with everything including a plain SELECT
  • heap - the actual table data on disk
  • lock queue - the list of backends waiting on a lock, served in order

All the numbers below come from a disposable PostgreSQL 18 cluster driven by run-benchmark.sh. The full output is on the benchmark results page.

Dropping the column

Dropping a column from a 500,000 row table with 126 MB of heap took 1.377 ms, and the table was still 126 MB afterwards. The heap never gets touched at all, as the whole thing is a single update to that column’s row in pg_attribute, which sets attisdropped, renames the column so the name can be reused and resets the type to 0 so nothing can interpret the old bytes any more.

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

That mangled name is what you see if you go looking. A table with a lot of them has clearly been through quite a few schema changes.

Rows written after the drop are smaller, whilst rows written before it still carry the old bytes around. Both shapes end up sitting on the same page under pageinspect, where the pre-drop tuples are 541 bytes and the post-drop ones are 37.

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

Dependent indexes

Any index on the column gets dropped as part of the same ALTER TABLE, in the same transaction and under the same lock, and since there is no CONCURRENTLY version of ALTER TABLE this is the only place where the amount of data has any effect on how long the lock is held for. It turned out to be a lot less than I expected though. Two indexes on the column, grown from 85 MB up to 676 MB, only moved the drop from ~3 ms to ~30 ms.

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

That works out at roughly 0.04 ms per MB of index. The reason it is so cheap is that Postgres is not reading those indexes or tidying them up at all, it just unlinks the files at commit and leaves the filesystem to reclaim the space. Dropping them beforehand with DROP INDEX CONCURRENTLY gets the ALTER TABLE down to ~0.5 ms whatever the size, which saves you about 30 ms at the 676 MB end. You would need something like a 25 GB index before the cascade on its own held the lock for a full second.

Locking

The problem with AccessExclusiveLock is not really how long it is held for, but that Postgres queues lock requests in order, so once the ALTER TABLE is stuck waiting behind a slow query, every SELECT arriving after it has to wait as well, even though on its own that SELECT would have been granted straight away.

You can reproduce this with three sessions side by side. A reporting query holds AccessShareLock for ten seconds, the DROP COLUMN arrives a second later, and a SELECT ... LIMIT 1 a 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

The SELECT reads a single row from a 100,000 row table and would normally return in under a millisecond, however here it took 7.991 seconds. It was queued behind the ALTER TABLE, which itself only needs about a millisecond of work, and the ALTER TABLE was queued behind the reporting query, so a query doing basically no work ended up waiting on someone else’s report to finish.

This is the scenario to plan the migration around, and note that none of it depends on the size of the column, as a boolean and a 2 KB jsonb column behave the same and the number of indexes on the table makes no difference either. The only thing that matters is how long the transaction ahead of the drop runs for, and how much traffic piles up behind it whilst it waits.

Setting a lock_timeout

The ALTER TABLE itself only needs a millisecond of work, so what you want to cap is how long it is allowed to sit at the front of the queue. That is what lock_timeout does - once it expires the ALTER TABLE gives up and the queries stuck behind it get released.

SET lock_timeout = '500ms';
ALTER TABLE orders DROP COLUMN notes;

ERROR:  canceling statement due to lock timeout

The queue is still there whilst the ALTER TABLE is waiting though, and it still blocks everything behind it right up until the timeout fires, which is easy to see by running the same ten second reporting query again with the SELECT arriving 0.2 seconds after the ALTER TABLE.

lock_timeout ALTER TABLE waited SELECT behind it waited
none 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 timeout, so the value you pick is the stall you are willing to put every query on that table through, and you pay it again on every retry. The migration itself failed, which is fine. You retry it (eg: in a loop with a short sleep) until it manages to grab the lock in a gap between long transactions. Keep the timeout short on a busy table, somewhere between 100 ms and a second, because if the lock is free the drop gets it immediately anyway and a longer timeout only buys you a longer outage when it is not.

If your migration tooling already wraps DDL in a transaction, set lock_timeout inside that transaction rather than globally, and remember that the same timeout then applies to every statement in it - getting into this habit is going to save you a lot more grief than dropping the indexes concurrently beforehand ever will.

Reclaiming the space

The old column data is still in every pre-existing tuple, so the table does not shrink, and a plain VACUUM does not help either because it reclaims space from dead tuples, whereas these are live tuples which happen to contain a field the planner ignores. It takes a full rewrite to clear them out, which builds each tuple again from the current column list and leaves out anything marked as dropped. On the 126 MB table above, VACUUM FULL took 211 ms and brought it down to 25 MB.

-- rewrites the heap, holds AccessExclusiveLock for the whole rewrite
VACUUM FULL orders;

On a production table you want pg_repack instead, which does the same rewrite online and only needs a brief strong lock at the end. CLUSTER will reclaim the space as a side effect too, if you are already running it for ordering reasons. None of this is urgent unless the column was really large. It is perfectly reasonable to let the space come back through whatever rewrite you were going to do anyway.

The 1600 column limit

attnum is allocated sequentially and never reused, and the 1,600 column limit counts the dropped columns along with the live ones. A table that gets columns added and removed regularly builds up ghost slots, and if the churn comes from automation it can eat through the limit surprisingly quickly.

The benchmark puts a table with one real column through 1,599 add/drop cycles, which leaves it one slot away from the limit.

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 
ERROR:  tables can have at most 1600 columns

The middle row is the one that caught me out. VACUUM FULL rewrites the heap and reclaims the dropped column’s bytes, so you would assume it tidies up the catalog as well, but it does not touch pg_attribute at all and the ghost slots survive intact, so the only way to get them back is to build a new relation, ie: CREATE TABLE ... AS SELECT followed by a swap, or whatever your zero-downtime table swap tooling uses.

You can check where your own schema stands with the query below.

SELECT c.relname                                  AS table_name,
       count(*) FILTER (WHERE a.attisdropped)     AS ghost_slots,
       count(*) FILTER (WHERE NOT a.attisdropped) AS live_columns,
       max(a.attnum)                              AS highest_attnum,
       1600 - max(a.attnum)                       AS remaining_slots
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r'
  AND c.relnamespace = 'public'::regnamespace
  AND a.attnum > 0
GROUP BY c.relname
HAVING max(a.attnum) > 100
ORDER BY highest_attnum DESC;

The attnum > 0 filter drops the system columns such as ctid and xmin, which carry negative values, and the HAVING clause keeps the output down to tables with enough history to be worth looking at. If you find tables with hundreds of ghost slots it is worth scheduling a rewrite for them, rather than finding out about the limit when a routine migration fails.

Recommendations

  • DROP COLUMN itself is a catalog write and takes about a millisecond, whilst the index cascade adds tens of milliseconds even at ~700 MB of index
  • always set a short lock_timeout before the ALTER TABLE and retry on failure, since the timeout caps how long AccessExclusiveLock can hold up the queries queued behind it
  • pre-dropping the indexes with DROP INDEX CONCURRENTLY saves at most ~30 ms of lock time at these sizes, so do it when it is easy and do not build a process around it
  • the old bytes stay in the heap until a VACUUM FULL or a pg_repack, which is not urgent unless the column was large
  • the attnum slot is gone for good and still counts towards the 1,600 column limit, so check with the query above if your schema changes a lot

TL;DR - DROP COLUMN is cheap, but be mindful of what else is running on the table when you take the lock, and always set a lock_timeout.