Partition types (range, list, hash) in PostgreSQL - Mini Project: Build & Apply

Create and Use Table Partitions in PostgreSQL

📖 Scenario: You are managing a sales database for a retail company. The company wants to organize their sales data efficiently by splitting the main sales table into smaller parts based on different criteria. This will help speed up queries and make data management easier.

🎯 Goal: Build partitioned tables in PostgreSQL demonstrating three types of partitions: range, list, and hash. Create sales partitioned by range on sale_date, sales_list by list on region, and sales_hash by hash on customer_id.

📋 What You'll Learn

Create a main sales table partitioned by range on the sale_date column.

Create range partitions for sales in 2023 and 2024.

Create a list-partitioned sales_list table on the region column with partitions for 'North' and 'South'.

Add a hash partition on the customer_id column with 2 partitions.

💡 Why This Matters

🌍 Real World

Partitioning large sales data helps companies quickly access relevant data by date, region, or customer, improving report speed and reducing storage overhead.

💼 Career

Database administrators and backend developers use partitioning to optimize database performance and scalability in real-world applications.

1

Create the main sales table partitioned by range

Create a table called sales with columns sale_id (integer), sale_date (date), region (text), and customer_id (integer). Partition this table by RANGE on the sale_date column.

PostgreSQL

-- Your code here to create the main sales table partitioned by RANGE on sale_date

Need a hint?

Use PARTITION BY RANGE (sale_date) when creating the table.

2

Create range partitions for 2023 and 2024 sales

Create two partitions of the sales table: sales_2023 for sales from '2023-01-01' to '2024-01-01' and sales_2024 for sales from '2024-01-01' to '2025-01-01'. Use FOR VALUES FROM and TO syntax.

PostgreSQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    region TEXT,
    customer_id INT
) PARTITION BY RANGE (sale_date);

-- Your code here to create range partitions sales_2023 and sales_2024

Need a hint?

Use CREATE TABLE <partition_name> PARTITION OF sales FOR VALUES FROM (...) TO (...) syntax.

3

Add list partitions for regions 'North' and 'South'

Create a table called sales_list partitioned by LIST on the region column. Then create two partitions: sales_list_north for region 'North' and sales_list_south for region 'South'.

PostgreSQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    region TEXT,
    customer_id INT
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Your code here to create list-partitioned sales_list table with sales_list_north and sales_list_south

Need a hint?

Use PARTITION BY LIST (region) and FOR VALUES IN ('North') syntax for the sales_list table and its partitions.

4

Create hash partitions on customer_id with 2 partitions

Create a new table called sales_hash with the same columns as sales. Partition this table by HASH on the customer_id column. Then create two hash partitions: sales_hash_0 and sales_hash_1 for hash values 0 and 1 respectively.

PostgreSQL

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    region TEXT,
    customer_id INT
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE sales_list (
    sale_id INT,
    sale_date DATE,
    region TEXT,
    customer_id INT
) PARTITION BY LIST (region);

CREATE TABLE sales_list_north PARTITION OF sales_list
    FOR VALUES IN ('North');

CREATE TABLE sales_list_south PARTITION OF sales_list
    FOR VALUES IN ('South');

-- Your code here to create sales_hash table partitioned by HASH on customer_id with 2 partitions

Need a hint?

Use PARTITION BY HASH (customer_id) and FOR VALUES WITH (MODULUS 2, REMAINDER 0) syntax.