List partitioning by category in PostgreSQL - Mini Project: Build & Apply

List Partitioning by Category in PostgreSQL

📖 Scenario: You are managing a database for an online store. The store sells products in different categories like Electronics, Clothing, and Books. To improve performance and organization, you want to split the main products table into smaller tables based on product categories using list partitioning.

🎯 Goal: Create a partitioned table products in PostgreSQL that divides data into partitions by the category column. You will create the main table, define partitions for Electronics, Clothing, and Books, and insert sample data into the correct partitions.

📋 What You'll Learn

Create a partitioned table named products partitioned by list on the category column.

Create three partitions named products_electronics, products_clothing, and products_books for categories 'Electronics', 'Clothing', and 'Books' respectively.

Insert sample rows into the products table with correct categories so they go into the right partitions.

Verify the partitioning setup with correct table and partition definitions.

💡 Why This Matters

🌍 Real World

Partitioning large tables by category helps improve query speed and manageability in real-world databases like e-commerce product catalogs.

💼 Career

Database administrators and backend developers use partitioning to optimize storage and query performance in production systems.

1

Create the main partitioned table

Write a SQL statement to create a table called products with columns id (integer), name (text), and category (text). Partition this table by list on the category column.

PostgreSQL

-- Create the main partitioned table named products
-- Your code here

Hint

Use PARTITION BY LIST (category) to specify list partitioning on the category column.

2

Create partitions for each category

Write SQL statements to create three partitions of the products table: products_electronics for category 'Electronics', products_clothing for category 'Clothing', and products_books for category 'Books'. Use FOR VALUES IN with the exact category names.

PostgreSQL

CREATE TABLE products (
    id INT,
    name TEXT,
    category TEXT
) PARTITION BY LIST (category);

-- Create partition products_electronics for 'Electronics'
-- Create partition products_clothing for 'Clothing'
-- Create partition products_books for 'Books'
-- Your code here

Hint

Use CREATE TABLE <partition_name> PARTITION OF products FOR VALUES IN ('CategoryName'); for each category.

3

Insert sample data into the partitioned table

Write SQL insert statements to add these rows into the products table: (1, 'Smartphone', 'Electronics'), (2, 'Jeans', 'Clothing'), and (3, 'Novel', 'Books').

PostgreSQL

