Partition types (range, list, hash) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of range partition query

Given a table sales partitioned by range on sale_date, what rows will be returned by this query?

SELECT * FROM sales WHERE sale_date < '2023-01-01';

Assume partitions are:
- sales_2022 for dates < '2023-01-01'
- sales_2023 for dates >= '2023-01-01'

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Sample data inserted in sales_2022 and sales_2023 partitions
-- Query:
SELECT * FROM sales WHERE sale_date < '2023-01-01';

A Returns no rows because the query is invalid for partitioned tables

B Returns all rows with sale_date in 2023 only, from sales_2023 partition

C Returns all rows with sale_date in 2022 only, from sales_2022 partition

D Returns all rows from both partitions regardless of sale_date

💡 Hint

Think about how range partitions split data by date ranges.

Range partitioning divides data into partitions based on continuous ranges. The query filters for sale_date before 2023-01-01, so only the partition covering 2022 dates is scanned.

🧠 Conceptual

intermediate

Identifying partition type by definition

Which partition type is described by this definition?

"Data is divided into partitions based on a list of discrete values for a column."

A Range partitioning

B List partitioning

C Hash partitioning

D Composite partitioning

💡 Hint

Think about partitions defined by specific values, not ranges or hashes.

List partitioning divides data by explicitly listing values for each partition, unlike range or hash partitioning.

📝 Syntax

advanced

Correct syntax for hash partition creation

Which option shows the correct syntax to create a hash partitioned table users on column user_id with 4 partitions in PostgreSQL?

A

CREATE TABLE users (user_id INT, name TEXT) PARTITION BY HASH (user_id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

B

CREATE TABLE users (user_id INT, name TEXT) PARTITION BY HASH (user_id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);

C

CREATE TABLE users (user_id INT, name TEXT) PARTITION BY HASH (user_id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES IN (0,1,2,3);

D

CREATE TABLE users (user_id INT, name TEXT) PARTITION BY HASH (user_id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);

💡 Hint

Hash partitions require one partition per remainder value from 0 to modulus-1.

Hash partitioning requires defining each partition with a modulus and a unique remainder. Option A correctly defines all 4 partitions with remainders 0 to 3.

❓ optimization

advanced

Best partition type for querying by month

You have a large orders table with a order_date column. Most queries filter by month. Which partition type will optimize query performance best?

A Range partitioning on order_date by month ranges

B Hash partitioning on order_date

C List partitioning on order_date with each day as a list value

D No partitioning, use indexing instead

💡 Hint

Think about how to group continuous time periods efficiently.

Range partitioning by month allows pruning partitions for queries filtering by month, improving performance. List partitioning by day is too granular and hash partitioning doesn't help with date ranges.

🔧 Debug

expert

Why does this partition insert fail?

Given this table creation:

CREATE TABLE logs (id SERIAL, log_level TEXT, message TEXT) PARTITION BY LIST (log_level);

And this partition:

CREATE TABLE logs_info PARTITION OF logs FOR VALUES IN ('INFO', 'DEBUG');

Why does this INSERT fail?

INSERT INTO logs (log_level, message) VALUES ('ERROR', 'test');

A Because 'ERROR' is not a valid log_level value in the table schema

B Because the INSERT syntax is invalid for partitioned tables

C Because the partition logs_info includes 'ERROR' but the INSERT filters it out

D Because there is no partition defined for 'ERROR' values, so INSERT fails with no matching partition error

💡 Hint

Consider what happens when a value is not assigned to any partition in list partitioning.

In list partitioning, all possible values must be assigned to partitions. Inserting a value not assigned to any partition causes an error because no partition holds those rows.