Attaching and detaching partitions in PostgreSQL - Mini Project: Build & Apply

Attaching and Detaching Partitions in PostgreSQL

📖 Scenario: You manage a sales database that stores orders by year. To improve performance, you want to split the orders table into yearly partitions. This helps the database quickly find orders from a specific year.

🎯 Goal: Build a partitioned table orders by year, create a child table for 2023, attach it as a partition, then detach it back to a standalone table.

📋 What You'll Learn

Create a main table orders partitioned by RANGE on the order_year column.

Create a child table orders_2023 with the same structure.

Attach orders_2023 as a partition for year 2023.

Detach orders_2023 from the partitioned table.

💡 Why This Matters

🌍 Real World

Partitioning large tables by date or category helps databases run faster and manage data better.

💼 Career

Database administrators and backend developers often use partitioning to optimize performance and maintenance.

1

Create the main partitioned table

Create a table called orders with columns order_id (integer), order_year (integer), and amount (numeric). Partition this table by RANGE on the order_year column.

PostgreSQL

# Create the partitioned table orders
# Your code here

Need a hint?

Use PARTITION BY RANGE (order_year) to define the partitioning column.

2

Create a child table for 2023

Create a table called orders_2023 with the same columns as orders: order_id (integer), order_year (integer), and amount (numeric). Do not partition this table.

PostgreSQL

CREATE TABLE orders (
    order_id INT,
    order_year INT,
    amount NUMERIC
) PARTITION BY RANGE (order_year);

# Create the child table orders_2023
# Your code here

Need a hint?

Define orders_2023 with the same columns but no partitioning.

3

Attach the child table as a partition

Attach the table orders_2023 as a partition of orders for the range starting at 2023 and ending before 2024 using FOR VALUES FROM (2023) TO (2024).

PostgreSQL

CREATE TABLE orders (
    order_id INT,
    order_year INT,
    amount NUMERIC
) PARTITION BY RANGE (order_year);

CREATE TABLE orders_2023 (
    order_id INT,
    order_year INT,
    amount NUMERIC
);

# Attach orders_2023 as a partition for year 2023
# Your code here

Need a hint?

Use ALTER TABLE orders ATTACH PARTITION orders_2023 FOR VALUES FROM (2023) TO (2024);

4

Detach the child table from the partitioned table

Detach the partition orders_2023 from the orders table using DETACH PARTITION. This makes orders_2023 a standalone table again.

PostgreSQL

CREATE TABLE orders (
    order_id INT,
    order_year INT,
    amount NUMERIC
) PARTITION BY RANGE (order_year);

CREATE TABLE orders_2023 (
    order_id INT,
    order_year INT,
    amount NUMERIC
);

ALTER TABLE orders ATTACH PARTITION orders_2023 FOR VALUES FROM (2023) TO (2024);

# Detach orders_2023 from orders
# Your code here

Need a hint?

Use ALTER TABLE orders DETACH PARTITION orders_2023; to detach.