How to explain/introspect what locks a DDL operation will need (Postgres)
What I do is BEGIN the transaction, run the DDL, then look at the pg_locks table to see what locks are held (not blocked, but held).  I do this on the test server, so there is nothing to block it or be blocked by it. But the knowledge generally transfers to production.  Some DDL can't be run in transactions, those generally require special handling.