I'm trying to run the following Postgres migration script

DROP TABLE some_table;
DROP TABLE another_table;

The problem is that this script causes excessive locking when run on the production database and therefore has to be aborted. Is there a way to reduce the locking? I've considered removing all indices on these tables and/or foreign keys before executing the DROP statements, but I don't have an easy way to simulate the production environment in order to test whether this will have a positive or negative influence on the locking.

  • If your dropping these tables they're not needed. Could you revoke all rights to these tables besides the admin account? I assume your locking issues are from users or processes going at the tables while you are trying to drop it or vice verse users already starting transactions on this table and you coming in to drop it.
    – Kuberchaun
    Oct 29, 2012 at 14:18
  • 3
    What exactly do you mean with "excessive locking". Apart from the tables being dropped, nothing else should be locked. Btw: you can drop both tables with a single statement: drop table some_table, another_table
    – user1822
    Oct 29, 2012 at 15:16
  • do you mean you cannot drop the tables because they are locked? Oct 29, 2012 at 18:22
  • I have the same problem here, I cannot drop two tables (because of locking) even though I am very sure that nobody is accessing them. May 31, 2022 at 8:51

2 Answers 2


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_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.


I was having locking issues recreating a temporary table, and this also causing the database to hang.

Based on Craig Ringer's solution I created the following sequence using pg_locks and pg_cancel_backend:

    -- look for any query locking the table and cancel it
        pid IN (SELECT pid FROM pg_locks)
        query LIKE '%temp_table%'
        -- except this specific query
        query not like '%pg_stat_activity%';

    -- drop table and recreate it without issue
    -- since nothing else is locking it
    DROP TABLE IF EXISTS temp_table;
    CREATE TABLE temp_table(input_id VARCHAR);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.