Database Essentials - Partitioning Tables and Indexes - Sub Partitioning

In this article, we will explore how to create nested partitions in PostgreSQL to efficiently manage large amounts of data. We will use list-list partitioning as an example, where we partition by year and then further partition by quarter.

Key Concepts Explanation

List - List Partitioning

List-list partitioning involves creating tables based on lists of values for both the main partition and the sub-partitions. We will create tables for yearly partitions and quarterly partitions by year.

-- Create main table users_qtly partitioned by year
CREATE TABLE users_qtly (
    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,
    created_year INT,
    created_mnth INT,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (created_year, created_mnth, user_id)
) PARTITION BY LIST(created_year);

-- Create table for yearly partitions
CREATE TABLE users_qtly_2016 PARTITION OF users_qtly FOR VALUES IN (2016) PARTITION BY LIST (created_mnth);

-- Create table for quarterly partitions
CREATE TABLE users_qtly_2016q1 PARTITION OF users_qtly_2016 FOR VALUES IN (1, 2, 3);
CREATE TABLE users_qtly_2016q2 PARTITION OF users_qtly_2016 FOR VALUES IN (4, 5, 6);

List - Range Partitioning

List-range partitioning involves creating tables based on a range of values for the main partition and sub-partitions. We will create tables for yearly partitions and quarterly partitions by year.

-- Create main table users_qtly partitioned by year
CREATE TABLE users_qtly (
    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,
    created_year INT,
    created_mnth INT,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (created_year, created_mnth, user_id)
) PARTITION BY LIST(created_year);

-- Create table for yearly partitions
CREATE TABLE users_qtly_2016 PARTITION OF users_qtly FOR VALUES IN (2016) PARTITION BY RANGE (created_mnth);

-- Create table for quarterly partitions
CREATE TABLE users_qtly_2016q1 PARTITION OF users_qtly_2016 FOR VALUES FROM (1) TO (3);
CREATE TABLE users_qtly_2016q2 PARTITION OF users_qtly_2016 FOR VALUES FROM (4) TO (6);

Hands-On Tasks

  1. Create a nested partitioning strategy for your own dataset based on year and quarter.
  2. Experiment with different partitioning techniques to optimize query performance.

Conclusion

In conclusion, nested partitioning in PostgreSQL allows for efficient data management by organizing data into logical partitions. By following the examples provided, you can implement nested partitions in your own PostgreSQL databases. Remember to explore different partitioning strategies based on your specific requirements.

Let’s dive into the video to visualize these concepts in action:

Click here to watch the video demonstration

Watch the video tutorial here