Repartitioning – Range

Topic
Materials

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 that are involved in repartitioning from year to month.

    • Detach all yearly partitions from users_range_part.

    • Add new partitions for each month.

    • Load data from detached partitions into the table with new partitions for each month.

    • Validate to ensure that all the data is copied.

    • Drop all the detached partitions.

%load_ext sql

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

Note

Detach all yearly partitions

%%sql

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2016

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2017

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2018

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2019

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE users_range_part
    DETACH PARTITION users_range_part_2020

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

Note

Add new partitions for every month between 2016 January and 2020 December.

!pip install psycopg2

Copy to clipboard

Defaulting to user installation because normal site-packages is not writeable
Requirement already satisfied: psycopg2 in /opt/anaconda3/envs/beakerx/lib/python3.6/site-packages (2.8.6)

Copy to clipboard

import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd

months = pd.date_range(start='1/1/2016', end='3/31/2016', freq='1M')

for month in months:
    begin_date = month - MonthBegin(1)
    end_date = month + MonthEnd(0)
    print(str(month)[:7].replace('-', ''), end=':')
    print(str(begin_date).split(' ')[0], end=':')
    print(str(end_date).split(' ')[0])

Copy to clipboard

201601:2016-01-01:2016-01-31
201602:2016-02-01:2016-02-29
201603:2016-03-01:2016-03-31

Copy to clipboard

import psycopg2

Copy to clipboard

import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd

months = pd.date_range(start='1/1/2016', end='12/31/2020', freq='1M')

connection = psycopg2.connect(
    host='localhost',
    port='5432',
    database='itversity_sms_db',
    user='itversity_sms_user',
    password='sms_password'
)
cursor = connection.cursor()
table_name = 'users_range_part'
query = '''
CREATE TABLE {table_name}_{yyyymm}
PARTITION OF {table_name}
FOR VALUES FROM ('{begin_date}') TO ('{end_date}')
'''
for month in months:
    begin_date = month - MonthBegin(1)
    end_date = month + MonthEnd(0)
    print(f'Adding partition for {begin_date} and {end_date}')
    cursor.execute(
        query.format(
            table_name=table_name,
            yyyymm=str(month)[:7].replace('-', ''),
            begin_date=str(begin_date).split(' ')[0],
            end_date=str(end_date).split(' ')[0]
        ), ()
    )
connection.commit()
cursor.close()
connection.close()

Copy to clipboard

Adding partition for 2016-01-01 00:00:00 and 2016-01-31 00:00:00
Adding partition for 2016-02-01 00:00:00 and 2016-02-29 00:00:00
Adding partition for 2016-03-01 00:00:00 and 2016-03-31 00:00:00
Adding partition for 2016-04-01 00:00:00 and 2016-04-30 00:00:00
Adding partition for 2016-05-01 00:00:00 and 2016-05-31 00:00:00
Adding partition for 2016-06-01 00:00:00 and 2016-06-30 00:00:00
Adding partition for 2016-07-01 00:00:00 and 2016-07-31 00:00:00
Adding partition for 2016-08-01 00:00:00 and 2016-08-31 00:00:00
Adding partition for 2016-09-01 00:00:00 and 2016-09-30 00:00:00
Adding partition for 2016-10-01 00:00:00 and 2016-10-31 00:00:00
Adding partition for 2016-11-01 00:00:00 and 2016-11-30 00:00:00
Adding partition for 2016-12-01 00:00:00 and 2016-12-31 00:00:00
Adding partition for 2017-01-01 00:00:00 and 2017-01-31 00:00:00
Adding partition for 2017-02-01 00:00:00 and 2017-02-28 00:00:00
Adding partition for 2017-03-01 00:00:00 and 2017-03-31 00:00:00
Adding partition for 2017-04-01 00:00:00 and 2017-04-30 00:00:00
Adding partition for 2017-05-01 00:00:00 and 2017-05-31 00:00:00
Adding partition for 2017-06-01 00:00:00 and 2017-06-30 00:00:00
Adding partition for 2017-07-01 00:00:00 and 2017-07-31 00:00:00
Adding partition for 2017-08-01 00:00:00 and 2017-08-31 00:00:00
Adding partition for 2017-09-01 00:00:00 and 2017-09-30 00:00:00
Adding partition for 2017-10-01 00:00:00 and 2017-10-31 00:00:00
Adding partition for 2017-11-01 00:00:00 and 2017-11-30 00:00:00
Adding partition for 2017-12-01 00:00:00 and 2017-12-31 00:00:00
Adding partition for 2018-01-01 00:00:00 and 2018-01-31 00:00:00
Adding partition for 2018-02-01 00:00:00 and 2018-02-28 00:00:00
Adding partition for 2018-03-01 00:00:00 and 2018-03-31 00:00:00
Adding partition for 2018-04-01 00:00:00 and 2018-04-30 00:00:00
Adding partition for 2018-05-01 00:00:00 and 2018-05-31 00:00:00
Adding partition for 2018-06-01 00:00:00 and 2018-06-30 00:00:00
Adding partition for 2018-07-01 00:00:00 and 2018-07-31 00:00:00
Adding partition for 2018-08-01 00:00:00 and 2018-08-31 00:00:00
Adding partition for 2018-09-01 00:00:00 and 2018-09-30 00:00:00
Adding partition for 2018-10-01 00:00:00 and 2018-10-31 00:00:00
Adding partition for 2018-11-01 00:00:00 and 2018-11-30 00:00:00
Adding partition for 2018-12-01 00:00:00 and 2018-12-31 00:00:00
Adding partition for 2019-01-01 00:00:00 and 2019-01-31 00:00:00
Adding partition for 2019-02-01 00:00:00 and 2019-02-28 00:00:00
Adding partition for 2019-03-01 00:00:00 and 2019-03-31 00:00:00
Adding partition for 2019-04-01 00:00:00 and 2019-04-30 00:00:00
Adding partition for 2019-05-01 00:00:00 and 2019-05-31 00:00:00
Adding partition for 2019-06-01 00:00:00 and 2019-06-30 00:00:00
Adding partition for 2019-07-01 00:00:00 and 2019-07-31 00:00:00
Adding partition for 2019-08-01 00:00:00 and 2019-08-31 00:00:00
Adding partition for 2019-09-01 00:00:00 and 2019-09-30 00:00:00
Adding partition for 2019-10-01 00:00:00 and 2019-10-31 00:00:00
Adding partition for 2019-11-01 00:00:00 and 2019-11-30 00:00:00
Adding partition for 2019-12-01 00:00:00 and 2019-12-31 00:00:00
Adding partition for 2020-01-01 00:00:00 and 2020-01-31 00:00:00
Adding partition for 2020-02-01 00:00:00 and 2020-02-29 00:00:00
Adding partition for 2020-03-01 00:00:00 and 2020-03-31 00:00:00
Adding partition for 2020-04-01 00:00:00 and 2020-04-30 00:00:00
Adding partition for 2020-05-01 00:00:00 and 2020-05-31 00:00:00
Adding partition for 2020-06-01 00:00:00 and 2020-06-30 00:00:00
Adding partition for 2020-07-01 00:00:00 and 2020-07-31 00:00:00
Adding partition for 2020-08-01 00:00:00 and 2020-08-31 00:00:00
Adding partition for 2020-09-01 00:00:00 and 2020-09-30 00:00:00
Adding partition for 2020-10-01 00:00:00 and 2020-10-31 00:00:00
Adding partition for 2020-11-01 00:00:00 and 2020-11-30 00:00:00
Adding partition for 2020-12-01 00:00:00 and 2020-12-31 00:00:00

Copy to clipboard

Note

Load data from detached yearly partitions into monthly partitioned table.

%%sql

INSERT INTO users_range_part
SELECT * FROM users_range_part_2016

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO users_range_part
SELECT * FROM users_range_part_2017

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO users_range_part
SELECT * FROM users_range_part_2018

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO users_range_part
SELECT * FROM users_range_part_2019

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO users_range_part
SELECT * FROM users_range_part_2020

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT * FROM users_range_part

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.

Copy to clipboard

user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
3 Mickey Mouse mickey@mouse.com False None U False 2017-06-22 2020-11-24 12:12:27.094936
1 Scott Tiger scott@tiger.com False None U False 2018-10-01 2020-11-24 12:12:27.094936
2 Donald Duck donald@duck.com False None U False 2019-02-10 2020-11-24 12:12:27.094936
%%sql

SELECT * FROM users_range_part_201706

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
3 Mickey Mouse mickey@mouse.com False None U False 2017-06-22 2020-11-24 12:12:27.094936
%%sql

SELECT * FROM users_range_part_201810

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
1 Scott Tiger scott@tiger.com False None U False 2018-10-01 2020-11-24 12:12:27.094936
%%sql

SELECT * FROM users_range_part_201902

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.

Copy to clipboard

user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
2 Donald Duck donald@duck.com False None U False 2019-02-10 2020-11-24 12:12:27.094936

Note

As we are able to see the data in the monthly partitioned table, we can drop the tables which are created earlier using yearly partitioning strategy.

%%sql

DROP TABLE users_range_part_2016

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

DROP TABLE users_range_part_2017

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

DROP TABLE users_range_part_2018

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

DROP TABLE users_range_part_2019

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

DROP TABLE users_range_part_2020

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT table_catalog, 
    table_schema, 
    table_name FROM information_schema.tables
WHERE table_name ~ 'users_range_part_'
ORDER BY table_name

Copy to clipboard

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
61 rows affected.

Copy to clipboard

table_catalog table_schema table_name
itversity_sms_db public users_range_part_201601
itversity_sms_db public users_range_part_201602
itversity_sms_db public users_range_part_201603
itversity_sms_db public users_range_part_201604
itversity_sms_db public users_range_part_201605
itversity_sms_db public users_range_part_201606
itversity_sms_db public users_range_part_201607
itversity_sms_db public users_range_part_201608
itversity_sms_db public users_range_part_201609
itversity_sms_db public users_range_part_201610
itversity_sms_db public users_range_part_201611
itversity_sms_db public users_range_part_201612
itversity_sms_db public users_range_part_201701
itversity_sms_db public users_range_part_201702
itversity_sms_db public users_range_part_201703
itversity_sms_db public users_range_part_201704
itversity_sms_db public users_range_part_201705
itversity_sms_db public users_range_part_201706
itversity_sms_db public users_range_part_201707
itversity_sms_db public users_range_part_201708
itversity_sms_db public users_range_part_201709
itversity_sms_db public users_range_part_201710
itversity_sms_db public users_range_part_201711
itversity_sms_db public users_range_part_201712
itversity_sms_db public users_range_part_201801
itversity_sms_db public users_range_part_201802
itversity_sms_db public users_range_part_201803
itversity_sms_db public users_range_part_201804
itversity_sms_db public users_range_part_201805
itversity_sms_db public users_range_part_201806
itversity_sms_db public users_range_part_201807
itversity_sms_db public users_range_part_201808
itversity_sms_db public users_range_part_201809
itversity_sms_db public users_range_part_201810
itversity_sms_db public users_range_part_201811
itversity_sms_db public users_range_part_201812
itversity_sms_db public users_range_part_201901
itversity_sms_db public users_range_part_201902
itversity_sms_db public users_range_part_201903
itversity_sms_db public users_range_part_201904
itversity_sms_db public users_range_part_201905
itversity_sms_db public users_range_part_201906
itversity_sms_db public users_range_part_201907
itversity_sms_db public users_range_part_201908
itversity_sms_db public users_range_part_201909
itversity_sms_db public users_range_part_201910
itversity_sms_db public users_range_part_201911
itversity_sms_db public users_range_part_201912
itversity_sms_db public users_range_part_202001
itversity_sms_db public users_range_part_202002
itversity_sms_db public users_range_part_202003
itversity_sms_db public users_range_part_202004
itversity_sms_db public users_range_part_202005
itversity_sms_db public users_range_part_202006
itversity_sms_db public users_range_part_202007
itversity_sms_db public users_range_part_202008
itversity_sms_db public users_range_part_202009
itversity_sms_db public users_range_part_202010
itversity_sms_db public users_range_part_202011
itversity_sms_db public users_range_part_202012
itversity_sms_db public users_range_part_default