Database Essentials - Partitioning Tables and Indexes - Managing Partitions - Range

Databases Postgres
1

Let us understand how to manage partitions for the table users_range_part.

  • All users data created in a specific year should go to the respective partition created.
  • For example, all users data created in the year of 2016 should go to users_range_part_2016.
  • We can add partition to the existing partitioned table using CREATE TABLE partition_name PARTITION OF table_name.
  • We can have a partition for default values so that all the data that does not satisfy the partition condition can be added to it.
  • We can have a partition for a specific range of values using FOR VALUES FROM (from_value) TO (to_value) as part of CREATE TABLE partition_name PARTITION OF table_name.
  • Once partitions are added, we can insert data into the partitioned table.

Key Concept 1

Here is how we can create a partition for default values for a range partitioned table users_range_part.

%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@pg.itversity.com:5432/itversity_sms_db
%%sql

CREATE TABLE users_range_part_default
PARTITION OF users_range_part DEFAULT

Key Concept 2

As there is an overlap between the previous partition and the following one, the command to create a partition for data ranging from 2016-01-01 till 2017-12-31 will fail.

%%sql
CREATE TABLE users_range_part_2017
PARTITION OF users_range_part
FOR VALUES FROM ('2016-01-01') TO ('2017-12-31')

This is how we can create partitions for the years 2017, 2018, 2019, etc.

%%sql
CREATE TABLE users_range_part_2017
PARTITION OF users_range_part
FOR VALUES FROM ('2017-01-01') TO ('2017-12-31')

%%sql
CREATE TABLE users_range_part_2018
PARTITION OF users_range_part
FOR VALUES FROM ('2018-01-01') TO ('2018-12-31')

%%sql
CREATE TABLE users_range_part_2019
PARTITION OF users_range_part
FOR VALUES FROM ('2019-01-01') TO ('2019-12-31')

%%sql
CREATE TABLE users_range_part_2020
PARTITION OF users_range_part
FOR VALUES FROM ('2020-01-01') TO ('2020-12-31')

Hands-On Tasks

Let’s perform the following tasks to apply the concepts discussed in the article.

  1. Insert sample data into the partitioned table users_range_part.
  2. Query different partitions like users_range_part_default, users_range_part_2017, users_range_part_2018, users_range_part_2019, and users_range_part_2020.

Conclusion

In this article, we have learned how to manage partitions for a range partitioned table in PostgreSQL. It is essential to understand partitioning concepts and apply them effectively to manage data efficiently. Practice and engage with the community to enhance your skills and knowledge.

[embed]https://www.youtube.com/watch?v=yourvideourl[/embed]

Watch the video tutorial here