Should a partition be detached before dropping?

Question

I'm using PostgreSQL 12, in which there is a partitioned table. This table has old partitions that need to be deleted. I've seen the code where the old partitions are firstly detached and only then dropped:

ALTER TABLE partitioned_table DETACH PARTITION partitioned_table_1;
DROP TABLE partitioned_table_1;

Is there any reason to detach a partition before dropping? Does just dropping a partition without detaching impact on other queries to a database?

Anthology

Answer 1

from the manual.

• DROP TABLE partitioned_table_1; means drop table. ACCESS EXCLUSIVE lock on the parent table.

• ALTER TABLE partitioned_table DETACH PARTITION partitioned_table_1; means that partitioned_table_1 will still exists. ACCESS EXCLUSIVE lock on the parent table

The detached partition continues to exist as a standalone table, but no longer has any ties to the table from which it was detached.

In postgresql 14, DETACH PARTITION partitioned_table_1 CONCURRENTLY SHARE UPDATE EXCLUSIVE lock on the parent table. more info: https://www.postgresql.org/docs/12/sql-altertable.html
https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION https://www.postgresql.org/docs/current/ddl-partitioning.html

Albur

Answer 2

This is an interesting question.
Let's take this partitioned table structure for reference (taken from PgAnalyze blog post)

Speaking of locking procedure wise, both two type of operations:
Drop the partitioned table right away (immediate-drop)

DROP TABLE people_partitioned_birthdays_1800_to_1850;

versus
Detach the partitioned table from the root(parent) table, then drop it (detach-drop)

ALTER TABLE people_partitioned DETACH PARTITION people_partitioned_birthdays_1800_to_1850;
DROP TABLE people_partitioned_birthdays_1800_to_1850;

both requires taking an ACCESS EXCLUSIVE LOCK on the parent table people_partitioned, which blocks any SELECT (without FOR UPDATE/SHARE) statement against the people_partitioned while the lock is being in placed.

However, the detach-drop strategy is truly shine when you need to perform additional maintenance operations on the partitioned table. It's very likely that you might want to create a backup of this partition with COPY, pg_dump, perform data aggregation, analytics, etc... before dropping it for good. And since this partitioned table has been detached, it would be free from lock conflicting with the parent table.

Now you might wonder that you can still perform these maintenance actions while the schedule for delete partition still in place?

-- perform maintenance action on people_partitioned_birthdays_1800_to_1850 ...
DROP TABLE people_partitioned_birthdays_1800_to_1850;

then again, if there's any operation that require excluse lock on the parent (e.g another new partition is being created/deleted, which also require exclusive lock on the parent table), then your maintenance action above will be blocked until the lock conflict is resolved.

So to conclude, it's generally safer to detach-drop a partitioned table from the root partitioning table than immediate-drop. Any operation on the existing partitioned tables structure should not block your maintenance operation on the detached partition, and vice-versa

Reference
Postgres DDL Declarative Partitioning (with use cases)

Boehmer

Answer 3

You wrote three question, not one. :) There is already a notice about

DETACH PARTITION partitioned_table_1 CONCURRENTLY

I'll answer only on main question

Should a partition be detached before dropping?

You must detach partition before dropping if there is other tables that have foreign keys pointed on your partitioned table. You will not able just drop partition in such case, at least in PostgreSQL 14 (don't check other versions).

Sweetie