postgresqlpartitioningdatabase-administration

Is it safe to drop a partition just after detaching it concurrently?

I am trying to automate partition management on my psql (version 15) DB. I would like to delete old partitions to free some space in the db. To do so, i plan to detach it using CONCURRENTLY keyword so I do not need an ACCESS EXCLUSIVE lock on the partitioned table.

Once my detach partition concurrently run, is it safe to drop the table just after ? Or should I wait for the concurrent detach to be done ? If i need to do that how can I ?

Thanks

I tried to run manually and sequentially the two commands and it worked but I fear that the behavior might be different in prod environment that is under heavy workload.

Solution

  • ALTER TABLE ... DETACH PARTITION CONCURRENTLY is complete as soon as that statement has finished. Since DROP TABLE requires an ACCESS EXCLUSIVE lock, an attempt to drop the partition before ALTER TABLE ... DETACH PARTITION CONCURRENTLY is done will cause DROP TABLE to block.

    So simply wait until ALTER TABLE ... DETACH PARTITION CONCURRENTLY is done, then run DROP TABLE to get rid of the partition.