Sub-partitioning in PostgreSQL - Mini Project: Build & Apply

Create a Sub-Partitioned Table in PostgreSQL

📖 Scenario: You are managing a sales database for a retail company. The company wants to organize their sales data efficiently by partitioning the table first by region and then sub-partitioning by sale_date. This will help speed up queries and manage large data volumes.

🎯 Goal: Create a partitioned sales table in PostgreSQL that is partitioned by region and sub-partitioned by sale_date. You will define partitions for two regions and sub-partitions for two date ranges within each region.

📋 What You'll Learn

Create a main sales table partitioned by LIST on region

Create partitions for regions 'North' and 'South'

Sub-partition each region partition by RANGE on sale_date

Create sub-partitions for date ranges '2023-01-01' to '2023-06-30' and '2023-07-01' to '2023-12-31' in each region partition

💡 Why This Matters

🌍 Real World

Large databases often use partitioning to improve query speed and manageability by dividing data into smaller, more manageable pieces.

💼 Career

Database administrators and backend developers use partitioning to optimize performance and storage in enterprise applications.

1

Create the main partitioned sales table

Create a table called sales with columns id (integer), region (text), sale_date (date), and amount (numeric). Partition this table by LIST on the region column.

PostgreSQL

# Create the main sales table partitioned by LIST on region
# Your code here

Need a hint?

Use PARTITION BY LIST (region) to partition the table by region.

2

Create partitions for regions 'North' and 'South'

Create two partitions of the sales table named sales_north and sales_south for the regions 'North' and 'South' respectively. Partition these tables by RANGE on the sale_date column.

PostgreSQL

CREATE TABLE sales (
    id INT,
    region TEXT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);

-- Create partitions for 'North' and 'South' regions partitioned by RANGE on sale_date
# Your code here

Need a hint?

Use PARTITION OF sales FOR VALUES IN ('RegionName') and add PARTITION BY RANGE (sale_date) for sub-partitioning.

3

Create sub-partitions for date ranges in the 'North' region

Create two sub-partitions of sales_north named sales_north_1 and sales_north_2. The first sub-partition should cover sale_date from '2023-01-01' to '2023-07-01' (inclusive start, exclusive end), and the second from '2023-07-01' to '2024-01-01'.

PostgreSQL

CREATE TABLE sales (
    id INT,
    region TEXT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('North')
    PARTITION BY RANGE (sale_date);

CREATE TABLE sales_south PARTITION OF sales
    FOR VALUES IN ('South')
    PARTITION BY RANGE (sale_date);

-- Create sub-partitions for sales_north for two date ranges
# Your code here

Need a hint?

Use FOR VALUES FROM ('start_date') TO ('end_date') to define range partitions.

4

Create sub-partitions for date ranges in the 'South' region

Create two sub-partitions of sales_south named sales_south_1 and sales_south_2. The first sub-partition should cover sale_date from '2023-01-01' to '2023-07-01' (inclusive start, exclusive end), and the second from '2023-07-01' to '2024-01-01'.

PostgreSQL

CREATE TABLE sales (
    id INT,
    region TEXT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('North')
    PARTITION BY RANGE (sale_date);

CREATE TABLE sales_south PARTITION OF sales
    FOR VALUES IN ('South')
    PARTITION BY RANGE (sale_date);

CREATE TABLE sales_north_1 PARTITION OF sales_north
    FOR VALUES FROM ('2023-01-01') TO ('2023-07-01');

CREATE TABLE sales_north_2 PARTITION OF sales_north
    FOR VALUES FROM ('2023-07-01') TO ('2024-01-01');

-- Create sub-partitions for sales_south for two date ranges
# Your code here

Need a hint?

Repeat the sub-partitioning pattern used for sales_north on sales_south.