Database Essentials - Partitioning Tables and Indexes - List Partitioning

Let us understand how we can take care of list partitioning of tables.

  • It is primarily used to create partitions based on the values.

  • Here are the steps involved in creating a table using the list partitioning strategy.

    • Create a table using PARTITION BY LIST.
    • Add default and value-specific partitions.
    • Validate by inserting data into the table.
  • We can detach as well as drop the partitions from the table.

Create Partitioned Table

Let us create a partitioned table with the name users_part.

  • It contains the same columns as users.
  • We will partition based on the user_role field.
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U',
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users_part (
    user_id SERIAL,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U',
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_role, user_id)
) PARTITION BY LIST(user_role);
CREATE INDEX users_part_email_id_idx 
    ON users_part(user_email_id);
-- Below INSERT statement will fail if we attempt to execute it here
INSERT INTO users_part (user_first_name, user_last_name, user_email_id)
VALUES 
    ('Scott', 'Tiger', 'scott@tiger.com'),
    ('Donald', 'Duck', 'donald@duck.com'),
    ('Mickey', 'Mouse', 'mickey@mouse.com');

Watch the video tutorial here