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

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

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

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

Need a hint?

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