Hash partitioning for distribution in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of hash partition selection

Given a table users partitioned by hash on user_id into 4 partitions, what is the output of the following query?

SELECT tableoid::regclass, user_id FROM users WHERE user_id = 7;

Assume user_id 7 is inserted.

PostgreSQL

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);

INSERT INTO users VALUES (7, 'Alice');

SELECT tableoid::regclass, user_id FROM users WHERE user_id = 7;

A users_p0 | 7

B users_p1 | 7

C users_p2 | 7

D users_p3 | 7

💡 Hint

Hash partitioning uses the hash of the key modulo the number of partitions to decide placement.

The hash of 7 modulo 4 is 3, so the row goes to partition users_p3. The query shows the partition table name and user_id.

🧠 Conceptual

intermediate

Understanding hash partition modulus and remainder

In PostgreSQL hash partitioning, what do the MODULUS and REMAINDER values specify for a partition?

A MODULUS is the hash function used; REMAINDER is the key column name

B MODULUS is the maximum value of the key; REMAINDER is the minimum value of the key

C MODULUS is the total number of partitions; REMAINDER is the partition number for rows where hash(key) % MODULUS = REMAINDER

D MODULUS is the number of rows per partition; REMAINDER is the partition size in MB

💡 Hint

Think about how hash(key) % number_of_partitions decides placement.

MODULUS defines how many partitions exist. REMAINDER defines which partition a row belongs to based on the hash value modulo MODULUS.

📝 Syntax

advanced

Identify the syntax error in hash partition creation

Which option contains a syntax error when creating a hash partition in PostgreSQL?

PostgreSQL

CREATE TABLE sales (id INT, amount NUMERIC) PARTITION BY HASH (id);

-- Partition creation options below

A CREATE TABLE sales_p3 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 3);

B CREATE TABLE sales_p1 PARTITION OF sales FOR VALUES WITH (MODULUS 4 REMAINDER 1);

C CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 2);

D CREATE TABLE sales_p0 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 0);

💡 Hint

Check punctuation between MODULUS and REMAINDER.

Option B is missing a comma between MODULUS 4 and REMAINDER 1, causing a syntax error.

❓ optimization

advanced

Optimizing query performance on hash partitioned table

You have a hash partitioned table orders on order_id with 8 partitions. Which query will most efficiently retrieve the row with order_id = 12345?

A SELECT * FROM orders WHERE order_id = 12345;

B SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM orders WHERE order_id = 12345);

C SELECT * FROM orders WHERE order_id % 8 = 1 AND order_id = 12345;

D SELECT * FROM orders WHERE order_id > 10000;

💡 Hint

PostgreSQL automatically routes queries on partition keys to the correct partition.

Query C uses the partition key in WHERE clause, so PostgreSQL prunes partitions and queries only the relevant one. Option A is redundant and less efficient. Options A and D scan multiple partitions.

🔧 Debug

expert

Why does this hash partitioned table insert fail?

Given the table and partitions:

CREATE TABLE logs (log_id INT, message TEXT) PARTITION BY HASH (log_id);
CREATE TABLE logs_p0 PARTITION OF logs FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Why does INSERT INTO logs VALUES (5, 'error'); fail?

A Partition logs_p2 has a different MODULUS (4) than the parent table's MODULUS (3), causing a partition mismatch error.

B The value 5 is out of range for all partitions, so no partition matches.

C The INSERT statement is missing a column list, causing a syntax error.

D Partition logs_p1 is missing, so the insert fails.

💡 Hint

All partitions must have the same MODULUS value for hash partitioning.

Partition logs_p2 uses MODULUS 4, which conflicts with the parent's MODULUS 3. This causes PostgreSQL to reject inserts that hash to remainder 2 because no valid partition exists for that remainder under MODULUS 3.

Practice

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

easy

A. To split data into parts using a hash function on a column

B. To sort data alphabetically in each partition

C. To store data only on one server

D. To encrypt data for security

Solution

1. Step 1: Understand hash partitioning concept

   Hash partitioning divides data by applying a hash function to a column value, distributing rows into partitions.

2. Step 2: Compare options with concept

   Only To split data into parts using a hash function on a column correctly describes this purpose; others describe unrelated features.

3. Final Answer:

   To split data into parts using a hash function on a column -> Option A

