Postgresql – Dropping a large column from a large table in postgres

That query should work nearly instantly because it only hides the column, without trying to do any actual work dropping it. From the ALTER TABLE doc:

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

If there’s partitioning and/or inheritance set up on it, unless you ALTER TABLEONLYa DROP COLUMN b;, it’ll try to cascade through the whole structure. In that case, drop it on just the parent, then process all descendants. Similar story with foreign key references to this column, if there are any: you might want to drop those FK columns one by one, then drop this one without having to CASCADE.

You didn’t explicitly exclude these so I’m just making sure – if you had partitioning, I guess you’d mention it, and FK is unlikely on a long text column. It’d also require a unique constraint, which would automatically create an index to enforce it – and you did say there are none on that column.

Other than that, check pg_locks and/or pg_stat_activity to see what’s blocking you. First thing ALTER TABLE does is request and wait for a lock:

Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.

So it’s probably sitting there staring at a crowd of sessions doing something on that table, waiting for them to finish, while new queries begin to queue up behind it with any other lock request. This also means you might want to take a look at auto_explain and see who’s patient enough to wait you out and who’s probably wasting your resources on queries that routinely fail on timeout. If you waited 30min for the lock, it means someone’s been holding it for 30min.

It’s more likely you’re dealing with long transactions holding on to locks longer than they need to, while none of the individual statements inside them takes that long. There’s transaction_timeout, lock_timeout and idle_in_transaction_session_timeout that you can tweak to clear those off automatically but ideally, it’s the app that should be designed to keep client transactions as short as possible. If it works with autocommit off, someone, somewhere probably forgot to commit explicitly. Some pools even go beyond discard all and routinely close and re-establish connections upon release – if yours doesn’t, it might be a good idea to enable that.

To hunt down the blocking sessions, you can use the example from the PostgreSQL Wiki:

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid     AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

If you’re impatient, you can resort to pg_cancel_backend() or even pg_terminate_backend().