Range partitioning by date in PostgreSQL - Mini Project: Build & Apply

Range Partitioning by Date in PostgreSQL

📖 Scenario: You are managing a sales database for a retail company. The sales data grows quickly, so you want to organize the sales records by date to improve query speed and management.

🎯 Goal: Create a partitioned table sales in PostgreSQL that stores sales records. Partition the table by range on the sale_date column, with separate partitions for each quarter of the year 2024.

📋 What You'll Learn

Create a main table sales partitioned by range on sale_date.

Create four partitions named sales_q1_2024, sales_q2_2024, sales_q3_2024, and sales_q4_2024.

Each partition should cover one quarter of the year 2024.

Use the sale_date column to define the range boundaries.

💡 Why This Matters

🌍 Real World

Range partitioning by date is common in sales, logs, and event data to improve query speed and data management.

💼 Career

Database administrators and backend developers use partitioning to optimize large databases and maintain performance.

1

Create the main partitioned table

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

PostgreSQL

# Create the main partitioned table sales
# Your code here

Need a hint?

Use PARTITION BY RANGE (sale_date) when creating the table.

2

Create the first partition for Q1 2024

Create a partition table called sales_q1_2024 for the sales table. It should include sales from January 1, 2024, up to but not including April 1, 2024.

PostgreSQL

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

-- Create partition sales_q1_2024
-- Your code here

Need a hint?

Use PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-04-01').

3

Create partitions for Q2 and Q3 2024

Create two more partitions: sales_q2_2024 for April 1, 2024, to July 1, 2024, and sales_q3_2024 for July 1, 2024, to October 1, 2024.

PostgreSQL

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_q1_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- Create partitions sales_q2_2024 and sales_q3_2024
-- Your code here

Need a hint?

Create each partition with PARTITION OF sales FOR VALUES FROM (...) TO (...) for the correct date ranges.

4

Create the final partition for Q4 2024

Create the last partition called sales_q4_2024 for the sales table. It should cover sales from October 1, 2024, up to but not including January 1, 2025.

PostgreSQL

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_q1_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_q2_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_q3_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

-- Create partition sales_q4_2024
-- Your code here

Need a hint?

Use FOR VALUES FROM ('2024-10-01') TO ('2025-01-01') for the last partition.