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

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

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

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

Hint

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

Practice

1. Which partition type in PostgreSQL is best suited for dividing a table based on continuous ranges of values, such as dates or numbers?

easy

A. HASH partitioning

B. LIST partitioning

C. RANGE partitioning

D. NONE partitioning

Solution

1. Step 1: Understand partition types

   RANGE partitions split data into continuous ranges, like dates or numeric intervals.

2. Step 2: Match partition type to use case

   Since the question asks about continuous ranges, RANGE partitioning fits best.

3. Final Answer:

   RANGE partitioning -> Option C

4. Quick Check:

   Continuous ranges = RANGE partitioning [OK]

Hint: Continuous values? Choose RANGE partitioning [OK]

Common Mistakes:

• Confusing LIST with RANGE for continuous data
• Thinking HASH is for ordered ranges
• Assuming NONE is a valid partition type

2. Which of the following is the correct syntax to create a LIST partitioned table in PostgreSQL?

easy

A. CREATE TABLE sales (id INT, region TEXT) PARTITION BY LIST (region);

B. CREATE TABLE sales PARTITION BY LIST region (id INT, region TEXT);

C. CREATE TABLE sales (id INT, region TEXT) PARTITION BY RANGE (region);

D. CREATE TABLE sales (id INT, region TEXT) PARTITION BY HASH (region);

Solution

1. Step 1: Identify correct PARTITION BY syntax

   PostgreSQL syntax requires PARTITION BY followed by partition type and column in parentheses after table columns.

2. Step 2: Check each option

   CREATE TABLE sales (id INT, region TEXT) PARTITION BY LIST (region); uses correct syntax: columns first, then PARTITION BY LIST (region). Options A, B, C have syntax errors or wrong partition type.

3. Final Answer:

   CREATE TABLE sales (id INT, region TEXT) PARTITION BY LIST (region); -> Option A

4. Quick Check:

   Correct syntax = CREATE TABLE sales (id INT, region TEXT) PARTITION BY LIST (region); [OK]

Hint: PARTITION BY type (column) after columns [OK]

Common Mistakes:

• Placing PARTITION BY before column definitions
• Using wrong partition type for LIST
• Missing parentheses around partition column

3. Given the following partitioned table and inserts:

CREATE TABLE orders (
  order_id INT,
  order_date DATE
) PARTITION BY RANGE (order_date);

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

INSERT INTO orders VALUES (1, '2023-06-15');
INSERT INTO orders VALUES (2, '2022-12-31');

What will happen when the second insert is executed?

medium

A. The row is inserted into orders_2023 partition

B. The row is rejected with a constraint violation error

C. The row is inserted into a default partition automatically

D. The row is inserted into the parent table without partition

Solution

1. Step 1: Understand RANGE partition boundaries

   The orders_2023 partition accepts dates from 2023-01-01 up to but not including 2024-01-01.

2. Step 2: Check the inserted date '2022-12-31'

   This date is before the partition range, so no matching partition exists for it.

3. Step 3: Behavior on no matching partition

   PostgreSQL rejects inserts that don't fit any partition unless a default partition exists (none here).

4. Final Answer:

   The row is rejected with a constraint violation error -> Option B

5. Quick Check:

   Out-of-range insert = error [OK]

Hint: Out-of-range insert without default partition causes error [OK]

Common Mistakes:

• Assuming automatic default partition insertion
• Thinking parent table stores unmatched rows
• Ignoring partition range boundaries

4. Consider this partitioned table creation:

CREATE TABLE employees (
  emp_id INT,
  department TEXT
) PARTITION BY LIST (department);

CREATE TABLE employees_sales PARTITION OF employees FOR VALUES IN ('Sales');
CREATE TABLE employees_hr PARTITION OF employees FOR VALUES IN ('HR');

Which error will occur if you try to insert INSERT INTO employees VALUES (1, 'Marketing');?

medium

A. No partition found for value 'Marketing', insert fails

B. Syntax error due to missing partition

C. Row inserted into employees_sales partition by default

D. Row inserted into parent table without partition

Solution

1. Step 1: Check defined partitions

   Partitions exist only for 'Sales' and 'HR' departments.

2. Step 2: Check inserted value 'Marketing'

   'Marketing' is not listed in any partition's VALUES list.

3. Step 3: PostgreSQL behavior on unmatched LIST value

   Without a default partition, insert fails with no matching partition error.

4. Final Answer:

   No partition found for value 'Marketing', insert fails -> Option A

5. Quick Check:

   Unlisted LIST value = insert failure [OK]

Hint: LIST partition needs matching value or default partition [OK]

Common Mistakes:

• Assuming insert goes to any partition by default
• Expecting parent table to store unmatched rows
• Confusing syntax error with runtime insert error

5. You want to evenly distribute a large table's rows across 4 partitions to improve query performance without caring about specific value ranges. Which partition type and setup is best in PostgreSQL?

hard

A. Use no partitioning and rely on indexes.

B. Use LIST partitioning with 4 specific values.

C. Use RANGE partitioning on a numeric column with 4 ranges.

D. Use HASH partitioning with 4 partitions.

Solution

1. Step 1: Understand partitioning goals

   The goal is even distribution across 4 partitions without caring about value ranges.

2. Step 2: Match partition type to goal

   HASH partitioning evenly distributes rows based on a hash function, ideal for this case.

3. Step 3: Evaluate other options

   RANGE and LIST require specific ranges or values, not suitable for even spread without criteria. No partitioning misses distribution benefits.

4. Final Answer:

   Use HASH partitioning with 4 partitions. -> Option D

5. Quick Check:

   Even distribution = HASH partitioning [OK]

Hint: Even spread without ranges? Choose HASH partitioning [OK]

Common Mistakes:

• Using RANGE or LIST when no value grouping needed
• Thinking indexes replace partitioning benefits
• Confusing HASH with LIST partitioning