Attaching and Detaching Partitions

Partitioned tables in PostgreSQL are powerful for managing large datasets. But how do you handle data that needs to be added or removed from these partitions?

This lesson introduces ATTACH PARTITION and DETACH PARTITION, two critical commands that allow you to efficiently move data in and out of your partitioned tables with minimal disruption.

DETACH PARTITION is used to separate an existing partition from its parent partitioned table. When a partition is detached, it becomes a standalone, regular table.

• Syntax: ALTER TABLE parent_table DETACH PARTITION child_table;
• Why use it? For archiving old data, performing maintenance on a specific partition, or moving data to a different storage tier.

The main partitioned table remains available for queries during this operation.