Attaching and detaching partitions in PostgreSQL

Introduction

Attaching and detaching partitions helps organize large tables into smaller parts for easier management and faster queries.

When you want to add a new partition to an existing partitioned table.

When you need to remove a partition from a partitioned table without deleting data.

When reorganizing data by moving it between partitions.

When cleaning up old data by detaching partitions before archiving.

When improving query performance by managing partitions separately.

Syntax

PostgreSQL

ALTER TABLE parent_table ATTACH PARTITION child_table FOR VALUES partition_values;

ALTER TABLE parent_table DETACH PARTITION child_table;

The parent_table must be a partitioned table.

The child_table must have data matching the partition values when attaching.

Examples

Attach the partition sales_2023_q1 to the sales table for the first quarter of 2023.

PostgreSQL

ALTER TABLE sales ATTACH PARTITION sales_2023_q1 FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

Detach the partition sales_2022_q4 from the sales table without deleting its data.

PostgreSQL

ALTER TABLE sales DETACH PARTITION sales_2022_q4;

Sample Program

This example creates a partitioned table orders by order_date. It creates a partition orders_2023 for 2023 data. Then it creates a table old_orders with 2022 data and attaches it as a partition for 2022. Finally, it counts rows in each partition.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE old_orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  amount NUMERIC
);

INSERT INTO old_orders (order_date, amount) VALUES ('2022-06-15', 100), ('2022-12-31', 200);

ALTER TABLE orders ATTACH PARTITION old_orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

SELECT tableoid::regclass AS partition_name, count(*) AS rows_count FROM orders GROUP BY tableoid;

Important Notes

When attaching a partition, the data in the child table must fit the partition range or list defined.

Detaching a partition keeps the data in the child table intact; it just removes it from the parent partitioned table.

You cannot attach a partition if the parent table is not partitioned.

Summary

Attaching partitions adds existing tables as parts of a partitioned table.

Detaching partitions removes them without deleting data.

This helps manage large datasets by splitting them into smaller, manageable pieces.