Sub-partitioning in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Query on Sub-partitioned Table

Given a table sales sub-partitioned by region and then by month, what will be the output of this query?

SELECT region, month, SUM(amount) FROM sales GROUP BY region, month ORDER BY region, month;

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  region TEXT,
  month INT,
  amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales FOR VALUES IN ('East') PARTITION BY RANGE (month);
CREATE TABLE sales_east_jan PARTITION OF sales_east FOR VALUES FROM (1) TO (2);
CREATE TABLE sales_east_feb PARTITION OF sales_east FOR VALUES FROM (2) TO (3);

INSERT INTO sales (region, month, amount) VALUES
('East', 1, 100), ('East', 1, 150), ('East', 2, 200), ('East', 2, 50);

A [{"region": "East", "month": 1, "sum": 250}, {"region": "East", "month": 2, "sum": 300}]

B [{"region": "East", "month": 1, "sum": 250}, {"region": "East", "month": 2, "sum": 250}]

C [{"region": "East", "month": 1, "sum": 250}, {"region": "East", "month": 2, "sum": 200}]

D [{"region": "East", "month": 1, "sum": 100}, {"region": "East", "month": 2, "sum": 250}]

💡 Hint

Remember that SUM adds all amounts for each group.

The query groups by region and month. For month 1, amounts are 100 and 150, summing to 250. For month 2, amounts are 200 and 50, summing to 250. So the correct sums are 250 for month 1 and 250 for month 2.

Option B shows 300 for month 2, which is incorrect. The correct sum is 250.

📝 Syntax

intermediate

Identify the Correct Syntax for Sub-partitioning

Which of the following SQL statements correctly creates a table orders partitioned by country and sub-partitioned by order_date range in PostgreSQL?

A CREATE TABLE orders (id INT, country TEXT, order_date DATE) PARTITION BY LIST (country, order_date);

B

CREATE TABLE orders (id INT, country TEXT, order_date DATE) PARTITION BY RANGE (country);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US') PARTITION BY LIST (order_date);

C

CREATE TABLE orders (id INT, country TEXT, order_date DATE) PARTITION BY LIST (country);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US') PARTITION BY RANGE (order_date);

D

CREATE TABLE orders (id INT, country TEXT, order_date DATE) PARTITION BY RANGE (order_date);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US') PARTITION BY LIST (country);

💡 Hint

Sub-partitioning means partitioning a partitioned table again by another column.

Option C correctly partitions orders by country using LIST, then sub-partitions the orders_us partition by RANGE on order_date. Other options misuse partition types or combine columns incorrectly.

❓ optimization

advanced

Optimizing Query Performance on Sub-partitioned Tables

You have a large transactions table partitioned by region and sub-partitioned by transaction_date. Which indexing strategy will most improve query performance filtering by region and transaction_date?

A Create local indexes on <code>transaction_date</code> for each sub-partition.

B Create a global index on <code>transaction_date</code> only.

C Create a composite index on (<code>region</code>, <code>transaction_date</code>) on the main table.

D Create a composite index on (<code>transaction_date</code>, <code>region</code>) on each sub-partition.

💡 Hint

PostgreSQL does not support global indexes on partitioned tables.

PostgreSQL requires indexes on each partition or sub-partition. Creating local indexes on transaction_date for each sub-partition helps queries filter efficiently. Global indexes are not supported, so option A is invalid. Composite indexes on the main table do not exist; indexes must be on partitions.

🔧 Debug

advanced

Debugging Partition Constraint Violation

You created a sub-partitioned table logs partitioned by server and sub-partitioned by log_date. Inserting a row with server = 'A' and log_date = '2024-07-01' causes a constraint violation. What is the most likely cause?

PostgreSQL

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  server TEXT,
  log_date DATE,
  message TEXT
) PARTITION BY LIST (server);

CREATE TABLE logs_a PARTITION OF logs FOR VALUES IN ('A') PARTITION BY RANGE (log_date);
CREATE TABLE logs_a_july PARTITION OF logs_a FOR VALUES FROM ('2024-06-01') TO ('2024-06-30');

A The <code>server</code> value 'A' is not allowed in the main partition.

B The primary key constraint is violated because <code>id</code> is duplicated.

C The <code>message</code> column is missing a NOT NULL constraint.

D The row's <code>log_date</code> value '2024-07-01' does not fall into any sub-partition range.

💡 Hint

Check the ranges defined for sub-partitions.

The sub-partition logs_a_july covers dates from '2024-06-01' to '2024-06-30'. The inserted date '2024-07-01' is outside this range and no other sub-partition exists for July, causing a constraint violation.

🧠 Conceptual

expert

Understanding Sub-partitioning Benefits

Which of the following best explains the main benefit of using sub-partitioning in a large database table?

A It allows organizing data into smaller, more manageable pieces for faster query pruning and maintenance.

B It automatically compresses data to save disk space without user intervention.

C It merges multiple tables into one to simplify schema design.

D It eliminates the need for indexes by optimizing full table scans.

💡 Hint

Think about how partitioning helps with performance and management.

Sub-partitioning breaks data into smaller parts based on multiple columns, which helps the database quickly skip irrelevant data during queries and makes maintenance tasks easier. It does not compress data automatically, merge tables, or remove the need for indexes.

Practice

1. What is the main purpose of sub-partitioning in PostgreSQL?

easy

A. To encrypt data within partitions

B. To create backups of partitions automatically

C. To merge multiple partitions into one

D. To split data twice for better organization and faster queries

