Partition pruning behavior in PostgreSQL - Mini Project: Build & Apply

Understanding Partition Pruning Behavior in PostgreSQL

📖 Scenario: You are managing a large sales database for a retail company. The sales data is partitioned by year to improve query performance. You want to learn how PostgreSQL's partition pruning works to efficiently query only relevant partitions.

🎯 Goal: Build a partitioned table for sales data by year, insert sample data, and write queries that demonstrate partition pruning behavior in PostgreSQL.

📋 What You'll Learn

Create a partitioned table named sales partitioned by range on the sale_year column.

Create partitions for years 2021, 2022, and 2023.

Insert sample sales data into each partition with exact values.

Write a query filtering sales for year 2022 to demonstrate partition pruning.

Write a query filtering sales for years 2021 and 2023 to demonstrate pruning multiple partitions.

💡 Why This Matters

🌍 Real World

Partitioning large tables by date or other keys is common in real-world databases to improve query speed and manageability.

💼 Career

Understanding partition pruning is important for database administrators and developers to optimize queries and maintain large datasets efficiently.

1

Create the partitioned table and partitions

Create a table called sales partitioned by range on the column sale_year. Then create three partitions named sales_2021, sales_2022, and sales_2023 for the year ranges 2021, 2022, and 2023 respectively.

PostgreSQL

-- Create the partitioned table and partitions
-- Your code here

Need a hint?

Use PARTITION BY RANGE (sale_year) when creating the main table. Then create partitions for each year range using FOR VALUES FROM (...) TO (...).

2

Insert sample sales data into partitions

Insert the following sales data into the sales table: (1, 2021, 100.00), (2, 2021, 150.50), (3, 2022, 200.00), (4, 2022, 250.75), (5, 2023, 300.00). Use the exact sale_id, sale_year, and amount values as shown.

PostgreSQL

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_year INT NOT NULL,
    amount NUMERIC(10,2) NOT NULL
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM (2021) TO (2022);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM (2022) TO (2023);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

-- Insert the sample sales data below
-- Your code here

Need a hint?

Use a single INSERT INTO sales (sale_id, sale_year, amount) VALUES (...), (...), ...; statement with the exact values.

3

Query sales for year 2022 to demonstrate partition pruning

Write a SELECT query to get all sales from the sales table where sale_year is 2022. Use the exact query: SELECT * FROM sales WHERE sale_year = 2022;

PostgreSQL

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_year INT NOT NULL,
    amount NUMERIC(10,2) NOT NULL
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM (2021) TO (2022);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM (2022) TO (2023);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

INSERT INTO sales (sale_id, sale_year, amount) VALUES
    (1, 2021, 100.00),
    (2, 2021, 150.50),
    (3, 2022, 200.00),
    (4, 2022, 250.75),
    (5, 2023, 300.00);

-- Write the SELECT query to get sales for year 2022 below
-- Your code here

Need a hint?

Write the exact query SELECT * FROM sales WHERE sale_year = 2022; to see partition pruning in action.

4

Query sales for years 2021 and 2023 to prune multiple partitions

Write a SELECT query to get all sales from the sales table where sale_year is either 2021 or 2023. Use the exact query: SELECT * FROM sales WHERE sale_year IN (2021, 2023);

PostgreSQL

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_year INT NOT NULL,
    amount NUMERIC(10,2) NOT NULL
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM (2021) TO (2022);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM (2022) TO (2023);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

INSERT INTO sales (sale_id, sale_year, amount) VALUES
    (1, 2021, 100.00),
    (2, 2021, 150.50),
    (3, 2022, 200.00),
    (4, 2022, 250.75),
    (5, 2023, 300.00);

SELECT * FROM sales WHERE sale_year = 2022;

-- Write the SELECT query to get sales for years 2021 and 2023 below
-- Your code here

Need a hint?

Use the exact query SELECT * FROM sales WHERE sale_year IN (2021, 2023); to see pruning of multiple partitions.