drop postgres table with minimal locking

Question

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.

Answer 1

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.

Answer 2

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:

BEGIN;
    -- look for any query locking the table and cancel it
    SELECT 
        pg_cancel_backend(pid)
    FROM 
        pg_stat_activity
    WHERE 
        pid IN (SELECT pid FROM pg_locks)
    AND 
        query LIKE '%temp_table%'
    AND 
        -- 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);
COMMIT;