CTE materialization behavior in PostgreSQL - Mini Project: Build & Apply

Understanding CTE Materialization Behavior in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database for a small online bookstore. You want to analyze sales data efficiently using Common Table Expressions (CTEs). Understanding how CTEs are materialized can help you write better queries that perform well.

🎯 Goal: Build a PostgreSQL query using a CTE to calculate total sales per author, then filter authors with sales above a certain threshold. Learn how CTE materialization affects query performance.

📋 What You'll Learn

Create a CTE named author_sales that sums sales per author from the sales table joined with books.

Define a threshold variable min_sales with value 1000.

Write a main query that selects authors from author_sales with total sales greater than min_sales.

Use PostgreSQL syntax and demonstrate CTE materialization behavior.

💡 Why This Matters

🌍 Real World

CTEs are often used in real-world databases to organize complex queries and improve readability. Understanding materialization helps optimize performance.

💼 Career

Database developers and analysts frequently write queries with CTEs. Knowing how PostgreSQL handles CTEs can help in tuning queries for faster results.

1

Create the author_sales CTE

Write a CTE named author_sales that selects author_id and the sum of amount as total_sales from the sales table joined with books on book_id. Group the results by author_id.

PostgreSQL

-- Write the CTE named author_sales here
WITH author_sales AS (
    -- Your code here
)

Hint

Use WITH author_sales AS (SELECT ...) and join sales with books on book_id. Group by author_id and sum amount.

2

Define the min_sales threshold

Add a variable min_sales and set it to 1000 using a SELECT statement with WITH clause. This will be used as a filter threshold in the next step.

PostgreSQL

WITH author_sales AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
-- Define min_sales threshold here
min_sales AS (
    -- Your code here
)

Hint

Use a CTE named min_sales that selects the number 1000 as value.

3

Write the main query filtering authors by min_sales

Write a main query that selects author_id and total_sales from author_sales where total_sales is greater than the value from min_sales. Use a join or cross join to access min_sales.value.

PostgreSQL

WITH author_sales AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
min_sales AS (
    SELECT 1000 AS value
)
-- Write the main query filtering authors here
SELECT author_id, total_sales
FROM author_sales
JOIN min_sales ON true
WHERE total_sales > min_sales.value

Hint

Join author_sales with min_sales using ON true and filter with WHERE total_sales > min_sales.value.

4

Add MATERIALIZED keyword to the author_sales CTE

Modify the author_sales CTE to include the MATERIALIZED keyword explicitly to control materialization behavior in PostgreSQL.

PostgreSQL

-- Add MATERIALIZED keyword to author_sales CTE
WITH author_sales MATERIALIZED AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
min_sales AS (
    SELECT 1000 AS value
)
SELECT author_id, total_sales
FROM author_sales
JOIN min_sales ON true
WHERE total_sales > min_sales.value

Hint

Insert the keyword MATERIALIZED right after author_sales in the CTE definition.

Practice

1. What does CTE materialization control in PostgreSQL?

easy

A. The data type of columns in the CTE

B. The order of execution of SQL statements

C. The type of join used in the query

D. Whether the CTE results are stored temporarily or inlined into the main query

Solution

1. Step 1: Understand CTE basics

   A Common Table Expression (CTE) is a temporary result set used within a query.

2. Step 2: Identify materialization role

   Materialization controls if PostgreSQL stores the CTE result temporarily or inlines it into the main query for optimization.

3. Final Answer:

   Whether the CTE results are stored temporarily or inlined into the main query -> Option D

4. Quick Check:

   CTE materialization = store or inline [OK]

Hint: Materialization means store or inline CTE results [OK]

Common Mistakes:

• Confusing materialization with join types
• Thinking it controls execution order
• Assuming it changes data types

2. Which of the following is the correct syntax to force PostgreSQL to store a CTE result instead of inlining it?

easy

A. WITH cte_name AS (SELECT * FROM table_name) MATERIALIZED

B. WITH cte_name AS NOT MATERIALIZED (SELECT * FROM table_name)

C. WITH cte_name AS MATERIALIZED (SELECT * FROM table_name)

D. WITH cte_name AS MATERIALIZED SELECT * FROM table_name

Solution

