Hash partitioning for distribution in PostgreSQL - Mini Project: Build & Apply

Hash Partitioning for Distribution in PostgreSQL

📖 Scenario: You are managing a large customer database for an online store. To improve query performance and data management, you want to distribute the customer data across multiple partitions based on their customer ID using hash partitioning.

🎯 Goal: Create a PostgreSQL table customers partitioned by hash on the customer_id column, with 4 partitions. Each partition should store customers whose customer_id hashes to that partition.

📋 What You'll Learn

Create a parent table customers partitioned by hash on customer_id

Create exactly 4 partitions named customers_part_0 to customers_part_3

Each partition must be defined with FOR VALUES WITH (MODULUS 4, REMAINDER n) where n is 0 to 3

Include columns customer_id (integer) and customer_name (text) in the parent table

💡 Why This Matters

🌍 Real World

Hash partitioning helps distribute large datasets evenly across partitions, improving query speed and maintenance in real-world databases.

💼 Career

Database administrators and backend developers use partitioning to optimize large-scale data storage and retrieval.

1

Create the parent table with hash partitioning

Create a table called customers with columns customer_id (integer) and customer_name (text). Partition this table by HASH on the customer_id column.

PostgreSQL

-- Create the parent table 'customers' partitioned by hash on 'customer_id'
-- Your code here

Hint

Use PARTITION BY HASH (customer_id) in the table definition to enable hash partitioning.

2

Create the first two hash partitions

Create two partitions named customers_part_0 and customers_part_1 for the customers table. Use FOR VALUES WITH (MODULUS 4, REMAINDER 0) for customers_part_0 and FOR VALUES WITH (MODULUS 4, REMAINDER 1) for customers_part_1.

PostgreSQL

CREATE TABLE customers (
    customer_id INT,
    customer_name TEXT
) PARTITION BY HASH (customer_id);

-- Create partition customers_part_0
-- Your code here

Hint

Use CREATE TABLE <partition_name> PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER n) to create each partition.

3

Create the remaining two hash partitions

Create two more partitions named customers_part_2 and customers_part_3 for the customers table. Use FOR VALUES WITH (MODULUS 4, REMAINDER 2) for customers_part_2 and FOR VALUES WITH (MODULUS 4, REMAINDER 3) for customers_part_3.

PostgreSQL

CREATE TABLE customers_part_0 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE customers_part_1 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

-- Create partition customers_part_2
-- Your code here

Hint

Remember to use the correct remainder values 2 and 3 for these partitions.

4

Verify the partitioning setup

Add a comment on the parent table customers describing that it is hash partitioned by customer_id into 4 partitions.

PostgreSQL

CREATE TABLE customers (
    customer_id INT,
    customer_name TEXT
) PARTITION BY HASH (customer_id);

CREATE TABLE customers_part_0 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE customers_part_1 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE customers_part_2 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE customers_part_3 PARTITION OF customers
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Add a comment on the parent table
-- Your code here

Hint

Use COMMENT ON TABLE customers IS '...' to add a descriptive comment.

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