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

Need a 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

Need a 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

Need a 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

Need a hint?

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