Hash partitioning for distribution in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a hash partitioned table on the column 'user_id'.

PostgreSQL

CREATE TABLE users (
  user_id INT,
  name TEXT
) PARTITION BY [1] (user_id);

Drag options to blanks, or click blank then click option'

Alist

Bhash

Crange

Dcolumn

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' or 'list' instead of 'hash' for hash partitioning.

Using 'column' which is not a valid partition method.

The hash partitioning method distributes rows based on a hash of the partition key, here 'user_id'.

2fill in blank

medium

Complete the code to create a hash partition with 4 partitions.

PostgreSQL

CREATE TABLE users_part_[1] PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER [1]);

Drag options to blanks, or click blank then click option'

A0

B1

C2

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Starting remainder at 1 instead of 0.

Using a remainder outside the range 0 to modulus-1.

Partitions are created with a remainder value from 0 to modulus-1. Here, remainder 0 is the first partition.

3fill in blank

hard

Fix the error in the partition creation statement to correctly specify the remainder.

PostgreSQL

CREATE TABLE users_part_[1] PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER [1]);

Drag options to blanks, or click blank then click option'

A3

B1

C2

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using remainder equal to modulus causes an error.

Not understanding remainder range limits.

The remainder must be less than the modulus. Since modulus is 4, remainder 3 is the highest valid value.

4fill in blank

hard

Fill both blanks to create two hash partitions with modulus 3 and remainders 0 and 1.

PostgreSQL

CREATE TABLE users_part_[1] PARTITION OF users
FOR VALUES WITH (MODULUS [2], REMAINDER 0);

CREATE TABLE users_part_1 PARTITION OF users
FOR VALUES WITH (MODULUS [2], REMAINDER 1);

Drag options to blanks, or click blank then click option'

A0

B1

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using modulus 4 instead of 3.

Using invalid remainder values.

The partition names use remainders 0 and 1, and modulus is 3 for both partitions.

5fill in blank

hard

Fill all three blanks to create a hash partitioned table and one partition with modulus 5 and remainder 2.

PostgreSQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT
) PARTITION BY [1] (customer_id);

CREATE TABLE orders_part_[2] PARTITION OF orders
FOR VALUES WITH (MODULUS [3], REMAINDER 2);

Drag options to blanks, or click blank then click option'

Arange

Bhash

C5

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' instead of 'hash' for partitioning.

Mixing up modulus and remainder values.

The table is hash partitioned on 'customer_id'. The partition name uses remainder 2, and modulus is 5.

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