Sub Partitioning

Private: Mastering SQL using Postgresql Partitioning Tables and Indexes Sub Partitioning

We can have sub partitions created with different permutations and combinations. Sub Partitioning is also known as nested partitioning.

List - List

List - Range and others.

Note

Try different sub-partitioning strategies based up on your requirements.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

List - List Partitioning¶

Let us understand how we can create table using list - list sub partitioning. We would like to have main partition per year and then sub partitions per quarter.

Create table users_qtly with PARTITION BY LIST with created_year.
Create tables for yearly partitions with PARTITION BY LIST with created_month.
Create tables for quarterly partitions with list of values using FOR VALUES IN.

%%sql

DROP TABLE IF EXISTS users_qtly

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

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', --U and A
    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)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016
PARTITION OF users_qtly
FOR VALUES IN (2016)
PARTITION BY LIST (created_mnth)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016q1
PARTITION OF users_qtly_2016
FOR VALUES IN (1, 2, 3)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016q2
PARTITION OF users_qtly_2016
FOR VALUES IN (4, 5, 6)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

List - Range Partitioning¶

Let us understand how we can create table using list - Range sub partitioning using same example as before (partitioning by year and then by quarter).

Create table with PARTITION BY LIST with created_year.
Create tables for yearly partitions with PARTITION BY RANGE with created_month.
Create tables for quarterly partitions with the range of values using FOR VALUES FROM (lower_bound) TO (upper_bound).

%%sql

DROP TABLE IF EXISTS users_qtly

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

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', --U and A
    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)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016
PARTITION OF users_qtly
FOR VALUES IN (2016)
PARTITION BY RANGE (created_mnth)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016q1
PARTITION OF users_qtly_2016
FOR VALUES FROM (1) TO (3)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_qtly_2016q2
PARTITION OF users_qtly_2016
FOR VALUES FROM (4) TO (6)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

Previous Topic

Back to Lesson

Next Topic