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.