1. Step 1: Recall correct CTE syntax with materialization

   PostgreSQL syntax to force materialization is WITH cte_name AS MATERIALIZED (query).

2. Step 2: Check options for syntax correctness

   WITH cte_name AS MATERIALIZED (SELECT * FROM table_name) matches the correct syntax; others have misplaced keywords or missing parentheses.

3. Final Answer:

   WITH cte_name AS MATERIALIZED (SELECT * FROM table_name) -> Option C

4. Quick Check:

   Materialized keyword goes right after AS [OK]

Hint: Use AS MATERIALIZED right before the query in parentheses [OK]

Common Mistakes:

• Placing MATERIALIZED outside parentheses
• Using NOT MATERIALIZED to force storage
• Omitting parentheses around the query

3. Given the query:

WITH cte AS MATERIALIZED (SELECT id FROM users WHERE active = true) SELECT * FROM cte WHERE id < 10;

What is the effect of MATERIALIZED here?

medium

A. The CTE result is stored temporarily before filtering by id < 10

B. The CTE is inlined and filtered directly without storing

C. The query will produce a syntax error

D. The CTE is ignored and the main query runs alone

Solution

1. Step 1: Understand MATERIALIZED effect on CTE

   Using MATERIALIZED forces PostgreSQL to store the CTE result before the outer query uses it.

2. Step 2: Analyze query filtering

   The outer query filters the stored CTE result by id < 10, so filtering happens after storage.

3. Final Answer:

   The CTE result is stored temporarily before filtering by id < 10 -> Option A

4. Quick Check:

   MATERIALIZED = store then filter [OK]

Hint: MATERIALIZED stores CTE before outer query filters [OK]

Common Mistakes:

• Thinking filtering happens before storing
• Assuming MATERIALIZED causes syntax error
• Believing CTE is ignored

4. Consider this query:

WITH cte AS NOT MATERIALIZED (SELECT * FROM orders WHERE amount > 100) SELECT * FROM cte WHERE amount < 500;

Why might this query fail or behave unexpectedly?

medium

A. NOT MATERIALIZED is not supported in this PostgreSQL version causing syntax error

B. The CTE is stored and filtering is delayed causing slow performance

C. The query ignores the WHERE clause inside the CTE

D. The CTE is materialized twice causing duplicate rows

Solution

1. Step 1: Check PostgreSQL support for NOT MATERIALIZED

   NOT MATERIALIZED was introduced in PostgreSQL 12; older versions do not support it and cause syntax errors.

2. Step 2: Analyze query behavior if unsupported

   If the version is older, the query fails with syntax error due to unrecognized keyword.

3. Final Answer:

   NOT MATERIALIZED is not supported in this PostgreSQL version causing syntax error -> Option A

4. Quick Check:

   NOT MATERIALIZED requires PostgreSQL 12+ [OK]

Hint: Check PostgreSQL version for NOT MATERIALIZED support [OK]

Common Mistakes:

• Assuming NOT MATERIALIZED always works
• Thinking filtering is ignored
• Believing CTE duplicates rows

5. You have a complex query with a CTE that is used multiple times in the main query. You want to optimize performance by avoiding repeated execution of the CTE. Which materialization behavior should you choose and why?

hard

A. Use NOT MATERIALIZED to inline the CTE and let PostgreSQL optimize repeated execution

B. Use MATERIALIZED to store the CTE result once and reuse it multiple times

C. Avoid using CTEs and write subqueries instead for better performance

D. Use MATERIALIZED to force PostgreSQL to re-execute the CTE each time

Solution

1. Step 1: Understand repeated CTE usage

   If a CTE is referenced multiple times, storing its result once avoids repeated computation.

2. Step 2: Choose materialization behavior

   Using MATERIALIZED forces storing the CTE result, so it is computed once and reused, improving performance.

3. Final Answer:

   Use MATERIALIZED to store the CTE result once and reuse it multiple times -> Option B

4. Quick Check:

   MATERIALIZED = store once, reuse many [OK]

Hint: Store CTE once with MATERIALIZED to reuse efficiently [OK]

Common Mistakes:

• Thinking NOT MATERIALIZED avoids repeated execution
• Believing MATERIALIZED causes repeated execution
• Avoiding CTEs unnecessarily