How to DROP ROLE or DROP USER in PostgreSQL

Handling the lock memory cost of dropping a PostgreSQL role

Raising max_locks_per_transaction to a very large value comes at a price: that memory is allocated when the server starts and is only released once PostgreSQL is stopped. It is therefore not an ideal permanent setting, though it almost certainly will not consume all of the machine's RAM.

A practical workaround exists — bump the parameter high, restart, run DROP OWNED, then lower the parameter and restart again.

Estimating the requirement

An upper bound for the lock count can be derived before touching the configuration:

• Run SELECT count(*) FROM pg_class;
• Divide the result by max_connections
• Use that quotient as the value for max_locks_per_transaction