CREATE TABLE products (
    id INT,
    name TEXT,
    category TEXT
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('Electronics');

CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('Clothing');

CREATE TABLE products_books PARTITION OF products
    FOR VALUES IN ('Books');

-- Insert (1, 'Smartphone', 'Electronics') into products
-- Insert (2, 'Jeans', 'Clothing') into products
-- Insert (3, 'Novel', 'Books') into products
-- Your code here

Hint

Insert rows into the main products table. PostgreSQL will route them to the correct partitions automatically.

4

Verify the partitioning setup

Write a SQL query to select all rows from the products table ordered by id. This will confirm that data is correctly partitioned and accessible from the main table.

PostgreSQL

CREATE TABLE products (
    id INT,
    name TEXT,
    category TEXT
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('Electronics');

CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('Clothing');

CREATE TABLE products_books PARTITION OF products
    FOR VALUES IN ('Books');

INSERT INTO products (id, name, category) VALUES (1, 'Smartphone', 'Electronics');
INSERT INTO products (id, name, category) VALUES (2, 'Jeans', 'Clothing');
INSERT INTO products (id, name, category) VALUES (3, 'Novel', 'Books');

-- Select all rows from products ordered by id
-- Your code here

Hint

Use a simple SELECT * FROM products ORDER BY id; to see all rows from all partitions combined.

Practice

1. What is the main purpose of list partitioning by category in PostgreSQL?

easy

A. To split a table into parts based on specific category values

B. To combine multiple tables into one large table

C. To encrypt data in the table for security

D. To create temporary tables for faster queries

Solution

1. Step 1: Understand list partitioning concept

   List partitioning divides a table into smaller parts based on specific category values.

2. Step 2: Identify the main purpose

   This helps organize data and speeds up queries by focusing on relevant partitions.

3. Final Answer:

   To split a table into parts based on specific category values -> Option A

4. Quick Check:

   List partitioning = split by category [OK]

Hint: List partitioning splits tables by category values [OK]

Common Mistakes:

• Confusing list partitioning with table joins
• Thinking it encrypts data
• Assuming it merges tables

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

easy

A. CREATE TABLE sales PARTITION BY GROUP (category);

B. CREATE TABLE sales PARTITION BY RANGE (category);

C. CREATE TABLE sales PARTITION BY HASH (category);

D. CREATE TABLE sales PARTITION BY LIST (category);

Solution

1. Step 1: Identify partition type syntax

   List partitioning uses PARTITION BY LIST in PostgreSQL.

2. Step 2: Match correct syntax

   Only CREATE TABLE sales PARTITION BY LIST (category); uses PARTITION BY LIST with the column name correctly.

3. Final Answer:

   CREATE TABLE sales PARTITION BY LIST (category); -> Option D

4. Quick Check:

   List partitioning syntax = PARTITION BY LIST [OK]

Hint: Use PARTITION BY LIST for list partitioning [OK]

Common Mistakes:

• Using PARTITION BY RANGE instead of LIST
• Using PARTITION BY HASH incorrectly
• Using non-existent PARTITION BY GROUP

3. Given the following setup:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  category TEXT
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products FOR VALUES IN ('electronics');
CREATE TABLE products_clothing PARTITION OF products FOR VALUES IN ('clothing');

INSERT INTO products (name, category) VALUES ('Phone', 'electronics'), ('Shirt', 'clothing');

SELECT * FROM products WHERE category = 'electronics';

What will the SELECT query return?

medium

A. No rows because category is filtered incorrectly

B. All rows from both partitions

C. Only rows where category is 'electronics', here the 'Phone' row

D. An error because partitions are not queried directly

Solution

1. Step 1: Understand partition filtering

   Query filters category = 'electronics', so only that partition is scanned.

2. Step 2: Check inserted data

   Only 'Phone' has category 'electronics', so only that row is returned.

3. Final Answer:

   Only rows where category is 'electronics', here the 'Phone' row -> Option C

4. Quick Check:

   Partition filter returns matching rows [OK]

Hint: Query filters partition by category value [OK]

Common Mistakes:

• Expecting all rows without filter
• Thinking query causes error
• Assuming partitions must be queried separately

4. Consider this incorrect partition creation:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product TEXT,
  category TEXT
) PARTITION BY LIST (category);

CREATE TABLE orders_electronics PARTITION OF orders FOR VALUES IN ('electronics', 'gadgets');

What is the error in this partition definition?

medium

A. FOR VALUES IN must list values as separate strings, not combined

B. Partitions cannot have multiple values in FOR VALUES IN clause

C. The partition table name is invalid

D. The parent table cannot be partitioned by LIST

Solution

1. Step 1: Check FOR VALUES IN syntax

   FOR VALUES IN expects a list of values as separate strings, e.g. ('electronics', 'gadgets').

2. Step 2: Identify error in given code

   The code uses multiple values in one partition which is not allowed in PostgreSQL list partitioning.

3. Final Answer:

   Partitions cannot have multiple values in FOR VALUES IN clause -> Option B

4. Quick Check:

   Each partition must have exactly one value [OK]

Hint: Each partition must have a single value in FOR VALUES IN clause [OK]

Common Mistakes:

• Using one string with commas inside
• Misnaming partition tables
• Thinking LIST partitioning is not allowed

5. You want to create a list partitioned table events by event_type with partitions for 'login', 'logout', and 'purchase'. Which of the following is the correct way to create the partitions and insert a new 'purchase' event?

hard

A. CREATE TABLE events PARTITION BY LIST (event_type);
CREATE TABLE events_login PARTITION OF events FOR VALUES IN ('login');
CREATE TABLE events_logout PARTITION OF events FOR VALUES IN ('logout');
CREATE TABLE events_purchase PARTITION OF events FOR VALUES IN ('purchase');
INSERT INTO events (event_type) VALUES ('purchase');

B. CREATE TABLE events PARTITION BY RANGE (event_type);
CREATE TABLE events_login PARTITION OF events FOR VALUES FROM ('login') TO ('logout');
INSERT INTO events (event_type) VALUES ('purchase');

C. CREATE TABLE events PARTITION BY LIST (event_type);
CREATE TABLE events_all PARTITION OF events FOR VALUES IN ('login', 'logout', 'purchase');
INSERT INTO events (event_type) VALUES ('purchase');

D. CREATE TABLE events PARTITION BY HASH (event_type);
CREATE TABLE events_hash PARTITION OF events FOR VALUES IN (1);
INSERT INTO events (event_type) VALUES ('purchase');

Solution

1. Step 1: Choose correct partition type and syntax

   List partitioning by event_type requires PARTITION BY LIST and partitions with FOR VALUES IN for each category.

2. Step 2: Verify partitions and insert

   CREATE TABLE events PARTITION BY LIST (event_type); CREATE TABLE events_login PARTITION OF events FOR VALUES IN ('login'); CREATE TABLE events_logout PARTITION OF events FOR VALUES IN ('logout'); CREATE TABLE events_purchase PARTITION OF events FOR VALUES IN ('purchase'); INSERT INTO events (event_type) VALUES ('purchase'); correctly creates partitions for 'login', 'logout', and 'purchase' and inserts a 'purchase' event into the parent table.

3. Final Answer:

   Option A with correct list partitions and insert -> Option A

4. Quick Check:

   List partitions per category + insert into parent [OK]

Hint: Create partitions per category, insert into parent table [OK]

Common Mistakes:

• Using RANGE or HASH instead of LIST
• Creating one partition for all values
• Inserting into partitions directly instead of parent