require-concurrent-partition-detach

problem

Detaching a partition via ALTER TABLE t DETACH PARTITION y; requires an ACCESS EXCLUSIVE lock, which prevents reads and writes to the table.

solution

Ensure all partition detaches use the CONCURRENTLY option.

Instead of:

-- blocks reads and writes
alter table t detach partition p;

Use:

-- allows reads and writes while detaching
alter table t detach partition p concurrently;

links

• https://www.postgresql.org/docs/current/ddl-partitioning.html
• https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION