Database Essentials - Partitioning Tables and Indexes - Repartitioning - Range

Let us understand how we can repartition the existing partitioned table.

  • We will use users_range_part table. It is originally partitioned for each year.
  • Now we would like to partition for each month.
  • Here are the steps involved in repartitioning from year to month.

Detach all yearly partitions

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2016
ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2017
ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2018
ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2019
ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2020

Add new partitions

CREATE TABLE users_range_part_{yyyymm}
PARTITION OF users_range_part
FOR VALUES FROM ('{begin_date}') TO ('{end_date}')

Code is provided for adding new partitions to the table.

Load data into monthly partitioned table

INSERT INTO users_range_part
SELECT * FROM users_range_part_2016
INSERT INTO users_range_part
SELECT * FROM users_range_part_2017
INSERT INTO users_range_part
SELECT * FROM users_range_part_2018
INSERT INTO users_range_part
SELECT * FROM users_range_part_2019
INSERT INTO users_range_part
SELECT * FROM users_range_part_2020

Dropping old partitions

DROP TABLE users_range_part_2016
DROP TABLE users_range_part_2017
DROP TABLE users_range_part_2018
DROP TABLE users_range_part_2019
DROP TABLE users_range_part_2020

After data is loaded into the monthly partitioned table and all the records are verified, old tables using yearly partitioning strategy can be dropped.

Please refer to the video link for a detailed explanation of this process.

Click here to view the video

Hands-On Tasks

  1. Detach all yearly partitions and add new partitions for each month.
  2. Load data from detached yearly partitions into the monthly partitioned table.

Conclusion

In this article, we discussed how to repartition a table from yearly to monthly granularity. It involved detaching yearly partitions, adding monthly partitions, loading data, and dropping old partitions. Practice these concepts to have a better understanding and feel free to engage with the community for further questions or discussions.

Watch the video tutorial here