Solution

1. Step 1: Understand partitioning basics

   Partitioning divides a table into parts to improve management and performance.

2. Step 2: Recognize sub-partitioning role

   Sub-partitioning splits each partition further, organizing data more finely and speeding up queries.

3. Final Answer:

   To split data twice for better organization and faster queries -> Option D

4. Quick Check:

   Sub-partitioning = double data split [OK]

Hint: Sub-partitioning means splitting partitions again [OK]

Common Mistakes:

• Thinking sub-partitioning creates backups
• Confusing sub-partitioning with encryption
• Believing it merges partitions

2. Which of the following is the correct syntax to create a sub-partitioned table in PostgreSQL?

easy

A. CREATE TABLE sales (id INT, region TEXT, month INT) SUBPARTITION BY RANGE (region) PARTITION BY LIST (month);

B. CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) PARTITION BY LIST (month);

C. CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month);

D. CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY LIST (region) SUBPARTITION BY HASH (month);

Solution

1. Step 1: Identify correct keywords for partitioning

   PostgreSQL uses PARTITION BY for main partition and SUBPARTITION BY for sub-partition.

2. Step 2: Check syntax order and clauses

   CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month); correctly uses PARTITION BY RANGE then SUBPARTITION BY LIST, matching PostgreSQL syntax.

3. Final Answer:

   CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month); -> Option C

4. Quick Check:

   Use PARTITION BY then SUBPARTITION BY [OK]

Hint: Use PARTITION BY first, then SUBPARTITION BY [OK]

Common Mistakes:

• Using PARTITION BY twice instead of SUBPARTITION BY
• Swapping PARTITION BY and SUBPARTITION BY keywords
• Using SUBPARTITION BY before PARTITION BY

3. Given the following table and partitions:

CREATE TABLE orders (id INT, country TEXT, year INT) PARTITION BY LIST (country) SUBPARTITION BY RANGE (year);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US') SUBPARTITION BY RANGE (year);
CREATE TABLE orders_us_2022 PARTITION OF orders_us FOR VALUES FROM (2022) TO (2023);

What will be the result of SELECT * FROM orders WHERE country = 'US' AND year = 2022; if there are rows with country 'US' and year 2022?

medium

A. Rows with country 'US' and year 2022 will be returned

B. No rows will be returned because subpartition is missing

C. Syntax error due to incorrect partitioning

D. Rows with any country but year 2022 will be returned

Solution

1. Step 1: Understand partition and subpartition setup

   The table is partitioned by country (LIST) and subpartitioned by year (RANGE). The 'US' partition and 2022 subpartition exist.

2. Step 2: Query filters match partition and subpartition

   The query filters country='US' and year=2022, matching the defined partitions, so matching rows will be found.

3. Final Answer:

   Rows with country 'US' and year 2022 will be returned -> Option A

4. Quick Check:

   Partition + subpartition match = rows returned [OK]

Hint: Query matches partition and subpartition filters [OK]

Common Mistakes:

• Assuming no rows because subpartition is complex
• Thinking query causes syntax error
• Ignoring subpartition filtering

4. You wrote this code:

CREATE TABLE logs (id INT, region TEXT, day DATE) PARTITION BY RANGE (region) SUBPARTITION BY LIST (day);

What is the error in this statement?

medium

A. RANGE partitioning cannot be done on a TEXT column

B. Partitioning by RANGE requires a numeric or date type, not TEXT

C. Syntax error: SUBPARTITION BY must come before PARTITION BY

D. SUBPARTITION BY LIST cannot be used with RANGE partitioning

Solution

1. Step 1: Check partition column data type

   Partitioning by RANGE requires a column with an orderable type like numeric or date, not TEXT.

2. Step 2: Identify the error cause

   Here, region is TEXT, so RANGE partitioning on it is invalid.

3. Final Answer:

   Partitioning by RANGE requires a numeric or date type, not TEXT -> Option B

4. Quick Check:

   RANGE needs numeric/date, not TEXT [OK]

Hint: RANGE partition needs numeric or date column [OK]

Common Mistakes:

• Thinking TEXT can be used for RANGE partitioning
• Confusing order of PARTITION BY and SUBPARTITION BY
• Assuming SUBPARTITION BY LIST is invalid with RANGE

5. You want to create a sales table partitioned by region (LIST) and subpartitioned by sale_date (RANGE). Which approach correctly handles the subpartitioning to optimize query performance for recent sales?

hard

A. Partition by LIST on region, then subpartition by RANGE on sale_date with recent years as separate subpartitions

B. Partition by RANGE on sale_date, then subpartition by LIST on region with all regions in one subpartition

C. Partition by HASH on region, no subpartitioning needed for sale_date

D. Partition by LIST on sale_date, then subpartition by RANGE on region

Solution

1. Step 1: Match partitioning to data and query needs

   Partitioning by region (LIST) groups data by location, then subpartitioning by sale_date (RANGE) organizes by time.

2. Step 2: Optimize recent sales queries

   Using RANGE subpartitions for recent years allows fast access to recent data, improving query speed.

3. Final Answer:

   Partition by LIST on region, then subpartition by RANGE on sale_date with recent years as separate subpartitions -> Option A

4. Quick Check:

   LIST then RANGE for region and date [OK]

Hint: Partition by region LIST, subpartition by date RANGE [OK]

Common Mistakes:

• Reversing partition and subpartition order
• Using HASH partitioning without subpartitioning
• Partitioning sale_date by LIST instead of RANGE