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

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

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

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

Hint

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

Practice

1. What is the main purpose of partition pruning in PostgreSQL?

easy

A. To merge all partitions into one table

B. To create new partitions automatically

C. To skip scanning partitions that cannot contain matching rows

D. To backup partitions separately

Solution

1. Step 1: Understand partition pruning concept

   Partition pruning means the database avoids scanning partitions that do not match the query filter.

2. Step 2: Identify the main benefit

   This skipping reduces query time by focusing only on relevant partitions.

3. Final Answer:

   To skip scanning partitions that cannot contain matching rows -> Option C

4. Quick Check:

   Partition pruning = skip irrelevant partitions [OK]

Hint: Partition pruning skips irrelevant partitions to speed queries [OK]

Common Mistakes:

• Thinking pruning merges partitions
• Assuming pruning creates partitions
• Confusing pruning with backup

2. Which of the following WHERE clauses will enable partition pruning on a table partitioned by column region?

easy

A. WHERE region = 'US'

B. WHERE UPPER(region) = 'US'

C. WHERE LENGTH(region) > 2

D. WHERE region LIKE '%US%'

Solution

1. Step 1: Identify pruning conditions

   Partition pruning works best with simple direct comparisons on the partition key.

2. Step 2: Analyze each option

   WHERE region = 'US' uses a direct equality on region, enabling pruning. Options A, B, and D use functions or patterns, preventing pruning.

3. Final Answer:

   WHERE region = 'US' -> Option A

4. Quick Check:

   Simple equality on partition key enables pruning [OK]

Hint: Use simple column = value filters on partition keys [OK]

Common Mistakes:

• Using functions on partition keys disables pruning
• Using LIKE patterns disables pruning
• Assuming any WHERE clause prunes partitions

3. Given a table sales partitioned by year with partitions for 2021 and 2022, what will the query below scan?

SELECT * FROM sales WHERE year = 2021;

medium

A. Only the 2021 partition

B. Both 2021 and 2022 partitions

C. No partitions, query returns empty

D. All partitions plus a full table scan

Solution

1. Step 1: Understand partition pruning with equality filter

   The query filters on year = 2021, which matches exactly one partition.

2. Step 2: Determine scanned partitions

   PostgreSQL will prune and scan only the 2021 partition, skipping 2022.

3. Final Answer:

   Only the 2021 partition -> Option A

4. Quick Check:

   Filter on partition key = value scans matching partition only [OK]

Hint: Equality on partition key scans only matching partition [OK]

Common Mistakes:

• Assuming all partitions scan regardless of filter
• Thinking query returns empty if filter matches a partition
• Believing full table scan always happens

4. You wrote this query on a partitioned table orders partitioned by order_date:

SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

Why might partition pruning NOT occur?

medium

A. Partition pruning only works with numeric columns

B. Using a function on the partition key disables pruning

C. The query syntax is invalid

D. Partition pruning requires an index on order_date

Solution

1. Step 1: Identify pruning limitation

   Partition pruning requires direct use of the partition key in filters without wrapping functions.

2. Step 2: Analyze the query

   The query uses EXTRACT(YEAR FROM order_date), a function on the partition key, preventing pruning.

3. Final Answer:

   Using a function on the partition key disables pruning -> Option B

4. Quick Check:

   Functions on partition keys disable pruning [OK]

Hint: Avoid functions on partition keys for pruning [OK]

Common Mistakes:

• Thinking pruning needs indexes
• Believing pruning works with any filter
• Assuming query syntax error causes pruning failure

5. A table events is range partitioned by event_date with monthly partitions. You want to query events in March 2023. Which query will maximize partition pruning?

hard

A. SELECT * FROM events WHERE event_date BETWEEN '2023-02-28' AND '2023-03-31';

B. SELECT * FROM events WHERE EXTRACT(MONTH FROM event_date) = 3 AND EXTRACT(YEAR FROM event_date) = 2023;

C. SELECT * FROM events WHERE TO_CHAR(event_date, 'YYYY-MM') = '2023-03';

D. SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01';

Solution

1. Step 1: Understand pruning with range partitions

   Range partitions work best with direct range conditions on the partition key.

2. Step 2: Evaluate each query

   SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01'; uses a direct range filter on event_date, enabling pruning. Options B and C use functions, disabling pruning. SELECT * FROM events WHERE event_date BETWEEN '2023-02-28' AND '2023-03-31'; includes dates outside March, scanning extra partitions.

3. Final Answer:

   SELECT * FROM events WHERE event_date >= '2023-03-01' AND event_date < '2023-04-01'; -> Option D

4. Quick Check:

   Direct range filters maximize pruning [OK]

Hint: Use direct range filters on partition keys for pruning [OK]

Common Mistakes:

• Using functions disables pruning
• Including extra dates scans more partitions
• Assuming BETWEEN always prunes perfectly