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:
DROP TABLE some_table;
CREATE TABLE some_table ( new_definition );
INSERT INTO some_table (...) VALUES (...);
DROP TABLE other_table;
CREATE TABLE other_table ( new_definition );
INSERT INTO other_table (...) VALUES (...);
... 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_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.