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.