4. Quick Check:

   Hash partitioning = split data by hash [OK]

Hint: Hash partitioning splits data by hashing a column [OK]

Common Mistakes:

• Confusing hash partitioning with sorting
• Thinking it stores data on one server only
• Mixing partitioning with encryption

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

easy

A. CREATE TABLE sales PARTITION BY COLUMN (region);

B. CREATE TABLE sales PARTITION BY RANGE (region);

C. CREATE TABLE sales PARTITION BY LIST (region);

D. CREATE TABLE sales PARTITION BY HASH (region);

Solution

1. Step 1: Identify partitioning syntax

   PostgreSQL uses PARTITION BY HASH(column) to create hash partitions.

2. Step 2: Match syntax with options

   Only CREATE TABLE sales PARTITION BY HASH (region); uses PARTITION BY HASH correctly; others use different partition types or invalid syntax.

3. Final Answer:

   CREATE TABLE sales PARTITION BY HASH (region); -> Option D

4. Quick Check:

   Hash partition syntax = PARTITION BY HASH [OK]

Hint: Use PARTITION BY HASH(column) for hash partitioning [OK]

Common Mistakes:

• Using RANGE or LIST instead of HASH
• Writing PARTITION BY COLUMN which is invalid
• Omitting parentheses around column

3. Given the table definition:

CREATE TABLE users (id INT, name TEXT) PARTITION BY HASH (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);

Which partition will the row with id = 7 be stored in?

medium

A. users_p3

B. users_p1

C. users_p2

D. users_p0

Solution

1. Step 1: Calculate hash partition remainder

   Partition is chosen by (hash(id) % modulus). Assuming hash(id) = id for simplicity, 7 % 4 = 3.

2. Step 2: Match remainder to partition

   Remainder 3 corresponds to partition users_p3.

3. Final Answer:

   users_p3 -> Option A

4. Quick Check:

   7 % 4 = 3 -> users_p3 [OK]

Hint: Compute id % modulus to find partition remainder [OK]

Common Mistakes:

• Confusing remainder with modulus
• Using id directly without modulo
• Mixing partition numbers

4. You try to create a hash partition with this command:

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 3);

What is the error in this statement?

medium

A. MODULUS must be a prime number

B. REMAINDER cannot be equal to or greater than MODULUS

C. Partition name must start with 'orders_'

D. FOR VALUES WITH is not valid syntax for hash partitions

Solution

1. Step 1: Understand modulus and remainder rules

   Remainder must be less than modulus because remainder is result of modulo operation.

2. Step 2: Check given values

   MODULUS is 3, REMAINDER is 3, which is invalid since remainder must be 0, 1, or 2.

3. Final Answer:

   REMAINDER cannot be equal to or greater than MODULUS -> Option B

4. Quick Check:

   Remainder < Modulus rule violated [OK]

Hint: Remainder must be less than modulus in partitions [OK]

Common Mistakes:

• Setting remainder equal to modulus
• Thinking modulus must be prime
• Misunderstanding syntax for hash partitions

5. You want to distribute a large logs table by hashing the user_id column into 5 partitions. Which of the following is the correct way to define the partitions?

hard

A. Create 5 partitions with MODULUS 6 and REMAINDER values from 0 to 5

B. Create 5 partitions with MODULUS 4 and REMAINDER values from 0 to 4

C. Create 5 partitions with MODULUS 5 and REMAINDER values from 0 to 4

D. Create 5 partitions with MODULUS 5 and REMAINDER values from 1 to 5

Solution

1. Step 1: Understand modulus and remainder for partitions

   MODULUS is total partitions count; REMAINDER ranges from 0 to MODULUS-1.

2. Step 2: Apply to 5 partitions

   MODULUS must be 5; REMAINDER values must be 0,1,2,3,4 for 5 partitions.

3. Final Answer:

   Create 5 partitions with MODULUS 5 and REMAINDER values from 0 to 4 -> Option C

4. Quick Check:

   Partitions = MODULUS 5, REMAINDER 0-4 [OK]

Hint: Use MODULUS = partitions count; REMAINDER from 0 to MODULUS-1 [OK]

Common Mistakes:

• Setting remainder range incorrectly
• Using wrong modulus number
• Starting remainder at 1 instead of 0