drop postgres table with minimal locking
My best guess about what you're talking about is that the process of obtaining locks on the tables to drop them takes too long. You're running the changes in a transaction, so you obtain an exclusive lock on some_table before moving on to try to obtain an exclusive lock on other_table. At this point, some_table is inaccessible to clients, but other_table still has queries running against it so the migration script can't get a lock yet. Queries start to block on some_table and production operations begin to stall. You might even have transactions deadlocking because they already have a read lock on other_table and are trying to acquire a read lock on some_table, the reverse order to your DDL migration script.

Presumably your migration script is going to re-create the tables with a new definition after dropping them, then re-populate them, since you wouldn't be dropping tables that are in production use otherwise.

If that's the issue, your options are limited. You can rearrange your migration script into a set of individual transactions that don't depend on each other, like:

BEGIN;
DROP TABLE some_table;
CREATE TABLE some_table ( new_definition );
INSERT INTO some_table (...) VALUES (...);
COMMIT;


BEGIN;
DROP TABLE other_table;
CREATE TABLE other_table ( new_definition );
INSERT INTO other_table (...) VALUES (...);
COMMIT;


... or do the same thing with ALTER TABLE transformations instead of a drop, re-create and re-load.

Alternately, you can REVOKE access to the tables then query pg_locks and pg_cancel_backend all connections that currently hold locks on the tables you want to modify, apply the migration, and then GRANT access again. That'll ensure the migration runs promptly at the cost of aborting some queries. A well-written application will just re-try, so this shouldn't be particularly disruptive.