Database Essentials - Partitioning Tables and Indexes - Range Partitioning

Databases Postgres
1

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

  • It is primarily used to create partitions based on a given range of values.
  • Here are the steps involved in creating a table using range partitioning strategy:
    • Create a table using PARTITION BY RANGE
    • Add default and range-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_range_part.

  • It contains the same columns as users.
  • We will partition the table based on the created_dt field.
  • We will create one partition per year with the naming convention users_range_part_yyyy (e.g., users_range_part_2016).
%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@pg.itversity.com:5432/itversity_sms_db
%sql DROP TABLE IF EXISTS users_range_part
%%sql

CREATE TABLE users_range_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', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (created_dt, user_id)
) PARTITION BY RANGE(created_dt)

We will not be able to insert data until we add at least one partition.

Key Concepts Explanation

Create Partitioned Table

In this concept, we demonstrated creating a partitioned table named users_range_part with proper column definitions and partitioning strategy based on the created_dt field.

CREATE TABLE users_range_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 (created_dt, user_id)
) PARTITION BY RANGE(created_dt)

Validate by Inserting Data

You can validate the partitioned table by inserting data into it. After adding at least one partition, you can insert data into the users_range_part table.

INSERT INTO users_range_part VALUES (DEFAULT, 'John', 'Doe', 'john.doe@example.com', TRUE, 'password123', 'U', TRUE, '2016-01-01', now());

Hands-On Tasks

  1. Create a partitioned table users_range_part similar to the example provided.
  2. Insert sample data into the newly created partitioned table to ensure it works as expected.

Conclusion

In this article, we covered the concept of range partitioning for tables in a database. By following the steps mentioned, readers can create partitioned tables and manage partitions effectively. Please practice these concepts to enhance your understanding further.

Watch the video tutorial here